金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 执行计划及Sql查询优化初探_MsSql_脚本之家,执行

执行计划及Sql查询优化初探_MsSql_脚本之家,执行

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-23 02:25

四个值分别为:

初探Sql Server 执行计划及Sql查询优化 收藏.text:''):(d.getSelection?d.getSelection():'');void(saveit=window.open(');)

MSSQL优化之————探索MSSQL执行计划

 

作者:no_mIss

 

最近总想整理下对MSSQL的一些理解与感悟,却一直没有心思和时间写,晚上无事便写了一篇探索MSSQL执行计划,本文讲执行计划但不仅限于讲执行计划。

 

网上的SQL优化的文章实在是很多,说实在的,我也曾经到处找这样的文章,什么不要使用IN了,什么OR了,什么AND了,很多很多,还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么(有点可笑),让许多人不知道其是对还是错。而SQL优化又是每个要与数据库打交道的程序员的必修课,所以写了此文,与朋友们共勉。

 

谈到优化就必然要涉及索引,就像要讲锁必然要说事务一样,所以你需要了解一下索引,仅仅是索引,就能讲半天了,所以索引我就不说了(打很多字是很累的,况且我也知之甚少),可以去参考相关的文章,这个网上资料比较多了。

 

今天来探索下MSSQL的执行计划,来让大家知道如何查看MSSQL的优化机制,以此来优化SQL查询。

 

--DROP TABLE T_UserInfo----------------------------------------------------

--建测试表

CREATE TABLE T_UserInfo

(

    Userid varchar(20),  UserName varchar(20),

    RegTime datetime, Tel varchar(20),

)

--插入测试数据

DECLARE @I INT

DECLARE @ENDID INT

SELECT @I = 1

SELECT @ENDID = 100  --在此处更改要插入的数据,重新插入之前要删掉所有数据

WHILE @I <= @ENDID

BEGIN

    INSERT INTO T_UserInfo

    SELECT 'ABCDE'+CAST(@I AS VARCHAR(20))+'EF','李'+CAST(@I AS VARCHAR(20)),

       GETDATE(),'876543'+CAST(@I AS VARCHAR(20))

    SELECT @I = @I + 1

END

 

--相关SQL语句解释


--建聚集索引

CREATE CLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

--建非聚集索引

CREATE NONCLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

--删除索引

DROP INDEX T_UserInfo.INDEX_Userid



--显示有关由Transact-SQL 语句生成的磁盘活动量的信息

SET STATISTICS IO ON

--关闭有关由Transact-SQL 语句生成的磁盘活动量的信息

SET STATISTICS IO OFF

--显示[返回有关语句执行情况的详细信息,并估计语句对资源的需求]

SET SHOWPLAN_ALL  ON

--关闭[返回有关语句执行情况的详细信息,并估计语句对资源的需求]

SET SHOWPLAN_ALL  OFF


请记住:SET STATISTICS IO 和 SET SHOWPLAN_ALL 是互斥的。

 

OK,现在开始:

首先,我们插入100条数据

然后我写了一个查询语句:

SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

选中以上语句,按Ctrl+L,如下图

金沙棋牌app手机下载 1 

 

这就是MSSQL的执行计划:表扫描:扫描表中的行

 

然后我们来看该语句对IO的读写:

执行:SET STATISTICS IO ON

此时再执行该SQL:SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

切换到消失栏显示如下:

表'T_UserInfo'。扫描计数1,逻辑读1 次,物理读0 次,预读0 次。

解释下其意思:

四个值分别为:

    执行的扫描次数;

    从数据缓存读取的页数;

    从磁盘读取的页数;

    为进行查询而放入缓存的页数

重要:如果对于一个SQL查询有多种写法,那么这四个值中的逻辑读(logical reads)决定了哪个是最优化的。

 

接下来我们为其建一个聚集索引

执行CREATE CLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

然后再执行SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

切换到消息栏如下显示:

表'T_UserInfo'。扫描计数1,逻辑读2 次,物理读0 次,预读0 次。

此时逻辑读由原来的1变成2,

说明我们又加了一个索引页,现在我们查询时,逻辑读就是要读两页(1索引页+1数据页),此时的效率还不如不建索引。

 

此时再选中查询语句,然后再Ctrl+L,如下图:

金沙棋牌app手机下载 2

** 

聚集索引查找:扫描聚集索引中特定范围的行

说明,此时用了索引。

 

OK,到这里你应该已经知道初步知道MSSQL查询计划和如何查看对IO的读取消耗了吧!

 

 

接下来我们继续:

 

现在我再把测试数据改变成1000条

再执行SET STATISTICS IO ON,再执行

SELECT * FROM T_UserInfo WHERE USERID='ABCDE6EF'

在不加聚集索引的情况下:

表'T_UserInfo'。扫描计数1,逻辑读7 次,物理读0 次,预读0 次。

在加聚集索引的情况下:CREATE CLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

表'T_UserInfo'。扫描计数1,逻辑读2 次,物理读0 次,预读0 次。

(其实也就是说此时是读了一个索引页,一个数据页)

如此,在数据量稍大时,索引的查询优势就显示出来了。

 

 

 

先小总结下

当你构建SQL语句时,按Ctrl+L就可以看到语句是如何执行,是用索引扫描还是表扫描?

通过SET STATISTICS IO ON 来查看逻辑读,完成同一功能的不同SQL语句,逻辑读

越小查询速度越快(当然不要找那个只有几百条记录的例子来反我)。

 

 

我们再继续深入:

OK,现在我们再来看一次,我们换个SQL语句,来看下MSSQL如何来执行的此SQL呢?

现在去掉索引:DROP INDEX T_UserInfo.INDEX_Userid

现在打开[显示语句执行情况的详细信息]:SET SHOWPLAN_ALL  ON

然后再执行:SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

看结果栏:结果中有些具体参数,比如IO的消耗,CPU的消耗。

在这里我们只看StmtText:

SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

  |--Table Scan(OBJECT:([student].[dbo].[T_UserInfo]), WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)))

Ctrl+L看下此时的图行执行计划:

金沙棋牌app手机下载 3

 

我再加上索引:

先关闭:SET SHOWPLAN_ALL OFF

再执行:CREATE CLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

再开启:SET SHOWPLAN_ALL ON

再执行:SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

查看StmtText:

SELECT * FROM T_UserInfo WHERE USERID LIKE 'ABCDE8%'

  |--Clustered Index Seek(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), SEEK:([T_UserInfo].[Userid] >= 'ABCDE8' AND [T_UserInfo].[Userid] < 'ABCDE9'),  WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)) ORDERED FORWARD)Ctrl+L看下此时的图行执行计划:

Ctrl+L看下此时的图行执行计划:

金沙棋牌app手机下载 4 

 

