MySQL 添加索引完整語法指南：主鍵、唯一、普通、全文、多列索引

快速語法速查

詳細實戰演示

1. 基礎添加索引

mysql> ALTER TABLE test ADD INDEX (t_name);
Query OK, 0 rows affected (0.17 sec)

mysql> DESCRIBE test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| t_id       | int(11)     | YES  |     | NULL    |       |
| t_name     | varchar(50) | NO   | MUL | NULL    |       |
| t_password | char(32)    | YES  |     | NULL    |       |
| t_birth    | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Key 列顯示 MUL 表示：非唯一索引前導列、或唯一索引組成部分但允許 NULL。

2. 創建表時直接定義索引

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    INDEX idx_username (username),
    UNIQUE uk_email (email),
    FULLTEXT ft_bio (bio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 刪除索引

ALTER TABLE test DROP INDEX idx_name;
-- 或
DROP INDEX idx_name ON test;

4. 查看索引

SHOW INDEX FROM test;
SHOW CREATE TABLE test;  -- 含索引定義

索引設計黃金法則

1. 最左前綴原則：組合索引 (a,b,c) 支援 a / (a,b) / (a,b,c)，不支援 b / c / (b,c)
2. 區分度優先：高區分度列放前面
3. 覆蓋索引：SELECT 列全部在索引中，避免回表
4. 前綴索引：長字符串列索引前 N 字符 INDEX(name(10))
5. 避免過度索引：單表建議 ≤ 5 個索引，寫入密集表更少

InnoDB 專屬提示

• 主鍵即聚簇索引，必須有主鍵 (建議自增整數)
• 二級索引葉子節點存主鍵值，主鍵越小二級索引越緊湊
• 外鍵列自動創建索引 (若無显式索引)