SQL性能优化

时间:2021-12-04 03:02:08

引言:  

  以前在面试的过程中,总有面试官问道:你做过sql性能优化吗?对此,我的答复是没有。一次没有不是自己的错误,两次也不是,但如果是多次呢?今天痛下决心,把有关sql性能优化的相关知识总结一下,以便在不久的将来,我的回答不是“没有”,总能多多少少说一些东西。算是长进吧。说到性能优化,本人感觉到有必要先了解sql语句的执行顺序,因为对优化或多或少的会有些帮助。

sql语句执行顺序:  

  sql语句和其他相关的编程语言最大不同的地方应该是执行顺序。对于大多数编程语言来说都是按照顺序进行执行,但对于sql语句,尽管select是最开始出现,但几乎总是最后一个执行,最开始执行的往往是from子句。每一步骤产生一个虚拟表,这些虚拟表对于调用者来说是不能用的,仅仅作用于下一步骤,而只有最后的查询结果表才能被调用者所使用。当有步骤没有出现时便跳过该执行步骤。下面上代码:

()SELECT ()DISTINCT  ()<Top Num> <select list>
()FROM [left_table]
()<join_type> JOIN <right_table>
() ON <join_condition>
()WHERE <where_condition>
()GROUP BY <group_by_list>
()WITH <CUBE | RollUP>
()HAVING <having_condition>
()ORDER BY <order_by_list>

逻辑查询处理阶段简介:

1)from:对FROM子句中的前两个表执行笛卡尔积(Cartesian product)(交叉联接),生成虚拟表VT1

2)on:对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2

3)outer(join):如 果指定了OUTER JOIN(相对于CROSS JOIN 或(INNER JOIN),保留表(preserved table:左外部联接把左表标记为保留表,右外部联接把右表标记为保留表,完全外部联接把两个表都标记为保留表)中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3,直到处理完所有的表为止。

4)where:对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.

5)group by:按GROUP BY子句中的列列表对VT4中的行分组,生成VT5.

6)cube|roolup:把超组(Suppergroups)插入VT5,生成VT6.

7)having:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.

8)select:处理SELECT列表,产生VT8.

9)distinct:将重复的行从VT8中移除,产生VT9.

10)order by:将VT9中的行按ORDER BY 子句中的列列表排序,生成游标(VC10)

11)top:从VC10的开始处选择指定数量或比例的行,生成表VT11,并返回调用者。

注:

步骤10,按ORDER BY子句中的列列表排序上步返回的行,返回游标VC10.这一步是第一步也是唯一一步可以使用SELECT列表中的列别名的步骤。这一步不同于其它步骤的 是,它不返回有效的表,而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序,它只是成员的逻辑集合,成员的顺序无关紧要。对表进行排序 的查询可以返回一个对象,包含按特定物理顺序组织的行。ANSI把这种对象称为游标。理解这一步是正确理解SQL的基础。

因为这一步不返回表(而是返回游标),使用了ORDER BY子句的查询不能用作表表达式。表表达式包括:视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。

  在SQL中,表表达式中不允许使用带有ORDER BY子句的查询,而在T—SQL中却有一个例外(应用TOP选项)。所以要记住,不要为表中的行假设任何特定的顺序。换句话说,除非你确定要有序行,否则不要指定ORDER BY 子句。排序是需要成本的,SQL Server需要执行有序索引扫描或使用排序运行符。

  sql语句执行时是按照从右到左的顺序处理from子句中的表名,from子句中写在最后的表也即是基础表将被最先处理,因此在from子句中包含多个表的情况下,选择记录条数最少的表作为基础表,在某种程度上将会极大的提高其性能。如果有3个以上的表,则选择交叉表作为基础表。此处对性能优化来说相当重要。

  

执行计划:

说完执行顺序后,便讨论下执行计划:

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式。

可见,执行计划并不是固定的,它是“个性化的”。产生一个正确的“执行计划”有两点很重要:

(1)    SQL语句是否清晰地告诉查询优化器它想干什么?

(2)    查询优化器得到的数据库统计信息是否是最新的、正确的?

优化检测工具:

  基础知识介绍完毕了,开始性能优化,但是我们怎么才能知道该系统中的那些sql语句应该进行性能优化,该语句是否应该进行系统优化,查看相关资料,针对sqlserver,找到sqlserver数据库对应的有个sql server profiler,使用该工具可以找到针对某个数据库表来说,有什么样的操作行为拉低了其性能。

打开系统主菜单--sqlserver几---性能工具--->>sql server profiler;

然后文件--新建跟踪--显示跟踪属性窗口;

首先那个select%是个筛选监测的TextData。那个%是个通配符,他的意思就是筛选select开口的语句。当然这你自己可以随便定义,如update%,delete%....。

把那个排除不包含值的行也给带上,然后确定,运行。然后在数据库中运行一句select。你会发现他检测到啦。

1.查找持续时间最长的查询

一般情况下,最长查询时间的查询语句就是最影响性能的原因存在。它不仅占用数据库引擎大量的时间,还浪费系统资源,还影响数据库应用系统的交互速度。再对数据用应用系统进行优化时,先找出他,对其优化,在创建跟踪时,勾上TSQL-SQL:BatchCompleted.跟Stored Procedures-RPC:completed。这样就能找出来这个最长时间查询然后对其进行分析优化。

