비용 모델에 의존하지 않는 멋진 SQL 최적화입니다. 1 부

메타데이터(예: 제약 조건)와 쿼리 자체만을 기반으로 구현할 수 있는 5가지 간단한 최적화 데이터베이스와 SQL에 대한 일반적인 이해와 DBMS에 대한 실제 경험이 있는 사람들을 위해 설계된 Lukas Eder의 기사를 적용한 내용을 제공합니다. . 비용 최적화는 실제로 최신 데이터베이스에서 SQL 쿼리를 최적화하는 표준 방법입니다. 이것이 바로 3GL(3세대 프로그래밍 언어) 에서 최신 최적화 프로그램이 생성한 동적으로 계산된 실행 계획을 초과하는 복잡한 알고리즘을 수동으로 작성하는 것이 매우 어려운 이유입니다. 오늘은 비용 최적화, 즉 데이터베이스의 비용 모델을 기반으로 한 최적화에 대해 논의하지 않겠습니다. 훨씬 간단한 최적화를 살펴보겠습니다. 메타데이터(예: 제한 사항)와 요청 자체를 기반으로만 구현할 수 있는 것입니다. 일반적으로 데이터베이스에 대한 구현은 뉴턴 이항식이 아닙니다. 이 경우 인덱스의 존재 여부, 데이터 볼륨 및 데이터 배포의 왜곡도에 관계없이 모든 최적화가 더 나은 실행 계획으로 이어지기 때문입니다. "뉴턴 이항이 아님"은 최적화를 구현하는 것이 얼마나 쉬운 지가 아니라 이를 수행해야 하는지 여부에 대한 의미입니다. 이러한 최적화는 [데이터베이스에 대한] 불필요한 추가 작업을 제거합니다( 이미 설명한 불필요한 필수 작업과 반대 ).

이러한 최적화는 무엇을 위해 사용됩니까?

대부분은 다음 용도로 사용됩니다.

• 쿼리의 버그 수정;
• 데이터베이스가 실제로 뷰 로직을 실행하지 않고도 뷰를 재사용할 수 있습니다.

첫 번째 경우에는 "그럼 이 어리석은 SQL 쿼리를 수정해 보세요."라고 말할 수 있습니다. 그러나 한 번도 실수한 적이 없는 사람이 먼저 나에게 돌을 던지십시오. 두 번째 경우는 특히 흥미롭습니다. 여러 계층에서 재사용할 수 있는 복잡한 뷰 및 테이블 함수 라이브러리를 생성할 수 있는 기능을 제공합니다.

사용된 데이터베이스

이 기사에서는 가장 널리 사용되는 5가지 DBMS( 데이터베이스 순위에 따라 ) 의 10가지 SQL 최적화를 비교할 것입니다 .

• 오라클 12.2;
• MySQL 8.0.2;
• SQL 서버 2014;
• 포스트그레SQL 9.6;
• DB2 LUW 10.5.

또 다른 평가는 거의 그것을 반영합니다. 평소와 마찬가지로 이 기사에서는 Sakila 데이터베이스를 쿼리할 것입니다 . 다음은 이러한 10가지 유형의 최적화 목록입니다.

1. 전이적 폐쇄;
2. 불가능한 술어와 불필요한 테이블 호출;
3. JOIN 제거;
4. "무의미한" 술어 제거;
5. EXISTS 하위 쿼리의 예측;
6. 술어 병합;
7. 아마도 빈 세트;
8. 제약사항 확인;
9. 불필요한 반사적 연결;
10. 푸시다운 조건자

오늘 우리는 pp에 대해 논의 할 것입니다. 1-3, 두 번째 부분 - 4 및 5, 부분 3 - 6-10.

1. 전이적 폐쇄

더 간단한 것부터 시작해보자: 전이적 폐쇄(transitive closure) . 이는 항등 연산자와 같은 많은 수학 연산에 적용되는 사소한 개념입니다. 이 경우 다음과 같이 공식화할 수 있습니다. A = B이고 B = C이면 A = C입니다.

