索引操作四大核心場景

索引是查詢效能的關鍵，掌握完整的索引生命週期管理技能是 DBA 與開發者的必修課。本文系統整理 MySQL 索引的創建、重建、查詢、刪除全套命令與最佳實踐。

一、創建索引

1. 隨表創建（推薦）

CREATE TABLE `article` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200),
  `category_id` INT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_title` (`title`),
  KEY `idx_category` (`category_id`)
) ENGINE=InnoDB;

2. 事後添加（ALERT TABLE / CREATE INDEX）

索引類型	ALTER TABLE 語法	CREATE INDEX 語法
普通索引	ALTER TABLE tbl ADD INDEX idx_name (col1, col2);	CREATE INDEX idx_name ON tbl (col1, col2);
唯一索引	ALTER TABLE tbl ADD UNIQUE uk_name (col1);	CREATE UNIQUE INDEX uk_name ON tbl (col1);
主鍵索引	ALTER TABLE tbl ADD PRIMARY KEY (col1);	不支援

3. 實戰範例

-- 單列索引
ALTER TABLE `article` ADD INDEX `idx_author` (`author_id`);

-- 聯合索引（遵循最左前綴原則）
ALTER TABLE `article` ADD INDEX `idx_cat_status` (`category_id`, `status`);

二、重建索引

索引碎片化會導致查詢效能下降，定期重建可恢復 B+ 樹飽滿度。

-- 快速重建（僅 MyISAM 有效，InnoDB 建議用 OPTIMIZE TABLE 或 ALTER TABLE）
REPAIR TABLE tbl_name QUICK;

-- InnoDB 通用重建方式（線上 DDL，5.6+ 支援）
OPTIMIZE TABLE tbl_name;
-- 或
ALTER TABLE tbl_name ENGINE=InnoDB;

三、查詢索引

SHOW INDEX 詳細輸出解讀

SHOW INDEX FROM `article`;
-- 等同於
SHOW KEYS FROM `article`;

列名	含義	關鍵關注點
Table	表名	–
Non_unique	0=唯一，1=非唯一	判斷唯一性約束
Key_name	索引名稱	PRIMARY 為主鍵
Seq_in_index	索引內列順序	聯合索引列序
Column_name	列名	–
Collation	排序方式	A=升序，NULL=無序
Cardinality	基數（唯一值估計）	越大選擇性越高
Sub_part	前綴長度	NULL=全列索引
Index_type	索引類型	BTREE, FULLTEXT, HASH

四、刪除索引

-- 刪除普通/唯一索引
ALTER TABLE tbl DROP INDEX idx_name;
DROP INDEX idx_name ON tbl;

-- 刪除主鍵（無需指定名稱）
ALTER TABLE tbl DROP PRIMARY KEY;

⚠️ 級聯影響提醒

• 刪除列時，該列所屬索引自動移除；組合索引中若列全被刪，整個索引消失。
• 外鍵引用的索引不可直接刪除，需先刪外鍵。

最佳實踐清單

1. 命名規範：`idx_` 普通、`uk_` 唯一、`pk_` 主鍵，便於識別。
2. 聯合索引列序：高選擇性、等值查詢列優先，範圍列置後。
3. 定期檢查：ANALYZE TABLE 更新 Cardinality，配合 pt-index-usage 發現未用索引。
4. 線上變更：生產環境優先使用 pt-online-schema-change 或 5.6+ 原生 Online DDL。