Что на самом деле означает кластерный и некластеризованный индекс?

Ответ 1

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

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

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

Запись в таблицу с кластеризованным индексом может быть медленнее, если есть необходимость перегруппировать данные.

Ответ 2

Кластеризованный индекс означает, что вы сообщаете базе данных о сохранении близких значений, фактически близких друг к другу на диске. Это обеспечивает быстрое сканирование/извлечение записей, попадающих в некоторый диапазон значений кластеризованного индекса.

Например, у вас есть две таблицы: Заказ и Заказ:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

Если вы хотите быстро получить все заказы одного конкретного клиента, вы можете захотеть создать кластерный индекс в столбце "CustomerID" таблицы Order. Таким образом, записи с одним и тем же CustomerID будут физически храниться рядом друг с другом на диске (с кластеризацией), что ускоряет их извлечение.

P.S. Очевидно, что индекс на CustomerID не является уникальным, поэтому вам нужно добавить второе поле для "uniquify" индекса или позволить базе данных обрабатывать это для вас, но это другая история.

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

Ответ 3

В ориентированном на SQL Server хранилище кластеризованные и некластеризованные индексы организованы как деревья B.

(Источник изображения)

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

1. Строки на страницах кластеризованного листа индекса всегда содержат что-то для каждого из (не разреженных) столбцов в таблице (либо значение, либо указатель на фактическое значение).
2. Кластеризованный индекс является основной копией таблицы.

Некластеризованные индексы также могут делать точку 1, используя предложение INCLUDE (Начиная с SQL Server 2005), чтобы явно включать все не ключевые столбцы, но они являются вторичными представлениями, и всегда есть другая копия данных вокруг (сама таблица).

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

Два указанных выше индекса будут почти одинаковыми. С индексными страницами верхнего уровня, содержащими значения для ключевых столбцов A,B и страниц уровня листа, содержащих A,B,C,D

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

Вышеприведенная цитата из книг SQL Server в Интернете вызывает много путаницы

По-моему, это было бы гораздо лучше сформулировано.

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

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

Это было бы абсурдной реализацией. Например, если строка вставлена в середину таблицы 4 ГБ, SQL Server не должен копировать 2 ГБ данных в файл, чтобы освободить место для вновь вставленной строки.

Вместо этого происходит разделение страниц. Каждая страница на уровне листа как кластерных, так и некластеризованных индексов имеет адрес (File:Page) следующей и предыдущей страниц в порядке логического ключа. Эти страницы не обязательно должны быть смежными или ключевыми.

например, связанная цепочка страниц может быть 1:2000 <-> 1:157 <-> 1:7053

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

Степень логического порядка и смежности отличается от идеализированной физической версии - степень логической фрагментации.

Во вновь созданной базе данных с одним файлом я запускал следующее.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

Затем проверили макет страницы с помощью

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

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

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

После запуска

ALTER INDEX ix ON T REBUILD;

Я получил следующее

Если таблица не имеет кластерного индекса, она называется кучей.

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

В последнем случае, если некластеризованный индекс уже, естественно, включает в себя столбцы ключевых слов CI либо в виде столбцов ключа NCI, либо в столбцах INCLUDE -d, тогда ничего не добавляется. В противном случае отсутствующий столбец (-ы) ключа CI незаметно добавляется в NCI.

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

Кластеризованные индексы получают uniquifier для любых строк с ключевыми значениями, которые дублируют существующую строку. Это просто восходящее целое число.

Для некластеризованных индексов, не объявленных как уникальный SQL Server, тихо добавляет локатор строк в некластеризованный индексный ключ. Это относится ко всем строкам, а не только к тем, которые фактически дублируются.

Кластерная или некластеризованная номенклатура также используется для индексов хранилища столбцов. В статье " Улучшения состояния столбцов столбцов SQL Server"

Хотя данные хранилища столбцов на самом деле не "кластеризованы" на любом ключе, мы решили сохранить традиционное соглашение SQL Server о присвоении первичного индекса как кластерного индекса.

Ответ 4

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

CLUSTERED INDEX

