MySQL 索引操作命令大全：建立、重建、查詢、刪除索引一網打盡

前言

索引管理是 DBA 日常核心工作。本文彙總 MySQL 索引的建立、重建、查詢、刪除完整語法，並說明主鍵、唯一、普通、複合索引的建立差異。

一、建立索引

1. ALTER TABLE（最通用）

-- 普通索引
ALTER TABLE `tbl` ADD INDEX `idx_name` (`col1`, `col2`);
-- 唯一索引
ALTER TABLE `tbl` ADD UNIQUE INDEX `uk_name` (`col`);
-- 主鍵索引
ALTER TABLE `tbl` ADD PRIMARY KEY (`id`);

2. CREATE INDEX

CREATE INDEX `idx_name` ON `tbl` (`col1`, `col2`);
CREATE UNIQUE INDEX `uk_name` ON `tbl` (`col`);

注意：CREATE INDEX 不能建立主鍵。

3. 建表時同步建立

CREATE TABLE `tbl` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50),
  INDEX `idx_name` (`name`),
  UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB;

二、重建索引

長期運行導致索引碎片，影響效能。

-- 快速重建（僅 MyISAM/InnoDB 支援）
REPAIR TABLE `tbl` QUICK;

-- InnoDB 推薦：重建表（同時整理索引）
ALTER TABLE `tbl` ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE `tbl`;

三、查詢索引

SHOW INDEX FROM `tbl`\G
-- 重點欄位：
-- Key_name: 索引名
-- Column_name: 索引欄位
-- Seq_in_index: 複合索引順序
-- Cardinality: 基數（區分度參考）
-- Index_type: BTREE / FULLTEXT / HASH

四、刪除索引

1. DROP INDEX

DROP INDEX `idx_name` ON `tbl`;

2. ALTER TABLE DROP

ALTER TABLE `tbl` DROP INDEX `idx_name`;
ALTER TABLE `tbl` DROP PRIMARY KEY;  -- 僅一個主鍵，無需指定名稱
ALTER TABLE `tbl` DROP UNIQUE `uk_name`;

五、刪除欄位對索引的連帶影響

• 刪除單欄索引欄位 → 索引自動移除。
• 複合索引中刪除部分欄位 → 該欄位從索引移除，剩餘欄位保留。
• 刪除複合索引所有欄位 → 整個索引移除。

六、實務建議

1. 命名規範：idx_ 普通、uk_ 唯一、pk_ 主鍵。
2. 定期 ANALYZE TABLE 更新 Cardinality，讓優化器做正確決策。
3. 線上大表 DDL（加索引/改欄位）建議使用 pt-online-schema-change 或 MySQL 8.0+ INSTANT DDL 避免鎖表。