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

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

например, есть две таблицы Customer и Order:

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

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

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

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

в ориентированном на строки хранилище 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 вызывает много путаницы

по-моему, это было бы значительно лучше назвать.

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

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

это была бы абсурдная реализация. Например, если строка вставлена в середину таблицы 4GB, 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) и состоит из трех компонентов (File:Page:Slot). В случае кластеризованного индекса локатор строк является логическим (ключ кластеризованного индекса).

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

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

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

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

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

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

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

КЛАСТЕРИЗОВАННЫЙ ИНДЕКС

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

НЕКЛАСТЕРИЗОВАННЫЙ ИНДЕКС

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

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

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

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

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

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

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

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

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

как "словарь" нет необходимости в каком-либо другом индексе, его уже индекс по словам

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

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

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

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

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

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

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

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

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

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

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