MySQL 索引優化實戰：類型選擇、創建策略、EXPLAIN 分析與引擎配合

索引核心價值

索引是提升查詢性能的關鍵。無索引時 MySQL 全表掃描（Type=ALL），建立索引後可將掃描行數從數十萬降至 1，查詢耗時從秒級降至毫秒級。

五大索引類型與創建語法

1. 主鍵索引

CREATE TABLE user(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(40) NOT NULL,
    PRIMARY KEY (id)
);

特點：唯一、非空、聚簇索引（InnoDB），每表僅一個。

2. 普通索引

CREATE INDEX idx_name ON user(name(20));

特點：無唯一性限制，支持前綴索引（節省空間，如 name(20)）。

3. 唯一索引

CREATE UNIQUE INDEX idx_email ON user(email);

特點：值唯一，允許 NULL（多個 NULL 不衝突）。

4. 全文索引

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=MyISAM;

SELECT * FROM articles WHERE MATCH(title,body) AGAINST('database');

特點：僅 MyISAM 支持（5.6+ InnoDB 也支持），自然語言搜索，返回相關度評分。

5. 復合索引（最左前綴原則）

CREATE TABLE test(
    id INT NOT NULL PRIMARY KEY,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    INDEX name (last_name, first_name)
);

生效查詢：

• WHERE last_name='Widenius' ✓
• WHERE last_name='Widenius' AND first_name='Michael' ✓
• WHERE first_name='Michael' ✗（非最左列）

何時創建索引：EXPLAIN 實戰分析

-- 未建索引前
EXPLAIN SELECT name FROM fps WHERE name='李武';
-- type=ALL, rows=1041, Extra=Using where

-- 建立索引
CREATE INDEX idx_name ON fps(name);

-- 建索引後
EXPLAIN SELECT name FROM fps WHERE name='李武';
-- type=ref, rows=1, Extra=Using where; Using index

EXPLAIN 關鍵列解讀

存儲引擎與索引的協同

MyISAM

• .MYD 數據文件、.MYI 索引文件分離
• 支持數據/索引文件分盤提升 IO
• 表級鎖，讀多寫少場景優勢大
• myisamchk 修復、myisampack 壓縮

InnoDB

• 聚簇索引：主鍵即數據順序，二級索引葉子存主鍵值
• 行級鎖 + MVCC，高並發寫入優勢
• 共享表空間或獨立表空間（innodb_file_per_table=1）
• 崩潰恢復通過 redo/undo log，備份需 xtrabackup 或 mysqldump --single-transaction

引擎選擇指南

優化檢查清單

1. WHERE/JOIN/ORDER BY/GROUP BY 涉及的列建立索引
2. 復合索引遵循最左前綴，區分度高的列靠前
3. 避免索引列上使用函數/運算（WHERE id+1=5 失效）
4. 覆蓋索引：SELECT id, name FROM t WHERE email=? 含 INDEX(email, name)
5. 定期 ANALYZE TABLE 更新統計信息，OPTIMIZE TABLE 整理碎片
6. 監控 slow_query_log + EXPLAIN 持續優化