Составные индексы в SQL — порядок колонок, левый префикс и рабочие схемы

Составной индекс хранит значения нескольких колонок в заранее заданном порядке и ускоряет запросы, которые используют подходящий левый фрагмент этого порядка. Результат зависит от сочетания WHERE, JOIN, ORDER BY, диапазонов и набора возвращаемых полей. В этом гайде разберём проектирование, проверку через EXPLAIN и рабочие схемы для PostgreSQL, MySQL и SQLite.

Недавно мы подробно разбирали пространственные индексы и быстрый поиск объектов на карте. Теперь перейдём к более распространённой задаче: ускорению запросов, которые одновременно фильтруют, связывают или сортируют данные по нескольким обычным колонкам.

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

Составной индекс хранит упорядоченные комбинации значений

Составной, многоколоночный или composite index — это индекс, ключ которого состоит из двух и более колонок. В B-tree каждая запись можно мысленно представить как кортеж:

(tenant_id, status, created_at, ссылка_на_строку)

Если индекс создан так:

CREATE INDEX idx_orders_tenant_status_created
    ON orders (tenant_id, status, created_at);

значения сортируются сначала по tenant_id. Внутри одинакового tenant_id строки располагаются по status. Внутри одинаковой пары tenant_id + status порядок задаёт created_at.

Упрощённо индекс выглядит так:

(1, cancelled, 2026-07-01 09:10)
(1, paid,      2026-07-01 08:15)
(1, paid,      2026-07-02 11:40)
(1, shipped,   2026-07-03 16:20)
(2, new,       2026-07-01 10:00)
(2, paid,      2026-07-02 14:30)

База быстро находит начало диапазона для tenant_id = 1, затем сужает его до status = 'paid' и проходит только нужный интервал дат. Поиск по одному status сложнее: одинаковые статусы разбросаны по отдельным группам каждого tenant_id.

Именно поэтому два индекса с одинаковыми колонками могут решать разные задачи:

CREATE INDEX idx_a ON orders (tenant_id, status, created_at);
CREATE INDEX idx_b ON orders (status, tenant_id, created_at);

idx_a удобен для запросов внутри одной организации. idx_b подходит для административных выборок по статусу сразу среди всех организаций.

Левый префикс определяет основные сценарии использования B-tree

Для индекса (a, b, c) левыми префиксами считаются:

(a)
(a, b)
(a, b, c)

Поэтому B-tree обычно хорошо обслуживает такие условия:

WHERE a = ?

WHERE a = ? AND b = ?

WHERE a = ? AND b = ? AND c = ?

Условие на b без a не образует левый префикс:

WHERE b = ?

Условие на a и c использует ведущую колонку, но пропускает b:

WHERE a = ? AND c = ?

В этом случае база может быстро найти весь диапазон a = ?, а ограничение по c применить к найденным индексным записям. Размер просматриваемого диапазона определяется только a, потому что записи внутри него сгруппированы сначала по b, затем по c.

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

Матрица запросов для индекса (tenant_id, status, created_at DESC)

Последняя строка особенно показательна. В индексе даты упорядочены отдельно внутри каждого статуса. Чтобы получить общую ленту заказов арендатора по времени, базе придётся объединить несколько групп и отсортировать результат. Для такой ленты лучше подходит индекс (tenant_id, created_at DESC).

Правило левого префикса относится прежде всего к B-tree

Большинство составных индексов на числах, строках, датах и идентификаторах используют B-tree. Именно для него порядок колонок задаёт лексикографическую сортировку ключей и делает ведущую часть особенно важной.

PostgreSQL поддерживает многоколоночные индексы нескольких типов:

• B-tree — ведущие равенства и первый диапазон определяют основной участок сканирования;
• GiST — условия по любым колонкам допустимы, но селективность первой колонки сильнее влияет на объём работы;
• GIN — условия могут обращаться к любой части многоколоночного индекса, эффективность поиска меньше зависит от позиции колонки;
• BRIN — каждая колонка описывает диапазоны физических блоков, а условия могут использовать разные части ключа.

Например, многоколоночный GIN может объединять полнотекстовый вектор и массив тегов, а BRIN — дату и последовательный идентификатор в огромном журнале. Их внутренняя логика отличается от обычного индекса (tenant_id, status, created_at).

Дальнейшие правила о равенствах, диапазонах, ORDER BY и левом префиксе относятся к B-tree. Это основной тип для транзакционных таблиц и самый частый источник ошибок при выборе порядка колонок.

Порядок условий в WHERE не меняет порядок колонок индекса

Эти запросы логически эквивалентны:

SELECT *
FROM orders
WHERE tenant_id = 7
  AND status = 'paid';

SELECT *
FROM orders
WHERE status = 'paid'
  AND tenant_id = 7;

Оптимизатор рассматривает предикаты как набор условий. Перестановка строк в WHERE не превращает индекс (tenant_id, status) в индекс (status, tenant_id) и обычно не меняет план.

Порядок важен в определении индекса:

CREATE INDEX idx_orders_tenant_status
    ON orders (tenant_id, status);

и в последовательности сортировки:

ORDER BY tenant_id, status

Форматирование самого WHERE можно выбирать по читаемости кода.

Равенства идут перед первым диапазоном

Практичное правило для B-tree выглядит так:

1. колонки с точным равенством;
2. колонка с диапазоном;
3. колонки, нужные для продолжения сортировки;
4. поля покрытия, если движок поддерживает отдельные included columns.

Рассмотрим запрос:

SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 50;

Подходящий индекс:

CREATE INDEX idx_orders_tenant_status_created
    ON orders (tenant_id, status, created_at DESC);

tenant_id и status проверяются равенством. Они фиксируют конкретную область дерева. created_at задаёт границу диапазона и одновременно отдаёт строки в нужном порядке.

Теперь добавим ещё одно условие:

WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= '2026-07-01'
  AND total_amount >= 10000

Индекс:

(tenant_id, status, created_at, total_amount)

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

Индекс:

(tenant_id, status, total_amount, created_at)

лучше сужает поиск по сумме, когда сумма является главным диапазоном. При этом он теряет естественный порядок по created_at после диапазона total_amount.

Выбор зависит от реального запроса: какой диапазон важнее, сколько строк он отсекает и требуется ли ORDER BY ... LIMIT.

Правило «самая селективная колонка первой» часто приводит к неверному индексу

Селективность показывает, насколько условие сокращает число строк. Уникальный email очень селективен. Булевый is_active обычно делит таблицу на две крупные группы.

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

Представим многопользовательскую таблицу:

CREATE TABLE documents (
    id           BIGINT PRIMARY KEY,
    tenant_id    BIGINT NOT NULL,
    external_id  UUID NOT NULL,
    title        TEXT NOT NULL,
    created_at   TIMESTAMP NOT NULL
);

external_id почти уникален во всей таблице, а tenant_id повторяется много раз. Приложение всегда проверяет принадлежность документа организации:

SELECT *
FROM documents
WHERE tenant_id = 42
  AND external_id = '2ca6b1d0-06f8-4fad-a063-16f5cd24ef55';

Индекс (tenant_id, external_id) логичен по нескольким причинам:

• он обслуживает поиск всех документов организации по tenant_id;
• он поддерживает составное ограничение уникальности внутри организации;
• порядок совпадает с моделью доступа и изоляцией арендаторов;
• оба равенства вместе приводят к одной строке.

Индекс (external_id, tenant_id) тоже быстро найдёт точное совпадение, но не поможет запросам, которые начинаются только с tenant_id.

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

Один составной индекс и несколько одиночных индексов решают разные задачи

Сравним две схемы.

Вариант с одиночными индексами:

CREATE INDEX idx_orders_tenant ON orders (tenant_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created ON orders (created_at);

Вариант с составным индексом:

CREATE INDEX idx_orders_tenant_status_created
    ON orders (tenant_id, status, created_at);

PostgreSQL умеет объединять отдельные индексы через bitmap AND и bitmap OR. MySQL поддерживает стратегии Index Merge. Такие планы полезны для запросов, которые встречаются в разных сочетаниях и не оправдывают отдельный составной индекс для каждой комбинации.

У объединения есть цена:

• движок сканирует несколько деревьев;
• строит промежуточные наборы ссылок на строки;
• объединяет или пересекает их;
• затем читает таблицу;
• порядок исходных индексов обычно теряется, поэтому ORDER BY требует отдельной сортировки.

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

Отдельные индексы полезнее, когда колонки часто используются независимо:

WHERE tenant_id = ?

WHERE status = ?

WHERE created_at >= ?

Составной индекс полезнее для повторяющегося шаблона:

WHERE tenant_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC

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

OR может объединить несколько индексов

Условие OR описывает несколько независимых путей к строкам:

SELECT *
FROM tickets
WHERE (tenant_id = 7 AND assignee_id = 501)
   OR (tenant_id = 7 AND reporter_id = 501);

Для первой ветки подходит:

CREATE INDEX idx_tickets_assignee
    ON tickets (tenant_id, assignee_id);

Для второй:

CREATE INDEX idx_tickets_reporter
    ON tickets (tenant_id, reporter_id);

PostgreSQL способен объединить результаты через BitmapOr. MySQL применяет Index Merge в подходящих случаях. SQLite показывает стратегию MULTI-INDEX OR, если каждая ветка имеет usable index.

Один индекс:

(tenant_id, assignee_id, reporter_id)

не заменяет два пути. Ветка по reporter_id пропускает assignee_id, поэтому не получает полноценный ведущий префикс.

Иногда запрос удобно разделить:

SELECT *
FROM tickets
WHERE tenant_id = 7
  AND assignee_id = 501

UNION

SELECT *
FROM tickets
WHERE tenant_id = 7
  AND reporter_id = 501;

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

Составной индекс ускоряет ORDER BY только при совместимом порядке

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

Индекс:

CREATE INDEX idx_messages_chat_created
    ON messages (chat_id, created_at DESC);

подходит запросу:

SELECT id, sender_id, body, created_at
FROM messages
WHERE chat_id = 9001
ORDER BY created_at DESC
LIMIT 50;

chat_id = 9001 фиксирует первую колонку. Следующие записи уже идут по убыванию created_at, и движок может остановиться после первых 50 строк.

LIMIT усиливает пользу правильной сортировки

Без подходящего индекса база может:

1. найти все сообщения чата;
2. отсортировать их по времени;
3. оставить 50 строк.

С подходящим индексом она читает начало готового диапазона и прекращает работу после достижения лимита. Разница растёт вместе с размером истории.

Смешанное направление сортировки требует точного определения

PostgreSQL и современные версии MySQL поддерживают направления ASC и DESC в B-tree. Индекс:

CREATE INDEX idx_events_project_priority_created
    ON events (project_id ASC, priority DESC, created_at ASC);

может соответствовать запросу:

WHERE project_id = ?
ORDER BY priority DESC, created_at ASC;

Простой индекс (project_id, priority, created_at) можно сканировать вперёд или назад. Обратное сканирование меняет направление всех сортируемых ключей одновременно. Оно не превращает (priority ASC, created_at ASC) в (priority DESC, created_at ASC). Для смешанного порядка направление колонок приходится задавать явно.

Пропущенная колонка разрушает нужный порядок

Индекс:

(tenant_id, status, created_at DESC)

не выдаёт глобальную сортировку по created_at для такого запроса:

WHERE tenant_id = 7
ORDER BY created_at DESC;

Внутри tenant_id = 7 строки сначала разбиты по status. Даты упорядочены только внутри каждой статусной группы.

Фильтрация и сортировка должны рассматриваться как единый шаблон

Частая ошибка — создавать один индекс под WHERE, а второй под ORDER BY, хотя запрос почти всегда использует их вместе.

Таблица задач:

CREATE TABLE tasks (
    id          BIGINT PRIMARY KEY,
    project_id  BIGINT NOT NULL,
    assignee_id BIGINT,
    status      VARCHAR(32) NOT NULL,
    priority    INTEGER NOT NULL,
    due_at      TIMESTAMP,
    created_at  TIMESTAMP NOT NULL
);

Запрос очереди исполнителя:

SELECT id, priority, due_at
FROM tasks
WHERE project_id = 10
  AND assignee_id = 501
  AND status = 'open'
ORDER BY priority DESC, due_at ASC
LIMIT 100;

Подходящий индекс:

CREATE INDEX idx_tasks_queue
    ON tasks (
        project_id,
        assignee_id,
        status,
        priority DESC,
        due_at ASC
    );

Первые три поля фиксируются равенствами. Следующие два задают порядок выдачи. Если due_at допускает NULL, нужно проверить фактическое расположение NULL в выбранной СУБД и при необходимости указать NULLS FIRST или NULLS LAST в PostgreSQL.

Если в приложении часто запрашивается очередь проекта без assignee_id, этот индекс не обеспечит ожидаемую сортировку по приоритету: между project_id и priority останутся assignee_id и status. Для второго шаблона может потребоваться отдельный индекс.

Составные индексы для JOIN начинаются с колонок связи

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

Пример:

SELECT o.id, o.created_at, i.product_id, i.quantity
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.tenant_id = 7
  AND o.created_at >= '2026-07-01';

Для order_items базовый индекс:

CREATE INDEX idx_order_items_order
    ON order_items (order_id);

Если внутри заказа часто ищутся позиции конкретного товара:

SELECT *
FROM order_items
WHERE order_id = ?
  AND product_id = ?;

подходит составной индекс:

CREATE INDEX idx_order_items_order_product
    ON order_items (order_id, product_id);

Короткий индекс (order_id) может стать избыточным, потому что более длинный начинается с той же колонки. Перед удалением нужно проверить:

• ограничения внешнего ключа;
• планы запросов;
• размер обоих индексов;
• особенности конкретного движка;
• статистику использования.

Для соединения по нескольким колонкам порядок должен совпадать с ведущей частью индекса:

JOIN prices AS p
  ON p.tenant_id = oi.tenant_id
 AND p.product_id = oi.product_id
 AND p.currency = o.currency

CREATE INDEX idx_prices_lookup
    ON prices (tenant_id, product_id, currency);

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

GROUP BY и DISTINCT используют упорядоченный левый фрагмент индекса

Индекс иногда помогает группировке и устранению дубликатов, когда порядок групп совпадает с левым префиксом.

Индекс:

CREATE INDEX idx_events_tenant_type_created
    ON events (tenant_id, event_type, created_at);

может быть полезен для:

SELECT tenant_id, event_type, COUNT(*)
FROM events
GROUP BY tenant_id, event_type;

или:

SELECT DISTINCT tenant_id, event_type
FROM events;

Группировка только по event_type пропускает ведущий tenant_id. Движок может выбрать иной план.

Запрос с фиксированным арендатором:

SELECT event_type, COUNT(*)
FROM events
WHERE tenant_id = 7
GROUP BY event_type;

лучше согласуется с индексом: равенство по tenant_id оставляет данные упорядоченными по event_type.

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

Покрывающий индекс отдаёт запросу все нужные колонки

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

Запрос:

SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 7
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

PostgreSQL использует INCLUDE для полезной нагрузки

CREATE INDEX idx_orders_paid_feed
    ON orders (tenant_id, status, created_at DESC)
    INCLUDE (id, total_amount);

tenant_id, status и created_at участвуют в поиске и сортировке. id и total_amount хранятся как payload: они доступны для index-only scan, но не определяют порядок дерева.

PostgreSQL всё равно проверяет видимость строк через MVCC. Index-only scan приносит максимальную пользу на страницах, которые достаточно давно не изменялись и отмечены в visibility map. На активно обновляемой таблице часть обращений к heap сохранится.

MySQL включает дополнительные поля в обычный ключ

У MySQL нет отдельного синтаксиса INCLUDE. Покрывающий индекс создаётся добавлением возвращаемых колонок:

CREATE INDEX idx_orders_paid_feed
    ON orders (tenant_id, status, created_at DESC, total_amount);

Для InnoDB значение первичного ключа уже хранится в каждой записи вторичного индекса. Если id является первичным ключом, его часто не требуется явно добавлять ради покрытия.

Дополнительные поля в MySQL остаются частями ключа. Они увеличивают размер записи и могут влиять на доступные префиксы, лимиты длины и стоимость записи.

SQLite использует обычный многоколоночный covering index

CREATE INDEX idx_orders_paid_feed
    ON orders (tenant_id, status, created_at DESC, total_amount);

Если запрос читает только перечисленные значения и rowid, SQLite может выполнить поиск по индексу без дополнительного обращения к таблице.

Широкое покрытие окупается только для частых чтений

Каждая добавленная колонка:

• увеличивает размер индекса на диске;
• уменьшает число ключей на странице;
• повышает расход памяти кеша;
• удорожает INSERT;
• удорожает UPDATE индексируемых полей;
• увеличивает объём журнала транзакций и репликации;
• может повысить глубину B-tree.

Поля TEXT, JSON-документы, длинные URL и крупные строки редко стоит добавлять только ради покрытия. Выигрыш нужно подтверждать планом и замерами.

Составной UNIQUE обеспечивает уникальность комбинации

Составной уникальный индекс запрещает повторение полного набора значений:

CREATE UNIQUE INDEX uq_users_tenant_email
    ON users (tenant_id, email);

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

(tenant_id = 1, email = 'user@example.com')  — допустимо
(tenant_id = 2, email = 'user@example.com')  — допустимо
(tenant_id = 1, email = 'user@example.com')  — конфликт

Для бизнес-правил предпочтительнее объявлять ограничение в схеме:

ALTER TABLE users
ADD CONSTRAINT uq_users_tenant_email
UNIQUE (tenant_id, email);

СУБД создаст индекс, необходимый для проверки ограничения.

NULL меняет поведение уникальности

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

PostgreSQL поддерживает NULLS NOT DISTINCT:

CREATE UNIQUE INDEX uq_profiles_tenant_username
    ON profiles (tenant_id, username)
    NULLS NOT DISTINCT;

Такой индекс считает NULL равным NULL для проверки уникальности.

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

Частичный составной индекс хранит только нужные строки

PostgreSQL и SQLite поддерживают partial indexes. Индекс содержит строки, удовлетворяющие предикату.

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

CREATE INDEX idx_orders_active_queue
    ON orders (tenant_id, created_at DESC)
    WHERE status IN ('new', 'paid', 'processing');

Запрос:

SELECT id, status, created_at
FROM orders
WHERE tenant_id = 7
  AND status IN ('new', 'paid', 'processing')
ORDER BY created_at DESC
LIMIT 100;

может работать с компактным индексом, в котором отсутствуют миллионы завершённых заказов.

Преимущества:

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

Условие запроса должно логически соответствовать предикату индекса. PostgreSQL сопоставляет предикаты на этапе планирования и не доказывает произвольную математическую эквивалентность выражений. Параметризованные условия иногда мешают распознать подходящий partial index.

MySQL 8.4 не поддерживает partial indexes в синтаксисе PostgreSQL. Похожий сценарий можно реализовать через generated column и индекс по ней, но структура запроса и стоимость записи будут другими.

Индексы на выражениях решают проблему функций в WHERE

Обычный индекс по email часто не помогает запросу:

WHERE LOWER(email) = 'user@example.com'

База должна вычислить LOWER(email) для строк, если у неё нет индекса на таком выражении.

PostgreSQL:

CREATE INDEX idx_users_tenant_lower_email
    ON users (tenant_id, LOWER(email));

SQLite:

CREATE INDEX idx_users_tenant_lower_email
    ON users (tenant_id, LOWER(email));

MySQL поддерживает функциональные индексы и индексы по generated columns. Вариант с generated column:

ALTER TABLE users
ADD COLUMN email_normalized VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(email)) STORED,
ADD INDEX idx_users_tenant_lower_email
    (tenant_id, email_normalized);

