Ускорение запроса MySQL с помощью обратного условия

На службе была поставлена задача ускорить работу одного контроллера веб-приложения, который формировал страницу в среднем в течение 7 секунд. Как не раз писалось умными людьми (Дональд Кнут, Мартин Фаулер, Карлос Буэно), при оптимизации важно не делать предположения о причинах медленной работы программы, а производить замеры в среде, для которой осуществляется оптимизация. Поэтому было выполнено профилирование работы проблемного контроллера на боевом сервере и все вызываемые им методы были отсортированы по времени выполнения в порядке убывания. И в самом долго выполняющемся методе был обнаружен «интересный» запрос, подсчитывающий общее число объектов, учитывая условие в связанной таблице:

SELECT COUNT(t1.id)
FROM `table1` t1
LEFT JOIN `table2` t2 ON t2.id=t1.table2_id
LEFT JOIN `table3` t3 ON t3.id=t2.table3_id
LEFT JOIN `table4` t4 ON t4.id=t3.table4_id WHERE t4.some_field != 1

Запрос выполнялся несколько секунд (в среднем 4,5 секунды), что в данном случае было абсолютно неприемлемо. Поэтому было решено начать ускорение открытия страницы именно с него. Далее описаны шаги, которые позволили сократить время работы этого запроса в несколько раз.

Как и положено, начал с анализа плана выполнения запроса, предлагаемого MySQL. Вот вывод команды EXPLAIN (во всех таблицах плана запроса исключен столбец partitions, т.к. он пустой):

На первый взгляд всё было сделано грамотно — план запроса выглядел как стандартный способ отбора записей из связанных таблиц:

1. Для всех внешних ключей имеются и используются индексы (а жаль, часто простое добавление забытых индексов позволяет закрыть задачу быстро и изящно);

2. Сначала по индексу table2_id отбираются записи из таблицы table1, для каждой из них выбирается по одной записи из цепочки связанных таблиц самым быстрым способом (eq_ref) и

3. После этого отобранные строки фильтруются по условию.

Вот только фактическое время выполнения не устраивало.

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

1. Во-первых, возможно, значений в поле table4.some_field равных единице очень мало, и это заставляет MySQl обрабатывать слишком много строк;

2. Во-вторых, эти строки обрабатываются в присоединяемых таблицах в цикле на третьем уровне вложенности, что ещё больше замедляет запрос;

3. В-третьих, используется оператор неравенства, который вынуждает MySQL использовать не самый быстрый тип отбора (range вместо ref).

Для проверки этих предположений сделал запрос только по одной таблице:

Эксперимент подтвердил гипотезу — без связанных таблиц и условий в них запрос исполнялся в среднем 0,01 секунды с использованием сканирования по индексу (Using index). При этом число обрабатываемых строк сопоставимо с числом строк в исходном варианте. Стало понятно, что надо ускорить обработку соединения таблиц с нужным нам условием.

Для этого проверил, какие данные у нас хранятся в поле table4.some_field.

SELECT some_field, COUNT(id)
FROM table4
GROUP BY some_field

Ага, догадка о соотношении значений в поле table4.some_field тоже была верной. Мало того что у нас используется не самое эффективное условие сравнения, так ещё вместо отбора 0,04% числа записей, MySQL вынужден отбирать 99,96% лишних строк (на самом деле меньше, проверяются только связанные строки). Инверсия условия в запросе показала правильность и этого предположения. Вот его EXPLAIN:

Скорость выполнения запроса с инвертированным условием оказалась вполне приемлемой: 0,13 — 0,26 секунды. Как видно из плана выполнения для такой формулировки запроса и для такого набора данных, оптимизатор запросов MySQL сначала резонно отбирает строки из таблицы table4, т.к. по условию some_field = 1 их там очень мало, и уже потом строит цепочку связанных строк из других таблиц (пусть и менее эффективным образом: ref вместо eq_ref). Это ещё раз показало верность предполагаемой причины медленной работы исходного запроса: долгое время связывания присоединённых таблиц по условию. Надо переписать JOIN’ы и условия для связанных таблиц.

Как показывает мой опыт работы с MySQL, несколько простых и быстрых запросов выполняются как правило быстрее, чем один сложный универсальный запрос. Поэтому было бы идеально решить проблему двумя простыми запросами:

/* Находим общее число записей в table1 */
SELECT COUNT(t1.id)
FROM table1 t1

/*
Находим число записей в table1, для которых выполняется условие
в связанной таблице t4.some_field = 1
*/
SELECT COUNT(t1.id)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id=t1.table2_id
LEFT JOIN table3 t3 ON t3.id=t2.table3_id
LEFT JOIN table4 t4 ON t4.id=t3.table4_id
WHERE t4.some_field = 1

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

К сожалению, проблемный запрос к MySQL формируется в приложении программно, и указанное условие может быть лишь одним из многих. Пришлось встраивать найденный ускоренный вариант отбора в формируемый запрос как одну из строк в выражение WHERE.

Это можно было сделать двумя путями:

1. Сначала отобрать table1.id для которых table4.some_field = 1 отдельным запросом в коде программы, и потом передать их условием в WHERE: table1.id NOT IN (… вставить сюда список всех найденных id …)

2. Или не выносить получение table1.id для которых table4.some_field = 1 в отдельный запрос, а реализовать это подазпросом.

Сначала протестировал скорость выполнения обоих вариантов непосредственно в MySQL. Первый выполнялся в среднем 0,35 секунды, второй — около 0,5 секунды. Хотя вариант с двумя простыми запросами работает быстрее, он не понравился тем, что пришлось бы учитывать ограничения на размер пакета, передаваемого в MySQL из программы. А это усложнило бы программный код. Плюс возможные издержки на передачу данных из MySQL в приложение и обратно могли нивелировать преимущество использования простых запросов в 0,15 секунды. Поэтому был сразу реализован второй вариант с подзапросом.

Вот итоговое решение:

В результате проделанной работы время выполнения запроса сократилась в 9 раз (с 4,5 секунд до 0,5 секунды) и проблемная страница стала открываться, по мнению пользователей, практически мгновенно (на самом деле 1-2 секунды), и дальнейшая оптимизация не потребовалась. Плюс, поскольку это условие формируется программно и для других таблиц, внесённые изменения ускорили работу многих других запросов и улучшили отзывчивость всего веб-приложения в целом.