Руководство по архитектуре и разработке индексов SQL ServerSQL Server Index Architecture and Design Guide

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data WarehouseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data Warehouse

Плохо спроектированные индексы и их недостаточное количество — основной источник узких мест в приложениях баз данных.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. Проектирование эффективных индексов имеет первостепенную важность для достижения высокой производительности баз данных и приложений.Designing efficient indexes is paramount to achieving good database and application performance. Это руководство по проектированию индексов SQL ServerSQL Server содержит сведения об архитектуре индексов и рекомендации, руководствуясь которыми, вы сможете создавать эффективные индексы, удовлетворяющие потребностям ваших приложений.This SQL ServerSQL Server index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of your application.

Предполагается, что читатель обладает общими знаниями типов индексов, которые есть в SQL ServerSQL Server.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. Общее описание типов индексов приведено в разделе Типы индексов.For a general description of index types, see Index Types.

В этом руководстве рассматриваются следующие типы индексов:This guide covers the following types of indexes:

  • КластеризованныйClustered
  • НекластеризованныйNonclustered
  • Уникальная идентификацияUnique
  • FilteredFiltered
  • columnstoreColumnstore
  • ХэшHash
  • Некластеризованный индекс, оптимизированный для памятиMemory-Optimized Nonclustered

Сведения об XML-индексах см. в разделе XML-индексы.For information about XML indexes, see XML Indexes Overview.

Сведения о пространственных индексах см. в разделе Общие сведения о пространственных индексах.For information about Spatial indexes, see Spatial Indexes Overview.

Сведения о полнотекстовых индексах см. в разделе Заполнение полнотекстовых индексов.For information about Full-text indexes, see Populate Full-Text Indexes.

Основы проектирования индексовIndex Design Basics

Представьте себе обычную книгу: в конце книги есть указатель, который помогает быстро находить информацию в книге.Think about a regular book: at the end of the book there is an index which helps to quickly locate information within the book. Указатель представляет собой отсортированный список ключевых слов, а рядом с ключевым словом — номера страниц, где можно найти каждое ключевое слово.The index is a sorted list of keywords and next to each keyword is a set of page numbers pointing to the pages where each keyword can be found. Индекс SQL Server устроен так же. Это упорядоченный список значений, и для каждого значения есть указатели на страницы данных, где находятся эти значения.A SQL Server index is no different: it is an ordered list of values and for each value there are pointers to the data pages where these values are located. Сам индекс хранится на страницах индексов в SQL Server.The index itself is stored on pages, making up the Index Pages in SQL Server. В обычной книге, если указатель занимает несколько страниц и необходимо найти указатели на все страницы, содержащие слово "SQL", например, вам придется листать до тех пор, пока вы не найдете страницу указателя с ключевым словом "SQL".In a regular book, if the index spans multiple pages and you have to find pointers to all the pages that contain the word "SQL" for example, you would have to leaf through until you locate the index page that contains the keyword "SQL". После этого можно следовать указателям на все страницы книги.From there you follow the pointers to all the book pages. Этот процесс можно оптимизировать, если в самом начале индекса создать одну страницу, содержащую алфавитный список расположения каждой буквы.This could be optimized further if at the very beginning of the index, you create a single page that contains an alphabetical list of where each letter can be found. Пример: буквы от А до Г — стр. 121, буквы от Д до Ж — стр. 122 и т. д.For example: "A through D - page 121", "E through G - page 122" and so on. Благодаря этой дополнительной странице не придется перелистывать указатель, чтобы найти нужное место.This additional page would eliminate the step of leafing through the index to find the starting place. Такая страница не существует в обычных книгах, но существует в индексе SQL Server.Such page does not exist in regular books, but it does exist in a SQL Server index. Эта единственная страница называется корневой страницей индекса.This single page is referred to as the root page of the index. Корневая страница — это начальная страница древовидной структуры, используемой индексом SQL Server.The root page is the starting page of the tree structure used by a SQL Server index. Следуя аналогии дерева, конечные страницы, содержащие указатели на реальные данные, называются "листьями" дерева.Following the tree analogy, the end pages which contain pointers to the actual data are referred to as "leaf pages" of the tree.

Индекс SQL Server является структурой на диске или в памяти, которая связана с таблицей или представлением и ускоряет получение строк из таблицы или представления.A SQL Server index is an on-disk or in-memory 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 Server.For on-disk indexes, these keys are stored in a tree structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

Данные индекса логически упорядочиваются в виде таблицы по строкам и столбцам, а физически хранятся в строковом формате, который называется rowstore 1, или в столбчатом формате, который называется columnstore .An index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore 1, or stored in a column-wise data format called columnstore.

Выбор правильных индексов для базы данных и ее рабочей нагрузки — это решение сложной задачи о соотношении скорости обработки запроса и стоимости обновления.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Узкие индексы, то есть индексы, в ключе которых мало столбцов, требуют меньше места на диске и меньше текущих издержек.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. С другой стороны, широкие индексы охватывают больше запросов.Wide indexes, on the other hand, cover more queries. Прежде чем удастся найти наиболее эффективный индекс, возможно, придется поэкспериментировать с несколькими различными вариантами.You may have to experiment with several different designs before finding the most efficient index. Добавление, изменение и удаление индексов не влияет на схему базы данных или конструкцию приложений.Indexes can be added, modified, and dropped without affecting the database schema or application design. Следовательно, эксперименты с различными индексами можно проводить без каких-либо опасений.Therefore, you should not hesitate to experiment with different indexes.

Оптимизатор запросов в SQL ServerSQL Server с большой вероятностью выбирает наилучший индекс в подавляющем большинстве случаев.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. Общая стратегия разработки индексов должна давать оптимизатору запросов по возможности разнообразные варианты, чтобы ему было из чего выбирать. Следует довериться его решению.Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. Это уменьшит время анализа и обеспечит высокую производительность в различных ситуациях.This reduces analysis time and produces good performance over a variety of situations. Чтобы выяснить, какие индексы оптимизатор запросов использует для отдельных запросов, в меню Запрос среды SQL Server Management StudioSQL Server Management Studio выберите Включить действительный план выполнения.To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

Использование индекса не всегда означает высокую производительность, а высокая производительность не всегда означает эффективное использование индекса.Do not always equate index usage with good performance, and good performance with efficient index use. Если бы использование индекса всегда способствовало производительности, то работа оптимизатора запросов была бы очень простой.If using an index always helped produce the best performance, the job of the query optimizer would be simple. На самом деле, неверный выбор индекса может привести к неоптимальной производительности.In reality, an incorrect index choice can cause less than optimal performance. Следовательно, задача оптимизатора запросов состоит в том, чтобы выбрать индекс или комбинацию индексов, если это улучшит производительность, и избежать индексированного поиска, если это ее понизит.Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

1 Rowstore — это традиционный способ хранения реляционных данных таблиц.1 Rowstore has been the traditional way to store relational table data. В SQL ServerSQL Server rowstore — это таблица с базовым форматом хранения данных "куча", "сбалансированное дерево" (кластеризованный индекс) или "таблица, оптимизированная для памяти".In SQL ServerSQL Server, rowstore refers to table where the underlying data storage format is a heap, a B-tree (clustered index), or a memory-optimized table.

Задачи проектирования индексовIndex Design Tasks

Рекомендуемая стратегия проектирования индексов включает в себя следующие задачи:The follow tasks make up our recommended strategy for designing indexes:

  1. Прежде всего следует понять характеристики самой базы данных.Understand the characteristics of the database itself.

  2. Определите наиболее часто используемые запросы.Understand the characteristics of the most frequently used queries. Например, если известно, что часто используется запрос на соединение двух и более таблиц, это поможет определить наилучший тип индексов.For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. Выясните характеристики столбцов, используемых в запросах.Understand the characteristics of the columns used in the queries. Например, идеальным будет индекс для столбцов с типом данных integer, которые к тому же имеют уникальные или отличные от NULL значения.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. Для столбцов с хорошо определенными подмножествами данных в SQL Server 2008SQL Server 2008 и более поздних версиях можно использовать отфильтрованный индекс.For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. Дополнительные сведения см. в разделе Рекомендации по проектированию отфильтрованных индексов этого руководства.For more information, see Filtered Index Design Guidelines in this guide.

  4. Определите, какие параметры индексов могут повысить производительность при создании индекса или при его поддержке.Determine which index options might enhance performance when the index is created or maintained. Например, при создании кластеризованного индекса для существующей большой таблицы выгодно будет использовать параметр ONLINE.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. Параметр ONLINE позволяет продолжать параллельную обработку базовых данных во время создания или повторного построения индекса.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. Дополнительные сведения см. в разделе Установка параметров индекса.For more information, see Set Index Options.

  5. Определите оптимальное расположение для хранения индекса.Determine the optimal storage location for the index. Некластеризованный индекс может храниться в той же файловой группе, что и базовая таблица, или в другой группе.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. Правильный выбор расположения для хранения индексов может повысить производительность запросов за счет повышения скорости дискового ввода-вывода.The storage location of indexes can improve query performance by increasing disk I/O performance. Например, если некластеризованный индекс хранится в файловой группе не на том диске, на котором расположены файловые группы таблицы, то производительность может повыситься, поскольку это позволяет одновременно обращаться к нескольким дискам.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.
    Кластеризованные и некластеризованные индексы могут использовать схему секционирования, которая охватывает несколько файловых групп.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. Секционирование делает большие таблицы и индексы более управляемыми, позволяет быстро и эффективно получать доступ к наборам данных и управлять ими, при этом сохраняя целостность всей коллекции.Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. Дополнительные сведения см. в разделе Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes. При выборе секционирования определите, требуется ли выравнивание индекса, то есть должен ли индекс быть секционирован точно так же, как и таблицы, или он может быть секционирован иным образом.When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

Общие рекомендации по проектированию индексовGeneral Index Design Guidelines

Опытный администратор базы данных может спроектировать хороший набор индексов, но эта задача очень сложна, требует много времени и сопряжена с ошибками даже для рабочих нагрузок и баз данных средней сложности.Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. В разработке оптимальных индексов может помочь понимание характеристик базы данных, запросов и столбцов данных.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

Соображения, связанные с базами данныхDatabase Considerations

При проектировании индекса следует учитывать следующие рекомендации:When you design an index, consider the following database guidelines:

  • Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE, потому что при изменении данных в таблице все индексы должны быть скорректированы соответствующим образом.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Например, если столбец используется в нескольких индексах и выполняется инструкция UPDATE, которая изменяет данные из этого столбца, каждый индекс, содержащий этот столбец, должен быть обновлен, как и столбец в базовой таблице (куча или кластеризованный индекс).For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • Избегайте использования чрезмерного количества индексов для интенсивно обновляемых таблиц и следите, чтобы индексы были узкими, то есть содержали как можно меньше столбцов.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • Используйте большое количество индексов, чтобы улучшить производительность запросов для таблиц с низкими требованиями к обновлениям, но большими объемами данных.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. Большое число индексов может повысить производительность запросов, которые не изменяют данных, таких как инструкции SELECT, поскольку у оптимизатора запросов будет больший выбор индексов при определении самого быстрого способа доступа.Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • Индексирование маленьких таблиц может оказаться не лучшим выбором, так как поиск данных в индексе может потребовать у оптимизатора запросов больше времени, чем простой просмотр таблицы.Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Следовательно, для маленьких таблиц индексы могут вообще не использоваться, но тем не менее их необходимо поддерживать при изменении данных в таблице.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • Индексы представлений могут дать значительное улучшение производительности, если представление содержит агрегаты, соединения таблиц или сочетание того и другого.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. Необязательно явно ссылаться в запросе на представление, чтобы его мог использовать оптимизатор запросов.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • Для анализа базы данных и получения рекомендаций по созданию индексов следует использовать помощник по настройке ядра СУБД.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. Дополнительные сведения см. в разделе Database Engine Tuning Advisor.For more information, see Database Engine Tuning Advisor.

Вопросы работы с запросамиQuery Considerations

При проектировании индекса следует принимать во внимание следующие рекомендации, связанные с обработкой запросов.When you design an index, consider the following query guidelines:

  • Следует создавать некластеризованные индексы для столбцов, которые часто используются в предикатах и условиях соединения в запросах.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. В нашем примере это столбцы SARGable1.These are your SARGable1 columns. Однако следует избегать добавления столбцов без необходимости.However, you should avoid adding unnecessary columns. Добавление слишком большого числа индексных столбцов может отрицательно повлиять на количество свободного места на диске и на производительность поддержания индекса.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • Покрывающие индексы могут повысить производительность запросов, так как данные, необходимые для удовлетворения требований запроса, присутствуют в самом индексе.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. Таким образом, для получения запрашиваемых данных требуются только страницы индекса, а не страницы данных таблицы или кластеризованного индекса. Следовательно, уменьшается общий объем операций дискового ввода-вывода.That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. Например, запрос по столбцам a и b таблицы, у которой есть составной индекс, созданный на основе столбцов a, bи c , может найти нужные данные, пользуясь только этим индексом.For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

    Важно!

    Покрывающими индексами называются некластеризованные индексы, которые разрешают один или несколько схожих результатов запроса напрямую, без доступа к базовой таблице и без уточняющих запросов.Covering indexes are the designation for a nonclustered index that resolves one or several similar query results directly with no access to its base table, and without incurring in lookups. Такие индексы имеют на конечном уровне все необходимые столбцы, отличные от SARGable.Such indexes have all the necessary non-SARGable columns in its leaf level. Это означает, что индекс включает столбцы, возвращаемые предложением SELECT и указываемые в любых аргументах WHERE и JOIN.This means that the columns returned by either the SELECT clause and all the WHERE and JOIN arguments are covered by the index. Это позволяет существенно снизить объем операций ввода-вывода для выполнения запроса, если индекс будет достаточно узким по сравнению с количеством строк и столбцов в самой таблице, то есть будет правильным подмножеством ее столбцов.There is potentially much less I/O to execute the query, if the index is narrow enough when compared to the rows and columns in the table itself, meaning it is a real sub-set of the total columns. Используйте покрывающие индексы, при обращении к небольшому фрагменту большой таблицы, который определяется фиксированным предикатом, например разреженными столбцами с малым числом непустых значений.Consider covering indexes when selecting a small portion of a large table, and where that small portion is defined by a fixed predicate, such as sparse columns that contain only a few non-NULL values, for example.

  • Запросы следует составлять так, чтобы они вставляли или изменяли как можно больше строк одной инструкцией, вместо того, чтобы использовать для обновления тех же строк нескольких запросов.Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. Используя только одну инструкцию, можно воспользоваться возможностями, которые обеспечивает поддержание оптимизированного индекса.By using only one statement, optimized index maintenance could be exploited.

  • Определите тип запроса и то, как в нем используются столбцы.Evaluate the query type and how columns are used in the query. Например: столбец, который используется в запросе с точным соответствием, может оказаться подходящим кандидатом для создания кластеризованного или некластеризованного индекса.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