select TextData,Duration,CPU from <跟踪的表>
where EventClass=12 -- 等于12表示BatchCompleted事件
and CPU<(0.4*Duration) --如果cpu的占用时间,小于执行sql语句时间的40%,说明该语句等待时间过长

2.最占用系统资源的查询

就是占用cpu时间,跟读写IO的次数。建议事件包含Connect、Disconnect、ExistingConnection、SQL:BatchCompleted、RPC:completed,列包含writes,reads,cpu。

3.检测死锁

在访问量,并发量都很大的数据库中,如果设计稍不合理,就有可能造成死锁,给系统性能带来影响。事件包含:RPC:Starting、SQL:BatchStarting、Lock:DeadLock(死锁事件)、Lock:DeadLockChaining(死锁的事件序列)。

查阅SqlServer性能检测和优化工具使用详细

数据库引擎优化顾问

和sql server profiler相对于的有个“数据库引擎优化顾问”,也是一个与性能优化有关的工具,可以抽时间了解了解。了解后再补充吧。

sql性能优化常见经验:

下面总结下载网上各个大牛们认为进行sql优化应该操作的事项:

1、模糊查询like。

使用like进行模糊查询时应该特别注意,这个很基本,基本上大家都知道。呵呵

select * from contact where username like ‘%yue%’

关键词%yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%。

2、where条件查询

尽量避免使用in,not in,having,可以使用 exist 和not exist代替 in和not in。不要以字符格式声明数字,要以数字格式声明字符值。

3、前面提到的from子句中有多个表进行关联查询时

在from子句中包含多个表的情况下,选择记录条数最少的表作为基础表,在某种程度上将会极大的提高其性能。如果有3个以上的表,则选择交叉表作为基础表

4、select *查询

尽量不要使用

select * from tablename

取而代之的则是:

select columnname1,columnname2 from tablename

5、排序操作

避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

6、索引表操作

对于此处,个人还没有弄明白,首先对于索引还不明白,那么性能优化更谈不上了。反正很多大牛都是操作索引表,需要特别注意。以后明白了再补充吧。

...

7、LEFT JOIN 和 inner join的区别,是否真的需要left join,否则选用inner join 来减少不必要的数据返回。

个人因为编程习惯问题,总喜欢写left join,看来以后要用大脑思考思考了。

同时,SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8、统一规范sql语句

编写规范的sql语句,这一点是最重要的一点,不管对于系统还是个人来说,都是相当的重要。

不规范的有:

很复杂的sql语句,对于编写者自己都晕了。

大小写随意编写,对于系统来说是个小麻烦。

肯定还有,就是平时多注意就ok了。

补充:

记录一次现场数据优化实例:

很多人有过类似的经历,项目在本地代码运行没有问题,本地测试没有问题,但系统发布到现场报错。最近本人遇到过类似的场景,简单描述整个历程。

因为是升级系统,老的文件先进行备份,现场实施替换成新文件后报错,于是要求把新文件发回本地在代码下运行,一番测试下显示没有问题。此时显得束手无策了,看后台日志显示超时,超时?因为涉及到http请求,如果数据量过大的话的确会超时。于是把请求过程中用的sql语句拿到数据库中专门执行,查询时间大于1分钟,此时问题就明了了,sql语句的问题。本地的环境和现场环境根本不可能相同,在本地不做压力测试的情况下,很多隐藏问题都没有暴露出来,由此在项目开展中不会那么顺利的。

Top n

Sql问题,看以前类似的sql语句,发现都使用了top n,于是sql语句加上top 200,因为取前200条记录已经可以满足业务需求,在现场的测试环境下使用数据库直接执行sql语句,执行时间在20s以上,如果把http请求超时时间设置的大些还是能满足要求的,但现场实际的业务场景根本不可能让你如此进行,这个sql仅仅是一个数据源,还有2个类似的数据源需要执行,那么20s显然不能满足。

索引

加索引,本地模拟现场的业务场景,插入了大量的测试数据,在sql的where条件查询字段下加了索引,查询时间进入到秒级,完全满足项目要求。现场提供的视图,而且视图的厂家没有人维护了,不可能创建其它东西的,所以虽然索引有效但是无法使用。

参数

现场系统可以通过配置参数来对业务进行调整,执行的sql语句中加入了@参数Name=@Name or @Name = '',上网经过搜索,发现参数不会对sql执行造成影响,但是如果你的where条件中的@参数正好加入了索引,那么影响就相当显著了。加入强制执行索引:

with(index(IX_Name)),效率有显示提升,奈何现场的视图已无参加维护。

Join

查询数据源采用了left join联表查询,问题来了,主表2w多行的数据,副表也是3w多行的数据,比较奇葩的使用了两个视图联表查询,还是那句没有厂家维护。联表查询n*m,那么减少基础表的记录数目可以有效的提高效率。那么把条件搜索放入到基础表先进性过滤,然后再进行联合查询。

select top  * from

(select  * from  [dbo].[table1] where (ss between @a1 and @a2)) a

 LEFT JOIN  dbo.[table2] ON a.m = dbo.[table2].n

参考:

SqlServer性能检测和优化工具使用详细

高手详解SQL性能优化十条经验

优化SQL查询:如何写出高性能SQL语句