Кльові оптимізації 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';

Так має бути, незалежно від індексу, адже порівняння стовпця з одним значенням виконується швидше, ніж із чотирма. Отже, які ж бази даних це вміє робити?

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 найбільш поширених баз даних.