SQLserver 索引概念与应用

一、索引的概念

• SQL 调优中有一则公式：T = S / V 其中 T 就是查询过程中所需要的时间，S 就是查询过程中所需要消耗的资源，而 V 则表示单位时间内资源的利用率，想要提升一条 SQL 的执行效率我们就可以从减少 S 增加 V 来考虑，设计数据库的大佬们就选择了 B tree 结构来作为底层的索引算法，如果能够让数据库正确索引，则会减少很多次 IO 操作也就是减少了 S 资源的消耗。

• MSSQL 的索引类型：

  1. 按照存储结构区分：聚簇索引、二级索引；
  2. 按照唯一性区分：唯一索引、非唯一索引；
  3. 按照索引列树区分：单列索引、复合索引；

• 聚簇索引与二级索引：SQLserver 与 MySQL 在索引设计方案相似，所有的数据都会依据 “主键索引字段” 的排序规则组织一颗索引树，然后树的最底层存储的是所有数据，所以说如果 WHERE 条件使用主键来过滤数据就可以使用聚簇索引，因为索引树是依据主键字段的排序规则组织的，所以只支持主键索引过滤。那么当我们有使用非主键索引作为过滤条件的需求时，就需要二级索引，按照我们指定的非主键索引字段的排序规则组织一颗索引树，打个比分我们有一张表有三个字段 id、name、phone 其中 id 为主键字段，如果使用 id 去作为过滤字段，效果当然非常好，因为主键有聚簇索引，那如果我们有一个需求要使用 name 字段来查询一个人的所有信息，那么此时就可以用 name 字段的排序规则组织一颗索引树，这颗索引树被称为 “二级索引” 或者 “非聚簇索引” 很显然我们只通过二级索引查出的数据只有该字段和主键索引的 Key 不能得到所有的数据，此时就需要拿着二级索引查询出的 Key 去主键索引再扫描一次，然后从底层拿出所有数据，这个过程叫做 “回表查询”。

• 填充因子(FILLFACTOR)：

  1. 什么是填充因子？MSSQL 索引的一个特性，定义该索引每页上的可用空间量。适应以后表数据的扩展并减小了页拆分的可能性。填充因子是从0到100的百分比数值，设为100时表示将数据页填满只有当不会对数据进行更改时(例如 只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的硬盘空间，但是性能会提高。所以设置的时候可以根据需求 “性能” 与 “资源” 二选一。

  2. 填充因子指定多少才合适？前辈经验：

     1. 低更改的表，几乎只读指定接近 100；
     2. 高更改的表，写超过读 50～70；
     3. 读写各占一半，80～90 填充因子；

二、MSSQL 索引管理维护

• MSSQL 创建索引语法：

  CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
  INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n])
  

  select * from AccountInfo with(index=indexAccount) where AccountCode='6225125478544587'
  

• CREATE INDEX 参数说明：

  1. UNIQUE：用于创建唯一索引约束，即创建该索引字段不能有重复值；
  2. CLUSTERED：用于创建聚集索引；
  3. NONCLUSTERED：用于创建非聚簇索引，也就是二级索引；
  4. index_name：用于指定所创建的索引的名称；
  5. table：用于指定创建索引的表的名称；
  6. view：用于指定创建索引的视图的名称；
  7. ASC | DESC：用于指定具体某个索引列的升序或降序排序方向；
  8. Column：用于指定被索引的列；
  9. PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间；
  10. FILLFACTOR = fillfactor：指定索引页的填充因子；
  11. IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应；
  12. DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引；
  13. STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算；
  14. SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中；
  15. ON filegroup：用于指定存放索引的文件组。

• 覆盖索引 include 用法：SQLserver 在 2005 版本提供了这个参数，比如有一条 SQL 过滤条件包含多个字段，字段选择性一般，但是合并在一起创建一个复合索引也是很快的，举一个栗子 🌰 吧!

  SELECT 
    ProcInstID, 
    ActInstDestID, 
    SN, 
    Status, 
    UserID, 
    ActivityK2Name, 
    DelegateUserID 
  FROM 
    BPM_JobList
  WHERE 
    AND JobID = ? 
    AND AppId = ?
  

  请看上方 SQL 结构很简单，此时如果表中的数据量特别大，我们需要加索引优化，核对 JobID 和 AppId 的选择性不错。

  1. 以 MySQL 角度来看，直接给 WHERE 条件两个字段添加复合索引，按照 JobID 字段的排序规则组织索引树，当 JobID 字段相同时再按照 AppId 字段排序规则组织，底层存储的是主键索引的 Key 和 JobID 及 AppId 两个字段，在这条 SQL 上是无法避免回表查询，不修改 SQL 情况下，该 SQL 在 MySQL 角度已经没有优化空间。

     alter table BPM_JobList add index idx_jobid_appid(JobID, AppId)
     

  2. SQLserver 针对此类情况作出优化，在创建索引时可以使用 include 额外指定叶子节点存储的字段，这些字段不影响索引树的组织，只用于避免回表查询。请看下方 T-SQL 只会按照 JobID 及 AppId 两个字段的排序规则组织索引树，底层不仅会存储主键 Key还会额外存储 INCLUDE 中包含的字段，那么使用这个索引就避免回表查询。

     CREATE NONCLUSTERED INDEX [idx_appid_jobid] 
     	ON [dbo].[BPM_JobList] ([AppId], [JobID]) 
     	INCLUDE (
       [ProcInstID], 
       [ActInstDestID], 
       [SN], 
       [Status], 
       [UserID], 
       [ActivityK2Name], 
       [DelegateUserID]
     )
     

• MSSQL 索引维护：

  1. 查询表中的索引等价于 MySQL show index from table_name;

     exec sp_helpindex table_name;
     

  2. 删除索引的名称：

     drop index table_name.index_name
     

  3. 检查索引的碎片信息：

     dbcc showcontig(BankCard,PK__BankCard__55FF25F13F49F3DB)
     

  4. 整理索引上的碎片：

     dbcc indexdefrag(BankCard,PK__BankCard__55FF25F13F49F3DB)
     

  5. 更新表中所有索引的统计信息：

     update statistics BankCard