如何选择合适的索引

--Ref

• SQL Server 索引基础知识系列文章
• SQL Server 索引结构及其使用系列文章

--用法总结

下面的表总结了何时使用聚集索引或非聚集索引（很重要）：

 

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应--物理顺序不同
一个或极少不同值	不应	不应--selectivity小
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

--聚集索引vs非聚集索引

1. 聚簇索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个聚簇索引。由于 update,delete语句要求相对多一些的读操作，因此聚簇索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个聚簇索引。
   在下面的几个情况下，你可以考虑用聚簇索引：
   
   • 例如： 某列包括的不同值的个数是有限的（但是不是极少的）顾客表的州名列有50个左右的不同州名的缩写值，可以使用聚簇索引。
   • 例如： 对返回一定范围内值的列可以使用聚簇索引，比如用between,>,>=,<,<=等等来对列进行操作的列上。select * from sales where ord_date between ’5/1/93’ and ’6/1/93’
   • 例如： 对查询时返回大量结果的列可以使用聚簇索引。SELECT * FROM phonebook WHERE last_name = ’Smith’
     
     当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立聚簇索引。如果你建立了聚簇的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟聚簇索引的逻辑次序一样的。
     当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立聚簇索引。如果你建立了聚簇的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟聚簇索引的逻辑次序一样的
     
     
2. 一个非聚簇的索引就是行的物理次序与索引的次序是不同的。一个非聚簇索引的叶级包含了指向行数据页的指针。在一个表中可以有多个非聚簇索引，你可以在以下几个情况下考虑使用非聚簇索引。在有很多不同值的列上可以考虑使用非聚簇索引
   例如：一个part_id列在一个part表中 select * from employee where emp_id = ’pcm9809f’
   例如：查询语句中用order by 子句的列上可以考虑使用

• 这里有一个比较关键的概念 Bookmark Lookup 可参看【揭秘SQL Server 2000中的Bookmark Lookup】http://www.cnblogs.com/Yahong111/archive/2007/09/13/891641.html
  虽然聚集和非聚集索引结构相似,但是一个非聚簇的索引就是行的物理次序与索引的次序是不同的.聚集索引叶节点包含的是实际的值;非聚集索引有两种情况
  1.对于堆表:该指针是指向行的指针
  2.对于聚集索引表:该指针叫做行定位器Bookmark
  SQL Server在查找数据时，服务器先使用和使用聚集索引相同的查找方法找到该索引的行定位器 Bookmark，然后通过行定位器来找到所需要的数据，这种通过行定位器查找数据的方式就是Bookmark Lookup；
  这里注意不是所有的在一个聚集表上使用非聚集索引进行查询,其性能低于在堆集上使用非聚集索引进行查询.因为当返回的字段包含了非聚集索引和聚集索引的列值，那么就会产生索引覆盖，而堆集上使用非聚集索引的返回字段只能是只身才会形成索引覆盖
  3.索引覆盖:在基于非聚集索引查找数据时，还有另外一种情形，那就是如果放回的数据列就包含于索引的键值中，或者包含于索引的键值+聚集索引的键值中，那么就不会发生Bookup Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了。这种情况，叫做索引覆盖；请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
  可以看看这个例子【Sql Server Database Indexes and Execution Plans】 http://www.cnblogs.com/programmingsnail/archive/2009/10/19/1585994.html
  
  
• 一个例子sp_spaceused 'order' 结果是3G大小谓词where date = '2009-12-10' 此时date字段上有非聚集索引,那么选择器将会
  1.自增列上建立聚集索引:对date字段上的非聚集索引扫描后，还需要去聚集索引树上seek一下 [子增列建立索引的问题] http://www.cnblogs.com/perfectdesign/archive/2008/05/28/identityID.html
  2.表没有聚集索引:去掉自增列上的主键聚集索引,此时表为堆,在非聚集索引扫描后直接就拿到ROWID(堆表的非聚集索引叶节点所存储的标 识所在行包括:FileID,pageID,SlotID)
  3.索引覆盖:将所有需要的字段都汇总到非聚集索引上比如
  

  select a,b from table where c; 
  --2000中索引覆盖为
  create index idx on t(c,a,b)
  
  --2005中索引覆盖为
  create index idx on t(c) include (a,b)
  
  通过扫描C键值所在的索引上层结构快速找到where条件所需的边界,然后扫描子叶层;循环扫描到a,b的记录位置
  
  --??我觉得这里有一个可以测试的地方就是到底是索引覆盖还是date字段上建立聚集索引好,上一篇文章中有一个查询性能比较：
  1. 返回行数较多：索引覆盖>聚集索引>表扫描>堆集的非聚集索引>聚集的非聚集索引
  2. 返回行数较少：索引覆盖=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表扫描

