Сложные индексы

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Сложные индексы

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

Базовое создание индексов в DDL — в статье DDL — определение структуры. Проверка плана и EXPLAIN — в Оптимизация SQL-запросов. Тип JSONB и операторы — JSONB. Практика JSONB с GIN — Практикум PostgreSQL по JSONB.

Какая структура под какой запрос. Точечный поиск и диапазоны по столбцу — B⁺-дерево (в PostgreSQL access method btree). Только равенство по ключу в специализированных сценариях — хеш (HASH, редко). Полнотекст и массивы — инвертированный подход (GIN, tsvector). Низкая кардинальность в OLAP — битовые карты. Сводная таблица — пять основных структур.

Роль индекса в доступе к строке. Кластерный (clustered) задаёт физический порядок строк — в InnoDB это PK; в SQL Server clustered index выбирают явно. Вторичный (secondary) ускоряет поиск по email, статусу и другим не-PK полям через цепочку "вторичный индекс → PK → строка". Первичный индекс по PK может быть плотным или разреженным на уровне блоков — см. типы primary, clustered, secondary. LSM и skip list — в восьми структурах хранения.

Многостолбцовые индексы

Многостолбцовый индекс — это индекс, построенный сразу по двум или более столбцам одной таблицы. Такой индекс организован как древовидная структура, в которой ключи сортируются сначала по первому указанному столбцу, затем по второму, и так далее. Порядок указания столбцов в определении индекса имеет критическое значение для его эффективности.

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

Когда условие WHERE содержит предикаты по всем столбцам индекса.
Когда предикаты заданы только по начальным столбцам индекса (например, по первому, или по первому и второму).
Когда запрос включает сортировку ORDER BY по тем же столбцам и в том же порядке, что и в индексе.
Когда запрос выполняет группировку GROUP BY по начальным столбцам индекса.

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

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

Покрывающие индексы

Покрывающий индекс — это индекс, который содержит все столбцы, необходимые для выполнения конкретного запроса. Благодаря этому оптимизатор может полностью удовлетворить запрос, используя только данные из индекса, без необходимости обращения к самой таблице. Такой подход называется "индексный поиск без обращения к строке" (index-only scan).

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

В некоторых СУБД, таких как PostgreSQL, покрывающий индекс можно создать с помощью конструкции INCLUDE. Эта конструкция позволяет добавить в индекс дополнительные неключевые столбцы, которые не участвуют в сортировке, но могут быть использованы для покрытия запроса. Например:

CREATE INDEX idx_orders_customer_date_include_total
ON orders (customer_id, order_date)
INCLUDE (total_amount);

В этом случае индекс упорядочен по customer_id и order_date, а total_amount хранится в листовых узлах индекса, но не влияет на порядок. Запрос вида:

SELECT total_amount
FROM orders
WHERE customer_id = 123 AND order_date >= '2025-01-01';

может быть выполнен только за счёт этого индекса, без обращения к таблице orders.

В других СУБД, например в Microsoft SQL Server, покрывающий индекс создаётся с помощью предложения INCLUDE аналогичным образом. В MySQL покрытие достигается путём включения всех необходимых столбцов непосредственно в список ключевых полей индекса, что может увеличить его размер, но сохраняет функциональность.

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

Частичные индексы

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

CREATE INDEX idx_orders_open_created
ON orders (created_at)
WHERE status = 'open';

Такой индекс ускорит запросы с тем же условием status = 'open' и фильтром по created_at. Уникальность можно задать только среди активных строк:

CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;

Подробнее об ограничениях с индексами — Ограничения целостности в SQL.

Типы индексов PostgreSQL (access methods)

В PostgreSQL тип индекса задаётся после USING. Без USING создаётся B-tree — универсальный вариант для большинства столбцов.

