記錄一次從“這不科學”到“原來如此”的數(shù)據(jù)庫性能優(yōu)化之旅。其中還有很多是與deepseek的battle, 雖然他解釋基本對，但是我沒理解。后面還是對比返回的ID和 relation_code的值，發(fā)現(xiàn)問題。
一、現(xiàn)象：違反直覺的性能差異
一切始于一個看似普通的查詢需求：從一個日志記錄表中篩選出特定狀態(tài)的記錄。表結構簡化如下：
sql
CREATE TABLE `biz_log_record` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵', `relation_code` varchar(32) DEFAULT NULL COMMENT '關聯(lián)編號', `log_remark` varchar(32) DEFAULT NULL COMMENT '日志備注', `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '創(chuàng)建時間', -- ... 其他字段省略 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;
我需要找出未被其他系統(tǒng)處理的記錄，最初的查詢只返回ID：
sql
-- 查詢A：只返回ID（執(zhí)行時間>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;
后來業(yè)務需要增加返回關聯(lián)編號，我調整了查詢：
sql
-- 查詢B：返回ID和relation_code（執(zhí)行時間<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;
令人費解的現(xiàn)象出現(xiàn)了：第二個查詢多返回了一個字段，執(zhí)行速度卻快了40倍以上！
二、初步排查：執(zhí)行計劃對比
首先使用 EXPLAIN 分析兩個查詢的執(zhí)行計劃：
查詢A（慢查詢）的執(zhí)行計劃：
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（快查詢）的執(zhí)行計劃：
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 | +----+-------------+-------+-------+---------------------------+--------------------------+---------+------+--------+--------------------------+
第一個發(fā)現(xiàn)：兩個查詢都使用了覆蓋索引（Using index），但優(yōu)化器選擇了不同的索引：
查詢A使用了單列索引
idx_log_remark(log_remark)查詢B使用了復合索引
idx_log_remark_relation(log_remark,relation_code)
三、深入數(shù)據(jù)庫內核：Handler統(tǒng)計揭示的真相
為了了解數(shù)據(jù)庫底層到底發(fā)生了什么，我查看了InnoDB的Handler統(tǒng)計信息：
-- 測試查詢A
FLUSH STATUS;
-- 執(zhí)行查詢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;
-- 執(zhí)行查詢B
SHOW SESSION STATUS LIKE 'Handler_read%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 94 |
| Handler_read_next | 175 | -- 僅讀取了175次！
+--------------------------+-------+
關鍵發(fā)現(xiàn)：Handler_read_next 統(tǒng)計顯示，查詢A在索引中順序掃描了411萬行，而查詢B只掃描了175行，相差超過20000倍！
四、數(shù)據(jù)分布分析：問題的根源
查看 log_remark 字段的數(shù)據(jù)分布，發(fā)現(xiàn)了異常情況：
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%的數(shù)據(jù)是NULL！ | 505530 | SPECIAL_FLAG | -- 約19%的數(shù)據(jù) | 45 | NORMAL_LOG | | 5 | SYSTEM_LOG | | 3 | CUST_20251030_0002| +----------+-------------------+
五、關鍵驗證：復合索引的“魔法”并非萬能
我提出了一個假設：是否任意復合索引都能解決這個問題？ 為了驗證，我創(chuàng)建了一個測試索引：
sql
-- 創(chuàng)建測試索引：(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;
結果出乎意料：執(zhí)行時間依然>4秒，Handler_read_next 仍然是411萬次！
進一步分析 create_time 的分布：
sql
SELECT DATE(create_time) as 日期, COUNT(*) as 記錄數(shù) FROM biz_log_record WHERE log_remark IS NULL GROUP BY DATE(create_time) ORDER BY 記錄數(shù) DESC LIMIT 3; +------------+-----------+ | 日期 | 記錄數(shù) | +------------+-----------+ | 2025-12-16 | 783697 | -- 大量NULL記錄集中在最近幾天！ | 2025-12-15 | 775135 | | 2025-10-22 | 302789 | +------------+-----------+
六、真相揭示：索引的物理存儲結構是關鍵
6.1 單列索引的實際存儲
idx_log_remark 索引實際上存儲的是兩列：(log_remark, id)。
text
索引葉子節(jié)點結構示例： +---------------------+--------+ | log_remark | id | +---------------------+--------+ | NULL | 1 | | NULL | 2 | | NULL | 3 | | ...（213萬條連續(xù)的NULL記錄）... | | NULL | 2135966| | SPECIAL_FLAG | 2135967| | ...（其他值）... | +---------------------+--------+
問題所在：當執(zhí)行 log_remark != 'SPECIAL_FLAG' 時：
引擎定位到第一個
NULL記錄由于需要排除
SPECIAL_FLAG，必須掃描所有非SPECIAL_FLAG的記錄在找到10條記錄前，它掃描了幾乎整個索引（因為80%的數(shù)據(jù)是NULL且連續(xù)存儲）
6.2 高效復合索引的存儲結構
idx_log_remark_relation 索引存儲的是三列：(log_remark, relation_code, id)。
text
索引葉子節(jié)點結構示例： +---------------------+--------------------------+--------+ | 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)，但創(chuàng)建時間高度集中：
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| +---------------------+---------------------+--------+
關鍵區(qū)別：雖然 create_time 值不同，但大量NULL記錄的創(chuàng)建時間高度集中，導致它們在物理存儲上仍然是連續(xù)的塊。
八、根本原因總結
單列索引
(log_remark)實際存儲(log_remark, id)：按照id 排序，較早的ID排在前面，前面的ID數(shù)據(jù)恰恰又沒有需要的數(shù)據(jù)。高效的復合索引需要高區(qū)分度的第二列：
relation_code：加入這個列后，按照relation_code排序，打亂了ID的排序，ID不再是從小到大的排序。這里面有需要的ID列。找到10行后，返回結束。并非所有復合索引都有效：第二列必須與第一列的數(shù)據(jù)分布獨立。
create_time雖然值不同，但與log_remark為NULL的記錄在時間上高度相關，無法實現(xiàn)真正的“數(shù)據(jù)打散”。物理存儲順序決定掃描效率：數(shù)據(jù)在磁盤上的物理分布比邏輯分布對查詢性能影響更大。