• 测试
  

  
  --SQL Server 2005 Performance Tuning性能调校 代码列表 6.14：通过各种索引，测试所花的 IO 页数.sql
  USE Credit
  GO
  EXEC spCleanIdx 'Charge'
  
  --要求返回 IO 的统计，也就是分页访问的数目
  SET STATISTICS IO ON
  
  --没有索引的页数
  --表 'charge'。扫描计数 1，逻辑读取 584，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  SELECT charge_no FROM charge 
  WHERE charge_amt BETWEEN 20 AND 3000
  
  --通过聚簇索引查询的页数
  --表 'charge'。扫描计数 1，逻辑读取 419，实际读取 0，读取前读取 14，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  CREATE CLUSTERED INDEX ix_charge_amt ON Charge(charge_amt)
  SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
  
  DROP INDEX Charge.ix_charge_amt
  
  --强制通过非聚簇索引查询的页数,用错索引比不用索引糟糕很多倍
  CREATE INDEX ix_charge_amt ON Charge(charge_amt)
  --表 'charge'。扫描计数 5，逻辑读取 60198，实际读取 0，读取前读取 3，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  --表 'Worktable'。扫描计数 0，逻辑读取 0，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  
  SELECT charge_no FROM charge WITH(INDEX(ix_charge_amt)) WHERE charge_amt BETWEEN 20 AND 3000
  
  DROP INDEX Charge.ix_charge_amt
  
  --通过字段顺序不适用的覆盖索引查询的页数
  CREATE INDEX ix_charge_amt ON Charge(charge_no,charge_amt)
  --表 'charge'。扫描计数 1，逻辑读取 292，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
  
  DROP INDEX Charge.ix_charge_amt
  
  --通过覆盖索引查询的页数
  CREATE INDEX ix_charge_amt ON Charge(charge_amt,charge_no)
  --表 'charge'。扫描计数 1，逻辑读取 175，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
  
  DROP INDEX Charge.ix_charge_amt
  
  --通过字段顺序不适用的覆盖索引查询的页数
  CREATE INDEX ix_charge_amt ON Charge(charge_no) INCLUDE(charge_amt)
  --表 'charge'。扫描计数 1，逻辑读取 290，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
  
  DROP INDEX Charge.ix_charge_amt
  
  --透过子叶层覆盖索引查询的页数
  CREATE INDEX ix_charge_amt ON Charge(charge_amt) INCLUDE(Charge_no)
  --表 'charge'。扫描计数 1，逻辑读取 174，实际读取 0，读取前读取 0，LOB 逻辑读取 0，LOB 实际读取 0，LOB 读取前读取 0。
  SELECT charge_no FROM charge WHERE charge_amt BETWEEN 20 AND 3000
  
  DROP INDEX Charge.ix_charge_amt
  

   

   
• 上边文章里还有些重点
  • 一个堆集在sysindexes内有一行，其indid=0；
  • 某个表和视图的聚集索引在sysindexes内有一行，其indid=1，root列指向聚集索引B树的顶端；
  • 某个表或视图的非聚集索引在索引在sysindexes内也有一行，其indid值从2到250，root列指向非聚集索引B树的顶端；
  • SQL Server 的数据文件中有一类是IAM，即索引分配映射表，它存储有关表和索引所使用的扩展盘区信息；
  • Bookmark Lookup逻辑运算符和物理运算符使用书签（行 ID 或聚集键）在表或聚集索引内查找相应的行；

--是否值得建索引

无论在哪个数据库里都会有这样的疑问,但是这里永远有三个标准帮助我们来选择,他们是selectivity,density,distribution

• selectivity:首先要看需要建索引列的选择性,例如
  

  select * from test where id = 1 --假定select count(*) from test 是10000 那么这个的选择性就是 1/10000,选择性很高,适合建立索引
  select * from test where id > 1 --假定select count(*) from test 是10000 那么这个的选择性就是 9999/10000,选择性很低,不适合建立索引
  除非在id字段是聚集索引,如果采用非聚集索引,反而变成需要读至少9999页以上,因为每读取一条记录时都要将整页读出,再从中取出目标记录,就算数据记录在同一页上也要读多次

• density:密度指键值唯一的记录条数分之一 
  

  select 1/(select count(distinct id) from test) 
  --当结果越小也就是唯一性越高,就越合适建立索引,也可以使用以下方法检测看传回的All Density值
  Create index idx_id on test(id)
  DBCC Show_Statistics(test,idx_id)

• distribution:一个范围之内的记录条数,或者某个分区的记录条数
• 看看是否建多了索引
  select * from  sys.dm_db_index_usage_stats where object_id=object_id('table_name')
• 一些分析索引缺失的视图 (SQL Server 2005 Performance Tuning性能调校(含光盘) P300)

