SQL Server 查询性能优化——索引与SARG（一）

sdchy

这一篇文章修修改改，已经写了很久了，还是感觉好像自己没讲清楚，鉴于本人水平，就先这样写吧，待本人水平提高之后，再进行修补。
在写作的过程也学习到了，SQL查询优化程序也并不一定会使用查询参数中字段的相关索引，而是根据查询数据量的多少而产生的查询成本，来决定是使用查询参数中的字段索引，还是使用聚集索引或全表扫描。
中心思想就是关于SQL语句的“查询参数”（SARG）与索引的使用。符合SARG格式的数据肯定会使用到相应的索引呢？先给出答案，不是。
例如：Select * from WBK_PDE_LIST_ORG where cop_g_no='11000' ，假设在cop_g_no上建立了非聚集索引，那么当查询语句得出的结果数量小于某个数量阀值时，例如查询结果的数量小于600条时，会使用到非聚集索引，但当查询结果数量大于600条时，却可能不会使用非聚集索引，可能会使用聚集索引或全表扫描。

在编写SQL语句的WHERE 子句时，你是否考虑过WHERE子句中的条件参数的编写格式要符合“ （查询参数：SARG )”规则，SQL SERVER的查询优化程序才能建立有效的利用索引的计划。
在进行具体分析之前，首先建立以下索引。当然索引2、3与索引4、5的名称需要自己修改。

