萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引優化實戰:從建立原則到執行計劃深度解析

索引是銀彈,也是雙刃劍

索引能將全表掃描 (O(N)) 降為對數級 (O(logN)),但每個索引都增加寫入開銷 (INSERT/UPDATE/DELETE 需維護 B+ 樹)、佔用磁碟空間、擴大優化器搜索空間。盲目加索引反而拖垮效能。

八大建立索引黃金法則

  1. 主鍵/外鍵必建索引:PK 自動聚簇索引,FK 需手動建立輔助索引以加速關聯檢查。
  2. WHERE/JOIN/ORDER BY/GROUP BY 高頻列優先:EXPLAIN 觀察 key_len/ref 確認命中。
  3. 高選擇性列優先:COUNT(DISTINCT col)/COUNT(*) > 0.1 才值得建索引;性別/狀態碼 (低基數) 建索引無意義。
  4. 小字段優先:INT(4B) < BIGINT(8B) < CHAR(32) < VARCHAR(255)。索引越窄,單頁容納條目越多,樹高越低。
  5. 前綴索引處理長字串:INDEX idx_email_prefix (email(19)),在選擇性足夠前提下節省空間。
  6. 最左前綴原則:複合索引 (a,b,c) 等價於 (a)、(a,b)、(a,b,c) 三個索引。查詢必須從最左列開始連續匹配。
  7. 避免冗餘索引:已有 (a,b) 再建 (a) 屬冗餘;pt-duplicate-key-checker 可自動掃描。
  8. 頻繁寫入表少建索引:寫多讀少表 (日誌、流水) 僅保核心索引,必要時異步寫入 ES/ClickHouse 供查詢。

索引類型與存儲引擎差異

類型InnoDBMyISAMMemory適用場景
B+Tree (預設)✅ 聚簇+輔助✅ 非聚簇✅範圍、排序、全值、前綴匹配
Hash❌ (自適應哈希)❌✅ 預設精確等值查找 (=/IN)
Fulltext✅ 5.6+✅❌全文檢索
Spatial (R-Tree)✅✅❌GIS 地理空間

執行計劃 (EXPLAIN) 核心欄位解讀

EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status=1 ORDER BY created_at DESC LIMIT 10;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_uid_st    | idx_uid_st| 5       | const |  12  |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
  • type:system/const > eq_ref > ref > range > index > ALL (全表掃描最差)。
  • key:實際使用的索引;NULL 表示無索引可用。
  • key_len:索引使用長度,推斷複合索引用了幾列。
  • Extra:Using index (覆蓋索引)、**Using filesort (需排序)**、**Using temporary (臨時表)** 為優化重點。

高階優化模式

覆蓋索引

查詢列完全包含於索引中,Extra: Using index,避免回表。
範例:INDEX idx_uid_status_created (user_id, status, created_at) 覆蓋 SELECT user_id, status, created_at FROM orders WHERE user_id=?。

利用索引排序避免 filesort

  • 索引列順序 = ORDER BY 列順序,且方向一致 (全 ASC 或全 DESC)。
  • WHERE 為常量條件時,索引可同時服務過濾與排序。
  • 跨表 JOIN 排序僅支援驅動表索引排序。

索引下推 (ICP, Index Condition Pushdown)

MySQL 5.6+:Storage Engine 層直接用索引中列過濾 WHERE 條件,減少回表次數。
EXPLAIN 出現 Using index condition 表示生效。

鎖與索引的關係 (InnoDB)

  • 行鎖加在索引項上,無索引 → 全表掃描 → 鎖全表 (間隙鎖/Next-Key Lock)。
  • SELECT ... FOR UPDATE 若 WHERE 不走索引,會鎖住掃描到的所有行 (含不符合條件)。
  • 優化:確保更新/刪除/鎖定查詢皆命中索引,縮小鎖範圍,提升併發。

索引維護工具箱

-- 查看索引
SHOW INDEX FROM tbl;
-- 分析統計資訊 (更新 Cardinality)
ANALYZE TABLE tbl;
-- 檢查碎片
SELECT TABLE_NAME, INDEX_NAME, STAT_VALUE 
FROM INFORMATION_SCHEMA.INNODB_INDEXES 
WHERE STAT_VALUE > 0;  -- 需配合 innodb_stats_persistent
-- 重建索引 (在線 DDL 5.6+)
ALTER TABLE tbl DROP INDEX idx_old, ADD INDEX idx_new (...);
-- 或優化表 (重建所有索引)
OPTIMIZE TABLE tbl;

避坑速查表

反模式後果修正
WHERE YEAR(dt) = 2023函數導致索引失效WHERE dt >= '2023-01-01' AND dt < '2024-01-01'
LIKE '%keyword%'前導通配符無法用索引全文索引 / ES / 反向索引技巧
col != 100 / NOT IN範圍查詢導致全掃col > 100 OR col < 100 / NOT EXISTS
隱式類型轉換 WHERE phone = 13800000000字符列轉數字,索引失效WHERE phone = '13800000000'
複合索引列順序錯誤最左前綴斷裂按區分度高→低、WHERE/JOIN 順序排列

索引設計是數據庫效能的基石,測試驗證 > 經驗拍腦袋。善用 EXPLAIN、SHOW INDEX、pt-index-usage,讓每個索引都物有所值。

copyright © 萬盛學電腦網 all rights reserved