在内存优化表上使用索引的指导原则Guidelines for Using Indexes on Memory-Optimized Tables

索引用于高效访问 SQL ServerSQL Server 表中的数据。Indexes are used for efficiently accessing data in SQL ServerSQL Server tables. 指定正确索引可以显著提高查询性能。Specifying the right indexes can dramatically improve query performance. 例如,请考虑以下查询:Consider, for example, the query:

SELECT c1, c2 FROM t WHERE c1 = 1;  

如果 c1 列上没有索引,则 SQL ServerSQL Server 需要扫描整个表 t,然后筛选满足条件 c1=1 的行。If there is no index on column c1, SQL ServerSQL Server will need to scan the entire table t, and then filter on the rows that satisfy the condition c1=1. 但是,如果 t 在 c1 列上具有索引,则 SQL ServerSQL Server 可以直接查找值 1 并对行进行检索。However, if t has an index on column c1, SQL ServerSQL Server can seek directly on the value 1 and retrieve the rows.

针对表中一列或多列搜索具有特定值或值范围的记录时,SQL ServerSQL Server 可以使用这些列上的索引来快速找到对应记录。When searching for records that have a specific value, or range of values, for one or more columns in the table, SQL ServerSQL Server can use an index on those columns to quickly locate the corresponding records. 基于磁盘的表和内存优化表都会受益于索引。Both disk-based and memory-optimized tables benefit from indexes. 但是,在使用内存优化的表时,需要考虑索引结构之间的一些区别。There are, however, certain differences between index structures that need to be considered when using memory-optimized tables. (内存优化的表上的索引称为内存优化的索引。)其中一些主要的区别包括:(Indexes on memory-optimized tables are referred to as memory-optimized indexes.) Some of the key differences are:

  • 必须使用创建内存优化的索引CREATE TABLE (TRANSACT-SQL)Memory-optimized indexes must be created with CREATE TABLE (Transact-SQL). 基于磁盘上的索引可以使用 CREATE TABLECREATE INDEX 创建。Disk-based indexes can be created with CREATE TABLE and CREATE INDEX.

  • 内存优化索引仅存在于内存中。Memory-optimized indexes exist only in memory. 索引结构在磁盘中不持久化,并且不在事务日志中记录索引操作。Index structures are not persisted to disk and index operations are not logged in the transaction log. CREATE TABLE 过程中以及数据库启动过程中,在内存中创建内存优化的表时,将创建索引结构。The index structure is created when the memory-optimized table is created in memory, both during CREATE TABLE and during database startup.

  • 内存优化索引本质上是覆盖性的。Memory-optimized indexes are inherently covering. 覆盖表示真正在索引中包括所有列,因此内存优化的表不需要查找书签。Covering means that all columns are virtually included in the index and bookmark lookups are not needed for memory-optimized tables. 内存优化索引仅包含指向表数据结构中的实际行的内存指针,而不是对主键的引用。Rather than a reference to the primary key, memory-optimized indexes simply contain a memory pointer to the actual row in the table data structure.

  • 碎片和填充因子不适用于内存优化索引。Fragmentation and fillfactor do not apply to memory-optimized indexes. 在基于磁盘的索引中,碎片是指无序写入磁盘的 B 树中的页。In disk-based indexes, fragmentation refers to pages in the B-tree being written to disk out-of-order. 内存优化索引不会写入磁盘或从磁盘读取。Memory-optimized indexes are not written to or read from disk. 基于磁盘的 B 树索引中的填充因子是指使用实际数据填充物理页结构的程度。Fillfactor in disk-based B-tree indexes refers to the degree to which the physical page structures are filled with actual data. 内存优化索引结构没有固定大小的页。The memory-optimized index structures do not have fixed-size pages.

    内存优化的索引具有以下两种类型:There are two types of memory-optimized indexes:

  • 非聚集哈希索引,为点查找编制此类索引。Nonclustered hash indexes, which are made for point lookups. 有关哈希索引的详细信息,请参阅哈希索引For more information about hash indexes, see Hash Indexes.

  • 非聚集索引,为范围扫描和有序扫描编制此类索引。Nonclustered indexes, which are made for range scans and ordered scans.

    借助于哈希索引,可通过内存中的哈希表来访问数据。With a hash index, data is accessed through an in-memory hash table. 哈希索引没有页,并且始终为固定大小。Hash indexes do not have pages and are always of a fixed size. 但是,哈希索引的哈希存储桶可为空,这样会浪费一些空间,但数量有限。However, a hash index can have empty hash buckets, which result in limited wasted space. 对于使用哈希索引从查询返回的值不进行排序。The values returned from a query using a hash index are not sorted. 哈希索引为针对相等谓词的索引查找进行优化,还支持完整索引扫描。Hash indexes are optimized for index seeks on equality predicates and also support full index scans.

    非聚集索引(而非哈希索引)支持哈希索引支持的所有功能,外加针对不等谓词(例如大于或小于)的查找操作以及排序顺序。Nonclustered indexes (not hash indexes) support everything that hash indexes supports plus seek operations on inequality predicates such as greater than or less than, as well as sort order. 可以根据索引创建时指定的顺序检索行。Rows can be retrieved according to the order specified with index creation. 如果索引的排序顺序匹配特定查询所需的排序顺序,例如,如果索引键匹配 ORDER BY 子句,则无需作为查询执行的一部分对行进行排序。If the sort order of the index matches the sort order required for a particular query, for example if the index key matches the ORDER BY clause, there is no need to sort the rows as part of query execution. 内存优化的非聚集索引是无方向的;它们不支持按与索引的排序顺序反向的排序顺序来检索行。Memory-optimized nonclustered indexes are unidirectional; they do not support retrieving rows in a sort order that is the reverse of the sort order of the index. 例如,对于被指定为 (c1 ASC) 的一个索引,不可能以反向顺序(如 (c1 DESC))浏览该索引。For example, for an index specified as (c1 ASC), it is not possible to scan the index in reverse order, as (c1 DESC).

    每个索引都会占用内存。Each index consumes memory. 哈希索引的内存用量固定不变,是存储桶数量的函数。Hash indexes consume a fixed amount of memory, which is a function of the bucket count. 对于非聚集索引,内存用量是行数和索引键列大小的函数,还有一些其他开销取决于工作负荷。For nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns, with some additional overhead depending on the workload. 内存优化的索引所用的内存不计入用于在内存优化的表中存储行的内存,并区别于此类内存。Memory for memory-optimized indexes is in addition to and separate from the memory used to store rows in memory-optimized tables.

    重复键值始终共享同一哈希桶。Duplicate key values always share the same hash bucket. 如果一个哈希索引包含了多个重复键值,那么产生的长哈希链会损坏性能。If a hash index contains many duplicate key values, the resulting long hash chains will harm performance. 发生在任何哈希索引中的哈希冲突将会进一步减弱此方案中的性能。Hash collisions, which occur in any hash index, will further reduce performance in this scenario. 出于此原因,如果唯一索引键的数目是至少小 100 倍行计数,则可以降低哈希冲突的风险通过让桶计数更大 (至少为八个次唯一索引键的数目; 请参阅确定哈希索引的正确 Bucket 计数有关详细信息) 或可以通过使用非聚集索引来完全消除哈希冲突。For that reason, if the number of unique index keys is at least 100 times smaller than the row count, you can reduce the risk of hash collisions by making the bucket count much larger (at least eight times the number of unique index keys; see Determining the Correct Bucket Count for Hash Indexes for more information) or you can eliminate hash collisions entirely by using a nonclustered index.

