Когда индексировать таблицу: Практическое руководство для аналитиков

1. Что такое индекс на самом деле

Перед пояснением: в вашей таблице 500 000 строк, и вы запрашиваете одного клиента. Скажите, сколько строк должен просмотреть движок.

Все, если только что-то не подскажет, где остановиться. Таблица представляет собой кучу строк, расположенных в произвольном порядке, поэтому единственный способ убедиться, что вы нашли все совпадения, — это проверить каждую строку. Это полное сканирование таблицы, и это не ошибка; это единственное правильное решение без дополнительной информации.

Индекс — это дополнительная информация. Это отдельная структура, содержащая значения одного или нескольких столбцов, хранящиеся в отсортированном порядке, с указателем от каждого значения обратно на соответствующую строку. Поскольку оно отсортировано, движок может найти значение так же, как вы находите слово в словаре: не читая каждую страницу, а многократно отбрасывая ту половину, которая не может его содержать.

Сразу следуют два следствия, которые объясняют большую часть этой страницы. Индекс полезен только для охватываемых им столбцов, поэтому индекс для customer_id ничего не делает для запроса о region. И это помогает только в том случае, если запрос задает значение таким образом, чтобы отсортированный порядок мог ответить, поэтому существует пятый раздел.

2. Чтение плана: сканирование с помощью поиска

Вам никогда не придется угадывать, что из этого происходит. Любой движок подскажет вам это, а в SQLite это одно слово перед запросом.

EXPLAIN QUERY PLAN
SELECT COUNT(*), SUM(units*unit_price) FROM orders WHERE customer_id = 41234;

SCAN orders

SCAN означает каждую строку. После добавления индекса в этот столбец тот же запрос сообщает:

SEARCH orders USING INDEX idx_customer (customer_id=?)

SEARCH означает, что он прошел через индекс. Имя индекса находится прямо здесь, и таким образом вы подтверждаете, что тот, который вы добавили, используется, а не другой.

Третья фраза появляется постоянно и заслуживает внимания: COVERING INDEX. Это означает, что каждый столбец, необходимый запросу, находился внутри индекса, поэтому движку вообще не приходилось возвращаться к таблице. Это самый быстрый случай, и именно поэтому индекс, включающий столбец, который вы считаете, может превзойти индекс, включающий только столбец, по которому вы фильтруете.

В Постгрес те же три идеи читаются как Seq Scan, Index Scan и Index Only Scan. Разные слова, одно и то же различие и EXPLAIN ANALYZE там также указано время.

3. Измерение

Вот один и тот же запрос до и после одного индекса, на 500 000 строк, лучший из пяти прогонов.

SELECT COUNT(*), SUM(units*unit_price) FROM orders WHERE customer_id = 41234;

no index    SCAN orders                                        90.9 ms
index       SEARCH orders USING INDEX idx_customer (id=?)       0.2 ms

Результат идентичен: девять строк в сумме составляют 8415 в обоих случаях. Изменился только маршрут. В этом вся суть индекса и причина, по которой люди обращаются к нему в первую очередь.

Диапазон работает таким же образом. Фильтрация месяца из года по датам:

WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'

no index    SCAN orders                                       123.5 ms
index       SEARCH orders USING COVERING INDEX idx_date        3.9 ms

В любом случае 42 470 строк. Обратите внимание, что этот индекс вернулся в качестве покрывающего индекса: запросу требовалась только дата, а дата находится в индексе, поэтому таблица не была затронута.

Скажите вслух, почему анализ диапазона выполняется медленнее, чем поиск точки, даже с индексом. Он возвращает 42 470 совпадений, а не девять, и индекс быстро приводит вас к началу совпадений, но движку все равно приходится их проходить. Индекс устраняет затраты на поиск строк, а не затраты на чтение тех, которые вы запросили.

4. Сколько стоит индекс

Индексы не бесплатны, и знание цены — это то, что позволяет вам попросить их, не отмахиваясь.

Время строить. Создание индекса на 500 000 строк заняло 572 мс. Для ста миллионов строк это считанные минуты, а на некоторых движках таблица блокируется во время работы.