Access method	Назначение	Типичные операторы и типы
B-tree (по умолчанию)	Равенство, диапазоны, сортировка	`=`, `<`, `>`, `BETWEEN`, `ORDER BY`; числа, текст, даты, UUID
GIN	"Содержит элемент" в составном значении	`@>`, `?`, `?
GiST	Близость, пересечение, "рядом с точкой"	`&&`, `@>`, `<@`, `<<`, PostGIS, диапазоны, `EXCLUDE`
SP-GiST	Неравномерные пространственные и иерархические ключи	квадродеревья, префиксы, некоторые геотипы
BRIN	Очень большие таблицы с естественным порядком столбца	диапазоны по столбцу, слабая селективность блоков
Hash	Только равенство	`=` (редко; B-tree обычно достаточно)

Синтаксис:

CREATE INDEX idx_name ON table_name USING btree (column_name);
CREATE INDEX idx_tags ON articles USING gin (tags);

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

B-дерево (B-tree)

B-tree — структура по умолчанию для PRIMARY KEY, UNIQUE и обычного CREATE INDEX. Ключи упорядочены; оптимизатор использует индекс для фильтрации, соединений по равенству, диапазонов и для ORDER BY по префиксу столбцов индекса (см. правило левого префикса в многостолбцовых индексах выше).

Индекс по выражению ускоряет запросы, где в WHERE или ORDER BY стоит функция от столбца:

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

SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

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

LIKE 'prefix%' может использовать B-tree; шаблон LIKE '%suffix' — нет. Для нечёткого поиска по подстроке смотрите GIN с pg_trgm ниже.

Обобщённые инвертированные индексы (GIN)

Обобщённый инвертированный индекс (Generalized Inverted Index, GIN) — это специализированная структура индекса, реализованная в PostgreSQL для эффективной работы с составными и нескалярными типами данных, такими как массивы, JSON, полнотекстовый поиск и геометрические объекты.

В отличие от B-дерева, где каждый ключ в индексе соответствует одной строке таблицы, в GIN один элемент данных (например, значение внутри массива) может быть связан с множеством строк. Индекс строится как "инвертированная" карта: для каждого возможного значения хранится список идентификаторов строк, в которых это значение встречается.

Такая структура идеально подходит для запросов, проверяющих наличие определённого элемента в составном значении. Например, если в таблице есть столбец tags типа TEXT[], и нужно найти все записи, содержащие тег 'database', GIN-индекс позволит мгновенно получить список подходящих строк без полного сканирования таблицы.

Пример создания GIN-индекса для массива:

CREATE INDEX idx_articles_tags_gin ON articles USING GIN (tags);

Запрос:

SELECT * FROM articles WHERE 'database' = ANY(tags);

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

GIN также применяется для полнотекстового поиска. При индексировании tsvector-колонки GIN обеспечивает высокую скорость поиска документов по ключевым словам. Например:

CREATE INDEX idx_documents_fts ON documents USING GIN (to_tsvector('russian', content));

После этого запрос:

SELECT title FROM documents
WHERE to_tsvector('russian', content) @@ to_tsquery('russian', 'технологии & разработка');

выполняется с использованием GIN-индекса.

Для jsonb GIN ускоряет containment и проверку ключей:

CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);

SELECT event_id FROM events
WHERE payload @> '{"status": "paid"}'::jsonb;

Два класса операторов для jsonb (выбираются при создании индекса):

Класс	Создание	Сильная сторона	Ограничение
`jsonb_ops` (по умолчанию)	`USING GIN (payload)`	`@>`, `?`, `?	`,` ?&`, ключи
`jsonb_path_ops`	`USING GIN (payload jsonb_path_ops)`	`@>` по путям, компактнее	Нет `?` / `?

CREATE INDEX idx_data_path ON documents USING GIN (data jsonb_path_ops);

Числовые сравнения по вложенному полю ((payload->>'total')::numeric > 100) GIN по столбцу целиком не покрывает — нужен B-tree по выражению (см. практикум JSONB).

Расширение pg_trgm добавляет GIN (или GiST) для похожести строк и LIKE '%фрагмент%':

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT * FROM products WHERE name ILIKE '%телефон%';

Особенность GIN — высокая стоимость записи: одно обновление составного значения затрагивает много записей в индексе. GIN подходит для таблиц с преобладанием чтения или пакетных обновлений.

Параметры fastupdate и gin_pending_list_limit балансируют скорость вставки и поиска в OLTP.

