您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 索引架構與管理全指南：分類、創建、刪除與實戰優化原則

編輯：MySQL 綜合教學

MySQL 索引架構與管理全指南：分類、創建、刪除與實戰優化原則

索引是查詢效能的「加速器」，也是寫入效能的「代價」。本文系統梳理 MySQL 索引的六大分類、三種創建途徑、刪除語法、優缺點權衡，並給出基於業務查詢模式的索引設計鐵律與避坑清單，助你構建「精準、克制、可演進」的索引體系。

一、索引引入：B+ 樹與查找加速

MySQL 索引底層採用 B+ 樹，葉子節點存「鍵值 + 行指針」。無索引時全表掃描（O(N)），有索引時樹高查找（O(log N)）。百萬級表，索引查找通常只需 3~4 次 I/O，效能差距達數量級。

二、索引優缺點辯證

優點	缺點
極大提升查詢速度（WHERE/JOIN/ORDER BY/GROUP BY）	佔用額外磁碟空間（索引檔可超過數據檔）
支援唯一性約束（PRIMARY KEY/UNIQUE）	寫入開銷增加：INSERT/UPDATE/DELETE 需同步維護 B+ 樹
避免全表掃描鎖升級，減少併發阻塞	優化器統計失誤可能導致錯誤選擇索引

三、六大索引分類全景

類型	關鍵字	特性	引擎支援	典型場景
普通索引	INDEX / KEY	無唯一限制，允許重複值、NULL	全引擎	高頻查詢列、外鍵關聯列
唯一索引	UNIQUE INDEX	值唯一，允許多個 NULL	全引擎	業務鍵：手機、郵箱、身分證
主鍵	PRIMARY KEY	唯一 + NOT NULL，聚簇索引，唯一	全引擎	表標識列，InnoDB 強制有
全文索引	FULLTEXT	自然語言/布爾模式搜尋	MyISAM, InnoDB 5.6+	長文本搜尋、日誌分析
空間索引	SPATIAL	地理空間類型專用	MyISAM, InnoDB 5.7+	GIS 應用、位置服務
複合索引	INDEX (a,b,c)	多列組合，遵循最左前綴原則	全引擎	多條件組合查詢、覆蓋索引

單列 vs 多列：單列索引針對單欄；複合索引（多列索引）按定義順序構建 B+ 樹，滿足最左前綴可複用。

四、三種創建途徑：語法與選型

4.1 建表時同步創建（推薦：原子性、版本可控）

CREATE TABLE t_user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50),
  email VARCHAR(100),
  -- 單列普通索引
  INDEX idx_username (username),
  -- 單列唯一索引
  UNIQUE INDEX uk_email (email),
  -- 複合索引
  INDEX idx_name_email (username, email)
);

4.2 獨立 CREATE INDEX（線上加索引，5.6+ Online DDL）

CREATE INDEX idx_username ON t_user (username);
CREATE UNIQUE INDEX uk_email ON t_user (email);
CREATE INDEX idx_name_email ON t_user (username, email);

4.3 ALTER TABLE ADD INDEX（等價於 CREATE INDEX，語法兼容）

ALTER TABLE t_user ADD INDEX idx_username (username);
ALTER TABLE t_user ADD UNIQUE INDEX uk_email (email);
ALTER TABLE t_user ADD INDEX idx_name_email (username, email);

線上加索引建議**：MySQL 5.6+ 支援 Online DDL（僅鎖定元數據短暫時間），大表建議低峰期執行，監控 SHOW PROCESSLIST 狀態 copy to tmp table 進度。

五、刪除索引

DROP INDEX index_name ON table_name;
-- 範例
DROP INDEX idx_username ON t_user;
DROP INDEX idx_name_email ON t_user;

注意：主鍵用 ALTER TABLE t DROP PRIMARY KEY;；外鍵索引需先刪外鍵約束。

六、索引設計鐵律與避坑清單

最左前綴原則：複合索引 (a,b,c) 等價於 (a)、(a,b)、(a,b,c) 三個索引；查詢必須帶最左列 a
高選擇性優先：SELECTIVITY = COUNT(DISTINCT col)/COUNT(*)，越接近 1 越適合建索引
覆蓋索引：查詢列全包含於索引中，避免回表，Extra: Using index
前綴索引：長字串建 INDEX idx_name(name(10))，平衡空間與選擇性
避免函數/運算破壞索引：WHERE YEAR(dt)=2023 → WHERE dt >= '2023-01-01' AND dt < '2024-01-01'
定期審計**：pt-index-usage 找未用索引 → DROP；pt-duplicate-key-checker 找冗餘索引 → 合併

七、實戰案例：從慢查詢到精準索引

-- 原慢查詢
SELECT * FROM orders WHERE user_id = 1001 AND status = 1 AND created_at > '2023-10-01';
-- EXPLAIN 顯示 type=ALL, rows=500萬
-- 建立複合索引
ALTER TABLE orders ADD INDEX idx_uid_status_time (user_id, status, created_at);
-- 再次 EXPLAIN：type=ref, rows=12, Extra=Using index condition

索引設計是「業務理解 + 原理掌握 + 持續迭代」的動態平衡。建立「慢查詢收集 → EXPLAIN 分析 → 索引調整 → 效果驗證」閉環，方能在數據增長與業務變更中保持查詢效能穩定。