MySQL 索引創建與刪除 SQL 語句詳解

索引是查詢性能的關鍵。MySQL 支援 CREATE INDEX、ALTER TABLE、DROP INDEX 管理索引。本文系統整理創建、刪除、修改索引的語法、類型選擇及最佳實踐。

索引類型

創建索引

1. 建表時創建（推薦）

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  name VARCHAR(50),
  created_at DATETIME,
  UNIQUE INDEX uk_email (email),
  INDEX idx_name_created (name, created_at)
) ENGINE=InnoDB;

2. ALTER TABLE 添加（線上業務常用）

-- 普通索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 主鍵（表無主鍵時）
ALTER TABLE users ADD PRIMARY KEY (id);

-- 聯合索引（順序關鍵：最左前綴原則）
ALTER TABLE orders ADD INDEX idx_uid_status_created (user_id, status, created_at);

3. CREATE INDEX 語句

CREATE INDEX idx_email ON users (email);
CREATE UNIQUE INDEX uk_username ON users (username);
-- 不支持創建 PRIMARY KEY

刪除索引

-- 通用語法
DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;

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

修改索引（實為刪除重建）

ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name_status (name, status);

查看索引

SHOW INDEX FROM users;
-- 或
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='db' AND TABLE_NAME='users';

索引設計黃金法則

1. 最左前綴：聯合索引 (a,b,c) 支援 a、a,b、a,b,c 查詢
2. 高選擇性優先：區分度高的列放前面（COUNT(DISTINCT col)/COUNT(*) > 0.1）
3. 覆蓋索引：SELECT 列全在索引中，避免回表
4. 避免過度索引：單表建議 ≤ 5 個索引，寫入性能代價大
5. 外鍵自動建索引：InnoDB 外鍵列自動創建索引

線上 DDL 注意事項 (MySQL 5.6+)

• ALGORITHM=INPLACE：原地重建，不鎖表（大多數場景）
• ALGORITHM=COPY：複製表，阻塞寫入（舊版/特定操作）
• LOCK=NONE/SHARED/EXCLUSIVE/DEFAULT：控制並發
• 大表添加索引建議低峰期、分批、或用 pt-online-schema-change

實戰示例：電商訂單表

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  
  INDEX idx_user_created (user_id, created_at DESC),  -- 用戶訂單列表
  INDEX idx_status_created (status, created_at),     -- 狀態流轉查詢
  INDEX idx_updated (updated_at)                     -- 同步增量
) ENGINE=InnoDB;

總結

索引管理核心：建表時規劃、ALTER TABLE 線上變更、最左前綴設計、覆蓋索引避免回表、定期清理無用索引。使用 EXPLAIN 驗證 key 與 Extra，確保索引生效。