Запрос, который вчера отрабатывал мгновенно, сегодня держит страницу пять секунд. Код не менялся. Данных стало больше — но и это не объяснение, а место, откуда начинать.
Тупик: добавить индекс наугад
Первое, что делают почти все, — вешают индекс на колонку из условия. Иногда помогает, и это худший исход: помогло случайно, механика осталась непонятой, а в базе появился объект, который надо поддерживать при каждой записи.
Чаще не помогает, и тогда индексов становится два, потом три. Через полгода у таблицы их восемь, вставка замедлилась вдвое, а исходный запрос по-прежнему медленный. Так выглядит лечение симптома: каждое отдельное действие выглядит разумным, сумма — нет.
Причина, по которой это не работает, простая. Индекс ускоряет поиск строк, но медленный запрос не всегда занят поиском строк. Он может быть занят сортировкой, соединением таблиц, чтением с диска того, что не поместилось в память, или пересчётом одного и того же подзапроса для каждой строки. Индекс лечит первое и никак не влияет на остальное.
Начинать надо с плана, а не с догадки
У базы есть готовый ответ на вопрос «что ты делала эти пять секунд» — план выполнения. Не предполагаемый план, а фактический: с реальным временем каждого шага и реальным числом строк.
Читать его надо снизу вверх и искать три вещи.
Расхождение оценки и факта. База предполагала получить сотню строк, а получила миллион. Это не проблема плана — это проблема статистики: база приняла решение о способе выполнения, опираясь на устаревшее представление о данных. Дальше всё пошло не так по цепочке, и любой индекс тут бессилен.
Шаг, съедающий львиную долю времени. В плане почти всегда есть одна операция, которая стоит больше, чем все остальные вместе. Оптимизировать имеет смысл её, а не то, что бросается в глаза первым.
Повторение. Один и тот же подзапрос, выполненный столько раз, сколько строк во внешнем запросе. В плане это видно по числу циклов. Такой запрос не ускоряется индексом — он переписывается.
Четыре причины, которые покрывают почти всё
Устаревшая статистика. База не знает, что данных стало в сто раз больше, и выбирает способ, разумный для прежнего объёма. Самая частая причина внезапного замедления без изменения кода. Лечится обновлением статистики, а не индексом.
Условие, скрытое от индекса. Индекс есть, но не используется, потому что колонка обёрнута функцией, приведена к другому типу или сравнивается с выражением, которое база не может разложить. Формально индекс на месте, фактически он невидим.
Соединение, разворачивающее данные. Две таблицы соединяются по условию, которое не так избирательно, как казалось автору, и промежуточный результат получается на порядки больше конечного. Всё время уходит на материал, который потом отбросят.
Чтение мимо памяти. Рабочий набор перестал помещаться в память, и то, что читалось из кеша, стало читаться с диска. Кривая при этом не плавная: пока помещалось — быстро, перестало помещаться — медленно. Отсюда ощущение, что «сломалось за ночь».
Пятая причина встречается реже, но узнаётся тяжелее всего: запрос быстрый, а страница медленная, потому что запросов не один. Список из двадцати элементов, для каждого из которых догружаются связанные данные, даёт двадцать один поход в базу вместо двух. По отдельности все они мгновенные, и в журнале медленных запросов их нет — они туда не попадают по определению. Ищется это не в базе, а в трассировке запроса к приложению: там видно количество, а не длительность.
Что делать по порядку
Порядок выстроен от дешёвого к дорогому, и нарушать его невыгодно.
Сначала обновить статистику и повторить замер. Это минуты работы и объясняет заметную долю внезапных замедлений.
Затем снять фактический план и найти в нём шаг с наибольшим временем. Дальше работать только с ним. Соблазн поправить всё сразу велик, но тогда непонятно, что именно помогло.
Затем проверить, что условие вообще может воспользоваться индексом: убрать функцию с колонки, привести типы, вынести вычисление в сторону значения, а не колонки.
И только потом добавлять индекс — под конкретный шаг плана, а не под колонку из условия. У нового индекса есть цена: он замедляет запись и занимает память, поэтому его заводят под доказанную проблему.
Отдельно стоит проверить, нужен ли запрос в таком виде. Регулярно оказывается, что страница просит все колонки, а показывает три; или запрашивает тысячу строк, а рисует двадцать. Самый быстрый запрос — тот, который не выполняется.
И проверить, нужен ли он именно сейчас. Часть тяжёлых запросов обслуживает то, что читатель увидит через секунду после загрузки, а часть — то, что он, возможно, не увидит вовсе. Перенос второй части на момент, когда данные реально понадобятся, даёт больший выигрыш, чем любая оптимизация самого запроса, и не требует трогать базу.
Замерять нужно на данных того же порядка, что в бою. Запрос, отлаженный на тысяче строк, ведёт себя иначе на миллионе, и дело не в пропорции: база при разных объёмах выбирает разные способы выполнения. Оптимизация, проверенная на маленькой копии, регулярно оказывается бесполезной или вредной на большой.
Как понять, что вы закончили
Критерий не «стало быстрее», а «понятно, почему стало быстрее». Если ускорение случилось после четырёх изменений подряд, вы не знаете, какое сработало, и следующий раз начнётся с нуля.
Практический признак завершённости: вы можете назвать шаг плана, который был дорогим, и сказать, что именно его удешевило. Если такой фразы нет, работа не закончена, даже если время запроса упало.
Полезно записать это в задачу или в комментарий рядом с запросом. Через полгода данные вырастут снова, и человек — возможно, вы — начнёт с того же места. Записанная причина экономит ему весь путь.
Позиция редакции
Мы считаем, что индекс должен быть последним действием, а не первым. Порядок «статистика — план — условие — индекс» дешевле и оставляет после себя понимание, а не набор объектов в базе.
Мы неправы, если запрос выполняется редко, а разбираться некогда: для единичного отчёта, который гоняют раз в квартал, наугад повешенный индекс — рациональная экономия времени. Граница проходит по частоте: то, что выполняется в каждом запросе пользователя, разбирают до причины, то, что раз в квартал, — можно и заткнуть.