工作中复杂sql优化

最新推荐文章于 2025-10-04 04:58:56 发布

原创最新推荐文章于 2025-10-04 04:58:56 发布 · 674 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java #mysql #sql

该博客探讨了在大数据量下复杂SQL查询的优化方法，针对客户与标签关系的查询，提出了先筛选客户再挂载参数的两步SQL策略，以减少全表扫描。同时，针对分页问题，指出了原始实现中分页插件应用的不足，并提出了解决方案，即在返回结果前将第一个SQL的分页信息应用于第二个SQL的结果集，确保总记录数的准确性。

复杂sql优化思路

1 .确定方向

在这里插入图片描述

数据量：客户与标签关系表：数据量在 3W左右后面预计几十W
主标签表：目前几百左右预计在几K
子标签：目前不到3K 预计 1W多
标签权限表：目前5K 预计5W多

目标：查询单个客户，展示格式为：

客户 + 基础信息1 +基础信息2 +基础信息3 +手动标签 +自动标签

带标签筛选，基础信息1 ，2 ，3 筛选，手动标签筛选

2.实现方案

初始实现方案

查询客户与标签关系表 in  加外部接口筛选出来的客户

   LEFT JOIN  客户与标签关系表 查询 基础标签1 

 LEFT JOIN  客户与标签关系表 查询 基础标签2

 LEFT JOIN  客户与标签关系表 查询 基础标签2

 LEFT JOIN  （客户与标签关系表  

inner jion 标签权限表 查询相应的标签权限 ) 查询 手动标签

LEFT JOIN  （客户与标签关系表  

inner jion 标签权限表 查询相应的标签权限 ) 查询 自动标签

**2.**分页直接用分页插件

**3.**导出的话直接调用子方法放到easyExcel里面

3.思路与优化

照着上面思路写完，最后聚合函数把字段聚合一下最外层+筛选，在前面加分页，可以运行，但是速度极其慢，3W条数据，EXPLAIN了一下发现需要查四次全表。然后准备改进：

本质上这个查询在查客户，然后再挂载上各种参数，所以在一开始的思路查所有客户再挂载参数，再limit明显效率很差，所以分成两个sql写，

1.先查筛选过的客户，

2.再查需要展示的客户的挂载、

这样复杂的sql从需要查4K个客户变成了查少量的客户，因为分页插件的limit作用在了查客户的sql上。这样优化后就变成了:

1.SELECT DISTINCT 客户code from (客户标签表 where 客户code in (
SELECT DISTINCT
    客户code from 
    客户标签表 
    再根据入参判断是否需要额外inner join 标签子表
))
where 条件

查询出客户，再拼接

查询客户与标签关系表 in  (查询出的客户)

   LEFT JOIN  客户与标签关系表 查询 基础标签1 

 LEFT JOIN  客户与标签关系表 查询 基础标签2

 LEFT JOIN  客户与标签关系表 查询 基础标签2

 LEFT JOIN  （客户与标签关系表  

inner jion 标签权限表 查询相应的标签权限 ) 查询 手动标签

LEFT JOIN  （客户与标签关系表  

inner jion 标签权限表 查询相应的标签权限 ) 查询 自动标签

这样返回的结果集再转为pageInfo return回去肯定是不对的

原因是：

由于我们返回的事第二个sql里面的实体类，如果直接调用这个子方法然后把回参return回去肯定是不对的，因为分页插件作用在了第一个sql上，而我们返回的是第二个sql的结果集，导致分页插件的total只是第一个sql查出来的被二次筛选过的元素数，简单来说就是我通过条件查询客户，其实满足条件的有600个客户，前端给我的是第一页，19条数的分页入参，那我第一个虽然查到了600个客户，但是limit 10 后只剩下了10条数给第二个sql，返回前端后就是我的结果total是10条，这肯定是不对的。所以需要在返回第二个sql结果集之前将第一个sql结果集转为pageInfo类型，然后把list换成第二个结果集的内容，这样就可以了。

结果total是10条，这肯定是不对的。所以需要在返回第二个sql结果集之前将第一个sql结果集转为pageInfo类型，然后把list换成第二个结果集的内容，这样就可以了。