目录
sql优化的本质是减少 io、cpu 资源的消耗,让 sql 执行的更快,最终达到我们性能要求。
以下是我们经过 sql 优化实践提炼总结的要点:
1 业务逻辑优化,减少数据查询范围
# 以 in 为例,阿里巴巴规约建议批量查询控制在200以内
select * from 表名 where id in (1,3,,5,7 ......)
2 所取即所用,避免出现 select *
所取即所用,就是我们需要什么字段就取什么字段,避免 select * 。
为什么避免 select * 呢?假设我们的业务表有50个字段,select * 表示一次性取这50个字段,这50个字段在网络中传输需要占用很大带宽,影响传输速度。
3 合理建立索引,善于使用索引
- 选择表中字段区分度高的列建立索引。
- 优先考虑在 where 和 order by 涉及的列上建立索引。
# name在user表的区分度高,可以给name添加索引
select name from user where name = "张三";
# age建立索引,因为age字段有序,可以减少排序的消耗
select name from user order by age limit 20;
# (name,age)建立联合索引
select name,age from user where name = "张三" order by age limit 20;
4 避免在索引列上做运算或函数操作
在索引列上做运算或者函数操作,将导致索引失效。
select id from user where age/2 = 5;
5 避免 like 使用,一定要用,字段加上索引
select id from user where name like 'jim%'
6 建议使用 join 替代子查询
# 不建议
select a.name from a where a.id in (select rid from b)
# 建议
select a.name from a left join b on a.id = b.rid;
7 尽量避免使用 join
原则上避免使用 join,如果一定需要使用,建议关联字段要有索引,优先选择小表作为驱动表。
select a.name from a join b on a.id = b.rid where a.id = 9;
8 DML要短小
所谓的DML要短小,什么意思呢,举例来说,我们需要更新1万条数据,我们可以分成10次更新,每次1千条,执行10次,为啥要这么做呢?一次更新如果长时间持有锁,会阻塞其他sql,影响系统性能,而分成多次的话,将大大降低锁冲突的概率。
update user set b = 1 where id between 1 and 1000;
update user set b = 1 where id between 1001 and 2000;
......
update user set b = 1 where id between 9001 and 10000;
9 善用 force 字段
你可能遇到过 sql 没有走对索引的问题,这个时候就可以使用 force 让 sql 走索引。
select a.name from user force index(索引);
不要滥用 force,只有在 sql 没有走索引的时候,而我们需要让 sql 走索引的时候使用。