• select * from sys.dm_db_missing_index_groups
  select * from sys.dm_db_missing_index_group_stats
  select * from sys.dm_db_missing_index_details
  
  SELECT mig.*, statement AS table_name,
      column_id, column_name, column_usage
  FROM sys.dm_db_missing_index_details AS mid
  CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
  INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
  ORDER BY mig.index_group_handle, mig.index_handle, column_id;
  
  通过动态管理对象sys.dm_db_missing_index_details和sys.dm_db_missing_index_columns函数返回的结果呈现所需索引键数据行是相等(Equality),不相等(Inequality)或包容(Include)
  sys.dm_db_missing_index_details视图会在Equality_Columns,Inequality_Columns或Include_Columns等行返回这些信息
  sys.dm_db_missing_index_columns函数会在其column_usage数据行中返回此信息
  所以最后的规则就是将Equality_Columns放在最前边,Inequality_Columns随后,然后把Include_Columns放到Include子句中
  create index idx_test on test(Equality_Columns,Inequality_Columns) include (Include_Columns_1,Include_Columns_2)

--一些测试(这个是我看别人文章的总结,忘记出处了,抱歉)

1. 用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。 
2. 时间搜索：使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
   

   select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' 
   --用时：6343毫秒（提取100万条） 整年
   
   select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-6-6'' 
   --用时：3170毫秒（提取50万条）半年
   
   select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
   --用时：3326毫秒（和上句的结果一样.如果采集的数量一样,那么用大于号和等于号是一样的,和半年的数据量一样)
   
   select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' and fariqi<''2004-6-6'' 
   --用时：3280毫秒 半年
   
   --得出以上速度的方法是：在各个select语句前加：
   declare @d datetime
   set @d=getdate()
   --SQL Query
   select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 

--碎片

• 读书笔记 - 高效维护数据库的关键技巧

--统计信息对索引使用的影响

• 建立测试环境
   

  USE Tempdb
  --测试统计过期的结果
  SET NOCOUNT ON
  SET STATISTICS IO OFF
  SET STATISTICS PROFILE OFF
  
  CREATE TABLE tblTest(
  UserId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  UserName NVARCHAR(20),
  Gender NCHAR(1))
  
  --一开始构造 100000 笔 '女' 一笔 '男' 的悬殊记录差异
  INSERT tblTest VALUES('Hello World','男')
  
  DECLARE @int INT
  SET @int=1
  WHILE @int<100000
  BEGIN
      INSERT tblTest VALUES('Hello ' + CONVERT(NVARCHAR,@int),
              --CASE WHEN @int%2 = 0 THEN '男' ELSE '女' END
              '女'
              )
      SET @int=@int+1 
  END 

   

  --执行计划建立,更新,删除命令
  ALTER DATABASE SET
  CREATE STATISTICS
  DBCC SHOW_STATISTICS
  sp_help 'et_order'
  DBCC SHOW_STATISTICS ('et_order', idx_et_0);
  
  DROP STATISTICS
  sp_autostats
  sp_createstats
  UPDATE STATISTICS

   

• 统计数据的影响
  

  --此时建立索引所同时产生的统计会记录如此悬殊的比值
  CREATE INDEX idxGender ON tblTest(Gender)
  EXEC sp_helpindex tblTest
  --没有单独的统计数据
  EXEC sp_helpstats tblTest
  
  --统计是正确的，索引合用于当下的查询
  SET STATISTICS IO ON
  SELECT * FROM tblTest WHERE Gender='男'
  --强迫表扫描
  SELECT * FROM tblTest WITH(INDEX(0)) WHERE Gender='男'
  SET STATISTICS IO OFF
  
  --故意要求不要自动更新统计数据
  --EXEC sp_dboption 'Credit','Auto Update Statistics', { TRUE | FALSE} --针对整个表
  EXEC sp_autostats 'tblTest','OFF',idxGender
  
  --将记录改成 1:1
  UPDATE tblTest SET Gender='男' WHERE UserID %2=0
  
  SELECT Gender,COUNT(*) FROM tblTest GROUP BY Gender
  
  --比对一下用错索引时，两者的 I/O 差异
  SET STATISTICS IO ON
  
  --通过 SET STATISTICS PROFILE 输出的 Rows 和 EstimateRows 
  --可以比较真实与估计的记录数差异
  SET STATISTICS PROFILE ON
  
  SELECT * FROM tblTest WHERE Gender='男'
  
  --强迫表扫描
  SELECT * FROM tblTest WITH(INDEX(0)) WHERE Gender='男'
  
  DBCC SHOW_STATISTICS(tblTest,idxGender)--这个是建立在统计信息基础上的,上边把统计信息停止后,这个返回的结果是错误的
  
  --做完统计更新后，可以再试一次前述的范例
  --但要先清除旧的运行计划
  UPDATE STATISTICS tblTest
  DBCC FREEPROCCACHE

--