Sql Server查询性能优化之索引篇【推荐】
这篇是索引系列中比较完整的,经过整理而来的
一 索引基础知识
索引概述
1、概念
可以把索引理解为一种特殊的目录。就好比《新华字典》为了加快查找的速度,提供了几套目录,分别按拼音、偏旁部首、难检字等排序,这样我们就可以方便地找到需要的字。与书中的索引一样,数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键,以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询,可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性,从而确保表数据的数据完整性。
2. 分类
SQL SERVER提供了两种索引:聚集索引(Clustered Index)和非聚集索引(Nonclustered Index)。3. 认识索引的二叉树(索引B树)及级数
SQL Server中所有的索引都是平衡二叉树结构,平衡树的意思是所有叶子节点到根节点的距离都相同,SQL Server进行索引查找时总是从索引的根节点开始,并从根跳到下一级的相应页,并继续从一个级别跳到下一个级别,直到达把可以查找键的叶子页。所有叶级节点到底跟的距离都是相同的,这意味着一次查找操作在叶读取上的成本正好是页的级数。索引级数大多为2级到4级,2级索引大约包含几千行,3级索引大约4 000 000行,4级索引能容纳约4 000 000 000 行,这点上聚集索引和非聚集索引上是一样的。一般来说对于小表来说索引通常只有2级,对于大表通常包含3级或4级。索引是按照B树结构组织的。如下图。页是SQL Server存储数据的基本单位,大小为8KB。 请一定要记住,页是SQL Server进行数据读写的最小I/O单位,而不是行。SQL Server中一个页大小为8KB,每8个页形成一个区,每页8KB,其中页头占用96个字节,页尾的行指示器占用2个字节,还有几个保留字节,也就是一个页8192个字节,能用了存储数据的实际约8000个字节,8000个字节一般可以存储很多行数据。即便SQL Server只访问一行数据,它也要把整个页加载到缓存并从缓存读取数据,我们通常所说的开销主要就是I/O开销,这点不少人都没有清醒的认知。
索引级数也就意味着一次索引查找的最小开销,比如我们建立一个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,有兴趣的童鞋可以亲自去试验下。
认识窄索引
很多书籍和文章都写过索引要使用窄索引,窄索引是个什么东东呢,大白话就是把索引建在字节长度比较小的列上,比如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开销也就随之减少,查询效率自然也就高了。
认识书签查找的开销
当使用非聚集索引时,若查询条件没有实现索引覆盖就会形成书签查找,那么一次书签查找的开销是多少呢?答案是不一定,一般为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<=2
--这个聚集索引查找,返回记录3条,逻辑读6次
SELECT * FROM dbo.Users WHERE UserID IN(1,2)
--这个聚集索引查找,返回记录0条,逻辑读6次
SELECT * FROM dbo.Users WHERE UserID IN(10,20,22)
(7 行受影响)
表 'Users'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(3 行受影响)
表 'Users'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(3 行受影响)
表 'Users'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(0 行受影响)
表 'Users'。扫描计数 3,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
我们再来看下非聚集索引的随机读,当然我这里为了演示特意使用了索引提示,实际应用中没事千万别加索引提示,当使用非聚集索引时查询优化器发现使用索引后效率更低时会放弃索引转为使用表扫描,我们这个例子中若去掉索引提示的话使用表扫描仅需要2次逻辑读就可以完成查询,这里仅仅是为了演示
--非聚集索引查找,返回记录2,逻辑读2
SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName LIKE 'J%'
--非聚集索引查找,返回记录2,逻辑读4
SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob','Jack')
--非聚集索引查找+书签查找,返回记录2,逻辑读6
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName LIKE 'J%'
--非聚集索引查找+书签查找,返回记录0,逻辑读2(索引查找2*1+书签查找2*0)
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Rabbit')
--非聚集索引查找+书签查找,返回记录1,逻辑读4(索引查找2*1+书签查找2*1)
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob')
--非聚集索引查找+书签查找,返回记录2,逻辑读8(索引查找2*2+书签查找2*2)
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob','Jack')
(2 行受影响)
表 'Users'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(2 行受影响)
表 'Users'。扫描计数 2,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(2 行受影响)
表 'Users'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(0 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Users'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(1 行受影响)
表 'Users'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
(2 行受影响)
表 'Users'。扫描计数 2,逻辑读取 8 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
总结:
认识书签索引的查找开销、书签查找的开销、随机读的影响对我们具体创建索引和编写SQL有着积极的影响,毕竟对查询的的认识更加清楚了,我们在写索引和SQL时候才有更有针对性,最起码我们又知道了一个尽量不适用where in的理由,为什么要尽量规避书签查找,聚集索引查找不见得就一定高效,顺便留个问题:聚集索引扫描和非聚集索引扫描哪个有着更高的效率,什么情况下会发生非聚集索引扫描?
数据库索引分为聚集索引和非聚集索引,聚集索引就是物理索引,也就是数据的物理的存储顺序,聚集索引的叶子节点就是数据行本身;非聚集索引是逻辑索引,也可以简单的认为是对聚集索引建立的索引,一般来说聚集索引的键就是非聚集索引的叶子节点(在不使用include时)。
堆的物理结构
堆上的索引
堆上的非聚集索引
create table person1 (UserID int,pwd char(20),OtherInfo char(360),modifydate datetime) declare @i int set @i=0 while @i<80000 begin insert into person1 select cast(floor(rand()*100000) as int), cast(floor(rand()*100000) as varchar(20)), cast(floor(rand()*100000) as char(360)), GETDATE() set @i=@i+1 end |
CREATE NONCLUSTERED INDEX IX_person1_UserID ON person1 (UserID) |
DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES |
DBCC SHOWCONTIG 正在扫描 'person1' 表... 表: 'person1' (245575913);索引 ID: 0,数据库 ID: 8 已执行 TABLE 级别的扫描。 - 扫描页数................................: 4000 - 扫描区数..............................: 502 - 区切换次数..............................: 501 - 每个区的平均页数........................: 8.0 - 扫描密度 [最佳计数:实际计数].......: 99.60% [500:502] - 区扫描碎片 ..................: 1.79% - 每页的平均可用字节数.....................: 76.0 - 平均页密度(满).....................: 99.06% DBCC SHOWCONTIG 正在扫描 'person1' 表... 表: 'person1' (245575913);索引 ID: 2,数据库 ID: 8 已执行 LEAF 级别的扫描。 - 扫描页数................................: 179 - 扫描区数..............................: 23 - 区切换次数..............................: 22 - 每个区的平均页数........................: 7.8 - 扫描密度 [最佳计数:实际计数].......: 100.00% [23:23] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 4.35% - 每页的平均可用字节数.....................: 51.3 - 平均页密度(满).....................: 99.37% DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 |
SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),2,NULL,'DETAILED') |
SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen',max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),0,NULL,'DETAILED') |
堆上的(唯一、非空值)非聚集索引
create table person2 (UserID int not null,pwd char(20),OtherInfo char(360),modifydate datetime) declare @i int set @i=0 while @i<80000 begin insert into person2 select @i, cast(floor(rand()*100000) as varchar(20)), cast(floor(rand()*100000) as char(360)), GETDATE() set @i=@i+1 end |
CREATE UNIQUE NONCLUSTERED INDEX IX_person2_UserID ON person2 (UserID) |
SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person2'),NULL,NULL,'DETAILED') |
删除堆中的数据
delete person2 |
DBCC SHOWCONTIG ('person2') WITH ALL_INDEXES |
DBCC SHOWCONTIG 正在扫描 'person2' 表... 表: 'person2' (261575970);索引 ID: 0,数据库 ID: 8 已执行 TABLE 级别的扫描。 - 扫描页数................................: 296 - 扫描区数..............................: 39 - 区切换次数..............................: 38 - 每个区的平均页数........................: 7.6 - 扫描密度 [最佳计数:实际计数].......: 94.87% [37:39] - 区扫描碎片 ..................: 7.69% - 每页的平均可用字节数.....................: 8056.0 - 平均页密度(满).....................: 0.47% DBCC SHOWCONTIG 正在扫描 'person2' 表... 表: 'person2' (261575970);索引 ID: 2,数据库 ID: 8 已执行 LEAF 级别的扫描。 - 扫描页数................................: 1 - 扫描区数..............................: 1 - 区切换次数..............................: 0 - 每个区的平均页数........................: 1.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [1:1] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 0.00% - 每页的平均可用字节数.....................: 8078.0 - 平均页密度(满).....................: 0.20% DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 |
DBCC SHRINKFILE (N'db01' , 0, TRUNCATEONLY) |
聚集索引和非聚集索引
关于索引的选择
对于索引类型来说没什么好选的,一般来说聚集索引是必须的(有特殊需要的另说),非聚集索引看实际需要灵活建立。因此对于索引来说主要是决定在那些列上建立索引,尤其是对于聚集索引这点非常重要。
聚集索引
一、聚集索引的概念
聚集索引作为最重要的索引往往被我们所忽略,而其最大的优势就是大范围数据查询有着较高的效率,因此聚集索引列的选择往往对数据库性能有着灰常大的影响。为了尽量发挥聚集索引在大范围数据查找上的优势,推荐按以下顺序选择聚集索引列。
聚集索引字段选择优先级:时间字段>>会进行大范围查询的列>>具有唯一值的有实际意义的字段>>自增列ID
1.时间字段:若表里面有时间列,并且时间是按照数据插入顺序增长时(时间无需唯一即可有重复值,哪怕是大范围重复),建议采用时间列作为聚集索引的第一选择。理由:聚集索引有一个巨大的优势就是进行大范围数据查找,而且这个优势会随着数据量的增加而越来越明显,一般来说我们需要进行大数据量范围查询时都会用时间列围作为筛选条件,由于聚集索引不存在书签查找而且可以进行连续扫描,因此查询速度会非常快。时间列数据最好是顺序插入的这样可以尽量减少磁盘碎片,是数据存储相对集中,便于连续数据读取。
2.会进行大范围查询的列:若表里面没有时间字段或者时间字段不适合做聚集索引,可以选择那些在建表时就明确知道会经常进行大范围数据筛选的列,而且最好是选择性较低的列(即有较多重复值的列,性别这种列不算啦),如有必要可以使用组合索引。理由:聚集索引在数据查询的优势主要在于范围数据查找,把聚集索引弄成唯一的把这个大好优势给白白浪费了。
3.具有唯一值的有实际意义的字段:若找不到适合条件1、2的列,那还是乖乖的把聚集索引列建立在唯一列上吧,最好找那种有实际意义的具有唯一性的列,比如订单表可以用订单号作聚集索引,订单明细表使用订单号和产品编号做联合聚集索引。理由:找不到合适的时间字段和较低选择性字段的话,把主键建成聚集索引是我们大多情况下的选择。
这里建议把唯一性的聚集索引顺便建成主键,和编码时方法、变量命名一样,推荐列名自解释,即看到列名就知道它就是主键,省得你再去猜,比如订单表你来个自增ID列做主键,再建一个OrderCode列做订单号,用这个表时你得怀疑这个OrderCode是不是唯一滴呢,有木有建立唯一约束呢,同理在订单明细表来个自增列ID也会产生如此疑问,产生疑问还是小事,若是你忘记了在应该唯一的列上建立约束,没准哪天程序控制不好给你个巨大的惊喜。
4.自增列ID:前面3中条件都找不到合适的列了还是使用我们的神器自增列ID吧,自增列ID也是我们使用最多的主键(顺便也就成聚集索引了),而且能较好满足我们大多数需求。自增ID列堪称无所不能,int类型只占用4个字节完全满足窄索引要求,绝对的顺序存储可以有效降低索引碎片,完全符合我们的见表习惯,有用没用来个自增ID列做主键总是没错滴。
这里考虑聚集索引的键列主要为查询考虑,有些观点认为应该总是把聚集索引建立唯一列上,这里不敢苟同,诚然有些特殊情况下确实需要这么做,但大说情况下还是建立在选择性较低的列、时间列上比较好,这样才能发挥聚集索引在范围数据查找方面的巨大优势。关于聚集索引在列上重复数据SQL Server需要额外的建立唯一标示用以定位造成查询时的额外开销非常小,小到与其带来范围查找的优势而言完全可以忽略。
当然了在选择列时要尽量使用窄索引,也只是尽量而已,主要还是看付出的代价还获得的收益,若有足够的收益啥玩意都可以滴。记住我们滴目标是利用聚集索引提高大范围查询效率。
(非唯一)聚集索引
DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES |
DBCC SHOWCONTIG 正在扫描 'person1' 表... 表: 'person1' (245575913);索引 ID: 1,数据库 ID: 8 已执行 TABLE 级别的扫描。 - 扫描页数................................: 4009 - 扫描区数..............................: 502 - 区切换次数..............................: 501 - 每个区的平均页数........................: 8.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [502:502] - 逻辑扫描碎片 ..................: 0.37% - 区扫描碎片 ..................: 0.80% - 每页的平均可用字节数.....................: 44.2 - 平均页密度(满).....................: 99.45% DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 |
SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),1,NULL,'DETAILED') |
(唯一)聚集索引
create table person3 (UserID int not null,pwd char(20),OtherInfo char(360),modifydate datetime) declare @i int set @i=0 while @i<80000 begin insert into person3 select @i,cast(floor(rand()*100000) as varchar(10)), cast(floor(rand()*100000) as char(50)), GETDATE() set @i=@i+1 end |
CREATE UNIQUE CLUSTERED INDEX IX_person3_UserID ON person3 (UserID) |
SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person3'),1,NULL,'DETAILED') |
聚集索引与约束
ALTER TABLE person ADD CONSTRAINT PK_person_UserID PRIMARY KEY CLUSTERED (UserID) |
非聚集索引
与聚集索引不同,非聚集索引可以建立多个,这也给我们带来了很大的灵活,毕竟聚集索引就那么一个不可能靠它满足所有需求,更多的我们得依赖非聚集索引。记住非聚集索引不是大白菜,你想键多少就建多少,建立索引是有代价的,任何涉及到索引列的数据修改都会导致索引的修改,索引越多数据的曾、删、改的额外代价也就越大。对于非聚集索引来说,我们的目标是用尽可能少的索引覆盖尽可能多的查询。
SQL Server缺省情况下建立的索引是非聚簇索引,由于非聚簇索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时,要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外,还要考虑这些问题:
1、索引需要使用多少空间。
2、合适的列是否稳定。
3、索引键是如何选择的,扫描效果是否更佳。
4、是否有许多重复值。
对更新频繁的表来说,表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言,指向该数据的每个非聚簇索引的页级行也必须更新,有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销,另外,删除进程还必须把数据移到页面上部,以保证数据的连续性。所以,建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:
1、某列常用于集合函数(如Sum,....)。
2、某列常用于join,order by,group by。
3、查寻出的数据不超过表中数据量的20%。
非聚集索引的列选择顺序(组合索引):经常被使用为查询条件列>>具有较高选择性的列(选择性越高越好,唯一最好)>>经常排序的列
1.经常被使用为查询条件列:我们的查询千变万化,建立索引时要首先考虑有哪些列被经常性的用于各种查询,把使用频率较高的列作为组合索引的第一列(先导列),若一个查询中没有用到组合索引中的先导列,多数情况下这个索引就不会被使用,因此为了尽可能多的复用组合索引把使用较多的查询列作为组合索引的第一列吧。(关于这点对于聚集索引的组合索引同样适用)
2.具有较高选择性的列:这点很简单尽量使用高选择性列作为先导列,如果可以通过第一个条件过滤(随便什么判定逻辑=、>、<、like),只要能大幅减少数据范围,就把它作为先导列。
3.条件1、2、3都确定不了时那就用经常被排序的列吧,我们的很多操作都需要先进行排序才可以进行进一步查询,比如group by,like等操作都是要先进行排序操作才可以完成下一步查询。
补充一点:可以把经常被返回的列放到索引的include里面去,在不增加索引键大小的情况下尽可能覆盖尽可能多的列,这样当遇到某些查询,没有用到组合索引的先导列,但又感觉不值得为其建立索引时,若此查询用到得字段被组合索引实现了索引覆盖,可以进行非聚集索引扫描完成查询(当非聚集索引实现了索引覆盖时,进行非聚集索引扫描有着比聚集索引扫描更好的效率)。
下面我们用一些示例来简单说明下
CREATE TABLE Orders
(
ID INT IDENTITY PRIMARY KEY,--自增列ID做主键,这样创建默认就成了聚集索引
OrderCode BIGINT NOT NULL,--订单号
Price DECIMAL(18,2) NOT NULL,--订单金额
UserID INT NOT NULL,--用户ID
[Status] INT NOT NULL,--订单状态
PostTime DATETIME NOT NULL,--下单时间
Moblie CHAR(11) NOT NULL,--用户手机号
[Address] NVARCHAR(200) NOT NULL--收获地址
)
创建一个订单表,按照我们的习惯有用没用来个自增列ID做主键,随随便便也就建立了聚集索引,现在我们来看一下,对于订单表来我们一般都会一组规则生成订单号,而不是简单的使用自增ID,因此我们创建了OrderCode用作订单号,当然了订单号必须是唯一的,因此需要创建唯一约束,过了些日子有其它人用到订单表或你自己用这个表,难免就会有些疑惑,OrderCode需要唯一,在这个表里到底是不是唯一的呢,于是乎你首先查看OrderCode上面是否建立了唯一约束,然后知道OrderCode就是唯一的,这也没啥,但是来个人都要查一遍,过段时间忘了还得再确认一次,很是麻烦,再看看我们那个主键ID,他神马都没干,就在那里呆着,现在拿掉它,于是表变为
CREATE TABLE Orders
(
OrderCode BIGINT NOT NULL PRIMARY KEY,--订单号
Price DECIMAL(18,2) NOT NULL,--订单金额
UserID INT NOT NULL,--用户ID
[Status] INT NOT NULL,--订单状态
PostTime DATETIME NOT NULL,--下单时间
Moblie CHAR(11) NOT NULL,--用户手机号
[Address] NVARCHAR(200) NOT NULL--收获地址
)
现在不管谁、什么时候看到这个表基本上不会怀疑OrderCode是否唯一了,一个不起眼的小改进,带来了很大的便利,所以主键自解释很有必要的
我们看一下以下几个可能经常用到的查询
--查询1:指定用户特定时间内的所有订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查询2:指定用户的单个订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND OrderCode=22222222222
--查询3:指定用户特定时间内特定状态的订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND Status=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查询4:指定时间内所有的订单
SELECT * FROM dbo.Orders WHERE PostTime BETWEEN '2012-6-1' AND '2012-6-30' AND Status=1
为了最优查询速度有可能会创建以下索引
索引1:
CREATE INDEX IX_UserIDPostTime ON dbo.Orders(UserID,PostTime)
索引2:
CREATE INDEX IX_UserIDOrderCode ON dbo.Orders(UserID,OrderCode)
索引3:
CREATE INDEX IX_UserIDStatusPostTime ON dbo.Orders(UserID, Status,PostTime)
索引4:
CREATE INDEX IX_PostTimeStatus ON dbo.Orders(PostTime,Status)
最悲观的情况下上面4个索引可能同时存在,为每一个查询建立对应的索引固然可行,但代价未免太大,别忘了索引不是大白菜。因此我们应尽可能的用少的索引覆盖多的查询。来看下上面的索引,如果只创建了索引1,那么只有查询1、3能从索引1受益,查询4没用用到索引1的先导列故不会用到索引1,查询2由于聚集索引存在根本不需要额外的非聚集索引。而索引2由于聚集索引的存在更是完全没必要存在,因此首先干掉索引2。再看索引3,索引3可以覆盖查询1和查询3、查询4,但由于索引列顺序问题使其在应对查询4时基本无效,对查询1虽然有效但效果不尽如人意,我们对索引3做下简单调整,把PostTime列和Status列顺序互换,修改后索引3对原查询3基本没影响,而且对查询1的效率提升也达到最大化
修改后的索引3:
CREATE INDEX IX_UserIDPostTimeStatus ON dbo.Orders(UserID,PostTime,Status)
现在索引3可以很好的完成查询1和查询3,因此索引1现在可以删除掉,现在只剩索引3和索引4了,我们可以看到修改后的索引3由于先导列问题依然无法用于查询4,为了使索引3用于查询4我们再次修改索引3,把PostTime放到索引的第一列,其它列保持顺序不变
再次修改后的索引3:
CREATE INDEX IX_PostTimeUserIDStatus ON dbo.Orders(PostTime,UserID,Status)
可以看到现在索引3也可以有效的用于查询4了,但是由于先导列原因若将Status列和UserID列换货,固然可以提高查询4效率但是会影响查询1,我们考虑到Status列一般也就几种状态,多了也就几十种,相对于UserID来说选择性低高,因此还是把选择性较高的UserID列放在前面,最大化查询1和查询3查询效率。再来看最后一个索引4,索引4和对查询1和查询4起效果,由于查询1已有索引3可用,故忽略对查询1的作用,现在只剩下对查询4的起作用,我们看查询4,索引3和索引4都对其产生效果,毫无疑问索引4对查询4效果更大一些,但考虑到Status列的低选择性和多维护一个索引的代价,索引3已能较好的完成查询4,所以删除索引4。
这样一来针对Orders表的4个查询,我们经过对原来4个索引的优化调整后只保留了修改后的索引3,索引从4个变成一个,而查询效率方面却没有受较大的影响,达到了用尽可能少的索引完成尽可能多的查询的目的。
在上面的演示中我们使用了OrderCode做为聚集索引,通过对非聚集索引的调整较好的完成了查询,在大说数情况下这样就可以ok了,现在我们考虑下若Orders表数据量较大,执行我们的查询4若返回结果达到几万、几十万甚至更多的时候,很可能会导致索引失效从而发生表扫描,这时除非我们队查询4使用的索引实现索引覆盖,不过这基本上不大现实。那如何解决这个问题呢?这时时间列上的聚集索引就开始显示威力了,修改我们的Orders表将聚集索引建立到PostTime列上
--删除原来的聚集索引主键 PK_Orders
ALTER TABLE dbo.Orders DROP CONSTRAINT PK_Orders
--创建非聚集索引主键 PK_Orders
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(OrderCode)
--在时间列PostTime上创建聚集索引
CREATE CLUSTERED INDEX IX_PostTime ON dbo.Orders(PostTime)
修改后我们担心的书签查找问题彻底消失了,因为通常导致索引失效的原因都是过多的书签查找,发生大量书签查找时基本上和范围查询脱不开关系,大范围数据查询又肯定会用到时间列,所以推荐时间列做聚集索引。至于说修改了聚集索引后更新,订单查询效率会不会下降,对于这点基本上可以忽略,进行订单查询时一般数据量都会较小,那点书签查找开销完全可以忽略掉,比如我们的查询2。
这时我们可能发现我们上面修改的索引3有个尴尬的现状,索引3的先导列PostTime现在成了聚集索引,那么索引3对查询1、查询3、查询4还有木有用,索引3还有木有存在的必要,关于这点一般来说呢非聚集索引最好不要用聚集索引做先导列,很可能建了等于白建这里只是个建议,那么若删除了索引3,查询1、查询3、查询4效率会如何,查询4效率肯定是会提升的、查询1、查询3需要实际测试,当然若可以的话把聚集索引建成联合的,把UserID放进去就不会有此问题了
--在时间列PostTime和UserID列上创建聚集索引
CREATE CLUSTERED INDEX IX_PostTimeUserID ON dbo.Orders(PostTime,UserID)
当然了这种修改还得小心进行,根据实际需求灵活修改,理想的情况下是建立专门的只读数据库复制,在只读库上建立最适合范围查找的聚集索引,在主库上建立最有利于增、删、改的聚集索引,对实时性要求不高的查询全部转移到只读库上执行,相对而言需要大范围数据筛选的查询都不需要多好的实时性,尽可到只读库执行,而主库呢肯定是主要执行实时性要求高的小数据量查询。
总结:
聚集索引的优势在于大范围数据查询效率,因此需要将聚集索引建立在时间列、选择性相对较低并且经常会用于范围查询的列(选择性过低的如性别列肯定不行,过低的选择性列索引建了等于白建,比如你在性别列上集索引以为通过性别列起码一下过滤掉一半数据,范围大大减小你就大错特错了,这点选择性通常查询优化器会直接忽略掉,还不如个表扫描来的快),充分发挥聚集索引大范围数据查询优势。非聚集索引要尽量使用选择性较高的列以尽可能减少返回的数据量,利用组合索引提高索引的复用率,不要建过多的无用索引,如果发现某个表建了很多的非聚集索引,不妨把那些索引、查询摘出来分析合并下,减少没用索引的数量,以提高整体性能。
索引建立还需根据实际需要进行选择,本文所述观点在能够适用于大多数情况,但建立好的索引不是一朝一夕能够做到的,理论上成立的事实际应用中往往会事与愿违,索引的有效性还要依靠数据库统计信息等综合考虑,故每当建立索引后一定要查看下查询计划,查看下IO开销,看看查询优化器是否按照我们预期的方式使用了索引。
聚集索引上的非聚集索引
索引结构
实验
CREATE INDEX IX_person1_UserIDModifyDate ON person1 (UserID,ModifyDate) |
DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES |
DBCC SHOWCONTIG 正在扫描 'person1' 表... 表: 'person1' (389576426);索引 ID: 1,数据库 ID: 8 已执行 TABLE 级别的扫描。 - 扫描页数................................: 4000 - 扫描区数..............................: 500 - 区切换次数..............................: 499 - 每个区的平均页数........................: 8.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [500:500] - 逻辑扫描碎片 ..................: 0.03% - 区扫描碎片 ..................: 2.20% - 每页的平均可用字节数.....................: 76.0 - 平均页密度(满).....................: 99.06% DBCC SHOWCONTIG 正在扫描 'person1' 表... 已执行 LEAF 级别的扫描。 - 扫描页数................................: 179 - 扫描区数..............................: 23 - 区切换次数..............................: 22 - 每个区的平均页数........................: 7.8 - 扫描密度 [最佳计数:实际计数].......: 100.00% [23:23] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 4.35% - 每页的平均可用字节数.....................: 51.3 - 平均页密度(满).....................: 99.37% DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 |
SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),2,NULL,'DETAILED') |
比较三类索引占用的页数
覆盖索引(covering indexes)的使用
覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引,这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。
但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键,则覆盖索引的增加反而会降低性能。
索引碎片
一、碎片的产生
二、DBCC SHOWCONTIG 检查碎片程度
三. sys.dm_db_index_physical_stats 判断碎片程度
五、索引的重组与重建
ALTER INDEX IX_person2_UserID ON person2 REORGANIZE |
ALTER INDEX IX_person2_UserID ON person2 REBUILD WITH (ONLINE=OFF) |
六、填充因子
ALTER INDEX IX_person2_UserID ON person2 REBUILD WITH (FILLFACTOR = 60) |
索引设计指南
一、从数据库的角度进行设计
CREATE TABLE Table2 ( MyId int IDENTITY, MyName varchar(10) ) insert into Table2 (MyName) Values ('noname') select * from Table2 dbcc checkident('Table2', NORESEED) |
SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable |
二、从查询的角度
筛选索引
一、概念
USE AdventureWorks2008R2; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'FIProductAccessories' AND object_id = OBJECT_ID ('Production.Product')) DROP INDEX FIProductAccessories ON Production.Product; GO CREATE NONCLUSTERED INDEX FIProductAccessories ON Production.Product (ProductSubcategoryID, ListPrice) Include (Name) WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36; GO SELECT Name, ProductSubcategoryID, ListPrice FROM Production.Product WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ; GO |
索引选择
WHERE au_fname = 'Johnson'
本文转自
http://www.cnblogs.com/lzrabbit/archive/2012/06/11/2517963.html ;
http://www.cnblogs.com/lzrabbit/archive/2012/07/03/2549558.html
http://jimshu.blog.51cto.com/3171847/1254965
--- end ---