索引的学习和使用

            又是1个都月过去了，迷迷糊糊的，这短时间工作也不稳定，也没静下心来好好学点啥（懒）。

关于索引

 　　　　数据库索引分为聚集索引和非聚集索引，聚集索引就是物理索引，也就是数据的物理的存储顺序，聚集索引的叶子节点就是数据行本身；非聚集索引是逻辑索引，也可以简单的认为是对聚集索引建立的索引，一般来说聚集索引的键就是非聚集索引的叶子节点(在不使用include时)。

聚集索引

　　　　聚集索引作为最重要的索引往往被我们所忽略，而其最大的优势就是大范围数据查询有着较高的效率，因此聚集索引列的选择往往对数据库性能有着灰常大的影响。为了尽量发挥聚集索引在大范围数据查找上的优势，推荐按以下顺序选择聚集索引列。

聚集索引字段选择优先级：时间字段>>会进行大范围查询的列>>具有唯一值的有实际意义的字段>>自增列ID

　　1.时间字段：若表里面有时间列，并且时间是按照数据插入顺序增长时（时间无需唯一即可有重复值，哪怕是大范围重复），建议采用时间列作为聚集 索引的第一选择。理由：聚集索引有一个巨大的优势就是进行大范围数据查找，而且这个优势会随着数据量的增加而越来越明显，一般来说我们需要进行大数据量范 围查询时都会用时间列围作为筛选条件，由于聚集索引不存在书签查找而且可以进行连续扫描，因此查询速度会非常快。时间列数据最好是顺序插入的这样可以尽量 减少磁盘碎片，是数据存储相对集中，便于连续数据读取。

　　2.会进行大范围查询的列：若表里面没有时间字段或者时间字段不适合做聚集索引，可以选择那些在建表时就明确知道会经常进行大范围数据筛选的 列，而且最好是选择性较低的列(即有较多重复值的列，性别这种列不算啦)，如有必要可以使用组合索引。理由：聚集索引在数据查询的优势主要在于范围数据查 找，把聚集索引弄成唯一的把这个大好优势给白白浪费了。

　　3.具有唯一值的有实际意义的字段：若找不到适合条件1、2的列，那还是乖乖的把聚集索引列建立在唯一列上吧，最好找那种有实际意义的具有唯一 性的列，比如订单表可以用订单号作聚集索引，订单明细表使用订单号和产品编号做联合聚集索引。理由：找不到合适的时间字段和较低选择性字段的话，把主键建 成聚集索引是我们大多情况下的选择。

　　这里建议把唯一性的聚集索引顺便建成主键，和编码时方法、变量命名一样，推荐列名自解释，即看到列名就知道它就是主键，省得你再去猜，比如订单 表你来个自增ID列做主键，再建一个OrderCode列做订单号，用这个表时你得怀疑这个OrderCode是不是唯一滴呢，有木有建立唯一约束呢，同 理在订单明细表来个自增列ID也会产生如此疑问，产生疑问还是小事，若是你忘记了在应该唯一的列上建立约束，没准哪天程序控制不好给你个巨大的惊喜。

　　4.自增列ID：前面3中条件都找不到合适的列了还是使用我们的神器自增列ID吧，自增列ID也是我们使用最多的主键(顺便也就成聚集索引 了)，而且能较好满足我们大多数需求。自增ID列堪称无所不能，int类型只占用4个字节完全满足窄索引要求，绝对的顺序存储可以有效降低索引碎片，完全 符合我们的见表习惯，有用没用来个自增ID列做主键总是没错滴。

　　这里考虑聚集索引的键列主要为查询考虑，有些观点认为应该总是把聚集索引建立唯一列上，这里不敢苟同，诚然有些特殊情况下确实需要这么做，但大 说情况下还是建立在选择性较低的列、时间列上比较好，这样才能发挥聚集索引在范围数据查找方面的巨大优势。关于聚集索引在列上重复数据SQL Server需要额外的建立唯一标示用以定位造成查询时的额外开销非常小，小到与其带来范围查找的优势而言完全可以忽略。

非聚集索引

　　　　与聚集索引不同，非聚集索引可以建立多个，这也给我们带来了很大的灵活，毕竟聚集索引就那么一个不可能靠它满足所有需求，更多的我们得依赖非聚集索 引。记住非聚集索引不是大白菜，你想键多少就建多少，建立索引是有代价的，任何涉及到索引列的数据修改都会导致索引的修改，索引越多数据的曾、删、改的额 外代价也就越大。对于非聚集索引来说，我们的目标是用尽可能少的索引覆盖尽可能多的查询。

非聚集索引的列选择顺序(组合索引):经常被使用为查询条件列>>具有较高选择性的列(选择性越高越好，唯一最好)>>经常排序的列

　　1.经常被使用为查询条件列：我们的查询千变万化，建立索引时要首先考虑有哪些列被经常性的用于各种查询，把使用频率较高的列作为组合索引的第 一列(先导列)，若一个查询中没有用到组合索引中的先导列，多数情况下这个索引就不会被使用，因此为了尽可能多的复用组合索引把使用较多的查询列作为组合 索引的第一列吧。(关于这点对于聚集索引的组合索引同样适用)

