Оптимизация слияния производных таблиц

Предыстория

Пользователи «больших» систем баз данных привыкли использовать FROM подзапросы для структурирования своих запросов. Например, если первоначально нужно выбрать города с населением более 10 000 человек, а затем из этих городов выбрать те, что расположены в Германии, можно написать такой SQL:

SELECT * 
FROM 
  (SELECT * FROM City WHERE Population > 10*1000) AS big_city
WHERE 
  big_city.Country='DEU'

Для MySQL использование такого синтаксиса было табуировано. Если запустить EXPLAIN для этого запроса, можно понять почему:

mysql> EXPLAIN SELECT * FROM (SELECT * FROM City WHERE Population > 1*1000) 
  AS big_city WHERE big_city.Country='DEU' ;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 4068 | Using where |
|  2 | DERIVED     | City       | ALL  | Population    | NULL | NULL    | NULL | 4079 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.60 sec)

Он планирует выполнить следующие действия:

Слева направо:

1. Выполнить подзапрос: (SELECT * FROM City WHERE Population > 1*1000), точно так, как он был написан в запросе.
2. Поместить результат подзапроса во временную таблицу.
3. Прочитать обратно и применить условие WHERE из верхнего select, big_city.Country='DEU'

Выполнение такого подзапроса очень неэффективно, потому что высокоселективное условие из родительского select (Country='DEU') не используется при сканировании базовой таблицы City. Мы читаем слишком много записей из таблицы City, а затем должны записать их во временную таблицу и прочитать обратно, прежде чем окончательно отфильтровать их.

Слияние производных таблиц в действии

Если запустить этот запрос в MariaDB/MySQL 5.6, получится следующее:

MariaDB [world]> EXPLAIN SELECT * FROM (SELECT * FROM City WHERE Population > 1*1000) 
  AS big_city WHERE big_city.Country='DEU';
+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys      | key     | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | City  | ref  | Population,Country | Country | 3       | const |   90 | Using index condition; Using where |
+----+-------------+-------+------+--------------------+---------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

Из вышесказанного можно увидеть, что:

1. Вывод содержит только одну строку. Это означает, что подзапрос был слился в основной SELECT.
2. К таблице City осуществляется доступ через индекс по столбцу Country. По всей видимости, условие Country='DEU' было использовано для построения доступа ref к таблице.
3. Запрос будет читать около 90 строк, что значительно лучше, чем 4079 строк чтения плюс 4068 операций чтения/записи временной таблицы, которые были ранее.

Справочная информация

• Производные таблицы (подзапросы в FROM предложении) могут быть слиты со своим родительским select-запросом, если они не содержат группировок, агрегаций или ORDER BY ... LIMIT предложений. Эти требования такие же, как и требования к VIEW для поддержки algorithm=merge.
• Оптимизация включена по умолчанию. Можно отключить ее с помощью:

  set @@optimizer_switch='derived_merge=OFF'
  

• В версиях MySQL и MariaDB, не поддерживающих эту оптимизацию, подзапросы будут выполняться даже при запуске EXPLAIN. Это может привести к известной проблеме (см., например, MySQL Bug #44802) задержек при выполнении EXPLAIN заявок. Начиная с MariaDB 5.3+ и MySQL 5.6+ EXPLAIN команды выполняются мгновенно, независимо от настроек derived_merge.

См. также

• Статьи FAQ: Почему ORDER BY в подзапросе FROM игнорируется?