前面一篇文章从实例的角度进行数据库优化，通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢，影响业务流程。本文从SQL角度进行数据库优化，提升SQL运行效率。




判断问题SQL

判断SQL是否有问题时可以通过两个表象进行判断：


	
系统级别表象

	

		
CPU消耗严重
		
		

		
IO等待严重
		
		

		
页面响应时间过长
		
		

		
应用的日志出现超时等错误
		
	
可以使用sar命令，top命令查看当前系统状态。

也可以通过Prometheus、Grafana等监控工具观察系统状态。（感兴趣的可以翻看我之前的文章）



	
SQL语句表象

	

		
冗长
		
		

		
执行时间过长
		
		

		
从全表扫描获取数据
		
		

		
执行计划中的rows、cost很大
		
	
冗长的SQL都好理解，一段SQL太长阅读性肯定会差，而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手，如下所示：

执行计划告诉我们本次查询走了全表扫描Type=ALL，rows很大(9950400)基本可以判断这是一段"有味道"的SQL。

获取问题SQL

不同数据库有不同的获取方法，以下为目前主流数据库的慢查询SQL获取工具


	

	
MySQL
	

	

		
慢查询日志
		
		

		
测试工具loadrunner
		
		

		
Percona公司的ptquery等工具
		
	
	

	

	
Oracle
	

	

		
AWR报告
		
		

		
测试工具loadrunner等
		
		

		
相关内部视图如v$、$session_wait等
		
		

		
GRID CONTROL监控工具
		
	
	

	

	
达梦数据库
	

	

		
AWR报告
		
		

		
测试工具loadrunner等
		
		

		
达梦性能监控工具（dem）
		
		

		
相关内部视图如v$、$session_wait等
		
	
SQL编写技巧

SQL编写有以下几个通用的技巧：

• 合理使用索引


索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；

一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况


• 使用UNION ALL替代UNION


UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序


• 避免select * 写法


执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。


• JOIN字段建议建立索引


一般JOIN字段都提前加上索引


• 避免复杂SQL语句


提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理


• 避免where 1=1写法

• 避免order by rand()类似写法


RAND()导致数据列被多次扫描


SQL优化

执行计划

完成SQL优化一定要先读执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以MYSQL为例，看看执行计划是什么。（每个数据库的执行计划都不一样，需要自行了解）explain sql

字段
			
解释
		
id
			
每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
		
select_type
			
查询中每个select 字句的类型
		
table
			
被操作的对象名称，通常是表名，但有其他格式
		
partitions
			
匹配的分区信息(对于非分区表值为NULL)
		
type
			
连接操作的类型
		
possible_keys
			
可能用到的索引
		
key
			
优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
		
key_len
			
被优化器选定的索引键长度，单位是字节
		
ref
			
表示本行被操作对象的参照对象，无参照对象为NULL
		
rows
			
查询执行所扫描的元组个数（对于innodb，此值为估计值）
		
filtered
			
条件表上数据被过滤的元组个数百分比
		
extra
			
执行计划的重要补充信息，当此列出现Using filesort , Using temporary 字样时就要小心了，很可能SQL语句需要优化
		
接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。

优化案例


	

	
表结构

	
CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);

	
	
	

	

	
三张表关联，查询当前用户在当前时间前后10个小时的订单情况，并根据订单创建时间升序排列，具体SQL如下

	
select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

	
	
	

	

	
查看数据量  

	

	
	
	

	

	
原执行时间

	
	
	

	

	
原执行计划

	
	
	

	

	
初步优化思路
	
	

	
SQL中 where条件字段类型要跟表结构一致，表中user_id 为varchar(50)类型，实际SQL用的int类型，存在隐式转换，也未添加索引。将b和c表user_id 字段改成int类型。
	
	

	
因存在b表和c表关联，将b和c表user_id创建索引
	
	

	
因存在a表和b表关联，将a和b表seller_name字段创建索引
	
	

	
利用复合索引消除临时表和排序
	

初步优化SQL

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);




查看优化后执行时间





查看优化后执行计划




查看warnings信息




继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;



查看执行时间





查看执行计划




