您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 查詢優化之索引應用實戰：創建、驗證與雙刃劍效應平衡

編輯：MySQL 資料庫相關知識

糟糕的 SQL 會拖垮整個應用。MySQL 索引是加速查詢的核心手段，但「索引越多越好」是危險誤區。本文演示索引創建驗證、EXPLAIN 分析入門、以及「寫入放大 vs 讀取加速」的平衡藝術。

索引創建與驗證

-- 在 users 表 username 列建立普通索引
CREATE INDEX idx_username ON users(username);

-- 驗證索引是否生效
SHOW INDEX FROM users;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          1 | idx_username |            1 | username    | A         |        1000 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

索引的雙刃效應量化

操作	無索引	有索引	影響機制
SELECT (WHERE/JOIN/ORDER BY)	全表掃描 O(N)	索引查找 O(log N)	B+Tree 遍歷替換全掃
INSERT	追寫數據頁	追寫數據頁 + 更新所有索引樹	寫入放大：索引數 × 寫入開銷
UPDATE (索引列)	原地修改	舊索引刪除 + 新索引插入	雙倍寫入 + 頁分裂風險
DELETE	標記刪除	標記刪除 + 所有索引標記刪除	索引碎片累積
空間佔用	數據頁	數據頁 + 索引頁 (通常 20%-200% 數據大小)	佔用磁盤、緩存池

索引設計黃金原則

只為高頻查詢列建索引——低頻/唯一值極少（如性別）列不建。
聯合索引遵循最左前綴——INDEX(a,b,c) 支援 a、a,b、a,b,c，不支援 b、c、b,c。
覆蓋索引避免回表——SELECT a,b FROM t WHERE c=1 加 INDEX(c,a,b)，僅掃描索引樹。
單表索引數 ≤ 5——超過 5 個需嚴格評估寫入負載（MySQL 硬限制 64，實際建議 ≤5）。
定期 ANALYZE TABLE 更新統計信息——優化器依賴 Cardinality 選擇索引。
監控 Handler_read_rnd_next 暴增——暗示全表掃描，需補索引或改 SQL。

實戰檢查清單

-- 1. 查看表索引
SHOW INDEX FROM table_name;

-- 2. 分析慢查詢是否用到索引
EXPLAIN SELECT ... WHERE ...;
-- 關鍵列：type (ALL=全掃, ref/range=用到索引), key (實際用到的索引), rows (預估掃描行數)

-- 3. 強制/忽略索引（調試用）
SELECT * FROM t USE INDEX (idx_a) WHERE a=1;
SELECT * FROM t IGNORE INDEX (idx_b) WHERE b=1;

-- 4. 刪除無用索引
DROP INDEX idx_unused ON table_name;

索引是手術刀，而非機關槍。精準切入查詢熱點，克制建索引衝動，才是優化高手的風範。