Что такое индекс в базе данных: принцип работы, виды и SQL-примеры

Три ключевых факта об индексе: он ускоряет поиск, снижая сложность с O(n) до O(log n) или O(1); требует дополнительного дискового пространства; автоматически обновляется при каждой операции вставки, изменения или удаления строк.

В статье разберём, зачем нужны индексы в базах данных, как работает механизм поиска по TID, какие бывают виды, как выбрать подходящий тип и как правильно создать индекс с помощью SQL-команд.

Зачем нужны индексы в базе данных: задачи и область применения

При небольшом объёме данных разница между поиском по индексу и полным перебором почти незаметна. Но стоит таблице вырасти до сотен тысяч или миллионов записей — каждый SELECT с условием WHERE без индекса становится узким местом системы. Индексы в базе данных используются для ускорения нескольких типов операций: поиск строк по условию (WHERE column = value), объединение таблиц по внешнему ключу (JOIN), сортировка результатов (ORDER BY) и группировка данных (GROUP BY).

Когда индекс не нужен: таблица менее 1 000 строк — Seq Scan быстрее из-за меньших накладных расходов; столбец редко попадает в WHERE или JOIN; таблица с очень частыми операциями записи по большинству строк, где обновление индексов будет доминировать над выигрышем от чтения. Каждая лишняя структура — нагрузка при записи без пользы при чтении.

Как индекс сокращает алгоритмическую сложность: от O(n) к O(log n)

Без индекса СУБД выполняет полный перебор: проверяет каждую строку одну за другой. При n = 1 000 000 строк — миллион операций сравнения, сложность O(n).

B-дерево использует бинарный поиск по узлам. На каждом шаге отсекается половина пространства поиска. Для таблицы с миллионом строк достаточно примерно 20 проверок — log₂ 1 000 000 ≈ 20. Индексное сканирование сокращает количество операций в десятки тысяч раз по сравнению с полным перебором.

Хэш-индекс работает ещё быстрее для точного равенства: хэш-функция сразу вычисляет номер корзины (bucket), где хранится нужная запись. Теоретическая сложность — O(1), константная, не зависящая от размера таблицы. Однако хэш-индекс поддерживает только оператор = и не подходит для запросов по диапазону.

Как работает индексирование в базе данных

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

При INSERT, UPDATE или DELETE СУБД синхронно обновляет все индексы таблицы в рамках той же транзакции. Это главный компромисс: каждая операция записи становится дороже пропорционально числу индексов.

Отдельного внимания заслуживает HOT-механизм (Heap-Only Tuples — обновление только в куче данных). Если операция UPDATE изменяет поля, которые не входят ни в один индекс таблицы, PostgreSQL помечает новую версию строки как кучевую и не перестраивает индексную структуру. Это существенно снижает накладные расходы при частых точечных обновлениях полей, не входящих в условия WHERE.

TID, оптимизатор и выбор между Index Scan и Seq Scan

TID (Tuple Identifier, идентификатор кортежа) — физический адрес строки: шесть байт, кодирующих номер блока на диске и позицию строки внутри блока. Когда индекс находит совпадение с условием запроса, он возвращает СУБД именно TID, по которому та напрямую обращается к нужной странице данных.

Оптимизатор запросов не обязан использовать индекс — он выбирает план с наименьшей расчётной стоимостью. Если условие WHERE отбирает более 10–15% строк таблицы, оптимизатор, как правило, предпочитает последовательное сканирование: чтение страниц подряд эффективнее хаотичных прыжков по случайным адресам на диске. Это называется низкой селективностью — индекс просто не успевает окупиться.

Ключевой инструмент диагностики — EXPLAIN ANALYZE. Он показывает фактический план выполнения запроса: искать нужно строки «Index Scan» (индекс применён) или «Seq Scan» (игнорирован). Если оптимизатор выбрал Seq Scan там, где вы ожидали индекс — скорее всего, выборка недостаточно селективна или статистика устарела и стоит выполнить ANALYZE.

Виды индексов в базе данных: полная классификация

