MySQL 建立與刪除索引完整指南

索引建立的三種方式

MySQL 提供多種語法建立索引，可在建表時一併定義，也可事後單獨添加。

1. 使用 ALTER TABLE 建立索引

最常用、功能最完整的方式，支援普通、唯一、主鍵三類索引：

ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);

• index_name 可省略，MySQL 會自動以第一個索引欄位命名。
• 單條語句可同時建立多個索引。

實例

MySQL> USE tpsc;
MySQL> ALTER TABLE tpsc ADD INDEX shili (tpmc);
Query OK, 2 rows affected (0.08 sec)

2. 使用 CREATE INDEX 建立索引

專門用於為既有表添加普通或唯一索引，不支援主鍵，且索引名稱不可省略：

CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);

3. 在 CREATE TABLE 時定義

建表語句中直接指定 INDEX、UNIQUE、PRIMARY KEY，效能最佳，避免後續鎖表重建。

索引刪除語法

可用 DROP INDEX 或 ALTER TABLE 兩種方式：

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

• 刪除主鍵無需指定名稱，因每表僅有一個主鍵。
• 若無主鍵但有唯一索引，DROP PRIMARY KEY 會刪除第一個唯一索引。

刪除示例

MySQL> DROP INDEX shili ON tpsc;
Query OK, 2 rows affected (0.08 sec)

欄位刪除對索引的影響

• 單欄索引：欄位刪除，索引自動移除。
• 複合索引：刪除部分欄位，該欄位從索引中移除；若刪除所有組成欄位，整個索引被刪除。

最佳實踐建議

1. 優先在 CREATE TABLE 時定義索引，減少線上 DDL 鎖表風險。
2. 大表添加索引建議使用 pt-online-schema-change 等線上工具。
3. 定期檢視 INFORMATION_SCHEMA.STATISTICS 清理冗餘、未使用的索引。