MySQL 索引完整指南：查看、創建與刪除索引的實戰方法

為什麼需要索引？

在資料庫操作中，索引是提升查詢效率的關鍵機制。當資料量龐大、涉及多表關聯查詢時，合理使用索引往往能將查詢速度提升成千上萬倍。索引不僅提供有序查找，更利用各種快速定位技術大幅降低資料庫的掃描成本。

直觀理解索引的威力

假設有三張未建立索引的表 t1、t2、t3，每表僅包含單列 c1、c2、c3，各有 1000 行資料（值為 1~1000）。執行以下查詢：

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

預期結果為 1000 行，每行包含三個相等的值。若無索引，資料庫必須檢查三表所有組合——可能的組合數高達 1000×1000×1000（十億次），查詢極其緩慢。

若為每張表建立索引，查詢流程將大幅優化：

1. 從 t1 讀取第一行數值；
2. 利用 t2 索引直接定位匹配行，同理處理 t3；
3. 掃描 t1 下一行並重複上述過程。

此時僅對 t1 執行全表掃描，而 t2、t3 透過索引直接取數，速度比無索引快百萬倍。索引不僅加速 WHERE 條件篩選，在多表連接時也能大幅提升行匹配效率。

建立索引的多種方式

MySQL 支援在 CREATE TABLE 時同步建立索引，也可事後透過 CREATE INDEX 或 ALTER TABLE 新增。

1. 使用 ALTER TABLE

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)

• table_name：目標表名
• column_list：索引欄位，多欄位以逗號分隔
• index_name：索引名稱（可選，預設取首個索引欄位名）

ALTER TABLE 允許單一陳述式修改多個表，可同時建立多個索引。

2. 使用 CREATE INDEX

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

語意與 ALTER TABLE 相同，但索引名稱不可省略，且無法建立主鍵索引。

索引類型解析

建立索引時可規定是否允許重複值：

• 主鍵索引（PRIMARY KEY）：不允許重複值，且表中僅能存在一個主鍵。本質上是名為 PRIMARY 的唯一索引。
• 唯一索引（UNIQUE）：不允許重複值，單欄保證該欄值唯一，多欄保證組合值唯一。
• 普通索引（INDEX）：允許重複值，純粹為加速查詢。

範例：為 students 表的 sid 欄位新增主鍵：

ALTER TABLE students ADD PRIMARY KEY (sid)

刪除索引

可用 ALTER TABLE 或 DROP INDEX 刪除：

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

• 前兩句等價，刪除指定索引
• 第三句專用於刪除主鍵（無需指定索引名）；若無主鍵但有唯一索引，會刪除第一個唯一索引

注意：刪除表欄位時，相關索引會同步受影響。組合索引中若刪除部分欄位，該欄位會從索引移除；若刪除所有組合欄位，整個索引將被刪除。

查看索引資訊

mysql> show index from tblname;
mysql> show keys from tblname;

輸出欄位說明：

實用工具推薦

本站提供 SQL 代碼在線格式化美化工具：https://tools.jb51.net/code/sqlcodeformat，方便開發者快速整理 SQL 語句。

掌握索引的建立、查看與刪除，能讓你在 MySQL 資料庫設計與優化中更游刃有餘。希望本文對你的資料庫程式設計有所幫助！