Please indicate the source when reprinting: https://blog.csdn.net/men_ma/article/details/106847165.
This article comes from the blog of Little Yuanyuan who is not afraid of reporting errors but is afraid of not reporting errors.
When an error is reported Exception: Too many optimizations applied to query plan. Current limit 10000, solution to too many SQL placeholders
- reason:
-
- The original query SQL is as follows:
- Solution:
- The last modified SQL:
Reason:
When I have the same interface and there are no problems in the test and UAT environment, the following error is reported when I go to production.
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))\\ "
The original query SQL is as follows:
<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 & amp;lt; 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 & amp;lt; 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 & amp;lt; 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>
Since the same list parameter is used in multiple subqueries and a lot of foreach is used, the log is also very large when running SQL? (Placeholder) causes the SQL to be too long. For example: there are 700 or 800 business hours data in the storeHours collection, and there are more than 300 stores in storeCodes. Among them, I also used UNION ALL to perform a joint query, which is just a subquery. There are thousands of placeholders and four or five subqueries. The placeholders are directly flushed in the log, and the performance of the database will not support it.
Solution:
Use Java to splice the parameters in the storeHours collection with direct parameters, and then use a temporary table to store the data. When using the temporary table, perform a joint query based on the store number, so that each subquery will not have a large number of placeholders.
The storeCodes collection is also spliced directly using java, using ${} SQL injection instead of #{}.
The code processing is as follows:
//Splicing business hours SQL: Avoiding errors caused by too many placeholders in SQL traversal
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 ");
}
}
}
//Splice store numbers to avoid errors caused by too many placeholders in traversal
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();
}
Last modified 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 & amp;lt; 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 & amp;lt; 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 & amp;lt; 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>
If you have any better solutions, please leave a message in the comment area!