应用索引技术优化SQL 语句(Part 1)_索引用空间换时间,它对任何sql的执行速度只有好处,没有坏处-CSDN博客

一、前言

很多数据库系统性能不理想是因为系统没有经过整体优化，存在大量性能低下的SQL 语句。这类SQL语句性能不好的首要原因是缺乏高效的索引。没有索引除了导致语句本身运行速度慢外，更是导致大量的磁盘读写操作，使得整个系统性能都受之影响而变差。解决这类系统的首要办法是优化这些没有索引或索引不够好的SQL语句。

本文讨论和索引相关的有关内容，以及通过分析语句的执行计划来讲述如何应用索引技术来优化SQL 语句。通过分析执行计划，读者可以检查索引是否有用，以及如何创建高效的索引。本文对数据库管理人员以及数据库系统开发人员都有一定参考意义。

如果读者不知道应该优化数据库系统的哪些SQL语句，那么建议读者参考笔者的另外一篇文章，《应用Profiler优化SQL Server数据库系统》。那篇文章介绍如何利用Profiler和Read80trace工具找出数据库系统中的关键的和频繁运行的SQL语句，你可以把精力花在这些最值得优化的SQL语句上面。

二、创建索引的关键

优化SQL语句的关键是尽可能减少语句的logical reads。这里说的logical reads是指语句执行时需要访问的单位为8K的数据页总数。logical reads 越少，其需要的内存和CPU时间也就越少，语句执行速度就越快。不言而喻，索引的最大好处是它可以极大减少SQL语句的logical reads数目，从而极大减少语句的执行时间。创建索引的关键是索引要能够大大减少语句的logical reads。一个索引好不好，主要看它减少的logical reads多不多。

运行set statistics io命令可以得到SQL语句的logical reads信息。举例如下：

在Query Analyzer 中运行如下的命令：

/***** Script 1 *****************************/

set statistics io on

select au_id,au_lname ,au_fname

from pubs..authors where au_lname ='Green'

set statistics io on

/********************************************/

输出结果如下：

au_id au_lname au_fname

----------- ---------------------------------------- --------------------

213-46-8915 Green Marjorie

(1 row(s) affected)

Table 'authors'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

上面的logical reads 1就是指该Select语句的逻辑读总数是1。Logical reads 越少越好。如果Logical reads很大，而返回的行数很少，也即两者相差较大，那么往往意味者语句需要优化。比如语句没有索引，或索引不够好等。注意Logical reads和后面的physical reads的区别。Logical reads中包含该语句从内存数据缓冲区中访问的页数和从物理磁盘读取的页数。而physical reads表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页。Read-ahead reads是SQL Server为了提高性能而产生的预读。预读可能会多读取一些数据。优化的时我们主要关注Logical Reads就可以了。注意如果physical Reads或Read-ahead reads很大，那么往往意味着语句的执行时间（duration）里面会有一部分耗费在等待物理磁盘IO上。

二、单字段索引，组合索引和覆盖索引

顾名思义，单字段索引是指只有一个字段的索引，而组合索引指有多个字段构成的索引。

下面的例子讲述创建这些索引的一些技巧，以及如何结合执行计划判断SQL语句是否利用了索引。

1．对出现在where子句中的字段加索引

先运行如下的语句创建示例所需要的表：

/**************Script 2************************************/

use tempdb