Если вы заходите в публичную библиотеку, вы обнаружите, что все книги расположены в определенном порядке (скорее всего, десятичная система Дьюи или DDS). Это соответствует "кластерному индексу" книг. Если для нужной книги DDS # было 005.7565 F736s, вы должны начать с поиска строки книжных полок, помеченной как 001-099 или что-то в этом роде. (Этот знак endcap в конце стека соответствует "промежуточному индексу node" в индексе.) В конце концов вы перейдете к определенной полке с меткой 005.7450 - 005.7600, затем вы сканируете, пока не найдете книгу с указанным DDS #, и в этот момент вы нашли свою книгу.

НЕКОТОРЫЙ ИНДЕКС

Но если вы не зашли в библиотеку с записью DDS № вашей книги, вам понадобится второй указатель, который поможет вам. В старину вы найдете в передней части библиотеки прекрасное бюро ящиков, известное как "Карточный каталог". В нем были тысячи 3х5 карт - по одному для каждой книги, отсортированные в алфавитном порядке (по названию, возможно). Это соответствует "некластеризованному индексу". Эти карточные каталоги были организованы в иерархическую структуру, так что каждый ящик был бы помечен диапазоном карт, которые он содержал (например, Ka - Kl, т.е. "Промежуточный node" ). Еще раз, вы будете тренироваться, пока не найдете свою книгу, но в этом случае, как только вы ее нашли (т.е. "Лист node" ), у вас нет самой книги, а просто карта с номер индекса (DDS #), с помощью которого вы могли бы найти фактическую книгу в кластерном индексе.

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

Ответ 5

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

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

• Кластеризованные индексы - это индексы, которые однозначно идентифицируют строки в таблице SQL.
• Каждая таблица может иметь только один кластерный индекс.
• Вы можете создать кластеризованный индекс, который охватывает более одного столбца. Например: create Index index_name(col1, col2, col.....).
• По умолчанию столбец с первичным ключом уже имеет кластеризованный индекс.

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

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

Ответ 6

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

Ответ 7

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

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

• "dictionary" Никакой другой индекс не нужен, его уже по словам

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

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

• "Химия книга" при взгляде есть отдельный указатель для указания местоположения главы, а у "КОНЦА" есть еще один указатель, указывающий общее местоположение СЛОВ

Ответ 8

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

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

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

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

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

Вы можете добавить столбцы без ключа на уровень листа некластеризованного индекса, чтобы обходить существующие ограничения по ключевым словам и выполнять полностью покрытые, индексированные запросы. Дополнительные сведения см. В разделе "Создание индексов с включенными столбцами". Для получения дополнительной информации об ограничениях ключа ключа см. Спецификации максимальной емкости для SQL Server.

Ссылка: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

Ответ 9

Позвольте мне предложить определение учебника по "индексу кластеризации", которое взято из 15.6.1 из Системы баз данных: Полная книга:

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

Чтобы понять определение, давайте взглянем на пример 15.10, представленный в учебнике:

Отношение R(a,b), которое сортируется по атрибуту a и сохраняется в нем порядок, упакованный в блоки, обязательно кластеризован. Индекс a является индекс кластеризации, так как для данного a -value a1 все кортежи с это значение для a является последовательным. Таким образом, они выглядят упакованными в блоки, кроме, возможно, для первого и последнего блоков, которые содержат a -value a1, как показано на рис .15.14. Тем не менее, индекс на б вряд ли будет кластеризация, так как кортежи с фиксированным b -value будет распространяться по всему файлу, если значения a и b не очень тесно связаны.

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

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

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

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

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

Индекс для атрибута (ов) A в файле является индексом кластеризации, когда: все кортежи со значением атрибута A = a хранятся последовательно (= последовательно) в файле данных

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

Ответ 10

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

Некластеризованный индекс: фактические данные некластеризованного индекса не находятся непосредственно на конечном узле, вместо этого он должен предпринять дополнительный шаг для поиска, потому что он имеет только значения локаторов строк, указывающих на фактические данные. Некластерный индекс не может быть отсортирован как кластерный индекс. В одной таблице может быть несколько некластеризованных индексов, на самом деле это зависит от используемой нами версии сервера SQL. В основном Sql server 2005 допускает 249 некластеризованных индексов, а для вышеприведенных версий, таких как 2008, 2016, он допускает 999 некластеризованных индексов на таблицу.

Ответ 11

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