sqlserver 索引进阶（上）

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中的顺序。这时候的排序很可能没有办法避免。
相关阅读:
网络流 KM dinic
网络流增广路回退
 树链剖分
 线段树区间更新 lazy
全排列
 各种蕴含算法思想的DP
各种蕴含算法思想的DP
Strassen矩阵乘法之思考
 [0,x)的随机数
 hdu1331 按着题目的公式直接写
原文地址：https://www.cnblogs.com/anxiao/p/6647213.html