1 Термин SARGable в реляционных базах данных обозначает сочетание предикатов Searchable (поддерживающий поиск) и ARGument -able (поддерживающий аргументы), которые позволяют использовать индекс для ускорения запроса.1 The term SARGable in relational databases refers to a Search ARGument-able predicate that can leverage an index to speed up the execution of the query.

Вопросы работы со столбцамиColumn Considerations

При проектировании индекса, следует принимать во внимание следующие рекомендации, относящиеся к столбцам.When you design an index consider the following column guidelines:

  • Нужно следить, чтобы длина ключа для кластеризованных индексов была небольшой.Keep the length of the index key short for clustered indexes. Кроме того, кластеризованные индексы только выиграют при создании на основе уникальных или не принимающих значения NULL столбцах.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • Столбцы с типами данных ntext, text, image, varchar(max) , nvarchar(max) и varbinary(max) нельзя указать в качестве ключевых столбцов индекса.Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. Однако типы данных varchar(max) , nvarchar(max) , varbinary(max) и xml могут участвовать в некластеризованных индексах в качестве их неключевых столбцов индекса.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. Дополнительные сведения см. в разделе Индекс с включенными столбцамиэтого руководства.For more information, see the section 'Index with Included Columns' in this guide.

  • Столбцы типа xml могут быть ключевым столбцом только в XML-индексе.An xml data type can only be a key column only in an XML index. Дополнительные сведения см в разделе XML-индексы (SQL Server).For more information, see XML Indexes (SQL Server). С пакетом обновления 1 (SP1) в SQL Server 2012 появился новый тип XML-индекса — выборочный XML-индекс.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. Этот новый индекс повышает производительность запросов для данных, хранимых в виде XML на SQL Server, и тем самым значительно ускоряет индексирование рабочих нагрузок XML-данных большого объема и повышает масштабируемость за счет уменьшения места хранения самого индекса.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. Дополнительные сведения см. в разделе Выборочный XML-индекс (SXI).For more information, see Selective XML Indexes (SXI).

  • Проверьте уникальность столбцов.Examine column uniqueness. Замена неуникального индекса уникальным для той же комбинации столбцов обеспечивает оптимизатору запросов дополнительные сведения, что делает индекс более полезным.A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. Дополнительные сведения см. в разделе Правила по созданию уникальных индексов этого руководства.For more information, see Unique Index Design Guidelines in this guide.

  • Проверьте распределение данных в столбце.Examine data distribution in the column. Часто длительное выполнение запроса обусловлено индексированием столбца, в котором мало уникальных значений, или присоединением такого столбца.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. Это фундаментальная проблема, связанная с данными и запросом, и обычно она не может быть решена без определения ситуации.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. Например: физический телефонный справочник, отсортированный в алфавитном порядке по фамилии, не сможет быстро найти человека, если всех жителей города зовут Смит или Джонс.For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. Дополнительные сведения о распределении данных см. в разделе Statistics.For more information about data distribution, see Statistics.

  • Попробуйте применить отфильтрованные индексы для столбцов, имеющих точно определенные подмножества, например разреженных столбцов, столбцов, содержащих в основном значения NULL, столбцов с разнородными категориями значений и столбцов с различными диапазонами значений.Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. Правильно составленный отфильтрованный индекс может увеличить скорость выполнения запроса, уменьшить стоимость обслуживания индекса и стоимость хранения.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • Следует учитывать порядок столбцов, если индекс будет включать их несколько.Consider the order of the columns if the index will contain multiple columns. Столбец, использованный в предложении WHERE в условии поиска равных (=), больших (>), меньших (<) или находящихся в интервале (BETWEEN) значений или участвующий в соединении, должен стоять первым.The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Дополнительные столбцы должны быть упорядочены по уровню различимости, то есть от наиболее четкого к наименее четкому.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    Например, если индекс определен как LastName, FirstName , индекс будет полезным, если критерий поиска — WHERE LastName = 'Smith' или WHERE LastName = Smith AND FirstName LIKE 'J%'.For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. Однако оптимизатор запросов не станет использовать этот индекс для запроса только по критерию FirstName (WHERE FirstName = 'Jane').However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • Следует рассмотреть возможность индексирования вычисляемых столбцов.Consider indexing computed columns. Дополнительные сведения см. в разделе Индексы вычисляемых столбцов.For more information, see Indexes on Computed Columns.

Характеристики индексаIndex Characteristics

После того, как определено, что индекс соответствует запросу, можно выбрать наилучший тип индекса для конкретной ситуации.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. Ниже представлены характеристики индекса:Index characteristics include the following:

  • кластеризованный или некластеризованный;Clustered versus nonclustered
  • уникальный или неуникальный;Unique versus nonunique
  • с одним или несколькими столбцами;Single column versus multicolumn
  • порядок по возрастанию или по убыванию в столбцах индекса;Ascending or descending order on the columns in the index
  • полнотабличные или фильтруемые некластеризованные индексы.Full-table versus filtered for nonclustered indexes
  • columnstore или rowstore;Columnstore versus rowstore
  • хэш-индекс или некластеризованный индекс для таблиц, оптимизированных для памяти.Hash versus nonclustered for Memory-Optimized tables

Также можно задать начальные характеристики хранилища индекса, чтобы оптимизировать его производительность или поддержание, задав такие параметры, как FILLFACTOR.You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. Чтобы оптимизировать производительность, можно также определить место хранения индекса с помощью файловых групп или схем секционирования.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

Размещение индекса в файловых группах или схемах секцийIndex Placement on Filegroups or Partitions Schemes

Во время разработки стратегии индексирования следует обратить внимание на помещение индексов в файловые группы, связанные с базой данных.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. Аккуратный выбор схемы файловой группы или секционирования может улучшить производительность.Careful selection of the filegroup or partition scheme can improve query performance.

По умолчанию индексы хранятся в той же файловой группе, что и базовая таблица, для которой создается индекс.By default, indexes are stored in the same filegroup as the base table on which the index is created. Несекционированный некластеризованный индекс и базовая таблица всегда находятся в одной файловой группе.A nonpartitioned clustered index and the base table always reside in the same filegroup. Однако можно сделать следующее.However, you can do the following:

  • Создайте некластеризованные индексы в файловой группе, отличной от файловой группы базовой таблицы или кластеризованного индекса.Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.
  • Секционировать кластеризованные и некластеризованные индексы, чтобы они размещались в нескольких файловых группах.Partition clustered and nonclustered indexes to span multiple filegroups.
  • Переместить таблицу из одной файловой группы в другую, удалив кластеризованный индекс и указав новую файловую группу или схему секционирования в предложении MOVE TO инструкции DROP INDEX или выполнив инструкцию CREATE INDEX с предложением DROP_EXISTING.Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

Создав некластеризованный индекс в другой файловой группе, можно достичь прироста производительности, если файловые группы находятся на разных физических дисках с собственными контроллерами.By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Сведения о данных и индексе могут считываться параллельно несколькими головками.Data and index information can then be read in parallel by the multiple disk heads. Например, если таблица Table_A в файловой группе f1 и индекс Index_A в файловой группе f2 используются в одном и том же запросе, производительность увеличится, так как обе файловые группы используются полностью, не состязаясь между собой.For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. Однако если таблица Table_A сканируется запросом, а на индекс Index_A ссылки нет, то используется только файловая группа f1 .However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. В этом случае нет никакого выигрыша в производительности.This creates no performance gain.

Поскольку тип и время необходимого доступа спрогнозировать невозможно, лучшим решением может оказаться распределение таблиц и индексов по всем файловым группам.Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. Это гарантирует, что доступ будет осуществляться ко всем дискам, так как все данные и индексы равномерно распределены по ним, независимо от способа доступа к данным.This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. Для системных администраторов этот подход также более прост.This is also a simpler approach for system administrators.

Секции во многих файловых группахPartitions across multiple Filegroups

Можно рассмотреть возможность секционирования кластеризованных и некластеризованных индексов по нескольким файловым группам.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. Секционированные индексы разбиваются горизонтально или построчно, в зависимости от функции секционирования.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. Функция секционирования определяет, как каждая строка сопоставляется с набором секций на основе значений определенных столбцов — столбцов секционирования.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. Схема секционирования определяет сопоставление секций набору файловых групп.A partition scheme specifies the mapping of the partitions to a set of filegroups.

Секционирование индекса может предоставить следующие преимущества.Partitioning an index can provide the following benefits:

  • Система становится более масштабируемой, а управление большими индексами в ней упрощается.Provide scalable systems that make large indexes more manageable. Например, в системах OLTP можно реализовать приложения, учитывающие секционирование и работающие с большими индексами.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • Запросы выполняются быстрее и эффективнее.Make queries run faster and more efficiently. Когда запросы выполняются в нескольких секциях индекса, оптимизатор запросов может обрабатывать определенные секции в одно и то же время и исключать секции, к которым запрос не относится.When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

Дополнительные сведения см. в разделе Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes.

Рекомендации по созданию порядка сортировки индексовIndex Sort Order Design Guidelines

При определении индексов следует иметь в виду, что данные ключевых столбцов индекса сохраняются в порядке возрастания или убывания.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. По умолчанию сортировка производится по возрастанию, как и в предыдущих версиях SQL ServerSQL Server.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. Синтаксис инструкций CREATE INDEX, CREATE TABLE и ALTER TABLE поддерживает ключевые слова ASC (по возрастанию) и DESC (по убыванию) для конкретных столбцов в индексах и ограничениях.The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

Указание порядка, в котором значения ключей хранятся в индексе, полезно тогда, когда запрос ссылается на таблицу с предложением ORDER BY, в котором указано другое направление для ключевого столбца индекса или индексированного столбца.Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. В этом случае индекс может исключить необходимость в операторе SORT в плане запроса, то есть запрос будет выполняться значительно эффективнее.In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. Например, покупателю в отделе заказов Компания Adventure Works CyclesAdventure Works Cycles необходимо определить качество товаров от разных поставщиков.For example, the buyers in the Компания Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. Больше всего его интересуют товары тех поставщиков, которые имеют набольшую частоту отказов.The buyers are most interested in finding products sent by these vendors with a high rejection rate. Как показано в следующем запросе, получение данных по соответствию этому критерию требует, чтобы столбец RejectedQty в таблице Purchasing.PurchaseOrderDetail был отсортирован в порядке убывания (от большего значения к меньшему), а столбец ProductID — в порядке возрастания (от меньшего к большему).As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

Следующий план выполнения для этого запроса показывает, что оптимизатор запросов применяет оператор SORT для результирующего набора в порядке, указываемом предложением ORDER BY.The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

IndexSort1

Если создан индекс для ключевых столбцов, который соответствует индексу, указанному в предложении ORDER BY, оператор SORT может быть исключен из плана запроса, что значительно повысит его эффективность.If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

После повторного выполнения запроса план выполнения показывает, что оператор SORT исключен и используется вновь созданный некластеризованный индекс.After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

InsertSort2

Компонент Database EngineDatabase Engine одинаково эффективно работает в обоих направлениях.The Компонент Database EngineDatabase Engine can move equally efficiently in either direction. Индекс, определенный как (RejectedQty DESC, ProductID ASC) , может также быть использован в запросах с порядком сортировки, обратным порядку, определенному в предложении ORDER BY.An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. Например, запрос со следующим предложением ORDER BY-: ORDER BY RejectedQty ASC, ProductID DESC может воспользоваться этим индексом.For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

Порядок сортировки может быть указан только для ключевых столбцов в индексе.Sort order can be specified only for the key columns in index. Представление каталога sys.index_columns и функция INDEXKEY_PROPERTY помогут определить, в каком порядке хранится столбец индекса — возрастающем или убывающем.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

МетаданныеMetadata

Используйте приведенные ниже представления метаданных, чтобы увидеть атрибуты индексов.Use these metadata views to see attributes of indexes. В некоторых из этих представлений содержатся дополнительные сведения об архитектуре.More architectural information is embedded in some of these views.

Примечание

Все столбцы в индексах columnstore хранятся в метаданных как включенные столбцы.For columnstore indexes, all columns are stored in the metadata as included columns. Индекс columnstore не имеет ключевых столбцов.The columnstore index does not have key columns.

Правила проектирования кластеризованного индексаClustered Index Design Guidelines