어렵지 않죠? 그러나 이는 SQL 최적화 프로그램에 대해 몇 가지 흥미로운 의미를 갖습니다. 예를 살펴보겠습니다. ACTOR_ID = 1인 모든 영화를 추출해 보겠습니다.

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

결과는 다음과 같습니다.

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

이제 Oracle DBMS의 경우 이 쿼리를 실행하기 위한 계획을 살펴보겠습니다.

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

여기서는 술어에 관한 섹션이 특히 흥미롭습니다. 전이적 폐쇄로 인해 ACTOR_ID = 1 조건자는 ACTOR 테이블과 FILM_ACTOR 테이블 모두에 적용됩니다. 만약에:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

더 복잡한 쿼리의 경우 이는 매우 좋은 결과를 생성합니다. 특히, 다음 쿼리에서와 같이 배우별 평균 영화 수가 아닌 술어의 특정 상수 값을 기반으로 추정을 선택할 수 있게 되므로 카디널리티 추정의 정확도가 크게 향상됩니다. 같은 결과):

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

그의 계획:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

보시다시피 FILM_ACTOR 테이블의 행 수는 과대평가된 반면 NESTED LOOP는 과소평가되었습니다. 다음은 몇 가지 흥미로운 값입니다.

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

결과:

19
27.315

추정치가 나오는 곳입니다. 데이터베이스가 ACTOR_ID = 1에 대해 이야기하고 있다는 것을 알고 있으면 이 특정 배우 의 영화 수에 대한 통계를 수집할 수 있습니다 . 그렇지 않은 경우 (표준 통계 수집 메커니즘은 FIRST_NAME/LAST_NAME을 ACTOR_ID와 연관시키지 않기 때문에) 모든 배우 의 평균 영화 수를 얻습니다 . 이 특별한 경우에는 간단하고 중요하지 않은 오류이지만 복잡한 쿼리에서는 더 많이 전파되고 누적되어 쿼리(계획의 상위)로 더 나아가 잘못된 JOIN 선택으로 이어질 수 있습니다. 따라서 가능할 때마다 전이적 폐쇄를 활용하도록 조인과 간단한 조건자를 디자인하세요. 이 기능을 지원하는 다른 데이터베이스는 무엇입니까?

DB2

예!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

그런데 이와 같은 멋진 실행 계획을 좋아한다면 Markus Winand의 스크립트를 확인하세요 .

MySQL

불행하게도 MySQL 실행 계획은 이러한 유형의 분석에 적합하지 않습니다. 출력 정보에서 조건자 자체가 누락되었습니다.

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

그러나 REF 열에 const가 두 번 지정되었다는 사실은 두 테이블 모두 상수 값을 검색하고 있음을 나타냅니다. 동시에 FIRST_NAME/LAST_NAME을 사용한 쿼리 계획은 다음과 같습니다.

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

보시다시피 REF는 이제 JOIN 조건자의 열을 참조합니다. 카디널리티 점수는 Oracle과 거의 동일합니다. 그렇습니다. MySQL은 전이적 폐쇄도 지원합니다.

포스트그레SQL

예!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

SQL 서버

예!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

요약

우리의 모든 데이터베이스는 전이적 폐쇄를 지원합니다. 그러나 기사의 다음 부분에서 #6을 기다리십시오. 모든 데이터베이스가 처리할 수 없는 복잡한 전이적 폐쇄 사례가 있습니다.

2. 불가능한 술어와 불필요한 테이블 호출

이것은 완전히 어리석은 최적화인데 왜 안되겠습니까? 사용자가 불가능한 술어를 작성한다면, 굳이 그것을 실행할 필요가 있을까요? 여기 몇 가지 예가 있어요.

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

첫 번째 쿼리는 분명히 어떤 결과도 반환하지 않지만 두 번째 쿼리에도 동일한 설명이 적용됩니다. 결국 NULL IS NULL은 항상 TRUE이지만 NULL = NULL 계산의 결과는 NULL입니다. 이는 세 값 논리 에 따라 FALSE와 동일합니다. 이는 설명이 매우 필요하므로 어떤 데이터베이스가 이 최적화를 수행하는지 바로 알아봅시다.

DB2

예!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

