SQL SERVER2014学习笔记2--内存优化表索引简介

[guguda2008]

还不了解2014内存优化表的请参考这里

本文介绍内存优化表索引的种类、结构和使用注意事项等
---------------------------------------------------------内存索引种类介绍-----------------------------------------------------------------------
内存优化表（下面简称内存表）的索引和磁盘表不管在结构还是使用上都有很大的区别。
传统的磁盘表，分聚集索引和非聚集索引，通过B树的结构存储于磁盘上，在使用时像磁盘表一样将索引页加载到内存中读取。
内存表的索引则完全不同，它的种类分两种：哈希索引 和 非聚集索引。后者虽然名字和磁盘表索引一样，但结构和使用方法都有区别。
先说哈希索引，它是在内存里指定一块空间作为索引列的存储空间，然后将索引列值分布于这块空间中，在查找数据时，通过对索引键套用哈希函数读取找到存放对应值的空间地址后读取值。这个原理相信熟悉哈希连接的同学都很熟悉了，内存表的哈希索引就是用这种方式快速生成索引和快速查找的。注意，是真的很快，没有硬盘读写，没有B树调整，没有碎片没有填充因子，就是最简单的填充和查找。
通过了解哈希索引的结构和动作方式我们可以知道，首先它是需要分配空间的，由于内存空间有限，又要保证索引使用的空间不会被释放，因此需要事先声明使用足够多的空间，然后才能正常使用，对于内存表来说，这个动作只能在建表的时候完成，CREATE INDEX语法不适用于内存表索引。然后，由于不是使用B树结构，因此哈希索引不支持范围查找，即>=,<,BETWEEN之类的操作，是用不到索引查找的。最后，由于是直接使用函数生成目标内存地址而不是读取索引页，哈希索引的点查找速度非常快。
哈希索引的这些特性，使它很适合成为几乎不重复的列的索引，而不适合成为具有大量重复值的列的索引。MS给出的建议，是平均重复数不超过8个。
说完哈希索引，再看一下内存表的非聚集索引。首先内存非聚集索引也不是B树存储，然后它支持范围查找和点查找，最后，它只支持顺向排序查找，不支持逆向，即具有某列ASC非聚集索引的列，无法在ORDER BY COL DESC的查询中用到这个索引。现在给出的文档中并没有描述内存非聚集索引的结构，但根据最后这个限制可以推测出它是用某种单向链表形式存储的。
---------------------------------------------------------内存索引使用注意事项-----------------------------------------------------------------------
首先我们需要知道的是，所有内存表都需要有主键，主键类型要么是哈希，要么是非聚集。因此，内存表上至少有一个索引。而索引数量的上限是每个表上8个索引。
对于哈希索引来说，使用方式几乎不用考虑，因为只有一种，只有=和in时才会用到。最需要注意的是存储桶数的设定，一个适合的哈希索引的标准基本就是超过30%的剩余空间、没有大量单一重复值。因此，在创建时最好声明预期表行数2到3倍的存储桶数，如果你估不出来行数，就声明大概数量的5倍。随时注意存储桶使用情况，这个情况可以通过MS提供的DMV查看：

SELECT 
   -- object_name(hs.object_id) AS 'object name', 
   i.name as 'index name', 
   hs.total_bucket_count,
   hs.empty_bucket_count,
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
   hs.avg_chain_length, 
   hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs 
   JOIN sys.indexes AS i 
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

只要empty_bucket_percent列较高，就说明有空余桶，官方建议30%以上，如果低于这个数，建议调高存储桶数；而avg_chain_length则可以看到平均的重复值数量，官方建议不超过8，如果超过了建议增加索引键列来降低重复，或改成非聚集索引。

对于内存非聚集索引来说，使用方式只需要注意别逆向查找就好，最需要注意的是索引的大小。这个通过监控表数据量可以实现。MS给出了一段例子可以参考：
对于大型表，内存使用量可能成问题。 例如，对于有 2.5 亿行的表，它有 4 个哈希索引，每个都具有 10 亿的 Bucket 计数，则针对哈希表的开销是 4 个索引 * 10 亿 Bucket 计数 * 8 字节 = 32 GB 的内存使用量。 在为每个索引选择 2.5 亿的 Bucket 计数时，针对哈希表的总开销将是 8 GB。 请注意，这还没有算上每个索引添加到每个单独行的 8 字节的内存使用量，在此情况下为 8 GB（4 个索引 * 8 字节 * 2.5 亿行）。
-------------------------------------------------------------最后------------------------------------------------------------------
上篇就提过，内存表的数量控制很重要，反过来说，只要数量控制好了，索引什么的都不是问题，而它带来的性能优势是前所未有的。因此还是值得尝试的。

对于索引的性能测试我现在不方便测，后面会另起文章补上。

[KeepSayingNo]

1、希望楼主说明怎么样建立哈希索引 2、可以把聚族索引也拿进来一起比较

[黄_瓜]

我以为你那天给我说的是nba2014

[haitao]

原来sql2014第一条就是in memory 什么35x、340x的提升。。。。。 以前版本对内存、cache的利用，似乎也不错了，还能提高这么多倍？！

