Выведенная таблица с оптимизацией ключа

Идея

Если выведенная таблица не может быть объединена со своим родительским запросом SELECT, она будет материализована во временной таблице, а затем родительский запрос SELECT будет рассматривать ее как обычную базу данных.

До версии MariaDB 5.3/MySQL 5.6 временная таблица никогда не содержала индексов, и единственный способ чтения записей из нее — полное сканирование таблицы. Начиная с указанных версий сервера, оптимизатор имеет возможность создавать индекс и использовать его для объединения с другими таблицами.

Пример

Рассмотрим запрос: мы хотим найти страны в Европе, в которых проживает более миллиона человек в городах. Это достигается с помощью следующего запроса:

select * 
from
   Country, 
   (select 
       sum(City.Population) as urban_population, 
       City.Country 
    from City 
    group by City.Country 
    having 
    urban_population > 1*1000*1000
   ) as cities_in_country
where 
  Country.Code=cities_in_country.Country and Country.Continent='Europe';

Вывод EXPLAIN для него будет показан:

+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys     | key       | key_len | ref                | rows | Extra                           |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+
|  1 | PRIMARY     | Country    | ref  | PRIMARY,continent | continent | 17      | const              |   60 | Using index condition           |
|  1 | PRIMARY     | <derived2> | ref  | key0              | key0      | 3       | world.Country.Code |   17 |                                 |
|  2 | DERIVED     | City       | ALL  | NULL              | NULL      | NULL    | NULL               | 4079 | Using temporary; Using filesort |
+----+-------------+------------+------+-------------------+-----------+---------+--------------------+------+---------------------------------+

Здесь можно увидеть, что

• к таблице <derived2> обращаются через key0.
• ref столбец показывает world.Country.Code
• если мы проверим это в исходном запросе, мы найдем равенство, которое использовалось для построения доступа к ref: Country.Code=cities_in_country.Country.

Сведения

• Идея оптимизации «выведенная таблица с ключом» заключается в том, чтобы позволить материализованной выведенной таблице иметь один ключ, который используется для объединения с другими таблицами.
• Оптимизация применяется, если выведенная таблица не может быть объединена со своим родительским запросом SELECT,
  • что происходит, когда выведенная таблица не соответствует критериям объединяемого представления
• Оптимизация включена по умолчанию, ее можно отключить следующим образом:

set optimizer_switch='derived_with_keys=off'

См. также

• Оптимизация подзапросов в предложении FROM в руководстве MySQL 5.6
• Что такое MariaDB 5.3
• Карта оптимизаций подзапросов