MySQL 索引操作命令大全：建立、重建、查詢、刪除與最佳實踐

前言

索引是查詢效能的命脈。MySQL 提供 CREATE INDEX、ALTER TABLE ... ADD INDEX、DROP INDEX、SHOW INDEX 等命令管理索引全生命週期。本文系統整理四大核心操作語法、參數詳解、聯合索引設計原則、覆蓋索引驗證與常見誤區。

一、建立索引

1. ALTER TABLE（最常用、支援多索引一次建立）

-- 普通索引
ALTER TABLE `table_name` ADD INDEX `idx_name` (col1, col2);

-- 唯一索引
ALTER TABLE `table_name` ADD UNIQUE `uk_name` (col1, col2);

-- 主鍵索引
ALTER TABLE `table_name` ADD PRIMARY KEY `pk_name` (col1);

-- 範例：文章表加索引
ALTER TABLE `article` ADD INDEX `idx_id` (`id`);
ALTER TABLE `article` ADD INDEX (`id`, `order_id`);

2. CREATE INDEX（專用、語意清晰、不支援主鍵）

CREATE INDEX `idx_name` ON `table_name` (col_list);
CREATE UNIQUE INDEX `uk_name` ON `table_name` (col_list);
-- 無法建立 PRIMARY KEY

3. 建表時同步建立

CREATE TABLE t_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status TINYINT,
  INDEX idx_user_status (user_id, status),
  UNIQUE uk_order_no (order_no)
) ENGINE=InnoDB;

二、查詢索引

SHOW INDEX FROM `table_name`;
-- 或
SHOW KEYS FROM `table_name`;

輸出欄位解讀

三、重建索引

索引碎片、統計資訊過舊會導致優化器誤判。重建方式：

-- 1. REPAIR TABLE (僅 MyISAM，InnoDB 無效)
REPAIR TABLE tbl_name QUICK;

-- 2. ALTER TABLE ... DROP INDEX + ADD INDEX (通用，重建表)
ALTER TABLE tbl_name DROP INDEX idx_name, ADD INDEX idx_name (col_list);

-- 3. OPTIMIZE TABLE (重建表、整理碎片、更新統計)
OPTIMIZE TABLE tbl_name;

-- 4. ANALYZE TABLE (僅更新統計資訊，不重建)
ANALYZE TABLE tbl_name;

生產大表重建索引請用 pt-online-schema-change 或 gh-ost，避免鎖表。

四、刪除索引

-- ALTER TABLE 方式（推薦、可批次）
ALTER TABLE `table_name` DROP INDEX `idx_name`;
ALTER TABLE `table_name` DROP UNIQUE `uk_name`;
ALTER TABLE `table_name` DROP PRIMARY KEY;  -- 主鍵無需指定名

-- DROP INDEX 方式
DROP INDEX `idx_name` ON `table_name`;

刪除欄位對索引的影響

• 刪除欄位 → 該欄位從所有包含它的索引中移除。
• 組合索引所有欄位被刪 → 整個索引自動刪除。
• 刪除主鍵欄位 → 表無主鍵，InnoDB 會隱式建立 6 字節 RowID 作為叢集索引（效能差）。

五、索引設計最佳實踐

1. 最左前綴原則：聯合索引 (a,b,c) 支援 a、a,b、a,b,c。
2. 高選擇性前置：基數大的欄位放前面（性別/狀態等低基數欄位不適合做索引前綴）。
3. 覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表。
4. 避免過度索引：單表 ≤ 5 個索引，寫入效能與索引數成反比。
5. 外鍵需索引：子表外鍵欄位必須有索引（InnoDB 自動建立）。
6. 定期 ANALYZE：大表每日/每週 ANALYZE TABLE 更新 Cardinality。

總結

索引管理核心四招：**ALTER TABLE ADD INDEX 建立、SHOW INDEX 查詢、OPTIMIZE/ANALYZE 重建統計、ALTER TABLE DROP INDEX 刪除**。聯合索引遵循最左前綴，覆蓋索引消除回表，大表變更用線上工具。將索引審核納入上線流程（EXPLAIN 必過），是穩定查詢效能的關鍵。