确认

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

当前程序版本

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

详细堆栈日志


Comment From: xxx-tea

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

Comment From: 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回归正常。

Comment From: miemieYaho

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

Comment From: Varian-Wrynn

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

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

Comment From: miemieYaho

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

Comment From: Varian-Wrynn

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

我试试换个数据库连接池