优化SQL语句，十条建议让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系统，虽然并发量不大，但业务比较复杂，需要连接十几个表来查询数据。

因此，连接的表数量应根据系统的实际情况来确定，不能一概而论，越少越好。