MySQL 索引管理實戰：CREATE INDEX / DROP INDEX 語法詳解與多列索引驗證

DROP INDEX 語法

MySQL 3.22+ 引入，內部同為 ALTER TABLE。

DROP INDEX index_name ON tbl_name

實戰驗證：創建多列索引並檢查

-- 假設表 student(id, english, chinese, history)
CREATE INDEX idx_scores ON student (english, chinese, history);

-- 檢查索引結構
SHOW INDEX FROM student;
+---------+------------+----------+--------------+-------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+
| student |          1 | idx_scores |            1 | english     |
| student |          1 | idx_scores |            2 | chinese     |
| student |          1 | idx_scores |            3 | history     |
+---------+------------+----------+--------------+-------------+
-- Seq_in_index 確認列順序：english → chinese → history

-- 刪除索引
DROP INDEX idx_scores ON student;

等效 ALTER TABLE 寫法（通用、功能更全）

-- 添加普通索引
ALTER TABLE student ADD INDEX idx_scores (english, chinese, history);
-- 添加唯一索引
ALTER TABLE student ADD UNIQUE INDEX uk_scores (english, chinese, history);
-- 添加主鍵（CREATE INDEX 不支援）
ALTER TABLE student ADD PRIMARY KEY (id);
-- 刪除索引
ALTER TABLE student DROP INDEX idx_scores;

最佳實踐建議

1. 統一用 ALTER TABLE：功能完整（含主鍵、外鍵、列屬性變更）、語義清晰、工具支援更好。
2. 索引命名規範：idx_表_列 / uk_表_列，避免自動生成的不可讀名稱。
3. 大表加索引用 pt-online-schema-change：避免元數據鎖阻塞寫入。
4. 定期清理未使用索引：sys.schema_unused_indexes (5.7+ sys schema) 或 pt-index-usage 分析慢查詢日誌。

總結

CREATE INDEX/DROP INDEX 是 ALTER TABLE 的語法糖，**無法創建主鍵**，生產建議直接用 ALTER TABLE。掌握 SHOW INDEX 驗證 Seq_in_index 列順序，是聯合索引生效的關鍵檢查點。