|
|
一、现象：看得见，却查不到
线上某张表 t_invoice 出现一个反常现象：同一条记录，去掉某个等值条件能查到，加上却查不到。
-- 查询 A：带状态等值条件 —— 返回空
SELECT biz_id, status, serial_no
FROM t_invoice
WHERE biz_id = 537
AND status = 'PASSED';
-- 查询 B：不带状态条件 —— 返回 6 行，其中 3 行 status = 'PASSED'
SELECT biz_id, status, serial_no
FROM t_invoice
WHERE biz_id = 537;
查询 B 的结果：
biz_id
status
serial_no
537
PASSED
2692*************8629
537
PASSED
2692*************4377
537
PASSED
2692*************1155
537
REJECTED
2692*************6649
537
CANCELLED
2692*************6599
537
REJECTED
2692*************7967
明明有 3 条 PASSED，查询 A 却返回空——典型的"看得见却匹配不到"。数据没丢，问题出在哪？
二、排查
第 1 步：先怀疑隐藏字符 —— 排除
这类问题的头号嫌疑是字段值里混入了不可见字符（尾部空格、零宽字符、BOM ）。用 HEX 看真实字节：
SELECT status,
LENGTH(status) AS byte_len,
CHAR_LENGTH(status) AS char_len,
HEX(status) AS hex_val
FROM t_invoice
WHERE biz_id = 537;
结果：PASSED 的 HEX = 504153534544，纯 ASCII ，6 字节 6 字符，干干净净，无任何隐藏字符。排除数据问题，方向转向优化器。
第 2 步：看表结构 —— 发现一个"不对劲"的主键
SHOW CREATE TABLE t_invoice;
关键字段与索引：
`status` varchar(32) ... COLLATE utf8mb4_general_ci NOT NULL,
...
PRIMARY KEY (`id` DESC, `status`) USING BTREE, -- ⚠ 异常：降序 + 业务列进了主键
KEY `idx_biz` (`biz_id`),
KEY `idx_status` (`status`),
KEY `idx_biz_status` (`biz_id`, `status`)
字符集与排序规则一切正常，但主键 (id DESC, status) 极不寻常：
id 是 AUTO_INCREMENT 自增列，单独做主键就够了，没必要把业务状态列 status 塞进来；
DESC 降序索引对自增主键毫无意义，反而有害——自增插入会变成 B+ 树头部插入，引发页分裂。
这个设计是后面所有麻烦的源头。
第 3 步：对比执行计划 —— 锁定 Using intersect
EXPLAIN SELECT ... WHERE biz_id = 537 AND status = 'PASSED';
EXPLAIN SELECT ... WHERE biz_id = 537;
查询
type
key
Extra
结果
A （查不到）
index_merge
idx_biz_status, idx_status
Using intersect(...)
❌ 空
B （能查到）
ref
idx_biz
-
✅ 6 行
查询 A 触发了 index_merge + Using intersect：优化器对两个索引分别扫描后取交集。这就是头号嫌疑犯。
第 4 步：两个验证 —— 确认 intersect 就是元凶
-- ① 改用 LIKE ，走 range 而非 intersect
WHERE biz_id = 537 AND status LIKE 'PASSED%';
-- 执行计划：type=range, key=idx_biz_status, Extra=Using index condition
-- 结果：✅ 返回 3 条
-- ② 用 hint 关闭 index_merge
SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */ ...
WHERE biz_id = 537 AND status = 'PASSED';
-- 结果：✅ 返回 3 条
LIKE 能查到，说明复合索引物理完好、数据都在；关闭 index_merge 后等值查询也正常——铁证。问题既不是数据、也不是索引损坏，而是 intersect 算法本身算错了。
第 5 步：NO_INDEX 模拟删索引 —— 区分"索引问题"还是"算法问题"
用 NO_INDEX hint 让优化器假装某个索引不存在，无需真正 DDL 就能模拟"删索引后"的执行计划：
-- 模拟删复合索引 idx_biz_status
EXPLAIN SELECT /*+ NO_INDEX(t idx_biz_status) */ ...
-- -> 仍走 intersect(idx_biz, idx_status)，返回空 ❌
-- 模拟删单列索引 idx_status
EXPLAIN SELECT /*+ NO_INDEX(t idx_status) */ ...
-- -> 改走 idx_biz_status ref(const,const)，返回 3 条 ✅
模拟操作
执行计划
结果
删复合索引 idx_biz_status
index_merge / Using intersect(idx_biz, idx_status)
❌ 空
删单列索引 idx_status
ref / idx_biz_status / const,const
✅ 3 条
关键结论：删复合索引没用（还有两个索引继续 intersect ），删 idx_status 才有用（消除了 intersect 的候选）。说明问题不在某个索引，而在"多索引共存触发 intersect + 降序主键"这个组合。
三、根因：降序主键 × index_merge intersect
MySQL 8.0.26-cluster 的 index_merge intersection 算法与降序复合主键不兼容，触发链如下：
等值查询命中多个索引（idx_biz_status 与 idx_status 都覆盖 status 列）；
优化器选择 index_merge intersect，对两个索引的 rowid 集合取交集；
主键含降序列 id DESC，二级索引的 rowid （= 主键值）在交集比较时字节序处理出错；
本应匹配的主键被判为不相等 → 交集为空 → 查询返回空集。
而 LIKE 走 range、不带状态条件走单索引 ref，都不触发 intersect ，所以只有"biz_id = ? AND status = ?"这类等值查询会中招。
四、解决：
止血：应用层加 hint （立即生效）
-- 方式 A：精准指定复合索引（最优，直接定位）
SELECT /*+ INDEX(t idx_biz_status) */
t.biz_id, t.status, t.serial_no
FROM t_invoice t
WHERE t.biz_id = 537 AND t.status = 'PASSED';
-- 方式 B：关闭 index_merge （兜底）
SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */
t.biz_id, t.status, t.serial_no
FROM t_invoice t
WHERE t.biz_id = 537 AND t.status = 'PASSED';
注意：该表所有 biz_id=? AND status=? 等值查询都受影响，需统一加 hint ，不能只改一处。
根治：修正主键（最终采用方案）
由于数据量不大，备份表后，在业务量少时，把主键从 (id DESC, status) 改回标准自增主键 (id)：
-- 1. 备份（务必）
CREATE TABLE t_invoice_bak AS SELECT * FROM t_invoice;
-- 2. 修正主键
ALTER TABLE t_invoice
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`);
-- 3. 验证（应返回 3 条）
SELECT biz_id, status, serial_no
FROM t_invoice
WHERE biz_id = 537 AND status = 'PASSED';
-- 4. 行数核对
SELECT
(SELECT COUNT(*) FROM t_invoice) AS now_cnt,
(SELECT COUNT(*) FROM t_invoice_bak) AS bak_cnt;
安全性：id 为自增全局唯一，(id, status) 唯一 ⟸ id 唯一，改主键不改变任何唯一性语义；自增计数器保留；二级索引 rowid 由 MySQL 自动重建，无需手动干预。 |
|
发表于 2026-7-17 08:30:26