Виды индексов

Введение: Индексы бывают разными

В прошлом материале мы разобрались, что такое индекс и как он работает. Мы узнали, что индекс – это структура данных, которая ускоряет поиск, но замедляет запись.

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

Кластерный индекс (Clustered Index)

Главный порядок в таблице

Представьте, что у вас есть стопка книг на столе. Если книги сложены в случайном порядке, найти нужную трудно. А если вы сложили их в алфавитном порядке по названию, то найти книгу “Война и мир” становится легко.

Кластерный индекс – это способ физически упорядочить строки в таблице. Таблица с кластерным индексом хранит строки в отсортированном порядке по тому столбцу (или столбцам), по которому создан индекс.

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

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

Что это дает:

• Поиск по полю кластерного индекса происходит очень быстро.
• Сортировка по этому полю не требует дополнительных затрат – данные уже отсортированы.
• Поиск по диапазону (например, WHERE id BETWEEN 10 AND 20) эффективен, так как нужные строки лежат рядом.

Главный нюанс: В большинстве баз данных (например, в MySQL с движком InnoDB) первичный ключ автоматически становится кластерным индексом. Если вы не создали кластерный индекс явно, база данных может создать его неявно (например, используя скрытый служебный столбец).

Глубокое понимание кластерного индекса

Физическое устройство:

В B-Tree кластерного индекса листья дерева содержат сами строки данных, а не просто указатели на них. Это ключевое отличие от некластерного индекса.

Кластерный индекс (листья содержат строки целиком):
        [50]
       /    \
    [30]    [70]
   /   \   /   \
[20]  [40] [60]  [80]
 ↓     ↓     ↓     ↓
(строка) (строка) (строка) (строка)

Когда кластерный индекс особенно эффективен:

Когда кластерный индекс может быть проблемой:

Стратегии выбора кластерного ключа:

IDENTITY (Автоинкремент) – это целое число (INT, BIGINT), которое увеличивается на 1 с каждой новой записью. Новая запись всегда получает следующий номер (например, ... 5, 6, 7).

SEQUENCE (Последовательность) – это более гибкий аналог автоинкремента (есть в PostgreSQL, Oracle, SQL Server 2012+ и др.). Генерирует числа по заданному правилу (по умолчанию тоже +1). Аналогично IDENTITY – значения растут во времени. Плюс можно получить следующее значение заранее (кэширование), что снижает блокировки при вставке в кластерный индекс.

UUIDv7 – это универсальный уникальный идентификатор (128 бит), но версии 7 (самая важная деталь). UUIDv7 содержит временную метку Unix (миллисекунды) в первых 48 битах. Остальные биты – случайные или счетчики. В отличие от старых версий (UUIDv4 – полностью случайный), UUIDv7 сортируется по времени. Новые сгенерированные UUID всегда больше старых.

Пример плохого кластерного ключа: last_name. Фамилии не уникальны, могут меняться (при замужестве), не монотонны (новые фамилии могут попасть в середину), могут быть длинными.

Пример хорошего кластерного ключа: id INT IDENTITY(1,1). Уникальный, никогда не меняется, всегда возрастает, короткий (4 байта).

Некластерный индекс (Non-Clustered Index)

Отдельный указатель

Вернемся к аналогии с библиотекой.

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

Некластерный индекс – это отдельная структура данных, которая хранит значения индексированного столбца и указатели (ссылки) на строки в таблице. Сама таблица при этом может быть не отсортирована.

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

Что это дает:

• Можно создать индексы на разных столбцах (например, по name, по email, по city).
• Каждый индекс ускоряет поиск по своему столбцу.
• Не влияет на физический порядок строк, поэтому не замедляет вставки так сильно, как кластерный индекс.

Плата: Некластерный индекс занимает дополнительное место. При вставке строки нужно обновить все некластерные индексы.

Глубокое понимание некластерного индекса

Физическое устройство:

В B-Tree некластерного индекса листья содержат не строки целиком, а значение ключа + указатель на строку.

В таблицах без кластерного индекса указатель – это физический адрес строки (RID, CTID).

RID (SQL Server) / CTID (PostgreSQL) – встроенный физический адрес строки на диске (номер страницы + позиция на ней).

Например:

• PostgreSQL: (3, 12) — страница №3, строка №12
• SQL Server: бинарное значение (например, 0xE0A2B400)

