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

MySQL 索引操作全攻略：類型、建立、刪除、修改、查看與效能優化實戰

編輯：MySQL 綜合教學

前言

索引是資料庫效能的核心。MySQL 支援多種索引類型，合理設計與維護索引能讓查詢速度提升數量級。本文系統梳理六大索引類型、建立語法、前綴索引、刪除/修改/查看操作，並給出實戰優化建議。

一、MySQL 索引類型詳解

類型	關鍵字	特點	適用場景
普通索引	`KEY` / `INDEX`	僅加速查詢，允許重複值與 NULL	WHERE、ORDER BY、JOIN 條件欄位
唯一索引	`UNIQUE`	值必須唯一（允許單個 NULL），簡化管理、提高效率、自動防重	業務鍵：身分證、郵箱、手機號
主鍵索引	`PRIMARY KEY`	特殊唯一索引，不允許 NULL，每表僅一個，InnoDB 叢集索引	表主鍵，必建
外鍵索引	`FOREIGN KEY`	自動建立內部索引輔助約束檢查	關聯完整性要求高的場景
複合索引	`INDEX(col_a, col_b)`	多欄組合，遵循「最左前綴原則」：可用於 col_a、 (col_a, col_b)，不可用於 col_b、 (col_b, col_c)	多欄組合查詢、排序、分組
全文索引	`FULLTEXT`	針對長文本分詞建立倒排索引，支援自然語言/布爾模式搜尋，MyISAM/InnoDB 5.6+ 支援	文章搜尋、日誌分析、內容檢索

索引長度策略

CHAR/VARCHAR：可指定前綴長度（如 username(10)），節省空間、提升插入速度，通常 10~15 字元足夠區分度
BLOB/TEXT：必須指定前綴長度，最大 255 字元（MyISAM/InnoDB 可至 1000 位元組）

二、建立索引語法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON table_name (index_col_name, ...)

-- 等價 ALTER TABLE 寫法
ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name, ...) [USING index_type]

參數說明

index_type：BTREE（預設，MyISAM/InnoDB 僅支援）、HASH（MEMORY/HEAP 表可選）
index_col_name：欄位名，多欄用逗號分隔建立複合索引；col_name(length) 指定前綴長度

實戰範例

-- 普通索引
CREATE INDEX idx_user_username ON user (username(6));

-- 唯一索引
CREATE UNIQUE INDEX uk_user_email ON user (email);

-- 複合索引（遵循最左前綴）
CREATE INDEX idx_order_user_status ON orders (user_id, status, created_at);

-- 全文索引（需 MyISAM 或 InnoDB 5.6+）
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);

三、刪除索引

ALTER TABLE table_name DROP INDEX index_name;

-- 範例
ALTER TABLE user DROP INDEX idx_user_username;

四、修改索引

MySQL 無直接修改指令，標準做法：先刪除舊索引，再建立同名新索引。

ALTER TABLE user DROP INDEX idx_user_username;
CREATE INDEX idx_user_username ON user (username(8));

五、查看索引

SHOW INDEX FROM table_name [FROM db_name];
-- 或
SHOW INDEX FROM [db_name.]table_name

輸出關鍵欄位

欄位	含義
Table	表名
Non_unique	0=唯一，1=非唯一
Key_name	索引名
Seq_in_index	索引中欄位順序（1 起）
Column_name	欄位名
Collation	排序方式（A=升序，NULL=不排序）
Cardinality	基數（不重複值估計），越大選擇性越高
Sub_part	前綴長度，NULL 表全欄位
Index_type	BTREE / FULLTEXT / HASH 等
Comment	備註

六、效能優化實戰建議

最左前綴原則：複合索引順序 = WHERE/JOIN/ORDER BY 欄位順序
覆蓋索引：查詢欄位全包含在索引中，避免回表，EXPLAIN 顯示 Using index
避免索引失效：
- 不在索引欄上做運算/函數/隱式轉型
- LIKE '%abc' 導致全表掃描
- OR 條件未全命中索引
- 範圍查詢後的欄位無法用到複合索引
定期分析：ANALYZE TABLE 更新基數統計，OPTIMIZE TABLE 整理碎片
監控未用索引：sys.schema_unused_indexes（MySQL 5.7+）識別可刪除索引，減少寫入開銷
全文索引中文：MySQL 內建不支援中文分詞，需接入 Sphinx/Coreseek/Elasticsearch