記錄一次從“這不科學”到“原來如此”的數(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ù)在磁盤上的物理分布比邏輯分布對查詢性能影響更大。