Единственный способ найти строку в таблице-куче (без кластерного индекса). Некластерный индекс ссылается на RID/CTID.

Плюс: очень быстрый прямой доступ к строке (минуя индекс).

Минус (главный): меняется при перемещении строки (например, при UPDATE, когда строка перестала помещаться на странице).
Все некластерные индексы, ссылающиеся на старый RID, нужно обновлять, следовательно + время на обработку.

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

Некластерный индекс (листья содержат ключ + указатель на строку):
        [50]
       /    \
    [30]    [70]
   /   \   /   \
[20]  [40] [60]  [80]
 ↓     ↓     ↓     ↓
(20→указ.) (40→указ.) (60→указ.) (80→указ.)

Дорогостоящая операция: Lookup

Когда вы используете некластерный индекс для поиска, а запрос требует столбцы, которых нет в индексе, происходит lookup (также называется “обращение к таблице” или “bookmark lookup”):

1. Найти в некластерном индексе нужные значения ключа.
2. Получить указатели на строки.
3. Для каждого указателя перейти к строке в таблице и прочитать остальные столбцы.

Если индекс отобрал много строк (например, 10 000), то lookup выполнится 10 000 раз. Это может быть медленнее, чем полное сканирование таблицы, если таблица небольшая. Оптимизатор запросов учитывает это и может выбрать не индекс, а сканирование.

Когда lookup не нужен: покрывающий индекс (Covering Index)

Если индекс содержит все столбцы, которые запрашивает SELECT, то lookup не требуется. База данных может ответить на запрос, используя только индекс.

-- Если есть индекс на (city, name, id), то этот запрос может быть выполнен полностью по индексу
SELECT id, name FROM users WHERE city = 'Москва'

В этом случае индекс называется покрывающим для данного запроса.

Уникальный индекс (Unique Index)

Запрет на дублирование

Уникальный индекс – это индекс, который не позволяет хранить в столбце (или комбинации столбцов) одинаковые значения для разных строк.

Простыми словами: Уникальный индекс – это как паспортный номер. У двух людей не может быть одинакового номера паспорта.

Где применяется:

• Email пользователя (два пользователя не могут иметь одинаковый email).
• Номер заказа (каждый заказ имеет уникальный номер).
• Комбинация “дата + склад + товар” (на одном складе в один день не может быть двух одинаковых товаров).

Связь с первичным ключом: Первичный ключ (PRIMARY KEY) – это частный случай уникального индекса, который дополнительно запрещает пустые значения (NOT NULL).

Нюансы уникальных индексов

NULL и уникальность:

В стандарте SQL уникальный индекс допускает несколько строк с NULL. Потому что NULL – это не значение, а “неизвестно”. Два “неизвестно” не считаются одинаковыми.

Но! В разных СУБД поведение различается:

• SQL Server, PostgreSQL: допускают несколько NULL.
• MySQL (InnoDB): допускает несколько NULL.
• Oracle: тоже допускает несколько NULL (но с некоторыми историческими нюансами).

Если нужно запретить даже NULL, добавьте ограничение NOT NULL или используйте фильтрованный уникальный индекс (см. ниже).

Уникальный индекс для производительности:

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

Уникальный составной индекс:

Можно создать уникальный индекс по нескольким столбцам. Например, в таблице “Заказы_Товары” комбинация (order_id, product_id) должна быть уникальной – нельзя дважды добавить один и тот же товар в один заказ.

CREATE UNIQUE INDEX idx_order_product ON OrderItems (order_id, product_id);

Конфликт при вставке:

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

Составной индекс (Composite Index)

Индекс по нескольким столбцам

Составной индекс – это индекс, который включает два или более столбца.

Простыми словами: Это как телефонная книга, которая отсортирована сначала по фамилии, потом по имени. Вы можете искать человека по фамилии (быстро), а можете – по фамилии и имени вместе (еще точнее). Но искать только по имени вы не сможете – книга не отсортирована по именам.

Пример:

CREATE INDEX idx_name ON users (last_name, first_name);

Этот индекс ускорит:

• WHERE last_name = 'Петров'
• WHERE last_name = 'Петров' AND first_name = 'Иван'

Но не ускорит:

• WHERE first_name = 'Иван'

Глубокое понимание составных индексов