if exists (select * from dbo.sysobjects where id = object_id(N'[tbl1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [tbl1]

create table tbl1

(学生号 int,学生姓名 varchar(20),性别 char(2), 年龄 int,入学时间 datetime,备注 char(500))

declare @i int

set @i=0

declare @j int

set @j=0

while @i<5000

begin

if (rand()*10>3) set @j=1 else set @j=0

insert into tbl1 values(@i,

char( rand()*10+100)+char( rand()*5+50)+char( rand()*3+100)+char( rand()*6+80),

@j, 20+rand()*10,convert(varchar(20), getdate()-rand()*3000,112),

char( rand()*9+100)+char( rand()*4+50)+char( rand()*2+130)+char( rand()*5+70))

set @i=@i+1

end

/**************************************************/

然后我们看如下的语句应该如何创建索引：

/********Script 3**********************************/

set statistics profile on

set statistics io on

select 学生姓名, 入学时间 from tbl1 where 学生号=972

set statistics profile off

set statistics io off

/****************************************************/

注意上面的set statistics profile命令将输出语句的执行计划。也许你会问，为什么不用SET SHOWPLAN_ALL呢？使用SET SHOWPLAN_ALL也是可以的。不过set statistics profile输出的是SQL 语句的运行时候真正使用的执行计划，而SET SHOWPLAN_ALL输出的是预计（Estimate）的执行计划。使用SET SHOWPLAN_ALL是后面的语句并不会真正运行。

上面script输出结果（部分）如下：

学生姓名入学时间

-------------------- ------------------------------------------------------

g4eQ 2005-05-29 00:00:00.000

(1 row(s) affected)

Table 'tbl1'.Scan count 1,logical reads 385, physical reads 0,read-ahead reads 0.

Rows Executes StmtText

------------------------------------------------------------------------------

1 1 SELECT [学生姓名]=[学生姓名],[入学时间]=[入学时间] FROM [tbl1]

1 1 |--Table Scan(OBJECT:([tempdb].[dbo].[tbl1]), WHERE:([tbl1].

(2 row(s) affected)

从上面输出结果可以看到，这条语句执行时候使用了Table Scan，也就是对整个表进行了全表扫描。全表扫描的性能通常是很差的，要尽量避免。如果上面的select语句是数据库系统经常运行的关键语句，那么应该对它创建相应的索引。创建索引的技巧之一是对经常出现在where条件中的字段创建索引。所以对上面的select语句，应该在学生号字段上建立单字段索引idx_学生号：

create nonclustered index idx_学生号 on tbl1(学生号)

然后再运行Script 3，部分结果如下：

Table 'tbl1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

Rows Executes StmtText

---------------------------------------------------------------------------------------------

1 1 SELECT [学生姓名]=[学生姓名],[入学时间]=[入学时间] FROM [tbl1] WHERE [学生号]=@

1 1 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[tbl1]))

1 1 |--Index Seek(OBJECT:([tempdb].[dbo].[tbl1].[idx_学生号]), SEEK:([tbl1].

上面的结果显示我们刚刚创建的idx_学生号这个索引确实被使用到了。语句的logical reads极大减少，从没有索引前的385减少到3，Table Scan也变成了Index Seek，性能极大提高。从上面的例子可以知道，如果你在执行计划中看到Table Scan或聚集索引的Index Scan（聚集索引的Index Scan相当于Table Scan），而且对应的logical reads相当大，那么就要设法使之变成Index seek。设法避免Table scan或Index scan是优化SQL 语句使用的常用技巧。通常Index Seek需要的logical reads比前两者要少得多。

2．组合索引

如果where语句中有多个字段，那么可以考虑创建组合索引。例子如下：

/*****Script 4******************************************/

set statistics profile on

set statistics io on

select学生姓名, 入学时间 from tbl1

where 入学时间>='20050301' and 入学时间<'20050305' and 年龄>24

set statistics profile off

set statistics io off

/*******************************************************/

为了提高该语句的性能，可以在入学时间和年龄上建立一个组合索引如下：

create nonclustered index idx_入学时间年龄 on tbl1(入学时间,年龄)

你也许会问，如果把入学时间和年龄字段换个位置建立如下的组合索引如何？

create nonclustered index idx_年龄入学时间 on tbl1(年龄,入学时间)

这个索引没有前面的好。分析这两个字段的唯一性：

select count(*) from tbl1 group by 入学时间

select count(*) from tbl1 group by 年龄

部分输出结果如下：

distinct_value_of 入学时间

(2426 row(s) affected)

distinct_value_of 年龄

(10 row(s) affected)

结果显示入学时间字段有2426个唯一值，而年龄字段只有10个。也就是说入学时间字段的唯一性比年龄字段高得多。对于上面的两个索引分别运行Script 4，你会发现对第一个索引语句的logical reads是8 而第二个索引导致的logical reads为16，相差了一倍。如果表很大那么性能的差异可想而知。所以，组合索引中字段的顺序是非常重要的，越是唯一的字段越是要靠前。另外，无论是组合索引还是单个列的索引，尽量不要选择那些唯一性很低的字段。比如说，在只有两个值0和1的字段上建立索引没有多大意义。

有时候你要决定为每个相关字段单独建立索引还是建立一个组合索引。比如说如果下面的语句经常执行：

Select c1, c2,c3 from tblname where c1='abc' and c2=3

Select c1, c3 from tblname where c1='b'

Select c1, c2 from tblname where c2=10

应该如何建立索引呢？这取决于各语句的比例。如果大部分语句总是根据c1和c2查询，那么一个组合索引（c1＋c2）或者一个覆盖索引是非常有用的，然后多加一个单独对c3创建的索引。反之，如果第一个语句运行次数非常少，大部分语句是后面两种，那么当然要对c1和c2分别建立索引。你也许会问，对第一种语句，分别对c1和c2建立索引可以吗？可以。对某些语句SQL Server 可能会分别使用两个索引（即索引交叉技术）查询数据然后取其交集得到结果。但有时候SQL Server 未必会使用你建立的全部的单字段索引。所以如果对单字段进行索引，建议使用set statistics profile来验证索引确实被充分使用。logical reads越少的索引越好。

3．覆盖索引

对于script 4中的select语句，有没有更好的索引呢？有的。那就是使用覆盖索引（covered index）。覆盖索引能够使得语句不需要访问表仅仅访问索引就能够得到所有需要的数据。因为聚集索引叶子节点就是数据所以无所谓覆盖与否，所以覆盖索引主要是针对非聚集索引而言。不知大家注意到没有，我们前面讨论的执行计划中除了index seek外，还有一个Bookmark Lookup关键字。Bookmark Lookup表示语句在访问索引后还需要对表进行额外的Bookmark Lookup操作才能得到数据。也就是说为得到一行数据起码有两次IO，一次访问索引，一次访问基本表。如果语句返回的行数很多，那么Bookmark Lookup操作的开销是很大的。覆盖索引能够避免昂贵的Bookmark Lookup操作，减少IO的次数，提高语句的性能。

覆盖索引需要包含select子句和WHERE子句中出现的所有字段。Where语句中的字段在前面，select中的在后面。就script 5中的select语句而言，覆盖索引如下：

create nonclustered index idx_covered on tbl1(入学时间,年龄,学生姓名)

然后再运行script 4，输出结果如下：

Table 'tbl1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Rows Executes StmtText

------------------------------------------------------------------------------------------------------

6 1 SELECT [学生姓名]=[学生姓名],[入学时间]=[入学时间] FROM [tbl1] WHERE [入学时间]>=@1 AND [入学时间]<@2

6 1 |--Index Seek(OBJECT:([tempdb].[dbo].[tbl1].[idx_covered]), SEEK:(([tbl1].[入学时间], [tbl1].[年龄])

比较一下上面的logical reads，是大大减少了。Bookmark Lookup操作也消失了。所以创建覆盖索引是减少logical reads提升语句性能的非常有用的优化技巧。

实际上索引的创建原则是比较复杂的。有时候你无法在索引中包含了Where子句中所有的字段。在考虑索引是否应该包含一个字段时，应考虑该字段在语句中的作用。比如说如果经常以某个字段作为where条件作精确匹配返回很少的行，那么就绝对值得为这个字段建立索引。再比如说，对那些非常唯一的字段如主键和外键，经常出现在group by，order by中的字段等等都值得创建索引。因篇幅有限，这里不再进行展开了。SQL Server的联机手册中有很好的相关内容，请读者自行参考。