Некластеризованный индекс: Описание кластеризованных и некластеризованных индексов — SQL Server

Описание кластеризованных и некластеризованных индексов — SQL Server

• 
  02/11/2019
• Чтение занимает 4 мин

В этой статье

Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database

Индекс является структурой на диске, которая связана с таблицей или представлением и ускоряет получение строк из таблицы или представления.An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. Индекс содержит ключи, построенные из одного или нескольких столбцов в таблице или представлении.An index contains keys built from one or more columns in the table or view. Эти ключи хранятся в виде структуры сбалансированного дерева, которая поддерживает быстрый поиск строк по их ключевым значениям в SQL ServerSQL Server .These keys are stored in a structure (B-tree) that enables SQL ServerSQL Server to find the row or rows associated with the key values quickly and efficiently.

Таблица или представление может иметь индексы следующих типов.A table or view can contain the following types of indexes:

• КластеризованныйClustered

  • Кластеризованные индексы сортируют и хранят строки данных в таблицах или представлениях на основе их ключевых значений.Clustered indexes sort and store the data rows in the table or view based on their key values. Этими значениями являются столбцы, включенные в определение индекса.These are the columns included in the index definition. Существует только один кластеризованный индекс для каждой таблицы, так как строки данных могут храниться в единственном порядке.There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
  • Строки данных в таблице хранятся в порядке сортировки только в том случае, если таблица содержит кластеризованный индекс.The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. Если у таблицы есть кластеризованный индекс, то таблица называется кластеризованной.When a table has a clustered index, the table is called a clustered table. Если у таблицы нет кластеризованного индекса, то строки данных хранятся в неупорядоченной структуре, которая называется кучей.If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

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

  • Некластеризованные индексы имеют структуру, отдельную от строк данных.Nonclustered indexes have a structure separate from the data rows. В некластеризованном индексе содержатся значения ключа некластеризованного индекса, и каждая запись значения ключа содержит указатель на строку данных, содержащую значение ключа.A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

  • Указатель из строки индекса в некластеризованном индексе, который указывает на строку данных, называется указателем строки.The pointer from an index row in a nonclustered index to a data row is called a row locator. Структура указателя строки зависит от того, хранятся ли страницы данных в куче или в кластеризованной таблице.The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. Для кучи указатель строки является указателем на строку. For a heap, a row locator is a pointer to the row. Для кластеризованной таблицы указатель строки данных является ключом кластеризованного индекса.For a clustered table, the row locator is the clustered index key.

  • Вы можете добавить неключевые столбцы на конечный уровень некластеризованного индекса, чтобы обойти существующее ограничение на ключи индексов и выполнять полностью индексированные запросы.You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, and execute fully covered, indexed, queries. Дополнительные сведения см. в статье Create Indexes with Included Columns.For more information, see Create Indexes with Included Columns. Дополнительные сведения об ограничениях на ключи индексов см. в разделе Спецификации максимально допустимых параметров SQL Server.For details about index key limits see Maximum Capacity Specifications for SQL Server.

Как кластеризованные, так и некластеризованные индексы могут быть уникальными.Both clustered and nonclustered indexes can be unique. Это означает, что никакие две строки не имеют одинаковое значение для ключа индекса.This means no two rows can have the same value for the index key. В противном случае индекс не является уникальным, и несколько строк могут содержать одно и то же значение.Otherwise, the index is not unique and multiple rows can share the same key value. Дополнительные сведения см. в статье Создание уникальных индексов.For more information, see Create Unique Indexes.

Обслуживание индексов таблиц и представлений происходит автоматически при любом изменении данных в таблице.Indexes are automatically maintained for a table or view whenever the table data is modified.

Дополнительные типы специальных индексов см. в разделе Indexes .See Indexes for additional types of special purpose indexes.

Индексы и ограниченияIndexes and Constraints

Индексы создаются автоматически при определении ограничений PRIMARY KEY или UNIQUE на основе столбцов таблицы. Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. Например, при создании таблицы с ограничением UNIQUE Компонент Database EngineDatabase Engine автоматически создает некластеризованный индекс.For example, when you create a table with a UNIQUE constraint, Компонент Database EngineDatabase Engine automatically creates a nonclustered index. При настройке PRIMARY KEY Компонент Database EngineDatabase Engine автоматически создает кластеризованный индекс, если он еще не существует.If you configure a PRIMARY KEY, Компонент Database EngineDatabase Engine automatically creates a clustered index, unless a clustered index already exists. Если вы пытаетесь применить ограничение PRIMARY KEY в существующей таблице, для которой уже создан кластеризованный индекс, SQL Server применяет первичный ключ с помощью некластеризованного индекса.When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a nonclustered index.

Дополнительные сведения см. в разделах Create Primary Keys и Create Unique Constraints.For more information, see Create Primary Keys and Create Unique Constraints.

Как оптимизатор запросов использует индексыHow Indexes are used by the Query Optimizer

Правильно построенные индексы могут сократить количество дисковых операций ввода-вывода, уменьшить потребление системных ресурсов, таким образом улучшая производительность запроса.Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. Индексы могут быть полезны во множестве запросов, содержащих инструкции SELECT, UPDATE, DELETE или MERGE.Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements. Рассмотрим запрос SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 в базе данных AdventureWorks2012AdventureWorks2012 . Consider the query SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 in the AdventureWorks2012AdventureWorks2012 database. При выполнении этого запроса оптимизатор запросов оценивает все доступные методы получения данных и выбирает наиболее эффективный метод.When this query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method. Этим методом может являться просмотр таблицы или просмотр одного или более индексов, если они существуют.The method may be a table scan, or may be scanning one or more indexes if they exist.

При выполнении просмотра таблицы оптимизатор запросов считывает все строки таблицы и извлекает строки, удовлетворяющие критериям запроса.When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. Просмотр таблицы формирует много дисковых операций ввода-вывода и может быть ресурсоемкой операцией.A table scan generates many disk I/O operations and can be resource intensive. Но если результирующий набор запроса содержит высокий процент строк таблицы, то просмотр таблицы может оказаться самым эффективным методом.However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.

Когда оптимизатор запросов использует индекс, он выполняет поиск по ключевым столбцам индекса, находит место хранения запрашиваемых строк и извлекает оттуда совпадающие строки.When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. В основном поиск по индексу протекает намного быстрее, чем поиск по таблице, так как в отличие от таблицы индекс часто содержит мало столбцов в каждой строке и строки расположены в отсортированном порядке.Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.

Оптимизатор запросов обычно выбирает наиболее эффективный метод при выполнении запросов.The query optimizer typically selects the most efficient method when executing queries. Но если отсутствуют доступные индексы, оптимизатор запросов должен использовать просмотр таблицы.However, if no indexes are available, the query optimizer must use a table scan. Ваша задача — спроектировать и создать индексы, которые лучше всего подходят для конкретной среды, чтобы оптимизатор запросов мог выбирать из нескольких эффективных индексов.Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select. SQL ServerSQL Server включает помощник по настройке ядра СУБД , который может помочь при анализе среды базы данных и при выборе соответствующих индексов.provides the Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes.

Связанное содержимоеRelated content

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

