VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >
  • 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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

 

(61 行受影响)

'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1,逻辑读取250 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

从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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

29

0.0267688

2

索引1

SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1=1

   
   

'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1,逻辑读取1314 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

29

0.0268468

4

索引1

SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1>=312

   
   

'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1,逻辑读取1314 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

29

0.026875

6

索引1

SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1<2

   
   

'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1,逻辑读取1314 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

29

0.026875

8

索引1

SELECT  * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1!>1

   
   

'WBK_PDE_LIST_ORG_HISTROY'。扫描计数1,逻辑读取1314 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

   
     

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 次,物理读取次,预读次,lob 逻辑读取次,lob 物理读取次,lob 预读次。

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


相关教程