Единого «лучшего» типа индекса не существует — оптимальный выбор зависит от характера запросов, типа данных и используемой СУБД. Классификация строится по трём принципам: алгоритм хранения и поиска, охват данных (один столбец, несколько столбцов, часть таблицы), физическая организация строк на диске.

Разные алгоритмы поддерживают разные операторы: B-дерево охватывает диапазоны и сортировку, хэш-индекс оптимален только для точного равенства, GIN работает с составными типами. Один и тот же запрос может выполняться в разы быстрее с правильным типом — и практически без выигрыша с неправильным. Поэтому сначала определяют тип запроса, затем — тип индекса.

По алгоритму: B-дерево, хэш, GiST, SP-GiST, GIN, BRIN, Bitmap, полнотекстовый

Это самая обширная классификация — алгоритм определяет, какие операторы поддерживает индекс и при каком типе данных он эффективен.

B-дерево — тип по умолчанию во всех основных СУБД: PostgreSQL, MySQL, Oracle, SQL Server. Сбалансированное дерево, в котором каждый узел хранит диапазон ключей и ссылки на дочерние узлы. Поддерживает операторы =, <, >, <=, >=, BETWEEN, LIKE ‘prefix%’, ORDER BY и IS NULL. Сложность поиска — O(log n). Подходит для большинства задач, кроме геоданных и составных типов (массивы, jsonb).

Хэш-индекс работает только с оператором точного равенства =. Хэш-функция переводит значение ключа в номер корзины (bucket), где хранятся строки с тем же хэшем. Сложность поиска — O(1). Проблема коллизий решается цепочками внутри корзин. Поддержка WAL-журналирования добавлена в PostgreSQL 10+: до этой версии хэш-индексы не восстанавливались из резервных копий, что делало их рискованными для использования в продуктовых системах.

GiST (обобщённое поисковое дерево) использует предикативную функцию вместо упорядоченной сортировки. Оптимален для геоданных, диапазонов, геометрических объектов. Расширяем: можно добавлять пользовательские классы операторов. Медленнее B-дерева при вставке, но охватывает типы данных, которые B-дерево не поддерживает.

SP-GiST — несбалансированная версия GiST для данных с неравномерным распределением: IP-адреса, телефонные номера, координаты с quadtree или k-d tree структурой. Работает быстро, когда у дерева мало ветвей.

GIN (инвертированный индекс) хранит список TID для каждого элемента составного значения. Идеален для jsonb (операторы @>, ?), массивов (&&), полнотекстового поиска (@@ для tsvector). Быстрый при поиске, медленный при обновлении — подходит для столбцов, которые часто читают, но редко изменяют.

BRIN (Block Range Index, индекс диапазонов блоков) хранит минимальное и максимальное значение для каждого диапазона физических блоков таблицы. Занимает в сотни раз меньше места, чем B-дерево. Эффективен только при высокой корреляции данных с порядком вставки: дата найма, монотонно возрастающий идентификатор, временны́е ряды. При случайном распределении значений — практически бесполезен.

Bitmap-индекс хранит битовую карту: для каждого уникального значения столбца — вектор из 0 и 1 по строкам таблицы. Эффективен при низкой кардинальности (поле «Пол», «Статус заказа»). Поддерживает побитовые операции AND/OR/NOT — удобен для аналитических СУБД (Oracle). В PostgreSQL Bitmap Scan строится динамически из B-дерева, отдельного типа индекса нет.

Полнотекстовый индекс токенизирует текст, применяет стемминг (приведение слова к основе) и стоп-слова, затем строит инвертированный список. Поддерживает ранжирование по TF-IDF (взвешенная частота термина — обратная частота документа). В PostgreSQL реализуется через GIN с типом tsvector.

По охвату данных: составной, частичный, покрывающий, функциональный

Помимо алгоритма, важно решить, по скольким столбцам и по какому подмножеству строк строить индекс.