总结



	
查看执行计划 explain
	
	

	
如果有告警信息，查看告警信息 show warnings;
	
	

	
查看SQL涉及的表结构和索引信息
	
	

	
根据执行计划，思考可能的优化点
	
	

	
按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
	
	

	
查看优化后的执行时间和执行计划
	
	

	
如果优化效果不明显，重复第四步操作
	
 

系列文章

 

数据库优化 - 实例优化 
	
记一次对达梦数据库的优化过程
 









温馨提示





如果你喜欢本文，请关注我的个人公众号！或者关注我的个人博客www.javadaily.cn









 


 

一、为什么要对SQL进行优化

我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

二、SQL优化的一些方法

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。    

    

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：    

select id from t where num is null    

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    

select id from t where num=0    

    

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。    

    

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：    

select id from t where num=10 or num=20    

可以这样查询：    

select id from t where num=10    

union all    

select id from t where num=20    

    

5.in 和 not in 也要慎用，否则会导致全表扫描，如：    

select id from t where num in(1,2,3)    

对于连续的数值，能用 between 就不要用 in 了：    

select id from t where num between 1 and 3    

    

6.下面的查询也将导致全表扫描：    

select id from t where name like '%abc%'    

    

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    

select id from t where num/2=100    

应改为:    

select id from t where num=100*2    

    

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：    

select id from t where substring(name,1,3)='abc'--name以abc开头的id    

应改为:    

select id from t where name like 'abc%'    

    

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。    

    

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。    

    

11.不要写一些没有意义的查询，如需要生成一个空表结构：    

select col1,col2 into #t from t where 1=0    

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：    

create table #t(...)    

    

12.很多时候用 exists 代替 in 是一个好的选择：    

select num from a where num in(select num from b)    

用下面的语句替换：    

select num from a where exists(select 1 from b where num=a.num)    

    

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。    

    

14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，    

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。    

一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。    

    

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。    

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。    

    

16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，    

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。    

    

17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。    

    

18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。    

    

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，    

以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。    

    

22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。    

    

23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

 

 

 

一、为什么要对SQL进行优化

项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序

运行效率的影响不太明显，而我们也无法判断SQL对程序的运行效率

有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增

多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很

有必要。

二、SQL优化的一些方法

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而

进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索

引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用

索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引

而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'–name以abc开头的id

应改为:

select id from t where name like 'abc%'

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，

否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索

引中的第一个字段作为条件时才能保证系统使用该索引，

否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

12.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当

索引列有大量数据重复时，SQL查询可能不会去利用索引，

如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也

对查询效率起不了作用。

14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降

低了 insert 及 update 的效率，

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，

视具体情况而定。

一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的

索引是否有必要。

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会

降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字

型而言只需要比较一次就够了。

16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节

省存储空间，

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要

返回用不到的任何字段。

18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当

需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好

使用导出表。

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，

以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然

后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先

truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，

那么就应该考虑改写。

23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决

问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD

游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数

据时。

在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间

允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更

好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合

理。

面试中经常问到有没有做过Sql优化，每次都感觉是直接到数据优化的层次，都说没有，在此总结一下，主要就是针对查询

1、慢查询

2、索引

3、拆分表

全文索引

主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.

select * from articles where match(title,body) against(‘database’); 【可以】

唯一索引

unique空串（null）可以放多个 如果是具体的内容则不能重复

a: 在where条经常使用 

b: 该字段的内容不是唯一的几个值(sex)  （只有三个数据形成2级二叉树）

c: 字段内容不是频繁变化. 

查询优化

1.尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描：可以使用联合查询 union all

5.in 和 not in 也要慎用，否则会导致全表扫描，在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替

6.模糊查询like，关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%；“yue%”走索引

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

如：select id from t where age/2 = 10

应改为:select id from t where num = 10*2

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

如：

select id from t where substring(name,1,3) = ’abc’ #name以abc开头的id

select id from t where datediff(day,createdate,’2019-03-09′) = 0 #‘2019-03-09’ --生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate >= '2019-03-09' and createdate < '2019-03-10'

9.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致

10.与having比较，尽量使用where ,where 先过滤（数据就少了）在分组

11.理论上，尽量使用多表连接（join）查询（避免子查询）

 

2. 尽量使用列名，不要使用*