Кластеризованные индексы сортируют и хранят строки данных в таблице, основываясь на их ключевых значениях.Clustered indexes sort and store the data rows in the table based on their key values. Может быть только один кластеризованный индекс на таблицу, потому что сами строки данных могут быть отсортированы только в одном порядке.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. За несколькими исключениями, каждая таблица должна иметь кластеризованный индекс, определенный по столбцу или столбцам и предлагающий следующее:With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • Может применяться для часто используемых запросов.Can be used for frequently used queries.

  • Обеспечивает высокую степень уникальности.Provide a high degree of uniqueness.

    Примечание

    При создании ограничения PRIMARY KEY будет автоматически создан уникальный индекс по столбцу или столбцам.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. По умолчанию этот индекс кластеризован; однако при создании ограничения можно указать некластеризованный индекс.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • Может использоваться в диапазонных запросах.Can be used in range queries.

Если кластеризованный индекс создан не со свойством UNIQUE, компонент Компонент Database EngineDatabase Engine автоматически добавляет 4-байтовый столбец uniqueifier к таблице.If the clustered index is not created with the UNIQUE property, the Компонент Database EngineDatabase Engine automatically adds a 4-byte uniqueifier column to the table. При необходимости компонент Компонент Database EngineDatabase Engine автоматически добавляет значение uniqueifier к строке, чтобы сделать каждый ключ уникальным.When it is required, the Компонент Database EngineDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. Данный столбец и его значения используются внутри и недоступны пользователям для просмотра или использования.This column and its values are used internally and cannot be seen or accessed by users.

Архитектура кластеризованного индексаClustered Index Architecture

В SQL ServerSQL Server индексы организованы в виде сбалансированных деревьев.In SQL ServerSQL Server, indexes are organized as B-Trees. Каждая страница в сбалансированном дереве индекса называется узлом индекса.Each page in an index B-tree is called an index node. Верхний узел сбалансированного дерева называется корневым.The top node of the B-tree is called the root node. Узлы нижнего уровня индекса называются конечными.The bottom nodes in the index are called the leaf nodes. Все уровни индекса между корневыми и конечными узлами называются промежуточными.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. В кластеризованном индексе конечные узлы содержат страницы данных базовой таблицы.In a clustered index, the leaf nodes contain the data pages of the underlying table. На страницах индекса корневого и промежуточного узлов находятся строки индекса.The root and intermediate level nodes contain index pages holding index rows. Каждая строка индекса содержит ключевое значение и указатель либо на страницу промежуточного уровня сбалансированного дерева, либо на строку данных на конечном уровне индекса.Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. Страницы на каждом уровне связаны в двунаправленный список.The pages in each level of the index are linked in a doubly-linked list.

Для каждого кластеризованного индекса таблица sys.partitionsсодержит одну строку со значением index_id , равным 1 для каждой секции, используемой индексом.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. По умолчанию, кластеризованный индекс занимает одну секцию.By default, a clustered index has a single partition. Если кластеризованный индекс занимает несколько секций, каждая секция содержит сбалансированное дерево, содержащее данные этой секции.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. Например, если кластеризованный индекс занимает четыре секции, существует четыре сбалансированных дерева: по одному в каждой секции.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

В зависимости от типов данных, каждая структура кластеризованного индекса состоит из одной или более единиц распределения, которые применяются для хранения и управления данными секции.Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Для каждой секции кластеризованный индекс содержит, как минимум, одну единицу распределения IN_ROW_DATA.At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. Для хранения столбцов больших объектов (LOB) кластеризованному индексу требуется одна единица размещения LOB_DATA для каждой секции.The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Кроме того, для хранения столбцов переменной длины, превышающих ограничение на размер строки, равное 8060 байтам, для каждой секции требуется одна единица размещения ROW_OVERFLOW_DATA.It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

Страницы в цепочке данных и строки, которые они содержат, упорядочены по значению ключа кластеризованного индекса.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. Все строки вставляются так, чтобы значение ключа составляло вместе с существующими строками упорядоченную последовательность.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

На следующем рисунке изображена структура кластеризованного индекса для одной секции.This illustration shows the structure of a clustered index in a single partition.

bokind2

Вопросы работы с запросамиQuery Considerations

Прежде чем создать кластеризованные индексы, необходимо понять, как будет происходить обращение к данным.Before you create clustered indexes, understand how your data will be accessed. Рассмотрим использование кластеризованного индекса для запросов, которые выполняют следующее:Consider using a clustered index for queries that do the following:

  • Возвращают диапазон значений с помощью операторов, таких как BETWEEN, >, >=, < и <=.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    После того как строка с первым значением будет найдена с помощью кластеризованного индекса, строки с последующими индексированными значениями гарантированно окажутся физически смежными с этой строкой.After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. Например, если запрос получает записи из диапазона порядковых номеров продаж, кластеризованный индекс по столбцу SalesOrderNumber может быстро определить расположение строки, содержащей стартовый порядковый номер продаж, и затем извлечь все последующие строки из таблицы, пока не будет достигнут последний порядковый номер продаж.For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • Возвращают большие результирующие наборы.Return large result sets.

  • Используют предложения JOIN; обычно в них участвуют внешние ключевые столбцы.Use JOIN clauses; typically these are foreign key columns.

  • Используют предложения ORDER BY или GROUP BY.Use ORDER BY or GROUP BY clauses.

    Индекс по столбцам, указанным в предложении ORDER BY или GROUP BY, может исключить потребность в сортировке данных для компонента Компонент Database EngineDatabase Engine , потому что строки будут уже отсортированы.An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Компонент Database EngineDatabase Engine to sort the data, because the rows are already sorted. Это улучшает производительность запроса.This improves query performance.

Вопросы работы со столбцамиColumn Considerations

В общем случае надо так определить ключ кластеризованного индекса, чтобы в него вошло как можно меньше столбцов.Generally, you should define the clustered index key with as few columns as possible. Рассмотрите столбцы, которым присущ один или несколько следующих атрибутов:Consider columns that have one or more of the following attributes:

  • Являются уникальными или содержат много различных значений.Are unique or contain many distinct values

    Например, идентификатор сотрудника уникально идентифицирует служащих.For example, an employee ID uniquely identifies employees. Кластеризованный индекс или ограничение PRIMARY KEY на столбец EmployeeID улучшило бы производительность запросов, которые производят поиск сведений о сотруднике, основываясь на номере идентификатора сотрудника.A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. В качестве альтернативы кластеризованный индекс мог бы быть создан по столбцам LastName, FirstNameи MiddleName , потому что записи сотрудников часто группируются и запрашиваются именно таким образом, так что сочетание этих столбцов обеспечивало бы высокую степень различия.Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

    Совет

    Если не указано иное, при создании ограничения PRIMARY KEYSQL ServerSQL Server создает кластеризованный индекс для поддержки этого ограничения.If not specified differently, when creating a PRIMARY KEY constraint, SQL ServerSQL Server creates a clustered index to support that constraint. Хотя для обеспечения уникальности в качестве PRIMARY KEY можно использовать uniqueidentifier , он не является эффективным ключом кластеризации.Although a uniqueidentifier can be used to enforce uniqueness as a PRIMARY KEY, it is not an efficient clustering key. При применении uniqueidentifier в качестве PRIMARY KEY рекомендуется создавать его как некластеризованный индекс и использовать другой столбец, например IDENTITY, для создания кластеризованного индекса.If using a uniqueidentifier as PRIMARY KEY, the recommendation is to create it as a nonclustered index, and use another column such as an IDENTITY to create the clustered index.

  • Обращение к ним происходит последовательно.Are accessed sequentially

    Например, код продукта уникально идентифицирует продукты в таблице Production.Product в базе данных AdventureWorks2012AdventureWorks2012 .For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. Запросы, в которых указан последовательный поиск, например WHERE ProductID BETWEEN 980 and 999, извлекут заметную выгоду из кластеризованного индекса по ProductID.Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. Это происходит потому, что строки будут храниться в отсортированном порядке по этому ключевому столбцу.This is because the rows would be stored in sorted order on that key column.

  • Определен как IDENTITY.Defined as IDENTITY.

  • Часто используются для сортировки данных, полученных из таблицы.Used frequently to sort the data retrieved from a table.

    Это может служить хорошей идеей для кластеризации (которая по сути является физической сортировкой) таблицы по этому столбцу, потому что исключит затраты на сортировку при каждом запросе к столбцу.It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

Кластеризованные индексы — не лучший выбор для следующих атрибутов:Clustered indexes are not a good choice for the following attributes:

  • столбцов, которые подвергаются частым изменениям;Columns that undergo frequent changes

    Изменения вызывают перемещение целых строк, потому что компонент Компонент Database EngineDatabase Engine должен сохранять значения данных строки в физическом порядке.This causes in the whole row to move, because the Компонент Database EngineDatabase Engine must keep the data values of a row in physical order. Это важно при работе в крупномасштабных системах обработки транзакций, в которых данные обычно быстро меняются.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • широких ключей.Wide keys

    Широкие ключи составлены из нескольких столбцов или нескольких столбцов большого размера.Wide keys are a composite of several columns or several large-size columns. Ключевые значения из кластеризованного индекса используются всеми некластеризованными индексами как уточняющие запросы.The key values from the clustered index are used by all nonclustered indexes as lookup keys. Любые некластеризованные индексы, определенные на той же самой таблице, будут значительно больше, потому что записи некластеризованного индекса содержат ключ кластеризации, а также ключевые столбцы, определенные для этого некластеризованного индекса.Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

Рекомендации по созданию некластеризованных индексовNonclustered Index Design Guidelines

Некластеризованный индекс содержит значения ключей индекса и указатели строк, которые указывают на место хранения табличных данных.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. Можно создать несколько некластеризованных индексов для таблицы или индексированного представления.You can create multiple nonclustered indexes on a table or indexed view. Как правило, некластеризованные индексы следует создавать для повышения эффективности часто используемых запросов, не учитываемых кластеризованными индексами.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

Подобно тому, как читатель использует индекс в книге, оптимизатор запросов выискивает значение типа данных, просматривая некластеризованный индекс. Там он находит место расположения интересующего его значения в таблице и затем получает данные непосредственно из этого места.Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. Благодаря этому некластеризованные индексы считаются оптимальным выбором для запросов с точным соответствием, поскольку такие индексы содержат записи, описывающие точное расположение в таблице значений типов данных, которые задаются в подобных запросах.This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. К примеру, чтобы выбрать в таблице HumanResources. Employee всех сотрудников, подчиняющихся тому или иному менеджеру, оптимизатор запросов может воспользоваться некластеризованным индексом IX_Employee_ManagerID; ключевым столбцом в нем является ManagerID .For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. Оптимизатор запросов может быстро обнаружить в индексе все записи, соответствующие указанному значению ManagerID.The query optimizer can quickly find all entries in the index that match the specified ManagerID. Каждый элемент указателя ссылается на конкретную страницу и строку в таблице или на кластеризованный индекс, в котором можно найти соответствующие данные.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. После того как оптимизатор запросов обнаружит все записи в индексе, он может переходить непосредственно к нужной странице и строке, откуда он будет получать требуемые данные.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Архитектура некластеризованного индексаNonclustered Index Architecture

Некластеризованные индексы имеют ту же структуру сбалансированного дерева, что и кластеризованные индексы; существуют только следующие различия:Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • строки данных в базовой таблице не сортируются и хранятся в порядке, который основан на их некластеризованных ключах;The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • конечный уровень некластеризованного индекса состоит из страниц индекса вместо страниц данных.The leaf level of a nonclustered index is made up of index pages instead of data pages.

Указатели строк на строках некластеризованных индексов являются либо указателем на строку, либо ключом кластеризованного индекса для строки, как описано ниже.The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • Если таблица является кучей, что означает, что она не содержит кластеризованный индекс, то обнаружитель строки является указателем на строку.If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. Указатель строится на основе идентификатора файла (ID), номера страницы и номера строки на странице.The pointer is built from the file identifier (ID), page number, and number of the row on the page. Весь указатель целиком называется идентификатором строки (RID).The whole pointer is known as a Row ID (RID).

  • Если для таблицы имеется кластеризованный индекс или индекс построен на индексированном представлении, то указатель строки — это ключ кластеризованного индекса для строки.If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

Для некластеризованных индексов таблица sys.partitions содержит одну строку со значением столбца index_id >1 для каждой секции, используемой индексом.Nonclustered indexes have one row in sys.partitions with index_id > 1 for each partition used by the index. По умолчанию некластеризованный индекс включает одну секцию.By default, a nonclustered index has a single partition. Если некластеризованный индекс состоит из нескольких секций, то каждая секция имеет структуру сбалансированного дерева, в которой содержатся индексные строки для данной конкретной секции.When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. Например, если некластеризованный индекс состоит из четырех секций, то существуют четыре структуры сбалансированного дерева, по одной на каждую секцию.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

В зависимости от типов данных в некластеризованном индексе каждая структура некластеризованного индекса будет содержать одну или более единиц распределения, в которых хранятся данные для определенной секции.Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Каждый некластеризованный индекс будет содержать по меньшей мере одну единицу размещения IN_ROW_DATA на секцию, в которой хранятся страницы сбалансированного дерева индекса.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. Некластеризованный индекс будет также содержать одну единицу размещения LOB_DATA на секцию, если в индексе содержатся столбцы типа большого объекта (LOB).The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Кроме того, некластеризованный индекс будет содержать одну единицу размещения ROW_OVERFLOW_DATA на секцию, если в индексе содержатся столбцы переменной длины, в которых превышается максимальный размер строки, равный 8060 байт.Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

