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

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

4. "무의미한" 술어 제거

(거의) 항상 참인 술어도 마찬가지로 의미가 없습니다. 상상할 수 있듯이 다음과 같이 묻는다면:

SELECT * FROM actor WHERE 1 = 1;

...그러면 데이터베이스는 이를 실제로 실행하지 않고 단순히 무시합니다. 나는 Stack Overflow에서 이에 대한 질문에 답변한 적이 있으며 이것이 바로 이 글을 쓰기로 결정한 이유입니다. 나는 이것을 독자들에게 연습으로 남겨두겠지만, 술어가 조금 덜 "의미없다"면 어떻게 될까요? 예를 들어:

SELECT * FROM film WHERE release_year = release_year;

각 행에 대해 값 자체를 실제로 비교해야 합니까? 아니요, 이 조건자가 FALSE가 되는 값은 없습니다 . 그렇죠? 하지만 아직은 확인해 볼 필요가 있습니다. 술어는 FALSE 와 동일할 수 없지만 값이 3개인 논리로 인해 모든 곳에서 NULL 과 동일할 수 있습니다 . RELEASE_YEAR 열은 Null을 허용하며 행 중 RELEASE_YEAR IS NULL 이 있는 경우 NULL = NULL 결과는 NULL이 되며 행을 제거해야 합니다. 따라서 요청은 다음과 같습니다.

SELECT * FROM film WHERE release_year IS NOT NULL;

어떤 데이터베이스가 이 작업을 수행합니까?

DB2

예!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

안타까운 일이지만 MySQL은 조건을 실행 계획에 매핑하지 않으므로 MySQL이 이 특정 최적화를 구현하는지 파악하는 것이 약간 까다롭습니다. 성능 평가를 수행하고 대규모 비교가 이루어지고 있는지 확인할 수 있습니다. 또는 색인을 추가할 수 있습니다.

CREATE INDEX i_release_year ON film (release_year);

그리고 그 대가로 다음 요청에 대한 계획을 세우십시오.

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

최적화가 작동한다면 두 쿼리의 계획은 거의 동일해야 합니다. 그러나 이 경우에는 그렇지 않습니다.

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

보시다시피 두 쿼리는 POSSIBLE_KEYS 및 FILTERED 열의 값이 크게 다릅니다 . 그래서 나는 MySQL이 이것을 최적화하지 않는다는 합리적인 추측을 감행하고 싶습니다.

신탁

예!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

포스트그레SQL

불행하게도!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

계획과 비용은 다양합니다. 즉, 절대적으로 좋지 않은 카디널리티 평가를 살펴보십시오. 반면 이 술어는 다음과 같습니다.

SELECT * FROM film WHERE release_year IS NOT NULL;

훨씬 더 나은 결과를 제공합니다.

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

버머!

SQL 서버

이상하게도 SQL Server는 이 작업도 수행하지 않는 것 같습니다.

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

그러나 계획의 외관에 따라 카디널리티 평가는 비용과 마찬가지로 정확합니다. 그러나 SQL Server에 대한 내 경험에 따르면 이 경우에는 SQL Server가 계획에 실제로 실행된 조건자를 표시하므로 최적화가 발생하지 않는다고 말하고 싶습니다(이유를 알아보려면 아래의 CHECK 제약 조건 예 를 살펴보세요 ). NOT NULL 열의 "의미 없는" 조건자는 어떻습니까 ? 위의 변환은 RELEASE_YEAR가 정의되지 않을 수 있기 때문에 필요했습니다 . 예를 들어 FILM_ID 열에 대해 동일한 의미 없는 쿼리를 실행하면 어떻게 되나요 ?

SELECT * FROM film WHERE film_id = film_id

이제 술어가 전혀 해당되지 않습니까? 아니면 적어도 그래야 합니다. 하지만 그렇습니까?

DB2

예!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

술어는 전혀 적용되지 않으며 모든 영화를 선택합니다.

MySQL

예! (다시 말하지만, 교육받은 추측)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

이제 WHERE 절이 전혀 없는 것처럼 EXTRA 열이 비어 있습니다 .

신탁

예!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

다시 말하지만, 술어가 적용되지 않습니다.

포스트그레SQL

와, 또 안돼!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

필터가 적용되었지만 카디널리티 점수는 여전히 5입니다. 안타깝습니다!

SQL 서버

그리고 여기서도 안돼!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

요약

단순한 최적화처럼 보이지만 모든 DBMS에서 사용되는 것은 아니며, 특히나 이상하게도 SQL Server에서는 사용되지 않습니다!

5. EXISTS 하위 쿼리의 프로젝션

흥미롭게도 나는 마스터 클래스에서 항상 이에 대해 질문을 받았는데, 여기서 나는 SELECT *가 일반적으로 어떤 좋은 결과도 얻지 못한다 는 관점을 옹호했습니다 . 문제는 EXISTS 하위 쿼리 에서 SELECT *를 사용할 수 있습니까 ? 예를 들어, 영화에 출연한 배우를 찾아야 한다면...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

그리고 대답은... 그렇습니다. 할 수 있다. 별표는 요청에 영향을 주지 않습니다. 이것을 어떻게 확신할 수 있습니까? 다음 쿼리를 고려해보세요.

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

이러한 데이터베이스는 모두 0으로 나누기 오류를 보고합니다. 흥미로운 사실에 주목하세요. MySQL에서는 0으로 나누면 오류 대신 NULL이 발생하므로 또 다른 불법적인 작업을 수행해야 합니다. 이제 위의 쿼리 대신 다음 쿼리를 실행하면 어떻게 될까요?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

이제 어떤 데이터베이스도 오류를 반환하지 않습니다. 모두 TRUE 또는 1 을 반환합니다 . 이는 우리 데이터베이스 중 어느 것도 실제로 EXISTS 하위 쿼리의 프로젝션(즉, SELECT 절 ) 을 평가하지 않는다는 것을 의미합니다 . 예를 들어 SQL Server는 다음 계획을 보여줍니다.

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

보시다시피 CASE 표현식이 상수로 변환되고 하위 쿼리가 제거되었습니다. 다른 데이터베이스는 계획에 하위 쿼리를 저장하고 프로젝션에 대해 아무 것도 언급하지 않으므로 Oracle의 원래 쿼리 계획을 다시 살펴보겠습니다.

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

위의 쿼리 계획은 다음과 같습니다.

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Id=3 에서 투영에 대한 정보를 관찰합니다 . 사실 우리는 필요하지 않기 때문에 FILM_ACTOR 테이블에 액세스하지도 않습니다. EXISTS 술어는 단일 ACTOR_ID 열의 외래 키 인덱스를 사용하여 수행할 수 있습니다. 이는 SELECT * 를 작성했지만 이 쿼리에 필요한 모든 것입니다 .

요약

운 좋게도 모든 데이터베이스는 EXISTS 하위 쿼리에서 프로젝션을 제거합니다 . 다른 멋진 SQL 최적화에 대해 논의할 Part 3를 계속 지켜봐 주시기 바랍니다 .