數據庫索引設計實戰：從原理到優化的完整指南

數據庫索引設計實戰：從原理到優化的完整指南

索引是關係型資料庫性能的核心杠桿。本文結合 MySQL/PostgreSQL 實戰，系統講解索引創建原則、多列索引策略、EXPLAIN 執行計劃分析、排序優化，以及避坑指南。

一、 為什麼需要索引？

無索引時，查詢需全表掃描，時間複雜度 O(N)；B+ 樹索引將查找降為 O(log N)。但索引並非免費：

• 寫入開銷：每次 INSERT/UPDATE/DELETE 需同步維護所有索引樹。
• 空間佔用：每個索引佔用額外磁碟與內存。
• 優化器誤判：統計信息過期可能導致選錯索引。

二、 基礎索引創建

單列索引

CREATE TABLE mytable (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL DEFAULT 0,
    user_id INT NOT NULL DEFAULT 0,
    adddate INT NOT NULL DEFAULT 0
);

-- 單列索引命名規築：表名_列名
CREATE INDEX idx_mytable_category_id ON mytable (category_id);

驗證索引生效：EXPLAIN

EXPLAIN SELECT * FROM mytable WHERE category_id = 1;
-- 關鍵輸出：
-- type: ref  （非 ALL 表示用到索引）
-- key: idx_mytable_category_id
-- rows: 1    （預估掃描行數大幅下降）

三、 多列索引（聯合索引）與最左前綴原則

創建聯合索引

-- 命名：表名_列1_列2
CREATE INDEX idx_mytable_cat_user ON mytable (category_id, user_id);

最左前綴匹配規則

索引 (a, b, c) 可服務：

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 ORDER BY b

不可服務：WHERE b = 2、WHERE a = 1 AND c = 3（跳過 b）、WHERE a > 1 AND b = 2（範圍列後列失效）。

四、 覆蓋索引與排序優化

覆蓋索引

查詢列全部包含在索引中，無需回表：

CREATE INDEX idx_cat_user_add ON mytable (category_id, user_id, adddate);
-- 查詢
SELECT category_id, user_id, adddate FROM mytable WHERE category_id = 1 AND user_id = 2;
-- Extra: Using index  （覆蓋索引）

ORDER BY 避免 Filesort

索引列順序與 ORDER BY 順序一致、方向一致時可直接利用索引有序性：

-- 索引 (category_id, user_id, adddate DESC)
SELECT * FROM mytable
WHERE category_id = 1 AND user_id = 2
ORDER BY adddate DESC;
-- Extra 無 Using filesort

技巧：若 ORDER BY 方向混合（ASC/DESC），MySQL 8.0+ 支援定義索引時指定列排序方向；舊版本需通過應用層統一方向或接受 Filesort。

五、 索引失效常見場景

六、 索引維護最佳實踐

1. 定期 ANALYZE TABLE 更新統計信息，防止優化器誤判。
2. 監控索引使用率：SELECT * FROM sys.schema_unused_indexes;（MySQL 5.7+ sys schema）定期清理未用索引。
3. 控制索引數量：單表建議 ≤ 5 個索引，寫入密集表更少。
4. 主鍵選型：優先單調遞增 BIGINT，避免 UUID 導致頁分裂。
5. 前綴索引：長字符串列用 INDEX idx_name (col(10))，平衡區分度與空間。

七、 總結：索引設計清單

1. WHERE、JOIN、ORDER BY、GROUP BY 涉及的高選擇性列建索引。
2. 組合索引遵循「等值在前、範圍在後、排序在尾」。
3. 覆蓋索引消除回表，提升查詢 10-100 倍。
4. 用 EXPLAIN 驗證每次變更，關注 type、key、rows、Extra。
5. 寫入重、讀輕的表慎加索引；讀重表積極優化。