[五锅锅]

[好好学习一下]

不错学习了

[FancyMouse]

主要还是因为column store，在执行的时候更容易利用cpu的缓存，所以还能提高很多倍。

[haitao]

引用 34 楼 sz_haitao 的回复:

原来sql2014第一条就是in memory 什么35x、340x的提升。。。。。 以前版本对内存、cache的利用，似乎也不错了，还能提高这么多倍？！

速度有这么多提升，需要对2014做什么设置吗？还是默认安装就能享受这种提升？ 另外，ms应该组织 第一印象 文档： 2014各个版本（主要是企业版）支持 最多多少内存、数据库。。。 好像win2003不管打什么补丁，都不支持sql2014了！ 能直接从2005升级吗？ 能直接使用2005的数据库文件吗？还原2005的备份文件，似乎是可以的

[msxp]

学习一下,我才刚用上2008

[Neo_whl]

求索者继续跟进拜读！

[夏日小虫]

好东西，刚好学习

[鸥翔鱼游1]

期待中.......

[山寨DBA]

引用 12 楼 guguda2008 的回复:

[quote=引用 7 楼 yupeigu 的回复:] [quote=引用 5 楼 guguda2008 的回复:] [quote=引用 3 楼 yupeigu 的回复:] 可以按照多个列来建立哈希索引不

可以啊 这里有个点忘说了，内存索引没有包含列这一说，所有索引都包含所有列。[/quote] 你的意思是，在进行hash运算的时候，会对所有的列进行运算[/quote] 意思是内存的两类索引都在存放值的行里存了所有值，不用再去写索引包含哪些列了。[/quote] 楼主，内存优化表就只能创建一个索引（主键对应的hash索引）吗？不能创建多个索引了？

[guguda2008]

引用 43 楼 hwhmh2010 的回复:

[quote=引用 12 楼 guguda2008 的回复:] [quote=引用 7 楼 yupeigu 的回复:] [quote=引用 5 楼 guguda2008 的回复:] [quote=引用 3 楼 yupeigu 的回复:] 可以按照多个列来建立哈希索引不

可以啊 这里有个点忘说了，内存索引没有包含列这一说，所有索引都包含所有列。[/quote] 你的意思是，在进行hash运算的时候，会对所有的列进行运算[/quote] 意思是内存的两类索引都在存放值的行里存了所有值，不用再去写索引包含哪些列了。[/quote] 楼主，内存优化表就只能创建一个索引（主键对应的hash索引）吗？不能创建多个索引了？[/quote] 可以创建多个索引，每个索引实际上都可以认为是一张独立的内存表

[gem227]

学习了

[豫新]

dm_db_xtp_index_stats 楼主这个系統表是不是只有2014才有?

[BackerCao]

请问如何删除内存表？

[landingof]

膜拜~~

[释怀355_H]

感谢楼主分享

[litf]

引用 44 楼 guguda2008 的回复:

[quote=引用 43 楼 hwhmh2010 的回复:] [quote=引用 12 楼 guguda2008 的回复:] [quote=引用 7 楼 yupeigu 的回复:] [quote=引用 5 楼 guguda2008 的回复:] [quote=引用 3 楼 yupeigu 的回复:] 可以按照多个列来建立哈希索引不

可以啊 这里有个点忘说了，内存索引没有包含列这一说，所有索引都包含所有列。[/quote] 你的意思是，在进行hash运算的时候，会对所有的列进行运算[/quote] 意思是内存的两类索引都在存放值的行里存了所有值，不用再去写索引包含哪些列了。[/quote] 楼主，内存优化表就只能创建一个索引（主键对应的hash索引）吗？不能创建多个索引了？[/quote] 可以创建多个索引，每个索引实际上都可以认为是一张独立的内存表[/quote] 楼主，如果我再某列建了hash索引 ，在where条件中是多列，但包含了hash索引列，会用到hash搜因吗

[guguda2008]

引用 51 楼 litf 的回复:

[quote=引用 44 楼 guguda2008 的回复:] [quote=引用 43 楼 hwhmh2010 的回复:] [quote=引用 12 楼 guguda2008 的回复:] [quote=引用 7 楼 yupeigu 的回复:] [quote=引用 5 楼 guguda2008 的回复:] [quote=引用 3 楼 yupeigu 的回复:] 可以按照多个列来建立哈希索引不

可以啊 这里有个点忘说了，内存索引没有包含列这一说，所有索引都包含所有列。[/quote] 你的意思是，在进行hash运算的时候，会对所有的列进行运算[/quote] 意思是内存的两类索引都在存放值的行里存了所有值，不用再去写索引包含哪些列了。[/quote] 楼主，内存优化表就只能创建一个索引（主键对应的hash索引）吗？不能创建多个索引了？[/quote] 可以创建多个索引，每个索引实际上都可以认为是一张独立的内存表[/quote] 楼主，如果我再某列建了hash索引 ，在where条件中是多列，但包含了hash索引列，会用到hash搜因吗[/quote] 基本都会用到的，除非其它列上有更合适的索引