查看原文
其他

优化SQL语句,10条建议让SQL速度飞起来

学研妹 Java学研大本营 2024-01-02

分享一些优化SQL的技巧。

长按关注《Java学研大本营》

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系统,虽然并发量不大,但业务比较复杂,需要连接十几个表来查询数据。

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

推荐书单

《SQL语言从入门到精通(软件开发视频大讲堂)》

《SQL语言从入门到精通》从初学者角度出发,通过通俗易懂的语言、丰富多彩的实例,详细介绍了在开发中使用SQL语言必须掌握的技术。全书分为3篇共18章,内容包括SQL语言基础、SQL查询基础、复杂查询、数据排序、SQL函数的使用、分组统计、子查询、多表查询、添加数据、修改和删除数据、视图、存储过程、触发器、游标、索引、事务、管理数据库与数据表、数据库安全。所有知识都结合具体实例进行介绍,涉及的程序代码给出了详细的注释,读者可以轻松领会SQL语言的精髓,快速提高开发技能。

购买链接:https://item.jd.com/14055972.html

精彩回顾

使用SQL分析,挖掘产品市场数据库的价值

掌握4种SQL索引类型,剖析索引原理

4种使用Linux命令查询MySQL的高效方法

8个提升编程体验的VS Code插件

10个开发者必知必会的Git命令

长按关注《Java学研大本营》
长按访问【IT今日热榜】,发现每日技术热点
继续滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存