Подсказки для индекса: Как принудительно задать планы запросов

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

Вы можете изучить план запроса для SELECT, написав EXPLAIN перед оператором. SHOW EXPLAIN отображает вывод выполняемого запроса. В некоторых случаях его вывод может быть ближе к реальности, чем EXPLAIN.

Для следующих запросов мы будем использовать базу данных world в качестве примеров.

Настройка примера базы данных World

Загрузите её с ftp://ftp.askmonty.org/public/world.sql.gz

Установите её с помощью:

mariadb-admin create world
zcat world.sql.gz | ../client/mysql world

или

mariadb-admin create world
gunzip world.sql.gz
../client/mysql world < world.sql

Принудительное задание порядка соединения

Вы можете принудительно задать порядок соединения, используя STRAIGHT_JOIN либо в части SELECT, либо в части JOIN.

Самый простой способ принудительного задания порядка соединения — это расположить таблицы в правильном порядке в FROM-клаузе и использовать SELECT STRAIGHT_JOIN следующим образом:

SELECT STRAIGHT_JOIN SUM(City.Population) FROM Country,City WHERE
City.CountryCode=Country.Code AND Country.HeadOfState="Volodymyr Zelenskyy";

Если вы хотите принудительно задать порядок соединения только для нескольких таблиц, используйте STRAIGHT_JOIN в FROM-клаузе. В этом случае порядок будут заданы только для таблиц, связанных с STRAIGHT_JOIN. Например:

SELECT SUM(City.Population) FROM Country STRAIGHT_JOIN City WHERE
City.CountryCode=Country.Code AND Country.HeadOfState="Volodymyr Zelenskyy";

В обоих случаях Country будет сканирована первой, а для каждой соответствующей страны (в данном случае одна) будут проверяться все строки в City на соответствие. Поскольку соответствующих стран всего одна, это будет быстрее, чем исходный запрос.

Вывод EXPLAIN для указанных случаев:

Это один из немногих случаев, когда ALL приемлем, так как сканирование таблицы Country найдёт только одну соответствующую строку.

Принудительное использование определенного индекса для условия WHERE

В некоторых случаях оптимизатор может выбрать неэффективный индекс или вообще не использовать индекс, даже если теоретически какой-либо индекс мог бы быть использован.

В таких случаях у вас есть возможность либо указать оптимизатору использовать только ограниченный набор индексов, проигнорировать один или несколько индексов, либо принудительно использовать определенный индекс.

USE INDEX: Использование ограниченного набора индексов

Вы можете ограничить набор рассматриваемых индексов с помощью опции USE INDEX.

USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

По умолчанию используется 'FOR JOIN', что означает, что подсказка влияет только на оптимизацию WHERE-клаузы.

USE INDEX используется после имени таблицы в FROM-клаузе.

Пример:

CREATE INDEX Name ON City (Name);
CREATE INDEX CountryCode ON City (Countrycode);
EXPLAIN SELECT Name FROM City USE INDEX (CountryCode)
WHERE name="Helsingborg" AND countrycode="SWE";

Это даст:

Если бы мы не использовали USE INDEX, индекс Name был бы в possible keys.

IGNORE INDEX: Не использовать определенный индекс

Вы можете указать оптимизатору не учитывать определенный индекс с помощью опции IGNORE INDEX.

IGNORE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

Это используется после имени таблицы в FROM-клаузе:

CREATE INDEX Name ON City (Name);
CREATE INDEX CountryCode ON City (Countrycode);
EXPLAIN SELECT Name FROM City IGNORE INDEX (Name)
WHERE name="Helsingborg" AND countrycode="SWE";

Это даст:

Преимущества использования IGNORE_INDEX вместо USE_INDEX в том, что это не отключит новый индекс, который вы можете добавить позже.

Также см. Игнорируемые индексы для возможности указать в определении индекса, что индексы должны игнорироваться.

FORCE INDEX: Принудительное использование индекса

Принудительное использование индекса в основном полезно, когда оптимизатор решает выполнить сканирование таблицы, даже если известно, что использование индекса было бы лучше. (Оптимизатор может решить выполнить сканирование таблицы, даже если доступен индекс, когда он считает, что большинство или все строки будут соответствовать, и он может избежать накладных расходов на использование индекса).

CREATE INDEX Name ON City (Name);
EXPLAIN SELECT Name,CountryCode FROM City FORCE INDEX (Name)
WHERE name>="A" and CountryCode >="A";

Это даст:

