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

d6f46d7206f32b0f0baa9103d3c7df77

На службе была поставлена задача ускорить работу одного контроллера веб-приложения, который формировал страницу в среднем в течение 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
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
WHERE table4.some_field!= 1

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

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

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

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

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

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

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

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

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

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

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

EXPLAIN
SELECT COUNT (`table1`.`id`)
FROM `table1`

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
SELECT COUNT (`table1`.`id`)
FROM `table1`
LEFT JOIN `table2` ON `table2`.`id` = `table1`.`table2_id`
LEFT JOIN `table3` ON `table3`.`id` = `table2`.`table3_id`
LEFT JOIN `table4` ON `table4`.`id` = `table3`.`table4_id`
WHERE `table4`.`some_field` = 1

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.

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

  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 секунды. Поэтому был сразу реализован второй вариант с подзапросом.

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

EXPLAIN
SELECT COUNT (t1.id)
FROM `table1` t1
WHERE t1.id NOT IN (
SELECT 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
)

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 секунды), и дальнейшая оптимизация не потребовалась. Плюс, поскольку это условие формируется программно и для других таблиц, внесённые изменения ускорили работу многих других запросов и улучшили отзывчивость всего веб-приложения в целом.

© Habrahabr.ru