SQL Server 的查询过程、执行计划学习总结

SQL Server 的查询过程、执行计划

Building Blocks的概念

SQL Server的每一个查询都是由Building Block组成的集合，Building Block分为两种，operators和iterators。一个iterator从它的子iterator中获取数据，经过处理后返回给它的父iterator。

所有iterator都实现了一个接口， 这个接口中有两个函数，Open和GetRow。Open函数告诉operator准备输出结果行，GetRow函数返回一个新的结果。

Query Plan是一个由iterators组成的树形结构，控制流从根到叶子流动，数据流从叶子到根流动。

Iterators的属性

• 内存：iterator的执行需要申请内存，当执行一个iterator之前，会估计它占用的内存并预留出足够的内存。
• 非阻塞iterator和阻塞iterator：非阻塞iterator从子iterator消费输入行，和向父iterator生产输出行是同时进行的，接收到一行输入就立刻处理和输出。阻塞iterator得到所有的子iterator输入行并处理结束后，才会向父iterator输出。非阻塞iterator更适用于OLTP场景。
• 动态游标支持：动态游标查询有一些特别的属性，如果一个Query Plan是一个动态游标Query Plan，那么它必须可以一次返回一个集合，必须可以正向Scan和反向Scan，必须可以获取Scroll Locks。支持动态游标的Query Plan必须满足，组成它的每个iterator必须能够重置状态、正反向Scan、非阻塞。所以不是每个Query Plan都能支持动态游标。

Scan 和 Seek

• Scan ： 扫描整张表，通常是由于要查询的列不存在索引。如下

  |--Table Scan(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

• Seek： 查询的列存在索引，一般会采用Seek查询索引列，当然也有特殊，要考虑占用Memory和IO次数。

  |--Index Seek(OBJECT:([ORDERS].[OKEY_IDX]), SEEK:([ORDERKEY]=(2)) ORDERED FORWARD)

• | Scan | Seek
  —|---|—
  堆 | Table Scan | -
  聚集索引 | Clustered Index Scan | Clustered Index Seek
  非聚集索引 | index Scan | Index Seek

Book Lookup

• 原因：where从句中的谓词是非聚集索引，不包含需要查询的列，需要先seek非聚集索引，再从seek行的聚集索引查找到查询列。

create table T (a int, b int, c int);
create unique clustered index T_clu_a on T(a);
create index T_b on T(b);

select c from T where b = 2;

这里的查询需要首先利用非聚集索引b，seek到相应的行，因为b是非聚集索引，索引b只包含a、b两列，所以需要再根据聚集索引 a 查找 c。

Book Lookup 查询聚集索引是随机I/O，因此很耗时间，因此当查询出现大量Book Lookup时，我们通常是修改查询SQL或者添加其他索引。

Seek的谓词

单列索引, 假设索引是 a

当索引只有单列时，SQL Plan中下面几种查询谓词可以用到该索引

• a = 3.14
• a > 100
• a between 0 and 99
• a like ‘abc%’
• a in (2, 3, 5, 7)

下面的查询谓词不会用到索引。

• ABS(a) = 1
• a + 1 = 9
• a like ‘%abc’

多列索引，假设索引是(a, b)

当索引有多列时，下面的查询谓词会用到索引Seek。

• a = 3.14 and b = ‘pi’
• a = ‘xyzzy’ and b <= 0

下面的查询谓词不会用到索引。

• b = 0
• a + 1 = 9 and b between 1 and 9
• a like ‘%abc’ and b in (1, 3, 5)

唯一聚集索引(unique clustered index) 包含表所有的列，并且索引的顺序即是数据在磁盘中的物理顺序。

非聚集索引只包含它的索引列和所有的聚集索引列。

索引的权衡

同样一个SQL语句，由于查询到的数据行数不同，所执行的SQL Plan可能不同，这是由于数据库的Optimizer对使用内存空间大小和I/O次数的权衡所致。

• 如果每次查询都用聚集索引，那每次查询都要将整行的数据读到内存，而我们需要的往往是其中的某几列，这显然很浪费内存资源。
• 如果总是使用非聚集索引，可能会造成BookMark Lookup的次数过多，导致随机I/O的次数过多。

因此，权衡内存使用和随机I/O而改善SQL执行计划，是任何一款SQL优化器必须要做的。

Joins，表的连接

• Inner join， 内连接
• Outer join， 外连接
• Cross join， 笛卡尔积
• Cross apply，带参数的动态笛卡尔积(暂时这么解释)
• Semi-join， 半连接
• Anti-semi-join， 反半连接

Semi-join。所谓的semi-join是指semi-join子查询。当一张表在另一张表找到匹配的记录之后，semi-jion返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN或 EXISTS 作为连接条件。

Anti-semi-join则与semi-join相反，即当在第二张表没有发现匹配记录时，才会返回第一张表里的记录。当使用not exists/not in的时候会用到，两者在处理null值的时候会有所区别。

Nested Loops Join

嵌套循环连接，是使用的比较频繁的连接方式，它连接两张表，将其中一张表的每一行和另一张表的每一行根据是否符合连接条件相连接。下面是伪代码。

for each row R1 in the outer table
    for each row R2 in the inner table
        if R1 joins with R2
            return (R1, R2)

例子

select *
from Sales S inner join Customers C
on S.Cust_Id = C.Cust_Id
option(loop join)

因为没有任何索引，所以SQL Plan如下

|--Nested Loops(Inner Join, WHERE:([C].[Cust_Id]=[S].[Cust_Id]))
   |--Table Scan(OBJECT:([Customers] AS [C]))
   |--Table Scan(OBJECT:([Sales] AS [S]))

加一个唯一聚集索引

create clustered index CI on Sales(Cust_Id)

SQL Plan

|--Nested Loops(Inner Join, OUTER REFERENCES:([C].[Cust_Id]))
   |--Table Scan(OBJECT:([Customers] AS [C]))
   |--Clustered Index Seek(OBJECT:([Sales].[CI] AS [S]), SEEK:([S].[Cust_Id]=[C].[Cust_Id]) ORDERED FORWARD)

Nested loops join支持的连接类型如下。

• Inner join
• Left outer join
• Cross join
• Cross apply and outer apply
• Left semi-join and left anti-semi-join

不支持所有的右连接，试想如果支持右连接，就要返回(null, R2), 每次输入外部表的一行，就要遍历一次内部表，这样就会出现非常多重复的(null, R2)。

Merge Join ，合并连接

合并连接伪代码如下。

get first row R1 from input 1
get first row R2 from input 2
while not at the end of either input
    begin
        if R1 joins with R2
            begin
                return (R1, R2)
                get next row R2 from input