Эта оптимизация не вполне тут уместна, ведь нельзя утверждать, что она совершенно не основывается на стоимостной модели. Но поскольку я не могу придумать ни одной причины, почему оптимизатору не следует проталкивать предикаты в производные таблицы, то укажу это тут, вместе с остальными, не-стоимостными оптимизациями.
Рассмотрим запрос:
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 намного больше.
Мои надежды не сбылись. СУБД MySQL 8.0.2 пока что не полностью поддерживает эту простую оптимизацию. Все остальные, правда, поддерживают.
База данных
Проталкивание простой производной таблицы
Проталкивание производной таблицы с UNION
DB2 LUW 10.5
Да
Да
MySQL 8.0.2
Да
Нет
Oracle 12.2.0.1
Да
Да
PostgreSQL 9.6
Да
Да
SQL Server 2014
Да
Да
Заключение
Представленный тут перечень далеко не полон. Существует множество других простых SQL-преобразований, которые не представляют (или не должны представлять) сложностей для реализации базами данных, еще до того, как примется за дело стоимостный оптимизатор.
Они устраняют [для базы данных] ненужную, дополнительную работу (в отличие от ненужной, обязательной работы, о которой я уже писал).
Это важные инструменты для того, чтобы:
Глупые ошибки [разработчика] не влияли на производительность. Ошибки неизбежны, и по мере роста проекта и усложнения SQL-запросов, эти ошибки могут накапливаться, но, как хотелось бы надеяться, без какого-либо эффекта.
Предоставить возможность переиспользования сложных блоков, например, представлений и табличных функций, допускающих встраивание в родительские SQL-запросы, преобразование, а также частичное удаление или переписывание.
Эти возможности критически важны для 2-го пункта. Без них было бы очень сложно создавать SQL-запросы размером в 4000 строк с нормальной производительностью на основе библиотеки переиспользуемых SQL-компонентов.
К вящему разочарованию пользователей PostgreSQL и MySQL, этим двум популярным базам данных с открытым исходным кодом еще очень далеко до их коммерческих конкурентов DB2, Oracle и SQL Server, из которых лучше всего себя показала DB2, а Oracle и SQL Server примерно идут ноздря в ноздрю, чуть отставая.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