好的语句就像辆车，跑的又快又帅气！今天这里介绍一些技巧让你可以改装一下自己的车！网上确实有好多好多好多好多SQL 语句优化的文章，什么 优化大全 ，100个优化注意 ，确实整理了好多好多。那么为什么我也要凑热闹写一篇呢？ 好吧我也不知道！
重中之重—语句执行顺序

在QQ群和人聊天的时候突然有位群友说：我才知道原来语句走索引是按照select 的字段筛选的！ 振振有词，非常肯定！另一个群友反问update呢 ？ 看起来很小白的问题，但确实让我很震惊！所以我们先看看语句的执行顺序

如果我没记错这是《SQL SERVER 2005技术内幕–查询》这本书的开篇第一章第一节。书的作者也要让读者首先了解语句是怎么样的一个执行顺序，因为不知道顺序何谈写个好语句？

查询的逻辑执行顺序：

(1) FROM

(3)   JOIN    (2) ON

(4) WHERE

(5) GROUP BY

(6) WITH {cube | rollup}

(7) HAVING

(8) SELECT  (9) DISTINCT

(10) ORDER BY

标准的SQL 的解析顺序为:

(1).FROM 子句 组装来自不同数据源的数据

(2).WHERE 子句 基于指定的条件对记录进行筛选

(3).GROUP BY 子句 将数据划分为多个分组

(4).使用聚合函数进行计算

(5).使用HAVING子句筛选分组

(6).计算所有的表达式

(7).使用ORDER BY对结果集进行排序

执行顺序：

1.FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1

2.ON:对vt1表应用ON筛选器只有满足 为真的行才被插入vt2

3.OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2 生成t3如果from包含两个以上表则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束

4.WHERE：对vt3应用 WHERE 筛选器只有使 为true的行才被插入vt4

5.GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5

6.CUBE|ROLLUP：把超组(supergroups)插入vt6 生成vt6

7.HAVING：对vt6应用HAVING筛选器只有使 为true的组才插入vt7

8.SELECT：处理select列表产生vt8

9.DISTINCT：将重复的行从vt8中去除产生vt9

10.ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

11.TOP：从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者

我们了解了sqlserver执行顺序，请以前不知道的看官们，反复试验反复记忆！那么我们就接下来进一步养成日常sql好习惯，也就是在实现功能的同时又考虑性能的思想！

设计思路

具体写法的优化请不要着急，那都是小儿科！

设计思路说的有点大了，下面介绍几个最常见的设计问题！

循环改批量

循环单条操作，请改成批量操作，如果没办法修改，请尽量想办法修改！这算是最常见的吧：

• 应用代码端一记 for 循环再恶心点的每次打开关闭连接，跑个几分钟，数量大点几小时。请把你的每次for循环出来的结果放在一个datatable，list啥的，不要找到一条就往数据库写一条！
• 数据库中的游标也是差不多的道理，如果有可能不用游标循环一条一条处理，请尽量不要使用。如果自己认为必须用，也请问问别人是否可以有其他方式做批量！
• 如果没法避免一条一条的写入，那么在处理前显示开启一个事务 begin tran  在处理完成后 commit 这样也要比不开显示事务会快很多！
  

上个小例子：

create table test_0607 (a int,b nvarchar(100))

declare i int

set i = 1

while i < 10000

begin

insert into test_0607

select @i,'0607无显示整体事务'

set i = i + 1

end

对比


drop table test_0607
create table test_0607 (a int,b nvarchar(100))

---加上事务
begin tran
declare i int
set i = 1
while i < 10000
begin
insert into test_0607
select @i,'0607 显示整体事务'
set i = i + 1
end
----结束事务，提交
commit

结果 ： 8秒和0.8秒的区别，不用多说啥了吧！ 凡事有利有弊，这种显示开启大事务要保证的整体的过程不会执行特别长的时间，如果执行的操作特别多而且时间长就是灾难了！

降低语句复杂性

前文语句优化三板斧中已经介绍过，降低语句复杂性是常见的优化方式。这里在说一下，导致语句特别复杂一般有两个原因：

• 程序逻辑本身就很复杂，需要很多表连接，又要排序又要聚合，时不时来几个子查询，外加几个函数。
• 由于业务有很大的共性，所以创建出很多视图，甚至视图嵌套很多层视图，最后外层又要关联单个模块的特殊性表。
  

