MySQL 从一次反直觉的SQL优化谈起：为何加一列反而更快？

记录一次从“这不科学”到“原来如此”的数据库性能优化之旅。其中还有很多是与deepseek的battle, 虽然他解释基本对，但是我没理解。后面还是对比返回的ID和 relation_code的值，发现问题。

一、现象：违反直觉的性能差异

一切始于一个看似普通的查询需求：从一个日志记录表中筛选出特定状态的记录。表结构简化如下：

sql

CREATE TABLE `biz_log_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `relation_code` varchar(32) DEFAULT NULL COMMENT '关联编号',
  `log_remark` varchar(32) DEFAULT NULL COMMENT '日志备注',
  `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
  -- ... 其他字段省略
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_log_remark` (`log_remark`) USING BTREE,
  KEY `idx_log_remark_relation` (`log_remark`,`relation_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我需要找出未被其他系统处理的记录，最初的查询只返回ID：

sql

-- 查询A：只返回ID（执行时间>4秒）
SELECT lr.id AS recordId
FROM biz_log_record lr 
LEFT JOIN process_record pr ON pr.relation_id = lr.id
WHERE (lr.log_remark != 'SPECIAL_FLAG' OR lr.log_remark IS NULL)
AND pr.relation_id IS NULL
LIMIT 10;

后来业务需要增加返回关联编号，我调整了查询：

sql

-- 查询B：返回ID和relation_code（执行时间<0.1秒）
SELECT lr.id AS recordId, lr.relation_code AS refCode
FROM biz_log_record lr 
LEFT JOIN process_record pr ON pr.relation_id = lr.id
WHERE (lr.log_remark != 'SPECIAL_FLAG' OR lr.log_remark IS NULL)
AND pr.relation_id IS NULL
LIMIT 10;

令人费解的现象出现了：第二个查询多返回了一个字段，执行速度却快了40倍以上！

二、初步排查：执行计划对比

首先使用 EXPLAIN 分析两个查询的执行计划：

查询A（慢查询）的执行计划：

text

+----+-------------+-------+-------+-------------------+-----------------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys     | key             | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+-------------------+-----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | lr    | range | idx_log_remark,...| idx_log_remark  | 131     | NULL | 960k+  | Using where; Using index |
|  1 | SIMPLE      | pr    | ref   | idx_relation      | (略)            | (略)    | (略) | 1      | Using where; Using index |
+----+-------------+-------+-------+-------------------+-----------------+---------+------+--------+--------------------------+

查询B（快查询）的执行计划：

text

+----+-------------+-------+-------+---------------------------+--------------------------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys             | key                      | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------------------+--------------------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | lr    | range | idx_log_remark,...        | idx_log_remark_relation  | 131     | NULL | 960k+  | Using where; Using index |
|  1 | SIMPLE      | pr    | ref   | idx_relation              | (略)                     | (略)    | (略) | 1      | Using where; Using index |
+----+-------------+-------+-------+---------------------------+--------------------------+---------+------+--------+--------------------------+

第一个发现：两个查询都使用了覆盖索引（Using index），但优化器选择了不同的索引：

• 查询A使用了单列索引 idx_log_remark (log_remark)

• 查询B使用了复合索引 idx_log_remark_relation (log_remark,relation_code)

三、深入数据库内核：Handler统计揭示的真相

为了了解数据库底层到底发生了什么，我查看了InnoDB的Handler统计信息：

-- 测试查询A
FLUSH STATUS;
-- 执行查询A
SHOW SESSION STATUS LIKE 'Handler_read%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Handler_read_first | 0 |
| Handler_read_key | 2059505 |
| Handler_read_next | 4118997 | -- 关键指标：顺序读取了411万次！
+--------------------------+---------+

-- 测试查询B
FLUSH STATUS;
-- 执行查询B
SHOW SESSION STATUS LIKE 'Handler_read%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 94 |
| Handler_read_next | 175 | -- 仅读取了175次！
+--------------------------+-------+

关键发现：Handler_read_next 统计显示，查询A在索引中顺序扫描了411万行，而查询B只扫描了175行，相差超过20000倍！

四、数据分布分析：问题的根源

查看 log_remark 字段的数据分布，发现了异常情况：

sql

SELECT count(*), log_remark 
FROM biz_log_record 
GROUP BY log_remark 
ORDER BY count(*) DESC LIMIT 5;
+----------+-------------------+
| count(*) | log_remark        |
+----------+-------------------+
|  2135966 | NULL              |  -- 超过80%的数据是NULL！
|   505530 | SPECIAL_FLAG      |  -- 约19%的数据
|       45 | NORMAL_LOG        |
|        5 | SYSTEM_LOG        |
|        3 | CUST_20251030_0002|
+----------+-------------------+

