优化SQL语句，10条建议让SQL速度飞起来
SQL优化一直是备受关注的热门话题，无论是在面试还是工作中，开发人员都可能遇到。
当开发人员负责的在线界面出现性能问题时，需要对其进行优化。在优化的思路中，通常会首先考虑优化SQL语句，因为它的转换成本要比修改代码小得多。
本文分享一些优化SQL的技巧，希望对读者有所帮助。
1 避免使用select *
很多时候，开发人员在编写SQL语句时，为了方便起见，喜欢直接使用select * 来一次性查出表中所有列的数据。
错误示例
select * from user where id = 1;
实际的业务场景中，也许我们只真正需要使用其中的一两列数据。查了很多数据，但是却浪费了数据库资源，如内存或者CPU。
此外，在通过网络IO传输数据的过程中，数据传输时间也会增加。
另一个最重要的问题是：select * 不会使用覆盖索引，会产生大量的返回表操作，导致查询SQL性能低下。
优化如下：
正确示例
select name, age from user where id = 1;
在查询SQL语句时，只检查需要使用的列，不需要检查多余的列。
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表有1万条数据，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关键字来实现业务需求。
因为in关键字包含在SQL语句中，会先执行in子查询语句，然后执行in外部语句。如果in中的数据量较小，作为条件的查询速度更快。
而如果SQL语句包含exists关键字，会先执行exists左边的语句（主查询语句）。
然后将其作为条件与右边的语句进行匹配。如果匹配成功，就可以查询数据。如果没有匹配，数据就会被过滤掉。
在这个需求中，order表有1万条数据，user表有100条数据。
order是一个大表，user是一个小表。
如果order在左边，更适合使用in关键字。
总结一下：
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_id=123
order by create_date asc;
根据用户 ID 查询订单，按照订单时间排序，首先找出用户的所有订单数据，得到一个订单集合。
然后在代码中，获取第一个元素的数据，也就是第一笔订单的数据，以获取第一笔订单的时间。
List list = orderMapper.getOrderList();
Order order = list.get(0);
虽然这种方式在功能上没有问题，但是非常低效。它需要先查询所有的数据，有点浪费资源。
优化如下：
正确示例
select id, create_date
from order
where user_id=123
order by create_date asc
limit 1;
使用 limit 1 只返回用户最早的订单时间的数据。
另外，在删除或修改数据时，为了防止误操作导致删除或修改不相关的数据，也可以在 SQL 语句的末尾添加 limit。
update order set status=0,edit_time=now(3)
where id>=100 and id<200 limit 100;
这样，即使操作错误，例如 id 错误，也不会影响太多的数据。
6 不要在 in 关键字中使用过多的值
对于批量查询接口，通常使用 in 关键字来过滤数据。例如，我想通过一些指定的 id 批量查询用户信息。
SQL 语句如下：
select id,name from category
where id in (1,2,3...100000000);
如果不加任何限制，查询语句可能会一次性查询大量的数据，这很容易导致接口超时。
那么应该怎么做呢？
select id,name from category
where id in (1,2,3...100)
limit 500;
可以在 SQL 中使用 limit 来限制数据。
不过，我们更多地是在业务代码中添加限制。伪代码如下：
public List getCategory(List ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500) {
throw new BusinessException("太多了")
}
return mapper.getCategoryList(ids);
}
另一种解决方案是：如果 ids 中的记录超过 500 条，可以使用多线程来分批查询数据。每个批次只检查 500 条记录，最后将查询到的数据聚合并返回。
然而，这只是一个临时解决方案，不适用于 ids 过多的场景。因为 ids 很多，即使数据可以快速检测，如果返回的数据量过大，网络传输会非常消耗性能，接口性能也不会有太大提升。
7 增量查询
有时候，需要通过远程接口查询数据，然后将其同步到另一个数据库中。
错误示例
select * from user;
如果直接获取全部数据，然后进行同步。虽然这样非常方便，但是带来一个很大的问题，即如果数据量很大，查询性能会非常差。
select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;
按照 id 和时间升序，每次只同步一批数据，这批数据只有 100 条记录。每次同步完成后，保存这 100 条数据中最大的 id 和时间，用于同步下一批数据时使用。
这种增量查询方法可以提高单次查询的效率。
8 高效分页
有时，在列表页面查询数据时，为了避免一次性返回过多数据影响接口的性能，我们通常对查询接口进行分页处理。
MySQL中常用于分页的limit关键字：
select id,name,age
from user limit 10,20;
如果表中的数据量较小，使用limit关键字进行分页是没有问题的。但是如果表中的数据量很大，使用limit关键字会导致性能问题。
例如，现在分页参数变为：
select id,name,age
from user limit 1000000,20;
MySQL会找到1,000,020条数据，然后丢弃前1,000,000条数据，只查询最后的20条数据，这是一种资源浪费。
那么，如何对这些海量数据进行分页呢？
优化SQL语句：
select id,name,age
from user where id > 1000000 limit 20;
首先，找到上一页的最大id，然后利用id的索引进行查询。但是，在这种方案中，id需要连续有序。
还可以使用between进行分页优化。
select id,name,age
from user where id between 1000000 and 1000020;
需要注意的是，between应该在唯一索引上进行分页，否则每页的大小会不一致。
9 使用连接查询替代子查询
如果在MySQL中需要从两个以上的表中查询数据，通常有两种实现方法：子查询和连接查询。
子查询的示例如下：
select * from order
where user_id in (select id from user where status=1)
子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果之内。程序先运行最内层的嵌套语句，然后再运行外层语句。
子查询语句的优点是，如果涉及的表的数量较少，它简单且结构清晰。
但是，子查询执行时需要创建临时表，查询完成后需要删除这些临时表，这会带来一些额外的性能消耗。
这时，可以改为连接查询。
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1
10 连接的表不能太多
错误的示例
select a.name,b.name.c.name,d.name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id
如果join太多，MySQL在选择索引时会变得非常复杂，容易选择错误的索引。
而且如果没有命中，嵌套循环连接是从两个表中读取一行数据进行逐对比较，复杂度为n²。
因此，应尽量控制连接的表数量。
正确的示例
select a.name,b.name.c.name,a.d_name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
如果在业务场景的实现中需要查询其他表中的数据，可以在a、b、c表中添加冗余的特定字段，例如在表a中添加冗余的d_name字段来保存需要查询的数据。
然而，也有一些ERP系统，虽然并发量不大，但业务比较复杂，需要连接十几个表来查询数据。
因此，连接的表数量应根据系统的实际情况来确定，不能一概而论，越少越好。
发表评论