На ревью схемы обнаружен индекс, ускоряющий один запрос чтения в таблице с частыми вставками. Как определить, оправдывает ли выигрыш от индекса его стоимость?
Индекс оправдан, если измеренное сокращение стоимости критичных чтений превышает дополнительную стоимость записи, хранения и обслуживания индекса. Решение принимают по реальному профилю нагрузки: частоте запросов, их задержке, селективности условий и влиянию индекса на вставки, обновления и удаления.
Нельзя оценивать индекс только по тому, что один запрос стал быстрее. Он может заметно увеличить задержку записи, занять память и дисковое пространство, а оптимизатор может не использовать его при низкой селективности.
Индексы появились как способ не просматривать всю таблицу при каждом поиске. По мере роста объёма данных полный просмотр становится слишком дорогим, поэтому отдельная структура хранит данные, необходимые для быстрого поиска строк по определённым признакам.
Эта оптимизация переносит часть стоимости с чтения на изменение данных. При вставке, обновлении индексируемого поля или удалении строки индекс тоже должен быть изменён. Поэтому индекс всегда является компромиссом между скоростью чтения и стоимостью записи.
Рассмотрим таблицу, в которую постоянно поступают события, и редкий административный запрос, ищущий записи по точному значению атрибута. Индекс может ускорить этот запрос, но каждая новая запись потребует обновления дополнительной структуры.
Основные риски неверного решения:
Оценивать нужно не среднюю скорость одного запроса, а влияние на целевые показатели всей системы: задержки чтения и записи, пропускную способность, стоимость хранения и устойчивость под пиковыми нагрузками.
Сначала описывают рабочую нагрузку: какие запросы выполняются, как часто, какие поля участвуют в фильтрации, сортировке и соединениях, сколько строк обычно возвращается. Затем сравнивают несколько вариантов на данных, близких к производственным, с использованием плана выполнения и измерений под конкурентной нагрузкой.
Селективность показывает, насколько хорошо условие отбирает небольшую долю строк. Индекс обычно полезнее для поиска нескольких строк среди миллионов, чем для условия, совпадающего с большой частью таблицы. Однако окончательное решение зависит также от размера строк, стоимости полного просмотра, расположения данных и возможностей конкретной СУБД.
При чтении индекс позволяет найти подходящие ключи и ссылки на строки, не просматривая всю таблицу. Если для выдачи нужны дополнительные поля, системе может потребоваться отдельное обращение к таблице для каждой найденной строки. Поэтому индекс иногда проектируют так, чтобы он покрывал часто выполняемый запрос, но покрывающий индекс крупнее и дороже в обслуживании.
Для составного индекса важен порядок полей. В B-tree-подобных индексах порядок обычно определяет, для каких сочетаний фильтров и сортировок структура будет эффективна; индекс по полям в одном порядке не равнозначен индексу с обратным порядком. Конкретное поведение нужно проверять по документации и плану выполнения используемой СУБД.
Стоимость индекса включает несколько компонентов:
Практический алгоритм такой:
Компромисс часто улучшают частичным индексом, если важна только подгруппа строк, или составным индексом, который одновременно поддерживает фильтрацию и сортировку. Но такие варианты зависят от возможностей СУБД и конкретных шаблонов запросов; индекс нельзя проектировать только по названию поля.
Индекс не заменяет изменение модели данных. Если запрос системно строит тяжёлую аналитику по большому потоку событий, лучше рассмотреть отдельную проекцию, агрегаты или специализированное аналитическое хранилище. Это увеличивает архитектурную сложность и требования к согласованности, зато изолирует аналитическую нагрузку от транзакционной записи.
Сервис принимает большое количество событий, а операторы периодически ищут события конкретного типа за интервал времени. Команда предложила отдельные индексы по типу и времени, но таблица уже испытывала высокую нагрузку на вставку.
Были рассмотрены варианты. Отказ от индекса сохранял максимальную скорость записи, но поиск приводил к дорогому просмотру большого диапазона. Два отдельных индекса давали оптимизатору больше вариантов, однако не гарантировали эффективное сочетание фильтра и временного диапазона и увеличивали стоимость записи. Составной индекс по типу и времени лучше соответствовал основному шаблону запроса, но был полезен только при подходящем порядке полей и распределении значений.
Выбранный вариант проверили на копии производственного распределения данных: сравнили задержки поиска, вставки и обновления при обычной и пиковой конкуренции. Индекс оставили только после того, как измерения подтвердили существенное улучшение критичного поиска без неприемлемой регрессии записи; дополнительно настроили наблюдение за частотой использования индекса и изменением плана запросов.
Результат такого решения — не просто более быстрый один запрос, а контролируемый баланс между операционной ценностью поиска и стоимостью записи. Если со временем доля поиска уменьшится или распределение данных изменится, индекс нужно пересмотреть, а не считать его постоянной частью схемы.
1. Может ли индекс существовать, но не использоваться?
Да. Оптимизатор сравнивает стоимость разных планов, а не обязан использовать каждый подходящий индекс. Если условие возвращает большую долю таблицы, полный просмотр может оказаться дешевле, чем чтение индекса и последующее обращение к множеству строк.
Индекс также может не применяться из-за преобразования типа, функции над индексируемым полем, неподходящего шаблона поиска, неверного порядка полей в составном индексе или устаревшей статистики. Поэтому наличие индекса проверяют по фактическому плану выполнения и актуальности статистики, а не по определению схемы.
2. Почему два отдельных индекса не всегда заменяют один составной?
Отдельные индексы по двум полям могут помочь найти кандидатов по каждому условию, но СУБД может выбрать только один из них, объединить результаты с дополнительными затратами или предпочесть полный просмотр. Составной индекс заранее организует ключи с учётом совместного шаблона доступа.
Составной индекс особенно важен, когда запрос одновременно фильтрует по нескольким полям и сортирует результат. Но он не универсален: индекс, оптимальный для одного порядка фильтров и сортировки, может плохо подходить для другого. Порядок полей выбирают по наиболее частым запросам и проверяют измерениями.
3. Когда индекс следует удалить, даже если он иногда ускоряет запрос?
Индекс стоит удалить или заменить, если его редкая польза не компенсирует постоянную стоимость записи, хранения и обслуживания. Например, административный запрос может выполняться несколько раз в месяц, тогда как индекс обновляется при каждой записи в высоконагруженную таблицу.
Перед удалением анализируют фактическое использование за достаточно длинный период, сезонные сценарии и критичность ускоряемого запроса. Безопаснее сначала проверить отсутствие зависимости на тестовой или контролируемой копии и наблюдать за задержками после изменения. Если запрос важен, альтернативой может быть отдельная копия данных или специализированная проекция, но это добавляет задержку обновления и сложность согласования.