資料庫索引核心概念與 MySQL 實戰應用指南

資料庫索引核心概念與 MySQL 實戰應用指南

索引是資料庫效能調校的關鍵，本文系統整理索引原理、分類、建立策略、資料結構與 MySQL 具體操作，幫助開發者建立正確的索引觀念。

什麼是索引？

索引好比書的目錄，讓資料庫快速定位資料，避免全表掃描。例如查詢 SELECT * FROM table1 WHERE id=44，有索引時直接在索引結構中找到 44 對應的行位置，無索引則需遍歷整張表。

索引分類

建立索引的代價

• 增加儲存空間 (每個索引佔用額外磁碟空間)
• 寫入效能下降 (INSERT/UPDATE/DELETE 需同步維護索引)

為什麼建立索引？五大優勢

1. 唯一性索引保證資料唯一
2. 大幅加快檢索速度 (核心原因)
3. 加速表聯結 (特別是外鍵關聯)
4. 優化 GROUP BY / ORDER BY 排序分組
5. 啟用查詢優化器選擇更佳執行計畫

何時不該建立索引？四大反模式

1. 查詢中極少使用的欄位
2. 基數極低的欄位 (如性別、狀態碼)
3. 大型文字/二進位欄位 (TEXT, BLOB, BIT)
4. 寫入頻率遠高於讀取的場景

索引資料結構：B+ Tree

MySQL InnoDB 採用 B+ Tree 實現索引：

• 非葉節點僅存索引鍵值，葉節點存完整資料 (叢集索引) 或主鍵值 (二級索引)
• 葉節點雙向鏈表支援範圍掃描
• 插入分裂、刪除合併維持樹平衡

MySQL 索引類型與建立語法

1. 普通索引

CREATE INDEX idx_name ON mytable(username(10));
ALTER TABLE mytable ADD INDEX idx_name (username(10));
CREATE TABLE mytable (id INT, username VARCHAR(16), INDEX idx_name (username(10)));

2. 唯一索引

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

3. 主鍵索引

CREATE TABLE mytable (id INT PRIMARY KEY, username VARCHAR(16));
ALTER TABLE mytable ADD PRIMARY KEY (id);

4. 組合索引 (複合索引)

ALTER TABLE mytable ADD INDEX idx_name_city_age (name(10), city, age);

最左前綴原則： 組合索引 (a,b,c) 實際等同於 (a), (a,b), (a,b,c) 三個索引。查詢必須從最左欄位開始匹配才能用到索引。

索引建立時機判斷

• 出現在 WHERE、JOIN、ORDER BY、GROUP BY 的欄位
• 支援索引的運算子：=, >, <, >=, <=, BETWEEN, IN
• LIKE 'prefix%' 可用索引，LIKE '%suffix' 無法使用

索引使用注意事項與優化技巧

1. 索引不包含 NULL 值：複合索引任一欄為 NULL 導致該索引失效，建議設定 NOT NULL DEFAULT ''
2. 使用短索引：CHAR(255) 欄位前 10-20 字元區分度高時，只索引前綴長度
3. 避免索引欄位運算：WHERE YEAR(date)<2007 導致全表掃描，改為 WHERE date < '2007-01-01'
4. 慎用 NOT IN / <>：通常導致索引失效
5. ORDER BY 優化：單查詢只能用一個索引，WHERE 用了索引則 ORDER BY 無法再用索引，需建立複合索引

索引缺點與維護

• 降低寫入速度 (需同步更新索引檔)
• 佔用磁碟空間 (大表多組合索引膨脹快)
• 定期執行 ANALYZE TABLE 更新統計資訊、減少碎片
• 使用 pt-duplicate-key-checker 檢測重複/冗餘索引