Кластерные vs некластерные индексы: объяснение

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

Содержание

• Понимание кластеризованных индексов
• Понимание некластеризованных индексов
• Ключевые различия в общих чертах
• Когда использовать каждый тип
• Последствия для производительности
• Практические примеры
• Лучшие практики

Понимание кластеризованных индексов

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

Основные характеристики кластеризованных индексов:

• Физический порядок хранения: Строки данных физически сортируются и хранятся на диске в соответствии со значениями ключа индекса
• Только один на таблицу: Поскольку данные могут храниться только в одном физическом порядке, вы можете иметь только один кластеризованный индекс на таблицу
• Автоматически создается с первичными ключами: При создании ограничения первичного ключа SQL Server автоматически создает кластеризованный индекс, если вы не указали иное
• Таблица И есть индекс: Структура кластеризованного индекса содержит фактические строки данных

Представьте, что у вас есть таблица сотрудников с EmployeeID в качестве первичного ключа. При наличии кластеризованного индекса база данных физически хранит записи сотрудников в порядке EmployeeID: EmployeeID 1, затем 2, затем 3 и так далее, непосредственно на диске.

Согласно документации Microsoft Learn, “Когда таблица имеет кластеризованный индекс, она называется кластеризованной таблицей. Если таблица не имеет кластеризованного индекса, ее строки данных хранятся в неупорядоченной структуре, называемой кучей (heap).”

Понимание некластеризованных индексов

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

Основные характеристики некластеризованных индексов:

• Отдельная структура: Некластеризованные индексы хранятся отдельно от строк данных
• Разрешено несколько: Вы можете создавать несколько некластеризованных индексов для одной таблицы (до 999 в SQL Server)
• Содержат указатели: Индекс содержит значения индексируемого столбца и указатели на фактические строки данных
• Требует дополнительного шага: При использовании некластеризованного индекса база данных сначала переходит к индексу, а затем использует указатель для поиска фактических данных

Как объясняется на Stack Overflow, “Очень простое, нетехническое правило-палец: кластеризованные индексы обычно используются для вашего первичного ключа (или, по крайней мере, уникального столбца), а некластеризованные - для других ситуаций (возможно, для внешнего ключа)”.

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

Ключевые различия в общих чертах

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

Как объясняется на StrataScratch, “Кластеризованный индекс определяет физический порядок хранения строк данных в таблице. Проще говоря, данные таблицы и есть индекс.”

Когда использовать каждый тип

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

Используйте кластеризованный индекс, когда:

• У вас есть первичный ключ (это поведение по умолчанию)
• Вы часто выполняете диапазонные запросы (BETWEEN, >, <, ORDER BY)
• Вам требуется сортировка данных при извлечении (операции ORDER BY)
• У вас есть большие таблицы, где физическое упорядочение имеет наибольшее значение

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

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

Используйте некластеризованный индекс, когда:

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

Как отмечают участники сообщества Reddit SQL, “Столбцы, которые часто обновляются, должны покрываться некластеризованными индексами. Это дает вашим обычным запросам SELECT повышение производительности кластеризованного индекса, без существенного замедления ваших операций обновления.”

Последствия для производительности

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

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

Особенности производительности некластеризованных индексов

• Дополнительный шаг поиска: “Из-за этого дополнительного шага некластеризованные индексы медленнее кластеризованных индексов” SQL Shack
• Дополнительное хранение: “Некластеризованные индексы требуют дополнительного дискового пространства для хранения структур индекса” Мохамед Хендави в Medium
• Накладные расходы на запись: Каждый некластеризованный индекс должен обновляться при изменении данных
• Покрытые запросы: Вы можете добавлять “включенные столбцы” в индекс, чтобы избежать обращения к таблице: “Вы можете добавлять неключевые столбцы на уровень листа некластеризованного индекса, чтобы обойти существующие ограничения ключей индекса, и выполнять полностью покрытые, индексированные запросы” Stack Overflow

Практические примеры

Пример 1: Таблица клиентов интернет-магазина

-- Таблица клиентов с кластеризованным индексом по CustomerID (первичный ключ)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,  -- Автоматически создает кластеризованный индекс
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    CreatedDate DATETIME
);

-- Некластеризованный индекс для частых проверок по email
CREATE INDEX IX_Customers_Email ON Customers(Email);

-- Некластеризованный индекс для поиска по фамилии
CREATE INDEX IX_Customers_LastName ON Customers(LastName);

Пример 2: Анализ таблицы заказов

-- Таблица заказов - кластеризованный индекс по OrderID (первичный ключ)
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,  -- Здесь кластеризованный индекс
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20)
);

-- Некластеризованные индексы для распространенных шаблонов запросов
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);
CREATE INDEX IX_Orders_Status ON Orders(Status);

Лучшие практики

Общие рекомендации

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

2. Выбирайте мудро: Выбирайте столбцы для кластеризованных индексов, которые:

   • Уникальны или почти уникальны
   • Часто используются в диапазонных запросах
   • Используются в предложениях ORDER BY
   • Используются в условиях JOIN

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

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

Для разработчиков приложений

• Понимайте шаблоны ваших запросов: Анализируйте, как ваше приложение запрашивает данные
• Начинайте с первичных ключей в качестве кластеризованных индексов: Обычно это правильный выбор по умолчанию
• Добавляйте некластеризованные индексы для конкретных нужд: Не индексируйте каждый столбец
• Мониторьте производительность: Используйте инструменты базы данных для определения отсутствующих индексов
• Учитывайте шаблоны чтения и записи: Больше индексов означает более быстрое чтение, но более медленную запись

Как отмечается на GeeksforGeeks, “Индексирование - это метод оптимизации производительности в SQL Server, который значительно повышает скорость операций извлечения данных. Понимание различия между [кластеризованными и некластеризованными индексами] необходимо для разработчиков и администраторов баз данных для написания более быстрых и эффективных запросов.”

Источники

1. Кластеризованные и некластеризованные индексы - SQL Server | Microsoft Learn
2. Кластеризованное и некластеризованное индексирование - GeeksforGeeks
3. Что означают кластеризованный и некластеризованный индексы на самом деле? - Stack Overflow
4. Кластеризованные против некластеризованных индексов в SQL - StrataScratch
5. В чем разница между кластеризованными и некластеризованными индексами в SQL Server? - SQL Shack
6. Кластеризованный против некластеризованного индекса: Полное руководство по SQL - DBVis
7. Кластеризованные и некластеризованные индексы в SQL Server - Мохамед Хендави (Medium)
8. Требуется уточнение: Кластеризованные против некластеризованных индексов - Reddit r/SQL

Заключение

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

Основные выводы:

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

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