MySQL 索引全解析：類型、創建、優化與最佳實踐

索引的本質與價值

索引是數據庫性能的核心。有索引如法拉利，無索引如三輪車。對於百萬級數據，無索引全表掃描可能耗時秒級甚至分鐘級；有索引則毫秒級定位。

索引類型詳解

1. 普通索引 (Normal Index)

最基本索引，無任何約束，允許 NULL 值。

-- 創建
CREATE INDEX idx_title ON article(title(50));
ALTER TABLE article ADD INDEX idx_title (title(50));

-- 刪除
DROP INDEX idx_title ON article;

2. 唯一索引 (Unique Index)

索引列值唯一，允許 NULL (多個 NULL 不衝突)。

CREATE UNIQUE INDEX uk_email ON users(email);
ALTER TABLE users ADD UNIQUE uk_email (email);

3. 主鍵索引 (Primary Key)

特殊唯一索引，不允許 NULL，一表僅一個，聚簇索引。

CREATE TABLE t (id INT PRIMARY KEY, ...);
ALTER TABLE t ADD PRIMARY KEY (id);

4. 全文索引 (Fulltext Index)

MyISAM 專用 (5.6+ InnoDB 支持)，針對長文本搜索。

CREATE FULLTEXT INDEX ft_content ON article(content);
-- 查詢
SELECT * FROM article WHERE MATCH(content) AGAINST('關鍵詞');

5. 組合索引 / 聯合索引 (Composite Index)

多列組合，遵循最左前綴原則。

ALTER TABLE article ADD INDEX idx_title_time (title(50), create_time);
-- 等效於創建了：(title, create_time) 和 (title)
-- 不會創建：(create_time)

6. 空間索引 (Spatial Index)

針對幾何數據類型 (POINT, POLYGON 等)。

索引架構：B+ 樹

• 聚簇索引：數據與索引存儲在一起，InnoDB 主鍵為聚簇索引，葉子節點存完整行數據
• 非聚簇索引：葉子節點存主鍵值，查詢需「回表」查聚簇索引
• 覆蓋索引：查詢列全在索引中，無需回表，性能最高

索引創建語法總結

索引優化核心原則

1. 何時使用聚簇 vs 非聚簇

2. 索引不包含 NULL 值

單列索引不存 NULL；組合索引任一列為 NULL 則該行不入索引。建議列定義 NOT NULL DEFAULT ''。

3. 使用短索引 (前綴索引)

-- CHAR(255) 列，前 20 字符區分度高
ALTER TABLE t ADD INDEX idx_name(name(20));
-- 節省空間、提升 I/O、加快查詢

4. 索引列排序

MySQL 單查詢只用一個索引。WHERE 用了索引，ORDER BY 列通常不再用索引。避免多列排序，或創建對應組合索引。

5. LIKE 查詢優化

-- 使用索引
LIKE 'prefix%'
-- 不使用索引 (全表掃描)
LIKE '%suffix'
LIKE '%middle%'

6. 避免列運算導致索引失效

-- ❌ 失效：列參與運算
WHERE YEAR(create_time) = 2023

-- ✅ 生效：值運算
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'

索引使用條件總結

MySQL 僅對以下操作符使用索引：

<, <=, =, >=, >, BETWEEN, IN, LIKE 'prefix%'

實戰優化案例

-- 原慢查詢 (200萬行，無索引 12秒)
SELECT * FROM orders WHERE user_id=1001 AND status=1 ORDER BY create_time DESC LIMIT 10;

-- 優化：創建組合索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

-- 結果：毫秒級返回
-- EXPLAIN 顯示 type=ref, key=idx_user_status_time, rows=10

總結

索引是把雙刃劍：加速查詢、消耗寫入、佔用空間。遵循核心原則：業務驅動建索引、最左前綴組合、避免列運算、覆蓋索引優先、定期分析慢查詢。單表索引建議不超過 5 個，大表謹慎添加。