В чем разница между кластеризованным и некластеризованным индексом?
В чем разница между clustered и non-clustered index?
Кластеризация в основном означает, что данные расположены в таблице в физическом порядке. Вот почему у вас может быть только один на стол. Некластеризованный означает, что это «всего лишь» логический порядок.
Кластерный индекс фактически описывает порядок, в котором записи физически хранятся на диске, поэтому у вас может быть только одна. Некластеризованный индекс определяет логический порядок, который не соответствует физическому порядку на диске.
@biri что такое "логический" порядок? Некластеризованный индекс хранит ключи индекса в физическом порядке и хранит указатель на таблицу, а именно ключ кластеризованного индекса.
@Stephanie Page: логично с табличной точки зрения. Конечно, некластеризованные индексы физически упорядочиваются в самом индексе.
Осторожно: большинство ответов здесь неверны. Каждый ответ, утверждающий, что кластеризованный индекс «быстрее» или что некластеризованный индекс не «хранится в порядке индекса», неверен (это относится почти ко всем ответам).
Возможный дубликат Что на самом деле означают кластерный и некластеризованный индекс?
Ответы 12
Кластерные индексы физически хранятся в таблице. Это означает, что они самые быстрые, и у вас может быть только один кластеризованный индекс для каждой таблицы.
Некластеризованные индексы хранятся отдельно, и вы можете иметь их сколько угодно.
Наилучший вариант - установить кластерный индекс для наиболее часто используемого уникального столбца, обычно PK. У вас всегда должен быть хорошо выбранный кластерный индекс в ваших таблицах, если нет очень веской причины - не можете придумать ни одного, но эй, он может быть там - потому что этого не происходит.
не могли бы вы подробнее рассказать о том, что «у нас всегда должен быть кластерный индекс в наших таблицах»? без уточнения это утверждение просто неверно из-за слова всегда
Вы правы, Пасерье, не следует легкомысленно использовать абсолютные утверждения. Хотя я не знаю ни одного случая, когда у вас не должно быть хорошо выбранного кластерного индекса, такой случай может существовать, поэтому я изменил свой ответ на более общую версию.
Кластерный индекс фактически описывает порядок, в котором записи физически хранятся на диске, поэтому у вас может быть только одна.
Некластеризованный индекс определяет логический порядок, который не соответствует физическому порядку на диске.
Кластерный индекс
- Только по одному на стол
- Быстрее читать, чем некластеризованные, поскольку данные физически хранятся в порядке индекса
Некластерный индекс
- Может использоваться много раз для одного стола
- Быстрее для операций вставки и обновления, чем кластерный индекс
Оба типа индекса улучшают производительность при выборе данных с полями, которые используют индекс, но замедляют операции обновления и вставки.
Из-за более медленной вставки и обновления кластерные индексы следует устанавливать в поле, которое обычно является инкрементным, то есть Id или Timestamp.
SQL Server обычно использует индекс только в том случае, если его избирательность выше 95%.
Есть также соображения по хранению. При вставке строк в таблицу без кластеризованного индекса строки сохраняются друг за другом на странице, и обновление строки может привести к перемещению строки в конец таблицы, оставлению пустого места и фрагментации таблицы и индексов.
Что означает, что индекс «читается быстрее»? Сколько еще x в секунду вы можете сделать? Что такое х?
вам не нужно заботиться о том, что такое x. Все, что вам нужно знать, это то, что для приложения с миллионами пользователей x будет значительным
Это чисто догма. Это не «быстрее читать, потому что данные хранятся по порядку». Его читать быстрее, потому что вы избегаете чтения индекса, а ЗАТЕМ чтение таблицы. Быстрее сканировать по диапазону (если это имеет смысл), потому что данные хранятся по порядку. т.е. коэффициент кластеризации идеален.
Также заблуждение, что 95% записей должны быть уникальными. Допустим, у вас есть таблица с 1 000 000 строк и вы индексируете столбец с 500 000 ключей. 0% уникальны, но каждый ключ возвращает 2 строки из миллиона. Этот индекс абсолютно полезен, несмотря на то, что 0% записей уникальны.
@StephaniePage: Интересно, как можно измерить эту «избирательность». Индекс, который имеет одно значение для 999 900 записей и одно значение для 100, может быть очень полезным, если его когда-либо использовали только для поиска 100.
"данные физически хранятся в порядке индекса", что вы имеете в виду? На одном уровне это тривиально верно, потому что страницы данных и конечные страницы индекса - это одно и то же, поэтому очевидно, что порядок одной описывает порядок другой. Однако это не обязательно в каком-либо конкретном порядке, таком как порядок ключа индекса stackoverflow.com/questions/1251636/…
Если вы предпочитаете изучать вещи, понимая, как они работают, см. Ответ @ rslite ниже. Я считаю, что это лучший ответ.
Кластеризация в основном означает, что данные в таблице расположены в определенном физическом порядке. Вот почему у вас может быть только один на стол.
Некластеризованный означает, что это «всего лишь» логический порядок.
Кластеризованный индекс - это, по сути, отсортированная копия данных в индексированных столбцах.
Основное преимущество кластеризованного индекса заключается в том, что, когда ваш запрос (поиск) находит данные в индексе, для извлечения этих данных не требуется дополнительных операций ввода-вывода.
Накладные расходы на поддержку кластеризованного индекса, особенно в часто обновляемой таблице, могут привести к снижению производительности, и по этой причине может быть предпочтительнее создать некластеризованный индекс.
Плюсы:
Кластерные индексы отлично подходят для диапазонов (например, выберите * из my_table, где my_key между @min и @max)
В некоторых случаях СУБД не нужно выполнять работу по сортировке, если вы используете оператор orderby.
Минусы:
Кластерные индексы могут замедлять вставку, потому что физическая структура записей должна быть изменена по мере добавления записей, если новые ключи расположены не в последовательном порядке.
Кластерные индексы физически упорядочивают данные на диске. Это означает, что для индекса не требуются дополнительные данные, но может быть только один кластеризованный индекс (очевидно). Самый быстрый доступ к данным с помощью кластерного индекса.
Все остальные индексы не должны быть кластеризованными. Некластеризованный индекс содержит дубликаты данных из индексированных столбцов, которые хранятся в упорядоченном порядке вместе с указателями на фактические строки данных (указатели на кластеризованный индекс, если он есть). Это означает, что доступ к данным через некластеризованный индекс должен проходить через дополнительный уровень косвенного обращения. Однако, если вы выбираете только те данные, которые доступны в индексированных столбцах, вы можете получить данные обратно непосредственно из дублированных данных индекса (поэтому рекомендуется ВЫБРАТЬ только те столбцы, которые вам нужны, и не использовать *)
«Однако, если вы выберете только те данные, которые доступны в индексированных столбцах, вы можете получить данные обратно непосредственно из дублированных данных индекса» - да, это важное исключение из предпочтительной эвристики кластеризованного индекса. Я предполагаю, что в этом случае у вас, по сути, есть кластерный индекс, но меньше данных в запрашиваемой таблице, поэтому потенциально ее можно быстрее прочитать с диска.
Кластерный индекс
- Для таблицы может быть только один кластерный индекс.
- Обычно делается на первичном ключе.
- Листовые узлы кластерного индекса содержат страницы данных.
Некластерный индекс
- Для таблицы может быть только 249 некластеризованных индексов (до версии sql 2005 более поздние версии поддерживают до 999 некластеризованных индексов).
- Обычно делается на любой ключ.
- Конечный узел некластеризованного индекса не состоит из страниц данных. Вместо этого листовые узлы содержат индексные строки.
Индексированная база данных состоит из двух частей: набора физических записей, которые расположены в произвольном порядке, и набора индексов, которые определяют последовательность, в которой записи должны быть прочитаны, чтобы дать результат, отсортированный по некоторому критерию. Если нет корреляции между физическим расположением и индексом, то чтение всех записей по порядку может потребовать выполнения множества независимых операций чтения одной записи. Поскольку база данных может быть в состоянии прочитать десятки последовательных записей за меньшее время, чем это потребовалось бы для чтения двух непоследовательных записей, производительность может быть улучшена, если записи, которые являются последовательными в индексе, также будут последовательно сохранены на диске. Указание кластеризации индекса приведет к тому, что база данных приложит некоторые усилия (разные базы данных различаются по степени), чтобы организовать вещи так, чтобы группы записей, которые являются последовательными в индексе, были последовательными на диске.
Например, если начать с пустой некластеризованной базы данных и добавить 10 000 записей в случайной последовательности, записи, скорее всего, будут добавлены в конце в том порядке, в котором они были добавлены. Чтение базы данных по индексу потребует 10 000 чтений одной записи. Однако при использовании кластеризованной базы данных система могла бы проверять при добавлении каждой записи, была ли предыдущая запись сохранена сама по себе; если он обнаружит, что это так, он может записать эту запись с новой в конец базы данных. Затем он мог бы просмотреть физическую запись перед слотами, в которых раньше находились перемещенные записи, и увидеть, была ли сохранена следующая за ней запись. Если он обнаружит, что это так, он может переместить эту запись в это место. Использование такого подхода приведет к тому, что многие записи будут сгруппированы в пары, что потенциально может почти удвоить скорость последовательного чтения.
В действительности кластерные базы данных используют более сложные алгоритмы, чем этот. Однако важно отметить, что существует компромисс между временем, необходимым для обновления базы данных, и временем, необходимым для ее последовательного чтения. Поддержка кластеризованной базы данных значительно увеличит объем работы, необходимой для добавления, удаления или обновления записей любым способом, который может повлиять на последовательность сортировки. Если база данных будет читаться последовательно гораздо чаще, чем обновляться, кластеризация может стать большим выигрышем. Если он будет обновляться часто, но редко считывать последовательно, кластеризация может сильно истощить производительность, особенно если последовательность, в которой элементы добавляются в базу данных, не зависит от их порядка сортировки по отношению к кластеризованному индексу.
Кластерный индекс
- В таблице может быть только один кластеризованный индекс
- Отсортируйте записи и храните их физически в соответствии с порядком
- Получение данных происходит быстрее, чем некластеризованные индексы
- Не нужно дополнительное место для хранения логической структуры
Некластерный индекс
- В таблице может быть любое количество некластеризованных индексов.
- Не влияют на физический порядок. Создайте логический порядок для строк данных и используйте указатели на физические файлы данных
- Вставка / обновление данных происходит быстрее, чем кластерный индекс
- Используйте дополнительное пространство для хранения логической структуры
Помимо этих различий, вы должны знать, что когда таблица некластеризована (когда таблица не имеет кластеризованного индекса) файлы данных неупорядочиваются, и в качестве структуры данных используется структура данных кучи.
// Скопировано из MSDN, вторая точка некластеризованного индекса четко не упоминается в других ответах.
Кластеризованный
- Кластерные индексы сортируют и сохраняют строки данных в таблице или представлении исходя из их ключевых ценностей. Это столбцы, включенные в определение индекса. В таблице может быть только один кластеризованный индекс, потому что сами строки данных могут храниться только в одном порядке.
- Единственный раз, когда строки данных в таблице хранятся в отсортированном порядке, - это когда таблица содержит кластерный индекс. Когда в таблице есть кластеризованный индекс, таблица называется кластеризованной таблицей. Если таблица не имеет кластеризованного индекса, его строки данных хранятся в неупорядоченном виде структура называется кучей.
Некластеризованный
- Некластеризованные индексы имеют структуру, отдельную от строк данных. А
некластеризованный индекс содержит значения ключей некластеризованного индекса и
каждая запись значения ключа имеет указатель на строку данных, содержащую ключевое значение. - Указатель из строки индекса в некластеризованном индексе на строку данных называется локатором строк. Структура локатора строк зависит от хранятся ли страницы данных в куче или в кластерной таблице. Для кучи локатор строки - это указатель на строку. Для кластерного table, указателем строки является ключ кластеризованного индекса.
Возможно, вы прошли теоретическую часть из приведенных выше сообщений:
-Кластерный индекс, поскольку мы можем видеть, указывает непосредственно на запись, то есть его прямой, поэтому для поиска требуется меньше времени. Кроме того, для хранения индекса не потребуется дополнительная память / пространство.
- В то время как в некластеризованном индексе он косвенно указывает на кластерный индекс, тогда он будет обращаться к фактической записи, из-за его косвенного характера для доступа потребуется немного больше времени.Также ему нужна собственная память / пространство для хранения индекс
У вас может быть только один кластерный индекс для каждой таблицы. Но есть и другие отличия ...