На следующей иллюстрации показана структура некластеризованного индекса, состоящего из одной секции.The following illustration shows the structure of a nonclustered index in a single partition.

bokind1a

Соображения, связанные с базами данныхDatabase Considerations

При проектировании некластеризованных индексов следует принимать во внимание характеристики соответствующей базы данных.Consider the characteristics of the database when designing nonclustered indexes.

  • В базах данных или таблицах, характеризующихся нечастыми обновлениями и большими объемами хранимых данных, запросы могут выполняться быстрее при использовании множества некластеризованных индексов.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Рекомендуется создать отфильтрованные индексы для четко определенных подмножеств данных, что позволит повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными некластеризованными индексами.Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    Производительность приложений систем поддержки принятия решений, а также баз данных может быть увеличена за счет использования большого числа некластеризованных индексов.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. В процессе определения наиболее быстрого метода доступа оптимизатор запросов может выбирать из большего числа индексов, а если база данных не предназначена для частых обновлений, это значит, что операции по обслуживанию индексов не будут сказываться на ее быстродействии.The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • При работе с приложениями и базами данных обработки транзакций в сети (OLTP) следует избегать слишком большого числа индексов.Online Transaction Processing (OLTP) applications and databases that contain heavily updated tables should avoid over-indexing. Кроме того, индексы должны быть узкими, то есть содержать минимальное количество столбцов.Additionally, indexes should be narrow, that is, with as few columns as possible.

    Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE, потому что при изменении данных в таблице все индексы должны быть соответствующим образом изменены.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

Вопросы работы с запросамиQuery Considerations

Перед тем как приступать к созданию некластеризованных индексов, необходимо разобраться с тем, каким образом будет организован доступ к данным.Before you create nonclustered indexes, you should understand how your data will be accessed. Рассмотрите возможность использования некластеризованных индексов для запросов, обладающих следующими атрибутами:Consider using a nonclustered index for queries that have the following attributes:

  • Используют предложения JOIN или GROUP BY.Use JOIN or GROUP BY clauses.

    Создавайте многомерные некластеризованные индексы для столбцов, участвующих в операциях соединения и группирования, а кластеризованный индекс для внешних ключевых столбцов.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • Запросы, не возвращающие больших результирующих наборов.Queries that do not return large result sets.

    Создайте отфильтрованные индексы для использования с запросами, возвращающими четко определенные подмножества строк из большой таблицы.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

    Совет

    Как правило, предложение WHERE в инструкции CREATE INDEX совпадает с предложением WHERE в запросе, для которого создается индекс.Typically the WHERE clause of the CREATE INDEX statement matches the WHERE clause of a query being covered.

  • Содержат столбцы, часто встречающиеся в условиях поиска запроса, таких как предложение WHERE, возвращающих точные соответствия.Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

    Совет

    Сравнивайте затраты и преимущества при добавлении новых индексов.Consider the cost versus benefit when adding new indexes. Часто бывает лучше изменить существующий индекс в соответствии с требованиями новых запросов.It may be preferable to consolidate additional query needs into an existing index. Например, попробуйте добавить один или два столбца на конечный уровень существующего индекса, если он может покрыть сразу несколько важных запросов, а не создавать отдельный индекс для каждого конкретного запроса.For example, consider adding one or two extra leaf level columns to an existing index, if it allows coverage of several critical queries, instead of having one exactly covering index per each critical query.

Вопросы работы со столбцамиColumn Considerations

Рассмотрите столбцы, обладающие одним или несколькими указанными ниже атрибутами:Consider columns that have one or more of these attributes:

  • Покрытие запроса.Cover the query.

    Производительность повышается в тех случаях, когда индекс содержит все столбцы запроса.Performance gains are achieved when the index contains all columns in the query. Оптимизатор запросов может обнаружить все значения столбцов внутри данного индекса; необходимость в обращении к таблицам или данным кластеризованных индексов отпадает, в результате чего снижается интенсивность дисковых операций ввода-вывода.The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Для расширения покрытия столбцов не следует создавать широкий ключ индекса. Используйте индекс с включенными столбцами.Use index with included columns to add covering columns instead of creating a wide index key.

    Если таблица имеет кластеризованный индекс, то столбец или столбцы, определенные в этом кластеризованном индексе, автоматически добавляются к концу каждого некластеризованного индекса таблицы.If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. Это может привести к возникновению покрытого запроса без указания столбцов кластеризованного индекса в определении некластеризованного индекса.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. Так, если для таблицы имеется кластеризованный индекс в столбце C, то некластеризованный индекс в столбцах B и A будет иметь в качестве ключевых значений столбцы B, Aи C.For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • Множество индивидуальных значений, таких как сочетание фамилии и имени, если кластеризованный индекс используется для других столбцов.Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    Если имеется очень мало индивидуальных значений, скажем, только 1 и 0, то при выполнении большинства запросов этот индекс не будет использоваться, так как эффективность просмотра таблиц, как правило, выше.If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. Для этого типа данных рекомендуется создать отфильтрованный индекс для определенного значения, которое содержится в небольшом количестве строк.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. Например, если большая часть значений равна 0, то оптимизатор запросов может использовать отфильтрованный индекс для строк данных, в которых содержится 1.For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

Использование включенных столбцов для расширения некластеризованных индексовUse Included Columns to Extend Nonclustered Indexes

Функциональность некластеризованных индексов можно расширить с помощью добавления неключевых столбцов к конечному уровню некластеризованного индекса.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. Добавление неключевых столбцов позволяет создавать некластеризованные индексы, покрывающие больше запросов.By including nonkey columns, you can create nonclustered indexes that cover more queries. Это обусловлено следующими преимуществами неключевых столбцов.This is because the nonkey columns have the following benefits:

  • Они могут содержать типы данных, не разрешенные для ключевых столбцов индекса.They can be data types not allowed as index key columns.

  • Они не учитываются компонентом Компонент Database EngineDatabase Engine при расчете числа ключевых столбцов индекса и размера ключа индекса.They are not considered by the Компонент Database EngineDatabase Engine when calculating the number of index key columns or index key size.

Индекс с включенными неключевыми столбцами может значительно повысить производительность запроса, когда все столбцы запроса включены в индекс как ключевые или неключевые.An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Производительность повышается благодаря тому, что оптимизатор запросов может найти все значения столбцов в этом индексе; при этом нет обращения к данным таблиц или кластеризованных индексов, что приводит к меньшему количеству дисковых операций ввода-вывода.Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

Примечание

Если индекс содержит все столбцы, ссылаемые в запросе, это называется покрытием запроса.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

В то время как ключевые столбцы сохраняются на всех уровнях индекса, неключевые столбцы сохраняются только на конечном уровне.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

Использование включенных столбцов для обхода ограничений по размеруUsing Included Columns to Avoid Size Limits

Можно включать неключевые столбцы в некластеризованный индекс, чтобы избежать превышения текущих ограничений на размер индекса (16 ключевых столбцов) и размер ключа индекса (900 байт).You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Компонент Компонент Database EngineDatabase Engine не учитывает неключевые столбцы при расчете количества ключевых столбцов индекса и размера ключа индекса.The Компонент Database EngineDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.
Например, нужно индексировать следующие столбцы в таблице Document :For example, assume that you want to index the following columns in the Document table:

  • Title nvarchar(50)
  • Revision nchar(5)
  • FileName nvarchar(400)

Поскольку для типов данных nchar и nvarchar необходимо 2 байта для каждого символа, индекс, содержащий эти три столбца, на 10 байт превысит ограничение на размер в 900 байт (455 * 2).Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). Использование предложения INCLUDE в инструкции CREATE INDEX позволит определить ключ индекса как (Title, Revision), а FileName определить как неключевой столбец.By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. Таким образом, размер ключа индекса составит 110 байт (55 * 2), при этом индекс будет по-прежнему содержать все нужные столбцы.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. Следующая инструкция создает такой индекс:The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Правила для индекса с включенными столбцамиIndex with Included Columns Guidelines

При проектировании некластеризованных индексов с включенными столбцами пользуйтесь следующими правилами.When you design nonclustered indexes with included columns consider the following guidelines:

  • Неключевые столбцы определяются предложением INCLUDE инструкции CREATE INDEX.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • Неключевые столбцы можно определять только для некластеризованных индексов по таблицам или индексированным представлениям.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • Допускаются данные всех типов, за исключением text, ntextи image.All data types are allowed except text, ntext, and image.

  • Вычисляемые столбцы, являющиеся детерминированными и точными или неточными, могут быть включенными столбцами.Computed columns that are deterministic and either precise or imprecise can be included columns. Дополнительные сведения см. в разделе Индексы вычисляемых столбцов.For more information, see Indexes on Computed Columns.

  • Как и ключевые столбцы, вычисляемые столбцы, полученные на основе типов данных image, ntextи text , могут быть неключевыми (включенными) столбцами, если тип данных этого вычисляемого столбца допустим в качестве неключевого столбца индекса.As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • Имена столбцов нельзя указывать ни в списке INCLUDE, ни в списке ключевых столбцов.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • Имена столбцов в списке INCLUDE нельзя повторять.Column names cannot be repeated in the INCLUDE list.

Требования к размеру столбцовColumn Size Guidelines
  • Должен быть определен как минимум один ключевой столбец.At least one key column must be defined. Максимальное количество неключевых столбцов равно 1023.The maximum number of nonkey columns is 1023 columns. Это на 1 меньше, чем максимальное количество столбцов таблицы.This is the maximum number of table columns minus 1.

  • Ключевые столбцы индекса, в отличие от неключевых, должны удовлетворять текущим ограничениям на максимальное количество столбцов (16) и общий размер ключа индекса (900 байт).Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • Общий размер всех неключевых столбцов ограничен только размером столбцов, указанных в предложении INCLUDE, например, столбцы varchar(max) могут иметь размер до 2 ГБ.The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

Правила изменения столбцаColumn Modification Guidelines

При изменении столбца таблицы, определенного как включенный столбец, действуют следующие ограничения.When you modify a table column that has been defined as an included column, the following restrictions apply:

  • Неключевые столбцы нельзя удалять из таблицы до удаления соответствующего индекса.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • Неключевые столбцы нельзя изменять, за исключением следующих операций:Nonkey columns cannot be changed, except to do the following:

    • изменение поведения столбца в отношении значения NULL с NOT NULL на NULL;Change the nullability of the column from NOT NULL to NULL.

    • увеличение длины столбцов типов varchar, nvarcharи varbinary .Increase the length of varchar, nvarchar, or varbinary columns.

      Примечание

      Эти ограничения на изменение столбца также применяются к ключевым столбцам индекса.These column modification restrictions also apply to index key columns.

Рекомендации по проектированиюDesign Recommendations

Переопределите некластеризованные индексы с большим размером ключа индекса, чтобы только столбцы, используемые для поиска и уточняющего запроса, были ключевыми.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Все остальные столбцы, покрывающие запрос, сделайте включенными неключевыми столбцами.Make all other columns that cover the query included nonkey columns. Таким образом, в наличии будут все столбцы, покрывающие запрос, но сам ключ индекса будет небольшим и эффективным.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

Например, нужно спроектировать индекс, покрывающий следующий запрос:For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

Для покрытия запроса необходимо включить в индекс все его столбцы.To cover the query, each column must be defined in the index. Хотя можно определить все столбцы как ключевые, размер ключа составит 334 байт.Although you could define all columns as key columns, the key size would be 334 bytes. Поскольку в качестве критерия поиска реально используется только столбец PostalCode , имеющий длину 30 байт, более эффективный индекс определит PostalCode в качестве ключевого столбца, а все остальные столбцы включит как неключевые.Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

Следующая инструкция создает индекс с включенными столбцами, покрывающий данный запрос.The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
Вопросы производительностиPerformance Considerations

Избегайте добавления неиспользуемых столбцов.Avoid adding unnecessary columns. Добавление слишком большого количества столбцов, ключевых или неключевых, может оказать следующее влияние на производительность.Adding too many index columns, key or nonkey, can have the following performance implications:

  • На странице будет помещаться меньше строк индекса.Fewer index rows will fit on a page. Это может привести к увеличению количества операций ввода-вывода и снизить эффективность кэша.This could create I/O increases and reduced cache efficiency.

  • Увеличение места на диске, требуемого для хранения индекса.More disk space will be required to store the index. В частности, добавление типов данных varchar(max) , nvarchar(max) , varbinary(max) или xml в качестве неключевых столбцов индекса может значительно повысить требования к месту на диске.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. Это обусловлено тем, что значения столбцов копируются на конечный уровень индекса.This is because the column values are copied into the index leaf level. Поэтому они находятся и в индексе, и в базовой таблице.Therefore, they reside in both the index and the base table.

  • Обслуживание индекса может увеличить время выполнения операций изменения, вставки, обновления и удаления в базовой таблице или индексированном представлении.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Необходимо определить, что важнее — повышение производительности запросов или производительность при изменении данных и дополнительные требования к месту на диске.You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

Правила по созданию уникальных индексовUnique Index Design Guidelines

Уникальный индекс гарантирует, что ключ индекса не будет содержать одинаковых значений, а значит, каждая строка в таблице будет уникальна.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Создание уникального индекса имеет смысл, только если данные сами по себе могут быть уникальными.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. Например, если требуется обеспечить уникальность значений в столбце NationalIDNumber из таблицы HumanResources.Employee , когда первичным ключом является EmployeeID, создайте ограничение UNIQUE для столбца NationalIDNumber .For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. Если пользователь попытается ввести в этот столбец одно и тоже значение для нескольких сотрудников, появится сообщение об ошибке, а дублирующие данные не будут сохранены.If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