五、关键验证：复合索引的“魔法”并非万能

我提出了一个假设：是否任意复合索引都能解决这个问题？ 为了验证，我创建了一个测试索引：

sql

-- 创建测试索引：(log_remark, create_time)
ALTER TABLE biz_log_record ADD INDEX idx_test_remark_time(log_remark, create_time);

然后强制查询使用这个新索引：

sql

SELECT lr.id AS recordId
FROM biz_log_record lr FORCE INDEX (idx_test_remark_time)
LEFT JOIN process_record pr ON pr.relation_id = lr.id
WHERE (lr.log_remark != 'SPECIAL_FLAG' OR lr.log_remark IS NULL)
AND pr.relation_id IS NULL
LIMIT 10;

结果出乎意料：执行时间依然>4秒，Handler_read_next 仍然是411万次！

进一步分析 create_time 的分布：

sql

SELECT DATE(create_time) as 日期, COUNT(*) as 记录数
FROM biz_log_record 
WHERE log_remark IS NULL 
GROUP BY DATE(create_time) 
ORDER BY 记录数 DESC LIMIT 3;
+------------+-----------+
| 日期       | 记录数    |
+------------+-----------+
| 2025-12-16 |  783697   |  -- 大量NULL记录集中在最近几天！
| 2025-12-15 |  775135   |
| 2025-10-22 |  302789   |
+------------+-----------+

六、真相揭示：索引的物理存储结构是关键

6.1 单列索引的实际存储

idx_log_remark 索引实际上存储的是两列：(log_remark, id)。

text

索引叶子节点结构示例：
+---------------------+--------+
| log_remark          | id     |
+---------------------+--------+
| NULL                | 1      |
| NULL                | 2      |
| NULL                | 3      |
| ...（213万条连续的NULL记录）... |
| NULL                | 2135966|
| SPECIAL_FLAG        | 2135967|
| ...（其他值）...           |
+---------------------+--------+

问题所在：当执行 log_remark != 'SPECIAL_FLAG' 时：

1. 引擎定位到第一个 NULL 记录

2. 由于需要排除 SPECIAL_FLAG，必须扫描所有非 SPECIAL_FLAG 的记录

3. 在找到10条记录前，它扫描了几乎整个索引（因为80%的数据是NULL且连续存储）

6.2 高效复合索引的存储结构

idx_log_remark_relation 索引存储的是三列：(log_remark, relation_code, id)。

text

索引叶子节点结构示例：
+---------------------+--------------------------+--------+
| log_remark          | relation_code            | id     |
+---------------------+--------------------------+--------+
| NULL                | REF_001                  | 100    |
| NULL                | REF_002                  | 200    |
| NULL                | REF_003                  | 300    |
| NULL                | REF_004                  | 50     | ← id顺序被打乱！
| NULL                | REF_005                  | 150    |
| ...（relation_code各不相同）...                     |
| SPECIAL_FLAG        | REF_X001                 | 250    |
+---------------------+--------------------------+--------+

6.3 (log_remark, create_time) 索引为何无效？

测试索引 idx_test_remark_time 存储的是 (log_remark, create_time, id)，但创建时间高度集中：

text

+---------------------+---------------------+--------+
| log_remark          | create_time         | id     |
+---------------------+---------------------+--------+
| NULL                | 2025-12-15 00:00:01 | 1      |
| NULL                | 2025-12-15 00:00:02 | 2      |
| NULL                | 2025-12-15 00:00:03 | 3      |
| ...（156万条NULL记录集中在两天内）...                 |
| NULL                | 2025-12-16 23:59:59 | 1560000|
+---------------------+---------------------+--------+

关键区别：虽然 create_time 值不同，但大量NULL记录的创建时间高度集中，导致它们在物理存储上仍然是连续的块。

八、根本原因总结

1. 单列索引 (log_remark) 实际存储 (log_remark, id)：按照id 排序，较早的ID排在前面，前面的ID数据恰恰又没有需要的数据。

2. 高效的复合索引需要高区分度的第二列：relation_code ：加入这个列后，按照relation_code排序，打乱了ID的排序，ID不再是从小到大的排序。这里面有需要的ID列。找到10行后，返回结束。

3. 并非所有复合索引都有效：第二列必须与第一列的数据分布独立。create_time 虽然值不同，但与 log_remark 为NULL的记录在时间上高度相关，无法实现真正的“数据打散”。

4. 物理存储顺序决定扫描效率：数据在磁盘上的物理分布比逻辑分布对查询性能影响更大。