EXPERT FOR SQL SERVER诊断系列--索引

概述

 

索引设计是数据库设计中比较重要的一个环节，对数据库的性能起着至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。在我经历过的，众多的数据库性能问题案例中，80% 系统都存在索引不合理的问题.

 

 

为什么需要索引

 

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。

然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升。

举个例子，我们使用微软的示例数据库， 对于下面的查询：

select SalesOrderDetailID FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID=61026

1.SalesOrderDetailID 列没有索引

查询会进行全表扫描。逻辑读如下图：

 

表的数据页数如下所示，两者是几乎相等的。说明他对每个数据页都进行需要读取一次。

 

2.SalesOrderDetailID 建立索引，逻辑读取仅2次。

因为log2 1246 是等于10.28. 最大的逻辑读取次数是11.

 

 

利用EXPERT找到缺失索引

 

既然索引对我们如此重要，那么我们如何去快速，方便的找到需要的索引呢。利用体检专家可以轻松做到。

1.通过执行计划

在执行计划选项卡，对于查询语句，如果存在缺失索引，体检专家会在缺失索引列上打钩，如图所示。

同时列出SQL了 语句执行次数和执行时间。

 

 

2.通过数据库

在数据库选项卡中，选中缺失索引，体检专家会自动列出数据库中所有的缺失索引。

 

 

 

 

生成脚本

在找到缺失索引后可以通过体检专家自动生成脚本。执行计划中也可以类似的生成脚本。

 

 

 

 

如何建立

 

通过体检专家可以轻松找到系统中缺失的索引。但对于系统中大量的缺失索引，我们如何去选择呢？

1.执行计划中的缺失索引

具体的值，根据业务不同可能有些不同。但是执行次数越多，执行时间越长，影响百分比越大，就越要建立索引。我的建议是：执行次数每天超过200，执行时间超过1000ms，影响百分比超过50%。

 

2.数据库中缺失索引

平均影响百分比越大，平均用户开销越大，用户查找次数越大，那么就越需要建立。

同样给出我的建议值：影响百分比50%，用户开销1，用户查找次数每天200

 

总结

贴图是来自北京某银行系统。系统长时间语句非常多，严重影响前端用户体验。对系统第一轮优化，通过体检专家建立合适的索引后，系统的整体性能提升超过50%以上。

 

 

 文章用到的 Expert FOR SQLSERVER 工具下载链接：http://zhuancloud.com/ReceptionViews/Install.html