В случае уникальных индексов по нескольким столбцам индекс гарантирует, что каждая комбинация значений в ключе индекса уникальна.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. Например, если уникальный индекс создан для комбинации столбцов LastName, FirstNameи MiddleName , то никакие две строки в таблице не могут образовывать одну и ту же комбинацию этих значений.For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

Как кластеризованные, так и некластеризованные индексы могут быть уникальными.Both clustered and nonclustered indexes can be unique. Учитывая, что данные в столбце уникальны, можно создать уникальный кластеризованный индекс и несколько уникальных некластеризованных индексов для одной и той же таблицы.Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

Уникальные индексы имеют следующие преимущества:The benefits of unique indexes include the following:

  • Гарантируется целостность данных в определенных столбцах.Data integrity of the defined columns is ensured.

  • Предоставляются дополнительные сведения, полезные оптимизатору запросов.Additional information helpful to the query optimizer is provided.

Создание ограничений PRIMARY KEY или UNIQUE автоматически создает уникальный индекс для заданных столбцов.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. Между созданием ограничения UNIQUE и созданием уникального индекса независимо от ограничения больших различий нет.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Проверка данных происходит подобным же образом, и оптимизатор запросов не делает различия между уникальным индексом, который создан ограничением, и индексом, созданным вручную.Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. Однако на столбцы, для которых важна целостность данных, следует накладывать ограничение UNIQUE или PRIMARY KEY.However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. Тогда цель создания индекса будет ясна.By doing this the objective of the index will be clear.

РекомендацииConsiderations

  • Уникальный индекс и ограничения UNIQUE и PRIMARY KEY не могут быть созданы, если дублирующиеся значения уже существуют.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • Если данные уникальны и если нужно и далее требовать этой уникальности, создание уникального индекса вместо неуникального для тех же сочетаний столбцов предоставит дополнительные сведения оптимизатору запросов, который может создать более эффективные планы выполнения.If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. В этом случае рекомендуется создание уникального индекса (предпочтительнее через ограничение UNIQUE).Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • Уникальный некластеризованный индекс может содержать любые неключевые столбцы.A unique nonclustered index can contain included nonkey columns. Дополнительные сведения о включенных столбцах см. в разделе Индекс с включенными столбцами.For more information, see Index with Included Columns.

Рекомендации по проектированию отфильтрованных индексовFiltered Index Design Guidelines

Отфильтрованный индекс — это оптимизированный некластеризованный индекс, особенно подходящий для запросов, осуществляющих выборку из хорошо определенного подмножества данных.A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. Он использует предикат фильтра для индексирования части строк в таблице.It uses a filter predicate to index a portion of rows in the table. Хорошо спроектированный отфильтрованный индекс позволяет повысить производительность запросов, снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Применимо к: с SQL Server 2008SQL Server 2008 до SQL Server 2019 (15.x)SQL Server 2019 (15.x).Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Отфильтрованные индексы могут предоставить следующие преимущества по сравнению с индексами, построенными на всей таблице.Filtered indexes can provide the following advantages over full-table indexes:

  • Улучшение производительности запроса и качества планаImproved query performance and plan quality

    Хорошо спроектированный отфильтрованный индекс повышает производительность запроса и качество плана выполнения, поскольку он меньше, чем полнотабличный некластеризованный индекс, и содержит отфильтрованную статистику.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Отфильтрованная статистика точнее полнотабличной статистики, так как содержит только строки отфильтрованного индекса.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Снижение расходов на обслуживание индексаReduced index maintenance costs

    Индекс обслуживается только в случае, если инструкции языка обработки данных (DML) затрагивают данные в индексе.An index is maintained only when data manipulation language (DML) statements affect the data in the index. Отфильтрованный индекс уменьшает расходы на обслуживание индекса по сравнению с полнотабличным некластеризованным индексом, поскольку он меньше и обслуживается только при изменении данных в индексе.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. Возможно наличие большого числа отфильтрованных индексов, особенно если они содержат редко изменяющиеся данные.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Аналогично, если отфильтрованный индекс содержит только часто изменяемые данные, меньший размер индекса уменьшает затраты на обновление статистики.Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

  • Снижение затрат на хранение индексаReduced index storage costs

    Создание отфильтрованного индекса может уменьшить место на диске для некластеризованных индексов, если нет необходимости в полнотабличном индексе.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Полнотабличный некластеризованный индекс можно заменить несколькими отфильтрованными индексами без значительного увеличения требований к хранилищу.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Отфильтрованные индексы полезны, когда столбцы содержат хорошо определенные подмножества данных, указанных в инструкциях SELECT.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Примеры:Examples are:

  • Разреженные столбцы, содержащие небольшое количество значений, отличных от NULL.Sparse columns that contain only a few non-NULL values.

  • Разнородные столбцы, содержащие категории данных.Heterogeneous columns that contain categories of data.

  • Столбцы, содержащие диапазоны значений, таких как количество долларов, время и даты.Columns that contain ranges of values such as dollar amounts, time, and dates.

  • Секции таблицы, определенные логикой простого сравнения для значений столбцов.Table partitions that are defined by simple comparison logic for column values.

Снижение затрат на обслуживание отфильтрованного индекса более заметно, когда количество строк в индексе является небольшим по сравнению с полнотабличным индексом.Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. Если отфильтрованный индекс включает большую часть строк в таблице, его обслуживание может быть более затратным по сравнению с полнотабличным индексом.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. В этом случае нужно использовать полнотабличный индекс вместо отфильтрованного.In this case, you should use a full-table index instead of a filtered index.

Отфильтрованные индексы определены в одной таблице и поддерживают только простые операторы сравнения.Filtered indexes are defined on one table and only support simple comparison operators. Если необходим критерий фильтра, который ссылается на множество таблиц или имеет сложную логику, нужно создать представление.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Вопросы проектированияDesign Considerations

Для разработки эффективных отфильтрованных индексов важно понимать, какие запросы приложения используются и как они связаны с подмножествами данных.In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. Некоторые примеры данных, имеющих точно определенные подмножества, являются столбцами, содержащими в основном значения NULL, столбцами с разнородными категориями и столбцами с различными диапазонами значений.Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. Далее приведены различные сценарии, в которых отфильтрованный индекс дает преимущества над полнотабличными индексами.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Совет

Определение некластеризованного индекса columnstore поддерживает использование отфильтрованных условий.The nonclustered columnstore index definition supports using a filtered condition. Чтобы свести к минимуму негативное влияние на производительность, вызванное добавлением некластеризованного индекса columnstore для таблицы OLTP, можно использовать отфильтрованное условие для создания некластеризованного индекса columnstore только для холодных данных операционной рабочей нагрузки.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

Отфильтрованные индексы для подмножеств данныхFiltered Indexes for subsets of data

Если столбец содержит только небольшое число удовлетворяющих запросу значений, можно создать отфильтрованный индекс на этом подмножестве значений.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. Например, если столбец содержит в основном значения NULL, а запрос выбирает только из значений, отличных от NULL, можно создать отфильтрованный индекс для строк данных, отличных от NULL.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. В результате индекс уменьшится и затраты на его обслуживание будут значительно меньше, чем для полнотабличного некластеризованного индекса на тех же ключевых столбцах.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

Например, база данных AdventureWorks2012 содержит таблицу Production.BillOfMaterials с 2679 строками.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. Столбец EndDate содержит только 199 строк, содержащих значения, отличные от NULL, и 2 480 строк, содержащих значение NULL.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. Следующий отфильтрованный индекс может перекрывать запросы, которые возвращают столбцы, указанные в индексе, и выбирают только строки, содержащие значения EndDate, отличные от NULL.The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

Отфильтрованный индекс FIBillOfMaterialsWithEndDate допустим для следующего запроса.The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. Можно отобразить план выполнения запроса для проверки того, использует ли оптимизатор запросов отфильтрованный индекс.You can display the query execution plan to determine if the query optimizer used the filtered index.

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL   
    AND ComponentID = 5   
    AND StartDate > '20080101' ;  

Дополнительные сведения о создании отфильтрованного индекса и об определении выражения предиката отфильтрованного индекса см. в разделе Create Filtered Indexes.For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

Отфильтрованные индексы для разнородных данныхFiltered Indexes for heterogeneous data

Если таблица содержит строки с разнородными данными, можно создать отфильтрованный индекс для одной или более категорий данных.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

Например, продукты, содержащиеся в таблице Production.Product , связаны с идентификатором ProductSubcategoryID, который в свою очередь связан с категориями продуктов, такими как велосипеды, запчасти, одежда или аксессуары.For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. Эти категории являются разнородными, так как значения их столбцов в таблице Production.Product не являются близко коррелирующими.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. Например, столбцы Color, ReorderPoint, ListPrice, Weight, Classи Style имеют уникальные характеристики для каждой категории продукта.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Предположим, что существуют частые запросы на аксессуары, имеющие подкатегории с 27 по 36 включительно.Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. Можно повысить результативность запросов на аксессуары, создав отфильтрованный индекс по подкатегориям аксессуаров, как показано в следующем примере.You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;  

Отфильтрованный индекс FIProductAccessories включает следующий запрос, поскольку результатыThe filtered index FIProductAccessories covers the following query because the query

запроса содержатся в индексе, а план запроса не включает в себя поиск в базовых таблицах.results are contained in the index and the query plan does not include a base table lookup. Например, выражение предиката запроса ProductSubcategoryID = 33 — это подмножество предиката отфильтрованного индекса ProductSubcategoryID >= 27 и ProductSubcategoryID <= 36, а столбцы ProductSubcategoryID и ListPrice в предикате запроса являются ключевыми столбцами в индексе. Имя сохраняется на конечном уровне индекса в качестве включенного столбца.For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

SELECT Name, ProductSubcategoryID, ListPrice  
FROM Production.Product  
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;  

Ключевые столбцыKey Columns

Это лучший способ включения небольшого количества ключевых или включенных столбцов в определение отфильтрованного индекса и включения только столбцов, необходимых для оптимизатора запросов при выборе отфильтрованного индекса для плана выполнения запроса.It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. Оптимизатор запросов может выбрать отфильтрованный индекс независимо от того, перекрывает он запрос или нет.The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. Однако оптимизатор запросов с большей вероятностью выберет отфильтрованный индекс, если он перекрывает запрос.However, the query optimizer is more likely to choose a filtered index if it covers the query.

В некоторых случаях отфильтрованный индекс перекрывает запрос, не включая в определение отфильтрованного индекса в качестве ключевых или включенных столбцов столбцы из выражения отфильтрованного индекса.In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. Следующие правила содержат описание того, должен ли быть столбец в выражении отфильтрованного индекса ключевым или включенным столбцом в определении отфильтрованного индекса.The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. В примерах используется ранее созданный отфильтрованный индекс FIBillOfMaterialsWithEndDate .The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

Столбец в выражении отфильтрованного индекса не обязательно должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если выражение отфильтрованного индекса эквивалентно предикату запроса, а запрос не возвращает столбец с результатами запроса в выражение отфильтрованного индекса.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. Например, значение FIBillOfMaterialsWithEndDate охватывает следующий запрос, поскольку предикат запроса эквивалентен критерию фильтра, а возврат EndDate с результатами запроса не происходит.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate не требуется EndDate в качестве ключа или включенного столбца в определении отфильтрованного индекса.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;   

Столбец в выражении отфильтрованного индекса должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если предикат запроса использует в сравнении столбец, который не эквивалентен выражению отфильтрованного индекса.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression. Например, отфильтрованный индекс FIBillOfMaterialsWithEndDate допустим для следующего запроса, поскольку этот запрос выбирает подмножество строк из отфильтрованного индекса.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. Однако он не перекрывает следующий запрос, поскольку столбец EndDate используется в сравнении EndDate > '20040101', не эквивалентном выражению отфильтрованного индекса.However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. Обработчик запросов не может выполнить запрос без поиска значений EndDate.The query processor cannot execute this query without looking up the values of EndDate. Поэтому в определении отфильтрованного индекса EndDate должен быть ключевым или включенным столбцом.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate > '20040101';   

Столбец в выражении отфильтрованного индекса должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если этот столбец содержится в результирующем наборе запроса.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set. Например, отфильтрованный индекс FIBillOfMaterialsWithEndDate не перекрывает следующий запрос, поскольку тот возвращает столбец EndDate в результате запроса.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Поэтому в определении отфильтрованного индекса EndDate должен быть ключевым или включенным столбцом.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;  

Ключ кластеризованного индекса таблицы необязательно должен быть ключевым или включенным столбцом в определении отфильтрованного индекса.The clustered index key of the table does not need to be a key or included column in the filtered index definition. Ключ кластеризованного индекса автоматически включается во все некластеризованные индексы, в том числе отфильтрованные индексы.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

Операторы преобразования данных в предикате фильтраData Conversion Operators in the Filter Predicate

Если оператор сравнения определен в выражении отфильтрованного индекса результатов отфильтрованного индекса в неявном или явном преобразовании данных, произойдет ошибка, если преобразование выполняется в левой части оператора сравнения.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. Решением является применение выражения отфильтрованного индекса с оператором преобразования данных (CAST или CONVERT) в правой части оператора сравнения.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

В следующем примере создается таблица с различными типами данных.The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

В следующем определении отфильтрованного индекса столбец b неявно преобразуется в тип данных integer для сравнения с константой 1.In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. Это вызывает сообщение об ошибке 10611, поскольку преобразование выполняется в левой части оператора в отфильтрованном предикате.This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

Решением является преобразование константы в правой части к типу столбца b, как показано в следующем примере.The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = CONVERT(Varbinary(4), 1);  