보시다시피 ACTOR 테이블에 대한 액세스는 계획에서 완전히 제외됩니다. 여기에는 0개의 행을 생성하는 GENROW 연산만 포함됩니다. 완벽한.

MySQL

예!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

이번에는 MySQL이 WHERE 절이 불가능한 것에 대해 친절하게 알려 주었습니다. 감사합니다! 이는 특히 다른 데이터베이스에 비해 분석을 훨씬 쉽게 만듭니다.

신탁

예!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

계획에는 여전히 ACTOR 테이블에 대한 액세스가 언급되어 있고 예상 행 수는 여전히 200개이지만, TRUE가 아닌 Id=1의 필터링 작업(FILTER)도 있습니다. Oracle은 표준 SQL 부울 데이터 유형을 싫어하기 때문에 계획에 FALSE 대신 NULL IS NOT NULL을 표시합니다. 아 뭐... 하지만 진지하게, 그 술어를 보세요. 나는 1000줄 하위 트리와 극도로 높은 비용 값을 사용하여 실행 계획을 디버깅할 기회가 있었는데, 전체 하위 트리가 NULL IS NOT NULL 필터에 의해 "절단"되었다는 사실을 발견한 후에야 발견했습니다. 조금 실망 스럽습니다.

포스트그레SQL

예!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

이미 더 좋습니다. 성가신 ACTOR 테이블 호출이 없고 깔끔하고 작은 FALSE 조건자가 없습니다.

SQL 서버?

예!

|--Constant Scan

SQL Server는 이를 " 상수 스캔"이라고 부르는데, 이는 DB2와 유사하게 아무 일도 일어나지 않는 스캔입니다. 우리의 모든 데이터베이스는 불가능한 술어를 제외할 수 있습니다:

3. JOIN 제거

이전 섹션에서는 단일 테이블 쿼리에서 불필요한 테이블 액세스를 관찰했습니다. 하지만 JOIN에 여러 테이블 액세스 중 하나가 필요하지 않으면 어떻게 될까요? 내 블로그의 이전 게시물에서 JOIN 제거에 대해 이미 썼습니다 . SQL 엔진은 쿼리 유형과 기본 및 외래 키의 존재 여부를 기반으로 특정 쿼리에 특정 JOIN이 실제로 필요한지 또는 JOIN을 제거해도 쿼리 의미에 영향을 주지 않는지 여부를 결정할 수 있습니다. 다음 세 가지 예에서는 모두 JOIN이 필요하지 않습니다. 내부 ...-대-일 조인은 NOT NULL 외래 키를 사용하여 제거할 수 있습니다. 대신:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

데이터베이스는 다음을 수행할 수 있습니다.

SELECT first_name, last_name
FROM customer c

Null 허용 외래 키가 있는 경우 "...-to-one" 유형의 INNER JOIN을 대체할 수 있습니다. 위 쿼리는 외래 키에 NOT NULL 제약 조건이 적용되는 경우 작동합니다. 그렇지 않은 경우 예를 들어 다음 요청과 같이 하세요.

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

그런 다음 JOIN을 제거할 수 있지만 다음과 같이 NOT NULL 조건자를 추가해야 합니다.

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

"...-to-one" 유형의 OUTER JOIN은 고유 키가 있는 경우 제거할 수 있습니다. 대신에:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

데이터베이스는 다음과 같은 작업을 수행할 수 있습니다.

SELECT first_name, last_name
FROM customer c

... CUSTOMER.ADDRESS_ID에 대한 외래 키가 없는 경우에도 마찬가지입니다. "...-to-many" 유형의 고유한 외부 연결(DISTINCT OUTER JOIN)을 제거할 수 있습니다. 대신에:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

데이터베이스는 다음을 수행할 수 있습니다.

SELECT DISTINCT first_name, last_name
FROM actor a

이러한 모든 예는 이전 기사에서 자세히 연구되었으므로 반복하지 않고 다양한 데이터베이스가 제거할 수 있는 모든 것을 요약하겠습니다. 불행하게도 모든 데이터베이스가 모든 유형의 연결을 해결할 수 있는 것은 아닙니다. DB2와 SQL Server는 여기에서 확실한 리더입니다! 계속됩니다