《Microsoft Sql server 2008 Internals》读书笔记--第八章The Query Optimizer(7)

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internals》读书笔记--目录索引

前几篇主要介绍了查询结构优化中的几个关键概念:统计(Statistics)、基准估计(Cardinality estimation)和成本(costing) ,今天开始真正进入主题：
今天我们关注的是：索引选择

■Index Selection

索引选择是查询优化最重要的一点，索引匹配的基本思路是从Where子句、连接条件、或查询中的其他限定操作符中提取谓词，并转换这个操作为能被针对索引的操作。

两个基本的操作能被针对索引执行：

１、Seek(对一个单个值或索引键的一个值的范围range)

2、Scan the Index(向前或向后)

对Seek,初始的操作在B+树的根节点开始，沿b树向下到一个在索引键上的理想的索引位置。一旦完成，查询处理器会遍历所有的行以匹配谓词，或者直到范围中的最后一个值被找到。因为B+树的页在SQL Server中是链接的，使用这个结构查找所有的行成为可能，只要中间的Ｂ+树节点被遍历过。

查询优化器的一个工作是辨别出哪一个谓词能被应用到索引以尽可能快地返回行。某些谓词能被应用到某个索引，某些则不能。 如查询：
select col1,pkcol from myTable where col1=2 ，有一个形式为<column>=<constant>的谓词。如果该列有一个索引，则这个模式被匹配为一个Seek操作。生成的备选结果是针对一个非聚集索引执行一个seek，返回匹配的行。看一个基本索引的例子：

Create table IdxTest2010(col2 int,col3 int,col4 int);
Create index idex2010 on IdxTest2010(col2,col3);
select col2,col3 from IdxTest2010 where col2=5

注意：查询优化器也可以针对多列索引应用复合谓词，只要这个操作能被转换为开始和结束索引键。

因此，对于如下语句，可以得到相同的seek Plan:

能被转化为一个索引操作的谓词也被称作“可参数化的搜索”(sargable或search-Argument-able)谓词,这意味着这种谓词的形式可以被转化为一个索引操作。不能转化的则称为non-sargable谓词,它通常在索引seek后被应用，这样查询得以返回符合所有谓词的记录行。有时候让人感到迷惑的就是SQL Server通常在查询树的seek/scan操作中评估non-sargable谓词。这是一个优化进程，如果不这样做，SQL Server步骤如下：

1、Seek 操作：Seek至索引B+树中的一个键

2、锁页面(latch the page)

3、读取行

4、释放页面锁

5、返回行到筛选索引

6、 筛选：评估针对这些行的non-sargable谓词，如果通过鉴定，传递这些行到父操作。否则，转到第二步继续下一个候选行。

这个流程比最佳要慢一些，因为返回这些行到一个不同的操作符需要加载一个不同的列集和数据到CPU。通过保持逻辑在一个地方，整个评估查询的CPU成本下降了。在SQL Server中实际的操作类似如下：

1、Seek 操作：Seek至索引B+树中的一个键

2、锁页面(latch the page)

3、读取行

4、应用non-sargable谓词筛选，如果行没有通过筛选，转到第三步。否则，转到第五步。

5、释放页面锁

6、 返回行

这就是所谓的pushing non-sargable谓词(谓词被从一个筛选推进seek/scan）。这是一个物理优化，但它能展示处理多行的查询内部流程。

并不是所有的谓词都能被在seek/scan操作中评估。因为锁操作阻止其他用户甚至查看系统中的一个页，这个优化被保留给那些成本低廉的谓词。也就是所谓的non-pushing ,non-sargable谓词，例子包括：

■Predicates on Large Objects(包括varbonary(max),varchar(max),nvarchar(max))

■CLR函数

■一些T-SQL函数

谓词可搜索参数化能力，在数据库应用程序设计中是一个非常重要的因素。系统性能很差的一个原因是针对数据库的应用程序被写作这样一种方式：即谓词non-sargable。在很多情况下，这是可以避免的，如果主题能被标识得足够早，(按照一个可度量的顺序)修正这个issue有时会增加数据应用程序性能。

SQL Server在尽量（在一个查询中）应用针对可搜索参数化的谓词的索引时考虑多种方案。比如对于AND条件(Where col1=5 AND col2=a AND...),SQL Server会试着这样：

1、对于一个给定的列表（该列表中包含需要相等列、不等列、需要适合查询但不带谓词的列），首先试图找到一个精确匹配请求的索引。如果有这样一个索引，则使用它。

2、尽量找到一个索引集以适合等式条件，并为所有这样的索引执行一个内连接。

3、如果步骤2不能覆盖所有请求的列，考虑（在解决方案内）连接其他基于列集的索引。

4、最后，执行一个连接到基表得到任何剩余的列。

在所有这些案例中，每个解决方案的成本被考虑，如果它最确认为最低成本的解决方案，则返回访方案。因此，一个将其他索引连接在一起的解决方案，被使用仅仅因为它被确定比其他基表中的所有行的scan要节约成本。其次，算法仅仅在本地查询树上执行。即使查询优化器在此过程中生成了一个特定的替代方案，它也不一定就是最后查询计划的一部分。成本被用于判定成本最低的完整计划。因此，索引选择是一个启发式，是更广泛的（用于帮助选择高效查询计划的）成本基础设施的一部分。

■Filter Index

SQL Server 2008推出一种新的功能，即在创建索引时可以带简单的谓词，以限制包含在索引中的行集。乍看之下，这个内容已经包含在索引视图中功能的一个子集。实际上，这个功能存在的意义在于：1、索引视图使用和维护时成本高昂。2、匹配索引视图内容的兼容性不是在所有SQL Server 版本中都被支持。 3、大量的不同SQL Server用户使用的场景比视图等内容要复杂得多，他们可能还是倾向于使用传统的关联查询场景。

筛选索引在Create Index语句中使用where子句。

Create table TestFilter1(col1 int ,col2 int ); go set nocount on BEGIN TransAction; Declare @i int set @i=0 while @i<40000 BEGIN Insert into TestFilter1(col1,col2) values(rand()*1000,rand()*1000); set @i=@i+1 END Commit Transaction go Create Index idx2011 on TestFilter1(col2) where col2>800
此时，如果执行以下查询，则得到筛选索引的支持：
select col2 from TestFilter1 where col2>800


如果执行以下查询，则得不到筛选索引的支持：

select col2 from TestFilter1 where col2>799

筛选索引未完待续。

下文将继续了解筛选索引(Filtered Indexes)