Диск. Размер файла базы данных составлял 25,1 МБ с первым индексом. Каждый индекс представляет собой вторую копию своих столбцов плюс указатели, поэтому таблица с шестью индексами может быть в несколько раз больше ее собственного размера.

Пишет. Это то, что люди недооценивают. Каждая вставка, обновление и удаление должны поддерживать каждый индекс таблицы. Измерено на той же машине со вставкой 200 000 строк:

no indexes        449 ms
three indexes    3124 ms      7.0x slower

Семь раз по трем индексам. Это сделка в одном числе: чтение становится быстрее, а запись медленнее, и что имеет значение, полностью зависит от того, для чего предназначена таблица. Таблица отчетов, загружаемая один раз за ночь, может содержать множество индексов. Таблица, выполняющая запись для каждого действия пользователя, не может.

Здесь также объясняется совет, который звучит странно, пока вы не увидите это: при массовой загрузке большой таблицы отбросьте индексы, загрузите их, а затем перестройте. Построить индекс один раз по готовым данным гораздо дешевле, чем поддерживать его для каждой строки.

5. Три способа потерять имеющийся у вас индекс

Перед пояснением: указатель на customer_id существует, и фильтры запросов на customer_id. Скажите, гарантирует ли это, что он будет использоваться.

Это не так, и все три показателя были измерены на основе таблицы, в которой существовал индекс.

Функция или арифметика в столбце.

WHERE customer_id = 41234        SEARCH ... (customer_id=?)     0.2 ms
WHERE customer_id + 0 = 41234    SCAN                          57.9 ms

Индекс сортируется по customer_id, а не по customer_id + 0. С точки зрения движка это другое выражение, и у него нет его отсортированной копии, поэтому он вычисляет его для каждой строки. Версия с датой хуже:

WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'    3.9 ms
WHERE strftime('%Y-%m', order_date) = '2026-03'                 488.8 ms

Те же 42 470 строк, в 125 раз медленнее и медленнее, чем было при отсутствии индекса вообще, потому что теперь он выполняет полный проход. и вызов функции полмиллиона раз. Это конкретная причина рекомендаций в руководство по датам для сравнения необработанного столбца с диапазоном, а не для его переноса.

Ведущий подстановочный знак. LIKE 'Nor%' в принципе можно использовать отсортированный индекс, потому что все, что начинается с этих трех букв, находится вместе. LIKE '%orth' не может ни в одном движке, потому что отсортированный список не дает отправной точки для неизвестного префикса. На используемом здесь движке ни одна форма не использовала индекс, и обе сканировались примерно за 80–96 мс, в то время как простое равенство в том же столбце выполнялось как ПОИСК за 16,0 мс. Правило, которое следует принять: равенство или диапазон префиксов удобны для индексирования, а сопоставление текста в середине значения — это другая проблема, требующая другого типа индекса.

Несоответствие типов. Сравнение индексированного числа со строкой в ​​кавычках или индексированной строки с числом заставляет движок конвертировать одну сторону в строке, что является замаскированной функцией для столбца. Если запрос неожиданно сканируется, прежде чем проверять что-либо еще, убедитесь, что обе стороны сравнения имеют один и тот же тип.

6. Составные индексы и самое левое правило

Индекс может охватывать несколько столбцов, и порядок их перечисления определяет, для чего его можно использовать. Один индекс на (region, product), три запроса, все измеренные:

WHERE region = 'North'                         SEARCH (region=?)              16.1 ms
WHERE region = 'North' AND product = 'Desk'    SEARCH (region=? AND product=?)  4.7 ms
WHERE product = 'Desk'                         SCAN                            77.1 ms

Первые два используют его, а третий нет. Причина в том, что означает сортировка: индекс сортируется сначала по регионам, а по продуктам только внутри каждого региона. Спросите регион, и вы знаете, с чего начать. Запросите товар без региона, и соответствующие записи разбросаны по всему индексу, поэтому переходить не к чему.

Это правило крайнего левого префикса, и это самая полезная вещь, которую нужно знать о составных индексах. Индекс на (a, b, c) обслуживает запросы по a, на a and bи дальше a, b and c. Он ничего не делает для запроса b один.