Некластеризованный индекс имеет точно такую же структуру, что и кластеризованный индекс, но с двумя важными отличиями:

♦ некластеризованный индекс не изменяет физический порядок строк в таблице;

♦ страницы листьев в некластеризованном индексе состоят из индексных ключей и закладок.

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

Закладка в некластеризованном индексе показывает, где находится строка, соответствующая индексному ключу. Закладка, как часть индексного ключа, может иметь две формы, в зависимости от формы таблицы, т. е. таблица может быть в кластеризованной форме или находиться в куче. (В терминологии SQL Server куча — это таблица без кластеризованного индекса. ) Если существует кластеризованный индекс, то закладка некластеризованного индекса указывает на структуру В-дерева кластеризованного индекса таблицы. Если в таблице нет кластеризованного индекса, то закладка идентична идентификатору строки (Row IDentifier, RID), который содержит три части: адрес файла, в котором располагается соответствующая таблица, адрес физического блока (страницы), в котором хранится строка, и смещение, которое является позицией строки внутри страницы.

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

♦ куча- проход по некластеризованной индексной структуре, за которым следует поиск строки с использованием RID;

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

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

Создает Ли Index() Кластерный Или Некластеризованный Индекс В Mysql?

TL; DR Первичный ключ — и только первичный ключ — это кластеризованный индекс. Если вы явно не определяете первичный ключ, используется первый подходящий ключ UNIQUE. Если у вас нет первичного ключа или подходящего ключа UNIQUE, MySQL генерирует скрытый кластеризованный индекс. Вы не можете создать кластеризованный индекс, используя INDEX().

Как объяснено в документах (выделено мной):

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

…

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

Если вы не определяете PRIMARY KEY для своей таблицы, MySQL находит первый индекс UNIQUE где все ключевые столбцы NOT NULL а InnoDB использует его как кластеризованный индекс.

Если таблица не имеет PRIMARY KEY или подходящего UNIQUE индекса, InnoDB внутренне генерирует скрытый кластеризованный индекс на синтетическом столбце, содержащем значения идентификатора строки. Строки упорядочены по идентификатору, который InnoDB присваивает строкам в такой таблице. Идентификатор строки — это 6-байтовое поле, которое монотонно возрастает при вставке новых строк. Таким образом, строки, упорядоченные идентификатором строки, физически находятся в порядке размещения.

…

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

См. Также определение кластерного индекса в глоссарии, который определяет его как «Термин InnoDB для индекса первичного ключа», а также некоторые дополнительные сведения.

Итак, чтобы ответить на ваш вопрос, нет способа создать кластерный индекс, кроме создания первичного ключа или, в таблице без первичного ключа, подходящий ключ UNIQUE (все ключевые столбцы NOT NULL). INDEX() просто создает вторичный (т.е. некластеризованный) ключ, независимо от того, что вы с ним делаете.

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

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

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

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

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

Ключевые области покрыты

1. Что такое индекс
— определение, функциональность
2. Что такое кластерный индекс
— определение, функциональность
3. Что такое некластеризованный индекс
— определение, функциональность
4. В чем разница между кластерным и некластеризованным индексом
— Сравнение основных различий

Основные условия

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

Что такое индекс

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

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

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

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

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

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

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

Определение

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

Количество индексов

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

функциональность

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

Порядок хранения данных

Кроме того, кластерный индекс определяет порядок хранения данных на диске, в то время как некластеризованный индекс не влияет на порядок хранения данных на диске.

Требуемое пространство памяти

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

скорость

Скорость — это еще одно различие между кластеризованным и некластеризованным индексом. Некластеризованные индексы работают медленнее, чем кластеризованные индексы.

Заключение

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

Ссылка:

1. Ядав, Дургапрасад. «Индекс на сервере Sql». LinkedIn SlideShare, 21 ноября 2015 г.,

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

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

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

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

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

В
примере, приведенном на рис.8.8, вначале
осуществляется поиск в диапазоне.

Для
этого выполняется один просмотр индекса,
что позволяет выполнить выборку данных
с помощью некластеризованного индекса
для обнаружения непрерывного участка
данных, соответствующих применяемому
критерию (LIKE ‘T%’).
Просмотр такого рода, который позволяет
перейти непосредственно к конкретному
участку индекса, называется позиционированием
(seek).

После
этого начинается операция поиска второго
типа — поиск с использованием
кластеризованного индекса. Такая вторая
операция поиска осуществляется с очень
высоким быстродействием; единственная
проблема заключается в том, что эта
операция должна выполняться многократно.
Дело в том, что в СУБД SQL Server после
осуществления первой операции поиска
с помощью индекса формируется список
(в котором находятся все имена, начинающиеся
с буквы «т»), но этот список логически
не согласуется с кластеризованным
ключом в виде каких-либо непрерывных
участков, поэтому поиск каждой строки
с данными должен выполняться отдельно,
как показано на рис. 8.9.

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

Например,
если данные хранятся в таблице с длиной
строки, равной 1000 байтов, и выполняется
операция поиска, аналогичная показанной
на рис. 9.8 (скажем, такая операция, которая
должна возвратить 5 или 6 строк), ТО должно
потребоваться примерно 8-10 логических
операций чтения для получения информации
из некластеризованного индекса. Но
выполнение ЭТИХ операций чтения позволяет
лишь подготовиться к чтению строк с
помощью кластеризованного индекса. На
выполнение каждой такой операции поиска
потребуется приблизительно 3-4 логических
операций чтения, или 15-24 дополнительных
операций чтения. На первый взгляд
кажется, что такое увеличение количества
операций не имеет особого значения,
поэтому рассмотрим эту ситуацию под
другим углом зрения.

Количество
логических операций чтения увеличивается
от минимального значения 3 до максимального
значения 24, что соответствует увеличению
объема работы, подлежащей выполнению,
на 800%.

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

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

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

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

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

Сравнительная таблица

Определение кластерного индекса

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

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

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

Определение некластеризованного индекса

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

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

Ключевые различия между кластеризованным и некластеризованным индексом

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

Заключение

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

Задачи, связанные с созданием индекса

Рекомендуемая стратегия создания индексов включает следующие задачи:
Проектирование индекса.

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

Определение наилучшего метода создания индекса. Индекс можно создать несколькими способами:

Определив для столбца ограничение PRIMARY KEY или UNIQUE с использованием инструкции CREATE TABLE или ALTER TABLE

Компонент SQL Server Database Engine автоматически создает уникальный индекс, чтобы обеспечить уникальность требований ограничения PRIMARY KEY или UNIQUE. По умолчанию с целью форсирования ограничения PRIMARY KEY создается уникальный кластеризованный индекс, если только кластеризованный индекс уже не создан для таблицы и если не указан уникальный некластеризованный индекс. Чтобы форсировать ограничение UNIQUE, по умолчанию создается уникальный некластеризованный индекс, если явно не указан уникальный кластеризованный индекс и не существует связанного с таблицей кластеризованного индекса.

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

Индексу, созданному посредством ограничения PRIMARY KEY или UNIQUE, автоматически назначается имя, эквивалентное имени ограничения. Дополнительные сведения см. в разделах Ограничения PRIMARY KEY и Ограничения UNIQUE.

