На службе была поставлена задача ускорить работу одного контроллера веб-приложения, который формировал страницу в среднем в течение 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 секунды), и дальнейшая оптимизация не потребовалась. Плюс, поскольку это условие формируется программно и для других таблиц, внесённые изменения ускорили работу многих других запросов и улучшили отзывчивость всего веб-приложения в целом.
Комментарии (13)
positroid
08.11.2024 13:49У меня был похожий кейс, но с 2 таблицами, EXPLAIN'ом выяснилось что MySQL 5.7 строит запрос с eq_ref, а MySQL 5.6 берет ref и работает дольше на порядок. Такую разницу давали новые значения optimizer_switch в 5.7, которых не было в 5.6 (вроде favor_range_scan и condition_fanout_filter).
Быстрое решение (до обновления версии) внезапно нашел ChatGPT, который предложил заменить INNER_JOIN на STRAIGHT_JOIN (про этот оператор до этого момента ничего не знал) - и это помогло, EXPLAIN переключился на eq_ref индекс и начал отрабатывать на порядок быстрее.
Другой тип джойна насколько я понял заставляет отключить оптимизатор в части изменения порядка подключения таблиц при объединении и строго использовать тот порядок, что был записан в запросе.
TarMax
08.11.2024 13:49Спасибо. Интересная статья. А какой тип индекса на поле по которому фильтр идёт ? Если там сбалансированное дерево (в постгресе это дефолт), то не совсем понял, как как изменение != на = помогло. Ведь в сбалансированном дереве нам достаточно просто найти 1 и взять множество значений либо слева, либо справа. Далее сделать мерж найденного множества значений с тройным джоином. Это должно всё быстро отрабатывать
JustMisha Автор
08.11.2024 13:49Там обычный B-Tree индекс. Думаю дело просто в подавляющей разнице количества записей с 0 и 1. В обоих случаях используется отбор по индексу, просто число записей с 1 очень мало, поэтому и получилось такое ускорение. И даже действительно медленный вариант обора NOT IN не испортил общий результат оптимизации.
Dadadam999
08.11.2024 13:49То что несколько маленьких запросов выполняется быстрее, чем один быстрый правда. Сам заметил это давно.
Так же, как и тот факт (к теме не относится), что в mysql довольно кривые транзакции и одно время их не рекомендовали использовать сами создатели.
Статья интересная, автору спасибо.
antonkrechetov
08.11.2024 13:49Неудобно читать, потому что в разных запросах разные названия таблиц (t1/table1). Также в самом первом EXPLAIN-е два WHERE - это опечатка?
Что касается запроса:
Как заметили выше, левые джоины там не нужны.
Структура БД слегка подозрительная. table1 ссылается на table2, table2 на table3, table3 на table4 по первичному ключу. Кажется, что в такой ситуации в каждой последующей таблице должно быть не больше записей, чем в предыдущей, но из explain-ов видно, что в table1 проверяется 475k записей, а в table4 их 22M. Откуда лишние записи? Не стоит ли разделить какую-то из таблиц на несколько или просто почистить?
Необязательно писать в селекте именно ту таблицу, из которой нужно выбрать данные. Например, если, скажем, в какой-нибудь из таблиц в цепочке мало записей, можно попробовать джойнить остальные таблицы к ней.
JustMisha Автор
08.11.2024 13:49Также в самом первом EXPLAIN-е два WHERE - это опечатка?
Да, опечатка. Исправил, спасибо.
Структура БД слегка подозрительная. table1 ссылается на table2, table2 на table3, table3 на table4 по первичному ключу. Кажется, что в такой ситуации в каждой последующей таблице должно быть не больше записей, чем в предыдущей, но из explain-ов видно, что в table1 проверяется 475k записей, а в table4 их 22M. Откуда лишние записи? Не стоит ли разделить какую-то из таблиц на несколько или просто почистить?
На table4 ссылается много разных таблиц, поэтому в ней больше записей, чем в table1.
Необязательно писать в селекте именно ту таблицу, из которой нужно выбрать данные. Например, если, скажем, в какой-нибудь из таблиц в цепочке мало записей, можно попробовать джойнить остальные таблицы к ней.
Не очень понял, что предлагается, учитывая описанные в статье ограничения: оптимизированный запрос формируется программно и может являться частью другого запроса.
Akina
С точки зрения сервера, будь у него разум, такой запрос - изощрённое, тонкое издевательство. Вот зачем вы используете LEFT JOIN, если финальный WHERE превращает их все в INNER? Чтобы сервер попыхтел лишнего? Это раз.
Два. Условия some_field != 1 и some_field = 1 не являются обратными. Вернее, являются в случае, если some_field в структуре таблицы объявлено как NOT NULL. А структуры вы как-то привести не озаботились.
Три. Вообще-то WHERE NOT IN - самая медленная из возможных реализаций операции вычитания наборов записей. WHERE NOT EXISTS или LEFT JOIN WHERE IS NULL будут гарантированно не медленнее, а скорее всего быстрее. А если версия позволяет, то доступен ещё и EXCEPT.
И последнее. Поле с именем id в подавляющем большинстве случаев первичный индекс, как правило автоинкрементный. И, судя по использованию PRIMARY в планах, так оно и есть. А если так, то связывание вообще не нужно. Достаточно посчитать отдельно записи в таблице 1, отдельно в подзапросе, а потом тупо вычесть. Результат будет тем же, а геморрою серверу ну куда как меньше.
JustMisha Автор
Да, вы правы. внешнее объединение тут лишнее, сейчас попробовал на сервере и отыгралось еще 0,1 секунды. Спасибо.
Не понял какой рабочий вариант предлагаете.
Вроде написал об этом:
TarMax
Хоть комент и полезный но К чему эта токсичность, не понятно