[错误报告]: 在mapper里，执行长sql或者超长sql查询，项目运行时间一长，就会特别慢，cpu直接拉满

[Varian-Wrynn]

确认

• 我使用的版本是最新版, 并且使用插件确认过项目里无依赖版本冲突
• 我已经在 issue 中搜索过, 确认问题没有被提出过
• 我已经修改标题, 将标题中的 描述 替换为遇到的问题

当前程序版本

3.5.3.2

问题描述

在mapper里，执行长sql或者超长sql查询，项目运行时间一长，就会特别慢，cpu直接拉满
项目刚启动，这个问题没有出现，但项目运行一天之后，该接口就出现问题了。
出现该问题之后，只需要重启项目，接口就正常了，访问速度非常快。
项目里其他接口均不会出现该问题。
把sql单独拿出来到数据库里面执行，只需要20ms-200ms，但是用代码执行，就达到了20s-30s
接口里就一个执行mapper，然后return。

mybatis plus 插件只配置了一个分页插件，没有多租户插件。
尝试解决: 一开始试过将#{}，修改为${}，但不起作用。

接口内容非常简单
return serviceMapper.historyDataWithDay(page, requestData.getData().getStoreId(), poiIds);
只有这一句。

---

`public interface BClientOrderStatisticsServiceMapper {

IPage<DataResult> historyDataWithDay(@Param("page") IPage<DataResult> page,
                                                                            @Param("storeId") Integer storeId, @Param("poiIds") Set<String> poiIds);

}`
mapper 里的sql 内容：

