记一次生产sql优化

晴天M雨天

已于 2022-05-20 10:29:22 修改

阅读量444

点赞数

分类专栏：工作问题文章标签： sql nosql

于 2022-05-20 10:26:59 首次发布

本文链接：https://blog.csdn.net/weixin_38738049/article/details/124878023

版权

工作问题专栏收录该内容

25 篇文章 2 订阅

订阅专栏

本文详细分析了一个业务中查询性能瓶颈，通过Arthas工具定位到`historicalpaymentrate`方法的慢查询。通过小表驱动大表、调整SQL结构、优化索引和条件，将查询时间从4秒降至505ms，分享了性能提升90%的优化策略。涉及的知识点包括SQL优化、分库分表和NoSQL选择。

摘要由CSDN通过智能技术生成

总结优化思路
1分析当前业务
2定位慢查询arthas
3分析查询参数，优化查询参数，优化返回结果
4explan 分析慢查询根据sql 分解sql，看哪一个子查询慢，优化索引，优化查询顺序结构等
5以上都不行前期就应该建立分库分表或者使用ck nosql 进行查询

arthas 分析结果如下

[arthas@25356]$ trace com.rfca.dao.mapper.ParkingMapper QueryLatePhoto
Press Q or Ctrl+C to abort.
Affect(class count: 2 , method count: 1) cost in 140 ms, listenerId: 13
`---ts=2022-05-19 13:09:39;thread_name=pool-4-thread-6;id=67;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[28.034673ms] com.sun.proxy.$Proxy120:QueryLatePhoto()

`---ts=2022-05-19 13:09:50;thread_name=pool-4-thread-4;id=65;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[31.923302ms] com.sun.proxy.$Proxy120:QueryLatePhoto()

`---ts=2022-05-19 13:10:00;thread_name=pool-4-thread-5;id=66;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[61.771667ms] com.sun.proxy.$Proxy120:QueryLatePhoto()

[arthas@25356]$ 
[arthas@25356]$ trace com.rfca.dao.mapper.ParkingMapper QueryLongOccupys
Press Q or Ctrl+C to abort.
Affect(class count: 2 , method count: 1) cost in 138 ms, listenerId: 14
`---ts=2022-05-19 13:10:49;thread_name=pool-4-thread-4;id=65;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[64.901587ms] com.sun.proxy.$Proxy120:QueryLongOccupys()

`---ts=2022-05-19 13:11:23;thread_name=pool-4-thread-5;id=66;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[41.786277ms] com.sun.proxy.$Proxy120:QueryLongOccupys()

[arthas@25356]$ trace com.rfca.dao.mapper.ParkingMapper QueryLongEmptys
Press Q or Ctrl+C to abort.
Affect(class count: 2 , method count: 1) cost in 142 ms, listenerId: 15
`---ts=2022-05-19 13:11:54;thread_name=pool-4-thread-3;id=64;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[6.182256ms] com.sun.proxy.$Proxy120:QueryLongEmptys()

[arthas@25356]$ trace com.rfca.dao.mapper.ParkingMapper QuerySys
Press Q or Ctrl+C to abort.
Affect(class count: 2 , method count: 1) cost in 144 ms, listenerId: 16
`---ts=2022-05-19 13:12:25;thread_name=pool-4-thread-4;id=65;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[18.25196ms] com.sun.proxy.$Proxy120:QuerySys()

[arthas@25356]$ trace com.rfca.dao.mapper.ParkingMapper historicalpaymentrate
Press Q or Ctrl+C to abort.
Affect(class count: 2 , method count: 1) cost in 142 ms, listenerId: 17
`---ts=2022-05-19 13:13:04;thread_name=pool-4-thread-3;id=64;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[4663.98636ms] com.sun.proxy.$Proxy120:historicalpaymentrate()

