引言：　　

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

sql语句执行顺序：　　

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

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)        ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)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 500 * 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语句

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有