Создав индекс независимо от ограничения с использованием инструкции CREATE INDEX или диалогового окна Создание индекса обозревателя объектов среды Среда SQL Server Management Studio.

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

Создание индекса.

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

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

Замечания по реализации
Максимальные значения, допустимые для кластеризованных, некластеризованных, пространственных, отфильтрованных и XML-индексов, приведены в следующей таблице. Если не указано иное, эти ограничения относятся к индексам всех типов.
Показатель Максимальное значение Дополнительные сведения
Количество кластеризованных индексов в таблице 1
Количество некластеризованных индексов таблицы 999 Включает некластеризованные индексы, созданные ограничениями PRIMARY KEY или UNIQUE, отфильтрованные индексы, но не включает XML-индексы.
Количество XML-индексов таблицы 249 Включает первичные и вторичные XML-индексы столбцов типа xml. Индексы для столбцов типа данных xml
Количество пространственных индексов на таблицу 249 Работа с пространственными индексами (компонент Database Engine)
Количество ключевых столбцов на индекс 16* Если таблица содержит первичный XML-индекс или любое число пространственных индексов, то кластеризованный индекс ограничен 15 столбцами. Максимальный размер ключей индекса.
Размер записи ключа индекса 900 байт* Не применяется к XML-индексам и пространственным индексам. Для поддержки таблицей пространственных индексов размер ключа индекса не должен превышать 895 байт. Максимальный размер ключей индекса.
*При работе с некластеризованными индексами можно избежать ограничений, связанных с ключевым столбцом индекса и размером записи, включив в индекс неключевые столбцы. Дополнительные сведения см. в разделе Индекс с включенными столбцами.
Типы данных
Как правило, проиндексировать можно любой столбец таблицы или представления. Типы данных, на использование которых в индексах распространяются ограничения, указаны в следующей таблице.
Тип данных Использование в индексе Дополнительные сведения
Определяемый пользователем тип данных CLR Может быть проиндексирован, если тип поддерживает двоичное упорядочение. Работа с определяемыми пользователем типами данных CLR
Типы данных больших объектов (LOB): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml Не могут использоваться в качестве столбца ключа индекса. Однако столбец типа XML может быть ключевым столбцом в первичном или вторичном XML-индексе таблицы. Все эти типы, кроме image, ntext и text, можно использовать в качестве неключевых (включенных) столбцов некластеризованного индекса. Могут быть использованы в индексе, если входят в выражение вычисляемого столбца. Индекс с включенными столбцами Индексы для столбцов типа данных xml
Вычисляемые столбцы Могут быть проиндексированы. В их число входят вычисляемые столбцы, определенные как вызовы методов для определяемого пользователем столбца типа данных CLR, если эти методы отмечены как детерминированные. Вычисляемые столбцы, производные от типов данных LOB, могут быть проиндексированы или как ключевые, или как неключевые столбцы, если тип вычисляемого столбца может быть использован в качестве столбца ключа индекса или неключевого столбца. Создание индексов вычисляемых столбцов
Столбцы Varchar с внестрочными данными Ключ кластеризованного индекса не может включать в себя столбцы varchar, для которых существуют данные в единице распределения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается на столбце varchar и существующие данные располагаются в единице распределения IN_ROW_DATA, то последующие операции вставки или обновления для данного столбца, принудительно отправляющие данные за пределы строки, будут завершаться ошибкой. Организация таблиц и индексов Превышающие размер страницы данные строки, превышающие 8 КБ
geometry Возможно индексирование с помощью нескольких пространственных индексов. Типы пространственных данных
Дополнительные сведения
При создании индекса следует учитывать и некоторые дополнительные факторы:
Чтобы создать индекс, необходимо разрешение CONTROL или ALTER на доступ к таблице.

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

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

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

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

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

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

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

Место хранения промежуточных результатов сортировки данных. Использование параметра SORT_IN_TEMPDB может сократить время, требуемое для создания индекса, если база данных tempdb находится на дисках, отличных от тех, на которых находится пользовательская база данных. Дополнительные сведения см. в разделе База данных tempdb и создание индекса.

Создание индекса в режиме в сети или вне сети.

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

За исключением XML-индексов и пространственных индексов, можно указать, чтобы индекс был создан в режиме «в сети». Если параметр режима в сети имеет значение ON, таблицы на длительное время не блокируются, что позволяет продолжать выполнение запросов или обновлений базовых таблиц во время создания индекса. Мы рекомендуем использовать операции с индексами в сети, однако в некоторых средах со специфическими требованиями иногда лучше выполнять операции над индексами в режиме вне сети. Это ограничивает доступ к данным на время операции, но сама операция выполняется быстрее и потребляет меньше ресурсов. Дополнительные сведения см. в разделе Выполнение операции с индексами в сети.

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

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

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

В SQL Server есть два типа индексов:

1. Кластерный индекс
2. Некластерный индекс

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

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

Давайте взглянем. Сначала создайте таблицу «student» внутри «schooldb», выполнив следующий сценарий, или убедитесь, что ваша база данных полностью зарезервирована, если вы используете свои данные в реальном времени:

Обратите внимание, что здесь, в таблице «student», мы установили ограничение первичного ключа для столбца «id».Это автоматически создает кластеризованный индекс по столбцу «id». Чтобы увидеть все индексы в конкретной таблице, выполните хранимую процедуру sp_helpindex. Эта хранимая процедура принимает имя таблицы в качестве параметра и извлекает все индексы таблицы. Следующий запрос извлекает индексы, созданные для таблицы учеников.

Вышеупомянутый запрос вернет следующий результат:

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

Другой способ просмотреть индексы таблиц — перейти в «Обозреватель объектов-> Базы данных-> Имя_базы_данных-> Таблицы-> Имя_таблицы -> Индексы». Взгляните на следующий снимок экрана для справки.

Этот кластерный индекс хранит запись в таблице учеников в порядке возрастания идентификатора. Следовательно, если вставленная запись имеет идентификатор 5, запись будет вставлена ​​в 5 ^{-ю строку таблицы} вместо первой строки.Точно так же, если четвертая запись имеет идентификатор 3, она будет вставлена ​​в третью строку вместо четвертой. Это связано с тем, что кластеризованный индекс должен поддерживать физический порядок сохраненных записей в соответствии с индексированным столбцом, то есть идентификатором. Чтобы увидеть этот порядок в действии, выполните следующий сценарий:

Приведенный выше сценарий вставляет десять записей в таблицу учеников.Обратите внимание, что записи вставляются в случайном порядке значений в столбце «id». Но из-за кластеризованного индекса по умолчанию в столбце id записи физически хранятся в порядке возрастания значений в столбце «id». Выполните следующую инструкцию SELECT, чтобы получить записи из таблицы учеников.

Записи будут извлечены в следующем порядке:

Создание настраиваемого кластерного индекса

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

Чтобы удалить индекс, перейдите в «Обозреватель объектов-> Базы данных-> Имя_базы_данных-> Таблицы-> Имя_таблицы -> Индексы». Щелкните правой кнопкой мыши индекс, который вы хотите удалить, и выберите УДАЛИТЬ.См. Снимок экрана ниже.

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

