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