前言：索引是查詢的「導航儀」
索引操作看似基礎，實則暗藏「前綴長度單位陷阱」「引擎支援差異」「空間/全文索引限制」「ASC/DESC 偽語法」等細節。本文匯總 MySQL 索引全生命周期命令，結合引擎特性與實戰避坑指南，構建 DBA 索引管理的標準化操作手冊。
一、創建索引：CREATE INDEX 完整語法
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name, ...)
index_col_name:
col_name [(length)] [ASC | DESC]
1. 索引類型選擇
| 類型 | 關鍵字 | 適用列 | 引擎支援 | 說明 |
|---|
| 普通 | (無) | 所有類型 | 所有 | B+ 樹，預設 |
| 唯一 | UNIQUE | 所有類型 | 所有 | 值唯一，允許多個 NULL |
| 主鍵 | PRIMARY KEY | 非 NULL 列 | 所有 | 隱含 UNIQUE + NOT NULL |
| 全文 | FULLTEXT | CHAR/VARCHAR/TEXT | MyISAM, InnoDB(5.6+) | 自然語言/布爾模式搜索 |
| 空間 | SPATIAL | 幾何類型 | MyISAM, InnoDB(5.7+) | R 樹，GIS 查詢 |
2. 前綴索引：大字段索引的「瘦身術」
-- 僅索引 name 前 10 個字符
CREATE INDEX idx_name_prefix ON customer (name(10));
- 適用場景：
CHAR/VARCHAR 長文本、BLOB/TEXT（必須指定前綴長度）。
- 長度單位陷阱：語法中
length 為**字符數**，但底層限制為**字節數**：
- MyISAM/InnoDB：最大 **1000 字節**（InnoDB 可配置至 3072）。
- 多字節字符集（utf8mb4=4字節/字）：
name(255) 可能超限，需按字節換算。
- 選擇性評估：
SELECT COUNT(DISTINCT LEFT(col, n)) / COUNT(*) FROM t → 接近 1 時前綴長度足夠。
- 收益：索引體積大幅縮小，寫入更快，緩存命中率更高。
3. 存儲引擎與索引類型對應表
| 引擎 | 支援索引類型 | 備註 |
|---|
| MyISAM | BTREE | 全文/空間僅 MyISAM 早期支援 |
| InnoDB | BTREE | 聚簇索引即主鍵 B+ 樹 |
| MEMORY/HEAP | HASH, BTREE | HASH 適合等值查詢，範圍查詢需 BTREE |
-- 指定 MEMORY 表用 BTREE（預設 HASH）
CREATE INDEX id_idx USING BTREE ON lookup (id);
USING type_name 為標準語法；TYPE type_name 為舊同義詞（不推薦）。- 指定類型不合法時，引擎自動回退至支援的類型（不報錯）。
4. ASC | DESC：被忽略的偽語法
- 目前 MySQL **解析但忽略**，所有索引均為**升序存儲**。
- 未來版本可能支援降序索引（利於
ORDER BY col DESC 避免反向掃描）。
二、ALTER TABLE 方式創建索引（生產常用）
-- 普通索引
ALTER TABLE tbl_name ADD INDEX idx_name (col_list);
-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE uk_name (col_list);
-- 主鍵
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
-- 全文/空間（引擎支援前提下）
ALTER TABLE tbl_name ADD FULLTEXT INDEX ft_idx (col);
ALTER TABLE tbl_name ADD SPATIAL INDEX sp_idx (geom_col);
ALTER TABLE 支持 ALGORITHM=INPLACE（InnoDB 線上 DDL，無鎖/輕量鎖），CREATE INDEX 內部同樣走 Online DDL 路徑。- 生產環境大表**強制指定**
ALGORITHM=INPLACE, LOCK=NONE 避免鎖表。
三、查詢索引：SHOW INDEX 深度解讀
SHOW INDEX FROM tbl_name;
-- 或
SHOW KEYS FROM tbl_name;
| 列名 | 含義 | 關鍵判讀 |
|---|
Table | 表名 | - |
Non_unique | 0=唯一, 1=非唯一 | 主鍵為 0 |
Key_name | 索引名 | 主鍵固定為 PRIMARY |
Seq_in_index | 索引內列序號 | 聯合索引順序關鍵 |
Column_name | 列名 | - |
Collation | A=升序, NULL=不排序 | HASH 索引顯示 NULL |
Cardinality | 基數（估算唯一值數） | 越接近行數選擇性越高 |
Sub_part | 前綴長度（字節） | NULL=全列索引 |
Packed | 壓縮前綴 | MyISAM 壓縮索引 |
Null | YES/NO | 是否允許 NULL |
Index_type | BTREE/HASH/FULLTEXT/SPATIAL | - |
Comment | 備註 | - |
Index_comment | 創建時註釋 | - |
四、刪除索引：三種等效方式
-- 方式 1：DROP INDEX（需指定索引名）
DROP INDEX idx_name ON tbl_name;
-- 方式 2：ALTER TABLE DROP INDEX（同方式 1）
ALTER TABLE tbl_name DROP INDEX idx_name;
-- 方式 3：刪除主鍵（無需索引名，表僅一個 PK）
ALTER TABLE tbl_name DROP PRIMARY KEY;
- 刪除主鍵若無顯式主鍵，MySQL 會刪除第一個非空唯一索引並提升為主鍵（InnoDB 必須有主鍵）。
- 大表刪除索引同樣走 Online DDL，建議指定
ALGORITHM=INPLACE。
五、特殊約束與避坑指南
| 限制 | 說明 | 版本/引擎條件 |
|---|
| NULL 列加索引 | 僅 MyISAM/InnoDB/BDB 支援 | MEMORY/HEAP 不支援 |
| BLOB/TEXT 加索引 | 必須指定前綴長度 | MyISAM/InnoDB/BDB 支援 |
| 全文索引 | 僅 CHAR/VARCHAR/TEXT | MyISAM 全版本；InnoDB 5.6+ |
| 空間索引 | 僅幾何類型，列必須 NOT NULL | MyISAM；InnoDB 5.7+ |
| 單表索引數上限 | 64 個（InnoDB） | 含主鍵 |
| 單索引列數上限 | 16 列 | - |
| 索引鍵長度上限 | 3072 字節 (InnoDB, innodb_large_prefix=ON) | 舊版 767 字節 |
六、實戰最佳實踐清單
- 命名規範：
idx__、uk__、fk__。
- 聯合索引最左前綴：
INDEX (a,b,c) 支援 a/a,b/a,b,c 查詢。
- 選擇性優先：高選擇性列前置；
WHERE a=1 AND b=2 中 a 選擇性高則 INDEX (a,b)。
- 覆蓋索引：
SELECT a,b FROM t WHERE c=1 → INDEX (c,a,b) 避免回表。
- 定期重建：
ANALYZE TABLE 更新統計信息；碎片率高時 OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB 重建。
- 監控未用索引：
sys.schema_unused_indexes (5.7+) 定期清理零用索引，減輕寫入開銷。
結語
索引操作非單純增刪改查，而是**選擇性評估、前綴長度換算、引擎特性適配、Online DDL 鎖控制、統計信息維護**的系統工程。掌握本文命令全集與避坑指南，配合 EXPLAIN、sys schema、performance_schema 監控體系，即可構建「索引全生命周期精益管理」閉環，讓每一個索引都為查詢性能「負責」。