FORCE_INDEX работает, рассматривая только указанные индексы (как с USE_INDEX), но дополнительно сообщает оптимизатору считать сканирование таблицы очень дорогостоящим. Однако, если ни один из «принудительных» индексов не может быть использован, сканирование таблицы всё равно будет выполнено.

Префиксы индексов

При использовании подсказок индексов (USE, FORCE или IGNORE INDEX), имя индекса может также быть недвусмысленным префиксом имени индекса.

Принудительное использование индекса для ORDER BY или GROUP BY

Оптимизатор будет пытаться использовать индексы для разрешения ORDER BY и GROUP BY.

Вы можете использовать USE INDEX, IGNORE INDEX и FORCE INDEX, как в WHERE-клаузе выше, чтобы гарантировать, что используется определённый индекс:

USE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

Это используется после имени таблицы в FROM-клаузе.

Пример:

CREATE INDEX Name ON City (Name);
EXPLAIN SELECT Name,Count(*) FROM City
FORCE INDEX FOR GROUP BY (Name)
WHERE population >= 10000000 GROUP BY Name;

Это даст:

Без опции FORCE INDEX в столбце «Extra» было бы 'Using where; Using temporary; Using filesort', что означает, что оптимизатор создал бы временную таблицу и отсортировал бы её.

Помощь оптимизатору в оптимизации GROUP BY и ORDER BY

Оптимизатор использует несколько стратегий для оптимизации GROUP BY и ORDER BY:

• Разрешение с помощью индекса:
  • Сканирование таблицы в порядке индекса и вывод данных по мере продвижения. (Это работает только если ORDER BY / GROUP BY может быть разрешено индексом после выполнения постоянной подстановки).

• Filesort:
  • Сканирование таблицы для сортировки и сбор ключей сортировки во временный файл.
  • Сортировка ключей + ссылка на строку (с filesort)
  • Сканирование таблицы в отсортированном порядке

• Использование временной таблицы для ORDER BY:
  • Создание временной (в памяти) таблицы для данных, которые нужно отсортировать. (Если она становится больше, чем max_heap_table_size или содержит BLOB-данные, используется дисковая таблица Aria или MyISAM)
  • Сортировка ключей + ссылка на строку (с filesort)
  • Сканирование таблицы в отсортированном порядке

Временная таблица всегда будет использоваться, если поля, которые будут отсортированы, не из первой таблицы в порядке JOIN.

• Использование временной таблицы для GROUP BY:
  • Создание временной таблицы для хранения результатов GROUP BY с индексом, соответствующим полям GROUP BY.
  • Вывод строки результата
  • Если в временной таблице существует строка с ключом GROUP BY, добавьте новую строку результата в неё. Если нет, создайте новую строку.
  • Перед отправкой результатов пользователю отсортируйте строки с filesort, чтобы получить результаты в порядке GROUP BY.

Принудительное использование/отключение временных таблиц для GROUP BY:

Использование таблицы в памяти (как описано выше) обычно является самым быстрым вариантом для GROUP BY, если результат небольшой. Это не оптимально, если результат очень большой. Вы можете сообщить об этом оптимизатору, используя SELECT SQL_SMALL_RESULT или SELECT SQL_BIG_RESULT.

Например:

EXPLAIN SELECT SQL_SMALL_RESULT Name,Count(*) AS Cities FROM City GROUP BY Name HAVING Cities > 2;

даёт:

в то время как:

EXPLAIN SELECT SQL_BIG_RESULT Name,Count(*) AS Cities FROM City
GROUP BY Name HAVING Cities > 2;

даёт:

Разница заключается в том, что с SQL_SMALL_RESULT используется временная таблица.

Принудительное использование временных таблиц

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

Это можно сделать с помощью опции SQL_BUFFER_RESULT:

CREATE INDEX Name ON City (Name);
EXPLAIN SELECT SQL_BUFFER_RESULT Name,Count(*) AS Cities FROM City
GROUP BY Name HAVING Cities > 2;

Это дает:

Без SQL_BUFFER_RESULT, вышеуказанный запрос не использовал бы временную таблицу для набора результатов.

Переключатель оптимизатора

В MariaDB 5.3 мы добавили переключатель оптимизатора, который позволяет указать, какие алгоритмы будут рассматриваться при оптимизации запроса.

Дополнительную информацию о различных используемых алгоритмах см. в разделе оптимизатор.

См. также

• FORCE INDEX
• USE INDEX
• IGNORE INDEX
• GROUP BY
• Игнорируемые индексы