Индексы в MS SQL Server: кластерные, некластерные и покрывающие

Индекс в MS SQL Server — это отдельная структура данных на основе сбалансированного дерева (B-tree), которая хранит значения одного или нескольких столбцов в упорядоченном виде и ускоряет поиск строк, избавляя сервер от полного перебора таблицы. По устройству это ближе к B+ дереву, но в документации Microsoft закрепился термин B-tree.

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

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

Пока у таблицы нет кластерного индекса, ее строки лежат в куче (heap) — без гарантированного порядка. Чтобы найти нужные, серверу приходится читать таблицу целиком (table scan): чем больше строк, тем дольше.

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

Важно сразу развести три операции, которые легко спутать:

• Table scan — чтение всех строк кучи подряд.
• Index scan — чтение целиком структуры индекса (быстрее кучи, но все равно перебор).
• Index seek — точечный спуск по дереву к нужным строкам; ради него индексы и создают.

Как устроен индекс: B-дерево

Индекс SQL Server хранится как сбалансированное дерево из страниц по 8 КБ. У дерева три типа уровней:

• Корневой узел (root) — одна страница на вершине, точка входа.
• Промежуточные уровни (intermediate) — страницы с указателями вниз.
• Уровень листьев (leaf) — нижний уровень, где хранятся упорядоченные данные индекса.

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

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

Кластерный и некластерный индекс

Кластерный индекс (clustered) хранит в листьях сами строки таблицы, упорядоченные по ключу. То есть таблица физически и есть этот индекс, поэтому кластерный индекс может быть только один. По умолчанию он создается под ограничение PRIMARY KEY, если кластерного индекса еще нет.

Некластерный индекс (nonclustered) — это отдельная структура: в листьях лежат значения ключа и указатель на строку с данными. Если таблица — куча, указателем служит идентификатор строки (RID); если у таблицы есть кластерный индекс — значение его ключа. Таких индексов на таблицу можно создать до 999.

Когда некластерный индекс не содержит всех нужных столбцов, серверу приходится по указателю возвращаться к таблице за остальными полями — это дополнительная операция (key lookup). Избавиться от нее помогает покрывающий индекс.

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

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

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

• Ключевые столбцы — по ним индекс упорядочен, по ним идет поиск и они участвуют в навигации по дереву.
• Включенные столбцы (INCLUDE) — хранятся только в листьях, поиск по ним не идет, но их значения можно отдать без похода в таблицу. Они не считаются в лимит длины ключа.

Следующий пример — специфика SQL Server (T-SQL):

CREATE NONCLUSTERED INDEX ix_orders_customer
    ON orders (customer_id)          -- ключевой столбец: по нему ищем
    INCLUDE (order_date, total);     -- включенные: только отдать в результат

Запрос SELECT customer_id, order_date, total FROM orders WHERE customer_id = 42 теперь полностью покрыт индексом. Минус — такой индекс крупнее и замедляет запись, поэтому его строят под конкретный частый запрос.

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

Составной индекс строится по нескольким столбцам (в SQL Server — до 32 ключевых столбцов). Порядок столбцов в нем критичен: индекс упорядочен по первому столбцу, внутри него — по второму и так далее.

Отсюда правило левого префикса: индекс по (city, age) помогает запросам с условием по city или по city и age вместе, но бесполезен для запроса только по age. Поэтому первым ставят столбец, который чаще всего стоит в WHERE и лучше отбирает строки.

Проверяем механику на прогоняемом примере

Устройство индекса одинаково у всех СУБД с B-деревьями, поэтому механику удобно показать на SQLite: он использует такие же B-tree индексы, а EXPLAIN QUERY PLAN показывает выбор планировщика. В SQL Server то же самое видно в плане выполнения запроса (Index Seek против Table Scan).

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT,
    city  TEXT,
    age   INTEGER
);
INSERT INTO users (email, city, age) VALUES
 ('a@x.ru','Moscow',20),('b@x.ru','Kazan',31),('c@x.ru','Moscow',45),
 ('d@x.ru','Perm',28),('e@x.ru','Moscow',37),('f@x.ru','Kazan',52);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE city = 'Moscow';
CREATE INDEX ix_city ON users(city);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE city = 'Moscow';
CREATE INDEX ix_city_age ON users(city, age);
EXPLAIN QUERY PLAN SELECT age FROM users WHERE city = 'Moscow';

Вывод (последнее число в кортеже — внутренний идентификатор, важен текст плана):

[(2, 0, 216, 'SCAN users')]
[(3, 0, 62, 'SEARCH users USING INDEX ix_city (city=?)')]
[(2, 0, 55, 'SEARCH users USING COVERING INDEX ix_city_age (city=?)')]