Составной индекс создаётся по нескольким столбцам: CREATE INDEX ON orders (customer_id, created_at). Здесь действует правило левого префикса: индекс ускорит запрос по customer_id и по customer_id + created_at, но не поможет при фильтрации только по created_at. Порядок столбцов критичен — ставьте на первое место тот, который чаще встречается в WHERE самостоятельно или обладает наибольшей селективностью.

Частичный индекс создаётся с условием WHERE: CREATE INDEX ON orders (status) WHERE status = ‘pending’. Размер такого индекса в разы меньше полного — актуально при сильно неравномерном распределении значений, когда, например, 99% строк уже имеют status = ‘shipped’ и индексировать их не нужно.

Покрывающий индекс включает все столбцы, нужные для запроса. Тогда СУБД читает данные прямо из индекса, не обращаясь к таблице — это Index Only Scan. Требует актуального VACUUM: PostgreSQL использует карту видимости, чтобы убедиться в актуальности строк. В PostgreSQL 11+ дополнительные столбцы добавляются через INCLUDE: CREATE INDEX ON users (email) INCLUDE (name, created_at).

Функциональный индекс строится по выражению: CREATE INDEX ON users (LOWER(email)). Условие запроса должно содержать то же выражение: WHERE LOWER(email) = ‘user@example.com’. Если написать просто WHERE email = ‘…’, индекс не применится — выражения должны совпадать дословно.

По физической организации: кластеризованный и некластеризованный

Этот тип отличается не алгоритмом поиска, а отношением к физическому расположению строк на диске.

Кластеризованный индекс физически переупорядочивает строки таблицы в соответствии с ключом. Поскольку строки могут иметь только один физический порядок, кластеризованный индекс на таблицу — один. В SQL Server PRIMARY KEY автоматически создаёт кластеризованный индекс. В Oracle аналогом служит Index-Organized Table (IOT). Особенно эффективен для range queries: данные нужного диапазона расположены последовательно на диске.

Некластеризованный индекс — отдельная структура со ссылками-TID на строки основной таблицы. Их может быть несколько на одну таблицу. Большинство B-деревьев в PostgreSQL — некластеризованные.

Как выбрать тип индекса: практическое дерево решений

Выбор начинается с вопроса: какой оператор используется в условии WHERE и какой тип данных хранится в столбце?

• Равенство (=) на числовом или строковом столбце с высокой кардинальностью: B-дерево универсально, хэш-индекс — если запросов по диапазону нет.
• Диапазон (<, >, BETWEEN, ORDER BY): только B-дерево.
• Полнотекстовый поиск (@@): GIN с tsvector.
• Геоданные и геометрия: GiST или SP-GiST в зависимости от типа объектов.
• Столбец jsonb или массив: GIN.
• Монотонно возрастающее поле в огромной таблице: BRIN.

Когда индекс не нужен ни при каком алгоритме: таблица небольшая и читается почти целиком; условие WHERE отбирает более 30% строк — оптимизатор всё равно уйдёт на Seq Scan; столбец крайне редко появляется в запросах; таблица с 10 и более индексами при активной записи — сначала ревизия, потом добавление.

Кардинальность и селективность как главные критерии

Кардинальность — доля уникальных значений относительно общего числа строк. Высокая кардинальность (Email, UUID, идентификатор пользователя) означает, что каждое значение уникально или почти уникально — B-дерево принесёт максимальный выигрыш. Низкая кардинальность (поле «Пол», логический флаг, статус из трёх значений) — каждое значение встречается в тысячах строк. B-дерево при такой структуре данных нередко проигрывает Seq Scan. В Oracle для этого сценария применяют Bitmap; в PostgreSQL — частичный индекс или составной с высококардинальным столбцом на первом месте.

Селективность — обратная величина: какую долю строк вернёт условие запроса. Чем меньше строк — тем выгоднее Index Scan. Пороговое значение — 10–15% строк таблицы: если условие отбирает больше, оптимизатор переключится на Seq Scan, потому что последовательное чтение блоков дешевле хаотичного доступа.

Создать индекс недостаточно — необходимо убедиться, что оптимизатор его выбирает. Инструмент проверки — EXPLAIN ANALYZE: он показывает фактическую стоимость и тип доступа к данным для конкретного запроса.

