SQL Server DBA面试知识点（五）-- 索引与执行计划

参考了SQL Server 2019官网文章，建议查看：
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15

索引的定义和基本知识

索引是数据表中一个或多个字段中存储数据的高度概括，索引数据单独存储在硬盘上。通过创建并使用索引可以大大提高数据读取或操作的效率。

在SQL Server中索引分为clustered index聚集索引和non-clustered index非聚集索引。它们的区别是：
在一个heap table上创建聚集索引，SQL Server会按照索引键值的顺序将表数据重排序并按此顺序存储到数据页面上。因此聚集索引决定了表数据物理存储的顺序，从这点可知一个表只能有一个聚集索引(但可以建立在多个字段上)。而在字段上创建非聚集索引不会影响物理存储，一个表可有多个非聚集索引。
一个没有建立聚集索引的表称为heap table，因为此时表中的数据是无序的，称为Heap。对于Heap table，SQL Server会使用RID来定位数据(和Oracle中的ROWID类似)。

另外我们常常听到的primary key主键是一个特殊的索引：主键强调键值的唯一性，是唯一约束+非空约束的组合。而主键是否同时为聚集索引则视情况而定：
如果创建时表中一个聚集索引都没有，同时创建时没有强制指定使用非聚集索引，则主键会被自动创建为的聚集索引。否则为非聚集索引。

索引的数据结构
关系型数据库大都采用B-tree(注意B即不是指二叉树也不是指平衡树，B只是一个名字并没有实际意义)。注意有些文献称为B+tree，二者之间没有明显的界限。学习过数据结构的同学知道，B-tree结构尤其适用于快速查询磁盘数据的场景，因此B-tree在数据库索引和文件系统搜索等领域都得到了广泛应用。
B-tree是多层级的树状结构，如下图:

上图是一个最简单的索引，索引键只有一个字段，ID，范围是0~250，且ID值按顺序存储在索引页中。index node索引节点都是大小为8k的数据页面。最上层的那个成为index root，即根节点；根节点的下一层为树节点；最下层为叶节点。注意根节点到叶节点可以有多层。
由图可以看到，B-tree索引实际上是将底层数据页面中存储的字段的数值范围（如0~10）提取出来成为叶级索引页面中的一行。然后再将叶级索引页面中的数值范围提取出来成为上一级索引页面中的一行，以此类推。且所有页面中的数据是有序的(正序或倒序，以建立索引时的语句为准)。由此可见，只有叶子节点才会保存真正的数据，根节点到中间层节点都只保存数据的概括。
此时如果有一个查询语句where ID=223, 则从index root开始查找.从图中可见，只需经过3层，共3次查找就可以找到对应行的rowid，并以rowid到数据表中找到对应的数据行。由于每个节点为8k，可以存储很多信息，因此即使对于一个很大的表(比如千万或亿行数据)，树节点层级也不会增加很多，一般4~6次查询就可以找到对应的数据。例如一个1000w行的表，每个叶节点可以存储1000行数据的信息，那么一共需要1w个叶节点，而倒数第二层的树节点只需要10个就够了(也就是每个树节点存储1000行下级节点的信息)，再上一层根节点就够了。由此可见对于超大表，索引结构依然可以快速收敛到根节点，对于这个1000w行的表，只需3次查询就可以找到对应的数据。同时B-tree结构决定了所有数据从根节点到叶节点的层数是一样的，是一个平衡树结构。
需要注意的是，只有非聚集索引才需要用指针去数据表查询对应的行数据。对于聚集索引来说，叶节点就是表本身，因此省了这一步。

SQL Server的索引是B-tree结构，但其实也做了一些改进：同一层级的数据页面，包括叶子节点之间由双向链表直接相连。这样的好处是：
1，提高范围查询的效率。范围查询时不再需要总是从根节点出发，从叶子节点直接向后遍历即可。
2，在order by时省略了排序操作，且由于是双向链表，order by可以是正序或倒序的。来看例子：
order by c1 desc, c2 asc; – 在建立索引时指定c1 desc, c2 asc即可在运行order by时省略排序操作。
order by c1 asc, c2 desc; – 由于是双向链表，因此可以反向遍历链表，使用上面的索引可省略排序。
order by c1 asc, c2 asc;
order by c1 desc, c2 desc; – 这两种情况则无法省略排序。
其它关系型数据库也都使用B-tree或B+tree，某些产品还提供一些增强。例如Oracle还提供Bitmap-index，mySQL提供Hash index。

索引与查询效率

