Ускорение запроса 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, т.к. он пустой):
EXPLAIN | ||||||||||
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table1 | index | table2_id, table2_id_2, table2_id_3 | table2_id | 5 | 474781 | 100,00 | Using index | |
1 | SIMPLE | table2 | eq_ref | PRIMARY, idx_table2_si_id | PRIMARY | 4 | table1.table2_id | 1 | 100,00 | |
1 | SIMPLE | table3 | eq_ref | PRIMARY, idx_table3_i_id | PRIMARY | 4 | table2.table3_id | 1 | 100,00 | |
1 | SIMPLE | table4 | eq_ref | PRIMARY, some_field | PRIMARY | 4 | table3.table4_id | 1 | 50,00 | Using where |
На первый взгляд всё было сделано грамотно — план запроса выглядел как стандартный способ отбора записей из связанных таблиц:
Для всех внешних ключей имеются и используются индексы (а жаль, часто простое добавление забытых индексов позволяет закрыть задачу быстро и изящно);
Сначала по индексу table2_id отбираются записи из таблицы table1, для каждой из них выбирается по одной записи из цепочки связанных таблиц самым быстрым способом (eq_ref) и
После этого отобранные строки фильтруются по условию.
Вот только фактическое время выполнения не устраивало.
После более пристального изучения появились мысли о том, что может тормозить запрос:
Во-первых, возможно, значений в поле table4.some_field равных единице очень мало, и это заставляет MySQl обрабатывать слишком много строк;
Во-вторых, эти строки обрабатываются в присоединяемых таблицах в цикле на третьем уровне вложенности, что ещё больше замедляет запрос;
В-третьих, используется оператор неравенства, который вынуждает MySQL использовать не самый быстрый тип отбора (range вместо ref).
Для проверки этих предположений сделал запрос только по одной таблице:
EXPLAIN | ||||||||||
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table1 | index | /*любой вторичный индекс*/ | 1 | 475229 | 100,00 | Using index |
Эксперимент подтвердил гипотезу — без связанных таблиц и условий в них запрос исполнялся в среднем 0,01 секунды с использованием сканирования по индексу (Using index). При этом число обрабатываемых строк сопоставимо с числом строк в исходном варианте. Стало понятно, что надо ускорить обработку соединения таблиц с нужным нам условием.
Для этого проверил, какие данные у нас хранятся в поле table4.some_field.
SELECT some_field, COUNT (id)
FROM table4
GROUP BY some_field
some_field | COUNT (id) |
---|---|
0 | 22037468 |
1 | 8848 |
Ага, догадка о соотношении значений в поле table4.some_field тоже была верной. Мало того что у нас используется не самое эффективное условие сравнения, так ещё вместо отбора 0,04% числа записей, MySQL вынужден отбирать 99,96% лишних строк (на самом деле меньше, проверяются только связанные строки). Инверсия условия в запросе показала правильность и этого предположения. Вот его EXPLAIN:
EXPLAIN | ||||||||||
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | table4 | ref | PRIMARY, some_field | some_field | 2 | const | 15406 | 100,00 | Using index |
1 | SIMPLE | table3 | ref | PRIMARY, idx_table3_i_id | idx_table3_i_id | 5 | table4.id | 1 | 100,00 | Using index |
1 | SIMPLE | table2 | ref | PRIMARY, idx_table2_si_id | idx_table2_si_id | 5 | table3.id | 1 | 100,00 | Using where; Using index |
1 | SIMPLE | table1 | ref | table2_id, table2_id_2, table2_id_3 | table2_id | 5 | table2.id | 1 | 100,00 | Using index |
Скорость выполнения запроса с инвертированным условием оказалась вполне приемлемой: 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.
Это можно было сделать двумя путями:
Сначала отобрать table1.id для которых table4.some_field = 1 отдельным запросом в коде программы, и потом передать их условием в WHERE: table1.id NOT IN (… вставить сюда список всех найденных id …)
Или не выносить получение table1.id для которых table4.some_field = 1 в отдельный запрос, а реализовать это подазпросом.
Сначала протестировал скорость выполнения обоих вариантов непосредственно в MySQL. Первый выполнялся в среднем 0,35 секунды, второй — около 0,5 секунды. Хотя вариант с двумя простыми запросами работает быстрее, он не понравился тем, что пришлось бы учитывать ограничения на размер пакета, передаваемого в MySQL из программы. А это усложнило бы программный код. Плюс возможные издержки на передачу данных из MySQL в приложение и обратно могли нивелировать преимущество использования простых запросов в 0,15 секунды. Поэтому был сразу реализован второй вариант с подзапросом.
Вот итоговое решение:
EXPLAIN | ||||||||||
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | table1 | index | PRIMARY, /*большой список вторичных индексов*/ | /*MySQL использует последний вторичный индекс в списке*/ | 1 | 474881 | 100,00 | Using where; Using index | |
2 | SUBQUERY | table4 | ref | PRIMARY, some_field | some_field | 2 | const | 15406 | 100,00 | Using index |
2 | SUBQUERY | table3 | ref | PRIMARY, idx_table3_i_id | idx_table3_i_id | 5 | table4.id | 1 | 100,00 | Using index |
2 | SUBQUERY | table2 | ref | PRIMARY, idx_table2_si_id | idx_table2_si_id | 5 | table3.id | 1 | 100,00 | Using where; Using index |
2 | SUBQUERY | table1 | ref | PRIMARY, table2_id, table2_id_2, table2_id_3 | table2_id | 5 | table2.id | 1 | 100,00 | Using index |
В результате проделанной работы время выполнения запроса сократилась в 9 раз (с 4,5 секунд до 0,5 секунды) и проблемная страница стала открываться, по мнению пользователей, практически мгновенно (на самом деле 1–2 секунды), и дальнейшая оптимизация не потребовалась. Плюс, поскольку это условие формируется программно и для других таблиц, внесённые изменения ускорили работу многих других запросов и улучшили отзывчивость всего веб-приложения в целом.