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

Мы спроектировали нормализованную OLTP-схему, защитили её ограничениями и разобрались с конкурентностью. Осталась последняя тема модуля — индексы. Индексы — это не «настройка производительности, которую делают потом администраторы». То, какие индексы понадобятся и насколько они будут эффективны, во многом предопределяется на этапе моделирования — выбором ключей, типов столбцов, структуры связей.

В этом уроке мы разберём, как устроен B-tree индекс, какие индексы обязательны для OLTP-схемы, и — главное — какие решения моделирования влияют на индексы и должны быть приняты заранее.

Зачем нужен индекс и как он устроен

Без индекса найти строку по значению столбца можно только одним способом — полным сканированием таблицы (full table scan): СУБД читает все строки подряд и проверяет каждую. На таблице в миллион строк это миллион проверок на каждый запрос. Для OLTP, где запросы точечные и их тысячи в секунду, это неприемлемо.

Индекс — это вспомогательная структура, которая позволяет находить строки по значению быстро, не сканируя всю таблицу. Самый распространённый тип — B-tree (сбалансированное дерево поиска).

B-tree устроен как дерево из страниц: корневая страница, промежуточные узлы, листья. В листьях лежат значения индексируемого столбца по порядку и указатели на строки таблицы. Поиск идёт от корня вниз: на каждом узле СУБД выбирает, в какую ветвь спускаться, и за несколько шагов доходит до листа с нужным значением.

Ключевое свойство: глубина B-tree растёт логарифмически от числа строк. Для миллиона строк дерево имеет всего 3-4 уровня; для миллиарда — 5-6. Поиск по индексу — это считанные обращения к страницам вместо миллиона. Разница между O(log n) (индекс) и O(n) (полный скан) — это разница между миллисекундами и секундами.

Индекс не бесплатен: он занимает место на диске и его надо поддерживать при записи. Каждый INSERT, UPDATE индексируемого столбца, DELETE обновляет не только таблицу, но и все её индексы. В write-heavy OLTP это значимая цена — поэтому индексы ставят обдуманно, а не «на каждый столбец».

Какие индексы обязательны для OLTP-схемы

Часть индексов в OLTP-схеме нужна почти всегда — и появление многих из них предопределено самой моделью.

1. Индекс на primary key — создаётся автоматически. Объявляя PRIMARY KEY, вы получаете уникальный индекс на ключ бесплатно: СУБД создаёт его сама. Он обслуживает поиск строки по идентификатору — самую частую OLTP-операцию («покажи заказ 5001»).

2. Индекс на каждый foreign key — нужно создавать вручную. Об этом был урок про referential integrity: СУБД не индексирует столбцы FK автоматически, а без индекса каждое удаление или изменение строки родителя приводит к полному сканированию дочерней таблицы в поисках детей. Плюс по FK постоянно идут JOIN-ы. Вывод: каждый внешний ключ в модели — это запланированный индекс.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

3. Индекс на UNIQUE-столбцы — создаётся автоматически. UNIQUE реализуется через уникальный индекс — СУБД создаёт его сама. Он же обслуживает поиск по этому столбцу (например, по email).

4. Индексы на столбцы частых условий поиска и сортировки. Если приложение постоянно фильтрует заказы по status или по created_at, эти столбцы — кандидаты на индекс. Это уже не предопределено схемой жёстко, но вытекает из паттернов доступа, которые модельер должен предвидеть.

Как выбор ключей в модели влияет на индексы

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

Узкий ключ -> компактный индекс. Размер индекса и скорость поиска по нему зависят от ширины ключа в байтах. Узкий ключ — INTEGER (4 байта) или BIGINT (8 байт) — даёт компактный индекс: на одну страницу индекса помещается много значений, дерево мельче, поиск быстрее, индекс целиком влезает в память. Широкий ключ — длинная строка или составной ключ из нескольких текстовых столбцов — раздувает индекс: меньше значений на страницу, дерево глубже, больше обращений к диску. Это прямой аргумент в пользу узких surrogate keys.

Surrogate key vs естественный составной ключ. Если в качестве первичного ключа взять естественный составной ключ (скажем, {country, region, city, street, house}), то этот ключ войдёт в индекс PK, а заодно во все внешние ключи, которые на него ссылаются, и в их индексы. Каждый JOIN будет сравнивать длинные составные значения. Узкий surrogate key (один BIGINT) делает и индекс PK, и все ссылающиеся FK-индексы компактными, а сравнение при JOIN — одной машинной операцией вместо сравнения нескольких строк. Это решение принимается при моделировании ключей и его последствия для индексов необратимы без переделки схемы.

Порядок генерации ключа и фрагментация индекса. Тонкий, но важный момент. B-tree эффективнее всего, когда новые значения добавляются по возрастанию — новая строка ложится в конец дерева, структура остаётся плотной. Монотонно растущий ключ (BIGINT IDENTITY, последовательность) даёт именно такую картину. А вот случайный ключ — UUID версии 4 (полностью случайный) — вставляет новые значения в произвольные места дерева: страницы приходится расщеплять, индекс фрагментируется, раздувается, теряет локальность. Если по бизнес-причинам нужен UUID, выбирают time-ordered вариант — UUIDv7 (содержит timestamp в старших битах), который растёт по порядку и ведёт себя в индексе как монотонный integer. Выбор типа и стратегии генерации ключа — решение моделирования, и оно прямо определяет здоровье индексов.

Составные индексы и порядок столбцов. Если запросы часто фильтруют сразу по нескольким столбцам (WHERE customer_id = ? AND status = ?), эффективен составной индекс на эти столбцы. Но порядок столбцов в нём важен: составной индекс (customer_id, status) обслуживает фильтр по customer_id и по customer_id + status, но не помогает фильтру только по status. Какие составные индексы понадобятся — следует из паттернов запросов, которые модельер предвидит на этапе проектирования.

Моделирование «с прицелом на индексы»

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

• выбор узкого surrogate key вместо широкого составного — определяет компактность всех индексов схемы;
• выбор типа и стратегии генерации ключа (монотонный integer / UUIDv7 против случайного UUIDv4) — определяет, будет ли индекс плотным или фрагментированным;
• наличие внешних ключей в модели — каждый из них требует запланированного индекса;
• предвидение паттернов доступа — какие фильтры и сортировки будут частыми, какие составные индексы понадобятся.

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

Попробуй сам

Дана OLTP-схема платёжного сервиса:

ACCOUNTS(account_id PK, owner_name, balance)
PAYMENTS(payment_id PK, from_account, to_account, amount, status, created_at)

from_account и to_account — внешние ключи на ACCOUNTS. Приложение часто ищет платежи по счёту-отправителю и фильтрует по status и по диапазону created_at.

Выполните на бумаге:

1. Перечислите все индексы, которые СУБД создаст автоматически, и все, которые нужно создать вручную. Напишите для последних CREATE INDEX.
2. Для первичного ключа account_id сравните два варианта: узкий BIGINT surrogate key и естественный ключ — номер счёта в виде длинной строки. Как выбор повлияет на размер индекса PK и на индексы внешних ключей from_account/to_account?
3. Объясните, почему payment_id лучше сделать монотонно растущим (BIGINT identity или UUIDv7), а не случайным UUIDv4. Что произойдёт с B-tree индексом при случайных вставках?
4. Приложение делает запрос WHERE from_account = ? AND status = ?. Какой составной индекс это ускорит и почему порядок столбцов в нём имеет значение?