8.9.1 Controlling Query Plan Evaluation — MySQL 5.7

8.9.1 Управление оценкой плана запроса

Задача оптимизатора запросов — найти оптимальный план для выполнения SQL-запроса. Поскольку разница в производительности между «хорошим» и «плохим» планами может составлять несколько порядков величины (то есть секунды против часов или даже дней), большинство оптимизаторов запросов, включая MySQL, выполняют более или менее исчерпывающий поиск оптимального плана среди всех возможных планов оценки запроса. Для запросов с объединением число возможных планов, исследуемых оптимизатором MySQL, экспоненциально растёт с увеличением числа таблиц, на которые ссылается запрос. Для небольшого числа таблиц (обычно менее 7–10) это не проблема. Однако, при подаче более сложных запросов, время, потраченное на оптимизацию запроса, может легко стать основным узким местом в производительности сервера.

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

Поведение оптимизатора относительно числа оцениваемых планов можно контролировать с помощью двух системных переменных:

Переменная optimizer_prune_level сообщает оптимизатору о пропуске определённых планов на основе оценок количества строк, доступных для каждой таблицы. Наш опыт показывает, что этот вид «образованной догадки» редко пропускает оптимальные планы и может значительно сократить время компиляции запросов. Именно поэтому этот параметр по умолчанию включён (optimizer_prune_level=1). Однако, если вы считаете, что оптимизатор пропустил лучший план запроса, этот параметр можно выключить (optimizer_prune_level=0) с риском, что компиляция запроса может занять гораздо больше времени. Обратите внимание, что даже с использованием этой эвристики, оптимизатор по-прежнему исследует приблизительно экспоненциальное количество планов.
Переменная optimizer_search_depth указывает, насколько далеко в «будущее» каждого неполного плана должен заглянуть оптимизатор, чтобы оценить, следует ли его дальше расширять. Более низкие значения optimizer_search_depth могут привести к значительно меньшему времени компиляции запросов на несколько порядков величины. Например, запросы с 12, 13 или более таблицами могут легко потребовать часов и даже дней для компиляции, если optimizer_search_depth близко к числу таблиц в запросе. В то же время, если запросы скомпилированы с optimizer_search_depth, равным 3 или 4, оптимизатор может скомпилировать запрос за меньшее чем минуту для того же запроса. Если вы не уверены, какое разумное значение для optimizer_search_depth, эту переменную можно установить в 0, чтобы оптимизатор определил значение автоматически.