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

MySQL 索引建立、管理與優化實戰指南

編輯：MySQL 資料庫相關知識

索引是資料庫效能的核心杠桿。本文系統整理索引建立語法、類型選擇、ALTER TABLE 管理操作、SHOW INDEX 解讀與避坑原則，助您構建高效的索引策略。

一、索引類型與建立語法

類型	關鍵字	特性	適用場景
主鍵	PRIMARY KEY	唯一、非空、聚簇索引（InnoDB）	每張表必須、業務主鍵
唯一索引	UNIQUE	值唯一、允許多個 NULL	業務唯一鍵（Email、手機、身分證）
普通索引	INDEX / KEY	允許重複、允許 NULL	查詢/連接/排序/分組欄位
全文索引	FULLTEXT	支援自然語言/布林模式搜尋	文章、日誌、商品描述等長文本
空間索引	SPATIAL	GIS 幾何類型	地理位置、幾何運算
複合索引	(col1, col2, ...)	最左前綴原則、覆蓋索引潛力	多欄位組合查詢、排序、分組

建立語法三種方式

-- 1. 建表時指定
CREATE TABLE t (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50),
    INDEX idx_name (name),
    FULLTEXT ft_content (content)
);

-- 2. ALTER TABLE 新增
ALTER TABLE t ADD UNIQUE INDEX uk_phone (phone);
ALTER TABLE t ADD INDEX idx_created (created_at);

-- 3. CREATE INDEX（不支援主鍵）
CREATE UNIQUE INDEX uk_email ON t (email);
CREATE INDEX idx_name ON t (name DESC);  -- 降序索引（8.0+ 生效）

二、索引管理操作

-- 查看索引
SHOW INDEX FROM t \G
-- 關鍵欄位：Key_name, Seq_in_index, Column_name, Cardinality, Sub_part, Index_type, Comment

-- 刪除索引
ALTER TABLE t DROP INDEX uk_phone;
-- 或
DROP INDEX uk_phone ON t;

-- 修改主鍵（需先刪除舊主鍵）
ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (new_id);

三、複合索引設計鐵律：最左前綴原則

索引 (a, b, c) 可支援：

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?
ORDER BY a, b, c / GROUP BY a, b, c

不可支援： WHERE b = ?、WHERE a = ? AND c = ?（跳過 b）、WHERE a > ? AND b = ?（範圍查詢中斷前綴）。

四、覆蓋索引：避免回表的關鍵

若 SELECT a, b FROM t WHERE c = ?，且存在索引 (c, a, b)，則查詢僅掃描索引樹，不回主鍵聚簇索引取行，效能提升數倍。EXPLAIN 中 Extra: Using index 表示命中覆蓋索引。

五、索引失效常見原因

隱式類型轉換：WHERE varchar_col = 123（需 '123'）
對索引欄位做運算/函數：WHERE DATE(created_at) = '2024-01-01' → 改 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
LIKE '%keyword%' 以萬用字元開頭
OR 連接條件且未全覆蓋索引：WHERE a=1 OR b=2 → 改 UNION ALL
統計資訊過期：ANALYZE TABLE t 更新 Cardinality

六、索引維護最佳實踐

定期檢視： pt-index-usage 分析慢查詢日誌，找出未使用索引並 DROP。
碎片整理： OPTIMIZE TABLE t 或 ALTER TABLE t ENGINE=InnoDB 重建索引，回收空間、修復統計資訊。
監控指標： Handler_read_rnd_next（全表掃描）、Innodb_buffer_pool_reads（磁碟讀取）、Created_tmp_disk_tables（臨時表落盤）。
命名規範： pk_ 主鍵、uk_ 唯一、idx_ 普通、ft_ 全文、sp_ 空間，便於自動化腳本識別。