sqlserver 索引进阶(上)
参考原文:http://www.cnblogs.com/tjy9999/p/4494662.html
2. 非聚集索引
SET STATISTICS io ON SET STATISTICS time ON -- 1. 没有索引(logical reads 568) SELECT FirstName, LastName FROM dbo.Contact WHERE LastName LIKE 'S%' -- 创建非聚集索引 IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Person.Contact') AND name = 'FullName') DROP INDEX Person.Contact.FullName; CREATE NONCLUSTERED INDEX FullName ON Person.Contact( LastName, FirstName ); GO -- 2. 完全覆盖的查询(logical reads 14) SELECT FirstName, LastName FROM dbo.Contact WHERE LastName LIKE 'S%' -- 3. 非完全覆盖的查询(logical reads 568) SQL Server觉得使用索引查找,比直接扫描还要做更多的工作,因此没有使用索引. SELECT * FROM dbo.Contact WHERE LastName LIKE 'S%' -- 4. 非完全覆盖的查询(logical reads 111) 改变where条件,从而减少查询结果的范围,增加使用索引的好处,SQL Server使用索引查找来缩短查询时间. SELECT * FROM dbo.Contacts WHERE LastName LIKE 'Ste%'
3. 聚集索引
非聚集索引是独立的对象,有自己的存储空间,而聚集索引和表是同一个对象。创建一个聚集索引的时候,SQL Server用key对表进行排序,并且在修改数据的时候维护排序。因此当聚集索引的键是订单号,那么同一个订单的信息在表中的顺序是连续的。每张表只能有一个聚集索引,因为表只能按照一个顺序来排列。如果一张表上没有聚集索引,表也被叫做堆,因此表分为两种类型:聚集索引表和堆表。
CREATE CLUSTERED INDEX IX_SalesOrderDetail ON dbo.SalesOrderDetail (SalesOrderID, SalesOrderDetailID) GO -- 1. 堆表查询(logical reads 1495) & 聚集索引表查询(logical reads 3) SELECT * FROM SalesOrderDetailWHERE SalesOrderID = 43671 AND SalesOrderDetailID = 120 -- 2. 堆表查询(logical reads 1495) & 聚集索引表查询(logical reads 1513) -- ProductID列不是聚集索引的键。两种表都进行了表扫描。因为包含了聚集索引,聚集索引表更大,所以扫描了更多的次数。 SELECT * FROM SalesOrderDetail WHERE ProductID = 755
4. 页和区
SQL Server在创建数据库的时候,即指定数据文件的存放位置。SQL Server读取的不是行,读取的单位是一页或者更多页。页是最小的IO单元,每页的大小是8K。一个分区包含8个连续的页。每一行的大小=所有列的大小+行的头部信息。
5. 包含列
- 表中的每一行在索引中总是有一个入口(这条规则有一个意外,在后面的级别中我们会讲到)。这些入口总是用索引键排序。
- 在聚集索引中,索引的入口就是表的实际行。
- 在非聚集索引中,入口和数据行是分开的,索引由索引键列和标签组成,标签是索引键列到表数据行的映射。
那些经常出现在select中的,而不是where子句中的列,最好放在包含列中。索引列不会影响索引入口的排序,只会更新索引的入口,但是不需要移动。
CREATE NONCLUSTERED INDEX FK_ProductID_ ModifiedDate ON Sales.SalesOrderDetail (ProductID, ModifiedDate) INCLUDE (OrderQty, UnitPrice, LineTotal) -- 该查询在包含列的非聚集索引下速度更快 SELECT ProductID , ModifiedDate , SUM(OrderQty) AS 'No of Items' , AVG(UnitPrice) 'Avg Price' , SUM(LineTotal) 'Total Value' FROM Sales.SalesOrderDetail WHERE ProductID = 888 GROUP BY ProductID ,ModifiedDate ;
6. 标签
据库中的每一行,在任何时间,都可以用三个数字来标识:文件号-页号-行号。这三个数字的复合标识叫做rowid,通常叫做RID。因此文件1的77页的12行,RID会显示成1:77:12。
一个堆表的非聚集索引:RID为基础的标签
通常来讲,一个堆表的行是不会移动的,一旦他们被插入一页,他们会保持在这页中。更加精准的说法是:在堆表中的行很少移动,当他们移动的时候,在旧的位置上会留下新的地址。因为堆表的行不会移动,在堆表中RID永久的标识每一行。不仅值是永久的,而且物理位置也是永久的。索引中每一行的标签都是很有效的,直接指向对应的数据行。
聚集索引的非聚集索引:键为基础的标签
聚集索引表的行是可以移动的,在修改数据或者是维护索引的时候可能会分配到另外一页。当聚集索引的一行被移动到新页的时候,它只是被移动,而聚集索引的键值没有改变。因此可以用索引键值作为行的标签。聚集索引的键应该满足三个条件:短小、静态,并且唯一。聚集索引键值的改变,会导致每一个非聚集索引中对应行的入口发生更新操作。因此,如果一张表有n个非聚集索引,一次索引键的更新,会变成n+1次的更新。
非聚集索引的入口由查询键列、包含列、标签组成。标签的值既可以是RID,也可以是聚集索引的键,这依赖于表是堆表还是聚集索引表。为表选择最好的聚集索引需要你依据三条规则,确保索引键是一个好的标签。
7. 过滤的索引
过滤的索引消除了索引中无用的入口,产生的索引更小,更有利于查询。过滤的索引是通过在create index中指定where子句来实现的。
CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate ON Sales.SalesOrderDetail (ProductID,ModifiedDate) INCLUDE (OrderQty,UnitPrice,LineTotal) WHERE SpecialOfferID <>1 -- 当SpecialOfferID=1的行数占到95%时,在SpecialOfferID=1上创建索引没有意义,使用过滤的索引可以产生较小的索引 SELECT ProductID, ModifiedDate, SUM(OrderQty) 'No of Items', AVG(UnitPrice) 'Avg Price', SUM(LineTotal) 'Total Value' FROM Sales.SalesOrderDetail WHERE SpecialOfferID <> 1 GROUP BY ProductID, ModifiedDate
8. 唯一索引
唯一索引比较特别,不仅提高查询的性能,同时也带来数据完整性的好处。在SQL Server中,唯一索引是强制主键和候选键约束的唯一合理的方法。唯一索引不同于其他索引,入口不允许有相同的索引键值。因为索引的每个入口都会映射表中的一行,不允许相同的索引入口,也就是不允许表中存在相同的行。
a. 定义主键约束或者唯一索引约束,SQL Server会自动创建索引。你可以只包含索引,没有约束;但是不能只有约束,没有索引。在定义约束的时候,就会创建一个和约束同名的索引。每张表可以有多个唯一索引。
-- 创建唯一索引 CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Name] ON Production.Product ([Name]); -- 添加主键约束 ALTER TABLE Production.Product ADD CONSTRAINT PK_Product_ProductID PRIMARY KEY CLUSTERED (ProductID);
b. 主键约束和唯一约束有一些不同:
每张表只能包含一个主键约束,可以包含多个唯一约束。
主键约束不允许NULL值,唯一约束允许NULL值。但是,唯一约束视两个NULL为重复值,因此唯一约束列中只能存在一个NULL值。创建一个唯一过滤索引,允许UPCode列中存在多个NULL值。
创建主键约束,顺便会创建聚集索引,以下情况除外:创建唯一约束,顺便会创建非聚集索引,除非创建的时候指定了CLUSTERED关键字,并且表还没有聚集索引
-
- 表中已经包含聚集索引。
- 创建约束的时候指定了NONCLUSTERED关键字。
c. 过滤索引,允许多个NULL值存在
CREATE UNIQUE NONCLUSTERED INDEX AK_UPCode on ProductDemo(UPCode) where UPCode!=null)
d. 多行插入时当IGNORE_DUP_KEY = ON,只有重复的行会插入失败。update,create index和alter index语句会忽略IGNORE_DUP_KEY选项。IGNORE_DUP_KEY选项也可以在添加主键和唯一约束的使用使用。
CREATE UNIQUE NONCLUSTERED INDEX AK_Product_Name ON Production.Product ( [Name] ) WITH ( IGNORE_DUP_KEY = OFF );
9. 读懂执行计划
一个执行计划是SQL Server根据一个查询的一系列指令。SQL Server管理器可以用为文本,图形或者XML格式显示执行计划。
(1) 单表查询优化
观察下面这个查询语句在不同索引情况下的执行计划
SELECT LastName, FirstName, MiddleName, Title FROM Person.Contact WHERE Suffix = 'Jr.' ORDER BY Title
a. 无索引
b. 包含列的非聚集索引
CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact ( Suffix ) INCLUDE ( Title, FirstName, MiddleName, LastName )
再次执行查询,逻辑读从569次降到了3次。在新的非聚集索引中,Suffix是索引键,where stuffix='Jr.'的记录是聚集在一起的,因此会减少获取数据所需要的读取次数。排序操 作,占用了超过75%的消耗,而不是之前看到的5%。因此,原来的计划需要75/5=15倍。
c. 包含排序列的非聚集索引
CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact ( Suffix, Title ) INCLUDE ( FirstName, MiddleName, LastName )
现在,需要的信息会聚集在一起,新的执行计划如下所示。
(2) 表连接的查询优化
SELECT C.LastName, C.FirstName, C.MiddleName, C.Title, H.SalesOrderID, H.OrderDate FROM Person.Contact C JOIN Sales.SalesOrderHeader H ON H.ContactID = C.ContactID WHERE Suffix = 'Jr.' ORDER BY Title
不添加其他索引的执行计划是下面的样子
上面的执行计划告诉我们一些事情:
- 两张表同时被扫描。
- 大部分的工作花费在表扫描上。
- 大部分的数据来自于SalesOrderHeader表。
- 两张表聚集的顺序不是一样的,因此SalesOrderHeader中满足条件的每一行,在Contact表需要额外的工作。这种情况就需要哈希匹配操作。
- 堆排序的需要是微不足道的。
在测试查询计划瓶颈时,可以增加两个表的查询范围,从而查看哪个节点的耗时明显提高。增加Contact行导致匹配和排序操作是这个查询关键。如果我们需要提高性能,我们要首先从这两个操作入手。ContactID是SalesOrderHeader表的外键。通过ContactID外键访问SalesOrderHeader表的数据,在业务需求中很常见。在ContactID上建立索引会有很大的帮助。
CREATE NONCLUSTERED INDEX IX_ContactID ON Sales.SalesOrderHeader (ContactID ) INCLUDE ( OrderDate )
添加索引后的执行计划如下:
因为所有的输入流都按照ContactID排序,没有了分组和哈希匹配,工作量从26+5+3=34%减少到4%。
(3) 排序,预排序和哈希匹配
很多操作希望在操作之前数据是分好组的。这类操作包括:distinct,union,group by和join。正常情况,SQL Server将使用下面三种方法中的一种来完成分组:
- 很高兴的发现,数据已经预先排序进入分组序列。
- 通过哈希匹配操作对数据分组。
- 对即将分组的序列中的数据排序。
预先排序
索引就是你预先排序数据的方式,向SQL Server提供它通常所需的顺序。这就是创建有包含列的非聚集索引有利于查询的原因。事实上,如果你把鼠标放在图形执行计划的“合并连接”图标上的时候,将会出现“从两个已进行了相应排序的输入表中,使用其排序顺序对行进行匹配”的提示信息。这告诉我们两张表/索引在连接的时候,使用了最小的内存和处理器时间。
哈希匹配
如果输入的数据不是想要的顺序,SQL Server可能会用哈希匹配操作进行分组。哈希匹配是一种消耗大量内存的技术,但是比排序要高效。在执行distinct,union和join操作的时候,哈希匹配比排序有优势,因为处理完一行,这一行就可以进行下一个操作,而不用等所有行都哈希匹配完。但是,在计算分组聚合的时候,在进入下一个阶段之前,还是需要读取所有行才行。
哈希匹配所需要的内存,直接和分组产生的数量有关。
SELECT Gender, COUNT(*)
FROM NewYorkCityCensus
GROUP BY Gender
上面的分组消耗的内存就很少,因为只有两个分组:Female和Male。和输入数据的行数没有关系。
SELECT LastName, FirstName, COUNT(*)
FROM NewYorkCityCensus
GROUP BY LastName, FirstName
这个分组就会占用大量的内存,因为产生大量的组。这么大量的内存消耗,导致哈希匹配在查询的时候变成了一个不受欢迎的技术。
排序
如果数据没有排好序(没有索引),同时SQL Server认为哈希匹配不能高效的完成,SQL Server就会对数据进行排序。正常来说,这可能是最不想看到的。因此,如果排序图标出现在执行计划的早期,检查一下是否可以改进你的索引。如果排序图标出现在执行计划的后期,很可能意味着SQL Server因为请求中的order by子句,而对最终输出结果进行排序;这个顺序不同于join,group by和union中的顺序。这时候的排序很可能没有办法避免。