在有索引的情况下,我们再写一个SQL:

SET SHOWPLAN_ALL ON

SELECT * FROM T_UserInfo WHERE LEFT(USERID,4)='ABCDE8%'

查看StmtText:

SELECT * FROM T_UserInfo WHERE LEFT(USERID,4)='ABCDE8%'

  |--Clustered Index Scan(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), WHERE:(substring([T_UserInfo].[Userid], 1, 4)='ABCDE8%'))

Ctrl+L看下此时的图行执行计划:

金沙棋牌app手机下载 5 

 

我们再分别看一下三种情况下对IO的操作

金沙棋牌app手机下载,分别如下:

第一种情况:表'T_UserInfo'。扫描计数1,逻辑读7 次,物理读0 次,预读0 次。

第二种情况:表'T_UserInfo'。扫描计数1,逻辑读3 次,物理读0 次,预读0 次。

第三种情况:表'T_UserInfo'。扫描计数1,逻辑读8 次,物理读0 次,预读0 次。

这说明:

第一次是表扫描,扫了7页,也就是全表扫描

第二次是索引扫描,扫了1页索引,2页数据页

第三次是索引扫描+表扫描,扫了1页索引,7页数据页

[图形界面也有对CPU和IO的消耗,也可以看出来哪个最优!] 

 

通过比较,嘿嘿,很容易的看出:第二种第三种写法在都有索引的情况下,like有效的使用索引,而left则不能,这样一个最简单的优化的例子就出来了,哈哈。

 

 

如果以上你都明白了,那么你可能已经对SQL的优化有初步新的想法了,网上一堆堆的SQL优化的文章真的是那样吗?你自己试试就知道了,而不必盲目去记那些东西,自己试试,看看MSSQL到底是怎么来执行就明白了。

在我举的例子中,用的是聚集索引扫描,字段是字母加数字,大家可以试试看纯数字的、字母的、汉字的等等,了解下MMSQL会如何改变SQL语句来利用索引。然后再试试非聚集索引是什么情况?用不用索引和什么有关?子查询MSSQL是如何执行?IN用不用索引,LIKE用不用索引?函数用不用索引?OR、AND、UNION?子查询呢?在这里我不一一去试给大家看了,只要知道了如何去看MSSQL的执行计划(图形和文本),很多事情就很明朗了。

 

大总结:

实现同一查询功能的SQL写法可能会有多种,如果判断哪种最优化,如果仅仅是从时间上来测,会受很多外界因素的影响,而我们明白了MSSQL如何去执行,通过IO逻辑读、通过查看图示的查询计划、通过其优化后而执行的SQL语句,才是优化SQL的真正途径。

 

另外提醒下:数据量的多少有时会影响MSSQL对同一种查询写法语句的执行计划,这一点在非聚集索引上特别明显,还有就是在多CPU与单CPU下,在多用户并发情况下,同一写法的查询语句执行计划会有所不同,这个就需要大家有机会去试验了(我也没有这方面的太多经验与大家分享)。

 

先写这些吧,由于我对MSSQL认识还很浅薄,如有不对的地方,还请指正。

3.3     、关联查询的优化(使用表扫描)

QualityProblem是一个数据表,QualityProblemDictionary是一个字典表,字典表的数据基本上是固定不变的,大概80行的样子。而在与QualityProblem表关联时,QualityProblem表中符合条件数据的,每一行都会读取一次QualityProblemDictionary字典表。SQL和运行效果如下,现在打算将QualityProblemDictionary字典表一次全读取到内存中,这样就不用每条符合条件的QualityProblem表中数据都去访问QualityProblemDictionary字典表了,修改方法就是在表的Hint中指出不要用索引扫描,要采用表扫描。代码:with(index(0))

 

SELECT [QualityId] ,[ItemId] AS [ID] ,d.Name ,d.FullName ,d.FullPath ,d.Level ,d.ParentId FROM [QualityProblem] as p with(nolock)

join [QualityProblemDictionary] as d with(nolock) on d.Id  = p.ItemId where p.QualityId =  592805 

优化前效果:

(7 row(s) affected)

表'QualityProblemDictionary'。扫描计数7,逻辑读取14 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

表'QualityProblem'。扫描计数1,逻辑读取3 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

 

 SQL Server 执行时间:

   CPU 时间= 15 毫秒,占用时间= 23 毫秒。

 

as d with(index(0),nolock) on d.Id  = p.ItemId

 

优化后效果:

(7 row(s) affected)

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

表'QualityProblemDictionary'。扫描计数1,逻辑读取19 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

表'QualityProblem'。扫描计数1,逻辑读取3 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

 

 SQL Server 执行时间:

   CPU 时间= 0 毫秒,占用时间= 2 毫秒。


 

第二种情况:表'T_UserInfo'。扫描计数1,逻辑读3次,物理读0次,预读0次。

9.3.3、         求总行数的性能优化

1、  连接消除

在求总行数是,凡是仅仅是因为显示数据列需要关联的表,就可以不用再关联了。

2、  排序消除

排序是需要很大消耗的,在求总行数时,不需要再用到排序。

3、  与分页数据查询放在同一个查询中执行,这样可以减少网络连接与传输

第一笔数据就是分页数据,比如:第10条到第20条数据,另外一笔数据是当前查询的总记录数,那么我们可以用一个”;”分号进行SQL来实现一次查询取出2笔数据,然后在C#的DataSet对象中分别从Table[0]和Table[1]中取出这2笔不同的数据。

 

简化后的代码如下:

SELECT COUNT(*)AS TotalCount

 FROM Evaluation evl WITH(NOLOCK) WHERE  evl.RowStatus=0 AND evl.[AutoSeries]=@id and evl.[EditedFeeling]=1 and evl.[Grade]>-10

聚集索引查找:扫描聚集索引中特定范围的行

4.3     、如何解决查询计划不被缓存

只要采用sp_executesql,存储过程,函数这3种方式写的sql查询计划就会被缓存,但也有原因会导致他们不会被缓存,原因如下:

 

  1. 1.          
  2. 2.          
  3. 3.          
  4. 4.          

4.2.          

4.3.          

执行:SETSTATISTICSIOON

1.2     表设计的优化

SETSTATISTICSIOOFF

2.8     索引建立时的开销及注意事项

建索引过程会产生全表锁和建索引过程中会产生全表排序.

后果:普通的对表建索引将会导致针对该表的更新操作无法进行,需要等待索引建完,更新操作将会被建索引的动作阻塞。

 

解决办法:

CREATE NONCLUSTERED INDEX [IX_Auto_Serial] ON [dbo].[Auto]

(

    [Serial] ASC,

    [RowStatus] ASC

)

 WITH (  ONLINE = ON) 

 