Процесс создания кластерного индекса аналогичен обычному индексу за одним исключением.При использовании кластерного индекса вы должны использовать ключевое слово «CLUSTERED» перед «INDEX».

Приведенный выше сценарий создает кластерный индекс с именем «IX_tblStudent_Gender_Score» в таблице студентов. Этот индекс создается по столбцам «пол» и «total_score». Индекс, созданный более чем для одного столбца, называется «составным индексом».

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

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

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

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

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

Создание некластеризованного индекса

Синтаксис для создания некластеризованного индекса аналогичен синтаксису кластеризованного индекса. Однако в случае некластеризованного индекса ключевое слово «NONCLUSTERED» используется вместо «CLUSTERED». Взгляните на следующий сценарий.

Приведенный выше сценарий создает некластеризованный индекс в столбце «имя» таблицы учеников.Индекс сортируется по имени в порядке возрастания. Как мы уже говорили ранее, данные таблицы и индекс будут храниться в разных местах. Записи таблицы будут отсортированы по кластеризованному индексу, если он есть. Индекс будет отсортирован в соответствии с его определением и будет храниться отдельно от таблицы.

Данные таблицы учеников:

IX_tblStudent_Name Данные индекса

Обратите внимание: здесь в индексе каждая строка имеет столбец, в котором хранится адрес строки, которой принадлежит имя.Таким образом, если выдается запрос на получение пола и даты рождения студента по имени «Джон», база данных сначала будет искать имя «Джон» внутри индекса. Затем он прочитает адрес строки «Jon» и перейдет непосредственно к этой строке в таблице «student», чтобы получить пол и дату рождения Джона.

Заключение

Из обсуждения мы обнаруживаем следующие различия между кластерными и некластеризованными индексами.

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

Другие замечательные статьи от Ben

Бен Ричардсон руководит Acuity Training, ведущим поставщиком обучения SQL в Великобритании.Он предлагает полный спектр обучения SQL от вводных курсов до углубленного обучения администрированию и хранилищам данных — подробнее см. Здесь. У Acuity есть офисы в Лондоне и Гилфорде, графство Суррей. Он также иногда ведет блог в блоге Acuity

Посмотреть все сообщения Бена Ричардсона

Последние сообщения Бена Ричардсона (посмотреть все)

Индексы

О Бен Ричардсон

Бен Ричардсон руководит Acuity Training, ведущим поставщиком обучения SQL в Великобритании.Он предлагает полный спектр обучения SQL от вводных курсов до углубленного обучения администрированию и хранилищам данных — подробнее см. Здесь. У Acuity есть офисы в Лондоне и Гилфорде, графство Суррей. Он также иногда ведет блог в блоге Acuity.

Просмотреть все сообщения Бена Ричардсона

614,923 Просмотров

Взаимодействие с другими людьми

Когда использовать кластерные или некластеризованные индексы в SQL Server

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

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

Индексы

SQL Server можно разделить на два основных типа:

1. Кластерные индексы
2. Некластеризованные индексы

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

Начнем с кластерного индекса.

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

Кластерные индексы по умолчанию

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

СОЗДАТЬ БАЗУ ДАННЫХ Госпиталь

СОЗДАТЬ ТАБЛИЦУ Пациенты
(
id INT PRIMARY KEY,
имя VARCHAR (50) NOT NULL,
пол VARCHAR (50) NOT NULL,
возраст INT NOT NULL
)

Приведенный выше сценарий создает фиктивную базу данных Hospital. В базе 4 столбца: id, имя, пол, возраст. Столбец id является столбцом первичного ключа. При выполнении вышеуказанного сценария кластеризованный индекс автоматически создается в столбце id.Чтобы увидеть все индексы в таблице, вы можете использовать хранимую процедуру «sp_helpindex».

USE Hospital
EXECUTE sp_helpindex Пациенты

Вот результат:

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

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

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

ИСПОЛЬЗОВАТЬ Госпиталь
ИЗМЕНИТЬ ТАБЛИЦУ Пациенты
ОГРАНИЧЕНИЕ ОТКАЗА PK__Patients__3213E83F3DFAFAAD
GO

Следующий сценарий создает настраиваемый индекс «IX_tblPatient_Age» в столбце возраста таблицы «Пациенты».Благодаря этому индексу все записи в таблице пациентов будут храниться в порядке возрастания возраста.


использовать Госпиталь
СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС IX_tblPatient_Age
ON Пациенты (возраст ASC)


Давайте теперь добавим несколько фиктивных записей в таблицу «Пациенты», чтобы увидеть, действительно ли они вставлены в порядке возрастания возраста:

USE Hospital

ВСТАВИТЬ В Пациенты

ЗНАЧЕНИЯ
(1, «Сара», «Женщина», 34),
(2, «Джон», «Мужчина», 20),
(3, «Майк» , «Мужской», 54),
(4, «Ана», «Женский», 10),
(5, «Ник», «Женский», 29)

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

ВЫБРАТЬ * ИЗ ПАЦИЕНТОВ

Вот результат:

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

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

Чтобы создать некластеризованный индекс, вы должны использовать оператор «CREATE NONCLUSTERED». Остальной синтаксис остается таким же, как и синтаксис для создания кластерного индекса. Следующий сценарий создает некластеризованный индекс «IX_tblPatient_Name», который сортирует записи в порядке возрастания имени.

использовать Госпиталь
СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС IX_tblPatient_Name
ON Пациенты (имя ASC)

Приведенный выше сценарий создаст индекс, содержащий имена пациентов и адреса их соответствующих записей, как показано ниже:

Здесь «Адрес записи» в каждой строке — это ссылка на фактические записи таблицы для пациентов с соответствующими именами.

Например, если вы хотите получить возраст и пол пациента с именем «Майк», база данных сначала будет искать «Мик» в некластеризованном индексе «IX_tblPatient_Name», а из некластеризованного индекса будет извлекаться фактическая запись. ссылку и будет использовать ее для возврата фактического возраста и пола Пациента по имени «Майк»

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

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

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

1. Количество указателей

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

2. Операции SELECT

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

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

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

3. Операции INSERT / UPDATE

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

4. Дисковое пространство

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

5. Окончательный приговор

Как правило, каждая таблица должна иметь по крайней мере один кластеризованный индекс, предпочтительно в столбце, который используется для ВЫБОРА записей и содержит уникальные значения. Столбец первичного ключа — идеальный кандидат для кластерного индекса.

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

Куч в SQL Server: Часть 3 Некластеризованные индексы

Пока что серий:

1. Кучи в SQL Server: часть 1, основы
2. кучи в SQL Server: часть 2 Оптимизация чтения
3. Кучи в SQL Server: часть 3 Некластеризованные индексы
4. Кучи в SQL Server: часть 4, конкуренция за PFS

В предыдущей статье «Кучи в SQL Server: часть 2, оптимизация чтения» описывалось, как можно оптимизировать производительность для выбора данных из кучи.В этой статье описывается возможность достижения оптимального времени запроса куч с помощью некластеризованных индексов.

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

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

Демонстрация

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

• Номер дела
• Страница данных
• Слот

Эти три части информации хранятся в качестве ссылки в каждом некластеризованном индексе для фактического ключа индекса.