Создание и управление индексами в SQL: синтаксис и примеры

Создание индекса в PostgreSQL начинается с команды CREATE INDEX. Базовый синтаксис для индексации запросов по столбцу email:

CREATE INDEX idx_users_email ON users (email);

Для запрета дублей — CREATE UNIQUE INDEX:

CREATE UNIQUE INDEX idx_users_email_uniq ON users (email);

Явный выбор алгоритма задаётся через USING:

CREATE INDEX idx_users_email_hash ON users (email) USING HASH;

CREATE INDEX idx_docs_body_gin ON documents (body) USING GIN;

CREATE INDEX idx_logs_created ON logs (created_at) USING BRIN;

Функциональный индекс для регистронезависимого поиска:

CREATE INDEX idx_lower_email ON users (LOWER(email));

Частичный индекс только для незакрытых заказов:

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = ‘pending’;

Покрывающий индекс с дополнительными столбцами (PostgreSQL 11+):

CREATE INDEX idx_users_cover ON users (email) INCLUDE (name, created_at);

Удалить индекс: DROP INDEX idx_users_email. Перестроить при повреждении: REINDEX INDEX idx_users_email. Проверить, нет ли невалидных индексов после прерванного создания:

SELECT indexrelid::regclass AS indexname

FROM pg_index

WHERE NOT indisvalid;

CREATE INDEX CONCURRENTLY и чтение EXPLAIN ANALYZE

Стандартный CREATE INDEX устанавливает блокировку SHARE: читать таблицу можно, но запись заблокирована до завершения. На больших таблицах это может занять минуты и остановить работу приложения.

CREATE INDEX CONCURRENTLY снимает это ограничение: блокировка SHARE UPDATE EXCLUSIVE разрешает DML в процессе построения. Платит за это двумя полными проходами по таблице — операция занимает в 2–3 раза дольше. Нельзя параллельно менять структуру таблицы через ALTER TABLE.

Если построение прервалось из-за конфликта, индекс останется в состоянии INVALID. Найти такие: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid. Затем выполнить DROP INDEX CONCURRENTLY idx_name и пересоздать.

Чтение EXPLAIN ANALYZE: строка «Index Scan» или «Index Only Scan» означает, что индекс применён. «Seq Scan» — проигнорирован. Смотрите Actual Time (реальное время выполнения) и Heap Fetches при Index Only Scan — чем меньше обращений к таблице, тем эффективнее покрывающий индекс.

Производительность и компромиссы: когда индекс помогает, а когда вредит

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

B-дерево занимает приблизительно 10–30% от объёма исходной таблицы. При проектировании схемы это нужно учитывать заранее, особенно для таблиц с миллиардами строк.

NULL-значения индексируются в большинстве методов: B-дерево, GiST и GIN обрабатывают IS NULL и IS NOT NULL через индекс — это работает корректно и не требует специальных обходных решений.

HOT-механизм в PostgreSQL частично снимает проблему записи: если UPDATE затрагивает только поля, не вошедшие ни в один индекс, перестройка индексной структуры не происходит. Новая версия строки помечается указателем внутри той же страницы данных.

Практическое правило: не более 5–7 индексов на таблицу с активной записью. Для аналитических хранилищ с редкой вставкой и преобладающими SELECT-запросами ограничение значительно менее жёсткое.

Nextcloud: ошибка «в базе данных отсутствуют некоторые индексы»

При обновлении Nextcloud между версиями миграции не всегда создают все необходимые индексы. В результате СУБД выполняет последовательное сканирование там, где должен быть Index Scan — это проявляется как заметное замедление операций с файловым кэшем и папками.

Диагностика: откройте Настройки → Администрирование → Обзор. Если система показывает предупреждение «В базе данных отсутствуют некоторые индексы» — нужно добавить их вручную.

Решение — команда из корневой директории Nextcloud:

php occ db:add-missing-indices

При проблемах с размером столбцов идентификаторов выполните дополнительно:

php occ db:convert-filecache-bigint