序号	索引类型	SQL语句
1	主键聚集索引	ALTER TABLE [dbo].[WBK_PDE_LIST_ORG_HISTROY] ADD  CONSTRAINT [PK_WBK_PDE_LIST_ORG_HISTROY] PRIMARY KEY CLUSTERED( [WBOOK_NO] ASC, [G_NO] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
2	非聚集索引（无INCLUDE）	CREATE NONCLUSTERED INDEX [idx_WBK_PDE_LIST_QTY1] ON [dbo].[WBK_PDE_LIST_ORG_HISTROY] ( [QTY_1] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
3		CREATE NONCLUSTERED INDEX [idx_WBK_PDE_LIST_COP_G_NO] ON [dbo].[WBK_PDE_LIST_ORG_HISTROY] ( [COP_G_NO] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
4	非聚集索引（有INCLUDE）	CREATE NONCLUSTERED INDEX [idx_WBK_PDE_LIST_QTY1] ON [dbo].[WBK_PDE_LIST_ORG_HISTROY] ( [QTY_1] ASC ) INCLUDE ( [WBOOK_NO],[G_NO],[CODE_T],[COP_G_NO],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
5		CREATE NONCLUSTERED INDEX [idx_WBK_PDE_LIST_COP_G_NO] ON [dbo].[WBK_PDE_LIST_ORG_HISTROY] ( [COP_G_NO] ASC ) INCLUDE ( [WBOOK_NO],[G_NO],[CODE_T],[QTY_1],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

　　
　　
　　
Index Seek 运算符利用索引的查找功能从非聚集索引中检索行。
Index Scan 运算符从 Argument 列中指定的非聚集索引中检索所有行。如果可选的 WHERE:() 谓词出现在 Argument 列中，则只返回满足该谓词的那些行。
Clustered Index Scan 运算符会扫描查询执行计划的 Argument 列中指定的聚集索引。如果出现可选 WHERE:()谓词，则只返回满足该谓词的行。
Clustered Index Seek 运算符可以利用索引的查找功能从聚集索引中检索行。Argument 列包含所使用的聚集索引名称和 SEEK:() 谓词。存储引擎仅使用索引来处理满足此 SEEK:() 谓词的行。它还包括 WHERE:() 谓词，其中存储引擎对满足 SEEK:() 谓词的所有行进行计算，但此操作是可选的，并且不使用索引来完成此过程。

Table Scan 运算符从查询执行计划的 Argument 列所指定的表中检索所有行。如果 WHERE:()谓词出现在 Argument 列中，则仅返回满足此谓词的那些行。
Filter 运算符扫描输入，仅返回那些符合 Argument 列中的筛选表达式（谓词）的行。
Inner Join 逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入所组成的联接的每一行。
SQL Server 2005 Service Pack 2 中引入的 Key Lookup 运算符是在具有聚集索引的表上进行的书签查找。Argument 列包含聚集索引的名称和用来在聚集索引中查找行的聚集键。

RID Lookup 是在使用提供的行标识符 (RID) 在堆上进行的书签查找。Argument 列包含用于查找行的书签标签和从中查找行的表的名称。RID
1. 有效地查询参数
得到相同查询结果的SQL语句的写法有很多种，那么应该如何决定采用哪种SQL语句编写方式比较有用呢？最重要的考虑因素之一是WHERE 条件子句， WHERE子句限制了查询所要返问的记录数量，查询优化程序会尝试判断己有的索引，分析对查找符合WHERE子句条件的记录是否有帮助。
查询优化程序首先就要查看WHERE 子句中所有的条件，以决定这些条件在限制SQL SERVER 访问数据时是否有用。换句话说，查询子句是否有用要看查询参数（Searchable Arguments , SARG〕
很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如：
SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY] where QTY_1>53 and COP_G_NO='90206884'
  和执行:
SELECT *
  FROM [WBK_PDE_LIST_ORG_HISTROY] where  COP_G_NO='90206884' and QTY_1>53
一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果QTY_1是一个非聚集索引，那么前一句仅仅从QTY_1大于53的记录中查找就行了；而后一句则要先从全表中查找看有几个COP_G_NO='90206884'的，而后再根据限制条件条件QTY_1>53来提出查询结果。
事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以根据WHERE子句中的搜索条件进行自动优化，建立有效的索引使用计划。
上面两句的IO情况是一样的，都是250次逻辑读取操作。具体执行结果如下：
(61 行受影响)
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取250 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

(61 行受影响)
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取250 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
从Managemenet studio中可以看出上面两句的查询执行计划都是一样的。如下图。

　　
所以上面两句的执行效率是一样的。

虽然查询优化器可以根据WHERE子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，我们有时会以查询参数这个名词来泛指在WHERE 子句中所有的条件，但此处使用SARG缩写来代表查询参数的有效格式。在大多数状况下，查询优化程序只能对符合SARG 条件的WHERE子句通过索引找到优化的执行方式。如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。

SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。SARG 包含常量描述式（或是可以解析成常量的变量）来与数据表中的字段做比较。SARG 的格式是：
列名 操作符  
或
操作符 列名
列名出现在操作符的一边，而常量或变量出现在另一边。如果列名同时出现在操作的两边就不算是SARG。
SARG包含以下操作符=、>、=、=312
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450]表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取29 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450] 　　
[/td][td=1,1,30]29
[/td][td=1,1,26]0.0268468
[/td][/tr][tr][td=1,1,30]4
[/td][td=1,1,30]索引1
[/td][td=1,1,450]SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1>=312
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450]表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450] 　　
[/td][td=1,1,30]1314
[/td][td=1,1,26]1.03687
[/td][/tr][tr][td=1,1,30]5
[/td][td=1,1,30]索引4
[/td][td=1,1,450]SELECT  [WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T]  

      ,[QTY_1],[UNIT_1],[TRADE_TOTAL]  
      ,[GROSS_WT] FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_11
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450]表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450] 　　
[/td][td=1,1,30]1314
[/td][td=1,1,26]1.03687
[/td][/tr][tr][td=1,1,30]9
[/td][td=1,1,30]索引2
[/td][td=1,1,450]SELECT *
  FROM [WBK_PDE_LIST_ORG_HISTROY] where QTY_1 between 412 and 500
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450]表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1021 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450] 　　
[/td][td=1,1,30]1021
[/td][td=1,1,26]0.959746
[/td][/tr][tr][td=1,1,30]10
[/td][td=1,1,30]索引3
[/td][td=1,1,450]SELECT *
  FROM [WBK_PDE_LIST_ORG_HISTROY] where cop_g_no like '80215%'
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450]表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取320 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450] 　　
[/td][td=1,1,30]320
[/td][td=1,1,26]0.316824
[/td][/tr][tr][td=1,1,30]11
[/td][td=1,1,30]索引3
[/td][td=1,1,450]SELECT *
  FROM [WBK_PDE_LIST_ORG_HISTROY] where cop_g_no like '802%'
SELECT *
  FROM [WBK_PDE_LIST_ORG_HISTROY] where cop_g_no like '%21%'
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450]表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
[/td][td=1,1,30]1314
[/td][td=1,1,26]1.03687
[/td][/tr][tr][td=1,1,30] [/td][td=1,1,30] [/td][td=1,1,450] 　　
[/td][td=1,1,30] [/td][td=1,1,26] [/td][/tr][/table]
SQL SERVER查询分析优化器对于每一条查询语句的WHERE子句进行评估，看是使用索引的查询成本还是使用聚集索引扫描的查询成本低。
从上表中我们可以看出根据不同的查询语句与不同的查询字段，会使用不同的索引，如果当查询出来的记录数比较多时，也就是超过了直接使用聚集索引扫描或全表扫描查询出来的数据时，即使WHERE子名是SARG格式的写法，他也将使用放弃使用相应的索引，而使用全表扫描与聚集索引扫描（例如上表中的2，4，6，8，11）。
　　

使用索引	查询语句	查询记录数量	执行成本
索引2	9	1021	0.959746
索引3	10	320	0.316824
索引4	1，3，5，7	29	0.026875
索引5
索引1	2，4，6，8，11	1314	1.03687