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

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

8. Ограничения CHECK

О, это крутая штука! В нашей базе данных Sakila есть ограничение CHECK на столбце FILM.RATING:

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

Серьезно, используйте ограничения CHECK для обеспечения целостности данных. Стоимость их добавления исключительно невелика – намного меньше, чем других ограничений, например, PRIMARY, UNIQUE или FOREIGN KEY, ведь для их работы не нужен индекс, так что они достаются вам практически "бесплатно". Но тут имеется интересный нюанс, связанный с оптимизацией! Рассмотрим следующие запросы:

Невозможные предикаты

Мы уже сталкивались с невозможными предикатами, даже с ограничениями NOT NULL (которые, на самом деле, представляют собой особую разновидность ограничений CHECK), но это еще круче:

SELECT *
FROM film
WHERE rating = 'N/A';

Подобного фильма нет, и не может быть, поскольку ограничение CHECK предотвращает его вставку (или обновление). Опять же, это должно преобразовываться в команду ничего не делать. А как насчет вот такого запроса?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Благодаря вышеприведенному индексу, достаточно, вероятно, просто выполнить быстрый просмотр индекса и подсчитать все фильмы с rating = 'NC-17', ведь это единственный оставшийся рейтинг. Так что запрос должен быть переписан вот так:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Так должно быть, независимо от индекса, ведь сравнение столбца с одним значением выполняется быстрее, чем с 4-мя. Итак, какие же базы данных умеет это делать?

DB2

Невозможный предикат (rating = 'N/A') Круто!

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

Predicate Information
 2 - RESID (1 = 0)

Обратный предикат (rating = 'NC-17') Не-а...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Хотя на шаге ID=3 используется индекс, и хотя кардинальности правильны, происходит полный просмотр, так как в плане отсутствует предикат диапазона, а есть только предикат "SARG". Подробности можно найти в обзоре Маркуса Винанда. Можно также продемонстрировать это, вручную инвертировав предикат и получив:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Теперь получился желаемый предикат диапазона.

MySQL

MySQL поддерживает синтаксис ограничений CHECK, но почему-то не обеспечивать их выполнение. Попробуйте вот это:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

и вы получите:

A
-
0

Ноль баллов для MySQL (ну правда, почему бы просто не поддерживать ограничения CHECK?)

Oracle

Невозможный предикат (rating = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Опять же, очень странный фильтр NULL IS NOT NULL, отсекающий FULL TABLE SCAN, которое можно было бы с тем же успехом вообще убрать из плана. Но, по крайней мере, работает! Обратный предикат (rating = 'NC-17') Упс:

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

Предикат не получается инвертировать, оценка кардинальности сильно хромает, вдобавок получаем INDEX FAST FULL SCAN вместо INDEX RANGE SCAN, и предикат filter вместо предиката access. А вот что мы должны были бы получить, например, вручную инвертировав предикат:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

Облом!

PostgreSQL

Обратите внимание, версия базы данных Sakila для PostgreSQL использует тип ENUM вместо ограничений CHECK для столбца RATING. Я продублировал таблицу, воспользовавшись вместо него ограничением CHECK. Невозможный предикат (rating = 'N/A') Не работает:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

Обратный предикат (rating = 'NC-17') Тоже не работает:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

Очень жалко! Примечание: Как любезно указал нам в комментариях Дэвид Роули (David Rowley), эту возможность можно включить путем задания параметра:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Обратный предикат (rating = 'NC-17') Тоже да!

  |--Compute Scalar
       |--Stream Aggregate
            |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Резюме

9. Ненужные рефлексивные соединения.

По мере повышения сложности запросов, вполне может оказаться, что вам нужно выполнить рефлексивное соединение таблицы по её первичному ключу. Поверьте, это вполне распространенная практика при построении сложных представлений и соединений их друг с другом, так что то, чтобы база данных обращала на это внимание – критически важная часть оптимизации сложного кода SQL. Я не стану демонстрировать сложный пример, достаточно простого, допустим:

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Это можно рассматривать как особый случай устранения JOIN, так как нам фактически не нужно соединение с A2, мы можем сделать всё, что нужно, при помощи одной только таблицы A1. Далее, устранение INNER JOIN функционирует нормально только при наличии FOREIGN KEY, которого у нас тут нет. Но благодаря первичному ключу по ACTOR_ID мы можем доказать, что на самом деле A1 = A2. В некотором смысле, это опять транзитивное замыкание. Можно пойти еще дальше и воспользоваться столбцами из обеих таблиц A1 и A2:

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

В классическом случае устранения JOIN, его уже нельзя было бы устранить, поскольку проецируются обе таблицы. Но раз мы уже доказали, что A1 = A2, то они взаимозаменяемы, так что можно ожидать, что запрос будет преобразован в:

SELECT first_name, last_name
FROM actor;

Какие же СУБД могут это сделать?

DB2

Проекция только таблицы A1 Да:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Проекция таблиц A1 и A2 ... тоже да:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

Проекция только таблицы A1 Нет.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Проекция таблиц A1 и A2 ... тоже нет

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Сплошное разочарование...

Oracle

Проекция только таблицы A1 Да

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Проекция таблиц A1 и A2 Опять да

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Проекция только таблицы A1 Нет:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Проекция таблиц A1 и A2 И снова нет:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

SQL Server

Проекция только таблицы A1 Как ни странно, нет! (Но не забывайте, что я использую SQL Server 2014, возможно, в более новых версиях уже всё исправлено. Мне определенно не помешает обновить версию!)

  |--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
       |--Index Scan(OBJECT:([a2]))
       |--Sort(ORDER BY:([a1].[actor_id] ASC))
            |--Table Scan(OBJECT:([a1]))

Проекция таблиц A1 и A2 Опять нет, причем план даже поменялся в худшую сторону:

  |--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
       |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
       |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Резюме

Откровенно говоря, я ждал, что эта оптимизация будет выполняться на всех базах данных, но сильно ошибался, как ни печально. Наряду с устранением JOIN, это одна из самых важных оптимизаций, позволяющая строить громадные SQL-запросы из таких переиспользуемых частей, как представления и табличные функции. К сожалению, она не поддерживается в 3 из 5 наиболее распространенных баз данных.