Индексы SQLExplained

31 of 31 rows

Понятие↕	Объяснение↕	Совет↕
Типы индексов
⤢B-tree	Сбалансированное дерево для равенств, диапазонов и сортированных обходов. Выбор по умолчанию в большинстве баз данных.	Используйте для =, <, >, BETWEEN, ORDER BY. Не для полнотекстового поиска.
⤢Clustered	Индекс, у которого уровень листьев — сама таблица. Строки хранятся в порядке ключа. Один на таблицу.	InnoDB кластеризует по первичному ключу. Таблицы SQL Server без него — кучи (heaps).
⤢Nonclustered	Отдельная структура с ключами и указателем обратно на базовую таблицу. Много на таблицу.	В куче хранит ID строки. В кластеризованной таблице хранит ключ кластеризации.
⤢Hash	Хеш-таблица только для поисков по равенству. Точное совпадение за постоянное время.	Используйте только для операций =; диапазонам и сортировке нужен B-tree. Хеш для оптимизированных под память таблиц SQL Server требует число корзин, близкое к числу строк.
⤢GIN	Обобщённый инвертированный индекс. Отображает значения на строки. Создан для массивов и полнотекстового поиска.	Используйте для jsonb, массивов, tsvector и триграммного поиска. Строится медленнее B-tree.
⤢GiST	Обобщённое дерево поиска. Расширяем для нескалярных типов и запросов на пересечение.	Используйте для геометрических диапазонов, kNN и ограничений-исключений. pg_trgm ускоряет LIKE.
⤢SP-GiST	GiST с разбиением пространства. Делит данные на непересекающиеся области через radix- или квадродеревья.	Используйте для точек, IP-префиксов и поиска по префиксу. Не для запросов на пересечение; берите GiST.
⤢BRIN	Индекс диапазонов блоков. Хранит min/max на диапазон блоков. Крошечный размер.	Используйте на больших физически отсортированных таблицах. Дёшево построить, слабая селективность.
⤢Columnstore	Индекс, хранящийся сегментами по столбцам вместо строк. Высокое сжатие, пакетное исполнение.	Используйте для аналитических обходов по многим строкам и малому числу столбцов. Поиск одной строки медленный.
⤢Full-text	Выделенный инвертированный словарный индекс в MySQL и SQL Server. Строится на текстовый столбец.	Используйте MATCH AGAINST или CONTAINS. Полнотекстовый поиск Postgres вместо этого использует GIN по tsvector.
⤢Bitmap	Индекс Oracle, хранящий по одной битовой карте на каждое отдельное значение. Каждый бит отмечает одну строку.	Используйте на столбцах с низкой кардинальностью в хранилищах, нагруженных чтением. Параллельные записи сериализуются по битовой карте.
⤢Descending	Столбец индекса объявлен DESC. Ключи этого столбца хранятся в обратном порядке.	Нужен для смешанных сортировок вроде (a ASC, b DESC). Простые сортировки DESC могут читать обычный индекс и в обратную сторону.
⤢Invisible	Индекс MySQL 8, который оптимизатор игнорирует. При этом поддерживается при каждой записи.	Скройте индекс перед удалением, чтобы оценить влияние. Верните видимость без перестройки.
Проектирование
⤢Composite	Составной индекс по нескольким столбцам. Ведущие столбцы должны совпадать с порядком фильтров запроса.	Сначала столбцы равенства, затем диапазоны. Два одиночных индекса не заменят один составной.
⤢Partial	Индекс по предикату WHERE. SQL Server называет его отфильтрованным индексом. Покрывает только подходящие строки.	Индексируйте только строки с active='t'. Вдвое меньше размер и стоимость записи.
⤢Covering (INCLUDE)	B-tree с добавленными дополнительными столбцами вне ключа. Обходы только по индексу избавляют от обращений к куче.	Добавляйте часто извлекаемые столбцы через INCLUDE. Индекс остаётся сортируемым.
⤢Unique	Индекс, запрещающий дубликаты значений. Отклоняет конфликтующие вставки.	Используйте для естественных ключей и связей один-к-одному. UNIQUE с NULL допускает много NULL.
⤢Expression	Индекс, построенный на функции от столбцов, а не на самих столбцах.	Индексируйте lower(email) или date_trunc('day', created_at). Запрос должен повторять то же выражение в точности.
⤢Opclass	Класс операторов. Определяет, как индекс сравнивает и хранит тип одного столбца.	Задайте varchar_pattern_ops для LIKE 'abc%' без сворачивания регистра. Значения по умолчанию подходят для равенств и диапазонов.
⤢Index-only scan	План, который отвечает на запрос только страницами индекса. Куча не читается вовсе.	Нужны все извлекаемые столбцы в индексе или в списке INCLUDE. Vacuum поддерживает актуальность visibility map.
⤢Foreign keys	Столбцы внешних ключей не получают автоматического индекса в ссылающейся таблице.	Индексируйте каждый столбец внешнего ключа, по которому соединяетесь или удаляете. Каскадные удаления без него сканируют дочернюю таблицу.
Обслуживание
⤢EXPLAIN	Инспектор планов. Показывает последовательные обходы, выбор индексов и оценки стоимости.	Используйте EXPLAIN (ANALYZE, BUFFERS). Seq Scan по таблице в 10 млн строк означает отсутствующий индекс.
⤢ANALYZE	Выборочно сканирует столбцы таблицы для статистики планировщика. Гистограммы определяют выбор индекса.	Запускайте после массовых загрузок. Устаревшая статистика ведёт к seq scan по индексированным столбцам.
⤢Over-indexing	Каждый индекс увеличивает накладные расходы на запись. Вставки, обновления и удаления платят за каждый индекс.	Удаляйте неиспользуемые индексы. Следите за использованием в pg_stat_user_indexes.
⤢pg_stat_user_indexes	Представление Postgres об использовании каждого индекса. Считает обходы и прочитанные кортежи с последнего сброса статистики.	Фильтруйте idx_scan = 0, чтобы найти неиспользуемые индексы. Сбрасывайте счётчики функцией pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Строит индекс, не блокируя чтение и запись. Требует двух проходов по таблице.	Нельзя запускать внутри транзакции. Неудачная сборка оставляет INVALID-индекс, который нужно удалить.
⤢DROP INDEX	Удаляет индекс и освобождает его хранилище. По умолчанию берёт монопольную блокировку.	Используйте DROP INDEX CONCURRENTLY на нагруженных таблицах. Перед удалением проверьте статистику использования.
⤢REINDEX	Перестраивает раздутый индекс. Возвращает место от мёртвых кортежей.	Используйте REINDEX CONCURRENTLY в продакшене. Без него блокировки останавливают записи.
⤢Bloat	Мёртвые кортежи и дыры в страницах, накапливающиеся в MVCC-таблицах и их индексах.	Измеряйте через pgstattuple. Частый vacuum; REINDEX, когда мёртвое место преобладает.
⤢Fillfactor	Процент заполнения страницы при записи. Свободное место поглощает будущие обновления.	Снизьте до 70-90 на таблицах с частыми обновлениями. Полные страницы вызывают деление страниц.
⤢CLUSTER	Перезаписывает таблицу в физическом порядке по индексу. Разовая операция.	Сочетается с BRIN для отсортированных блоков. Порядок не сохраняется при последующих записях.