Это дает практическое правило упорядочивания: первым ставьте столбец, по которому вы всегда фильтруете, а вторым — тот, который вы иногда добавляете. Если вам действительно нужны оба направления, то это два индекса, и теперь вы вернулись в четвертый раздел, решая, стоит ли затрат на запись того.

7. Селективность: тот же индекс, с разницей в 130 раз.

До пояснения: один индекс, один столбец, два запроса, отличающиеся только количеством в них. Предскажите, займут ли они одинаковое время.

WHERE customer_id > 100        499,157 rows match      26.1 ms
WHERE customer_id > 59990           74 rows match       0.2 ms

Оба использовали индекс. Один из них в 130 раз медленнее другого, и ничего в запросе или индексе не изменилось, кроме того, какая часть таблицы совпала.

У этого свойства есть имя, избирательность: какую часть таблицы сохраняет условие. Индекс имеет смысл иметь, когда условие является выборочным, и мало что дает, если это не так, поскольку возврат большей части таблицы означает чтение большей части таблицы, как бы вы туда ни попали. Тот же эффект проявляется в столбце только с двумя значениями:

WHERE is_returned = 0     490,000 rows      45.2 ms
WHERE is_returned = 1      10,000 rows       0.9 ms

Идентичный индекс в одном и том же столбце отлично подходит для редких значений и почти бессмысленен для обычных. Вот почему «индексировать каждый столбец» — плохой совет: индекс для флага, который составляет 98% от одного значения, оплачивает полную стоимость записи и почти ничего не приносит от запроса, который люди фактически выполняют.

Именно поэтому движки ведут статистику распределения значений и почему запрос, который был быстрым в прошлом месяце, сегодня может быть медленным, и его никто не меняет: данные изменили форму, и оптимизатор теперь делает другой выбор.

8. Что индексировать, а что нет

Краткий список в том порядке, в котором я бы его проработал.

Еще один совет, который важнее любого из вышеперечисленных: как аналитик вы часто не можете создать индекс, потому что у вас есть доступ на чтение к чужой базе данных. Это не тупик. Вывод плана по-прежнему доступен вам, и «этот запрос сканирует сто миллионов строк, потому что в нем нет индекса». order_date« — это конкретный, практически осуществимый запрос, который владелец базы данных может выполнить за минуту. Расплывчатые запросы на ускорение ничего не дают; результат плана и измеренное время получают индекс.

И когда ответ на самом деле отрицательный, оставшиеся ходы — те, которые нужно сделать. Обработка больших наборов данных: фильтровать ранее, агрегировать в базе данных, выполнять выборку во время разработки и материализовать результат один раз, вместо повторного запроса.

Пограничные случаи, которые стоит знать

Шесть, которые появятся.

Оптимизатор намеренно игнорирует ваш индекс. Если он считает, что большая часть таблицы совпадает, сканирование действительно обходится дешевле, чем прыгать по нему, и оно будет сканировать. Обычно это правильно, а если и неверно, то это потому, что статистика устарела.

Устаревшая статистика. Механизмы выбирают планы из сводок данных, которые периодически обновляются. После большой нагрузки запустите команду обновления движка, ANALYZE как на SQLite, так и на Постгрес, или план может быть основан на таблице, которая больше не существует в этой форме.

Индексы в представлении. Простое представление не содержит данных и не может быть проиндексировано. Вы индексируете базовые таблицы или вам нужно материализованное представление, которое представляет собой реальный сохраненный результат.

НУЛИ. Сохраняются ли нули в индексе и есть ли IS NULL возможность его использования зависит от двигателя. Если вы часто фильтруете пропущенные значения, проверьте это, а не предполагайте.

Индекс, который дублирует другой. Индекс на (a) является избыточным, когда индекс на (a, b) уже существует по крайнему левому правилу. Это чистая стоимость записи. Найдите их, прежде чем добавлять больше.

Присоединяется. Неиндексированный ключ соединения — самая дорогая вещь в большинстве медленных запросов, поскольку поиск происходит один раз для каждой строки другой таблицы. Если соединение происходит медленно, сначала индексируйте ключ на большей стороне; механика того, что делает соединение, находится в руководство по объединениям.