В чем разница между кластеризованным и некластеризованным индексом?

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

• Только по одному на стол
• Быстрее читать, чем некластеризованные, поскольку данные физически хранятся в порядке индекса

Некластеризованный индекс

• Может использоваться много раз для одного стола
• Быстрее для операций вставки и обновления, чем кластерный индекс

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

Из-за более медленной вставки и обновления кластерные индексы должны быть установлены в поле, которое обычно является инкрементным, то есть Id или Timestamp.

SQL Server обычно использует индекс, только если его избирательность выше 95%.

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

Все остальные индексы не должны быть кластеризованными. Некластеризованный индекс имеет дубликаты данных из индексированных столбцов, которые хранятся упорядоченными вместе с указателями на фактические строки данных (указатели на кластеризованный индекс, если он есть). Это означает, что доступ к данным через некластеризованный индекс должен проходить через дополнительный уровень косвенного обращения. Однако, если вы выбираете только те данные, которые доступны в индексированных столбцах, вы можете получить данные обратно непосредственно из дублированных данных индекса (поэтому рекомендуется ВЫБРАТЬ только те столбцы, которые вам нужны, и не использовать *)

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

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

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

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

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

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

1. Для таблицы может быть только 249 некластеризованных индексов (до версии sql 2005 более поздние версии поддерживают до 999 некластеризованных индексов).
2. Обычно делается на любую клавишу.
3. Конечный узел некластеризованного индекса не состоит из страниц данных. Вместо этого листовые узлы содержат индексные строки.

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

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

Некластеризованный индекс

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

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

Плюсы:

Кластерные индексы отлично подходят для диапазонов (например, выберите * из my_table, где my_key между @min и @max)

В некоторых случаях СУБД не нужно будет выполнять работу по сортировке, если вы используете оператор orderby.

Минусы:

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

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

Некластеризованный означает, что это «всего лишь» логический порядок.

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

Некластеризованный индекс определяет логический порядок, который не соответствует физическому порядку на диске.

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

Например, если начать с пустой некластеризованной базы данных и добавить 10 000 записей в случайной последовательности, записи, вероятно, будут добавлены в конце в том порядке, в котором они были добавлены. Чтение базы данных по индексу потребует 10 000 чтений одной записи. Однако, если бы нужно было использовать кластеризованную базу данных, система могла бы проверять при добавлении каждой записи, была ли предыдущая запись сохранена сама по себе; если он обнаружит, что это так, он может записать эту запись с новой в конец базы данных. Затем он мог бы просмотреть физическую запись перед слотами, в которых раньше находились перемещенные записи, и увидеть, была ли сохранена следующая за ней запись. Если он обнаружит, что это так, он может переместить эту запись в это место. Использование такого подхода приведет к тому, что многие записи будут сгруппированы вместе в пары, что потенциально может почти вдвое увеличить скорость последовательного чтения.

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

Кластеризованный индекс - это, по сути, отсортированная копия данных в индексированных столбцах.

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

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

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

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

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

// Скопировано из MSDN, вторая точка некластеризованного индекса четко не упоминается в других ответах.

Сгруппированы

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

Некластеризованный

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

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

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

Некластерные индексы

• Некластеризованные индексы медленнее извлекают данные и быстрее вставляют и обновляют.

• Таблица может иметь несколько некластеризованных индексов.

• Требуется дополнительное пространство для хранения логической структуры.

• Не влияет на порядок хранения данных на диске.