Перемещение преобразования данных из левой части оператора сравнения в правую может изменить значение преобразования.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. В приведенном выше примере при добавлении оператора CONVERT в правую часть сравнение изменяется со сравнения целочисленного типа на сравнение типа varbinary .In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

Рекомендации по проектированию индексов columnstoreColumnstore Index Design Guidelines

columnstore index — это технология хранения, получения данных и управления ими с помощью формата хранения данных в один столбец, называемого columnstore.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Дополнительные сведения см. в статье Общие сведения об индексах columnstore.For more information, refer to Columnstore Indexes overview.

Сведения о версиях см. в разделе Новые возможности индексов columnstore.For version information, see Columnstore indexes - What's new.

Архитектура индексов columnstoreColumnstore Index Architecture

Ознакомившись с базовыми принципами, вы сможете приступить к изучению других руководств по индексам columnstore, в которых рассматривается их эффективное использование.Knowing these basics will make it easier to understand other columnstore articles that explain how to use them effectively.

Данные хранятся в форматах columnstore и rowstoreData storage uses columnstore and rowstore compression

При обсуждении индексов columnstore для обозначения формата хранения данных используются термины rowstore и columnstore.When discussing columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. Индексы columnstore используют оба типа хранилища.Columnstore indexes use both types of storage.

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

  • columnstore — это данные, логически организованные в виде таблицы, состоящей из строк и столбцов, и физически хранящиеся как столбцыA columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

    Индекс columnstore физически сохраняет большинство данных в формате columnstore.A columnstore index physically stores most of the data in columnstore format. В этом формате данные представлены столбцами, которые можно сжимать и распаковывать.In columnstore format, the data is compressed and uncompressed as columns. Не нужно распаковывать в каждой строке значения, не соответствующие запросам.There is no need to uncompress other values in each row that are not requested by the query. Благодаря этому можно быстро просматривать целые столбцы большой таблицы.This makes it fast to scan an entire column of a large table.

  • rowstore — это данные, логически организованные в виде таблицы, состоящей из строк и столбцов, и физически хранящиеся как строки. Это стандартный способ хранения реляционных данных таблицA rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. Это стандартный способ хранения реляционных данных таблиц, например индекса кучи или кластеризованного индекса сбалансированного дерева.This has been the traditional way to store relational table data such as a heap or clustered B-tree index.

    Индекс columnstore также физически сохраняет некоторые строки в формате rowstore, который называется deltastore.A columnstore index also physically stores some rows in a rowstore format called a deltastore. deltastore также называют разностными группами строк. Это место хранения строк, которых слишком мало для сжатия в columnstore.The deltastore,also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Каждая разностная группа строк реализована в виде кластеризованного индекса сбалансированного дерева.Each delta rowgroup is implemented as a clustered B-tree index.

  • deltastore — это место хранения строк, которых слишком мало для сжатия в columnstore.The deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. В deltastore таблица хранится в формате rowstore.The deltastore stores the rows in rowstore format.

См. сведения в статье Общие сведения об индексах columnstore.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Операции выполняются в сегментах групп строк и столбцовOperations are performed on rowgroups and column segments

Индексы columnstore группируют строки в управляемые элементы.The columnstore index groups rows into manageable units. Каждый из этих элементов называется группой строк.Each of these units is called a rowgroup. Для лучшей производительности число строк в группе строк должно быть достаточно большим, чтобы повысить скорость сжатия, и достаточно малым для использования преимуществ операций в памяти.For best performance, the number of rows in a rowgroup is large enough to improve compression rates and small enough to benefit from in-memory operations.

В группах строк индекс columnstore может выполнять следующие операции:For example, the columnstore index performs these operations on rowgroups:

  • сжимает группы строк в columnstoreCompresses rowgroups into the columnstore. (выполняется в каждом сегменте столбца в группе строк);Compression is performed on each column segment within a rowgroup.
  • объединяет группы строк во время операции ALTER INDEX ... REORGANIZE с очисткой удаленных данных.Merges rowgroups during an ALTER INDEX ... REORGANIZE operation, including removing deleted data.
  • создает новые группы строк во время операции ALTER INDEX ... REBUILD;Creates new rowgroups during an ALTER INDEX ... REBUILD operation.
  • отправляет отчеты об исправности и фрагментации групп строк в динамических административных представлениях.Reports on rowgroup health and fragmentation in the dynamic management views (DMVs).

deltastore состоит из одной или нескольких групп строк, которые называются разностными группами строк.The deltastore is comprised of one or more rowgroups called delta rowgroups. Каждая разностная группа строк — это кластеризованный индекс сбалансированного дерева, в котором хранятся небольшие массовые загрузки и вставки, пока группа строк не будет содержать 1 048 576 строк. При этом процесс, который называется задачей переноса кортежей, автоматически сжимает закрытую группу строк в columnstore.Each delta rowgroup is a clustered B-tree index that stores small bulk loads and inserts until the rowgroup contains 1,048,576 rows, at which time a process called the tuple-mover automatically compresses the closed rowgroup into the columnstore.

См. сведения о состояниях групп строк в статье sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).For more information about rowgroup statuses, see sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Совет

Слишком большое количество небольших групп строк ухудшает качество индекса columnstore.Having too many small rowgroups decreases the columnstore index quality. Операция реорганизации объединяет небольшие группы строк в соответствии с внутренней политикой пороговых значений, которая определяет способ очистки удаленных строк и объединения сжатых групп строк.A reorganize operation will merge smaller rowgroups, following an internal threshold policy that determines how to remove deleted rows and combine the compressed rowgroups. После объединения качество индекса должно повыситься.After a merge, the index quality should be improved.

Примечание

Начиная с SQL Server 2019 (15.x)SQL Server 2019 (15.x), задача переноса кортежей выполняется вместе с задачей фонового объединения. Последняя автоматически сжимает небольшие разностные группы строк с состоянием OPEN, которые существовали некоторое время в соответствии с внутренним пороговым значением, или объединяет группы строк с состоянием COMPRESSED, из которых было удалено большое количество строк.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the tuple-mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted.

Каждый столбец содержит несколько собственных значений в каждой группе строк.Each column has some of its values in each rowgroup. Эти значения называются сегментами столбцов.These values are called column segments. Каждая rowgroup содержит один сегмент столбца для каждого столбца в таблице.Each rowgroup contains one column segment for every column in the table. Каждый столбец содержит один сегмент столбца в каждой группе строк.Each column has one column segment in each rowgroup.

Сегмент столбцаColumn segment

Когда индекс columnstore сжимает группу строк, он отдельно сжимает каждый сегмент столбца.When the columnstore index compresses a rowgroup, it compresses each column segment separately. Чтобы распаковать целый столбец, индексу columnstore необходимо распаковать только один сегмент столбца из каждой группы строк.To uncompress an entire column, the columnstore index only needs to uncompress one column segment from each rowgroup.

См. сведения в статье Общие сведения об индексах columnstore.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Небольшие загрузки и вставки переносятся в deltastoreSmall loads and inserts go to the deltastore

Индекс columnstore улучшает сжатие и производительность columnstore за счет сжатия как минимум 102 400 строк в индекс columnstore за раз.A columnstore index improves columnstore compression and performance by compressing at least 102,400 rows at a time into the columnstore index. Чтобы выполнить массовое сжатие строк, индекс columnstore накапливает небольшие загрузки и вставки в deltastore.To compress rows in bulk, the columnstore index accumulates small loads and inserts in the deltastore. Операции deltastore обрабатываются в фоновом режиме.The deltastore operations are handled behind the scenes. Для получения правильных результатов запросов кластеризованные индексы columnstore объединяют результаты запроса от columnstore и deltastore.To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

Переход строк в deltastore происходит в следующих случаях:Rows go to the deltastore when they are:

  • если они вставляются с помощью инструкции INSERT INTO ... VALUES;Inserted with the INSERT INTO ... VALUES statement.
  • если по завершении массовой загрузки они насчитывают меньше 102 400 строк;At the end of a bulk load and they number less than 102,400.
  • в случае обновленияUpdated. (каждое обновление реализуется как операция удаления или вставки).Each update is implemented as a delete and an insert.

В deltastore также хранится список идентификаторов для удаленных строк, которые были помечены как удаленные, но еще не удалены физически из columnstore.The deltastore also stores a list of IDs for deleted rows that have been marked as deleted but not yet physically deleted from the columnstore.

См. сведения в статье Общие сведения об индексах columnstore.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Когда разностные группы строк заполнены, они сжимаются в columnstoreWhen delta rowgroups are full they get compressed into the columnstore

Прежде чем сжать группу строк в columnstore, кластеризованные индексы собирают 1 048 576 строк в каждой разностной группе строк.Clustered columnstore indexes collect up to 1,048,576 rows in each delta rowgroup before compressing the rowgroup into the columnstore. Это повышает степень сжатия индекса columnstore.This improves the compression of the columnstore index. Когда количество строк в разностной группе строк достигает максимального значения, состояние группы меняется с OPEN на CLOSED.When a delta rowgroup reaches the maximum number of rows, it transitions from an OPEN to CLOSED state. Фоновый процесс, который называется задачей переноса кортежей, проверяет наличие закрытых групп строк.A background process named the tuple-mover checks for closed row groups. При обнаружении закрытой группы строк она сжимается и сохраняется в columnstore.If the process finds a closed rowgroup, it compresses the rowgroup and stores it into the columnstore.

При сжатии разностной группы строк существующая разностная группа строк переходит в состояние TOMBSTONE. После исчезновения ссылок на нее она будет удалена задачей переноса кортежей, а новая сжатая группа строк будет помечена как COMPRESSED.When a delta rowgroup has been compressed, the existing delta rowgroup transitions into TOMBSTONE state to be removed later by the tuple-mover when there is no reference to it, and the new compressed rowgroup is marked as COMPRESSED.

См. сведения о состояниях групп строк в статье sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).For more information about rowgroup statuses, see sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Чтобы перестроить или реорганизовать индекс, вы можете принудительно сжать разностную группу строк в columnstore с помощью ALTER INDEX.You can force delta rowgroups into the columnstore by using ALTER INDEX to rebuild or reorganize the index. Обратите внимание, что если во время сжатия наблюдается нехватка памяти, индекс columnstore может уменьшить число строк в сжимаемой группе строк.Note that if there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup.

См. сведения в статье Общие сведения об индексах columnstore.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Каждая секция таблицы содержит собственные группы строк и разностные группыEach table partition has its own rowgroups and delta rowgroups

Понятие секционирования одинаково для двух кластеризованных индексов — индекса кучи и columnstore.The concept of partitioning is the same in both a clustered index, a heap, and a columnstore index. При секционировании таблица разделяется на небольшие группы строк в соответствии с диапазоном значений столбцов.Partitioning a table divides the table into smaller groups of rows according to a range of column values. Это часто используется для управления данными.It is often used for managing the data. Например, вы можете создать секцию для каждого года данных, а затем использовать переключение секций, чтобы архивировать данные для получения более экономичного хранилища.For example, you could create a partition for each year of data, and then use partition switching to archive data to less expensive storage. Переключение секций выполняется для индексов columnstore и упрощает перемещение секции данных в другое расположение.Partition switching works on columnstore indexes and makes it easy to move a partition of data to another location.

Группы строк всегда определяются в пределах секции таблицы.Rowgroups are always defined within a table partition. При секционировании индекса columnstore каждая секция получает свои собственные сжатые группы строк и разностные группы строк.When a columnstore index is partitioned, each partition has its own compressed rowgroups and delta rowgroups.

Совет

Если вам нужно удалить данные из columnstore, попробуйте использовать секционирование таблиц.Consider using table partitioning if there's a need to remove data from the columnstore. Отключение и усечение секций, которые больше не нужны, является эффективной стратегией удаления данных без фрагментации, которая возникает при использовании небольших групп строк.Switching out and truncating partitions that are not needed anymore is an efficient strategy to delete data without generating fragmentation introduced by having smaller rowgroups.

Каждая секция может содержать несколько разностных групп строкEach partition can have multiple delta rowgroups

Каждая секция может содержать несколько разностных групп строк.Each partition can have more than one delta rowgroups. Когда индексу columnstore требуется добавить данные в разностную группу строк, которая заблокирована, он попытается получить блокировку в другой разностной группе строк.When the columnstore index needs to add data to a delta rowgroup and the delta rowgroup is locked, the columnstore index will try to obtain a lock on a different delta rowgroup. Если доступные разностные группы строк отсутствуют, индекс columnstore создаст новую группу.If there are no delta rowgroups available, the columnstore index will create a new delta rowgroup. Например, у таблицы с 10 секциями может быть 20 и более разностных групп строк.For example, a table with 10 partitions could easily have 20 or more delta rowgroups.

Вы можете объединить индексы columnstore и rowstore в одной таблицеYou can combine columnstore and rowstore indexes on the same table

