Sql Server查询性能优化之创建合理的索引(上篇)

zsyzhou

　　本文主要作为优化查询性能的一些知识储备，感觉知识点有些散，不知道起啥名字好，独立成文又没有达到把每个点都说透彻那样的高度，且就当做创建合理索引的一个楔子把。本文对实际应用没有太大的指导意义，但可以加深我们对SQL Server理解，夯实我们的基本功，就像小说里面的武功一样，没有足够的内功基础，给你再好的秘籍你也成不了武林高手。

序言
　　写这篇文章时表示鸭梨很大，主要是对SQL Server的认识很有限，远远不足把这个话题说清楚，不过还是鼓起勇气写出来，也算作自己对索引认识的一个总结。索引这潭水太深了，应用场景不同，所建立的索引在有些情况下运行良好，有些情况下可能完全无效。而对于索引理解、认识层次不同，怎样建才比较合理的也是众说分云，用万金油的说法就是没有最合理的只有最合适的。一般来说最懂数据库的当属DBA，不过DBA却不懂业务，一般除了一些核心业务模块表建立及索引维护有DBA完成外，大多数情况下索引、SQL维护都是有开发人员完成的，因此我们通常认为的索引建立、优化都是有开发人员完成的(并不是我无视DBA，事实就是这个情况)，可能有人疑惑了，DBA不维护索引、不写SQL那他们都干嘛吃去了，这个俺还真不知道，不过DBA很忙、很累我是知道的，估计、可能、大概会做以下事情：数据库并发、分发复制、性能监控、数据迁移、备份、日常维护、索引、SQL调优等等，DBA童鞋别喷我，我是真的不知道，DBA童鞋们可以留言告诉我下哈。　
　　下面简单说一些暂时想到的对使用索引影响较大的几个注意点　

页是SQL Server存储数据的基本单位，大小为8KB。
　　请一定要记住，页是SQL Server进行数据读写的最小I/O单位，而不是行。SQL Server中一个页大小为8KB，每8个页形成一个区，每页8KB，其中页头占用96个字节，页尾的行指示器占用2个字节，还有几个保留字节，也就是一个页8192个字节，能用了存储数据的实际约8000个字节，8000个字节一般可以存储很多行数据。即便SQL Server只访问一行数据，它也要把整个页加载到缓存并从缓存读取数据，我们通常所说的开销主要就是I/O开销，这点不少人都没有清醒的认知。

认识窄索引
　　很多书籍和文章都写过索引要使用窄索引，窄索引是个什么东东呢，大白话就是把索引建在字节长度比较小的列上，比如int占用4个字节,bigint占用8个字节,char(20)占用20个字节nchar(20)最占用40个字节，那么int 相对于bigint来说就是窄了(占用字节数更少)，bigint比char(20)也要窄,char(20)和nchar(20)相比要窄(nchar(20)每个字符占用2个字节)。
　　明白了啥是窄索引我们来说下为什么要使用窄索引，我们知道数据存储和读取的最小单位是页，一个页8K大小，当使用比较窄的列做索引列时，每个页能存储的数据就更多，以int和bigint为例，一个8K的页大约能存储8*1024/4(int 4个字节)=2048(实际值要比这个数字小)条数据，使用bigint大约能存储8*1024/8(bigint为8个字节)=1024(实际值要比这个数字小)条数据，也就是说索引列的长度也小，每个页能存储的数据也就越多，反过来说就是存储索引所需要的页数也就越少，页数少了进行索引查找时需要检索的页自然也就少了，检索页数少了IO开销也就随之减少，查询效率自然也就高了。

认识索引的二叉树级数
　　SQL Server中所有的索引都是平衡二叉树结构，平衡树的意思是所有叶子节点到根节点的距离都相同，SQL Server进行索引查找时总是从索引的根节点开始，并从根跳到下一级的相应页，并继续从一个级别跳到下一个级别，直到达把可以查找键的叶子页。所有叶级节点到底跟的距离都是相同的，这意味着一次查找操作在叶读取上的成本正好是页的级数。索引级数大多为2级到4级，2级索引大约包含几千行，3级索引大约4 000 000行，4级索引能容纳约4 000 000 000 行，这点上聚集索引和非聚集索引上是一样的。一般来说对于小表来说索引通常只有2级，对于大表通常包含3级或4级。
　　索引级数也就意味着一次索引查找的最小开销，比如我们建立一个User表