Запрос должен использовать выражение или сгенерированную колонку в форме, которую оптимизатор способен сопоставить с индексом.

Подобные индексы подходят для:

• нормализованного email;
• даты, извлечённой из временной метки;
• ключа из JSON;
• вычисляемого статуса;
• округлённого значения;
• префикса или нормализованного идентификатора.

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

IN, BETWEEN, LIKE и NULL по-разному влияют на продолжение ключа

IN часто работает как набор равенств

Запрос:

WHERE tenant_id = 7
  AND status IN ('paid', 'shipped')
  AND created_at >= '2026-07-01'

может выполнять несколько диапазонных проходов по индексу (tenant_id, status, created_at): отдельно для каждого значения status. Реальный план зависит от размера списка, статистики и движка.

Очень длинный IN способен превратиться в дорогую серию поисков. Иногда временная таблица и JOIN дают более устойчивый план.

BETWEEN является диапазоном

WHERE tenant_id = 7
  AND created_at BETWEEN '2026-07-01' AND '2026-07-31'

хорошо соответствует индексу (tenant_id, created_at).

Следующая колонка после диапазона обычно не сужает один непрерывный участок B-tree:

(tenant_id, created_at, status)

для запроса:

WHERE tenant_id = 7
  AND created_at BETWEEN ...
  AND status = 'paid'