Некластеризованный индекс содержит копию всех или части строк и столбцов в базовой таблице.A nonclustered index contains a copy of part or all of the rows and columns in the underlying table. Индекс определяется как один или несколько столбцов таблицы и включает дополнительное условие для фильтрации строк.The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), можно создавать обновляемый некластеризованный индекс columnstore в таблице rowstore.Starting with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. Индекс columnstore сохраняет копию данных, так что вам обязательно потребуется дополнительное хранилище.The columnstore index stores a copy of the data so you do need extra storage. Тем не менее данные в индексе columnstore будут сжаты до меньшего размера, чем это требуется для таблицы rowstore.However, the data in the columnstore index will compress to a smaller size than the rowstore table requires. Таким образом, вы сможете выполнять аналитику на основе индекса columnstore и транзакции на основе индекса rowstore одновременно.By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. Хранилище столбцов обновляется при каждом изменении данных в таблице rowstore, поэтому оба индекса работают с одними и теми же данными.The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), индекс columnstore может включать один или несколько некластеризованных индексов rowstore.Starting with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index. Это обеспечивает эффективность поиска по таблицам на основе базового индекса columnstore.By doing this, you can perform efficient table seeks on the underlying columnstore. Кроме того, появляется доступ к другим возможностям.Other options become available too. Например, можно принудительно задать ограничение PRIMARY KEY, применив к таблице rowstore ограничение UNIQUE.For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. Поскольку неуникальное значение в таблицу rowstore не вставляется, SQL Server не может вставить значение в columnstore.Since an non-unique value will fail to insert into the rowstore table, SQL Server cannot insert the value into the columnstore.

Вопросы производительностиPerformance considerations

  • Определение некластеризованного индекса columnstore поддерживает использование отфильтрованных условий.The nonclustered columnstore index definition supports using a filtered condition. Чтобы свести к минимуму негативное влияние на производительность, вызванное добавлением некластеризованного индекса columnstore для таблицы OLTP, можно использовать отфильтрованное условие для создания некластеризованного индекса columnstore только для холодных данных операционной рабочей нагрузки.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

  • Таблица в памяти может включать один индекс columnstore.An in-memory table can have one columnstore index. Его можно создать при создании таблицы или добавить позже с помощью процедуры ALTER TABLE (Transact-SQL).You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). До версии SQL Server 2016 (13.x);SQL Server 2016 (13.x) создание индекса columnstore допускалось только в таблицах на дисках.Before SQL Server 2016 (13.x);SQL Server 2016 (13.x), only a disk-based table could have a columnstore index.

Дополнительные сведения см. в статье Производительность запросов по индексам columnstore.For more information, refer to Columnstore indexes - Query performance.

Руководство по проектированиюDesign Guidance

  • Таблица rowstore может включать один обновляемый некластеризованный индекс columnstore.A rowstore table can have one updateable nonclustered columnstore index. До версии SQL Server 2014 (12.x)SQL Server 2014 (12.x) некластеризованный индекс columnstore был доступен только для чтения.Before SQL Server 2014 (12.x)SQL Server 2014 (12.x), the nonclustered columnstore index was read-only.

Дополнительные сведения см. в статье Руководство по проектированию индексов columnstore.For more information, refer to Columnstore indexes - Design Guidance.

Рекомендации по проектированию хэш-индексовHash Index Design Guidelines

В каждой таблице, оптимизированной для памяти, должен использоваться как минимум один индекс, так как индексы объединяют строки.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. В таблице, оптимизированной для памяти, каждый индекс также будет оптимизирован для памяти.On a memory-optimized table, every index is also memory-optimized. Хэш-индексы являются одним из возможных типов индексов в таблице, оптимизированной для памяти.Hash indexes are one of the possible index types in a memory-optimized table. Дополнительные сведения см. в статье Индексы для оптимизированных для памяти таблиц.For more information, see Indexes for Memory-Optimized Tables.

Применимо к: с SQL Server 2014 (12.x)SQL Server 2014 (12.x) до SQL Server 2019 (15.x)SQL Server 2019 (15.x).Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Архитектура хэш-индексаHash Index Architecture

Хэш-индекс состоит из массива указателей. Каждый элемент массива называется хэш-контейнером.A hash index consists of an array of pointers, and each element of the array is called a hash bucket.

  • Каждый контейнер имеет размер 8 байт, в которых хранится адрес списка ссылок на записи ключей.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • Каждая запись представляет собой значение ключа индекса, к которому добавляется адрес соответствующей строки в оптимизированной для памяти таблице.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
  • Каждая запись указывает на следующую запись в списке ссылок на записи, связанных с текущим контейнером.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

При определении индекса необходимо указать число контейнеров.The number of buckets must be specified at index definition time:

  • Чем меньше отношение количества контейнеров к количеству строк или уникальных значений, тем длиннее будет средний список ссылок на контейнеры.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • Короткие списки обладают большим быстродействием по сравнению с длинными.Short link lists perform faster than long link lists.
  • Максимальное число контейнеров в хэш-индексах составляет 1 073 741 824.The maximum number of buckets in hash indexes is 1,073,741,824.

Совет

Чтобы определить подходящий BUCKET_COUNT для ваших данных, см. раздел Настройка числа контейнеров хэш-индекса.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

Хэш-функция применяется к ключевым столбцам индекса, и результат функции определяет, к какому контейнеру относится ключ.The hash function is applied to the index key columns and the result of the function determines what bucket that key falls into. Каждый контейнер содержит указатель на строки, хэшированные значения ключей которых сопоставлены с этим контейнером.Each bucket has a pointer to rows whose hashed key values are mapped to that bucket.

Функция, используемая для хэширования индексов, имеет следующие характеристики.The hashing function used for hash indexes has the following characteristics:

  • SQL ServerSQL Server имеет одну хэш-функцию, используемую для всех хэш-индексов.has one hash function that is used for all hash indexes.
  • Хэш-функция является детерминированной.The hash function is deterministic. Одно значение входного ключа всегда связано с одним контейнером в хэш-индексе.The same input key value is always mapped to the same bucket in the hash index.
  • Несколько ключей индекса могут быть сопоставлены с одним и тем же хэш-контейнером индекса.Multiple index keys may be mapped to the same hash bucket.
  • Хэш-функция сбалансирована, а это означает, что распределение значений ключей индекса, связанных с хэш-контейнерами, соответствует распределению Пуассона или нормальному распределению, а не плоскому линейному распределению.The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson or bell curve distribution, not a flat linear distribution.
  • Распределение Пуассона не является равномерным.Poisson distribution is not an even distribution. Значения ключа индекса не распределяются в хэш-контейнерах равномерно.Index key values are not evenly distributed in the hash buckets.
  • Если два ключа индекса сопоставляются с одним хэш-контейнером, происходит хэш-конфликт.If two index keys are mapped to the same hash bucket, there is a hash collision. Большое число конфликтов хэша может оказывать негативное влияние на операции чтения.A large number of hash collisions can have a performance impact on read operations. Реалистичная цель — сделать так, чтобы 30 % контейнеров содержали два различных значения ключа.A realistic goal is for 30% of the buckets contain two different key values.

Взаимозависимость хэш-индекса и контейнеров иллюстрируется на следующем рисунке.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

Настройка числа контейнеров хэш-индексаConfiguring the hash index bucket count

Число контейнеров хэш-индекса указывается в момент создания индекса и может быть изменено с помощью синтаксиса ALTER TABLE...ALTER INDEX REBUILD.The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

В большинстве случаев идеальное число контейнеров должно находиться в диапазоне, в 1–2 раза превышающем число уникальных значений в ключе индекса.In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.
Не всегда можно спрогнозировать количество значений, которые содержит конкретный ключ индекса.You may not always be able to predict how many values a particular index key may have, or will have. Обычно производительность остается хорошей, если значение BUCKET_COUNT входит в диапазон значений, в 10 раз превышающих фактическое число ключевых значений, и, как правило, его лучше переоценить, чем недооценить.Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

Слишком малое количество контейнеров имеет следующие недостатки.Too few buckets has the following drawbacks:

  • Возникает больше конфликтов хэша из-за уникальных значений ключей.More hash collisions of distinct key values.
  • Каждое уникальное значение вынуждено использовать один и тот же контейнер с другим уникальным значением.Each distinct value is forced to share the same bucket with a different distinct value.
  • Средняя длина цепочки для контейнера возрастает.The average chain length per bucket grows.
  • Чем длиннее цепочка контейнеров, тем медленнее скорость выполнения проверки на равенство индексов.The longer the bucket chain, the slower the speed of equality lookups in the index.

Слишком большое количество контейнеров имеет следующие недостатки.Too many buckets has the following drawbacks:

  • Слишком большое число контейнеров может привести к увеличению количества пустых контейнеров.Too high a bucket count might result in more empty buckets.
  • Пустые контейнеры влияют на скорость полного сканирования индексов.Empty buckets impact the performance of full index scans. Если сканирования выполняются регулярно, можно выбрать число контейнеров, близкое к числу уникальных значений ключа индекса.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
  • Пустые контейнеры задействуют память, хотя каждый контейнер использует всего 8 байт.Empty buckets use memory, though each bucket uses only 8 bytes.

Примечание

При добавлении большего числа контейнеров цепочка записей, которые имеют повторяющееся значение, не уменьшается.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. Степень повторяемости значения используется для того, чтобы решить, правильный ли тип имеет индекс, а не для определения числа контейнеров.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

Вопросы производительностиPerformance considerations

Производительность хэш-индекса обладает следующими характеристиками.The performance of a hash index is:

  • Отличная, если предикат в предложении WHERE задает точное значение каждого столбца в ключе хэш-индекса.Excellent when the predicate in the WHERE clause specifies an exact value for each column in the hash index key. Хэш-индекс вернется к сканированию данного предиката неравенства.A hash index will revert to a scan given an inequality predicate.
  • Низкая, если в предикате в предложении WHERE указан диапазон значений ключа индекса.Poor when the predicate in the WHERE clause looks for a range of values in the index key.
  • Низкая, если в предикате в предложении WHERE указано одно конкретное значение для первого столбца ключа хэш-индекса, состоящего из двух столбцов, но не указано значение для других столбцов ключа.Poor when the predicate in the WHERE clause stipulates one specific value for the first column of a two column hash index key, but does not specify a value for other columns of the key.

Совет

Предикат должен содержать все столбцы в ключе хэш-индекса.The predicate must include all columns in the hash index key. Для хэш-индекса требуется ключ (для хэша) для поиска в индексе.The hash index requires a key (to hash) to seek into the index. Если ключ индекса состоит из двух столбцов, а в предложении WHERE предоставлен только первый столбец, у SQL ServerSQL Server не будет полного ключа для хэширования.If an index key consists of two columns and the WHERE clause only provides the first column, SQL ServerSQL Server does not have a complete key to hash. Это приведет к получению плана запроса сканирования индекса.This will result in an index scan query plan.

Если применяется хэш-индекс и число уникальных ключей индекса превышает число строк в сто раз или более, рекомендуем увеличить число контейнеров, чтобы избежать образования больших цепочек строк, либо воспользоваться вместо этого некластеризованным индексом.If a hash index is used and the number of unique index keys is 100 times (or more) than the row count, consider either increasing to a larger bucket count to avoid large row chains, or use a nonclustered index instead.

Вопросы, связанные с обновлениемDeclaration considerations

Хэш-индекс может существовать только для таблицы, оптимизированной для памяти.A hash index can exist only on a memory-optimized table. Он не может существовать для таблицы на диске.It cannot exist on a disk-based table.

Хэш-индекс можно объявить как:A hash index can be declared as:

  • УНИКАЛЬНЫЙ или неуникальный по умолчанию.UNIQUE, or can default to Non-Unique.
  • НЕКЛАСТЕРИЗОВАННЫЙ, который используется по умолчанию.NONCLUSTERED, which is the default.

Ниже приведен пример синтаксиса для создания хэш-индекса за пределами инструкции CREATE TABLE.The following is an example of the syntax to create a hash index, outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
ADD INDEX ix_hash_Column2 UNIQUE  
HASH (Column2) WITH (BUCKET_COUNT = 64);

Версии строк и сборка мусораRow versions and garbage collection

При изменении строки с помощью инструкции UPDATE в таблице, оптимизированной для памяти, создается обновленная версия строки.In a memory-optimized table, when a row is affected by an UPDATE, the table creates an updated version of the row. Во время транзакции обновления другие сеансы могут считать старую версию строки и тем самым избежать снижения производительности, связанной с блокировкой строки.During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

Кроме того, хэш-индекс может иметь различные версии записей для отражения обновления.The hash index might also have different versions of its entries to accommodate the update.

Позже, когда старые версии больше не требуются, поток сборки мусора перебирает список контейнеров и их списки ссылок, удаляя старые записи.Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. Поток сборки мусора работает быстрее, если списки ссылок короткие.The GC thread performs better if the link list chain lengths are short. Дополнительные сведения см. в статье Сборка мусора модулем In-Memory OLTP.For more information, refer to In-Memory OLTP Garbage Collection.

Рекомендации по проектированию некластеризованных индексов, оптимизированных для памятиMemory-Optimized Nonclustered Index Design Guidelines

Некластеризованные индексы являются одним из возможных типов индексов в таблице, оптимизированной для памяти.Nonclustered indexes are one of the possible index types in a memory-optimized table. Дополнительные сведения см. в статье Индексы для оптимизированных для памяти таблиц.For more information, see Indexes for Memory-Optimized Tables.

Применимо к: с SQL Server 2014 (12.x)SQL Server 2014 (12.x) до SQL Server 2019 (15.x)SQL Server 2019 (15.x).Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Архитектура некластеризованного индекса в памятиIn-memory Nonclustered Index Architecture

Некластеризованные индексы в памяти реализуются с помощью структуры данных, которая называется BW-деревом. Она была изобретена и описана подразделением Microsoft Research в 2011 году.In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree, originally envisioned and described by Microsoft Research in 2011. BW-дерево является разновидностью сбалансированного дерева без блокировок и кратковременных блокировок.A Bw-Tree is a lock and latch-free variation of a B-Tree. См. дополнительные сведения о сбалансированном дереве для новых аппаратных платформ.For more details please see The Bw-Tree: A B-tree for New Hardware Platforms.