确定要用于内存优化表的索引Determining Which Indexes to Use for a Memory-Optimized Table

每个内存优化的表都必须具有至少一个索引。Each memory-optimized table must have at least one index. 请注意,每个 PRIMARY KEY 约束都会隐式创建一个索引。Note that each PRIMARY KEY constraint implicitly creates an index. 因此,如果表中包含主键,则该表具有索引。Therefore, if a table has a primary key, it has an index. 主键是对持久内存优化表的要求。A primary key is a requirement for a durable memory-optimized table.

查询内存优化的表时,在谓词子句仅包含相等谓词的情况下,哈希索引的性能更好。When querying a memory-optimized table, hash indexes perform better when the predicate clause contains only equality predicates. 谓词必须包括哈希索引键中的所有列。The predicate must include all columns in the hash index key. 如果有不等谓词,则哈希索引将恢复为扫描。A hash index will revert to a scan given an inequality predicate.

内存优化的表中的列可以同时为哈希索引和非聚集索引的一部分。A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

在用不等谓词查询内存优化的表时,非聚集索引的性能将高于非聚集哈希索引。When querying a memory-optimized table with inequality predicates, nonclustered indexes will perform better than nonclustered hash indexes.

哈希索引需要键(哈希)才能仔细查找索引。The hash index requires a key (to hash) to seek into the index. 如果索引键由两列组成,但仅提供第一列,则 SQL ServerSQL Server 没有完整的键可进行哈希运算。 If an index key consists of two columns and you only provide the first column, SQL ServerSQL Server does not have a complete key to hash. 这将产生索引扫描查询计划。This will result in an index scan query plan. 由用法决定应编制哪些列的索引。Usage determines which columns should be indexed.

当非聚集索引中的列在许多行中的值相同(索引键列有许多重复值)时,更新、插入和删除的性能会降低。When a column in a nonclustered index has the same value in many rows (index key columns have a lot of duplicate values), performance can degrade for updates, inserts, and deletions. 在这种情况下提高性能的一种方法是向非聚集索引添加另一列。One way to improve performance in this situation is to add another column to the nonclustered index.

