聊聊 sql 优化的 15 个小技巧

sql 优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。

如果某天你负责的某个线上接口，出现了性能问题，需要做优化。那么你首先想到的很有可能是优化 sql 语句，因为它的改造成本相对于代码来说也要小得多。

那么，如何优化 sql 语句呢？

这篇文章从 15 个方面，分享了 sql 优化的一些小技巧，希望对你有所帮助。

推荐下自己做的 Spring Boot 的实战项目：

https://github.com/YunaiV/ruoyi-vue-pro

1 避免使用 select *

很多时候，我们写 sql 语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。

反例：

select * from user where id=1;

在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者 cpu。

此外，多查出来的数据，通过网络 IO 传输的过程中，也会增加数据传输的时间。

还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询 sql 的性能很低。

那么，如何优化呢？

正例：

select name,age from user where id=1;

sql 语句查询时，只查需要用到的列，多余的列根本无需查出来。

推荐下自己做的 Spring Cloud 的实战项目：

https://github.com/YunaiV/onemall

2 用 union all 代替 union

我们都知道 sql 语句使用union关键字后，可以获取排重后的数据。

而如果使用union all关键字，可以获取所有数据，包含重复的数据。

反例：

(select * from user where id=1)
        union
        (select * from user where id=2);

排重的过程需要遍历、排序和比较，它更耗时，更消耗 cpu 资源。

所以如果能用 union all 的时候，尽量不用 union。

正例：

(select * from user where id=1) 
union all
(select * from user where id=2);

除非是有些特殊的场景，比如 union all 之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用 union。

3 小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有 order 和 user 两张表，其中 order 表有 10000 条数据，而 user 表有 100 条数据。

这时如果想查一下，所有有效的用户下过的订单列表。

可以使用in关键字实现：

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

也可以使用exists关键字实现：

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

前面提到的这种业务场景，使用 in 关键字去实现业务需求，更加合适。

为什么呢？

因为如果 sql 语句中包含了 in 关键字，则它会优先执行 in 里面的子查询语句，然后再执行 in 外面的语句。如果 in 里面的数据量很少，作为条件查询速度更快。

而如果 sql 语句中包含了 exists 关键字，它优先执行 exists 左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order 表有 10000 条数据，而 user 表有 100 条数据。order 表是大表，user 表是小表。如果 order 表在左边，则用 in 关键字性能更好。

总结一下：

• in 适用于左边大表，右边小表。
• exists 适用于左边小表，右边大表。

不管是用 in，还是 exists 关键字，其核心思想都是用小表驱动大表。

4 批量操作

如果你有一批数据经过业务处理之后，需要插入数据，该怎么办？

反例：

for(Order order: list){
   orderMapper.insert(order):
}

在循环中逐条插入数据。

insert into order(id,code,user_id) 
values(123,'001',100);

该操作需要多次请求数据库，才能完成这批数据的插入。

但众所周知，我们在代码中，每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。

那么如何优化呢？

正例：

orderMapper.insertBatch(list):

提供一个批量插入数据的方法。

insert into order(id,code,user_id) 
values(123,'001',100),(124,'002',100),(125,'003',101);

这样只需要远程请求一次数据库，sql 性能会得到提升，数据量越多，提升越大。

但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在 500 以内。如果数据多于 500，则分多批次处理。

5 多用 limit

有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。

反例：

select id, create_date 
 from order 
where user