对于第一种情况，代码看起来就很长很复杂，看起来很牛逼的代码其实在高手看来都是很LOW的。而对于第二种，看起来代码很简洁，但经过SQL优化器的二次编译，其实和第一种并无区别。这两种的解决办法都是降低复杂性，把一些能拆分出来的尽量拆分出来放入临时表或者表变量中，比如先把条件筛选性较强的几张表关联，然后把结果放入临时表，在用临时表和其他表关联。可以理解成我有10张表关联，我先拿5张表出来关联，然后把结果放入临时表，再跟另外5张表关联。这样这个查询的复杂度由10张表的联合变成 5+6，这样降低了复杂语句复杂度。

复杂视图也是如此，在视图和外层关联前，放入临时表，再跟外层关联。

子查询也是如此，可以分离出来成为临时表的子查询，先分离出来。

避免重复读取

曾经遇到过很多这样的程序，类似对商品有多种分析，而每种分析要做一些不同的处理，但是他们都会读取同一份基础数据商品和商品明细等。很多程序都是按照每种分析作为一个单独的存储过程去处理，那么也就是说有20种处理他们创建了20个存储过程，并且每个存储过程的第一步，就是先读取基础数据–商品和明细等等。不巧的是商品和商品明细有巨大的数据量，虽然做了分表（按照月份，每个表大概2QW数据），但是每个存储过程要读取一年的数据，大概是2QW * 12 ，这么庞大的数据巨量，查询后被放入一张temp表，20个存储过程顺序执行，也就是说这份基础数据每天晚上会被查询20次！ 基本上这个处理占据了系统夜间维护的所有时间，有时甚至会跑不完影响白天正常业务！

也许你看完描述就会笑，谁会把处理设计成这个样子？这不开玩笑么？没错，解决这个问题其实超简单，把20个存储过程合成一个。让基础数据的查询只查询一次，放入临时表，创建出下面逻辑处理需要的索引，在用这个临时表分别做下面所有的处理。这样一个夜间需要跑6小时以上的处理被缩短成40分钟！（当然说的有点夸张，里面还有些其他的优化，√）

这里就提到一个使用临时表比较重要的问题，那就是类似上面的大量数据写入临时表，一定要用 先create 再 insert 的方式，不要直接使用 select into 临时表的方式，否则就是灾难了！

论索引的重要性

老生常谈的话题了，我想所有公司招人的时候都会问到这样的面试题： 什么是索引，索引有哪些类，有何不同？等等….

索引是啥？什么是聚集索引？什么是非聚集索引？什么是主键查找？什么是主键扫描？什么是索引查找？什么是书签查找？有啥区别？ 这里都不介绍，请自行百度！

很多开发人员意识不到索引到底对语句，甚至对系统有对重要。关于索引对系统的重要性请关注后续文章。

减少不必要的操作

写语句之前，理清你的思路！

• 杜绝不必要的表连接，多一个表链接代表多很大部分开销。
• 减少不必要的条件判断，很多时候前台传入为空值得时候 后台语句被写成XX=XX OR XX IS NULL OR XX LIKE OR …OR …OR 等。这是比较经典的问题了，请加入判断在拼入最后的条件！
• 你的语句需要去重复么？ distinct 、union等操作
• LEFT JOIN 和 inner join的区别，是否真的需要left join，否则选用inner join 来减少不必要的数据返回。
• order by 你的语句是否需要排序？排序是否可以通过索引来降低性能消耗？ 我见过竟然插入数据也带着order by的 ！
  

尽量早的筛选

• 最经典的例子就是where 和 having的区别，看过语句执行顺序你应该已经明白了。能写在where 中不要放在having中。
• 使用临时表降低语句复杂性，要降低临时表的数据量，也就是要把有条件的表尽量关联并做成临时表。
• 前面提到的隐式转换，索引字段使用计算或函数，也会导致数据不能尽早筛选。
  

常用的写法误区（以下都是网上片面结论）所有别人提到的方法到底有无效

• or 要用union all 代替 （or是很常规的一种写法，情况分很多种，一个表的两个条件用  a.a =X or a.a = XX ，一个表两个字段用 a.a =X or a.b = x，两个不同表字段用 a.a = X or b.a = X 这是网上说的union all代替的）
• 避免使用 in、not in （数据量小的时候不会有问题，如果数据量大可能影响性能，数据量大处理方式先把in 中的数据放入临时表）
• 事务操作过程要尽量小，能拆分的事务要拆分开来。（前文中提到的例子，有些情况循环写入下，显示开启一个大事务会有很大帮助）
• 使用with（nolock）查询语句不会阻塞 （一般情况下是这样，但是如果有架构修改或快照发布等使用with（nolock）也会阻塞）
• 用exists 代替 in （情况也很复杂不能一概而论）
  

总结 ： 就写到这里吧，说道语句优化，有太多太多的注意，这些需要明白原理，能看懂执行计划，并且不断积累。

