sql查询速度慢如何优化（sql查询速度慢如何优化）

系统业务量越大，数据库数据量越多，原先效率很高的SQL，查询速度会越来越慢，尤其是复杂的多表关联查询，因此我们很有必要针对SQL进行优化，下面潘老师来说下SQL优化查询速度常用的几种方法。

一、为什么要进行SQL优化

主要原因其实也很简单，就是用户操作页面功能发现要等好久才能刷出数据来，而其背后的原因可能如下：

• 1、硬件配置差
• 2、网络传输不稳定
• 3、后台业务处理复杂
• 4、数据库数据量大
• 5、数据库架构和表设计不合理
• 6、SQL写的太差
• ….等等

但一般80%的原因都是因为数据库性能差，查询慢，而80%的数据库性能问题都是因为SQL导致的，数据库架构和表设计那是DBA的事，我们作为后端工程师，应该着重考虑如何优化SQL以提高查询效率。

二、优化SQL的意义

• 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
• 优化效果：硬件<系统配置<数据库表结构<SQL及索引。

从上面看出，SQL及索引优化是成本最低且效果最好的一种方式。

三、查询SQL的执行过程

在执行SQL优化之前，我们必须清楚SQL的执行过程；

以MySQL为例，MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行,是需要经过一系列复杂的流程,最终转变成二进制的机器码,才能被执行的,我们需要对执行的SQL进行优化,那么就有必须先来了解下，一个SQL语句的执行有哪些主要环节，以查询的SQL来举例

1）通过网络的通讯协议接收客户端传入的SQL
2）查看该SQL对应的结果在查询缓存中是否存在
a.存在则直接返回结果
b.不存在则继续往下走
3）由解析器来解析当前SQL,最终形成初步的解析树
4）再由预处理器对解析树进行调整,完成占位符赋值等操作
5）查询优化器对最终的解析树进行优化,包括调整SQL顺序等
6）根据优化后的结果得出查询语句的执行计划,就是查询数据的具体实施方案,交给查询的执行引擎
7）查询执行引擎调用存储引擎提供的API,最后由存储引擎来完成数据的查询,然后返回结果

四、SQL的执行顺序

我们写SQL时的顺序如下：

select … from … join … on … where … group by … having … order by … limit …

数据库解析SQL时的顺序如下：

from … on … join … where … group by … having … select … order by … limit …

五、SQL优化的一些方法

1、EXPLAIN

做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据：

• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
• key列，使用到的索引名。如果没有选择索引，值是NULL。
• key_len列，索引长度。不损失精确性的情况下，长度越短越好
• rows列，扫描行数。该值是个预估值。
• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

针对explain命令生成的执行计划，这里有一个查看心法。我们可以先从查询类型type列开始查看，如果出现all关键字，后面的内容就都可以不用看了，代表全表扫描。再看key列，看是否使用了索引,null代表没有使用索引。然后看rows列，该列用来表示在SQL执行过程中被扫描的行数，该数值越大，意味着需要扫描的行数越多，相应的耗时越长，最后看Extra列，在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现，这些是很影响数据库性能的。

2、避免使用select *

任何地方都不要使用 select * from t ，用具体的字段列表代替*，用哪些字段就写哪些字段，不要返回用不到的任何字段。
原因如下：

• SELECT * 会增加很多不必要的消耗（CPU、IO、内存、网络带宽），
• 增加了使用覆盖索引的可能性；
• 当表结构发生改变时，前端也需要更新。

所以要求直接在select后面接上字段名。

3、区分in和exists

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键）。如果是exists，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。 比如：

# in
select num from a where num in(select num from b)
# 可以使用exists代替
select num from a where exists(select 1 from b where num=a.num)

4、in 和 not in 也要慎用，否则会导致全表扫描

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

select id from t where num in(1,2,3)
# 使用如下代替：
select id from t where num between 1 and 3

如果一定要使用in，那么in包含的值不应过多。

5、避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。针对为null的数据可以给个默认值代替。

6、应尽量避免在 where 子句中使用!=或操作符

where 子句中使用!=或操作符，将导致引擎放弃使用索引而进行全表扫描，可以使用 or 代替。

select id from t where num != 10
# 使用or代替
select id from t where num <10 or num >10

7、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替or会得到更好的效果。

select id from t where num=50 or num=100
# 代替写法
select id from t where num=50
union all
select id from t where num=100

8、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

9、避免在where子句中对字段进行表达式操作

比如下面的SQL中对字段就行了算术运算，这会造成引擎放弃使用索引，

# 表达式运算导致放弃索引
select username,salary from user_base where salary*2=20000
# 可以这样替换
select username,salary from user_base where salary=20000/2

10、尽量避免在where子句中对字段进行函数操作

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

# 比如
select id from t where substring(name,1,3)='abc'； --name以abc开头的id
# 可以使用如下代替
select id from t where name like 'abc%'

11、不建议使用%前缀模糊查询

比如like '%abc'或者like '%abc%'，这种查询会导致索引失效而进行全表扫描。但是可以使用like 'abc%'。

12、避免隐式类型转换

隐式转换导致索引失效，这一点应当引起重视，也是开发中经常会犯的错误，比如is_success是varchar(1)类型，如果你传数值型的1或0，数据库会隐士转换为字符串形势的'1'或'0'则会导致索引失效。

# 比如
select id from t where is_success = 1
# 替换优化
select id from t where is_success = '1'

13、如果排序字段没有用到索引，就尽量少排序

14、对于联合索引(组合索引)来说，要遵守最左前缀法则

多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。例如组合索引（a,b,c），组合索引的生效原则是：
从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
比如：

#这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and b=4 and c=5
#这种情况下b就是断点，a发挥了效果，c没有效果
where a=3 and c=5
#这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=3 and c=4
# 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关
where b=4 and a=3 and c=5

# 比如
select id,name from product limit 50000, 10

select id,name from product where id> 50000 limit 20

#比如：
select id from t order by rand() limit 1000;
#上面的SQL语句，可优化为：
select id from t t1 join (select rand() * (select max(id) from t) as nid) t2 on t1.id > t2.nid limit 1000;

SELECT * FROM table1 CROSS JOIN table2 
SELECT * FROM table1 JOIN table2 
SELECT * FROM table1 , table2

select *,if(a.date is null,b.date,a.date) as date from a left join b on a.date = b.date  -- 只有a的全部
union -- union上面的复制下来left改为right，连个表头要一致
select *,if(a.date is null,b.date,a.date) as date from a right join b on a.date = b.date

//例: user表10000条数据，class表20条数据
select * from user u left join class c u.userid=c.userid

explain select * from user u left join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id 
 WHERE 1=1 ORDER BY u.create_time DESC limit 0,10

//优化第一步：LEFT JOIN改为JOIN，让较少的数据表作为驱动表
explain select * from user u join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id 
 WHERE 1=1 ORDER BY u.create_time DESC limit 0,10
//优化第二步：从上面执行计划得知， 有Using temporary(临时表);Using filesort，解决方法是调整排序字段（借助前面讲过排序的原则）
explain select * from user u join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id 
 WHERE 1=1 ORDER BY c.id DESC limit 0,10

随机文章