[arthas@25356]$ trace com.rfca.dao.mapper.ParkingMapper paymentrateByTime
Press Q or Ctrl+C to abort.
Affect(class count: 2 , method count: 1) cost in 140 ms, listenerId: 18
`---ts=2022-05-19 13:13:47;thread_name=pool-4-thread-9;id=6a;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[9.511763ms] com.sun.proxy.$Proxy120:paymentrateByTime()

`---ts=2022-05-19 13:14:16;thread_name=pool-4-thread-8;id=69;is_daemon=false;priority=5;TCCL=org.apache.catalina.loader.ParallelWebappClassLoader@62cc7715
    `---[6.436025ms] com.sun.proxy.$Proxy120:paymentrateByTime()

由以上结果分析可知
trace com.rfca.dao.mapper.ParkingMapper historicalpaymentrate

这个类的历史查询接口返回需要4秒多需要优化，

QueryChargeRate11 方法需要7秒需要优化

原7秒的sql

<!--        SELECT-->
<!--        t_parking_record.parkinglotid AS id,SUM(t_charge_record.money) AS VALUE ,SUM(t_charge_record.receivable) AS total-->
<!--        FROM  t_charge_record LEFT JOIN t_parking_record ON  t_charge_record.parkingrecordid = t_parking_record.id-->
<!--        LEFT JOIN t_parkinglot ON t_parkinglot.id = t_parking_record.parkinglotid-->
<!--        where-->
<!--        t_parking_record.state > -1-->
<!--        <if test="start_date !=null">-->
<!--            and t_charge_record.modifytime &gt; #{start_date}-->
<!--        </if>-->
<!--        <if test="list !=null and list.size > 0 ">-->
<!--            and t_parkinglot.parkinglotno in-->
<!--            <foreach item="item" index="index" collection="list" open="("-->
<!--                     separator="," close=")">-->
<!--                #{item}-->
<!--            </foreach>-->
<!--        </if>-->
<!--        GROUP BY t_parking_record.parkinglotid-->

优化思路，通过小表驱动大表的思路先把查询结果找出在进行join

    SELECT  t_parking_record.parkinglotid AS id,
        SUM(b.money) AS VALUE ,
        SUM(b.receivable) AS total
        FROM (SELECT  a.parkingrecordid,a.money,a.receivable FROM t_charge_record AS a WHERE a.modifytime &gt; #{start_date}) AS b
        LEFT JOIN t_parking_record ON  b.parkingrecordid = t_parking_record.id
        LEFT JOIN t_parkinglot ON t_parkinglot.id = t_parking_record.parkinglotid
        WHERE
        t_parking_record.state > -1

        GROUP BY t_parking_record.parkinglotid

优化后 505ms

4秒的原sql

  SELECT b.`parkinglotid` as id,SUM(receivable) AS total,SUM(proceeds) AS proceeds FROM ( SELECT t.id  FROM  t_parkinglot t WHERE 1=1
        <if test="list !=null and list.size > 0 ">
            and t.parkinglotno in
            <foreach item="item" index="index" collection="list" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
        )AS a
        LEFT JOIN t_parking_record b ON  a.id = b.parkinglotid
        AND b.state > -1
        <if test="start_date !=null">
            and b.outtime &gt; #{start_date} and b.outtime &lt; #{end_date}
        </if>
        GROUP BY b.parkinglotid

这个sql慢的原始是查询了半年的时间，扫描行数2千万行，又因为使用GROUP BY sum等操作没有使用索引索引使用索引覆盖原理添加索引 outtime state parkinglotid receivable proceeds ,
条件优化
直接使用 parkinglotid 查询没有使用 parkinglotno
优化后 814 ms

这种方式如果还是不行，查询结果太大的话直接使用
使用 SQL_BIG_RESULT 进行优化直接走文件排序少了内存到文件的复制操作

如果上述结果还是很慢
扩展
5以上都不行前期就应该建立分库分表或者使用ck nosql 进行查询

本次优化结果性能提升 90%