在创建索引时,加上online参数,这种建索引的方式不会阻止针对该表的更新操作,与建普通索引相反的是,online建索引的动作是反过来被更新操作阻塞。

从数据缓存读取的页数;

4       查询计划的缓存与CPU高优化

1      

2      

3      

4      

|--Clustered Index Scan(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), WHERE:(substring([T_UserInfo].[Userid], 1, 4)='ABCDE8%'))

4.3.1、         查询计划不被缓存案例示警

上次线上数据库CPU总是高,从DMV中查到编译数过高,原因是开发人员为线上跟踪代码运行结果和查错方便,在公共数据访问层中,自动为每一条SQL中都加了一条注释,该注释内容为当前系统的Url,因为一个公共方法会被不同模块调用,当前模块的Url自然也是不停的在变,导致SQL的查询计划无法重用。

查看StmtText:

3.2     、多表联查的实现原理及表关联字段的设计原则

三大表连接的概要说明:1、Nested Loops Join。2、Hash Join。3、Merge Sort Join

Nested Loops Join驱动结果集的条数决定被驱动表的访问次数

Hash Join两表各自只会访问1次或0次。

Merge Sort Join与Hash Join的相同

 

表驱动顺序与性能(Nested LoopsJoin性能与驱动顺序有关)

Hash Join性能与驱动顺序有关(和NL相似)

Merge Sort Join性能与表驱动顺序无关

 

 

 

Nested Loops Join优化要点

1:驱动表的限制条件要有索引。2、被驱动表限制条件要建立索引。3:确保小结果集先驱动,大的被驱动。

 

Hash Join忧化要点:1:请确保用在全扫描的OLAP场景。2:明确该SQL是否限制Hash Join。3、两表无任何索引倾向HashJoin。第1斧:两表限制条件有索引(看返回量)。第2斧:要小结果集先驱动,大的被驱动。第3斧:尽量保证PGA能容纳Hash运算。

 

Merge Sort Join优化第1式(两表限制条件有索引)

Merge Sort Join 优化第2式(连接条件索引消除排序,即消除2边分别排序,再合并的这种情况)

 

因为被驱动表的查询是依赖与ON 条件中写的字段列,如: A join B on A.CID=B.C ID,

那么应该B表的CID设置为索引列,最好设置为聚集索引列,这样关联SQL在运行时,从B表中查找数据时,可以命中索引,否则在B表中查找数据时,会引发表扫描和Hash匹配。

 

代码示例:

 

优化前的效果

 

(1 row(s) affected)

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

表 'EvaluationPurposeTerms'。扫描计数 1,逻辑读取 1383 次,物理读取 3 次,预读 2269 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

表 'Evaluation'。扫描计数 1,逻辑读取 110 次,物理读取 2 次,预读 80 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

 

(1 row(s) affected)

 

 SQL Server 执行时间:

   CPU 时间 = 140 毫秒,占用时间 = 155 毫秒。

 

 

 

优化后的SQL:

(1 row(s) affected)

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

表 'Evaluation'。扫描计数 1,逻辑读取 20 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒,占用时间 = 4 毫秒。

 

 

 

表'T_UserInfo'。扫描计数1,逻辑读2次,物理读0次,预读0次。

2.3     单列索引的设计与优化

单列索引设计比较简单,一般就是根据业务条件来定义就行,如根据车系ID或车型ID。但要注意索引列的可选择性。

我再加上索引:

9.3.1、         延迟索引

海量数据分页优化的2个重要思想就是:延迟索引和延迟关联。

举例:

select id,name from lx_com join userInfo between 5000000 and 5000010;

这个SQL中,它的工作原理是,他先取500W条,并且因为name字段不是主键,需要不断的回行到磁盘上去取,然后最后返回的是500W到5000010行数据,那么前500W行数据的name,又要扔掉,不返给客户端,这样就形成极大的资源浪费。

 

修改为

With T as (select id from lx_com between 5000000 and 5000010)

select id,name from UserInfo join T on UserInfo.id=T.id

 

具体项目中的应用:

;WITH AllCTE AS( SELECT evl.EvaluationID,ROW_NUMBER() OVER(ORDER BY evl.Grade desc,evl.AppendCount desc,evl.LastAppend desc)

AS RowNum FROM Evaluation evl WITH(NOLOCK)

WHERE  evl.[AutoSeries]=@id),evlCTE AS

(SELECT TOP 100 PERCENT EvaluationID,RowNum FROM AllCTE WHERE RowNum BETWEEN @pagstart AND @pagend ORDER BY RowNum ASC)

在这里我们只看StmtText:

9.2.1、         非关联子查询

非关联子查询的执行,不依赖于外层父查询的任何属性值。这样子查询具有独立性,可独自求解,形成一个子查询计划先于外层的查询求解,如:

 

相关和非相关子询的判断条件就是看子查询是否出现外层父查询中的数据列。

先关闭:SETSHOWPLAN_ALLOFF

9.1     、SQL语句分析方法

SQL分析技巧,SQL可以分为3段:

1、  Select部分,重点关注Select部分有没有标题子查询,有没有自定义函数

2、  From后面,重点关注有没有内联视图,有没有视图,有没有进行视图合并

3、  Where条件部分,看有没有In/Not In,Exists/Not in子查询,有没有外连接,有没有

在列上面有函数导致不能走索引,比如:where len(feeling)>0

内联视图要手工运行返回多少行,子查询也要查看返回多少行。

SELECT@I=@I+1

2       索引优化

解释下其意思:

4.4     、缓存的查询计划什么时候被重新生成

1、当表数据增长率达到当前数据量的30%时,查询计划会被重编译。

2、当新建和修改索引时。

3、当新建和删除字段时。

SETSTATISTICSIOON

9.2.8、         子查询优化—-字段冗余

在Evaluation表新建一个LastAppendingDrivenKilometers字段

用于冗余存放EvaluationAppending表中当前口碑的最后一条追加数据的DrivenKilometers字段数据,代码就简洁多了。

 

然后我写了一个查询语句:

4.5     、In条件参数化   

Declare @User1 int;Declare @User2 int;Declare @User3 int;Declare @User4 int;declare @User5 int;

Where userId in(@User1,@User2,@User3,@User4,@User5)

在不加聚集索引的情况下:

9       SQL语句性能优化

这就是MSSQL的执行计划:表扫描:扫描表中的行

4.2     、如何使查询计划被缓存

  1. 3.          
  2. 4.          

4.2.          

今天来探索下MSSQL的执行计划,来让大家知道如何查看MSSQL的优化机制,以此来优化SQL查询。

6       日志库优化

当有insert和update,delete操作时,会在日志库中记录日志,随着时间的积累,日志库记录越来越多,每插入一条日志都非常耗时,直接影响我们系统的数据操作。

 

