MySQL 索引優化實戰：從 CPU 36 降至 1.73 的複合索引創建與數據分佈分析全過程

MySQL 索引優化實戰：從 CPU 36 降至 1.73 的複合索引創建與數據分佈分析全過程

生產環境 MySQL CPU 飆升至 36，mysqladmin processlist 顯示大量 SELECT * FROM rep_corp_vehicle_online_count WHERE corp_id = ? AND vehicle_id = ? 全表掃描。本文完整復盤從現象觀測、表結構分析、數據分佈掃描、EXPLAIN 計劃對比、創建複合索引到負載驟降的全過程。

一、 現場觀測

現象：CPU 負載 36，大量慢查詢堵塞。

進程列表：

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    |                |
+-------------+-------------+------+-----+---------+----------------+

現有索引：僅主鍵 PRIMARY KEY (id)。

SHOW INDEX FROM rep_corp_vehicle_online_count;
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rep_corp_vehicle_online_count | 0          | PRIMARY  | 1            | id          | A         | 1247259     | NULL     | NULL   |      | BTREE      |         |               |
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

三、 執行計劃確認全表掃描

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 ***************************
id: 1
select_type: SIMPLE
table: rep_corp_vehicle_online_count
type: ALL                    -- 全表掃描
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1248495                -- 掃描 124 萬行
Extra: Using where
1 row in set (0.00 sec)

四、 數據分佈掃描：發現高重複度，極適合建索引

-- corp_id 僅 18 個不同值，極低基數
SELECT COUNT(DISTINCT corp_id) FROM rep_corp_vehicle_online_count; -- 18
SELECT COUNT(corp_id) FROM rep_corp_vehicle_online_count;          -- 123 萬

-- vehicle_id 2580 個不同值，中等基數
SELECT COUNT(DISTINCT vehicle_id) FROM rep_corp_vehicle_online_count; -- 2580
SELECT COUNT(vehicle_id) FROM rep_corp_vehicle_online_count;         -- 123 萬

結論：corp_id 區分度極低（18 個值），單列索引效果差；vehicle_id 區分度中等。組合索引 (corp_id, vehicle_id) 可先按 corp_id 過濾到少量分區，再按 vehicle_id 精確定位，效果最佳。

五、 創建複合索引並驗證

CREATE INDEX r_c_v ON rep_corp_vehicle_online_count(corp_id, vehicle_id);
Query OK, 1487993 rows affected (6.09 sec)

索引創建後結構：

SHOW INDEX FROM rep_corp_vehicle_online_count;
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rep_corp_vehicle_online_count | 0          | PRIMARY  | 1            | id          | A         | 1490176     | NULL     | NULL   |      | BTREE      |         |               |
| rep_corp_vehicle_online_count | 1          | r_c_v    | 1            | corp_id     | A         | 18          | NULL     | NULL   |      | BTREE      |         |               |
| rep_corp_vehicle_online_count | 1          | r_c_v    | 2            | vehicle_id  | A         | 2596        | NULL     | NULL   |      | BTREE      |         |               |
+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

六、 效果驗證：CPU 從 36 降至 1.73

同樣 SQL 現走 ref 類型，掃描行數從 124 萬降至預估個位數，CPU 負載瞬間回落。

七、 索引設計遺珠總結

1. 觀測先行：processlist + EXPLAIN 定位全表掃描 SQL。
2. 分析數據分佈：COUNT(DISTINCT col) 判斷基數，低基數列靠前組合高基數列。
3. 最左前綴原則：(corp_id, vehicle_id) 支持 WHERE corp_id=?、WHERE corp_id=? AND vehicle_id=?，不支持單獨 vehicle_id。
4. 覆蓋索引潛力：若查詢僅需索引列，可避免回表，進一步提速。
5. 大表建索引：使用 pt-online-schema-change 或 gh-ost 在線變更，避免鎖表阻塞業務。

一條精準的複合索引，抵得上十台服務器擴容。索引優化，是性價比最高的 MySQL 調優手段。