15个SQL优化的技巧

1.不要使用select *，要使用 select 指定列

• 直接使用select * 会导致将数据表中所有的列都查询出来，多查询出来的数据会浪费CPU、内存等资源，多查出来的数据通过网络进行传输，也会增加网络IO的传输时间。
• select * 不会走覆盖索引(除非一个表中所有的字段都建立了索引，当然这种情况一般不可能) ，如果select *走了非主键索引的情况下，那就只能通过二级索引查找到主键上的值，再根据查找到的主键值，去回表查询数据，也就是会将会发生大量的回表操作。
• 而且select * 比select 指定列在解析时，要多解析出查询的是哪些列这一步骤
• 重构数据库时（对数据库的结构进行改动），可能与应用程序中的字段产生不一致。例如，mybatis中的resultMap结果集映射或者是与实体类去映射。解决方法：可以利用视图来解决。
• 可能对数据的安全有影响。假如我们有一个类包含账户、密码等，使用select *可能导致用户的信息泄露。或者是后期添加了一些私密的信息。解决方法：可以利用视图来解决。
  如何优化呢？那就是查询时，只查使用的列，不使用的列，不需要去查询，如：

select name, age from person where age = 18

2.使用union all，不要使用union

因为使用union操作，会将数据结果进行去重，在去重时，会将所有的数据进行排序、遍历和比较，如果数据本身就没有重复的话，那么这个步骤就是多余的步骤，或者说如果业务场景下，不在乎数据是否重复，那么就尽可能的使用union all操作
如:

(select name from person where age = 19)
union all
(select name from person where age = 20)

3. 小表驱动大表

用小表数据集去驱动大表数据集
口诀：

• in适用于左边大表，右边小表。因为执行该类查询时，会先执行in里面的子查询，再执行外面的查询，所以应该让in里面的子查询是一个小表。
• exists适用于左边小表，右边大表。因为执行该类查询时，会先执行exists左边的查询，用查询的结果去挨个匹配右边的条件，所以exists应该左边的查询是小表。

Demo: Order表中有10000行数据，User表中有100行数据
查询所有有效的用户，下过的订单列表，则应该使用如下sql:

select * from order where user_id in (select user_id from user where status = 1);

而不应该使用下面查询语句：

select * from order where exists (select 1 from user where order.user_id = user.user_id and status = 1)

4.批量操作

如果有一批数据需要插入到数据库中，不要分别的单条插入，而是使用批量插入的方法
如：

insert into person (age, name) values (18, '张三'), (19, '李四'), (20, '王五');

而一次批量操作的数据也不要太多，建议一次批量插入500行数据就可以

5. 多用limit

如果查询一个用户下的第一笔订单的时间，则使用limit 1，而不是查询全部的订单，再在代码中获取第一个
如下：

select * from order where user_id = 1 order by create_time desc limit 1;

6. in中的条件值不要太多

如 select * from person where id in (1, 2, 3, 4, 5…100000)
in后面跟的值太多了，不建议这样做
可以的做法是select * from person where id in (1, 2, 3, 4, 5…100000) limit 500
提示客户端一次性查询的条数，不能超过500条，或者是使用多线程，将这批值使用多线程的方式查询出来，然后进行数据的汇总合并。

7. 增量查询

有时候需要同步数据到别的数据库表中，就会想到使用select * from person 将表中的所有数据查询出来之后，再导入到别的表，这种做法虽然简便， 但是查询性能却会非常差。
正确的做法是：

select * from person
where id > #{lastId}
and create_time > #{lastCreateTime}
limit 100

使用这种增量查询的方式，每次查询100条数据，然后进行插入到数据库表中，每一次的lastId都是上一批查询的数据的最大值

8. 使用limit进行分页

使用分页时，如果能知道上一页的id值，而查询下一页的时候，可以使用

# 使用下面的sql要求该id是连续的，并且有序的
select * from person where id > 100000 limit 20;
# 不要使用下面sql,下面的sql会查询出100020条数据，然后丢弃前100000条数据，会导致资源的浪费
select * from person limit 100000, 20;

9.用连接查询，代替子查询

如果查询sql如下：这样查询，会导致先执行子查询，在执行子查询的时候，会创建临时表，然后在查询完的时候，又会删除临时表，这样会导致性能的浪费

select * from order where user_id in (select user_id from user where status = 1);

可以使用下面的sql进行查询

select * from order o left join user u on o.user_id = u.user_id where u.status = 1;

10.join表的数量不能过多

根据阿里巴巴开发手册规定，join表的数量，最好不要超过三个
数据库在选择索引的时候会非常复杂，会导致选错索引，
反例：

select a.name, b.name, c.name, d.name
from a
inner join b on a.id = b.id
inner join c on c.id = b.id
inner join d on d.id = c.id
inner join e on e.id = d.id
inner join f on f.id = e.id;

但是在一些ERP系统重，查询的数据要join的表的数量非常多，所以不能一概而论，尽量越少越好。

11. join的选择问题

如果两张表使用inner join进行关联，数据库会自动选择较小的表去驱动较大的表。
但如果使用left join进行关联，则数据库会使用左边的表去驱动右边的表，所以如果使用left join的时候，尽量让左边的表是小表。

12.控制索引的数量

虽然索引能提高查询的速度，但是表中新增数据时，维护索引也是需要消耗性能的。
阿里巴巴开发手册中规定，单表的索引数量不要超过5个，单个索引的字段数量不要超过5个，数据库中使用b+树的结构来保存索引。在insert update 和delete时，都需要更新b+树索引。
如果你的表中的索引超过了5个怎么办，那也没关系，你的系统的并发量不多，超过5个也可以。
对于一些高并发的系统，最好遵守这个规范。
那么高并发系统如何优化索引呢，可以对于索引建立联合索引，将多个索引列放一起，可以删除部分无用的单个索引，将部分查询功能迁移到其他类型的数据库中，如Elastic Search中

13. 选择合理的字段类型

能用varchar就不要使用char字段
varchar可变长字段
char不可变长字段
金额字段用：decimal
长度可变的字符串用：varchar
长度不可变的字符串用：char

14.group by 效率提升

不要使用下面的sql:

select * from person
group by name
having age > 30

使用下面的sql:

select * from person
where age > 30
group by name

应先将数据进行缩小范围，再进行数据的批量操作

15.索引的优化

很多时候，sql走没走索引，执行的时间差距会很大，
可以使用explain命令查看是否走索引。
索引失效的常见原因：

• 不满足最左前缀匹配原则
• 范围索引列没有放在最后
• 使用了select *
• 索引列上有计算
• 索引列上使用了函数
• 字符类型没有加上引号，既字符串类型的字段，传参时，传入了数字类型的参数，导致类型不匹配
• 用is null和is not null没注意字段是否允许为空
• like查询左边有%
• 使用or关键字没有注意，使用or关键字时，所有的字段都要加上索引，不然所有的索引都会失效
• 两个单独建立了索引的列，进行列对比时，索引失效
• 对于数据库有时会选错索引，可以强制sql使用某个索引，force index

16. MySQL在使用反向查询（!=, <>, NOT LIKE）的时候无法使用索引，会导致全表扫描，覆盖索引除外。

EXPLAIN SELECT * FROM user_innodb WHERE name != '123';

文章学自于b站视频：苏三说技术
部分补充自其它地方