Сначала выбирается временной интервал арендатора. status проверяется внутри него.

Если очередь почти всегда фильтруется по статусу, индекс (tenant_id, status, created_at) обычно лучше.

LIKE использует B-tree при известном начале строки

Условие:

WHERE tenant_id = 7
  AND username LIKE 'alex%'

может использовать индекс (tenant_id, username), потому что известен начальный диапазон строк.

Условие:

username LIKE '%alex%'

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

Коллация, регистр и операторный класс могут менять применимость индекса. Это особенно заметно в PostgreSQL при локализованных сравнениях и в MySQL при разных collations.

IS NULL способен использовать индекс

NULL хранится в B-tree, и запросы вида:

WHERE tenant_id = 7
  AND deleted_at IS NULL

могут использовать индекс (tenant_id, deleted_at).

Для выборки только активных строк в PostgreSQL и SQLite часто компактнее partial index:

CREATE INDEX idx_documents_active
    ON documents (tenant_id, updated_at DESC)
    WHERE deleted_at IS NULL;

Skip scan помогает отдельным запросам, но не заменяет правильный порядок

PostgreSQL 18 документирует skip scan для многоколоночных B-tree. При индексе (x, y) и условии WHERE y = 7700 оптимизатор может перебрать отдельные значения x, выполняя внутренние поиски вида:

x = первое_значение AND y = 7700
x = второе_значение AND y = 7700
x = третье_значение AND y = 7700

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

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

Skip scan стоит воспринимать как дополнительную оптимизацию конкретного движка. Индекс под важный запрос по y всё равно лучше начинать с y, если запрос регулярно работает без ограничения по x.

MySQL и SQLite имеют собственные оптимизации пропущенных частей ключа в отдельных сценариях. Они зависят от версии, статистики и распределения данных. Базовая схема проектирования остаётся прежней: частые запросы должны иметь полезный ведущий префикс.

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

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

INSERT добавляет запись во все подходящие индексы. DELETE удаляет или помечает соответствующие записи. UPDATE индексируемой колонки меняет ключ. Даже обновление другого поля может создавать дополнительные версии строк и влиять на обслуживание индексов в MVCC-системах.

Индекс:

(tenant_id, status, created_at, total_amount, customer_id)

занимает больше места, чем:

(tenant_id, status, created_at)

Широкий ключ уменьшает плотность страниц. Для того же числа строк требуется больше страниц, больше операций чтения и больше места в кеше.

В InnoDB каждая запись вторичного индекса содержит первичный ключ. Длинный составной primary key автоматически увеличивает все вторичные индексы. Поэтому компактный первичный ключ способен заметно сократить общий объём таблицы.

PostgreSQL допускает до 32 колонок в многоколоночном индексе с учётом INCLUDE в стандартной сборке. Документация прямо предупреждает, что индексы более чем с тремя ключевыми колонками редко полезны без строго повторяющегося шаблона доступа. InnoDB допускает до 16 частей многоколоночного индекса. Технический лимит не является рекомендацией заполнять индекс до максимума.

Префиксные дубликаты часто можно удалить

Если существуют индексы:

idx_a (tenant_id)
idx_b (tenant_id, status)
idx_c (tenant_id, status, created_at)

длинный индекс содержит левый префикс коротких. Некоторые короткие индексы могут стать лишними.

Удаление нельзя выполнять автоматически по одному совпадению колонок. Нужно проверить различия:

• UNIQUE и обычный индекс;
• направление ASC и DESC;
• NULLS FIRST и NULLS LAST;
• operator class и collation;
• partial predicate;
• выражения;
• INCLUDE;
• размер ключа;
• планы запросов;
• влияние на внешние ключи;
• частоту использования;
• стоимость сканирования длинного индекса для короткого запроса.

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

Длинный индекс не всегда равноценен короткому

Запрос по одному tenant_id может прочитать меньше страниц из компактного (tenant_id), чем из широкого (tenant_id, status, created_at, total_amount). На крупных таблицах короткий индекс иногда сохраняют ради часто выполняемой операции.

Сначала измеряют:

• размер индекса;
• число чтений;
• задержку запроса;
• объём записи;
• попадание в кеш;
• изменение плана после удаления на тестовом окружении.

EXPLAIN показывает фактическую роль каждой части индекса

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

PostgreSQL — EXPLAIN ANALYZE и BUFFERS

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 50;

Ищем в плане:

• Index Scan или Index Only Scan;
• имя выбранного индекса;
• Index Cond — условия, которые ограничивают поиск;
• Filter — условия, применённые после получения кандидатов;
• Rows Removed by Filter;
• отдельный узел Sort;
• Heap Fetches для index-only scan;
• фактическое и оценочное число строк;
• shared hit и shared read в блоке BUFFERS.

Пример хорошего фрагмента:

Index Scan using idx_orders_tenant_status_created on orders
  Index Cond: (
      tenant_id = 7
      AND status = 'paid'
      AND created_at >= '2026-07-01'
  )

Если status оказался в Filter, индекс или тип условия не позволили использовать его как границу сканирования.

EXPLAIN ANALYZE выполняет запрос. Для UPDATE, DELETE и других изменяющих операций его нельзя запускать без безопасной транзакции и понимания последствий.

MySQL — EXPLAIN ANALYZE и key_len

EXPLAIN ANALYZE
SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 50;

В классическом EXPLAIN полезны поля:

• possible_keys — индексы-кандидаты;
• key — выбранный индекс;
• key_len — длина реально используемой части ключа;
• ref — значения или колонки, сравниваемые с ключом;
• rows — оценка просмотренных строк;
• filtered — ожидаемый процент строк после фильтра;
• Extra: Using index — покрывающий доступ;
• Extra: Using index condition — Index Condition Pushdown;
• Extra: Using filesort — отдельная сортировка.

key_len помогает понять, сколько ведущих частей составного индекса участвует в поиске. Точное число байтов зависит от типов, длины строк, кодировки и возможности NULL.

SQLite — EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN
SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 50;

Ожидаемый смысл вывода:

SEARCH orders USING INDEX idx_orders_tenant_status_created
(tenant_id=? AND status=? AND created_at>?)

Если индекс покрывает запрос, SQLite укажет USING COVERING INDEX.

Строка:

USE TEMP B-TREE FOR ORDER BY

показывает, что порядок индекса не соответствует сортировке и SQLite создаёт временную структуру.

SCAN ... USING INDEX и SEARCH ... USING INDEX имеют разный смысл. SEARCH использует ограничения для перехода к диапазону. SCAN может читать индекс целиком как более компактную замену таблице.

Статистика определяет выбор между подходящими индексами

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

PostgreSQL обновляет статистику через ANALYZE, обычно в рамках autovacuum:

ANALYZE orders;

MySQL:

ANALYZE TABLE orders;

SQLite:

ANALYZE;

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

Коррелированные колонки искажают простые оценки

В таблице заказов status и paid_at связаны: оплаченные заказы обычно имеют paid_at, новые — нет. Если оптимизатор оценивает условия независимо, он может ошибиться в числе строк:

WHERE status = 'paid'
  AND paid_at IS NOT NULL

PostgreSQL поддерживает расширенную статистику по нескольким колонкам:

CREATE STATISTICS st_orders_status_paid_at
    (dependencies, mcv)
    ON status, paid_at
    FROM orders;

ANALYZE orders;

Она не заменяет составной индекс. Её задача — помочь планировщику точнее оценить совместное распределение значений и выбрать правильный план.

Рабочий алгоритм проектирования составного индекса

Шаг 1. Соберите реальные запросы

Нужен список конкретных SQL-шаблонов с частотой и задержкой:

Q1 — 12 000 раз/мин, p95 180 мс
Q2 — 400 раз/мин, p95 2,4 с
Q3 — 20 раз/день, p95 8 с

Q1 может принести больше общей нагрузки, даже если отдельный запуск Q3 медленнее.

Источники:

• slow query log;
• pg_stat_statements;
• Performance Schema;
• APM;
• трассировка ORM;
• журналы приложения;
• планы запросов из production-like среды.

Шаг 2. Разделите условия по типу

Для каждого запроса выпишите:

Равенства: tenant_id, status
Диапазон: created_at >= ...
Сортировка: created_at DESC
Возвращаемые поля: id, total_amount, created_at
Лимит: 50

Шаг 3. Сформируйте ключевой порядок

Базовый кандидат:

(равенства, первый важный диапазон, оставшаяся сортировка)

Для примера:

(tenant_id, status, created_at DESC)

Шаг 4. Проверьте другие запросы на левый префикс

Индекс может обслуживать семейство:

WHERE tenant_id = ?

WHERE tenant_id = ? AND status = ?

WHERE tenant_id = ? AND status = ? AND created_at >= ?

Запрос по одному status потребует другого решения.

Шаг 5. Решите вопрос покрытия

Добавляйте payload только при подтверждённой пользе:

PostgreSQL:

INCLUDE (id, total_amount)

MySQL и SQLite:

(..., id, total_amount)

Проверьте ширину, частоту обновления и план index-only/covering scan.

Шаг 6. Сравните альтернативы через EXPLAIN

Минимум три варианта:

1. текущая схема;
2. новый составной индекс;
3. отдельные одиночные индексы или другой порядок колонок.

Сравнивайте фактические строки, чтения страниц, сортировку, heap/table lookups и общее время.

Шаг 7. Измерьте стоимость записи

Нагрузочный тест должен включать:

• INSERT;
• массовые загрузки;
• обновление индексируемых колонок;
• удаление;
• репликацию;
• размер WAL/binlog;
• время создания индекса;
• расход диска.

Шаг 8. Наблюдайте после внедрения

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

Практический пример — лента заказов в SaaS

Создадим таблицу:

CREATE TABLE orders (
    id            BIGINT PRIMARY KEY,
    tenant_id     BIGINT NOT NULL,
    customer_id   BIGINT NOT NULL,
    status        VARCHAR(32) NOT NULL,
    total_amount  DECIMAL(12, 2) NOT NULL,
    created_at    TIMESTAMP NOT NULL,
    paid_at       TIMESTAMP NULL
);