Чтобы следить за демонстрациями, см. Статью Куча в SQL Server: Часть 2 Оптимизация чтения. Используется таблица с примерно 4 000 000 записей данных.

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

Используется сканирование таблицы, как показано в плане выполнения на рисунке 1.

Рисунок 1. СКАНИРОВАНИЕ ТАБЛИЦЫ используется для 208 записей

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

Рисунок 2: ЦП используется более 1,6 секунды, а прошло 0,716 секунды

Высокое время ЦП связано с тем, что запрос использует параллельное выполнение, показанное на рисунке 3.

Рисунок 3: Параллельный план

К сожалению, часто игнорируются следующие вещи, актуальные вне зависимости от времени:

• Запрос распараллеливает и использует процессоры, настроенные для MAXDOP!
• Блокировка [SCH-S] сохраняется в таблице во время выполнения! (См. Эту статью)
• Что произойдет, если не только один пользователь выполняет запрос, но также есть веб-клиент, выполняющий тысячи запросов параллельно?

По причинам, указанным выше, желательно оптимизировать запрос.Для оптимизации запроса создается некластеризованный индекс для атрибута [OrderDate] .

Если вы повторно выполните тот же запрос, что и в предыдущем примере, вы получите следующие улучшения, показанные на рисунке 4:

Рисунок 4: Меньше операций ввода-вывода за счет использования соответствующего индекса

Теперь запрос больше не сканирует таблицу, и затраченное время также значительно уменьшилось, как показано на рисунке 5:

Рисунок 5: Отсутствие чрезмерного использования ресурсов

Некоторые недостатки сканирования таблицы устранены за счет использования индекса:

• Запрос больше не распараллеливается, поскольку затраты упали ниже порогового значения для распараллеливания
• Ресурсы ЦП больше не могут быть измерены
• По-прежнему используется только блокировка [SCH-S]; однако, что касается времени выполнения — может быть — его можно проигнорировать.

Можно сказать, что некластеризованные индексы для куч могут быть большим преимуществом!

Внутренние конструкции

Некластеризованный индекс (NCI) должен ВСЕГДА хранить ссылку на запись в таблице. Поскольку NCI обычно содержит только несколько атрибутов таблицы, необходимо гарантировать, что атрибуты, которые не используются в NCI, могут быть определены из таблицы в любое время. Эта ссылка хранится в куче как RID (RowLocatorID) и имеет размер 8 байт!

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

Рисунок 6. Поиск в B-древовидной структуре индекса

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

Получите подробную информацию о структурах индекса, используя системный dmv для раскрытия секретов, как показано на рисунке 7.Имейте в виду, что индексы с ID = 0 или ID = 1 представляют собой саму таблицу! ID = 0 — это куча, а ID = 1 — кластерный индекс. Любые другие индексы некластеризованы.

Рисунок 7: Индексы

Корневой узел индекса [nix_CustomerOrderList_OrderDate] находится в файле №1 на странице данных 73.346. Содержимое страницы данных можно просмотреть с помощью недокументированной команды DBCC PAGE.

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

Рисунок 8: Поиск 2008-12-20 должен продолжаться на странице 73.348

Для поиска заказов на 20 декабря 2008 г. необходимо использовать атрибут [OrderDate] для поиска лимита, в котором находится искомая дата. Поиск продолжается на следующем уровне на странице 73.348, пока не будет достигнут самый низкий уровень.

Рисунок 9: С 3 входами / выходами вы достигнете первой записи в индексе

Фактически израсходовано три ввода-вывода.Учитывая общее количество операций ввода-вывода, равное 211, быстро становится ясно, что произойдет на следующем этапе. Поскольку индекс хранит только атрибут [OrderDate] , информация о других атрибутах для записи данных отсутствует. Чтобы получить эту информацию, Microsoft SQL Server должен декодировать RID, чтобы перейти на страницу данных, на которой расположена запись.

RID хранится в индексе для КАЖДОЙ записи и имеет фиксированную длину 8 байтов. Эти 8 байтов содержат всю необходимую информацию для доступа к записи данных.

Рисунок 10: Положение набора данных как RID

Запись, [OrderDate] которой представлена ​​в индексе, находится в файле №1 на странице данных № 219.904 в слоте №8.

Рисунок 11: Содержимое слота № 8

Процесс повторяется для каждой записи, найденной в индексе. Итак, математика, стоящая за результатом 211, довольно тривиальна: 3 ввода-вывода для поиска в индексе + 208 * 1 ввод-вывод для ссылки в самой таблице.

Рисунок 12: Доступ к куче по RID

Поиск RID против поиска ключа

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

Рисунок 13: Дополнительный ввод-вывод с использованием кластерного индекса

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

Рисунок 14: RID и планы выполнения поиска ключей

При поиске RID необходимо преобразовать RowLocatorID, что приведет к — предположительно — более высокой загрузке ЦП. Одна из самых больших проблем в планах выполнения Microsoft SQL Server — это оценка стоимости СКАЛЯРНОЙ операции. По умолчанию они рассчитываются как 0%; однако он забудет, что оператор для КАЖДОЙ записи должен запускаться из предыдущего оператора.

Чтобы сделать обоснованное заявление о стоимости преобразования, следующий код выполняется с SQLQueryStress Адамом Мачаником (b | t) с четырьмя потоками (количество ЦП в тестовой машине):

Рисунок 15: Производительность кучи и кластеризованного индекса

При прямом сравнении куча «выигрывает» во всех дисциплинах. В этом сравнении особенно интересен тот факт, что загрузка ЦП 3,3 мс / итерация значительно отстает от загрузки ЦП кластеризованного индекса в 5 мс.

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

Сводка

Почему разработчики из среды Microsoft SQL Server так сильно сопротивляются кучам? Во многих статьях в блогах известных экспертов по SQL Server неоднократно указывается, что было бы полезно предоставить таблицу с кластеризованным индексом. Раньше я всегда следовал этим рекомендациям, пока не прочитал две статьи, которые заставили меня «переосмыслить»:

Я могу только предложить переосмыслить «общий» подход к использованию кластерных индексов.Я не хочу отрицать, что есть ситуации, в которых кластерный индекс имеет смысл, а также является обязательным; это определенно не требуется для чистых операций SELECT . Принцип при выборе данных заключается в индексировании атрибутов, которые используются как предикат и / или как операторы JOIN .

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

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

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

Пример кластерного индекса

Предположим, у нас есть таблица с именем Employee, в которой есть столбец с именем EmployeeID. Допустим, мы создаем кластерный индекс по столбцу EmployeeID. Что происходит, когда мы создаем этот кластерный индекс? Итак, все строки внутри таблицы Employee будут физически — отсортированы (на фактическом диске) — по значениям внутри столбца EmployeeID.Что это дает? Что ж, это означает, что всякий раз, когда поиск / поиск последовательности идентификаторов EmployeeID выполняется с использованием этого кластерного индекса, поиск будет намного быстрее из-за того, что последовательность идентификаторов сотрудников физически хранится рядом друг с другом на диске — в этом преимущество кластерного индекса. Это связано с тем, что строк в таблице сортируются в том же порядке, что и кластерный индекс , а фактические данные таблицы хранятся в конечных узлах кластеризованного индекса.

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