解决办法:设定每天晚上自动备份,在数据库备份之后,数据库会自动收缩日志库,删除一些日志数据,这样日志库的数据量就下来了。因为数据库觉得你已经备份过了,万一出什么问题,可以用备份文件来恢复,也不需要依靠日志库来进行还原了,没必要再保存那么多日志了。

此时再选中查询语句,然后再Ctrl+L,如下图:

1.2.7、         数据压缩的利与弊

数据压缩和索引压缩会使存储空间和逻辑读减少,但是会使表更新的开销加大,查询耗费的CPU也更多,所以压缩表一般适合在更新比较少,且CPU消耗不大,IO消耗很大系统中适用。像企业管理软件就比较适用于数据压缩和BI系统。如果当前系统的IO并不高,但CPU非常繁忙,则不应该采用表和索引压缩,传统数据库的压缩率并不是太高,真正压缩率比较高的应该是BI的数据。

第一种情况:表'T_UserInfo'。扫描计数1,逻辑读7次,物理读0次,预读0次。

2.2     表扫描查询如何修改为索引列扫描

如:我们项目中以前用来判断用户是否填写Feeling时,用where len(feeling)>0,len(best)>0,

Len(wrost)>0,

在where条件中用函数会导致表扫描,所以应该设计成标识字段,如IsEditFeeling(bit),IsWroteBest(bit),IsWroteWrost(bit),SQL就可以优化成where IsEditFeeling=1,IsWrostBest=1,IsWroteWrost=1。这样就是索引扫描。

Userid varchar,UserName varchar,

9.2     、子查询优化

子查询的特点:主查询返回一行,子查询就会被执行一次。In语句外面是驱动表,in语句里面是被驱动表。

优化目的:

1、  子查询不用执行很多次

2、  优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序

子查询中的连接条件,过滤条件分别变成了父查询的连接条件,过滤条件,优化器可以对这些条件进行下推,以提高执行效率。

  1. 5.          
  2. 6.          
  3. 7.          
  4. 8.          
  5. 9.          

9.2.          

在加聚集索引的情况下:CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo

1.2.5、         数据库分库

数据库分库:当一个台数据库表服务器访问压力过大,数据量过大时,就需要考虑进行数据库分库,数据库分库条件和表分区的逻辑是比较像的。根据业务条件,如地区,时间,进行拆分。

实现同一查询功能的SQL写法可能会有多种,如果判断哪种最优化,如果仅仅是从时间上来测,会受很多外界因素的影响,而我们明白了MSSQL如何去执行,通过IO逻辑读、通过查看图示的查询计划、通过其优化后而执行的SQL语句,才是优化SQL的真正途径。

7.1     、CPU最大并行度

详细描述文档:

 

看结果栏:结果中有些具体参数,比如IO的消耗,CPU的消耗。

9.2.7、         子查询优化—-Update语句

update table_1 set score = score + 5 where uid in (select uid from table_2 where sid = 10);

其实update也可以用到left join、inner join来进行关联,可能执行效率更高,把上面的sql替换成join的方式如下:

update table_1 t1 inner join table_2 t2 on t1.uid = t2.uid set score = score + 5 where t2.sid = 10;

第二次是索引扫描,扫了1页索引,2页数据页

9.2.6、         关联子查询优化--谓词推入

         谓词推入:当SQL语句中包含有不能合并的视图,并且视图有谓词过滤(也就是where过滤条件),CBO会将where过滤条件推入视图中,这个就叫做谓词推入。谓词推入主要目的就是让SQL Server尽可能早的过滤掉无用的数据,从而提升查询性能。

当SQL语句中,OR条件上面有一个为子查询,并且子查询上的表与源表不同,这个时候就可以用union代替OR或者你发现执行计划中的filter有or并且or后面跟上子查询(Exists)的时候就要注意,比如:

 

修改为:

 

代码示例:

;WITH AppendEvalIDCTE AS( 

   SELECT  EvaluationID

        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY EvaluationID ORDER BY EvaluationAppendingID DESC ) AS RowNum ,

                            EvaluationID ,DrivenKilometers

                  FROM      EvaluationAppending with(nolock)

                  WHERE     RowStatus = 0

                ) T

        WHERE   T.RowNum = 1 AND DrivenKilometers > 5000 AND DrivenKilometers <= 20000

)

, AllEvalIDCTE AS

   SELECT HelpfulCount,LastAppend,Grade,AppendCount,EvaluationID FROM

   (

       SELECT evl.HelpfulCount,evl.LastAppend, evl.Grade ,evl.AppendCount,evl.EvaluationID FROM Evaluation evl

       INNER JOIN [Auto] at WITH ( NOLOCK ) ON at.AutoID = evl.AutoID

        WHERE  evl.[AutoSeries] =@id

              AND  evl.[DrivenKiloms] > 5000 AND evl.[DrivenKiloms] <= 20000

       UNION

        SELECT evl.HelpfulCount,evl.LastAppend, evl.Grade ,evl.AppendCount,evl.EvaluationID FROM Evaluation evl

        INNER JOIN [Auto] at WITH ( NOLOCK ) ON at.AutoID = evl.AutoID

       INNER JOIN AppendEvalIDCTE evlAppend ON evl.EvaluationID = evlAppend.EvaluationID

        WHERE  evl.[AutoSeries] =@id  

              --不能再有了AND  evl.[DrivenKiloms] > 5000 AND evl.[DrivenKiloms] <= 20000

   ) T

)

第三种情况:表'T_UserInfo'。扫描计数1,逻辑读8次,物理读0次,预读0次。

9.2.5、         关联子查询优化--子查询展开

非关联子查询的每次查询都依赖了驱动表的行数据内容,所以想修改为子查询展开方式不太好做,但我们可以从业务上分析,将相关的数据修改为不相关的数据集合,然后再跟不相关的数据集合进行关联,但跟不相关数据集合关联时,要带上条件。

 

代码示例:

and ((evl.[DrivenKiloms] >5000 and evl.[DrivenKiloms] <=20000) or evl.EvaluationID in (select EvaluationID from EvaluationAppending with(nolock) where DrivenKilometers>5000 and DrivenKilometers<20000 and RowStatus=0 and EvaluationAppendingID in (select MAX(EvaluationAppendingID) as EvaluationAppendingID from EvaluationAppending where RowStatus=0 group by EvaluationID)))) as

 

上面的逻辑含义是口碑的驱动公里数>5000 And <=20000,或者最后一条追加口碑的驱动公里数>5000 And <=20000,我们可以一次性把所有口碑的最后一条追加口碑并且是符合驱动公里数>5000 And <20000的条件,保存到临时表或衍生数据表和CTE中,然后用这个临时表或CTE与口碑表进行关联查询,修改后的代码如下:

;WITH AppendEvalIDCTE AS( 

    SELECT  EvaluationID

        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY EvaluationID ORDER BY EvaluationAppendingID DESC ) AS RowNum ,

                            EvaluationID ,DrivenKilometers

                  FROM      EvaluationAppending with(nolock)

                  WHERE     RowStatus = 0

                ) T

        WHERE   T.RowNum = 1 AND DrivenKilometers > 5000 AND DrivenKilometers <= 20000

)

关联代码:

INNER JOIN AppendEvalIDCTE evlAppend ON evl.EvaluationID = evlAppend.EvaluationID

Where  evl.[DrivenKiloms] > 5000 AND evl.[DrivenKiloms] <= 20000

接下来我们继续:

1.2.3、         纵向拆分

 

纵向拆分方法:把不需要用于查询的大字段,放到另外一个新建的附属表中,如feelingStructured表和Auto表。这样就将Evaluation表的数据内容减少到最少,存储块中可以多存储许多数据行,减少程序读取存储块的个数。

 

SELECT*FROMT_UserInfoWHEREUSERIDLIKE'ABCDE8%'

2.5     书签查找优化

2.5.          

--建非聚集索引

9.2.3、         子查询优化方法一(子查询合并)

子查询合并(subquery coalescing)

在某些条件下(语义等价:两个查询块产生同样的结果集),多个子查询能够合并成一个子查询(合并后还是子查询,以后可以通过其他技术消除掉子查询)。这样可以把多次表扫描,多次连接减少为单次表扫描和单次连接,如:

 

可优化为:

 

OK,现在开始:

7       SQLServer服务器系统参数配置与性能优化

我们再分别看一下三种情况下对IO的操作

9.4     、With As与性能优化

对于SELECT查询语句来说,通常情况下,为了使T-SQL代码更加简洁和可读,在一个查询中引用另外的结果集都是通过视图而不是子查询来进行分解的.但是,视图是作为系统对象存在数据库中,那对于结果集仅仅需要在存储过程或是用户自定义函数中使用一次的时候,使用视图就显得有些奢侈了.

    公用表表达式(Common Table Expression)是SQL SERVER 2005版本之后引入的一个特性.CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.

     除此之外,根据微软对CTE好处的描述,可以归结为四点:

  • 可以定义递归公用表表达式(CTE)
  • 当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁
  • GROUP BY语句可以直接作用于子查询所得的标量列
  • 可以在一个语句中多次引用公用表表达式(CTE)
  • 注意:如果with as短语被调用了2次以上,CBO会自动将with as 短语的数据放入一个临时表。

 

在MSDN中的原型:

WITH expression_name [ ( column_name [,...n] ) ]

 

AS

 

( CTE_query_definition )

 

代码示例:

 

--相关SQL语句解释

1       数据表结构的设计与性能优化

接下来我们为其建一个聚集索引

9.2.2、         关联子查询

相关子查询,子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数,当父查询的参数改变时,子查询需要根据新参数值重新执行(查询优化器对相关子查询进行优化有一定意义),如:

 

SELECT@ENDID = 100--在此处更改要插入的数据,重新插入之前要删掉所有数据

4.2.1、         sp_executesql存储过程

普通的即席SQL的查询计划是没有被缓存的,通过下面的示例可以看出来。

 

 

 

 

通过把SQL改写为用sp_executesql存储过程,即可实现查询计划被缓存。ado.net底层就是把我们写的SQL全部生成sp_executesql sql这种格式的sql,但ado.net无法自动区分我们sql中哪些是参数,哪些不是参数,所以光靠ado.net帮我们自动实现sp_executesql还无法实现查询计划的缓存,还需要我们手动把查询条件参数化写完整。最终实现一个标准的语法

Sp_executesql @sqltext,@param1,,,,,,@paramN

 

 

 

 

 

  1. 1.          
  2. 2.          
  3. 3.          
  4. 4.          

4.2.          

4.2.1、          

WHILE@I<=@ENDID

9.3.2、         延迟关联

 SELECT evlCTE.RowNum,evl.AppendCount,evl.EvaluationID,evl.AutoID,at.Brand AS AutoBrand,evl.AutoModel,evl.AutoSeries

                     ,evl.AutoOwner,at.[Level] AS AutoLevel,evl.CommentCount,evl.HelpfulCount

                ,evl.Created,evl.FeelingSummary ,at.BoughtDate AS AutoBoughtDate,at.BoughtProvince AS AutoBoughtProvince

,at.BoughtCity AS AutoBoughtCity, up.MemberId,up.NickName,up.IsAuthenticated,up.Gender,up.HeadImage,up.UserGrade,ps.SpecName

,ps.MinPrice,pss.SeriesName,pss.IsElectric

FROM evlCTE INNER JOIN Evaluation evl WITH(NOLOCK) ON evlCTE.EvaluationID = evl.EvaluationID

INNER JOIN [Auto] at with(nolock) ON at.AutoID = evl.AutoID  INNER JOIN UserProxy up with(nolock) ON up.UserID = evl.AutoOwner

INNER JOIN Product_Spec ps with(nolock) ON ps.SpecId=evl.AutoModel

INNER JOIN Product_Series pss with(nolock) ON pss.SeriesId=evl.AutoSeries ORDER BY evlCTE.RowNum ASC;

END

1.2.1、         字段类型优先级

Bit>int,int>date,time>char,varchar>text,原因:整型,time运算快,节省空间。

所以我们在表设计时,如果是bool类型的数据值就不应该用int类型字段。

再执行:CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo

9.2.9、         子查询优化—-物化视图(索引视图)

物化视图概念:对于涉及对大量的行进行复杂处理的非索引视图,为引用视图的每个查询动态生成结果集的开销会很大。这类视图包括聚集大量数据或联接许多行的视图。若经常在查询中引用这类视图,可通过在视图上创建唯一聚集索引来提高性能。在视图上创建唯一聚集索引时将执行该视图,并且结果集将存储在数据库中,就像带有聚集索引的表一样。

对于涉及对大量的行进行复杂处理的视图,由于结果集已经保存为一张带有聚集索引的表,因此无需重新计算,索引视图有明显的速度优势。

案例分析:

 

 

  SELECT ps.BrandId ,SUM(tfw.SCount) SCount FROM   [Replication].dbo.dxp_SaleData_Cleaned_Count tfw WITH(NOLOCK)INNER JOIN dbo.Product_Series ps WITH ( NOLOCK ) ON ps.SeriesId = tfw.SeriesId

                     WHERE  ( tfw.Syear = @spFromYear AND tfw.Smonth >= @spFromMonth )

                            OR ( tfw.Syear = @spToYear AND tfw.Smonth < @spToMonth)

                            OR ( tfw.Syear > @spFromYear AND tfw.Syear < @spToYear)

                     GROUP BY ps.BrandId) s ON s.BrandId = sbf.BrandId