Связь с темой прямая: отсутствующий индекс → Seq Scan по таблице файлового кэша, которая в крупной инсталляции содержит миллионы строк → ощутимое торможение интерфейса. Добавление индексов через occ устраняет причину, а не симптом.

Хотите разобраться с базами данных и SQL на практике? В рамках федерального проекта «Активные меры содействия занятости» можно пройти обучение по аналитике данных и информационным системам без отрыва от работы и без вложений. Смотрите каталог доступных программ.

Часто задаваемые вопросы

Чем отличается индекс от первичного ключа?

PRIMARY KEY — это ограничение: требует уникальности и запрещает NULL. Под него СУБД автоматически создаёт индекс. Но индексы — более широкое понятие: их создают на любых столбцах, включая внешние ключи, столбцы в условиях WHERE и составные выражения. Первичный ключ на таблицу — один; индексов может быть несколько.

Сколько индексов можно создать на одну таблицу?

Технически — без ограничений. На практике для таблиц с активной записью ориентируйтесь на не более 5–7: каждый индекс обновляется при DML и потребляет ресурсы транзакции. Для аналитических таблиц только для чтения ограничение значительно менее жёсткое.

Как проверить, что запрос использует индекс?

Запустите EXPLAIN ANALYZE перед запросом. В выводе ищите строки «Index Scan» — индекс применён, или «Seq Scan» — проигнорирован. Если видите Seq Scan там, где ожидали индекс — проверьте селективность: возможно, условие отбирает более 15% строк, и оптимизатор принял верное решение.

Когда не нужно создавать индекс?

В трёх ситуациях: таблица небольшая (менее 1 000 строк) — последовательное сканирование дешевле; столбец с низкой кардинальностью в OLTP-системе без поддержки Bitmap (поле статуса с двумя значениями); столбец крайне редко появляется в WHERE или JOIN. Индекс займёт место и замедлит запись без выигрыша при чтении.

Как правильно создать составной индекс в базе данных?

Порядок столбцов критичен — действует правило левого префикса. Индекс (author, year) ускорит запрос по author и по author + year, но не поможет при фильтрации только по year. Ставьте на первое место столбец с наибольшей селективностью или тот, который чаще используется в WHERE самостоятельно.

В чём разница между GIN и GiST?

GiST — сбалансированное дерево с предикативными функциями для геоданных, диапазонов и пользовательских типов данных. GIN — инвертированный индекс для составных типов: jsonb, массивов, tsvector. Полнотекстовый поиск или запросы к jsonb — GIN. Геоданные и задача «ближайший сосед» — GiST. GIN быстрее при поиске, медленнее при обновлении данных.

Что такое BRIN и когда его применять?

BRIN хранит минимальное и максимальное значения для диапазонов физических блоков таблицы. Эффективен только если данные коррелируют с порядком вставки: дата найма, монотонный идентификатор, временны́е ряды. Занимает в сотни раз меньше места, чем B-дерево. При случайном распределении значений — бесполезен.

Замедляет ли индекс запись в базу данных?

Да — это главный компромисс. При каждой операции INSERT, UPDATE или DELETE СУБД обновляет все индексы таблицы в той же транзакции. HOT-механизм PostgreSQL частично решает задачу: если обновляются только поля вне индекса, перестройка индексной структуры не происходит.

Что такое REINDEX и когда его запускать?

REINDEX INDEX name полностью перестраивает индекс — при повреждении, сильной фрагментации или после восстановления из резервной копии. Особенно актуально для хэш-индексов до PostgreSQL 10, которые не восстанавливались из WAL-журнала. PostgreSQL 12+ поддерживает REINDEX CONCURRENTLY — перестройка без длительной блокировки таблицы.

Как индексирование используется при обучении работе с базами данных?

Понимание индексов — ключевой навык системного аналитика и специалиста по информационным системам. В программе «Специалист по аналитике и базам данных в информационных системах» (72 ч., бесплатно по нацпроекту «Кадры», старт в июле) индексирование разбирается на практических задачах с реальными запросами и EXPLAIN ANALYZE.