测试结果：
explain format="verbose"
SELECT
MIN(id),
MAX(id)
FROM
`merchant_operation_log`
WHERE
(
(
created_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
OR (
modified_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
AND id IS NOT NULL
);
+------------------------------+--------------+-----------------+-----------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+------------------------------+--------------+-----------------+-----------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_13 | 1.00 | 24029006145.31 | root | | funcs:min(Column#20)->Column#18, funcs:max(Column#21)->Column#19 |
| └─TableReader_14 | 1.00 | 24029004607.75 | root | | data:HashAgg_6 |
| └─HashAgg_6 | 1.00 | 360435069052.86 | cop[tikv] | | funcs:min(dw_merchant_log.merchant_operation_log.id)->Column#20, funcs:max(dw_merchant_log.merchant_operation_log.id)->Column#21 |
| └─Selection_12 | 692744456.80 | 339694298508.65 | cop[tikv] | | or(and(ge(dw_merchant_log.merchant_operation_log.created_at, 2025-12-07), le(dw_merchant_log.merchant_operation_log.created_at, 2025-12-11)), and(ge(dw_merchant_log.merchant_operation_log.modified_at, 2025-12-07), and(le(dw_merchant_log.merchant_operation_log.modified_at, 2025-12-11), 1))) |
| └─TableFullScan_11 | 865930571.00 | 296484363015.75 | cop[tikv] | table:merchant_operation_log | keep order:false |
+------------------------------+--------------+-----------------+-----------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set, 5 warnings (0.01 sec)
(root@127.0.0.1) [(none)]>show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: copSingleReadTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: copMultiReadTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: rootTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: copSingleReadTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
explain format="verbose"
SELECT /*+ USE_INDEX_MERGE(merchant_operation_log,idx_create_time,idx_modify_time) */
MIN(id),
MAX(id)
FROM
`merchant_operation_log`
WHERE
(
(
created_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
OR (
modified_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
AND id IS NOT NULL
);
+------------------------------+--------------+-----------------+-----------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+------------------------------+--------------+-----------------+-----------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_13 | 1.00 | 24029006145.31 | root | | funcs:min(Column#20)->Column#18, funcs:max(Column#21)->Column#19 |
| └─TableReader_14 | 1.00 | 24029004607.75 | root | | data:HashAgg_6 |
| └─HashAgg_6 | 1.00 | 360435069052.86 | cop[tikv] | | funcs:min(dw_merchant_log.merchant_operation_log.id)->Column#20, funcs:max(dw_merchant_log.merchant_operation_log.id)->Column#21 |
| └─Selection_12 | 692744456.80 | 339694298508.65 | cop[tikv] | | or(and(ge(dw_merchant_log.merchant_operation_log.created_at, 2025-12-07), le(dw_merchant_log.merchant_operation_log.created_at, 2025-12-11)), and(ge(dw_merchant_log.merchant_operation_log.modified_at, 2025-12-07), and(le(dw_merchant_log.merchant_operation_log.modified_at, 2025-12-11), 1))) |
| └─TableFullScan_11 | 865930571.00 | 296484363015.75 | cop[tikv] | table:merchant_operation_log | keep order:false |
+------------------------------+--------------+-----------------+-----------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set, 6 warnings (0.00 sec)
show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | IndexMerge is inapplicable |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: copSingleReadTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: copMultiReadTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: rootTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: copSingleReadTask} |
| Note | 1105 | [merchant_operation_log,idx_created_at] remain after pruning paths for merchant_operation_log given Prop{SortItems: [], TaskTp: rootTask} |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
explain format="verbose"
SELECT
MIN(id),
MAX(id)
FROM
`merchant_operation_log`
WHERE
(
(
created_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
OR (
modified_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
-- AND id IS NOT NULL
);
+----------------------------------+------------+---------------+-----------+------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------+------------+---------------+-----------+------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_7 | 1.00 | 438186542.08 | root | | funcs:min(dw_merchant_log.merchant_operation_log.id)->Column#18, funcs:max(dw_merchant_log.merchant_operation_log.id)->Column#19 |
| └─IndexMerge_14 | 5008717.95 | 288224018.98 | root | | type: union |
| ├─IndexRangeScan_11(Build) | 2507990.92 | 510376151.72 | cop[tikv] | table:merchant_operation_log, index:idx_created_at(created_at) | range:[2025-12-07 00:00:00,2025-12-11 00:00:00], keep order:false |
| ├─IndexRangeScan_12(Build) | 2507990.92 | 510376151.72 | cop[tikv] | table:merchant_operation_log, index:idx_modified_at(modified_at) | range:[2025-12-07 00:00:00,2025-12-11 00:00:00], keep order:false |
| └─TableRowIDScan_13(Probe) | 5008717.95 | 1714925654.83 | cop[tikv] | table:merchant_operation_log | keep order:false |
+----------------------------------+------------+---------------+-----------+------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
explain format="verbose"
SELECT
MIN(id),
MAX(id)
FROM
`merchant_operation_log`
WHERE
(
(
created_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
OR (
modified_at BETWEEN date_sub(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 3 DAY
)
AND date_add(
str_to_date('20251210', '%Y%m%d'),
INTERVAL 1 DAY
)
)
)AND id IS NOT NULL;
+----------------------------------+------------+---------------+-----------+------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------+------------+---------------+-----------+------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_7 | 1.00 | 438186542.08 | root | | funcs:min(dw_merchant_log.merchant_operation_log.id)->Column#18, funcs:max(dw_merchant_log.merchant_operation_log.id)->Column#19 |
| └─IndexMerge_14 | 5008717.95 | 288224018.98 | root | | type: union |
| ├─IndexRangeScan_11(Build) | 2507990.92 | 510376151.72 | cop[tikv] | table:merchant_operation_log, index:idx_created_at(created_at) | range:[2025-12-07 00:00:00,2025-12-11 00:00:00], keep order:false |
| ├─IndexRangeScan_12(Build) | 2507990.92 | 510376151.72 | cop[tikv] | table:merchant_operation_log, index:idx_modified_at(modified_at) | range:[2025-12-07 00:00:00,2025-12-11 00:00:00], keep order:false |
| └─TableRowIDScan_13(Probe) | 5008717.95 | 1714925654.83 | cop[tikv] | table:merchant_operation_log | keep order:false |
+----------------------------------+------------+---------------+-----------+------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
可以看到 hint 的时候：
Warning | 1105 | IndexMerge is inapplicable
优化器认为这个查询不适用 Index Merge
-- 括号内的 AND id IS NOT NULL
or(
and(ge(...created_at...), le(...created_at...)),
and(ge(...modified_at...), and(le(...modified_at...), 1)) -- 注意这里多了 and(..., 1)
)
这里的 and(..., 1) 实际上是 id IS NOT NULL 的谓词表达式（因为 id IS NOT NULL 总是为真，被转换为常量 1）。
Index Merge 不生效的根本原因是：
| 原因 |
说明 |
| 运算符优先级 |
括号内的 AND id IS NOT NULL 只绑定到 modified_at 条件 |
| 非对称的 OR 分支 |
左分支和右分支的过滤逻辑不一致 |
| 破坏 Index Merge 模型 |
Index Merge 要求各分支独立且对称，不能有分支特定的额外过滤 |
| 优化器判断不适用 |
Warning “IndexMerge is inapplicable” 明确说明无法使用 |
