转载请标明出处：https://blog.csdn.net/men_ma/article/details/106847165.
本文出自 不怕报错 就怕不报错的小猿猿 的博客
当报错Exception: Too many optimizations applied to query plan. Current limit 10000，SQL占位符过多解决方法
原因：
当我同一个接口，在测试、UAT环境都没有问题时，上了生产就报以下错误
Code: 572. DB::Exception: Too many optimizations applied to query plan. Current limit 10000. (TOO_MANY_QUERY_PLAN_OPTIMIZATIONS) (version 23.4.2.11 (official build))\n"
原查询SQL如下：
<select id="getStoreLevelCompareTable" resultType="com.datastory.gac.domain.vo.store.StoreLevelCompareVO">
SELECT
test.storeCode,
max(test.flowNum) AS flowNum,
max(test.flowNumDay) AS flowNumDay,
max(test.avgRetentionDay) AS avgRetentionDay,
max(test.flowNumBatchDay) AS flowNumBatchDay
FROM
(
SELECT
store_code AS storeCode,
SUM(count) AS flowNum,
0 AS flowNumDay,
0 AS avgRetentionDay,
0 AS flowNumBatchDay
FROM gac_customer_flow hd
<if test="storeHours != null and storeHours.size > 0">
left join
<foreach collection="storeHours" item="item" open="(" close=")" separator="union all">
select #{item.storeCode} as store_code, #{item.startTime} as startTime, #{item.endTime} as endTime
</foreach>
as temp on hd.store_code = temp.store_code
</if>
<where>
<if test="storeHours != null and storeHours.size > 0">
hd.public_time >= toDate(hd.public_time) + toIntervalSecond(temp.startTime) and
hd.public_time < toDate(hd.public_time) + toIntervalSecond(temp.endTime)
</if>
<if test="storeCodes != null and storeCodes.size > 0">
and hd.store_code in
<foreach collection="storeCodes" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
and toDate(hd.public_time) BETWEEN #{startTime} and #{endTime}
AND hd.data_type = 0
</where>
GROUP BY hd.store_code
having toUInt64(flowNum)>0
UNION ALL
SELECT
store_code AS storeCode,
0 AS flowNum,
round( SUM ( timeTotalNum ) / COUNT ( * ) ) AS flowNumDay,
0 AS avgRetentionDay,
0 AS flowNumBatchDay
FROM
(
SELECT
toDate(hd.public_time) AS timeFrame,
store_code,
SUM(count) AS timeTotalNum
FROM gac_customer_flow hd
<if test="storeHours != null and storeHours.size > 0">
left join
<foreach collection="storeHours" item="item" open="(" close=")" separator="union all">
select #{item.storeCode} as store_code, #{item.startTime} as startTime, #{item.endTime} as endTime
</foreach>
as temp on hd.store_code = temp.store_code
</if>
<where>
<if test="storeHours != null and storeHours.size > 0">
hd.public_time >= toDate(hd.public_time) + toIntervalSecond(temp.startTime) and
hd.public_time < toDate(hd.public_time) + toIntervalSecond(temp.endTime)
</if>
<if test="storeCodes != null and storeCodes.size > 0">
and hd.store_code in
<foreach collection="storeCodes" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
and toDate(hd.public_time) BETWEEN #{startTime} and #{endTime}
AND hd.data_type = 0
</where>
GROUP BY toDate(hd.public_time), hd.store_code
having timeTotalNum>0
) AS tmp
GROUP BY store_code
UNION ALL
select
temp.store_code AS storeCode,
0 AS flowNum,
0 AS flowNumDay,
round( ( SUM ( temp.value ) / COUNT ( * ) ) / 60, 2 ) AS avgRetentionDay,
0 AS flowNumBatchDay
from (
SELECT store_code,
gsar.public_time AS xAxisName,
SUM(gsar.avg_retention) as value
FROM gac_store_avg_retention gsar
<where>
gsar.public_time BETWEEN #{startTime} and #{endTime}
<if test="storeCodes != null and storeCodes.size > 0">
and gsar.store_code in
<foreach collection="storeCodes" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
</where>
GROUP BY store_code, xAxisName
)temp
group by temp.store_code
UNION ALL
SELECT
store_code AS storeCode,
0 AS flowNum,
0 AS flowNumDay,
0 AS avgRetentionDay,
round(SUM(personBatchNum) / COUNT(*)) AS flowNumBatchDay
FROM
(
select
hd.batch_id,
hd.store_code,
toDate ( hd.stu_event_time ) as timeFrame,
COUNT( DISTINCT hd.batch_id ) AS personBatchNum
from
gac_batch_customer_flow hd
left join
<foreach collection="storeHours" item="item" open="(" close=")" separator="union all">
select #{item.storeCode} as store_code, #{item.startTime} as startTime, #{item.endTime} as endTime
</foreach>
as temp on hd.store_code = temp.store_code
<where>
<if test="storeHours != null and storeHours.size > 0">
hd.stu_event_time >= toDate(hd.stu_event_time) + toIntervalSecond(temp.startTime)
and
hd.stu_event_time < toDate(hd.stu_event_time) + toIntervalSecond(temp.endTime)
</if>
<if test="storeCodes != null and storeCodes.size > 0">
and hd.store_code in
<foreach collection="storeCodes" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
and toDate(hd.stu_event_time) BETWEEN #{startTime} and #{endTime}
</where>
GROUP BY
hd.batch_id,hd.store_code,toDate(hd.stu_event_time)
having personBatchNum>0
) AS tmp
GROUP BY store_code
) test
GROUP BY
test.storeCode
<if test="ascOrDesc==1 and fieldName=='flowNum'">
order by flowNum asc
</if>
<if test="ascOrDesc==0 and fieldName=='flowNum'">
order by flowNum desc
</if>
<if test="ascOrDesc==1 and fieldName=='flowNumDay'">
order by flowNumDay asc
</if>
<if test="ascOrDesc==0 and fieldName=='flowNumDay'">
order by flowNumDay desc
</if>
<if test="ascOrDesc==1 and fieldName=='avgRetentionDay'">
order by avgRetentionDay asc
</if>
<if test="ascOrDesc==0 and fieldName=='avgRetentionDay'">
order by avgRetentionDay desc
</if>
<if test="ascOrDesc==1 and fieldName=='flowNumBatchDay'">
order by flowNumBatchDay asc
</if>
<if test="ascOrDesc==0 and fieldName=='flowNumBatchDay'">
order by flowNumBatchDay desc
</if>
limit 20
</select>
由于同一个list参数在多个子查询中使用，用了很多foreach ，在运行SQL时，日志也也一大片？（占位符）导致SQL太长，例如：storeHours这个集合中有七八百条营业时间数据，storeCodes有三百多家门店，在这其中我还用了UNION ALL去联查，光一个子查询就有上千个占位符，四五个子查询，占位符就在日志中直接刷屏了，数据库的性能也不会支持
解决办法：
把storeHours集合中的参数用java去拼接直接参数，再用临时表去存放数据，在用到该临时表时就根据门店编号联查，这样就不会造成每个子查询都有一大片占位符了
把storeCodes集合也是用java直接拼接现成的，都用${} SQL注入方式，而不用#{} 了
代码处理如下：
//拼接营业时间SQL:避免SQL遍历存在过多占位符导致报错
StringBuilder sql = new StringBuilder();
if (ToolUtil.isNotEmpty(storeHours)) {
for (int i = 0; i < storeHours.size(); i++) {
StoreHourDto storeHour = storeHours.get(i);
sql.append("SELECT '").append(storeHour.getStoreCode()).append("' AS storeCode,")
.append(storeHour.getStartTime()).append(" AS startTime,")
.append(storeHour.getEndTime()).append(" AS endTime");
if (i < storeHours.size() - 1) {
sql.append(" UNION ALL ");
}
}
}
//拼接门店编号，避免遍历存在过多的占位符导致报错
StringBuilder storeSql = new StringBuilder();
List<String> storeCodes = levelCompareDto.getStoreCodes();
String storeCodeString=null;
if (ToolUtil.isNotEmpty(storeCodes)){
for (int i = 0; i < storeCodes.size(); i++) {
storeSql.append("'"+storeCodes.get(i)+"'");
if (i < storeCodes.size() - 1) {
storeSql.append(", ");
}
}
storeCodeString=storeSql.toString();
}
最后修改后的SQL：
<select id="getStoreLevelCompareTable" resultType="com.datastory.gac.domain.vo.store.StoreLevelCompareVO">
WITH StoreHourseTable AS (
SELECT
item.storeCode as store_code,
item.startTime,
item.endTime
FROM (
${storeHourSql}
) as item
)
SELECT
test.storeCode,
max(test.flowNum) AS flowNum,
max(test.flowNumDay) AS flowNumDay,
max(test.avgRetentionDay) AS avgRetentionDay,
max(test.flowNumBatchDay) AS flowNumBatchDay
FROM
(
SELECT
store_code AS storeCode,
SUM(count) AS flowNum,
0 AS flowNumDay,
0 AS avgRetentionDay,
0 AS flowNumBatchDay
FROM gac_customer_flow hd
<if test="storeHours != null and storeHours.size > 0">
left join StoreHourseTable as temp on hd.store_code = temp.store_code
</if>
<where>
<if test="storeHours != null and storeHours.size > 0">
hd.public_time >= toDate(hd.public_time) + toIntervalSecond(temp.startTime) and
hd.public_time < toDate(hd.public_time) + toIntervalSecond(temp.endTime)
</if>
<if test="storeCodes != null and storeCodes != ''">
and hd.store_code in (${storeCodes})
</if>
and toDate(hd.public_time) BETWEEN #{startTime} and #{endTime}
AND hd.data_type = 0
</where>
GROUP BY hd.store_code
having toUInt64(flowNum)>0
UNION ALL
SELECT
store_code AS storeCode,
0 AS flowNum,
round( SUM ( timeTotalNum ) / COUNT ( * ) ) AS flowNumDay,
0 AS avgRetentionDay,
0 AS flowNumBatchDay
FROM
(
SELECT
toDate(hd.public_time) AS timeFrame,
store_code,
SUM(count) AS timeTotalNum
FROM gac_customer_flow hd
<if test="storeHours != null and storeHours.size > 0">
left join StoreHourseTable as temp on hd.store_code = temp.store_code
</if>
<where>
<if test="storeHours != null and storeHours.size > 0">
hd.public_time >= toDate(hd.public_time) + toIntervalSecond(temp.startTime) and
hd.public_time < toDate(hd.public_time) + toIntervalSecond(temp.endTime)
</if>
<if test="storeCodes != null and storeCodes != ''">
and hd.store_code in (${storeCodes})
</if>
and toDate(hd.public_time) BETWEEN #{startTime} and #{endTime}
AND hd.data_type = 0
</where>
GROUP BY toDate(hd.public_time), hd.store_code
having timeTotalNum>0
) AS tmp
GROUP BY store_code
UNION ALL
select
temp.store_code AS storeCode,
0 AS flowNum,
0 AS flowNumDay,
round( ( SUM ( temp.value ) / COUNT ( * ) ) / 60, 2 ) AS avgRetentionDay,
0 AS flowNumBatchDay
from (
SELECT store_code,
gsar.public_time AS xAxisName,
SUM(gsar.avg_retention) as value
FROM gac_store_avg_retention gsar
<where>
gsar.public_time BETWEEN #{startTime} and #{endTime}
<if test="storeCodes != null and storeCodes != ''">
and gsar.store_code in (${storeCodes})
</if>
</where>
GROUP BY store_code, xAxisName
)temp
group by temp.store_code
UNION ALL
SELECT
store_code AS storeCode,
0 AS flowNum,
0 AS flowNumDay,
0 AS avgRetentionDay,
round(SUM(personBatchNum) / COUNT(*)) AS flowNumBatchDay
FROM
(
select
hd.batch_id,
hd.store_code,
toDate ( hd.stu_event_time ) as timeFrame,
COUNT( DISTINCT hd.batch_id ) AS personBatchNum
from
gac_batch_customer_flow hd
<if test="storeHours != null and storeHours.size > 0">
left join StoreHourseTable as temp on hd.store_code = temp.store_code
</if>
<where>
<if test="storeHours != null and storeHours.size > 0">
hd.stu_event_time >= toDate(hd.stu_event_time) + toIntervalSecond(temp.startTime)
and
hd.stu_event_time < toDate(hd.stu_event_time) + toIntervalSecond(temp.endTime)
</if>
<if test="storeCodes != null and storeCodes != ''">
and hd.store_code in (${storeCodes})
</if>
and toDate(hd.stu_event_time) BETWEEN #{startTime} and #{endTime}
</where>
GROUP BY
hd.batch_id,hd.store_code,toDate(hd.stu_event_time)
having personBatchNum>0
) AS tmp
GROUP BY store_code
) test
GROUP BY
test.storeCode
<if test="ascOrDesc==1 and fieldName=='flowNum'">
order by flowNum asc
</if>
<if test="ascOrDesc==0 and fieldName=='flowNum'">
order by flowNum desc
</if>
<if test="ascOrDesc==1 and fieldName=='flowNumDay'">
order by flowNumDay asc
</if>
<if test="ascOrDesc==0 and fieldName=='flowNumDay'">
order by flowNumDay desc
</if>
<if test="ascOrDesc==1 and fieldName=='avgRetentionDay'">
order by avgRetentionDay asc
</if>
<if test="ascOrDesc==0 and fieldName=='avgRetentionDay'">
order by avgRetentionDay desc
</if>
<if test="ascOrDesc==1 and fieldName=='flowNumBatchDay'">
order by flowNumBatchDay asc
</if>
<if test="ascOrDesc==0 and fieldName=='flowNumBatchDay'">
order by flowNumBatchDay desc
</if>
limit 20
</select>
如果大家有什么更好的解决办法欢迎大家评论区留言哦！
5744
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
