-
SQL Server 查询性能优化——索引与SARG（一）
这一篇文章修修改改，已经写了很久了，还是感觉好像自己没讲清楚，鉴于本人水平，就先这样写吧，待本人水平提高之后，再进行修补。
在写作的过程也学习到了，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包含以下操作符=、>、<、>=、<=、BETWEEN及部分情况下的LIKE。LIKE是否符合SARG，要看通配符%所在的位置。例如：LIKE '胡%'就是符合SARG，但是'%胡'就不符合SARG。因为以通配符开头无法限制SQL SERVER查询记录的数量，索引的摆放依然是以小到大，或以大到小顺序排列，如果以通配符“%”开头就无法利用有序的结构，以二分法来快速查找数据。
简言之，在查询子句中，SARG代表用来查找的常量或变量可以直接与索引键值进行比较，下面是一些常用SARG与执行索引的关系。
| 序号 | 索引 | SQL语句与查询执行计划 | 记录数 | 执行成本 |
|
1 |
索引4 |
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_1=1 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取29 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
29 |
0.0267688 |
||
|
2 |
索引1 |
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1=1 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
1314 |
1.03687 |
||
|
|
||||
|
3 |
索引4 |
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_1>=312 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取29 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
29 |
0.0268468 |
||
|
4 |
索引1 |
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1>=312 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
1314 |
1.03687 |
||
|
5 |
索引4 |
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_1<2 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取29 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
29 |
0.026875 |
||
|
6 |
索引1 |
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1<2 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
1314 |
1.03687 |
||
|
7 |
索引4 |
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_1!>1 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取29 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
29 |
0.026875 |
||
|
8 |
索引1 |
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1!>1 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
1314 |
1.03687 |
||
|
9 |
索引2 |
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where QTY_1 between 412 and 500 |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1021 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
1021 |
0.959746 |
||
|
10 |
索引3 |
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where cop_g_no like '80215%' |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取320 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
||||
|
|
320 |
0.316824 |
||
|
11 |
索引3 |
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%' |
||
|
表'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1，逻辑读取1314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
1314 |
1.03687 |
||
|
|
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
|
出处：https://www.cnblogs.com/chillsrc/archive/2012/10/31/2745675.html