针对内存优化表和基于磁盘的表的操作。Operations on memory-optimized and disk-based indexes.

运算Operation 内存优化、非聚集哈希索引Memory-optimized, nonclustered hash, index 内存优化的非聚集索引Memory-optimized nonclustered index 基于磁盘的索引Disk-based index
索引扫描,检索所有表行。Index Scan, retrieve all table rows. Yes Yes Yes
采用相等谓词 (=) 的索引查找。Index seek on equality predicate(s) (=). Yes

(需要完整的键。)(Full key required.)
1Yes 1 Yes
不等谓词的索引查找 (>,<, <=、 > =、 BETWEEN)。Index seek on inequality predicates (>, <, <=, >=, BETWEEN). 否(索引扫描中的结果)No (results in an index scan) 1Yes 1 Yes
按与索引定义匹配的排序顺序检索行。Retrieve rows in a sort-order matching the index definition. “否”No Yes Yes
按与索引定义相反的排序顺序检索行。Retrieve rows in a sort-order matching the reverse of the index definition. “否”No No Yes

在表格中,“是”意味着索引能充分地服务需求,而“否”则意味着索引不能用来成功地满足需求。In the table, Yes means that the index can adequately service the request and No means that the index cannot be used successfully to satisfy the request.

1完整的键不为非聚集内存优化索引中,需要执行索引查找。1 For a nonclustered memory-optimized index, the full key is not required to perform an index seek. 即便如此,给定索引键的列顺序后,如果缺少的列后面出现列值,则进行扫描。Although, given the column order of the index key, a scan will occur if a value for a column comes after a missing column.

索引计数Index Count

内存优化表可以具有多达 8 个索引,包括通过主键创建的索引。A memory-optimized table can have up to 8 indexes, including the index created with the primary key.

关于在内存优化表上创建的索引数目,请考虑以下几点:Regarding the number of indexes created on a memory-optimized table, consider the following:

  • 指定创建表时所需的索引。Specify the indexes you need when you create the table. 不能在创建内存优化表之后再为该表创建索引。You cannot create an index for a memory-optimized table after the table is created. 如果要向内存优化表添加索引,请删除并重新创建该表。If you want to add an index to a memory-optimized table, drop and recreate that table.

  • 请勿创建很少使用的索引:Do not create an index that you rarely use:

    如果表上的所有索引都经常使用,则垃圾收集效果最好。Garbage collection works best if all indexes on the table are frequently used. 很少使用的索引可能会导致垃圾收集系统对于旧的版本无法以最佳方式执行。Rarely-used indexes may cause the garbage collection system to not perform optimally for old row versions.

创建内存优化的索引:代码示例Creating a Memory-Optimized Index: Code Samples

列级别哈希索引:Column level hash index:

CREATE TABLE t1   
   (c1 INT NOT NULL INDEX idx HASH WITH (BUCKET_COUNT = 100))   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

表级别哈希索引:Table level hash index:

CREATE TABLE t1_1   
   (c1 INT NOT NULL,   
   INDEX IDX HASH (c1) WITH (BUCKET_COUNT = 100))   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

列级别主键哈希索引:Column level primary key hash index:

CREATE TABLE t2   
   (c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100))   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  

表级别主键哈希索引:Table level primary key hash index:

CREATE TABLE t2_2   
   (c1 INT NOT NULL,   
   PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 100))   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  

列级非聚集索引:Column level nonclustered index:

CREATE TABLE t3   
   (c1 INT NOT NULL INDEX ID)   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

表级非聚集索引:Table level nonclustered index:

CREATE TABLE t3_3   
   (c1 INT NOT NULL,   
   INDEX IDX NONCLUSTERED (c1))   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

列级主键非聚集索引:Column level primary key nonclustered index:

CREATE TABLE t4   
   (c1 INT NOT NULL PRIMARY KEY NONCLUSTERED)   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  

表级主键非聚集索引:Table level primary key nonclustered index:

CREATE TABLE t4_4   
   (c1 INT NOT NULL,   
   PRIMARY KEY NONCLUSTERED (c1))   
   WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  

定义列后定义的多列索引:Multicolumn index defined after columns are defined:

create table t (  
       a int not null constraint ta primary key nonclustered,  
       b int not null,  
       c int not null,  
       d int not null,  
       index idx_t_b_c_d nonclustered (b, c asc, d desc)  
) with (memory_optimized = on, durability = SCHEMA_AND_DATA)  
go  

请参阅See Also

为 XML Indexes on Memory-Optimized Tables
确定哈希索引的正确存储桶计数 Determining the Correct Bucket Count for Hash Indexes
哈希索引Hash Indexes