SELECT
            COALESCE(o.order_date, r.refund_date, v.verify_date) AS date,
            COALESCE(o.total_pay_amount, 0) AS mtOrderAmount,
            COALESCE(r.payReturnAmount, 0) AS refundAmount,
            COALESCE(o.total_pay_amount, 0) - COALESCE(r.payReturnAmount, 0) + COALESCE(v.couponPayAmount, 0) + COALESCE(v.platformDiscountAmount, 0) AS netAmount,
            COALESCE(o.total_pay_amount, 0) + COALESCE(v.couponPayAmount, 0) + COALESCE(v.platformDiscountAmount, 0) AS totalAmount,
            COALESCE(r.orderReturnCount, 0) AS orderReturnCount,
            COALESCE(v.couponPayAmount, 0) AS couponPayAmount,
            COALESCE(v.platformDiscountAmount, 0) AS platformDiscountAmount,
            COALESCE(v.platformDiscountAmount, 0) + COALESCE(v.couponPayAmount, 0) AS qudao,
            COALESCE(v.partyVerifyCount, 0) AS partyVerifyCount
        FROM
            (
                SELECT
                    DATE(PAY_TIME) AS order_date,
                    SUM(pay_amount) AS total_pay_amount
                FROM
                    mt_order
                WHERE
                    STORE_ID = ${storeId}
                  AND (`STATUS` IN ('I', 'B', 'K', 'J')
                    OR (DATE(return_time) != DATE(PAY_TIME) AND `STATUS` = 'H'))
                  AND TYPE = 'goods'
                GROUP BY
                    DATE(PAY_TIME)
            ) o
                LEFT JOIN
            (
                SELECT
                    DATE(audit_time) AS refund_date,
                    SUM(pay_return_amount) AS payReturnAmount,
                    COUNT(1) AS orderReturnCount
                FROM
                    mt_order_refund
                WHERE
                    refund_status = 'B'
                  AND states = 'B'
                  AND is_delete = 0
                  AND shop_id = ${storeId}
                  AND DATE(order_time) != DATE(audit_time)
                GROUP BY
                    DATE(audit_time)
            ) r ON o.order_date = r.refund_date
                LEFT JOIN
            (
                SELECT
                    DATE(verify_time) AS verify_date,
                    SUM(coupon_pay_amount) AS couponPayAmount,
                    SUM(platform_discount_amount) AS platformDiscountAmount,
                    COUNT(1) AS partyVerifyCount
                FROM
                    third_party_verify_record
                WHERE
                    STATUS = 1
                  AND channel = 1
                <if test="poiIds != null and poiIds.size() > 0">
                    and poi_id in
                    <foreach collection="poiIds" index="index"  item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                GROUP BY
                    DATE(verify_time)
            ) v ON COALESCE(o.order_date, r.refund_date) = v.verify_date
        UNION ALL
        SELECT
            r.refund_date AS date,
            0 AS mtOrderAmount,
            COALESCE(r.payReturnAmount, 0) AS refundAmount,
            -COALESCE(r.payReturnAmount, 0) + COALESCE(v.couponPayAmount, 0) + COALESCE(v.platformDiscountAmount, 0) AS netAmount,
            COALESCE(v.couponPayAmount, 0) + COALESCE(v.platformDiscountAmount, 0) AS totalAmount,
            COALESCE(r.orderReturnCount, 0) AS orderReturnCount,
            COALESCE(v.couponPayAmount, 0) AS couponPayAmount,
            COALESCE(v.platformDiscountAmount, 0) AS platformDiscountAmount,
            COALESCE(v.platformDiscountAmount, 0) + COALESCE(v.couponPayAmount, 0) AS qudao,
            COALESCE(v.partyVerifyCount, 0) AS partyVerifyCount
        FROM
            (
                SELECT
                    DATE(audit_time) AS refund_date,
                    SUM(pay_return_amount) AS payReturnAmount,
                    COUNT(1) AS orderReturnCount
                FROM
                    mt_order_refund
                WHERE
                    refund_status = 'B'
                  AND states = 'B'
                  AND is_delete = 0
                  AND shop_id = ${storeId}
                  AND DATE(order_time) != DATE(audit_time)
                GROUP BY
                    DATE(audit_time)
            ) r
                LEFT JOIN
            (
                SELECT
                    DATE(verify_time) AS verify_date,
                    SUM(coupon_pay_amount) AS couponPayAmount,
                    SUM(platform_discount_amount) AS platformDiscountAmount,
                    COUNT(1) AS partyVerifyCount
                FROM
                    third_party_verify_record
                WHERE
                    STATUS = 1
                  AND channel = 1
                <if test="poiIds != null and poiIds.size() > 0">
                    and poi_id in
                    <foreach collection="poiIds" index="index"  item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
                GROUP BY
                    DATE(verify_time)
            ) v ON r.refund_date = v.verify_date
        WHERE
            NOT EXISTS (
                SELECT 1
                FROM
                    (
                        SELECT
                            DATE(PAY_TIME) AS order_date
                        FROM
                            mt_order
                        WHERE
                            STORE_ID = ${storeId}
                          AND (`STATUS` IN ('I', 'B', 'K', 'J')
                            OR (DATE(return_time) != DATE(PAY_TIME) AND `STATUS` = 'H'))
                          AND TYPE = 'goods'
                        GROUP BY
                            DATE(PAY_TIME)
                    ) o
                WHERE
                    o.order_date = r.refund_date
            )
        UNION ALL
        SELECT
            v.verify_date AS date,
            0 AS mtOrderAmount,
            0 AS refundAmount,
            COALESCE(v.couponPayAmount, 0) + COALESCE(v.platformDiscountAmount, 0) AS netAmount,
            COALESCE(v.couponPayAmount, 0) + COALESCE(v.platformDiscountAmount, 0) AS totalAmount,
            0 AS orderReturnCount,
            COALESCE(v.couponPayAmount, 0) AS couponPayAmount,
            COALESCE(v.platformDiscountAmount, 0) AS platformDiscountAmount,
            COALESCE(v.platformDiscountAmount, 0) + COALESCE(v.couponPayAmount, 0) AS qudao,
            COALESCE(v.partyVerifyCount, 0) AS partyVerifyCount
        FROM
            (
                SELECT
                    DATE(verify_time) AS verify_date,
                    SUM(coupon_pay_amount) AS couponPayAmount,
                    SUM(platform_discount_amount) AS platformDiscountAmount,
                    COUNT(1) AS partyVerifyCount
                FROM
                    third_party_verify_record
                WHERE
                    STATUS = 1
                  AND channel = 1
                    <if test="poiIds != null and poiIds.size() > 0">
                        and poi_id in
                        <foreach collection="poiIds" index="index"  item="item" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                GROUP BY
                    DATE(verify_time)
            ) v
        WHERE
            NOT EXISTS (
                SELECT 1
                FROM
                    (
                        SELECT
                            DATE(PAY_TIME) AS order_date
                        FROM
                            mt_order
                        WHERE
                            STORE_ID = ${storeId}
                          AND (`STATUS` IN ('I', 'B', 'K', 'J')
                            OR (DATE(return_time) != DATE(PAY_TIME) AND `STATUS` = 'H'))
                          AND TYPE = 'goods'
                        GROUP BY
                            DATE(PAY_TIME)
                    ) o
                WHERE
                    o.order_date = v.verify_date
            )
          AND NOT EXISTS (
            SELECT 1
            FROM
                (
                    SELECT
                        DATE(audit_time) AS refund_date
                    FROM
                        mt_order_refund
                    WHERE
                        refund_status = 'B'
                      AND states = 'B'
                      AND is_delete = 0
                      AND shop_id = ${storeId}
                      AND DATE(order_time) != DATE(audit_time)
                    GROUP BY
                        DATE(audit_time)
                ) r
            WHERE
                r.refund_date = v.verify_date
        )

        ORDER BY
            date DESC