Правило левого префикса (Leftmost Prefix Rule):

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

Индекс (a, b, c) может быть использован для условий:

• WHERE a = ...
• WHERE a = ... AND b = ...
• WHERE a = ... AND b = ... AND c = ...
• WHERE a = ... AND c = ... (частично – только для a, фильтр по c не использует индекс)

Не может быть использован для условий:

• WHERE b = ...
• WHERE c = ...
• WHERE b = ... AND c = ...

Как выбрать порядок столбцов в составном индексе? Есть несколько стратегий.

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

Составной индекс особенно хорош для создания покрывающих индексов. Если вы часто выполняете запрос:

SELECT id, name, city FROM users WHERE city = 'Москва'

Индекс (city, id, name) позволит ответить на запрос полностью из индекса, без обращения к таблице.

Стоимость составного индекса:

• Занимает больше места, чем индекс по одному столбцу.
• При вставке/обновлении нужно обновлять все столбцы индекса.
• При большом количестве столбцов (5+) эффективность падает.

Рекомендация: Не создавайте составные индексы с более чем 3-4 столбцами без веской причины. Часто лучше создать несколько узких индексов или один покрывающий индекс для самого частого запроса.

Фильтрованный индекс (Filtered Index)

Индекс только для части данных

Фильтрованный индекс – это индекс, который включает не все строки таблицы, а только те, которые удовлетворяют определенному условию.

Простыми словами: Представьте, что в библиотеке есть отдельный каталог только для книг 2024 года издания. Искать в этом маленьком каталоге быстрее, чем в общем.

Пример: У вас есть таблица заказов. 99% заказов имеют статус “Завершен”, и только 1% – статус “В обработке”. Вы часто ищете заказы в обработке. Создаете индекс только для них:

CREATE INDEX idx_active_orders ON orders (created_at) WHERE status = 'В обработке';

Этот индекс будет маленьким (1% от таблицы) и очень быстрым.

Глубокое понимание фильтрованных индексов

Когда фильтрованные индексы особенно полезны:

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

-- Пользователь может иметь только один активный email,
-- но может иметь несколько неактивных (удаленных)
CREATE UNIQUE INDEX idx_active_email ON users (email) WHERE is_active = true;

Этот индекс запретит вставку двух строк с одинаковым email и is_active = true, но разрешит вставку строк с тем же email и is_active = false.

Ограничения фильтрованных индексов (зависит от СУБД):

Компромиссы:

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

Индекс по выражению (Function-Based Index / Index on Expression)

Индекс на результат вычисления

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

Зачем это нужно: Часто бывает, что вы ищете по преобразованному значению. Например, вы ищете пользователя по email в любом регистре:

SELECT * FROM users WHERE LOWER(email) = 'ivan@mail.ru'

Обычный индекс по email не поможет, потому что в индексе хранится "Ivan@Mail.Ru", а вы ищете "ivan@mail.ru". Индекс по выражению LOWER(email) решит проблему.

Глубокое понимание индексов по выражению

Когда использовать индексы по выражению:

Пример: поиск по году создания

-- Частый запрос: все заказы за 2024 год
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- Индекс по выражению
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM created_at));

Важное ограничение: Запрос должен использовать точно такое же выражение, как в индексе. Если вы создали индекс на LOWER(email), запрос WHERE UPPER(email) = 'IVAN@MAIL.RU' не использует этот индекс.

Стоимость индекса по выражению:

• Выражение вычисляется при каждой вставке и обновлении строки (для строк, попадающих в индекс).
• Выражение должно быть детерминированным – возвращать один и тот же результат для одних и тех же входных данных.
• Занимает дополнительное место (хранит результат вычисления).

Сравнительная таблица видов индексов

Резюме для системного аналитика

1. Кластерный индекс определяет физический порядок строк. У таблицы может быть только один кластерный индекс. Чаще всего это первичный ключ.
2. Некластерный индекс – это отдельная структура для быстрого поиска. У таблицы может быть много некластерных индексов.
3. Уникальный индекс запрещает дублирование значений.
4. Составной индекс – по нескольким столбцам. Порядок столбцов важен: индекс работает только с левого края.
5. Фильтрованный индекс – только для части строк (например, только активные записи).
6. Индекс по выражению – на результат функции (например, LOWER(email)).