CREATE     TABLE Users
(
UserID INT IDENTITY,
UserName nvarchar(50),
　　 Age INT,
　　 Gender BIT,
CreateTime DateTime
)
--在UserID列上创建聚集索引 IX_UserID
CREATE UNIQUE CLUSTERED INDEX IX_UserID ON dbo.Users(UserID)
--插入示例数据
insert into Users(UserName,Age,Gender,CreateTime)
select N'Bob',20,1,'2012-5-1'
union all
select N'Jack',23,0,'2012-5-2'
union all
select N'Robert',28,1,'2012-5-3'
union all
select N'Janet',40,0,'2012-5-9'
union all
select N'Michael',22,1,'2012-5-2'
union all
select N'Laura',16,1,'2012-5-1'
union all
select N'Anne',36,1,'2012-5-7'
　　
　　我们执行查询



SELECT * FROM dbo.Users WHERE UserID=1
　　可以看到输出信息为



(1 行受影响)
表 'Users'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
　　当表数据量增多至几千条时，执行上述查询逻辑读依然为2，当数据量到达百万级别时逻辑读会变成3，当到达千万、亿级别时，逻辑读就会变成4，有兴趣的童鞋可以亲自去试验下。

认识书签查找的开销
　　当使用非聚集索引时，若查询条件没有实现索引覆盖就会形成书签查找，那么一次书签查找的开销是多少呢？答案是不一定，一般为1到4次逻辑读，对于堆表(无聚集索引的表)来说，一次书签查找只需要一次逻辑读，对于非堆表(有聚集索引的表)来说一次书签查找的逻辑读次数为聚集索引的级数，在索引结构我们简单说了下大多数聚集索引的级数为2-4级，也就是说对于非堆表来说一次逻辑读的开销为2-4次逻辑读，下面我们做具体测试
　　创建非聚集索引 IX_UserName



CREATE INDEX IX_UserName ON dbo.Users(UserName)
　　执行查询



SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob')
　　由于我们的查询实现了索引覆盖，没有书签查找，获取一条数据需要2次逻辑读




(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
　　修改查询，返回列中增加CreateTime，这样就无法实现索引覆盖



SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob')
　　可以看到这时发生了书签查找，同样返回一条数据，逻辑读达到了4次(索引查找2次，书签查找2次），由于我们的表Users为非堆表，故一次书签查找需要2次(聚集索引IS_UserID的级数为2)逻辑读




(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
　　然后我们继续测试堆表的书签查找开销，删除表Users的聚集索引IX_UserID，使其变为堆表



DROP INDEX IX_UserID ON dbo.Users
　　再次执行我们的查询，可以看到逻辑读变成了3次（索引查找2次，书签查找1次），想想为什么堆表的书签查找次数少呢？



SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob')




(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
使用索引时的随机读
　　嗯，关于随机读我现在也有些迷糊，只是大概的知道发生随即读时即便需要的数据就存在同一页上，也会发生多次读取，而不是一次拿到整页数据后进行筛选。当进行where in查找或发生书签查找时，一定会使用随机读
　　首先我们看看聚集索引的随即读表现



--创建聚集索引IX_UserID
CREATE UNIQUE CLUSTERED INDEX IX_UserID ON dbo.Users(UserID)
　　执行如下查询，可以发现在聚集索引上面使用where in时不管有没有找到记录都会进行聚集索引查找，而且查找次数固定为where in里面的条件数*索引级数，不知道为什么明明索引扫描有着更高的效率，查询优化器还是选择聚集索引查找，有知道的朋友还请告知下哈



--这个聚集索引扫描，返回记录7条，逻辑读2次
SELECT * FROM dbo.Users
--这个聚集索引查找，返回记录3条，逻辑读2次
SELECT * FROM dbo.Users WHERE UserID