WHERE   1 = 1 AND sbf.CarType = 1 AND sbf.CountryId IN (1,2,3,4,5,6)

 

(8 行受影响)

表'dxp_SaleData_Cleaned_Count'。扫描计数1,逻辑读取378 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

表'Product_Series'。扫描计数1,逻辑读取10 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

 SQL Server 执行时间:

   CPU 时间= 15 毫秒,占用时间= 15 毫秒。

 

CREATE VIEW v_dxp_SaleData_Cleaned_Count WITH SCHEMABINDING AS

SELECT id,SeriesId,SCount,(Syear*100+Smonth))AS YearMonth

FROM dbo.dxp_SaleData_Cleaned_Count

CREATE  UNIQUE CLUSTERED INDEX v_dxp_SaleData_Cleaned_Count_Idx_ID ON v_dxp_SaleData_Cleaned_Count(id ASC);

CREATE  NONCLUSTERED INDEX v_dxp_SaleData_Cleaned_Count_Idx_YearMonth ON v_dxp_SaleData_Cleaned_Count(YearMonth ASC)INCLUDE(SeriesId,SCount);

 

;WITH CTE AS

(

SELECT ps.BrandId , SUM(tfw.SCount) SCount FROM   [Replication].dbo.v_dxp_SaleData_Cleaned_Count tfw WITH(NOLOCK)

INNER JOIN koubei.dbo.Product_Series ps WITH ( NOLOCK ) ON ps.SeriesId = tfw.SeriesId

WHERE tfw.YearMonth>=201407 AND tfw.yearmonth<201511 GROUP BY ps.BrandId

)

SELECT  sbf.BrandId Id,sbf.BrandName Name,sbf.PPH ,sbf.DefectiveSampleCount ,

        sbf.TroublefreeSamplesCount ,CTE.SCount

FROM   koubei.dbo.Stat_Brand_Fault sbf WITH ( NOLOCK ) INNER JOIN CTE  ON CTE.BrandId = sbf.BrandId

WHERE   1 = 1 AND sbf.CarType = 1 AND sbf.CountryId >=1 AND sbf.CountryId<=6 -- (1,2,3,4,5,6)

 

表'v_dxp_SaleData_Cleaned_Count'。扫描计数1,逻辑读取34 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

表'Product_Series'。扫描计数1,逻辑读取10 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

 

 SQL Server 执行时间:

   CPU 时间= 0 毫秒,占用时间= 5 毫秒。

|--Clustered Index Seek(OBJECT:([student].[dbo].[T_UserInfo].[INDEX_Userid]), SEEK:([T_UserInfo].[Userid] >= 'ABCDE8' AND [T_UserInfo].[Userid] < 'ABCDE9'),WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)) ORDERED FORWARD)Ctrl+L看下此时的图行执行计划:

9.3     、海量数据分页优化

9.3.          

)

7.2     、并行性开销阀值

 


2.5.3、         书签查找优化

 

 

我们再继续深入:

3.1     、字段冗余,不要联查

业务案例分析:在口碑表里冗余新建一个LastAppendingDrivenKilometers字段,这样就不用关联追加表进行查询了。再冗余新建一个AutoBoughtCity字段,就不用关联Auto表进行查询了。

通过SETSTATISTICSIOON来查看逻辑读,完成同一功能的不同SQL语句,逻辑读

4.2.2、         存储过程和函数

存储过程和函数的查询计划也会被缓存。

--关闭有关由Transact-SQL语句生成的磁盘活动量的信息

2.4     多列索引的设计与优化

  1. 2.          

2.2.          

2.3.          

2.4.          

(其实也就是说此时是读了一个索引页,一个数据页)

2.5.1、         数据表物理存储和索引的物理索引

 

 

数据存储块1(8KB)

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

 

 

数据存储块2(8KB)

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

 

 

索引存储块1(8KB)

非聚集索引1(include(区))

非聚集索引2

非聚集索引N

非聚集索引1

非聚集索引2

非聚集索引N

非聚集索引1

非聚集索引2

非聚集索引N

非聚集索引1

非聚集索引2

非聚集索引N

 

因为索引里面只存储了固定了几列,如果查询时需要读取索引列之外的数据,就需要到数据存储块,根据聚集索引去重新查找我们想要的数据,这就叫书签查找。

另外提醒下:数据量的多少有时会影响MSSQL对同一种查询写法语句的执行计划,这一点在非聚集索引上特别明显,还有就是在多CPU与单CPU下,在多用户并发情况下,同一写法的查询语句执行计划会有所不同,这个就需要大家有机会去试验了。

2.5.2、         书签查找的缺点

 

 

 

 

找出书签查找中查找的列

 

