SQL Server 查询性能优化——索引与SARG(二)

时间:2021-10-02 00:30:09

 

接 SQL Server 查询性能优化——索引与SARG(一)

对于非SARG语句,SQL SERVER 必须评估每一条记录以决定它是否符合WHERE子句的条件。所以索引对于采用非SARG条件的查询通常没什么用处。而通过非SARG语句通常包含以下操作: NOT、!=、<>、!>、!< 、NOT EXISTS 、NOT IN 和NOT LIKE 等,以及上述提及的“%IS%”,其中LIKE使用方法会造成全表扫描(TARLE SCAN)或是聚集扫描而降低性能.

创建SQL Server 查询性能优化——索引与SARG(一)中开头部分创建索引中的索引1,3

--例一、

SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY] where COP_G_NO like '%79'

 

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

 

SQL Server 查询性能优化——索引与SARG(二)

 

 

--例二、

  SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY] with(index(idx_wbk_pde_list_cop_g_no))where COP_G_NO like '0016%'

 

 

 

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

 

SQL Server 查询性能优化——索引与SARG(二)

 

 

-----例三、
SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY]  where COP_G_NO like '0016%'

 

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

 

SQL Server 查询性能优化——索引与SARG(二)

 

使用索引

查询语句

查询记录数量

执行成本

全表扫描

例一

916

1.03687

指定使用非聚集索引

例二

919

2.24756

使用聚集索引

例三

919

1.03687

       
       

从上表中的例一、例三中可以看出全表扫描与聚集索引扫描的执行成本是一样的,或相差不大。而例二中,使用了指定的非聚集索引,由于使用非聚集索引查找 数据,会有一个RID键值查找(或称Bookup Lookup)随机操作,所以当查询的记录数量越多,则执行成本就越大。

 

 

下面来讲讲一些常见的WHERE子句用法使用不当的情景,并探讨较正确的用法:

。对WHERE子句做运算。
。负向查询。
。对WHERE子句使用函数。
。使用OR 操作。


一、 不要对WHERE子句做运算
先以简单的范例来比较SARG 和非SARG ,你可以通过Managemenet studio来查看查询优化程序是否可以有效分析该语句。

  1 、没有任何索引

 

---例一:在WHERE中做运算
SELECT  *  FROM [WBK_PDE_LIST_ORG_HISTROY] where wbook_no+G_NO='BE40494245002011844'

---例二:在WHERE中不做运算
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where wbook_no='BE404942450020' and g_no='11844'

 

   1) 例一与例二查询的IO读写数

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

   2)例一与例二的查询执行计划成本

SQL Server 查询性能优化——索引与SARG(二)

 

 

 

 2、只创建聚集索引。

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]

GO

 

   1) 例一与例二查询的IO读写数

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

 

   2)例一与例二的查询执行计划成本

SQL Server 查询性能优化——索引与SARG(二)

 

 

 3  创建SQL Server 查询性能优化——索引与SARG(一)中开头部分创建索引中的索引1(聚集索引)和索引5(非聚集索引)

 

 

 

 

   1) 例一的情况同上面2.

   2) 例二查询的IO读写数

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

   3)例二的查询执行计划成本

SQL Server 查询性能优化——索引与SARG(二)

 

从三个执行计划中,可以看到明显的差异。

因为SARG 的写法让查询优化程序可以直接对比条件常量,可以有效地利用在WHERE子句中的列名字段上建立的聚集索引或非聚集索引。而非SARG 则因为需要运算才知道数据是否符合,导致无法直接使用索引,因而采用聚集索引扫描方式、全表扫描方式、索引扫描方式。

对于第一个查询语句,由于不符合SARG格式,所以你可以看到查询优化查询并没有有效的利用索引。

 

SQL Server 查询性能优化——索引与SARG(二)

第一种情况,查询优化查询对于例一与例二都使用了全表扫描方式,从上图中可以看出整个数据表有共有1306页,

而此查询语句的逻辑读是1306次,与总数据页差不多,对整个表进行了扫描。与查询计划示意图中所示一样。

第二种情况,由于没有了idx_WBK_PDE_LIST_COP_G_NO索引,只有聚集索引PK_WBK_PDE_LIST_ORG_HISTROY。从上图中可以看出PK_WBK_PDE_LIST_ORG_HISTROY的索引页共有1306页,而此查询语句的逻辑读是1314次,与总索引页差不多,也是聚集索引扫描,与查询计划示意图中所示一样。

对于第三种情况,由于符合SARG 格式,而且WHERE子句后面的条件中的字段中都建立有索引,所以你可以看到查询优化程序会有效地利用先前建立的聚集索引(索引1)进行聚集索引查找。速度非常快。