好的语句就像辆车，跑的又快又帅气！今天这里介绍一些技巧让你可以改装一下自己的车！网上确实有好多好多好多好多SQL 语句优化的文章，什么 优化大全 ，100个优化注意 ，确实整理了好多好多。那么为什么我也要凑热闹写一篇呢？ 好吧我也不知道！
重中之重—语句执行顺序

在QQ群和人聊天的时候突然有位群友说：我才知道原来语句走索引是按照select 的字段筛选的！ 振振有词，非常肯定！另一个群友反问update呢 ？ 看起来很小白的问题，但确实让我很震惊！所以我们先看看语句的执行顺序

如果我没记错这是《SQL SERVER 2005技术内幕–查询》这本书的开篇第一章第一节。书的作者也要让读者首先了解语句是怎么样的一个执行顺序，因为不知道顺序何谈写个好语句？

查询的逻辑执行顺序：

(1) FROM

(3)   JOIN    (2) ON

(4) WHERE

(5) GROUP BY

(6) WITH {cube | rollup}

(7) HAVING

(8) SELECT  (9) DISTINCT

(10) ORDER BY

标准的SQL 的解析顺序为:

(1).FROM 子句 组装来自不同数据源的数据

(2).WHERE 子句 基于指定的条件对记录进行筛选

(3).GROUP BY 子句 将数据划分为多个分组

(4).使用聚合函数进行计算

(5).使用HAVING子句筛选分组

(6).计算所有的表达式

(7).使用ORDER BY对结果集进行排序

执行顺序：

1.FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1

2.ON:对vt1表应用ON筛选器只有满足 为真的行才被插入vt2

3.OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2 生成t3如果from包含两个以上表则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束

4.WHERE：对vt3应用 WHERE 筛选器只有使 为true的行才被插入vt4

5.GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5

6.CUBE|ROLLUP：把超组(supergroups)插入vt6 生成vt6

7.HAVING：对vt6应用HAVING筛选器只有使 为true的组才插入vt7

8.SELECT：处理select列表产生vt8

9.DISTINCT：将重复的行从vt8中去除产生vt9

10.ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

11.TOP：从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者

我们了解了sqlserver执行顺序，请以前不知道的看官们，反复试验反复记忆！那么我们就接下来进一步养成日常sql好习惯，也就是在实现功能的同时又考虑性能的思想！

设计思路

具体写法的优化请不要着急，那都是小儿科！

设计思路说的有点大了，下面介绍几个最常见的设计问题！

循环改批量

循环单条操作，请改成批量操作，如果没办法修改，请尽量想办法修改！这算是最常见的吧：

• 应用代码端一记 for 循环再恶心点的每次打开关闭连接，跑个几分钟，数量大点几小时。请把你的每次for循环出来的结果放在一个datatable，list啥的，不要找到一条就往数据库写一条！
• 数据库中的游标也是差不多的道理，如果有可能不用游标循环一条一条处理，请尽量不要使用。如果自己认为必须用，也请问问别人是否可以有其他方式做批量！
• 如果没法避免一条一条的写入，那么在处理前显示开启一个事务 begin tran  在处理完成后 commit 这样也要比不开显示事务会快很多！
  

上个小例子：

create table test_0607 (a int,b nvarchar(100))

declare i int

set i = 1

while i < 10000

begin

insert into test_0607

select @i,'0607无显示整体事务'

set i = i + 1

end

对比


drop table test_0607
create table test_0607 (a int,b nvarchar(100))

---加上事务
begin tran
declare i int
set i = 1
while i < 10000
begin
insert into test_0607
select @i,'0607 显示整体事务'
set i = i + 1
end
----结束事务，提交
commit

结果 ： 8秒和0.8秒的区别，不用多说啥了吧！ 凡事有利有弊，这种显示开启大事务要保证的整体的过程不会执行特别长的时间，如果执行的操作特别多而且时间长就是灾难了！

降低语句复杂性

前文语句优化三板斧中已经介绍过，降低语句复杂性是常见的优化方式。这里在说一下，导致语句特别复杂一般有两个原因：

• 程序逻辑本身就很复杂，需要很多表连接，又要排序又要聚合，时不时来几个子查询，外加几个函数。
• 由于业务有很大的共性，所以创建出很多视图，甚至视图嵌套很多层视图，最后外层又要关联单个模块的特殊性表。
  

