事件背景
某驻场客户上线oa系统，上线后系统负载很高，经客户沟通对top sql进行调优处理，大部分SQL索引创建不合理，少部分需要进行改写调优，摘取其中一个比较典型sql进行描述。
调优过程
原SQL：
select count(*)
from xxxx.xxxxx_table_name
WHERE ID not IN (SELECT MIN(ID)
from xxxx.xxxxx_table_name
group by resourceid,
resourcetype,
infoid,
menutype,
sharetype,
sharevalue,
seclevel,
rolelevel,
customid,
jobtitlelevel,
jobtitlesharevalue);
COUNT(*)
----------
10291执行计划如下， xxxx.xxxxx_table_name表被驱动执行了22341次。
从上述执行计划可以了解到的信息是id 3，xxxx.xxxxx_table_name表全表扫描返回22341行数据，执行时间0.01s，对子查询进行匹配，子查询被扫描了22341次。该步骤为SQL执行主要消耗点。而not in可以被等价改写成left join。
改写调优方案一：
改写后SQL如下，反连接使用left join进行改写。
这里的改写思路是两个表直接关联查询，从而避免filter过滤导致子查询被扫描22341次，而是仅仅扫描两次表就返回想要的结果。
select count(*)
from xxxx.xxxxx_table_name a,
(SELECT MIN(ID) id
from xxxx.xxxxx_table_name
group by resourceid,
resourcetype,
infoid,
menutype,
sharetype,
sharevalue,
seclevel,
rolelevel,
customid,
jobtitlelevel,
jobtitlesharevalue) b
where a.id = b.id(+)
and b.id is null;
COUNT(*)
----------
10291改写调优方案二：
上述子查询因有min函数，且不包含这个最小值，姑可以查询满足比这个最小值要大，因此可以使用分析函数进行改写，可以进一步减少一次全表扫描，性能达到最优，改写后的SQL以及执行计划如下:
select count(*)
from (select row_number() over(partition by resourceid, resourcetype, infoid, menutype, sharetype, sharevalue, seclevel, rolelevel, customid, jobtitlelevel, jobtitlesharevalue order by id asc) rn
from xxxx.xxxxx_table_name)
where rn > 1;
COUNT(*)
----------
10291分析总结
SQL执行效率差，影响正常生产业务是数据库运维人员经常遇到的场景，SQL的写法很重要，很多系统随着数据量的增长越来越慢，大部分跟SQL写法不佳有关，如果我们运维人员不能识别这些低效写法，就会背上运维水平差的锅，影响运维公司形象。
在我们调优过程中，换个思路，可能会得到不一样的结果，通过以上两种调优思路希望能帮助到大家。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129573.html
摘要：最近遇见一个的慢查问题，于是排查了下，这里把相关的过程做个总结。而且，我检查了子查询的表的索引，中用到的查询条件都已经增加了索引。还好我们的子查询加了必要的索引，不然结果会更加惨不忍睹。这个结果真是太坑爹，而且十分违反直觉。 最近遇见一个 MySQL 的慢查问题，于是排查了下，这里把相关的过程做个总结。 定位原因 我首先查看了 MySQL 的慢查询日志，发现有这样一条 query 耗时...
摘要：实现事务的原子性，要支持回滚操作，在某个操作失败后，回滚到事务执行之前的状态。一致性事务使得系统从一个一致的状态转换到另一个一致状态。 本文作者 TomorrowWu,原创文章,转载注明出处,博客地址 https://segmentfault.com/u/to... 第一时间看后续精彩文章。觉得好的话,顺手分享到朋友圈吧,感谢支持。 笔者最近在准备面试,觉得学习最好的方式就是把知道的东...
阅读 1346·2023-01-11 13:20
阅读 1684·2023-01-11 13:20
阅读 1132·2023-01-11 13:20
阅读 1858·2023-01-11 13:20
阅读 4100·2023-01-11 13:20
阅读 2704·2023-01-11 13:20
阅读 1385·2023-01-11 13:20
阅读 3597·2023-01-11 13:20