GiST (Generalized Search Tree)

GiST — обобщённое сбалансированное дерево для данных, где важны отношения между значениями (пересечение, вхождение, расстояние), а не только сортировка по скаляру.

Типичные сценарии:

геометрия и PostGIS (ST_DWithin, && для bounding box);
типы диапазонов (daterange, int4range) — пересечение интервалов;
ограничение EXCLUDE — запрет пересекающихся интервалов бронирования (нужен индекс GiST или SP-GiST, см. Ограничения целостности в SQL);

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE room_bookings
ADD EXCLUDE USING GIST (
  room_id WITH =,
  during WITH &&
);

GiST для полнотекстового поиска возможен, но на больших объёмах чаще выбирают GIN из‑за скорости поиска по лексемам. GiST выигрывает, когда индексируют документ целиком и редко обновляют, а также для комбинированных типов через операторные классы.

Запись в GiST обычно дешевле, чем в GIN; поиск по "содержит ключ" в jsonb или массивах — зона GIN.

SP-GiST (Space-Partitioned GiST)

SP-GiST разбивает пространство ключей иерархически (квадродерево, префиксное дерево). Используется реже B-tree и GIN, когда распределение ключей сильно неравномерно — точки на карте, IP-префиксы, некоторые текстовые структуры.

CREATE INDEX idx_points_location ON geo_points USING SPGIST (location);

Перед созданием сверяйте доступные operator classes для типа столбца в документации PostgreSQL.

BRIN (Block Range INdex)

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

Идеальный случай — append-only журнал или факты с монотонно растущим created_at:

CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at);

Запрос по диапазону дат отсекает целые блоки без полного seq scan. BRIN слаб, если столбец часто обновляют "вразброс" или таблица сильно раздута UPDATE/VACUUM без кластеризации. Для точечного поиска по id остаётся B-tree.

Hash

Hash-индекс в PostgreSQL поддерживает только равенство (=). Размер и поведение на практике редко дают выигрыш перед B-tree; hash не участвует в диапазонах и сортировке. Используйте только при явном обосновании после EXPLAIN.

CREATE INDEX idx_cache_key_hash ON cache_entries USING HASH (cache_key);

GIN и GiST — как выбрать

Критерий	GIN	GiST
Модель	инвертированный список: ключ → строки	дерево "близости" и пересечений
Массивы, `jsonb`, FTS	основной выбор	возможен, обычно медленнее на поиске по элементам
Геометрия, диапазоны, `EXCLUDE`	не подходит	основной выбор
Запись	дороже	обычно дешевле
Размер индекса	часто больше	зависит от данных

При сомнении смотрите план: EXPLAIN (ANALYZE, BUFFERS) на репрезентативном объёме (Оптимизация SQL-запросов).

Стратегии индексирования в бэкенде

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

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

Анализ запросов и профилирование

Первый шаг в построении эффективной стратегии индексирования — это сбор и анализ реальных запросов. Большинство СУБД предоставляют инструменты для логирования медленных запросов, просмотра планов выполнения (execution plans) и мониторинга использования индексов. На основе этих данных можно выявить "тяжёлые" запросы, которые выполняются без использования индексов или используют их неэффективно.

Профилирование позволяет ответить на ключевые вопросы:

Какие столбцы чаще всего участвуют в условиях WHERE?
Какие комбинации полей используются в ORDER BY и GROUP BY?
Какие запросы возвращают большое количество строк, но фильтруют по редким значениям?
Какие запросы выполняются наиболее часто?

Ответы на эти вопросы формируют основу для принятия решений о создании новых индексов или удалении существующих.

Баланс между чтением и записью

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

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

Архитектурные паттерны, такие как CQRS (Command Query Responsibility Segregation), позволяют разделить модель записи и модель чтения. В этом случае таблица для записи может иметь минимальный набор индексов, а отдельная реплика или материализованное представление — богатый набор индексов, оптимизированных под запросы.

Индексирование в распределённых системах

В распределённых базах данных (например, Cassandra, ScyllaDB, CockroachDB) стратегия индексирования усложняется. Здесь важно учитывать не только локальную структуру данных, но и способ их распределения по узлам кластера.

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

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