对于第一种情况，代码看起来就很长很复杂，看起来很牛逼的代码其实在高手看来都是很LOW的。而对于第二种，看起来代码很简洁，但经过SQL优化器的二次编译，其实和第一种并无区别。这两种的解决办法都是降低复杂性，把一些能拆分出来的尽量拆分出来放入临时表或者表变量中，比如先把条件筛选性较强的几张表关联，然后把结果放入临时表，在用临时表和其他表关联。可以理解成我有10张表关联，我先拿5张表出来关联，然后把结果放入临时表，再跟另外5张表关联。这样这个查询的复杂度由10张表的联合变成 5+6，这样降低了复杂语句复杂度。

复杂视图也是如此，在视图和外层关联前，放入临时表，再跟外层关联。

子查询也是如此，可以分离出来成为临时表的子查询，先分离出来。

避免重复读取

曾经遇到过很多这样的程序，类似对商品有多种分析，而每种分析要做一些不同的处理，但是他们都会读取同一份基础数据商品和商品明细等。很多程序都是按照每种分析作为一个单独的存储过程去处理，那么也就是说有20种处理他们创建了20个存储过程，并且每个存储过程的第一步，就是先读取基础数据–商品和明细等等。不巧的是商品和商品明细有巨大的数据量，虽然做了分表（按照月份，每个表大概2QW数据），但是每个存储过程要读取一年的数据，大概是2QW * 12 ，这么庞大的数据巨量，查询后被放入一张temp表，20个存储过程顺序执行，也就是说这份基础数据每天晚上会被查询20次！ 基本上这个处理占据了系统夜间维护的所有时间，有时甚至会跑不完影响白天正常业务！

也许你看完描述就会笑，谁会把处理设计成这个样子？这不开玩笑么？没错，解决这个问题其实超简单，把20个存储过程合成一个。让基础数据的查询只查询一次，放入临时表，创建出下面逻辑处理需要的索引，在用这个临时表分别做下面所有的处理。这样一个夜间需要跑6小时以上的处理被缩短成40分钟！（当然说的有点夸张，里面还有些其他的优化，√）

这里就提到一个使用临时表比较重要的问题，那就是类似上面的大量数据写入临时表，一定要用 先create 再 insert 的方式，不要直接使用 select into 临时表的方式，否则就是灾难了！

论索引的重要性

老生常谈的话题了，我想所有公司招人的时候都会问到这样的面试题： 什么是索引，索引有哪些类，有何不同？等等….

索引是啥？什么是聚集索引？什么是非聚集索引？什么是主键查找？什么是主键扫描？什么是索引查找？什么是书签查找？有啥区别？ 这里都不介绍，请自行百度！

很多开发人员意识不到索引到底对语句，甚至对系统有对重要。关于索引对系统的重要性请关注后续文章。

减少不必要的操作

写语句之前，理清你的思路！

• 杜绝不必要的表连接，多一个表链接代表多很大部分开销。
• 减少不必要的条件判断，很多时候前台传入为空值得时候 后台语句被写成XX=XX OR XX IS NULL OR XX LIKE OR …OR …OR 等。这是比较经典的问题了，请加入判断在拼入最后的条件！
• 你的语句需要去重复么？ distinct 、union等操作
• LEFT JOIN 和 inner join的区别，是否真的需要left join，否则选用inner join 来减少不必要的数据返回。
• order by 你的语句是否需要排序？排序是否可以通过索引来降低性能消耗？ 我见过竟然插入数据也带着order by的 ！
  

尽量早的筛选

• 最经典的例子就是where 和 having的区别，看过语句执行顺序你应该已经明白了。能写在where 中不要放在having中。
• 使用临时表降低语句复杂性，要降低临时表的数据量，也就是要把有条件的表尽量关联并做成临时表。
• 前面提到的隐式转换，索引字段使用计算或函数，也会导致数据不能尽早筛选。
  

常用的写法误区（以下都是网上片面结论）所有别人提到的方法到底有无效

• or 要用union all 代替 （or是很常规的一种写法，情况分很多种，一个表的两个条件用  a.a =X or a.a = XX ，一个表两个字段用 a.a =X or a.b = x，两个不同表字段用 a.a = X or b.a = X 这是网上说的union all代替的）
• 避免使用 in、not in （数据量小的时候不会有问题，如果数据量大可能影响性能，数据量大处理方式先把in 中的数据放入临时表）
• 事务操作过程要尽量小，能拆分的事务要拆分开来。（前文中提到的例子，有些情况循环写入下，显示开启一个大事务会有很大帮助）
• 使用with（nolock）查询语句不会阻塞 （一般情况下是这样，但是如果有架构修改或快照发布等使用with（nolock）也会阻塞）
• 用exists 代替 in （情况也很复杂不能一概而论）
  

总结 ： 就写到这里吧，说道语句优化，有太多太多的注意，这些需要明白原理，能看懂执行计划，并且不断积累。