当数据库运行SQL时，面临多种选择：使用哪种join方式(merge/hash/loop/remote join)？使用哪个索引？是否将中间结果先排序效率更高？
很多时候虽然表中已经有了索引，但SQL Server的查询引擎并不一定会用。原因是SQL Server使用基于性能的优化(cost-based optimization)。通常做法是根据表中的索引和统计信息做出多个不同的执行计划，之后比较它们并选出一个最优方案，并使用最优方案执行SQL。因此创建一个并不合适的索引不会对SQL的性能有任何帮助。
那么如何知道SQL是否使用了索引呢？答案是查看SQL的执行计划。执行计划包括使用的索引，join方式，排序和其它很多SQL Server自动的操作。通过执行计划我们可以看出SQL是怎样一步步完成的。
执行计划为两种：估计的执行计划Estimated Execution Plan和实际执行计划Actual Execution Plan。估计的执行计划是SQL执行前，查询优化器根据索引信息，数据统计信息等估算出来的最好的执行计划。由于估计的执行计划是事先的预判，因此信息并不一定准确。例如对于一个查询query，估计执行计划中可以看到，该查询会返回1000行数据。这是基于数据库中已有的统计信息的预判，而不是真正执行的结果。如果执行该query，最终可能返回958行。查看估计执行计划不会导致SQL被执行，但你可以看到SQL Server准备如何执行这个SQL。而实际执行计划则是运行SQL时实际使用的执行计划。它不能直接被查看，需要执行SQL，同时选择“Includes Actual Execution Plan”，即返回SQL结果时，同时返回所用的执行计划。实际执行计划是SQL真实运行情况的统计，它是完全准确的。缺点是必须等到SQL运行结束才能看到此类执行计划。
请参考另外一篇文章：
http://blog.csdn.net/onlyqi/article/details/15501217

DBA做SQL调优的依据应该是执行计划，也只有执行计划，而不是任何主观的判断。执行计划中给出了很多信息，具体请自行参考官网。
在为任何一个SQL设置索引后，都应该查询执行计划以保证SQL Server会使用它。SQL Server不会使用不恰当的索引，但依然需要耗费资源去维护这些索引，无疑是非常浪费的。

参数化和执行计划重用

SQL Server需要耗费很多资源来制定执行计划，因此执行计划生成后会被缓存到内存中，以供下次使用。但是考察一下两个SQL，是否应该重用执行计划呢？

SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 56000
GO

SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 56001
GO

可见两个SQL几乎相同，仅仅是SalesOrderID稍有变化。但有时查询参数的变化因为数据分布的不同会影响执行计划。例如一个表中的“性别”字段99%为“男性”，1%为“女性”。此时查询条件为“性别是男性”的SQL就可以全表扫描，而查询条件为“性别是女性”时全表扫描是效率很低的做法。因此参数的变化对执行计划的良莠有很大影响。
因此SQL Server在决定一个SQL是否能重用执行计划时，需要先经过一步：参数化(parameterization)。If a SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. This process is called simple parameterization. 但simple parameterization有很多限制，仅仅是一些微小的差别就会导致SQL Server无法重用执行计划，例如：

SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 56000
GO

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader WHERE SalesOrderID = 56000
GO

对于第二个SQL，SQL Server无法重用执行计划。正由于实际SQL的复杂结构和数据的复杂变化，大量SQL无法参数化，也无法重用已存在的执行计划。限制如下：
Single Table – No JOINs
No IN clause
No UNION
No SELECT INTO
No Query Hints
No DISTINCT or TOP
No full-text, linked servers or table variables
No sub-queries
No GROUP BY
No <> in WHERE clause
No functions
No DELETE or UPDATE with FROM clause
只有对那些参数值不会影响执行计划的SQL，SQL Server才能对其重用执行计划–这点尤其重要。

而使用stored procedures, sp_executesql or Forced Parameterization可以保证SQL Server重用执行计划。
但随着表结构和数据的变化，缓存的执行计划可能会变得不合适。对于有些情况，SQL Server会自动产生新的执行计划(称为重编译)，但有时不会，可能需要手工重编译。具体请参考下文中的“Recommendations”。
https://msdn.microsoft.com/en-us/library/ms190439.aspx

不适合使用索引的情况
在某些情况下，使用索引并不会给性能带来任何益处：
1，数据量很小
这种情况下，全表扫描是可能是一种更好的选择。使用聚集索引时，一般而言大部分是顺序IO，有一些随机IO。非聚集索引可能会导致大量的随机IO，而全表扫描则基本是顺序IO。因此数据量不大的情况下，全表扫描效率并不低。
2，数据量超大
这种情况下，使用索引也可能导致大量随机IO，理由同上。同时维护索引的开销也很大。此时应使用分区表和分区索引，或使用覆盖索引。因为覆盖索引在索引页面就可以得到全部信息，不需要返回物理页面，因此不会产生随机IO。