Поэтому в распределённых системах предпочтение отдаётся проектированию схемы данных под конкретные запросы, а не созданию универсальных индексов.

Автоматическое и рекомендательное индексирование

Современные облачные СУБД (например, Amazon Aurora, Azure SQL Database, Google Cloud Spanner) предлагают функции автоматического создания индексов на основе анализа рабочей нагрузки. Эти системы отслеживают частоту запросов, их стоимость и потенциальный выигрыш от индекса, после чего предлагают или автоматически применяют рекомендации.

Однако полностью полагаться на автоматику не стоит. Автоматические рекомендации могут не учитывать долгосрочные последствия, например, рост объёма данных или изменение паттернов использования. Кроме того, они могут создавать избыточные индексы, если запросы временно меняют свой характер.

Лучшая практика — использовать автоматические рекомендации как отправную точку, но принимать окончательные решения вручную, с учётом архитектурных целей и бизнес-контекста.

Управление жизненным циклом индексов

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

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

Такой подход обеспечивает не только высокую производительность, но и экономическую эффективность, поскольку каждый индекс — это дополнительные затраты на хранение и обслуживание.

Индексирование в типичных бэкенд-сценариях

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

Фильтрация по диапазону дат и статусу
Многие приложения работают с событиями, заказами, логами или транзакциями, где часто требуется выбрать записи за определённый период и с конкретным статусом. Например:

SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31'
  AND status = 'completed';

Для такого запроса эффективен многостолбцовый индекс, в котором сначала идёт столбец с высокой селективностью (например, status), а затем — столбец диапазона (created_at). Это позволяет быстро найти все строки со статусом "completed", а затем отфильтровать их по дате без полного сканирования.

Сортировка с пагинацией
При реализации пагинации часто используется конструкция ORDER BY ... LIMIT ... OFFSET. Если порядок сортировки не поддерживается индексом, СУБД вынуждена сортировать весь результат перед применением LIMIT, что становится крайне медленным при росте объёма данных. Индекс по полям сортировки позволяет избежать этой операции.

Особое внимание требуется при смешанной сортировке (например, ORDER BY created_at DESC, id ASC). В таких случаях индекс должен точно соответствовать направлению сортировки каждого столбца.

Поиск по частичному совпадению
Запросы вида LIKE '%текст%' не могут использовать стандартные B-деревья. Для ускорения таких операций применяются специализированные индексы — полнотекстовые (в PostgreSQL, MySQL), GIN/GiST (в PostgreSQL), или сторонние решения вроде Elasticsearch. Важно понимать, что такие индексы требуют дополнительных ресурсов и усложняют архитектуру.

Агрегация по нескольким измерениям
В аналитических системах часто встречаются запросы с GROUP BY по комбинации полей. Например, группировка продаж по региону, категории товара и месяцу. Здесь помогает индекс по всем полям группировки, особенно если он покрывает также агрегируемые столбцы (например, сумму продаж).

Учёт специфики СУБД

Каждая система управления базами данных имеет свои особенности в реализации индексов. Например:

PostgreSQL — частичные индексы, INCLUDE, индексы по выражению и access methods (см. разделы выше в этой статье).

CREATE INDEX idx_orders_customer_status_expr
ON orders ((customer_id || '-' || status));

MySQL (InnoDB) — кластерный индекс только по PK; данные строки в листьях B⁺ PK. Каждый вторичный индекс хранит значения своих столбцов плюс PK для lookup. Покрытие запроса возможно, если все нужные столбцы есть в индексе.
Microsoft SQL Server — один clustered index на таблицу (часто PK); остальные индексы nonclustered (аналог secondary). Фильтруемые индексы и сжатие уменьшают размер индекса.
PostgreSQL — heap-таблицы; все индексы по сути secondary относительно физического порядка вставки. CLUSTER index_name ON table временно выравнивает heap по выбранному B-tree.
Oracle поддерживает инвертированные индексы, функциональные индексы и bitmap-индексы, которые особенно эффективны в хранилищах данных.

Сложные индексы