确认
当前程序版本
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
还有更详细的信息吗？
- 该sql返回了多少行数，是只有一条sql造成了cpu和运行时间锁定吗？
- 有没有监控到卡顿时的数据库连接池状态？
- 系统应用程序的配置文件
- 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执行耗时
我试试换个数据库连接池