Когда имеет смысл использовать кластерный индекс?

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

 Владельцы
Имя владельца
Owner_Age

Легковые автомобили
Тип автомобиля
Имя владельца
 

Предположим, у одного владельца может быть несколько автомобилей, поэтому одно имя Owner_Name может появляться в таблице Cars несколько раз.
Теперь предположим, что мы создаем кластерный индекс для столбца Owner_Name в таблице Cars. Что это дает нам? Ну, поскольку кластеризованный индекс физически хранится на диске в том же порядке, что и индекс, это будет означать, что для данного Owner_Name все записи о его / ее автомобилях будут храниться рядом друг с другом на диске.Другими словами, если есть владелец по имени «Джо Смит» или «Радж Гупта», то у каждого владельца все его записи в таблице Cars будут храниться рядом друг с другом на диске.

Когда использование кластерного индекса дает преимущество?

В чем преимущество этого? Что ж, предположим, что существует часто выполняемый запрос, который пытается найти все автомобили, принадлежащие определенному владельцу. С кластеризованным индексом, поскольку все записи автомобилей, принадлежащие одному владельцу, будут находиться рядом друг с другом на диске, запрос будет выполняться намного быстрее, чем если бы строки хранились на диске в некотором случайном порядке. И это ключевой момент, о котором нужно помнить!

Почему это называется кластеризованным индексом?

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

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

В чем недостаток использования кластерного индекса?

Недостатком использования кластеризованного индекса является тот факт, что если для данной строки обновлено значение в одном из ее (кластеризованных) индексированных столбцов, то обычно происходит то, что базе данных придется переместить всю строку, чтобы таблица продолжала оставаться отсортировано в том же порядке, что и столбец кластеризованного индекса. Рассмотрим наш пример выше, чтобы прояснить это. Предположим, некто по имени Рафаэль Надаль покупает машину — скажем, Porsche — у Роджера Федерера.Помните, что наш кластерный индекс создается в столбце Owner_Name. Это означает, что когда мы выполняем обновление, чтобы изменить имя в этой строке в таблице Cars, Owner_Name будет изменено с «Роджер Федерер» на «Рафаэль Надаль».

Но поскольку кластеризованный индекс также сообщает базе данных, в каком порядке физически хранить строки на диске, при изменении Owner_Name ему придется переместить обновленную строку, чтобы она все еще находилась в правильном порядке сортировки . Итак, теперь строку, которая раньше принадлежала «Роджеру Федереру», нужно будет переместить на диск, чтобы она была сгруппирована (или сгруппирована) со всеми записями о машинах, принадлежащими «Рафаэлю Надалю».Ясно, что это удар по производительности. Это означает, что простое UPDATE превратилось в DELETE, а затем в INSERT — просто для поддержания порядка кластеризованного индекса. Именно по этой причине кластеризованные индексы обычно создаются на первичных или внешних ключах, поскольку эти значения с меньшей вероятностью изменятся, если они уже являются частью таблицы.

Сравнение некластеризованного индекса с кластеризованным индексом с примером

В качестве примера некластеризованного индекса предположим, что у нас есть некластеризованный индекс в столбце EmployeeID.Некластеризованный индекс будет хранить как значение EmployeeID , так и указатель на строку в таблице Employee, где это значение фактически хранится. Но кластерный индекс, с другой стороны, фактически будет хранить данные строки для определенного EmployeeID, поэтому, если вы выполняете запрос, который ищет EmployeeID, равный 15, данные из других столбцов в таблице, таких как EmployeeName, EmployeeAddress и т. Д. фактически все будет храниться в конечном узле самого кластерного индекса.

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

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

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

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

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

Сводка различий между кластеризованными и некластеризованными индексами

Вот краткое описание различий:

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

Oracle SQL и PL / SQL: кластерные и некластеризованные индексы

Кластерные индексы и Некластеризованные индексы — Все, что вам нужно знать

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

В SQL Server индексы организованы как B-деревья. Каждая страница в индексном B-дереве называется индексным узлом. Верхний узел B-дерева называется корневым узлом. Нижний уровень узлов индекса называется листовыми узлами. Любые уровни индекса между корневым и конечным узлами в совокупности называются промежуточными уровнями. В кластеризованном индексе конечные узлы содержат страницы данных базовой таблицы.Узлы корневого и промежуточного уровней содержат индексные страницы, содержащие индексные строки. Каждая строка индекса содержит значение ключа и указатель либо на страницу промежуточного уровня в B-дереве, либо на строку данных на конечном уровне индекса. Страницы на каждом уровне индекса связаны в двусвязном списке. [Источник]

Некластеризованные индексы имеют ту же структуру B-дерева, что и кластеризованные индексы, за исключением следующих существенных различий:

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

Конечный слой некластеризованного индекса состоит из страниц индекса, а не страниц данных.

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

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

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

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

1. Физически хранятся в порядке (по возрастанию или убыванию)
2. Только один на таблицу
3. При создании первичного ключа автоматически создается кластерный индекс.
4. Если таблица подвергается значительным изменениям данных или для поиска используется первичный ключ, рекомендуется кластеризованный индекс по первичному ключу.
5. Столбцы со значениями, которые не меняются совсем или очень редко, являются лучшим выбором.
6. Для использования в столбцах, в которых часто выполняется поиск диапазонов данных
7.Для столбцов с низкой избирательностью

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

1. Для каждой таблицы или индексированного представления возможно до 249 некластеризованных индексов.
2. Ключи кластерного индекса используются для поиска, поэтому ключи кластеризованного индекса следует выбирать с минимальной длиной.
3. Охваченные запросы (все столбцы, используемые для объединения, сортировки или фильтрации, индексируются) не должны быть кластеризованы.
4. Внешние ключи не должны быть кластеризованными.
5. Если в таблице выполняется интенсивное извлечение данных из полей, отличных от первичного ключа, следует создать один кластеризованный индекс и / или один или несколько некластеризованных индексов для столбца (столбцов), используемых для извлечения данных.
6. Для столбцов, в которых выполняется поиск отдельных значений
7. Для использования в столбцах с высокой избирательностью [Источник]

Каковы различия (плюсы / минусы) между кластеризованными и некластеризованными индексами
StackOverflow

Некластеризованный индекс предпочтительнее кластеризованного в следующих ситуациях:

Когда размер ключа индекса велик.

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

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

Когда все столбцы (из таблицы), на которые ссылается запрос, могут быть безопасно размещены в самом некластеризованном индексе.

Производительность извлечения данных при использовании некластеризованного индекса обычно ниже, чем при использовании кластерного индекса, из-за затрат, связанных с переходом от строк некластеризованного индекса к строкам данных в базовой таблице. В случаях, когда переход к строкам данных не требуется, производительность некластеризованного индекса должна быть такой же или даже лучше, чем у кластеризованного индекса. Это возможно, если ключ некластеризованного индекса включает все столбцы, необходимые для таблицы.[Источник: Настройка производительности запросов SQL Server 2008, разработанная Грантом Фритчи, Sajal Dam]

Кластерные индексы не подходят для следующих атрибутов:

