Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 2

Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 1

4. Устранение "бессмысленных" предикатов

Столь же бессмысленными являются предикаты, которые (почти) всегда истинны. Как вы можете себе представить, если вы запрашиваете:

SELECT * FROM actor WHERE 1 = 1;

... то базы данных не станут его фактически выполнять, а просто проигнорируют. Я однажды отвечал на вопрос об этом на сайте Stack Overflow и именно поэтому решил написать данную статью. Оставлю проверку этого в качестве упражнения читателю, но что произойдет, если предикат чуть-чуть менее "бессмысленный"? Например:

SELECT * FROM film WHERE release_year = release_year;

Нужно ли действительно сравнивать значение с самим собой для каждой строки? Нет, ведь значения, для которого этот предикат будет FALSE, не существует, правда? Но нам все равно нужно проверить это. Хотя предикат не может оказаться равным FALSE, он вполне может оказаться везде равным NULL, опять же вследствие трёхзначной логики. Столбец RELEASE_YEAR допускает неопределенное значение, и если для какой-либо из строк 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 это не оптимизирует.

Oracle

Да!

----------------------------------------------------
| 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)

PostgreSQL

Увы, нет!

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 Server

Как ни странно, но 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

Обратите внимание, что столбец EXTRA теперь пуст, как будто у нас вообще нет предложения WHERE!

Oracle

Да!

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

Опять же, никаких предикатов не применяется.

PostgreSQL

Ничего себе, опять нет!

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

Применяется фильтр и оценка кардинальности по-прежнему равна 5. Облом!

SQL Server

И тут опять нет!

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

Резюме

Вроде бы и простая оптимизация, но она применяется отнюдь не во всех СУБД, в частности, как ни странно, не применяется в SQL Server!

5. Проекции в подзапросах EXISTS

Что интересно, о них меня все время спрашивают на моём мастер-классе, где я отстаиваю точку зрения о том, что SELECT * обычно до добра не доводит. Вопрос состоит в том: можно ли использовать SELECT * в подзапросе EXISTS? Например, если нам нужно найти актеров, игравших в фильмах...

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);

Все эти базы данных сообщают об ошибке деления на нуль. Обратите внимание на интересный факт: в MySQL, при делении на нуль, в результате мы получаем 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. Это значит, что ни одна из наших баз данных, на самом деле, не вычисляет проекцию (то есть предложение SELECT) подзапроса EXISTS. 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: Оставайтесь с нами, ведь вас ждет часть 3, в которой мы обсудим остальные клёвые оптимизации SQL.