MySQL 索引優化實戰：從 CPU 100% 到負載 1.73 的慢查詢根治過程

一個真實的 MySQL 索引優化案例複盤

線上系統 CPU 飆升至 36，mysqladmin processlist 顯示大量 Sending data 狀態的慢查詢：

SELECT * FROM `rep_corp_vehicle_online_count` 
WHERE corp_id = 48 AND vehicle_id = 10017543

現場信息收集

表結構

DESC rep_corp_vehicle_online_count;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| corp_id     | int(11)     | NO   |     | NULL    |                |
| vehicle_id  | int(11)     | NO   |     | NULL    |                |
| online_day  | varchar(20) | NO   |     | NULL    |                |
| loc_total   | int(11)     | NO   |     | NULL    |                |
| create_time | datetime    | NO   |     | NULL    |                |
| update_time | datetime    | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

現有索引

SHOW INDEX FROM rep_corp_vehicle_online_count;
-- 僅有主鍵 PRIMARY (id)

執行計劃分析

EXPLAIN SELECT * FROM rep_corp_vehicle_online_count 
WHERE corp_id = 79 AND vehicle_id = 10016911 AND online_day = '2016-03-29'\G
*************************** 1. row ***************************
type: ALL           -- 全表掃描
possible_keys: NULL -- 無可用索引
key: NULL
rows: 1248495       -- 掃描 124 萬行
Extra: Using where

數據分佈特徵

SELECT COUNT(DISTINCT corp_id) FROM ...;     -- 18 個企業
SELECT COUNT(corp_id) FROM ...;               -- 123 萬行
SELECT COUNT(DISTINCT vehicle_id) FROM ...;   -- 2580 輛車
SELECT COUNT(vehicle_id) FROM ...;            -- 123 萬行

結論：corp_id 區分度極低（18 個值），vehicle_id 中等（2580 個值），單列索引效果有限，需複合索引。

優化動作：創建複合索引

CREATE INDEX r_c_v ON rep_corp_vehicle_online_count(corp_id, vehicle_id);

耗時 6 秒，影響 148 萬行。

優化後驗證

SHOW INDEX FROM rep_corp_vehicle_online_count;
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+
| Table                         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+
| rep_corp_vehicle_online_count | 0          | PRIMARY  | 1            | id          | A         | 1490176     |
| rep_corp_vehicle_online_count | 1          | r_c_v    | 1            | corp_id     | A         | 18          |
| rep_corp_vehicle_online_count | 1          | r_c_v    | 2            | vehicle_id  | A         | 2596        |
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+

CPU 負載從 36 直降至 1.73，查詢從全表掃描轉為索引範圍掃描，響應時間毫秒級。

核心經驗總結

1. 低基數列必須組合高基數列建複合索引，順序遵循「最左前綴」與「區分度遞增」
2. EXPLAIN 關注 type（避免 ALL）、rows（預估掃描行）、Extra（Using where/index condition）
3. 大表加索引需評估寫入影響，可在低峰期 ALGORITHM=INPLACE 在線建索
4. 持續監控 SHOW GLOBAL STATUS LIKE 'Handler_read%' 觀察索引命中率