　　2.具有较高选择性的列：这点很简单尽量使用高选择性列作为先导列，如果可以通过第一个条件过滤(随便什么判定逻辑=、>、<、like)，只要能大幅减少数据范围，就把它作为先导列。

　　3.条件1、2、3都确定不了时那就用经常被排序的列吧，我们的很多操作都需要先进行排序才可以进行进一步查询，比如group by，like等操作都是要先进行排序操作才可以完成下一步查询。

　　　　以上概念均来自其他博客，下面的是自己的一些测试Demo.

Demo

　　　　表：zhangfunumberpoolst

　　　　表中数据量为：6001012行

　　　　尚未建立索引SQL:select * from  zhangfunumberpoolst where keyid='FC9AAB18-F342-4AC3-A607-56A3F13E27A7'

　　　　SQL Server 分析和编译时间: (1 行受影响)
　　　　　　表 'ZhangFuNumberPoolsT'。扫描计数 1，逻辑读取 95255 次，物理读取 1255 次，预读 94771 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
　　　  SQL Server 执行时间:
  　　　      CPU 时间 = 2699 毫秒，占用时间 = 29663 毫秒。

　　　　建立非聚集索引：CREATE   NONCLUSTERED   INDEX  index_keyid on  ZhangFuNumberPoolsT(keyid)  为KeyID（数据类型：Nvarchar(50)）建立索引

　　　　再次请求：select * from  zhangfunumberpoolst where keyid='FC9AAB18-F342-4AC3-A607-56A3F13E27A7'

　　　　SQL Server 分析和编译时间: (1 行受影响)

　　　　　　表 'ZhangFuNumberPoolsT'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 　　　 SQL Server 执行时间:
   　　　　 CPU 时间 = 0 毫秒，占用时间 = 89 毫秒。

　　　　删除索引SQL：drop index index_keyid  on   ZhangFuNumberPoolsT

　　　　注意非聚集索引不能建立在：Max类型的大对象数据类型上如：ntext、text、varchar(max)、 nvarchar(max)、varbinary(max)、xml

那么问题来了，大对象数据类型的如何建立索引（下面的例子感觉是杀鸡用牛刀，如果存储的是文本可能会比较合适）

　　　　哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行 的指针。

　　　　上面已经展示了未添加索引的性能，下面我直接添加哈希索引来进行对比。

　　　　建立哈希索引SQL:ALTER TABLE ZhangFuNumberPoolsT  ADD cs_name AS Checksum(keyid);-----添加一列列名：cs_name
　　　　　　　　　　       CREATE INDEX idx_name   ON ZhangFuNumberPoolsT(cs_name);----------添加为刚添加的列添加索引

　　　　查询SQL:select * from  zhangfunumberpoolst where keyid='FC9AAB18-F342-4AC3-A607-56A3F13E27A7'  AND cs_name = Checksum(
'FC9AAB18-F342-4AC3-A607-56A3F13E27A7' ) 

　　　　SQL Server 分析和编译时间: (1 行受影响)

　　　　　　表 'ZhangFuNumberPoolsT'。扫描计数 1，逻辑读取 4 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 　　　  SQL Server 执行时间:
  　　　　　CPU 时间 = 0 毫秒，占用时间 = 17 毫秒。

多条件下查询优化，多列组合索引妥妥的

　　　　查询条件：1.时间 2.类型（字段名：Type）

　　　　我要查询的SQL：select * from  zhangfunumberpoolst where CreatedTime>'2013-08-08 11:58:59.963' and CreatedTime<'2013-08-08 12:00:02.963' and Type=22
　　　　未建立索引SQL Server 分析和编译时间:
　　　　　　表 'ZhangFuNumberPoolsT'。扫描计数 1，逻辑读取 95255 次，物理读取 23 次，预读 563 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

　　　　SQL Server 执行时间:
 　　　　　　  CPU 时间 = 1045 毫秒，占用时间 = 2010 毫秒。

　　　　建立索引：CREATE   INDEX index_keyid  on   ZhangFuNumberPoolsT(createdtime,type)-----对CreatedTime,Type2个字段建立组合索引

　　　　建立索引后SQL Server 分析和编译时间:
　　　　　　表 'ZhangFuNumberPoolsT'。扫描计数 1，逻辑读取 102414 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
　　　　SQL Server 执行时间:
　　　　　　CPU 时间 = 312 毫秒，占用时间 = 1209 毫秒。
　　　　注意：dbcc freeProcCache----清楚sql缓存

最后

 　　　　聚集索引的优势在于大范围数据查询效率，因此需要将聚集索引建立在时间列、选择性相对较低并且经常会用于范围查询的列(选择性过低的如性别列肯定不行，过 低的选择性列索引建了等于白建，比如你在性别列上集索引以为通过性别列起码一下过滤掉一半数据，范围大大减小你就大错特错了，这点选择性通常查询优化器会 直接忽略掉，还不如个表扫描来的快），充分发挥聚集索引大范围数据查询优势。

　　　　非聚集索引要尽量使用选择性较高的列以尽可能减少返回的数据量，利用组合索引提高索引的复用率，不要建过多的无用索引，如果发现某个表建了很多的非聚集索引，不妨把那些索引、查询摘出来分析合并下，减少没用索引的数量，以提高整体性能。