Кльові оптимізації SQL, що не залежать від вартісної моделі. Частина 5

Кльові оптимізації SQL, що не залежать від вартісної моделі. Частина 1 Кльові оптимізації SQL, які не залежать від вартісної моделі. Частина 2 Кльові оптимізації SQL, які не залежать від вартісної моделі. Частина 3 Кльові оптимізації SQL, які не залежать від вартісної моделі. Частина 4

10. Проштовхування предикатів

Ця оптимізація не цілком тут доречна, адже не можна стверджувати, що вона зовсім не ґрунтується на вартісній моделі. Але оскільки я не можу придумати жодної причини, чому оптимізатору не слід проштовхувати предикати в похідні таблиці, то вкажу це тут, разом з рештою невартісних оптимізацій. Розглянемо запит:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

Похідна таблиця в цьому запиті ніякого сенсу не має, і повинна бути усунена шляхом зменшення кількості рівнів вкладеності запиту. Але давайте поки що не звертатимемо на це уваги. Очікується, що база даних виконає замість вищенаведеного такий запит:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

А потім, знову ж таки, можливо, усуне зовнішній запит. Більш складний приклад виходить при використанні UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

Результат цього запиту:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Тепер, було б чудово, якби оптимізатор бази даних виконав замість цього запиту такий:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

Тобто, щоб він проштовхнув предикат у похідну таблицю, а звідти в два підзапити UNION ALL , оскільки, зрештою, у нас є індекс як на стовпчику ACTOR.LAST_NAME , так і на CUSTOMER.LAST_NAME . Знову ж таки, це перетворення, можливо, ґрунтується на оцінці вартості в більшості баз даних, але я все ж таки вважаю, що це не становить складнощів, оскільки, за будь-якого алгоритму, практично завжди краще знизити кількість оброблених кортежів якомога раніше. Якщо вам відомий випадок, коли таке перетворення виявиться поганою ідеєю – буду радий вашим коментарям! Мені було б дуже цікаво. Отже, які з наших баз даних вміють це робити? (І будь ласка, це ж так просто і так важливо, нехай відповідь буде: все)

DB2

Проста похідна таблиця Так

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Похідна таблиця з UNION Теж так:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Також, в обох випадках, похідна таблиця (подання) була виключена з плану як фактично не потрібна.

MySQL

Проста похідна таблиця Так

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

Використовується звичайний доступ до первинного ключа за константним значенням. Похідна таблиця з UNION Упс, ні.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Перетворення вручну призводить до плану:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Це серйозна проблема у разі використання складних вкладених запитів у MySQL!

Oracle

Проста похідна таблиця Так, працює.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

І кількість рівнів вкладеності було зменшено. Похідна таблиця з UNION Теж працює:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Однак, без зменшення кількості рівнів вкладеності. Id=1 "View" показує, що похідна таблиця нікуди не поділася. У разі це не велика проблема, просто, можливо, невеликі додаткові витрати.

PostgreSQL

Проста похідна таблиця Так, працює:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Зверніть увагу, однак, що PostgreSQL іноді навіть не використовує первинного ключа для пошуку окремого рядка, а переглядає всю таблицю. В даному випадку 200 рядків × 25 байт на рядок ("ширина") поміщаються в одному блоці, так що який сенс возитися з читанням індексу, хіба що генерувати зайві операції вводу/виводу для звернення до такої маленької таблиці? Похідна таблиця з UNION Так, теж працює:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Знову ж таки, індекс по стовпцю ACTOR.LAST_NAME не використовується, а індекс по стовпцю CUSTOMER.LAST_NAME – використовується, оскільки таблиця CUSTOMER набагато більша.

SQL Server

Проста похідна таблиця Так, працює

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

Похідна таблиця з UNION Теж працює.

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Резюме

Мої надії не справдабося. СУБД MySQL 8.0.2 поки не повністю підтримує цю просту оптимізацію. Усі інші, щоправда, підтримують.

Висновок

Поданий тут список далеко не повний. Існує безліч інших простих SQL-перетворень, які не представляють (або не повинні представляти) складнощів для реалізації базами даних, ще до того, як візьметься за справу вартісний оптимізатор. Вони усувають [для бази даних] непотрібну, додаткову роботу ( на відміну непотрібної, обов'язкової роботи, про яку вже писав ). Це важливі інструменти для того, щоб:

1. Дурні помилки [розробника] не впливали на продуктивність. Помилки є неминучими, і в міру зростання проекту та ускладнення SQL-запитів, ці помилки можуть накопичуватися, але, як хотілося б сподіватися, без будь-якого ефекту.


2. Надати можливість перевикористання складних блоків, наприклад, уявлень та табличних функцій, що допускають вбудовування в батьківські SQL-запити, перетворення, а також часткове видалення або переписування.

Ці можливості є критично важливими для 2-го пункту. Без них було б дуже складно створювати SQL-запити розміром 4000 рядків з нормальною продуктивністю на основі бібліотеки SQL-компонентів, що перевикористовуються. До більшого розчарування користувачів PostgreSQL і MySQL, цим двом популярним баз даних з відкритим вихідним кодом ще дуже далеко до їхніх комерційних конкурентів DB2, Oracle і SQL Server, з яких найкраще себе показала DB2, а Oracle і SQL Server приблизно йдуть ніздрі в ніздрю, трохи відстаючи.