SQL优化

SQL优化

避免使用select *

开发时，可能为了方便直接使用**select ***来查出表中所有列数据

错误演示：

select * from tablexx where id =xx

实际的业务场景中，也许我们只真正需要使用其中的一两列数据。查了很多数据，但是却浪费了数据库资源，如内存或者CPU。

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

另一个最重要的问题是：select * 不会使用覆盖索引，会产生大量的返回表操作，导致查询SQL性能低下。

优化如下：

优化：只查我们需要的列

select id, name, age from tablexx where id=xxx

用"union all"替代"union"

两者的主要区别，前者会获取包括重复数据在内的所有数据，而后者是获取不重复且重新排序后的数据

错误演示：

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

优化：

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

这两个使用不绝对的！要看我们业务场景是什么，业务场景不允许重复的，就使用union

小表驱动大表

小表驱动大表，即一个小表的数据集驱动一个大表的数据集。

如果有两个表，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适用于左边是小表，右边是大表的情况。

批量插入数据

错误演示：

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，可以分多个批次进行处理。

不要在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 很多，即使数据可以快速检测，如果返回的数据量过大，网络传输会非常消耗性能，接口性能也不会有太大提升。

高效分页

有时，在列表页面查询数据时，为了避免一次性返回过多数据影响接口的性能，我们通常对查询接口进行分页处理。

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应该在唯一索引上进行分页，否则每页的大小会不一致。