记一次SQL优化

最新推荐文章于 2024-04-11 16:02:12 发布

凌麟柒

最新推荐文章于 2024-04-11 16:02:12 发布

阅读量244

点赞数

分类专栏：慢服务优化优化文章标签：慢服务优化 SQL优化

本文链接：https://blog.youkuaiyun.com/qq_40233503/article/details/89556850

版权

慢服务优化同时被 2 个专栏收录

1 篇文章

订阅专栏

优化

1 篇文章

订阅专栏

按照公司需求，优化一些慢服务，其中一个服务仅为SQL查询数据，无任何业务逻辑，所以单纯就SQL优化，写写自己的笔记，如果有好的建议，欢迎讨论

首先看看原SQL

原SQL
因为后面都是纯业务判断，不涉及这次优化的内容，所以也不予展示，来看看这条SQL，首先bsm.*就不应该写，就算真的要查出全列，也应该将列全部写出，这样方便扩展。

紧接着看到这些子查询，如果要优化，肯定是从这里优化下手，我们先不急，看看这条SQL的执行计划
改动前的执行计划
(由于查看执行计划的SQL只是全SQL的一部分，没有加上过滤条件，所以bli表进行了全表扫描)，具体每一列代表的什么意思这里不再列出，可以看看这一篇文章：http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

这里主要关注的是4个DEPENDENT SUBQUERY,DEPENDENT SUBQUERY代表其查询取决于外层查询，也就是说现在外层有13W的数据(生产环境的数据还会在多些)，所以想办法将这4个子查询优化，其实仔细读一下SQL，这几个子查询的列肯定没有用于条件过滤，并且这些子查询都是可以通过连表来解决的，而且这几条子查询都是简单的连表查询，所以我们完全可以将其转换为左外连接，为什么是左外连接，因为原SQL本意就是从表中重新连表进行筛选数据，所以使用左外连接不影响之前连接好的数据

这里需要注意的是inner join和left join的顺序不要改变，因为连表是有顺序的，每连一张表就产生一个结果集，这个结果集再连下一个结果集，所以JOIN的顺序不要改动，当然LEFT JOIN 除外，因为LEFT JOIN是不为影响左表的数据的

改动后的SQL

改动后的SQL
这时我们再看看其执行计划
改动后的执行计划
会发现已经没有DEPENDENT SUBQUERY了

优化分页

因为这是一条分页查询的SQL，分页查询导致慢的很大的一个原因就是需要统计行数，公司用的也是PageHelper插件，这个插件在进行统计SQL的时候默认是拼接SELECT COUNT(0)到第一个FROM前，所以会有一点不个性，关于PageHelper插件怎么处理这一块的代码以后有机会再整理。

关于分页的原则，我是这么这个整理的：

没有参与过滤条件的字段所在的表，没必要连，这是因为如果没有参与过滤，那么这些字段肯定只是用来展示用的，当然，这种情况仅限于左连，因为左连不会影响行数，其他连接需要看情况，内连其实也有在过滤数据
SELECT COUNT(0)足以，也不需要排序

这里用的分页插件时老大对PageHelper封装的，可以指定SQL执行分页，这里执行的SQL是

SELECT count(0)
FROM `bd_student_modify` bsm 
INNER JOIN bd_learn_info bli
ON bsm.`learn_id` = bli.`learn_id` 
INNER JOIN bd_unvs_profession bup 
ON bup.`pfsn_id` = bsm.`pfsn_id` 
INNER JOIN bd_university bu 
ON bu.`unvs_id` = bsm.`unvs_id` 
LEFT JOIN bd_check_record bcr 
ON bcr.`mapping_id` = bsm.`modify_id`
INNER JOIN bd_test_area bta
ON bta.`ta_id` = bsm.`ta_id`
WHERE
...

最后

发现，实质上效率并没有快多少，这可能是这几个子查询的表的数据量都不大。从这里开始，接着展开分析，使用子查询和左连接到底差距在哪里？

假设在上面的SQL场景中，几个基本表连接后产生的数据量是60，而4张需要子查询的表，分别都是4.
那么先看下通过4个子查询产生的量，对于一张表来说，每一次子查询都是从这张数据量为4的表中找到一个固定值，假设我们大表中这个60的数据量都是不重复的，那么这一个子查询在这60条数据中找60次，产生的数据量是60*4，而4个这样的子查询，就是60*4*4；
如果是连表查询呢，因为是左外连接,并且连表是有顺序的，所以4张表最后需要连接的所产生的数据量也是60*4，如果这么算，一样的数据量，而是子查询却要select的次数比连表的多，所以我们要避免使用子查询

总结：如何去优化一个接口

先去找到这个接口对应的代码，看看是业务逻辑导致服务慢还是SQL操作导致服务慢，可以在一些觉得需要判断的地方打印时间去分析
如果是业务逻辑的慢，则优先优化业务逻辑，如果是SQL慢，优化SQL
优化SQL步骤
1. 使用explain分析哪些表没有用到索引，是OR导致还是ORDER BY导致，或者给没有加上索引的表加上索引
2. 对于多张表的连表查询，试试能不能将一些表拆出来，因为连表查询有些表很大，但是又只是通过连表来查询，如果这些字段只是用来被显示，而不是用来过滤的字段，那么我们可以把这些表拆出来用于第二次查询，在Java代码中放进map里合并
3. 如果是分页查询，可以设置分页查询的列，查出的列越少越好(使用PageHelper插件默认是清除所有的列并在之前加上COUNT(0)，这里的优化分页是没有用于过滤的表可以删除)
  
  这里是使用自己封装的PageHelper插件
4. 进一步优化可以在默认加载页面加入过滤条件，比如默认查询今天的数据或者默认查询自己的数据，过滤条件越多，连接的数据越小