Три строки читаются так. До индекса — SCAN users, то есть полный перебор таблицы. После CREATE INDEX ix_city — SEARCH ... USING INDEX, то есть точечный поиск по дереву. А запрос SELECT age ... WHERE city целиком закрыт составным индексом (city, age): план говорит USING COVERING INDEX, к таблице планировщик не обращается — это и есть покрывающий индекс из предыдущего раздела.

Как создать и удалить индекс в SQL Server

Индексы создают через T-SQL (CREATE INDEX) или мышью в SSMS: узел таблицы -> «Индексы» -> «Создать индекс». Базовый синтаксис T-SQL (специфика SQL Server):

-- кластерный индекс (в таблице может быть только один)
CREATE CLUSTERED INDEX ix_orders_id ON orders (id);

-- некластерный индекс для поиска по дате
CREATE NONCLUSTERED INDEX ix_orders_date ON orders (order_date);

-- удаление индекса
DROP INDEX ix_orders_date ON orders;

Индекс, созданный автоматически под ограничение PRIMARY KEY или UNIQUE, командой DROP INDEX убрать нельзя — для него используют ALTER TABLE ... DROP CONSTRAINT.

Отдельно стоит фильтруемый индекс (тоже специфика SQL Server) — некластерный индекс с условием WHERE, который индексирует только часть строк. Он меньше и дешевле в обслуживании:

CREATE NONCLUSTERED INDEX ix_orders_open
    ON orders (order_date)
    WHERE status = 'open';   -- индексируем только открытые заказы

Когда индекс помогает, а когда мешает

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

• Индекс окупается, когда условие отбирает малую долю строк (высокая избирательность предиката). Если запрос возвращает большую часть таблицы, оптимизатор сам предпочтет сканирование.
• На маленьких таблицах индекс часто не нужен: перебор нескольких страниц быстрее навигации по дереву.
• Ключ кластерного индекса делайте коротким, уникальным и не допускающим NULL — его значение дублируется во всех некластерных индексах. Поэтому в этой роли обычно и выступает PRIMARY KEY.
• Индекс не сработает, если столбец обернут функцией: условие WHERE YEAR(order_date) = 2026 мешает поиску по дереву (предикат становится non-SARGable). Перепишите его диапазоном: order_date >= '2026-01-01' AND order_date < '2027-01-01'.

Кроме обычных B-tree индексов в SQL Server есть специальные виды под свои задачи: COLUMNSTORE для аналитики по большим объемам, полнотекстовый для поиска слов, XML- и пространственный (GEOGRAPHY/GEOMETRY), а также хеш-индексы в таблицах, оптимизированных для памяти (In-Memory OLTP).

Обслуживание: фрагментация индексов

Со временем вставки и удаления разбрасывают страницы индекса — появляется фрагментация, которая замедляет чтение. Ее уровень показывает системная функция sys.dm_db_index_physical_stats (столбец avg_fragmentation_in_percent).

Распространенный ориентир Microsoft: до 5 процентов ничего не делать, от 5 до 30 — реорганизация (ALTER INDEX ... REORGANIZE, легкая, без долгой блокировки), свыше 30 — перестроение (ALTER INDEX ... REBUILD, тяжелее, но эффективнее). Ориентир датированный: на современных SSD влияние фрагментации меньше, поэтому пороги — отправная точка, а не догма. Устаревшую DBCC DBREINDEX заменяет ALTER INDEX.

Выводы

• Индекс — это упорядоченная структура на основе B-дерева; она превращает полный перебор таблицы (scan) в точечный поиск по дереву (seek).
• Кластерный индекс хранит в листьях сами строки и бывает один; некластерный — отдельная структура с ключом и указателем, их до 999.
• Покрывающий индекс отдает весь ответ из листьев без обращения к таблице; включенные столбцы (INCLUDE) добавляются только для этого.
• В составном индексе решает порядок столбцов: работает левый префикс, первым ставят самый избирательный и частый в WHERE столбец.
• Индексы ускоряют чтение, но замедляют запись и занимают место, поэтому их создают под реальные запросы и обслуживают по мере фрагментации.

Где применяется и что учить дальше

Грамотная работа с индексами — повседневная задача разработчика и администратора баз данных: от нее напрямую зависит скорость приложений на любой заметной базе. Понимание кластерных и покрывающих индексов, планов выполнения и избирательности предикатов нужно каждому, кто пишет запросы к MS SQL Server.

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

FAQ

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

Сколько индексов создавать на таблицу? Ровно столько, сколько оправдано частыми запросами. Каждый лишний индекс замедляет INSERT, UPDATE, DELETE и занимает место, поэтому «на всякий случай» их не делают.

Индекс есть, но запрос все равно медленный — почему? Частые причины: неподходящий порядок столбцов в составном индексе (не работает левый префикс), низкая избирательность условия или функция над столбцом в WHERE, из-за которой предикат становится non-SARGable и поиск по дереву не применяется.