Главный запрос:

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = :tenant_id
  AND status = :status
  AND created_at < :cursor
ORDER BY created_at DESC, id DESC
LIMIT 50;

Это keyset pagination. Курсор содержит время и идентификатор последней строки. Для стабильного порядка нужен id как второй сортировочный ключ, потому что несколько заказов могут иметь одинаковый created_at.

PostgreSQL

CREATE INDEX CONCURRENTLY idx_orders_feed
    ON orders (
        tenant_id,
        status,
        created_at DESC,
        id DESC
    )
    INCLUDE (customer_id, total_amount);

Запрос с составным курсором:

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = :tenant_id
  AND status = :status
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

MySQL

CREATE INDEX idx_orders_feed
    ON orders (
        tenant_id,
        status,
        created_at DESC,
        id DESC,
        customer_id,
        total_amount
    );

InnoDB уже хранит primary key во вторичном индексе. Явное добавление id DESC здесь нужно для порядка курсора, а не для простого получения идентификатора.

SQLite

CREATE INDEX idx_orders_feed
    ON orders (
        tenant_id,
        status,
        created_at DESC,
        id DESC,
        customer_id,
        total_amount
    );

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

Отдельный индекс для общей ленты арендатора

Запрос без статуса:

SELECT id, status, created_at
FROM orders
WHERE tenant_id = :tenant_id
  AND (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

плохо совпадает с индексом, где между tenant_id и датой расположен status. Для общей ленты нужен кандидат:

CREATE INDEX idx_orders_tenant_feed
    ON orders (tenant_id, created_at DESC, id DESC);

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

Практический пример — последние сообщения чата

Таблица:

CREATE TABLE messages (
    id          BIGINT PRIMARY KEY,
    chat_id     BIGINT NOT NULL,
    sender_id   BIGINT NOT NULL,
    body        TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL,
    deleted_at  TIMESTAMP NULL
);

Запрос:

SELECT id, sender_id, body, created_at
FROM messages
WHERE chat_id = :chat_id
  AND deleted_at IS NULL
  AND id < :cursor_id
ORDER BY id DESC
LIMIT 50;

Если id монотонно растёт и задаёт порядок сообщений, кандидат:

CREATE INDEX idx_messages_chat_id
    ON messages (chat_id, id DESC);

В PostgreSQL и SQLite можно исключить удалённые сообщения:

CREATE INDEX idx_messages_active_chat_id
    ON messages (chat_id, id DESC)
    WHERE deleted_at IS NULL;

Добавлять body ради покрытия рискованно: текст сообщений резко увеличит индекс. Обычно индекс помогает найти 50 строк, после чего чтение 50 записей таблицы остаётся приемлемым.

Если приложение сортирует по created_at, а курсор использует только id, возможны расхождения после импорта или миграции. Индекс и курсор должны отражать фактическое правило порядка.

Практический пример — журнал событий с несколькими режимами поиска

Таблица:

CREATE TABLE audit_events (
    id           BIGINT PRIMARY KEY,
    tenant_id    BIGINT NOT NULL,
    actor_id     BIGINT,
    event_type   VARCHAR(64) NOT NULL,
    object_type  VARCHAR(64) NOT NULL,
    object_id    BIGINT,
    created_at   TIMESTAMP NOT NULL,
    payload      JSON
);

Основные запросы:

-- Лента организации
WHERE tenant_id = ?
ORDER BY created_at DESC
LIMIT 100

-- История объекта
WHERE tenant_id = ?
  AND object_type = ?
  AND object_id = ?
ORDER BY created_at DESC

-- Действия пользователя
WHERE tenant_id = ?
  AND actor_id = ?
ORDER BY created_at DESC

Один широкий индекс:

(tenant_id, object_type, object_id, actor_id, created_at)

не обслуживает все три режима одинаково. actor_id расположен после полей объекта, а общая лента теряет прямой порядок по времени.

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

CREATE INDEX idx_audit_tenant_created
    ON audit_events (tenant_id, created_at DESC);

CREATE INDEX idx_audit_object_created
    ON audit_events (
        tenant_id,
        object_type,
        object_id,
        created_at DESC
    );

CREATE INDEX idx_audit_actor_created
    ON audit_events (tenant_id, actor_id, created_at DESC);

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

Laravel создаёт составные индексы в миграциях одним вызовом

Обычный индекс:

Schema::table('orders', function (Blueprint $table) {
    $table->index(
        ['tenant_id', 'status', 'created_at'],
        'idx_orders_tenant_status_created'
    );
});

Составная уникальность:

Schema::table('users', function (Blueprint $table) {
    $table->unique(
        ['tenant_id', 'email'],
        'uq_users_tenant_email'
    );
});

Удаление:

Schema::table('orders', function (Blueprint $table) {
    $table->dropIndex('idx_orders_tenant_status_created');
});

Schema Builder не выражает все возможности PostgreSQL: INCLUDE, partial predicate, operator classes, NULLS NOT DISTINCT, CONCURRENTLY и смешанное направление иногда удобнее задавать через сырой SQL.

Пример для PostgreSQL:

DB::statement(<<<'SQL'
    CREATE INDEX CONCURRENTLY idx_orders_active_feed
    ON orders (tenant_id, created_at DESC, id DESC)
    INCLUDE (status, total_amount)
    WHERE status IN ('new', 'paid', 'processing')
SQL);

CREATE INDEX CONCURRENTLY в PostgreSQL нельзя выполнять внутри обычного transaction block. Миграцию нужно настроить с учётом механизма транзакций конкретной версии Laravel и драйвера.

Создание индекса на большой таблице требует плана внедрения

Обычный CREATE INDEX может долго читать таблицу, сортировать ключи, использовать временное место и блокировать часть операций.

PostgreSQL

Для production часто применяют:

CREATE INDEX CONCURRENTLY idx_orders_tenant_created
    ON orders (tenant_id, created_at DESC);

Запись остаётся доступной, но построение выполняет дополнительные проходы и занимает больше времени. При ошибке может остаться invalid index, который придётся удалить или перестроить.

Перед запуском проверяют:

• свободный диск;
• длительные транзакции;
• нагрузку на I/O;
• репликационный лаг;
• maintenance_work_mem;
• мониторинг pg_stat_progress_create_index;
• возможность повторного запуска.

MySQL

InnoDB поддерживает online DDL для многих операций с индексами. Фактический алгоритм и блокировки зависят от версии, типа операции и определения таблицы. Полезно явно проверять допустимость:

ALTER TABLE orders
    ADD INDEX idx_orders_tenant_created (tenant_id, created_at DESC),
    ALGORITHM=INPLACE,
    LOCK=NONE;

Если комбинация не поддерживается, MySQL вернёт ошибку вместо незаметного перехода к более блокирующему режиму.

SQLite

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

Типичные ошибки при проектировании

Индекс копирует порядок колонок из таблицы

Физический порядок столбцов в CREATE TABLE не связан с оптимальным порядком ключа. Индекс строится по запросам.

В индекс добавляются все поля из WHERE

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

Создаётся один огромный индекс для всех запросов

Разные ведущие фильтры требуют разных левых префиксов. Универсальный индекс быстро превращается в широкую структуру с высоким расходом записи и слабой пользой для части запросов.

Игнорируется ORDER BY

Индекс хорошо фильтрует строки, но запрос всё равно сортирует сотни тысяч результатов. Для ORDER BY ... LIMIT порядок часто важнее добавления очередного фильтрующего поля справа.

Покрытие включает крупные данные

Добавление body, payload, JSON или длинного описания удваивает объём хранения и снижает плотность страниц. Небольшое число table lookups может быть дешевле.

Индекс создаётся для запроса, возвращающего половину таблицы

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

После создания не запускается EXPLAIN

Наличие индекса в схеме не подтверждает его использование. План показывает фактическую границу поиска, сортировку и объём фильтрации.

Индексы добавляются без удаления устаревших

Схема постепенно накапливает перекрывающиеся деревья. Они расходуют диск и замедляют запись, хотя реальные запросы давно изменились.

Тест выполняется на маленькой пустой базе

На тысяче строк оптимизатор часто выбирает последовательное сканирование, а различие между вариантами скрывается в миллисекундах. Нужны данные, близкие к production по объёму и распределению.

Таблица выбора порядка колонок

Чек-лист перед добавлением составного индекса

1. Записан точный SQL, включая JOIN, ORDER BY, LIMIT и выбираемые поля.
2. Известна частота запроса и его вклад в общую нагрузку.
3. Равенства отделены от диапазонов.
4. Выбран первый диапазон, который должен ограничивать сканирование.
5. Проверено соответствие сортировке.
6. Проверены запросы по каждому левому префиксу.
7. Оценена необходимость отдельного индекса для пропущенной ведущей колонки.
8. Решено, нужно ли покрытие.
9. Проверен размер добавляемых полей.
10. Учтено автоматическое хранение primary key во вторичных индексах InnoDB.
11. Проверены NULL, collation и типы сравниваемых колонок.
12. Рассмотрен partial index для небольшой активной части данных.
13. Проверены дублирующие и префиксные индексы.
14. Выполнен EXPLAIN на данных, близких к production.
15. Измерена стоимость INSERT, UPDATE и репликации.
16. Подготовлен безопасный способ создания на большой таблице.
17. Настроено наблюдение за планом и использованием после внедрения.

Составной индекс должен отражать путь к данным

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

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

Для PostgreSQL, MySQL и SQLite базовая логика B-tree остаётся общей, но детали различаются: PostgreSQL предлагает INCLUDE, partial indexes, расширенную статистику и skip scan; InnoDB хранит primary key во вторичных индексах; SQLite прямо показывает covering index и временную B-tree для сортировки. Поэтому перенос индекса между СУБД требует повторной проверки плана.

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

Официальная документация

• PostgreSQL — Multicolumn Indexes
• PostgreSQL — Indexes and ORDER BY
• PostgreSQL — Index-Only Scans and Covering Indexes
• PostgreSQL — Partial Indexes
• MySQL 8.4 — How MySQL Uses Indexes
• MySQL 8.4 — EXPLAIN Output Format
• MySQL 8.4 — Clustered and Secondary Indexes
• SQLite — Query Planning
• SQLite — EXPLAIN QUERY PLAN