JavaRush /Java блог /Java Developer /Клёвые оптимизации SQL, не зависящие от стоимостной модел...

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

Статья из группы Java Developer
Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 1 Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 2 Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 3 Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 4
Клёвые оптимизации SQL, не зависящие от стоимостной модели. Часть 5  - 1

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 пока что не полностью поддерживает эту простую оптимизацию. Все остальные, правда, поддерживают.
База данных Проталкивание простой производной таблицы Проталкивание производной таблицы с UNION
DB2 LUW 10.5 Да Да
MySQL 8.0.2 Да Нет
Oracle 12.2.0.1 Да Да
PostgreSQL 9.6 Да Да
SQL Server 2014 Да Да

Заключение

Представленный тут перечень далеко не полон. Существует множество других простых SQL-преобразований, которые не представляют (или не должны представлять) сложностей для реализации базами данных, еще до того, как примется за дело стоимостный оптимизатор. Они устраняют [для базы данных] ненужную, дополнительную работу (в отличие от ненужной, обязательной работы, о которой я уже писал). Это важные инструменты для того, чтобы:
  1. Глупые ошибки [разработчика] не влияли на производительность. Ошибки неизбежны, и по мере роста проекта и усложнения SQL-запросов, эти ошибки могут накапливаться, но, как хотелось бы надеяться, без какого-либо эффекта.

  2. Предоставить возможность переиспользования сложных блоков, например, представлений и табличных функций, допускающих встраивание в родительские SQL-запросы, преобразование, а также частичное удаление или переписывание.
Эти возможности критически важны для 2-го пункта. Без них было бы очень сложно создавать SQL-запросы размером в 4000 строк с нормальной производительностью на основе библиотеки переиспользуемых SQL-компонентов. К вящему разочарованию пользователей PostgreSQL и MySQL, этим двум популярным базам данных с открытым исходным кодом еще очень далеко до их коммерческих конкурентов DB2, Oracle и SQL Server, из которых лучше всего себя показала DB2, а Oracle и SQL Server примерно идут ноздря в ноздрю, чуть отставая.
Комментарии
ЧТОБЫ ПОСМОТРЕТЬ ВСЕ КОММЕНТАРИИ ИЛИ ОСТАВИТЬ КОММЕНТАРИЙ,
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