В самой общей форме BW-дерево состоит из карты страниц, упорядоченных по идентификаторам (PidMap), средства для выделения и повторного использования идентификаторов страниц (PidAlloc) и набора страниц, связанных с картой страниц и друг с другом.At a very high level the Bw-Tree can be understood as a map of pages organized by page ID (PidMap), a facility to allocate and reuse page IDs (PidAlloc) and a set of pages linked in the page map and to each other. Эти три основных компонента образуют базовую внутреннюю структуру BW-дерева.These three high level sub-components make up the basic internal structure of a Bw-Tree.

Эта структура схожа со структурой обычного сбалансированного дерева в том плане, что каждая страница имеет набор упорядоченных ключевых значений и в индексе есть уровни, каждый из которых указывает на нижележащий уровень, а конечные уровни указывают на строки данных.The structure is similar to a normal B-Tree in the sense that each page has a set of key values that are ordered and there are levels in the index each pointing to a lower level and the leaf levels point to a data row. Тем не менее есть несколько отличий.However there are several differences.

Так же как и в случае с хэш-индексами, несколько строк данных могут быть связаны друг с другом (версии).Just like hash indexes, multiple data rows can be linked together (versions). Указателями на страницы между уровнями являются идентификаторы логических страниц, которые представляют собой смещения в таблице сопоставления страниц, которая, в свою очередь, содержит физические адреса всех страниц.The page pointers between the levels are logical page IDs, which are offsets into a page mapping table, that in turn has the physical address for each page.

Изменение страниц индекса на месте не производится.There are no in-place updates of index pages. Поэтому были введены новые разностные страницы.New delta pages are introduced for this purpose.

  • Для изменения страницы блокировка или кратковременная блокировка не требуется.No latching or locking is required for page updates.
  • Страницы индекса не имеют фиксированного размера.Index pages are not a fixed size.

Значение ключа на каждой странице не конечного уровня — это максимальное значение, которое содержится на ее дочерней странице. Каждая строка также содержит идентификатор этой логической страницы.The key value in each non-leaf level page depicted is the highest value that the child that it points to contains and each row also contains that page logical page ID. Страница конечного уровня, помимо значения ключа, содержит физический адрес строки данных.On the leaf-level pages, along with the key value, it contains the physical address of the data row.

Поиск точек выполняется так же, как в сбалансированных деревьях. Единственным отличием является то, что, поскольку страницы связаны только в одном направлении, Компонент SQL Server Database EngineSQL Server Database Engine следует по правым указателям на страницы, где каждая конечная страница имеет максимальное значение своей дочерней страницы, а не минимальное, как в случае со сбалансированным деревом.Point lookups are similar to B-Trees except that because pages are linked in only one direction, the Компонент SQL Server Database EngineSQL Server Database Engine follows right page pointers, where each non-leaf pages has the highest value of its child, rather than lowest value as in a B-Tree.

Если необходимо изменить страницу конечного уровня, Компонент SQL Server Database EngineSQL Server Database Engine не изменяет саму страницу.If a Leaf-level page has to change, the Компонент SQL Server Database EngineSQL Server Database Engine does not modify the page itself. Вместо этого Компонент SQL Server Database EngineSQL Server Database Engine создает разностную запись с описанием изменения и добавляет ее к прежней странице.Rather, the Компонент SQL Server Database EngineSQL Server Database Engine creates a delta record that describes the change, and appends it to the previous page. Затем оно также меняет адрес этой прежней страницы в таблице сопоставления страниц на адрес разностной записи, который теперь становится физическим адресом страницы.Then it also updates the page map table address for that previous page, to the address of the delta record which now becomes the physical address for this page.

Для управления структурой BW-дерева могут требоваться три разных операции: консолидация, разделение и слияние.There are three different operations that can be required for managing the structure of a Bw-Tree: consolidation, split and merge.

Консолидация разностных записейDelta Consolidation

Длинная цепочка разностных записей может постепенно привести к снижению производительности поиска, так как при поиске по индексу приходится проходить по всей цепочке.A long chain of delta records can eventually degrade search performance as it could mean we are traversing long chains when searching through an index. Если в цепочку, уже содержащую 16 элементов, добавляется новая разностная запись, изменения в разностных записях будут консолидированы в соответствующую страницу индекса, и эта страница затем будет перестроена, включая изменения, указанные в новой разностной записи, которая инициировала консолидацию.If a new delta record is added to a chain that already has 16 elements, the changes in the delta records will be consolidated into the referenced index page, and the page will then be rebuilt, including the changes indicated by the new delta record that triggered the consolidation. Перестроенная страница будет иметь тот же идентификатор страницы, но новый адрес в памяти.The newly rebuilt page will have the same page ID but a new memory address.

hekaton_tables_23ehekaton_tables_23e

Разделение страницыSplit page

Страница индекса в BW-дереве растет по мере необходимости, начиная от одной строки и заканчивая максимальным размером, равным 8 КБ.An index page in Bw-Tree grows on as-needed basis starting from storing a single row to storing a maximum of 8 KB. Если размер страницы индекса достиг 8 КБ, вставка еще одной строки приведет к разделению страницы.Once the index page grows to 8 KB, a new insert of a single row will cause the index page to split. Для внутренней страницы это означает, что больше нет места для добавления еще одного значения ключа и указателя, а для конечной страницы — что после включения всех разностных записей строка не будет помещаться на странице.For an internal page, this means when there is no more room to add another key value and pointer, and for a leaf page, it means that the row would be too big to fit on the page once all the delta records are incorporated. С помощью статистических данных в заголовке конечной страницы отслеживается необходимое место для консолидации разностных записей, и эти данные корректируются после добавления каждой новой разностной записи.The statistics information in the page header for a leaf page keeps track of how much space would be required to consolidate the delta records, and that information is adjusted as each new delta record is added.

Операция разделения производится в два отдельных этапа.A Split operation is done in two atomic steps. На приведенном ниже рисунке предполагается, что конечная страница инициирует разделение, так как вставляется ключ со значением 5 и существует неконечная страница, указывающая на конец текущей страницы конечного уровня (значение ключа 4).In the picture below, assume a Leaf-page forces a split because a key with value 5 is being inserted, and a non-leaf page exists pointing to the end of the current Leaf-level page (key value 4).

hekaton_tables_23fhekaton_tables_23f

Шаг 1. Выделяются две новые страницы (P1 и P2), и строки со старой страницы P1, включая вставляемую строку, разделяются между этими новыми страницами.Step 1: Allocate two new pages P1 and P2, and split the rows from old P1 page onto these new pages, including the newly inserted row. Для хранения физического адреса страницы P2 используется новый слот в таблице сопоставления страниц.A new slot in Page Mapping Table is used to store the physical address of page P2. Страницы P1 и P2 пока не доступны для параллельных операций.These pages, P1 and P2 are not accessible to any concurrent operations yet. Кроме того, задается логический указатель со страницы P1 на P2.In addition, the logical pointer from P1 to P2 is set. Затем в рамках одного этапа указатель со старой страницы P1 в таблице сопоставления страниц меняется на указатель с новой страницы P1.Then, in one atomic step update the Page Mapping Table to change the pointer from old P1 to new P1.

Шаг 2. Неконечная страница указывает на P1, но прямого указателя с неконечной страницы на P2 нет.Step 2: The non-leaf page points to P1 but there is no direct pointer from a non-leaf page to P2. Страница P2 доступна только через P1.P2 is only reachable via P1. Чтобы создать указатель с неконечной страницы на P2, выделите новую неконечную страницу (внутреннюю страницу индекса), скопируйте все строки со старой неконечной страницы и добавьте новую строку, указывающую на P2.To create a pointer from a non-leaf page to P2, allocate a new non-leaf page (internal index page), copy all the rows from old non-leaf page, and add a new row to point to P2. После этого в рамках одного этапа указатель со старой неконечной страницы в таблице сопоставления страниц меняется на указатель с новой неконечной страницы.Once this is done, in one atomic step, update the Page Mapping Table to change the pointer from old non-leaf page to new non-leaf page.

Слияние страницMerge page

Если в результате операции DELETE размер страницы составляет меньше 10 % максимального размера (в настоящее время 8 КБ) или на странице остается одна строка, эта страница объединяется со смежной страницей.When a DELETE operation results in a page having less than 10% of the maximum page size (currently 8 KB), or with a single row on it, that page will be merged with a contiguous page.

При удалении строки со страницы добавляется разностная запись для операции удаления.When a row is deleted from a page, a delta record for the delete is added. Кроме того, проводится проверка того, подлежит ли страница индекса (неконечная) слиянию.Additionally, a check is made to determine if the index page (non-leaf page) qualifies for Merge. При этом проверяется, составляет ли оставшееся место после удаления строки менее 10 % от максимального размера страницы.This check verifies if the remaining space after deleting the row will be less than 10% of maximum page size. Если это условие выполняется, слияние производится в три отдельных этапа.If it does qualify, the Merge is performed in three atomic steps.

На приведенном ниже рисунке предполагается, что операция DELETE удаляет значение ключа 10.In the picture below, assume a DELETE operation will delete the key value 10.

hekaton_tables_23ghekaton_tables_23g

Шаг 1. Создается разностная страница, представляющая значение ключа 10 (синий треугольник), а указатель этого значения на неконечной странице Pp1 устанавливается на новую разностную страницу.Step 1: A delta page representing key value 10 (blue triangle) is created and its pointer in the non-leaf page Pp1 is set to the new delta page. Кроме того, создается специальная разностная страница слияния (зеленый треугольник), которая связывается с разностной страницей.Additionally a special merge-delta page (green triangle) is created, and it is linked to point to the delta page. На этом этапе обе страницы (разностная страница и разностная страница слияния) недоступны для параллельных транзакций.At this stage, both pages (delta page and merge-delta page) are not visible to any concurrent transaction. В рамках этого же этапа указатель на страницу конечного уровня P1 в таблице сопоставления страниц обновляется так, чтобы теперь он указывал на разностную страницу слияния.In one atomic step, the pointer to the Leaf-level page P1 in the Page Mapping Table is updated to point to the merge-delta page. После этого этапа запись значения ключа 10 на странице Pp1 будет указывать на разностную страницу слияния.After this step, the entry for key value 10 in Pp1 now points to the merge-delta page.

Шаг 2. Необходимо удалить строку, представляющую значение ключа 10 на неконечной странице Pp1, и обновить запись для значения ключа 10 так, чтобы она указывала на страницу P1.Step 2: The row representing key value 7 in the non-leaf page Pp1 needs to be removed, and the entry for key value 10 updated to point to P1. Для этого выделяется новая неконечная страница Pp2 и копируются все строки со страницы Pp1, кроме строки, представляющей значение ключа 7. Затем строка значения ключа 10 обновляется так, чтобы теперь она указывала на страницу P1.To do this, a new non-leaf page Pp2 is allocated and all the rows from Pp1 are copied except for the row representing key value 7; then the row for key value 10 is updated to point to page P1. После этого в рамках того же этапа запись, указывающая на Pp1, в таблице сопоставления страниц обновляется так, чтобы теперь она указывала на Pp2.Once this is done, in one atomic step, the Page Mapping Table entry pointing to Pp1 is updated to point to Pp2. Страница Pp1 больше не доступна.Pp1 is no longer reachable.

Шаг 3. Страницы конечного уровня P2 и P1 сливаются, а разностные страницы удаляются.Step 3: The Leaf-level pages P2 and P1 are merged and the delta pages removed. Для этого выделяется новая страница P3, сливаются строки со страниц P2 и P1, а изменения разностной страницы включаются в новую страницу P3.To do this, a new page P3 is allocated and the rows from P2 and P1 are merged, and the delta page changes are included in the new P3. После этого в рамках того же этапа запись, указывающая на P1, в таблице сопоставления страниц обновляется так, чтобы теперь она указывала на страницу P3.Then, in one atomic step, the Page Mapping Table entry pointing to page P1 is updated to point to page P3.

Вопросы производительностиPerformance considerations

При запросе оптимизированной для памяти таблицы с предикатами неравенства некластеризованные индексы работают лучше, чем некластеризованные хэш-индексы.The performance of a nonclustered index is better than nonclustered hash indexes when querying a memory-optimized table with inequality predicates.

Примечание

Столбец в таблице, оптимизированной для памяти, может быть частью хэш-индекса и некластеризованного индекса.A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

Совет

Если столбец в ключевых столбцах некластеризованного индекса содержит много повторяющихся значений, производительность при обновлении, вставке и удалении может снизиться.When a column in a nonclustered index key columns have many duplicate values, performance can degrade for updates, inserts, and deletes. Один из способов повышения производительности в этой ситуации — добавление еще одного столбца в некластеризованный индекс.One way to improve performance in this situation is to add another column to the nonclustered index.

Дополнительные материалыAdditional Reading

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)CREATE XML INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
Реорганизация и перестроение индексов Reorganize and Rebuild Indexes
Повышение производительности с помощью индексированных представлений SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views
Partitioned Tables and IndexesPartitioned Tables and Indexes
Создание первичного ключа Create a Primary Key
Индексы для оптимизированных для памяти таблицIndexes for Memory-Optimized Tables
Общие сведения об индексах columnstoreColumnstore Indexes overview
Устранение неполадок хэш-индексов для оптимизированных для памяти таблиц Troubleshooting Hash Indexes for Memory-Optimized Tables
Оптимизированные для памяти динамические административные представления таблиц (Transact-SQL) Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Динамические административные представления и функции, связанные с индексами (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
Индексы вычисляемых столбцов Indexes on Computed Columns
Индексы и инструкция ALTER TABLE Indexes and ALTER TABLE
Адаптивная дефрагментация индексовAdaptive Index Defrag