本文正在参加「技术专题19期 漫谈数据库技术」活动
前言
之前因为做过一段时间后端，后面因公司需要转成了前端，把我知道的一些 SQL 优化分享给大家。
优化一小步，积小成多成为一大步。
实战优化
1、尽量避免直接查询全表，这样增加了查询的网络传输量，如：
select * from t
可以筛选出自己需要的列：
select id,name,age from t
2、尽量避免在where子句中对字段进行 null 值判断，这样导致引擎放弃使用索引进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num 列没有null值，然后这样查询：
select id from t where num=0
3、尽量避免在 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
4、尽量避免like左模糊和全模糊，这样会导致引擎放弃索引而进行全表扫描，如：
select id from t where name like '%abc%'
select id from t where name like '%abc'
这样查询不会进行全表扫描
select id from t where name like 'abc%'
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、尽量避免在where子句中对字段进行表达式操作，这样会导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
可以这么查询:
select id from t where num=100*2
7、尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
可以这么查询:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
8、尽量避免使用子查询。如：
select id,name from tab1 where id (select id from tab2 where name = 'ming');
可以使用 inner join连接语句：
select id,name from tab1 t1 inner join tab2 t2 on t1.id=t2.id where b.name='ming'
9、很多时候用 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)
常见优化要领
- 尽量避免在
where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 group by语句要先过滤后分组。- 不要超过5个以上的表连接。
- 一个表的索引最好不要超过5个。
select 的执行顺序
select 执行的两个顺序：
- 关键字顺序不能颠倒：
select ... from ... where ... group by ... having ... order by ...
- SQL 语句执行顺序不能乱：
--顺序
from > where > group by > having > select 字段 > distinct > order by > limit
--案例
select distinct id, name, count(*) as num --顺序5
from tab1 JOIN tab2 ON tab1.id = tab2.id --顺序1
where height > 1.80 --顺序2
group by tab1.id --顺序3
having num > 2 --顺序4
order by num desc --顺序6
limit 2 --顺序7
关键字说明
- From 表
- Where 条件过滤
- Group by 分组
- Having 过滤函数结果
- Select 列
- Distinct 去重
- Limit 取数据（可以提高数据传输效率）
在select执行步骤的时候，每个步骤都会产生一个虚拟表，然后把这个虚拟表出入下一个步骤作为输出。这些步骤在 SQL的执行过程中，对于使用者来说是不可见的。
总结
我们 SQL 优化的内容其实不少，但是我把一些我从前用过的，或者比较容易理解的部分整理了一下。
SQL 优化简单来说其实就两点：
-
尽可能避免全表扫描。
-
尽量减少无效数据的查询。
本文正在参加「技术专题19期 漫谈数据库技术」活动
