MySQL 索引類型詳解與查詢優化實戰指南

MySQL 索引類型詳解與查詢優化實戰指南

索引是資料庫提升查詢效能的核心機制。MySQL 支援多種索引類型，各有適用場景。本文系統整理索引分類、建立語法、優化原則及 EXPLAIN 實戰分析，助你構建高效能資料庫架構。

一、四大核心索引類型

1. 主鍵索引

唯一且不允許 NULL 值，每張表僅能有一個主鍵。

-- 建表時指定
CREATE TABLE pk_test (
    f1 INT NOT NULL,
    PRIMARY KEY (f1)
);

-- 既有表新增
ALTER TABLE customer MODIFY id INT NOT NULL,
ADD PRIMARY KEY (id);

2. 普通索引

允許重複值，適用於頻繁查詢、排序或分組的欄位。

-- 建表時指定
CREATE TABLE tablename (
    fieldname1 columntype,
    fieldname2 columntype,
    INDEX [indexname] (fieldname1[, fieldname2...])
);

-- 既有表新增
ALTER TABLE sales ADD INDEX idx_value (value);

3. 唯一索引

值必須唯一，但允許 NULL（多個 NULL 視為不同值）。

CREATE TABLE ui_test (f1 INT, f2 INT, UNIQUE (f1));
ALTER TABLE ui_test ADD UNIQUE (f2);

4. 全文索引

專為大量文本（CHAR、VARCHAR、TEXT）設計，支援自然語言與布林模式搜尋。

CREATE TABLE ft2 (
    f1 VARCHAR(255),
    FULLTEXT (f1)
) ENGINE=MyISAM;

-- 自然語言模式
SELECT * FROM ft2 WHERE MATCH(f1) AGAINST('master');

-- 布林模式：+ 代表必含，- 代表排除
SELECT * FROM ft2 
WHERE MATCH(f1) AGAINST('+master -pet' IN BOOLEAN MODE);

注意： MySQL 會忽略：(a) 出現於 50% 以上記錄的詞；(b) 長度 < 3 的詞；(c) 內建停用詞（如 the）。

二、特殊索引應用

前綴索引

對長字串欄位（VARCHAR、CHAR、BLOB、TEXT）僅索引前 N 個字元，節省空間並加快查詢。

ALTER TABLE customer ADD INDEX idx_surname_prefix (surname(10));

自動遞增欄位

配合索引使用，常作為主鍵。

CREATE TABLE tablename (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ...
);

-- 重置計數器
ALTER TABLE tablename AUTO_INCREMENT = 50;

-- 取得最新插入 ID
SELECT LAST_INSERT_ID();

多欄主鍵中的自動遞增： 當主鍵為 (rank, id) 時，id 會在各 rank 群組內獨立遞增，無法手動重置計數器。

三、索引刪除與修改

修改索引需先刪除再重建：

ALTER TABLE tablename DROP PRIMARY KEY;
ALTER TABLE tablename DROP INDEX indexname;
DROP INDEX indexname ON tablename;

四、高效使用索引的六大場景

1. WHERE 條件匹配： SELECT * FROM customer WHERE surname > 'c'
2. MIN/MAX 查詢： 直接讀取索引首尾值
3. 覆蓋索引： SELECT id FROM customer 僅讀取索引，無需回表
4. ORDER BY 排序： SELECT * FROM customer ORDER BY surname
5. 表連接加速： 連接欄位有索引時大幅提升 JOIN 效能
6. LIKE 前綴匹配： surname LIKE 'ser%' 可用索引；%ser% 則不可

五、索引選擇四大原則

1. 只為 WHERE、JOIN、ORDER BY 涉及的欄位建立索引
2. 區分度越高越好（主鍵 > 唯一索引 > 普通索引；枚舉類型區分度低，不宜建索引）
3. 優先使用短索引（如前綴索引）
4. 避免過度索引：每新增一個索引都會增加 INSERT/UPDATE/DELETE 開銷

六、最左前綴原則（組合索引核心）

組合索引 (surname, initial, first_name) 實際建立了三個可用的索引層級：

• (surname, initial, first_name)
• (surname, initial)
• (surname)

失效範例： WHERE initial='x'、WHERE first_name='yvonne'、WHERE surname='clegg' AND first_name='yvonne'（跳過 initial）均無法使用組合索引。

七、EXPLAIN 實戰解析

EXPLAIN SELECT * FROM customer;

關鍵 Extra 值解讀：

• Using filesort：需外部排序，優化索引或調整 ORDER BY
• Using temporary：建立臨時表，常見於 ORDER BY 與 GROUP BY 欄位不一致
• Using index：覆蓋索引，極佳效能
• Using where：WHERE 子句過濾

八、查看索引資訊

SHOW INDEX FROM tablename;

重點欄位：Cardinality（唯一值數量，越大區分度越高）、Sub_part（前綴長度）、Null（是否允許 NULL）。