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