Оптимизация вытягивания таблиц

Вытягивание таблиц — это оптимизация для подзапросов с полусоединением.

Идея вытягивания таблиц

Иногда подзапрос можно переписать как соединение. Например:

select *
from City 
where City.Country in (select Country.Code
                       from Country 
                       where Country.Population < 100*1000);

Если известно, что не более одной страны может иметь заданное значение Country.Code (это можно определить, если в таблице Country есть первичный ключ или уникальный индекс по этому столбцу), то этот запрос можно переписать как:

select City.* 
from 
  City, Country 
where
 City.Country=Country.Code AND Country.Population < 100*1000;

Вытягивание таблиц в действии

Если выполнить EXPLAIN для запроса выше в MySQL 5.1-5.6 или MariaDB 5.1-5.2, получим такой план:

MySQL [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
| id | select_type        | table   | type            | possible_keys      | key     | key_len | ref  | rows | Extra       |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | City    | ALL             | NULL               | NULL    | NULL    | NULL | 4079 | Using where |
|  2 | DEPENDENT SUBQUERY | Country | unique_subquery | PRIMARY,Population | PRIMARY | 3       | func |    1 | Using where |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Это показывает, что оптимизатор выполнит полное сканирование таблицы City, и для каждого города будет выполнить поиск в таблице Country.

Если выполнить тот же запрос в MariaDB 5.3, получим такой план:

MariaDB [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type | table   | type  | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY     | Country | range | PRIMARY,Population | Population | 4       | NULL               |   37 | Using index condition |
|  1 | PRIMARY     | City    | ref   | Country            | Country    | 3       | world.Country.Code |   18 |                       |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
2 rows in set (0.00 sec)

Интересные моменты:

• Обе таблицы имеют select_type=PRIMARY, и id=1 как если бы они были в одном соединении.
• Таблица `Country` стоит первой, за ней следует таблица `City`.

Действительно, если выполнить EXPLAIN EXTENDED; SHOW WARNINGS, можно увидеть, что подзапрос исчез и был заменён на соединение:

MariaDB [world]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS 
`Name`,`world`.`City`.`Country` AS `Country`,`world`.`City`.`Population` AS 
`Population` 

  
   from `world`.`City` join `world`.`Country` where 


((`world`.`City`.`Country` = `world`.`Country`.`Code`) and (`world`.`Country`.
`Population` < (100 * 1000)))
1 row in set (0.00 sec)

Преобразование подзапроса в соединение позволяет подать соединение в оптимизатор соединений, который может выбрать между двумя возможными порядками соединений:

1. City -> Country
2. Country -> City

по сравнению с единственным вариантом

1. City->Country

который был до оптимизации.

В приведённом выше примере выбор приводит к лучшему плану запроса. Без вытягивания план запроса с подзапросом прочитал бы (4079 + 1*4079)=8158 записей таблиц. С вытягиванием таблиц план соединения прочитал бы (37 + 37 * 18) = 703 строк. Не все чтение строк одинаково, но, как правило, чтение 10 раз меньше записей таблицы происходит быстрее.

Справочная информация по вытягиванию таблиц

• Вытягивание таблиц возможно только в подзапросах с полусоединением.
• Вытягивание таблиц основано на определениях ключей UNIQUE/PRIMARY.
• Выполнение вытягивания таблиц не отменяет возможных планов запросов, поэтому MariaDB всегда будет пытаться вытянуть по максимуму.
• Вытягивание таблиц может вытягивать отдельные таблицы из подзапросов к родительским запросам. Если все таблицы в подзапросе были вынесены, подзапрос (то есть его полусоединение) удаляется полностью.
• Один из распространённых советов по оптимизации MySQL — «Если возможно, перепишите подзапросы как соединения». Вытягивание таблиц делает именно это, поэтому ручные переписывания больше не нужны.

Управление вытягиванием таблиц

Нет отдельного флага @@optimizer_switch для вытягивания таблиц. Вытягивание таблиц можно отключить, отключив все оптимизации полусоединений с помощью команды SET @@optimizer_switch='semijoin=off'.