详细堆栈日志

[xxx-tea]

有没有更详细的信息？

1. 该sql返回了多少行数，是只有这条sql造成了cpu和耗时飙升吗？
2. 有没有监控到卡顿时的数据库连接池状态？
3. 系统applicaiton的配置文件
4. cpu飙升后，不调用该接口会不会逐渐下降。若不会，可能需要导出卡顿时的jvm内存分析

[Varian-Wrynn]

还有更详细的信息吗？

1. 该sql返回了多少行数，是只有一条sql造成了cpu和运行时间锁定吗？
2. 有没有监控到卡顿时的数据库连接池状态？
3. 系统应用程序的配置文件
4. cpu高峰后，不调用该接口会逐渐下降。如果不会，可能需要导出卡顿时的jvm内存分析

1.该sql返回最多几百行数据，因为是以天为单位去统计数据的，数据目前就几个月的数据。
02-13 11:13:50.102 [http-nio-8010-exec-9] ERROR c.f.m.b.s.BClientStatisticsControllerService - yingshouHistoryData day step5:2025-02-13 11:13:50
02-13 11:13:59.240 [http-nio-8010-exec-9] ERROR c.f.m.b.s.BClientStatisticsControllerService - yingshouHistoryData day step6:2025-02-13 11:13:59
执行这个mapper中的sql花了9秒
2.如何监控数据库连接池状态？
3.
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.leak-detection-threshold=2000
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.url=jdbc:mysql://***?useUnicode=true&characterEncoding=UTF8&useSSL=false
mybatis-plus.type-aliases-package=com.fuint.repository.model
mybatis-plus.mapper-locations=classpath*:/mapper//*.xml
mybatis-plus.global-config.db-config.logic-delete-field=is_deleted
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0
4.接口执行完之后，cpu马上回复正常。或者说，sql执行过后，cpu回归正常。

[miemieYaho]

数据库与你的项目在同一台物理机吗?

[Varian-Wrynn]

数据库与你的项目在同一台物理机吗?

不是，数据库用的阿里云数据库

[miemieYaho]

那你应该升级mp,升级连接池,升级驱动再试,毕竟根据你的链路来说和mp关系不大
或者你检查是不是内存给少了,毕竟程序里比你在数据库里执行sql多了封装成model的步骤,
或者尝试替换连接池为阿里那个来监控连接池状态以及sql执行耗时

[Varian-Wrynn]

那你应该升级mp,升级连接池,升级驱动再试,毕竟根据你的链路来说和mp关系不大 或者你检查是不是内存给少了,毕竟程序里比你在数据库里执行sql多了封装成model的步骤, 或者尝试替换连接池为阿里那个来监控连接池状态以及sql执行耗时

我试试换个数据库连接池