《索引优化测试.doc》由会员分享，可在线阅读，更多相关《索引优化测试.doc（11页珍藏版）》请在金锄头文库上搜索。
1、索引优化测试1.1 相关知识在本次优化测试之前，我们再强调一些相关知识，以便于我们分析测试结果。至于索引等基础知识，请参阅SQL Server联机丛书或相关资料，本处另不赘述。1）数据页：SQL Server获取数据，总是以页为单位，就算是只读取一行也会获取整张页。2）聚集索引与非聚集索引主要有两方面区别：基础表的数据行按聚集键值进行排序，而不按非聚集键的顺序排序和存储；聚集索引的叶层是由数据页组成，而非聚集索引的叶层是由索引页组成。3）我们在评估一段SQL语句的执行效率时通常查看其逻辑读取，而所谓的逻辑读取指的是从数据缓存中读取的页数；同理，物理读取是指从磁盘读入的页数。4）填充因子：确定每
2、个叶级页上要填充数据的空间百分比，以便保留一定百分比的可用空间供以后扩展索引。5）对于不使用搜索参数（SARG）运算符的表达式，索引是没有用的。非SARG运算符包括NOT、!=、NOT EXISTS、NOT IN、NOT LIKE、LIKE（后缀匹配）和用户自定义函数、系统内置函数等，此外，最好也不要包含计算，尤其是同一表内列之间的计算。1.2 索引优化测试1.2.1 索引有效性测试我们可以以鄞州项目的基础表_财务类_记账凭证表为例，该表记录所有账簿的A账凭证分录数据，在日常使用中，主要从中搜索某条凭证的明细分录（搜索条件：会计电子账簿编号、记账凭证日期、记账凭证编号）或某个科目的相关分录（搜
3、索条件：会计电子账簿编号、会计科目编号、记账凭证日期（可选条件）。在测试服务器上，基础表_财务类_记账凭证表共有487647条记录。在执行测试语句前，我们都使用以下语句清除数据库缓存。请注意，在服务器上对该语句的使用必须要小心，因为它将导致一段时间内后续的SQL语句执行变慢。DBCC FREEPROCCACHE-从计划缓存中删除所有元素DBCC DROPCLEANBUFFERS-从缓冲池中删除所有清除缓冲区1.2.1.1 聚集索引有效性测试我们在基础表_财务类_记账凭证表中先删除其主键，因为在建立主键时，系统默认建立了一个同名的聚集索引。此后，使用下面的语句建立组合聚集索引INDEX_科目：C
4、REATE CLUSTERED INDEX INDEX_科目 ON 基础表_财务类_记账凭证表(会计电子账簿编号 ASC,会计科目编号 ASC,记账凭证日期 ASC)此处我们未设置索引选项，即全部按照默认设置，在后面的索引建立过程中，我们将保持一致的风格以保证相同的测试条件。在建立聚集索引INDEX_科目前后，我们执行以下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139 AND 会计科目编号 = 5010101010210 AND 记账凭证日期 = 2007-6-30-执行结果：169条数据执行期间统计信息如下：有无索引扫描计数(次)
5、逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)无索引5163750163753304有索引1102734我们再执行以下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139 AND 会计科目编号 = 5010101010210-执行结果：349条数据执行期间统计信息如下：有无索引扫描计数(次)逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)无索引5163750163753479有索引11621362最后，我们执行以下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 1
6、39-执行结果：12347条数据执行期间统计信息如下：有无索引扫描计数(次)逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)无索引1163750163754418有索引143834351231对比聚集索引INDEX_科目建立前后不同条件查询的统计信息，发现组合聚集索引在各种条件下均有惊人表现，节省了大量的IO与时间。在建立索引前，三种查询条件均使用索引扫描（index scan）读取所有数据，它们的逻辑读取与物理读取是一样的；而建立聚集索引后，根据查询条件的不同，SQL Server2005会通过索引搜索（index seek）逐级确定数据的范围，再取范围内的数据页，尽可能地减少读取
7、次数。1.2.1.2 非聚集索引有效性测试在聚集索引有效性测试中我们删除了基础表_财务类_记账凭证表主键以新建聚集索引INDEX_科目，现在在非聚集索引有效性测试中，我们重新建立该主键。此操作的目的是贴合现有表结构；另外在实际中大多数数据表都是有主键的，故此环境下的测试结果会更具参考意义。此后，使用下面的语句建立组合非聚集索引INDEX_凭证：CREATE NONCLUSTERED INDEX INDEX_凭证 ON 基础表_财务类_记账凭证表(会计电子账簿编号 ASC,记账凭证日期 ASC,记账凭证编号 ASC)在建立非聚集索引INDEX_凭证前后，我们执行以下的查询语句：SELECT *
8、FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139 AND 记账凭证日期 = 2007-02-26 AND 记账凭证编号 = 65-执行结果：6条数据执行期间统计信息如下：有无索引扫描计数(次)逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)无索引5179916164114077有索引1215033我们再执行以下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139 AND 记账凭证日期 = 2007-02-26-执行结果：1131条数据执行期间统计信息如下：有无索引扫描计数(次)逻辑读取(次)物理读取
9、(次)预读(次)执行占用时间(ms)无索引5179916164113896有索引14780842204最后，我们执行以下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139-执行结果：12347条数据执行期间统计信息如下：有无索引扫描计数(次)逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)无索引1164163164124602有索引1164153164114704对比非聚集索引INDEX_凭证建立前后不同条件查询的统计信息，发现组合非聚集索引在许多情况下也能节省不少IO与时间。在前两组查询条件中，建立非聚集索引后，非聚集索引都
10、能以搜索索引（index seek）逐级确定数据的聚集索引键，并递归地根据聚集索引键快速定位数据页。但是在第三组查询条件中，符合的数据量很大，它们很有可能分布在大量的数据页上（这也是非聚集索引与聚集索引的区别所在），加上递归读取聚集索引页的开支，这使得非聚集索引搜索（noncustered index seek）的效率还不如聚集索引扫描（clustered index scan），此时即便有索引INDEX_凭证的存在，SQL Server2005仍会产生一个聚集索引扫描（clustered index scan）以完成目标数据读取，也就是说，此时的索引并未起效。1.2.2 索引有效性比较前面验
11、证了索引对于数据查询的优化效果，总的来说，聚集索引要优于非聚集索引。但是，每个表只允许有一个聚集索引，所以，对于有多组常用查询条件的数据表来说，应用哪组字段建立聚集索引便成了需要衡量的问题。另外，由于聚集索引会对非聚集索引的效率造成影响，所以，选择正确的聚集索引更显重要。1.2.2.1 聚集索引VS非聚集索引我们保留了非聚集索引INDEX_凭证作本次比较的选手之一。同时，作为对比，我们使用下列语句建立聚集索引INDEX_凭证（当然，在记录完非聚集索引INDEX_凭证的相关数据并将其与主键一并删除之后）CREATE CLUSTERED INDEX INDEX_凭证 ON 基础表_财务类_记账凭证
12、表(会计电子账簿编号 ASC,记账凭证日期 ASC,记账凭证编号 ASC)这次执行的查询语句也和上次一样：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139 AND 记账凭证日期 = 2007-02-26 AND 记账凭证编号 = 65-执行结果：6条数据执行期间统计信息如下：索引类别扫描计数(次)逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)聚集索引151215非聚集索引1215033虽然两边的IO与时间消耗都很少，但我们仍然能查看出聚集索引比非聚集索引具有更高的效率，毕竟在本例中，非聚集索引是通过聚集索引完成数据读取的。我们再执行以
13、下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139 AND 记账凭证日期 = 2007-02-26-执行结果：1131条数据执行期间统计信息如下：索引类别扫描计数(次)逻辑读取(次)物理读取(次)预读(次)执行占用时间(ms)聚集索引144240176非聚集索引143812030242在本例中，我们可以看到随着数据量增大，差距也随之扩大。最后，我们执行以下的查询语句：SELECT * FROM 基础表_财务类_记账凭证表 WHERE 会计电子账簿编号 = 139-执行结果：12347条数据执行期间统计信息如下：索引类别扫描计数(次)逻
14、辑读取(次)物理读取(次)预读(次)执行占用时间(ms)聚集索引143924361245非聚集索引1164153164114990这里的差距就更大了，在此需要注意后者，在非聚集索引的有效性测试中，我们分析了该语句在SQL Server2005中的执行情况：即便存在索引INDEX_凭证，SQL Server2005仍会以一个聚集索引扫描（clustered index scan）替代效率低下的非聚集索引搜索（noncustered index seek）完成目标数据的读取。而聚集索引不会出现这种情况，它的叶节点即是数据页，它即代表了数据的物理排序，所以由它可以直接确定目标数据的范围，而不会出现索引搜索（index seek）效率低于索引扫描（index scan）的情况。1.2.2.2 不同聚集索引下的非聚集索引聚集索