Столбцы, которые претерпевают частые изменения : Это приводит к тому, что во всей строке move, потому что компонент Database Engine должен сохранять значения данных строки в физическом порядке. Это важное соображение в системах обработки больших объемов транзакций, в которых данные обычно непостоянны.

Широкие клавиши : Широкие клавиши состоят из нескольких столбцов или нескольких столбцов большого размера.Значения ключей из кластеризованного индекса используются всеми некластеризованными индексами в качестве ключей поиска. Любые некластеризованные индексы, определенные в той же таблице, будут значительно больше, поскольку записи некластеризованного индекса содержат ключ кластеризации, а также ключевые столбцы, определенные для этого некластеризованного индекса. [Источник]

Еще несколько полезных материалов по индексам из Интернета:

Как индексы работают с представлениями?
StackOverflow

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

Реализация индексов
MSDN

Архитектура таблиц и индексов
MSDN

Руководство по индексам
Simple Talk

Поддерживайте свои индексы
Simple Talk

Реализация некластеризованных индексов

Последнее обновление: понедельник, 25 мая 2015 г.

Некластеризованные индексы имеют структуру, очень похожую на кластеризованные.Фактически, корневой и промежуточный уровни выглядят так же, как в кластеризованном индексе. Конечный уровень отличается, потому что он не содержит всех данных. То, что хранится на конечном уровне некластеризованного индекса, зависит от организации базовой таблицы, независимо от того, организована ли она как куча или как сбалансированное дерево. В одной таблице может быть до 999 некластеризованных индексов.
Конечный уровень некластеризованного индекса содержит ключи индекса и указатели строк. Опять же, вы можете иметь до 16 столбцов в ключе, и размер всех столбцов вместе в составном ключе не должен превышать 900 байт.Локатор строк указывает на строку в базовой таблице. Если таблица представляет собой кучу, то указатель строки называется идентификатором строки (RID). Это 8-байтовый указатель, содержащий идентификатор файла базы данных и идентификатор страницы целевой строки, а также идентификатор целевой строки на этой странице.
На рис. 15-3 показан некластеризованный индекс в куче. Здесь используется тот же пример таблицы заказов клиентов, что и на других рисунках в этой главе. Столбец порядкового номера используется в качестве ключа индекса.
Для поиска строки SQL Server необходимо пройти индекс до конечного уровня, а затем прочитать соответствующую страницу из кучи и получить строку со страницы.Операция получения строки из кучи называется поиском RID. Если ваш запрос очень избирательный и ищет только одну строку или небольшое количество строк, то поиск по индексу с поиском RID очень эффективен. Поскольку страницы на одном уровне индекса связаны в двусвязный список, SQL Server также может выполнять частичное или полное упорядоченное сканирование некластеризованного индекса, а затем выполнять поиск RID, не начиная путь от корневой страницы для каждой строки.
Однако по мере увеличения числа строк, извлекаемых запросом, поиск RID становится намного дороже, поскольку стоимость поиска RID обычно составляет одну страницу на строку.
Если таблица организована как сбалансированное дерево, то локатор строк является ключом кластеризации. Это означает, что когда SQL Server ищет строку, он должен пройти все уровни некластеризованного индекса, а затем также все уровни кластеризованного индекса. Эта операция называется поиском по ключу. На первый взгляд, это звучит хуже, чем получение отдельной страницы из кучи. Однако, поскольку в этом случае локатор строк указывает на логическую структуру, а не на физическую структуру, не имеет значения, где физически расположена строка в таблице.Это означает, что вы можете свободно реорганизовать или перестроить кластерный индекс; пока вы не меняете ключ кластеризации, SQL Server не должен обновлять некластеризованные индексы. Если строка перемещается в куче, SQL Server необходимо обновить все некластеризованные индексы, чтобы отразить новую позицию. В SQL Server есть оптимизация для обновлений кучи; если строка должна переместиться на другую страницу, SQL Server оставляет указатель пересылки в новое место на исходной странице, поэтому SQL Server по-прежнему не должен обновлять все некластеризованные индексы.Однако даже при такой оптимизации рекомендуется организовывать таблицы в виде сбалансированных деревьев. Если ключ кластеризации узкий — например, 4-байтовое целое число, — SQL Server также может разместить больше строк на странице конечного уровня, чем когда RID используется в качестве локатора строк.

На рис. 15-4 показан некластеризованный индекс кластеризованной таблицы. Это тот же пример таблицы заказов клиентов; данные заказа используются для ключа кластеризации, а идентификатор заказа используется для ключа некластеризованного индекса.
Обратите внимание, что ключ кластеризации не уникален, и поэтому к повторяющимся значениям добавляется уникальный указатель. Ключ некластеризованного индекса уникален. Если запрос ищет определенный идентификатор заказа (одну строку) и получает ключ кластеризации с датой заказа, равной более чем для одной строки, то SQL Server вернет неверный набор результатов. SQL Server вернет все строки с одинаковой датой заказа. Это, конечно, недопустимо. Следовательно, SQL Server должен поддерживать уникальность ключей кластеризации внутри себя.
Ключ кластеризации должен быть коротким и уникальным, поскольку он присутствует во всех некластеризованных индексах. Однако обратите внимание еще раз, что это не общее правило; в сценариях хранилища данных вы можете предпочесть выбрать ключ кластеризации, который поддерживает частое частичное сканирование. В любом случае кластеризация не должна часто меняться или, желательно, не должна меняться вообще. Если вы обновляете ключ кластеризации, SQL Server должен обновить все некластеризованные индексы. Вы также должны сначала создать кластерный индекс, а затем все некластеризованные индексы.Если вы измените структуру таблицы с кучи на сбалансированное дерево или наоборот, создав или отбросив кластеризованный индекс, а таблица имеет существующие некластеризованные индексы, SQL Server придется воссоздать все некластеризованные индексы.
Вы можете создать отфильтрованный некластеризованный индекс. Отфильтрованный индекс охватывает только подмножество значений столбцов и, таким образом, применяется к подмножеству строк таблицы. Отфильтрованные некластеризованные индексы полезны, когда одни значения в столбце встречаются редко, тогда как другие значения встречаются часто. В таких случаях вы должны создать отфильтрованный индекс только для редких значений.SQL Server использует этот индекс для поиска редких значений, но выполняет сканирование для частых значений. Отфильтрованные индексы недороги в обслуживании, потому что SQL Server должен обновлять их только для изменений в редких значениях. Вы создаете отфильтрованный индекс, добавляя предложение WHERE к оператору CREATE INDEX.
Вы можете использовать отфильтрованный индекс, чтобы обеспечить отфильтрованную уникальность. Например, представьте, что столбец содержит NULL в нескольких строках; однако известные значения должны быть уникальными. Вы не можете создать отфильтрованный первичный ключ или уникальное ограничение; однако вы можете создать отфильтрованный уникальный некластеризованный индекс только на основе известных значений, что позволит использовать несколько значений NULL и отклонить повторяющиеся известные значения.
SQL Server 2012 имеет метод хранения некластеризованных индексов. В дополнение к обычному хранению строк SQL Server 2012 может хранить данные индекса столбец за столбцом в так называемом индексе columnstore. Индексы Columnstore могут значительно ускорить запросы к хранилищу данных — от 10 до 100 раз.
Индекс columnstore — это просто еще один некластеризованный индекс таблицы. Оптимизатор запросов SQL Server рассматривает возможность использования индекса columnstore на этапе оптимизации запроса, как и любого другого индекса.Все, что вам нужно сделать, чтобы воспользоваться этой функцией, — это создать индекс columnstore в таблице.