(

2.4.1、         多列索引的存储规则

假设某个表有一个联合索引(a,b,c),我们来看下在这个索引中是如何存储这些字段数据的。

 

 

 

 

为进行查询而放入缓存的页数

1.2.2、         聚集索引字段类型的选择

 

 

主键的索引结构中,即存储了主键值,又存储了行数据,这种结构称为“聚集索引”

在插入数据时,数据节点会分裂,这个问题比较严重,节点下存储了“行数据”,分裂的时候,还要移动行数据。如果主键是无规律的,则会加速它的数据节点分裂,且效率极低。

 

高性能索引策略:

主键,尽量用整型,而且是递增的整型。如果是无规律的数据,将会产生页的分裂,影响速度。索引长度直接影响索引文件大小,影响增删改的速度,并间接影响查询速度(占用内存多)。因为主键索引在物理存放时是有序的,如果主键的值是无序的,那么主键每次插入时,索引文件都重新进行排序,会产生额外的数据消耗,另外主键的叶子上存放的数据,还会导致叶子数据行的移动和分裂,又会产生一些消耗,所以主键尽量用整型,且自增的类型。


5       并发和阻塞问题

1、减少事务长度。

2、SQL语句加Nolock。

3、读写分离

Ctrl+L看下此时的图行执行计划:

8       DB服务器监控

参考附件

 

数据性能调校--查出最耗资源的各种SQL.docx

 

 

在我举的例子中,用的是聚集索引扫描,字段是字母加数字,大家可以试试看纯数字的、字母的、汉字的等等,了解下MMSQL会如何改变SQL语句来利用索引。然后再试试非聚集索引是什么情况?用不用索引和什么有关?子查询MSSQL是如何执行?IN用不用索引,LIKE用不用索引?函数用不用索引?OR、AND、UNION?子查询呢?在这里我不一一去试给大家看了,只要知道了如何去看MSSQL的执行计划,很多事情就很明朗了。

4.1     、概述

我们SQL执行分为2部分,1是SQL查询计划编译部分,2是SQL运行部分。SQL分析器里显示的CPU时间也是包括编译时间和运行时间。

 

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒,占用时间= 0 毫秒。

SQL Server 执行时间:

   CPU 时间= 2668 毫秒,占用时间= 786 毫秒。

 

因为生成SQL的查询计划是非常耗时和耗CPU的,SQL Server在生成查询计划之后,会将已经生成的查询计划保存到计划缓存中,下次再执行该SQL时不用再重新生成一个全新的计划。

如果以上你都明白了,那么你可能已经对SQL的优化有初步新的想法了,网上一堆堆的SQL优化的文章真的是那样吗?你自己试试就知道了,而不必盲目去记那些东西,自己试试,看看MSSQL到底是怎么来执行就明白了。

2.6     索引与排序的优化

索引在物理存储上是有序的,所以如果我们的SQL的排序是基于索引列进行的,那么不需要再重新进行排序,反之,系统会在temp库中建立表变量或临时表,然后在这个表变量或临时表中重新进行排序。如果想为多表联查时,基于多个表做排序字段建立索引,如Order by A.Id,B.Created,则可以基于2个表建立一个物化视图,然后在这个物化视图上为这2列建立索引。

Create index idx_t on t(col1 desc,col2 asc)

Select * from t order by col1 desc,col2 asc

0:sorts(memory)

0:sorts(disk)

 

SQL Server 执行时间:

   CPU 时间= 2182 毫秒,占用时间= 231 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒,占用时间= 0 毫秒。

 

SQL Server 执行时间:

   CPU 时间= 0 毫秒,占用时间= 2 毫秒。

SQL Server 分析和编译时间:

   CPU 时间= 0 毫秒,占用时间= 0 毫秒。

现在去掉索引:DROPINDEXT_UserInfo.INDEX_Userid

2.1     索引列的设计与优化

索引列数据的重复度称为可选择性,如性别列的取值范围为”男,女”,这个索引列可选择性就比较低,你在里面能找出太多相同的数据列出来,如选择列数据内容为唯一的,则可选择性非常高。我们选择索引列时,要尽量选择高选择性的列。

案例分析:现在有一个商家黑盒处罚功能,假如商家发布违规的商品和促销信息,则会被系统管理员设置为5天或10天的墨盒状态,墨盒状态期间无法发布商品和促销信息,现在设计有商家黑盒表,有3字段,BusinessID(商家ID), 生效开始时间(StartTime)、生效结束时间(EndTime),这生效开始时间和生效结束时间2个列谁的选择性高呢?

示例业务数据:

BusinessID

StartTime

EndTime

11111

2014-3-1

2014-3-5

223333

2015-4-8

2015-4-13-

23423424

2016-1-13

2016-1-18

 

现在找出今天还属于黑盒的商家名单列表

 

Where startTime<=’2016-1-14’ and EndTime>=’2016-1-14’

 

大家可以看到,符合startTime<=’2016-1-14’的有3条记录,而符合EndTime>=’2016-1-14’的只有1条记录,所以EndTime的可选择性比StartTime高,这就会决定,我们到底是采用哪一列做索引列,如果2个列都做索引列,哪个索引列会排在前面(多列索引的设计见下面)。

此时再执行该SQL:SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

1.2.4、         横向拆分

横向拆分方法:表分区,表分区的条件,一张数据表的行数至少要达3000W行以上的数据,就可以考虑做表分区了。但这不是绝对,如果表的数据行内容特别多,查询特别慢时,也可以尽早做表分区。

注意问题:普通表在查询时,会比分区表要快一些,因为基于分区表的查询会遍历所有的分区表,而普通表只查询了普通表一个表。

解决办法,在查询条件中加入分区条件,这样查询就会落入指定的分区中,不用遍历所有的分区,但问题是,是不是所有的查询都能加入分区条件呢。只要进行了表分区,那么SQL的前提条件就是所有SQL都要加上分区条件,除非个别的汇总,统计类的SQL。

 

谈到优化就必然要涉及索引,就像要讲锁必然要说事务一样,所以你需要了解一下索引,仅仅是索引,就能讲半天了,所以索引我就不说了,可以去参考相关的文章,这个网上资料比较多了。

7.4     、数据库文件布局

将数据库文件分别存放在不同的服务器上,以加快并行处理速度。

 

--DROP TABLE T_UserInfo----------------------------------------------------

9.2.4、         非关联子查询优化--子查询展开

子查询展开(subquery unnesting)。

又称子查询反嵌套,又称为子查询上拉。

把一些子查询置于外层的父查询中,作为连接关系与外层父查询并列,其实质是把某些子查询重写为等价的多表连接操作(展开后,子查询不存在了,外部查询变成了多表连接)。

带来的好处是,有关的访问路径,连接方法和连接顺序可能被有效使用,使得查询语句的层次尽可能的减少。

WHERE  evl.[AutoSeries] =@id and evl.EvaluationID in (select EvaluationID from EvaluationPurposeTerms ept with(nolock) where ept.PurposeID=1)

 

修改为

INNER JOIN EvaluationPurposeTerms ept ON evl.EvaluationID = ept.EvaluationID

WHERE  evl.[AutoSeries] =@id and ept.PurposeID=1

 

注意:其实子查询和联合查询中的嵌套循环查询的底层思路是一样的,都是驱动表查询被驱动表,嵌套循环的伪代码如下:

 

那我们为什么还要修改子查询为关联查询呢?因为带来的好处是,有关的访问路径,连接方法和连接顺序可能被有效使用,使得查询语句的层次尽可能的减少。在子查询中,In语句外固定是驱动表,In语句内固定是被驱动表,如果驱动表返回1W条记录,那被驱动表则会被查询1W次。而关联查询时,驱动表和被驱动表的关系是根据谁返回数据行的大小来动态决定的,如A Join B,B返回10行,A返回1W行,则SQL底层执行时会将B作为驱动表,而A作为被驱动表,这样B执行一次查询,A执行10次查询,反过来如果A in(B),则A执行一次查询,B执行1W次查询。

 

代码示例2:

and evl.[AutoModel] in (601,1473,1474,1993,1994,1995,2743,2750,2751,2752,3681,3682,3683,3684,5405,5819,5910,5911,10665,10666,10667,13444,13481,13482,13483,13484,13485,15377,15379,17807,17808,17809,17810,17811,17812)

 

修改方法:SQL中关联Product_Spec表,

INNER JOIN dbo.Product_Spec prod_Spec ON prod_Spec.SpecId=evl.AutoModel

WHERE prod_Spec.SyearId=452 AND SpecState=(销售状态)

此时逻辑读由原来的1变成2,

2.7     关于索引的坏处

在有几个索引和情况下和只有主键索引的情况下,插入数据的速度相差10倍,而且数据表里数据记录越大,插入速度越明显。

在无索引的情况下,表的记录越大,插入的速度只会受到很小的影响,基本不会越慢。

在无索引的情况下,分区表的插入要比普通表更慢,因为插入的数据需要做判断,有这方面的开销。

解决办法:读写分离,在主库(或叫写库)上只有主键索引,而没有别的索引。只读库上有许多用于查询的索引,然后写库的数据定时同步到只读库上,这样的话,插入时不会因为索引的原因导致插入变慢,也不会因为没有索引导致查询变慢。

SELECT'ABCDE'+CAST+'EF','李'+CAST,

3       多表联查优化

选中以上语句,按Ctrl+L,如下图

7.3     、优化即席工作负载

 

--建测试表

2.4.3、         多列索引列的选择优化

根据以上的多列索引列的存储规则和左前缀规则,在建多列索引时,应该将可选择项高的列放在最左边,后面依次类推,如上面的黑盒案例分析中,应该将endTime列放在最左边,然后才是starttime列。

 

在选择性相同的情况下,应该把等值(如:=)的放在左边,不等值(如:>,<)放在后面。

Select * from t where object_id>=20 andobject_id<2000 and object_type=’TABLE’;

这个查询对应的索引,应该建成

Create index idx_id_type on t(object_type,object_id);

 

查看StmtText:

2.4.2、         多列索引左前缀规则

 

多列索引必须用到第1个,否则不生效。

 

 

CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo

1.2.6、         读索引为什么比读表快

这里面引发出一个触类旁通的问题,为什么索引查询会比直接查数据要快?因为索引做为一个独立的数据存储区,也是跟数据表存储块一样,以8KB为一个存储块,一个IO读取一次存储块,而索引中只有简单的几个索引列,而不是整个数据行的数据,所以它一个IO读取的数据会非常多,这样它的IO就会非常少,加快了查询速度。

RegTimedatetime,Tel varchar,

1.1     、数据表的存储原理

SQL Server每次读取1个存储块,每个存储块大小为8KB,每读取1个存储块计算为1个逻辑读。

问题:如果数据内容非常大,像我们系统中的Feeling字段非常大,就会导致每个存储块存放的数据行数会非常少,这样当我们读取数据时,要读取许多的存储块。

 

存储块1(8KB)

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

 

 

存储块2(8KB)

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

聚集索引

数据内容

 


首先,我们插入100条数据

切换到消息栏如下显示:

当你构建SQL语句时,按Ctrl+L就可以看到语句是如何执行,是用索引扫描还是表扫描?

分别如下:

--插入测试数据

|--Table Scan(OBJECT:([student].[dbo].[T_UserInfo]), WHERE:(like([T_UserInfo].[Userid], 'ABCDE8%', NULL)))

表'T_UserInfo'。扫描计数1,逻辑读2次,物理读0次,预读0次。

BEGIN

先小总结下

INSERTINTOT_UserInfo

DECLARE@ENDIDINT

切换到消失栏显示如下:

OK,到这里你应该已经知道初步知道MSSQL查询计划和如何查看对IO的读取消耗了吧!

SELECT*FROMT_UserInfoWHERELEFT='ABCDE8%'

现在打开[显示语句执行情况的详细信息]:SETSHOWPLAN_ALLON

重要:如果对于一个SQL查询有多种写法,那么这四个值中的逻辑读(logical reads)决定了哪个是最优化的。

CREATETABLET_UserInfo

SETSHOWPLAN_ALLOFF

然后再执行:SELECT*FROMT_UserInfoWHEREUSERIDLIKE'ABCDE8%'

第三次是索引扫描+表扫描,扫了1页索引,7页数据页

GETDATE(),'876543'+CAST

SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

这说明:

执行的扫描次数;

从磁盘读取的页数;

表'T_UserInfo'。扫描计数1,逻辑读7次,物理读0次,预读0次。

越小查询速度越快(当然不要找那个只有几百条记录的例子来反我)。

SELECT@I=1

再执行SETSTATISTICSIOON,再执行

说明我们又加了一个索引页,现在我们查询时,逻辑读就是要读两页,此时的效率还不如不建索引。

再开启:SETSHOWPLAN_ALLON

SELECT*FROMT_UserInfoWHEREUSERIDLIKE'ABCDE8%'

CREATENONCLUSTEREDINDEXINDEX_UseridONT_UserInfo

--关闭[返回有关语句执行情况的详细信息,并估计语句对资源的需求]

DROPINDEXT_UserInfo.INDEX_Userid

如此,在数据量稍大时,索引的查询优势就显示出来了。

在有索引的情况下,我们再写一个SQL:

--显示有关由Transact-SQL语句生成的磁盘活动量的信息

大总结:

网上的SQL优化的文章实在是很多,说实在的,我也曾经到处找这样的文章,什么不要使用IN了,什么OR了,什么AND了,很多很多,还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么,让许多人不知道其是对还是错。而SQL优化又是每个要与数据库打交道的程序员的必修课,所以写了此文,与朋友们共勉。

现在我再把测试数据改变成1000条

SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

Ctrl+L看下此时的图行执行计划:

[图形界面也有对CPU和IO的消耗,也可以看出来哪个最优!]

再执行:SELECT*FROMT_UserInfoWHEREUSERIDLIKE'ABCDE8%'

SETSHOWPLAN_ALLON

说明,此时用了索引。

OK,现在我们再来看一次,我们换个SQL语句,来看下MSSQL如何来执行的此SQL呢?

通过比较,嘿嘿,很容易的看出:第二种第三种写法在都有索引的情况下,like有效的使用索引,而left则不能,这样一个最简单的优化的例子就出来了,哈哈。

Ctrl+L看下此时的图行执行计划:

先写这些吧,由于我对MSSQL认识还很浅薄,如有不对的地方,还请指正。

表'T_UserInfo'。扫描计数1,逻辑读1次,物理读0次,预读0次。

--建聚集索引

SETSHOWPLAN_ALLON

然后再执行SELECT*FROMT_UserInfoWHEREUSERID='ABCDE6EF'

请记住:SETSTATISTICSIO和SETSHOWPLAN_ALL是互斥的。

--显示[返回有关语句执行情况的详细信息,并估计语句对资源的需求]

--删除索引

SELECT*FROMT_UserInfoWHERELEFT='ABCDE8%'

执行CREATECLUSTEREDINDEXINDEX_UseridONT_UserInfo

第一次是表扫描,扫了7页,也就是全表扫描

然后我们来看该语句对IO的读写:

DECLARE@IINT

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:执行计划及Sql查询优化初探_MsSql_脚本之家,执行

关键词: