SQL 查不到数据，数据却明明存在——MySQL 降序主键与 index_merge intersect 的一次诡异排查
# 一、现象：看得见，却查不到
线上某张表 `t_invoice` 出现一个反常现象：同一条记录，去掉某个等值条件能查到，加上却查不到。
```sql
-- 查询 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` 看真实字节：
```sql
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 步：看表结构 —— 发现一个"不对劲"的主键
```sql
SHOW CREATE TABLE t_invoice;
```
关键字段与索引：
```sql
`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`
```sql
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 就是元凶
```sql
-- ① 改用 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 就能模拟"删索引后"的执行计划：
```sql
-- 模拟删复合索引 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` 算法与**降序复合主键**不兼容，触发链如下：
1. 等值查询命中多个索引（`idx_biz_status` 与 `idx_status` 都覆盖 `status` 列）；
2. 优化器选择 `index_merge intersect`，对两个索引的 rowid 集合取交集；
3. 主键含降序列 `id DESC`，二级索引的 rowid （= 主键值）在交集比较时**字节序处理出错**；
4. 本应匹配的主键被判为不相等 → 交集为空 → 查询返回空集。
而 `LIKE` 走 `range`、不带状态条件走单索引 `ref`，都不触发 intersect ，所以只有"`biz_id = ? AND status = ?`"这类等值查询会中招。
---
# 四、解决：
## 止血：应用层加 hint （立即生效）
```sql
-- 方式 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)`：
```sql
-- 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 自动重建，无需手动干预。
---
原文链接：[点击查看](https://www.v2ex.com/t/1227886)
线上某张表 `t_invoice` 出现一个反常现象：同一条记录，去掉某个等值条件能查到，加上却查不到。
```sql
-- 查询 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` 看真实字节：
```sql
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 步：看表结构 —— 发现一个"不对劲"的主键
```sql
SHOW CREATE TABLE t_invoice;
```
关键字段与索引：
```sql
`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`
```sql
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 就是元凶
```sql
-- ① 改用 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 就能模拟"删索引后"的执行计划：
```sql
-- 模拟删复合索引 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` 算法与**降序复合主键**不兼容，触发链如下：
1. 等值查询命中多个索引（`idx_biz_status` 与 `idx_status` 都覆盖 `status` 列）；
2. 优化器选择 `index_merge intersect`，对两个索引的 rowid 集合取交集；
3. 主键含降序列 `id DESC`，二级索引的 rowid （= 主键值）在交集比较时**字节序处理出错**；
4. 本应匹配的主键被判为不相等 → 交集为空 → 查询返回空集。
而 `LIKE` 走 `range`、不带状态条件走单索引 `ref`，都不触发 intersect ，所以只有"`biz_id = ? AND status = ?`"这类等值查询会中招。
---
# 四、解决：
## 止血：应用层加 hint （立即生效）
```sql
-- 方式 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)`：
```sql
-- 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 自动重建，无需手动干预。
---
原文链接：[点击查看](https://www.v2ex.com/t/1227886)
· 0 个赞
· 0 个赞
· 0 个赞
· 0 个赞