Индекс columnstore хранится в сжатом виде. Коэффициент сжатия может в 10 раз превышать исходный размер индекса. Когда запрос ссылается на один столбец, который является частью индекса columnstore, SQL Server выбирает с диска только этот столбец; он не извлекает целые строки, как в случае с хранилищем строк. Это также снижает объем дискового ввода-вывода и потребление кеш-памяти.
индексы Columnstore используют собственный алгоритм сжатия; вы не можете использовать сжатие строк или страниц в индексе columnstore.
С другой стороны, SQL Server должен возвращать строки. Следовательно, строки должны быть восстановлены при выполнении запроса. Эта реконструкция строки занимает некоторое время и использует некоторые ресурсы ЦП и памяти. Для очень избирательных запросов, затрагивающих только несколько строк, индексы columnstore могут не использовать.
Индексы Columnstore ускоряют запросы к хранилищу данных, а не рабочие нагрузки OLTP. Из-за проблем с реконструкцией строк и других накладных расходов при обновлении сжатых данных таблицы, содержащие индекс columnstore, становятся доступны только для чтения.Если вы хотите обновить таблицу с помощью индекса columnstore, вы должны сначала удалить индекс columnstore. Если вы используете секционирование таблицы, вы можете переключить секцию на другую таблицу, которая не использует индекс columnstore, обновить там данные, создать индекс columnstore для этой таблицы (которая имеет меньшее подмножество данных), а затем переключить данные новой таблицы обратно в раздел исходной таблицы.
Индекс columnstore разделен на блоки, называемые сегментами. Сегменты хранятся как большие объекты и состоят из нескольких страниц.Сегменты — это единица передачи с диска в память.
Каждый сегмент имеет метаданные, в которых хранятся минимальное и максимальное значение каждого столбца для этого сегмента. Это дает возможность раннего исключения сегмента в механизме хранения. SQL Server загружает в память только те сегменты, которые запрашиваются запросом.

Основы индексации: некластеризованные индексы, часть 1

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

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

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

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

Кластерный индекс сообщает SQL Server, как физически сортировать и хранить данные таблицы.Считается, что это самый важный индекс, который вы можете иметь в таблице, и Microsoft заявляет, что «За некоторыми исключениями каждая таблица должна иметь кластерный индекс». Поскольку строки данных в таблице могут храниться только в одном порядке, для каждой таблицы может быть только один кластеризованный индекс. Поскольку все будет использовать кластерный индекс для поиска данных в вашей таблице, это наиболее реплицируемая структура данных в вашей базе данных SQL Server. Дополнительные сведения о кластерных индексах см. В моей публикации «Основы индекса: кластерные индексы».

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

Сколько некластеризованных индексов может содержать моя таблица?

Очень много. На самом деле, иногда даже слишком много. SQL Server допускает использование нескольких некластеризованных индексов для таблицы, количество зависит от версии SQL Server, которую вы используете. SQL Server 2005 допускает 249 некластеризованных индексов на таблицу, в то время как SQL Server 2008 и более поздние версии (на момент написания этой статьи 2016 г.) допускают 999 некластеризованных индексов на таблицу.Это много индексов.

Сейчас самое время отметить, что то, что вы МОЖЕТЕ создать такое количество индексов, не всегда означает, что вам СЛЕДУЕТ. Базы данных, содержащие в основном данные только для чтения, могут извлечь выгоду из многих некластеризованных индексов, в то время как базы данных, содержащие сильно обновленные таблицы, должны избегать чрезмерного индексирования. По мере добавления и изменения данных в вашей таблице SQL Server также соответствующим образом изменяет данные во всех индексах этой таблицы. Большое количество индексов в таблице влияет на производительность операторов INSERT, UPDATE, DELETE и MERGE, поскольку SQL Server должен выполнять больше работы, чтобы эти индексы оставались точными.Итак, если у вас есть 999 индексов в таблице, и вы вставляете новую запись, это 1000 вставок, которые SQL Server должен сделать. 1 вставка для фактической таблицы, а затем 999 для каждого из индексов.

Как узнать, какие столбцы использовать в некластеризованном индексе?

Это вопрос на миллион долларов, и, к сожалению, нет универсального ответа. Столбцы, которые вы выбираете для своего индекса, будут зависеть от запроса или запросов, для которых вы создаете индекс. Хорошее практическое правило — индексировать столбцы, указанные в предложении WHERE, и столбцы, используемые в предложениях JOIN или GROUP BY в вашем запросе.

Вот несколько советов по некластеризованному индексированию :

• Поместите некластеризованный индекс в каждый столбец внешнего ключа в вашей таблице, это поможет ускорить обратное присоединение запросов к родительской таблице.
• Индексы должны быть узкими, с как можно меньшим количеством столбцов. Однако некластеризованные индексы для отдельных столбцов редко бывают эффективными (за исключением упомянутых выше столбцов внешнего ключа).
• Старайтесь избегать создания повторяющихся индексов. Если у вас есть два (или более) индекса по одним и тем же столбцам, избавьтесь от всех, кроме одного из них.Кроме того, если у вас есть несколько индексов, которые лишь незначительно отличаются друг от друга, попробуйте объединить их в один индекс. Лучше иметь меньшее количество более эффективных индексов, чем иметь много индексов, которые могут даже не использоваться.
• Столбец с несколькими уникальными значениями редко бывает хорошим кандидатом для индексации. Более уникальные данные в индексе делают индекс более полезным.
• Избегайте чрезмерного индексирования сильно обновляемых таблиц.
• Порядок столбцов в индексе важен.Как указано в руководстве Microsoft по созданию индекса SQL Server, «Столбец, который используется в предложении WHERE в условиях поиска равно (=), больше (>), меньше (<) или BETWEEN, либо участвует в присоединиться, следует поставить первым. Дополнительные столбцы следует упорядочить в зависимости от уровня их различимости, то есть от наиболее отчетливых до наименее отчетливых ».

Итог…

Некластеризованные индексы являются ключевым фактором повышения производительности запросов. Целью некластеризованного индекса является поддержка запроса или запросов.Он содержит подмножество данных вашей таблицы, которые SQL Server может сканировать быстрее и, в свою очередь, быстрее находить и возвращать данные в ваш запрос. Таблицы с низкими требованиями к обновлению могут извлечь выгоду из многих некластеризованных индексов, в то время как таблицы, которые сильно обновляются, должны избегать чрезмерного индексирования, поскольку это может повлиять на производительность операторов INSERT, UPDATE, DELETE и MERGE. Выбор правильных некластеризованных индексов и количества этих индексов иногда может стать балансом между скоростью запроса и стоимостью обновления.

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

Ресурсы:

Более подробное описание некластеризованных индексов см.