萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引架構深度解析:類型選擇、最左前綴、優化實戰與避坑指南

索引基礎概念

索引是儲存引擎用於快速尋找記錄的資料結構 (B+ Tree 為主)。好比書的**目錄**,避免全表掃描。InnoDB 索引即資料 (Clustered Index),MyISAM 索引指向資料物理位置 (Non-clustered)。

索引類型完整對比

類型關鍵字特性適用場景建立語法
主鍵PRIMARY KEY唯一、非空、叢集索引 (InnoDB)每張表必有,業務主鍵或自增 ID`PRIMARY KEY (id)`
唯一索引UNIQUE值唯一、允許 NULL (單列單一 NULL)業務唯一鍵 (身分證、Email、手機)`UNIQUE INDEX uk_email (email)`
普通索引INDEX / KEY允許重複、允許 NULLWHERE/JOIN/ORDER BY/GROUP BY 高頻欄位`INDEX idx_name (name)`
全文索引FULLTEXT支援自然語言/布林模式搜尋 (5.6+ InnoDB 支援)文章、日誌、評論內容搜尋`FULLTEXT INDEX ft_content (content) WITH PARSER ngram`
空間索引SPATIALGIS 幾何類型 (POINT, POLYGON...)地理位置查詢`SPATIAL INDEX sp_loc (location)`
組合索引INDEX (a,b,c)多欄位組合,**遵循最左前綴原則**多條件聯合查詢、覆蓋索引`INDEX idx_a_b_c (a,b,c)`

最左前綴原則 核心法則

組合索引 `(a, b, c)` 等價於建立三個虛擬索引:`(a)`、`(a,b)`、`(a,b,c)`。

  • ✅ `WHERE a=1` / `WHERE a=1 AND b=2` / `WHERE a=1 AND b=2 AND c=3` → **用到索引**
  • ❌ `WHERE b=2` / `WHERE c=3` / `WHERE b=2 AND c=3` → **全表掃描** (除非覆蓋索引)
  • ⚠️ `WHERE a=1 AND c=3` → **僅用到 (a)**,`c` 無法利用索引查找 (中間斷層)。

優化技巧: 調整欄位順序將**高選擇性 (區分度高) 欄位放左邊**,或建立多個組合索引覆蓋不同查詢模式。

索引優化實戰清單

1. 何時使用叢集 vs 非叢集 (InnoDB 視角)

操作類型叢集索引 (PK)非叢集索引 (Secondary)
範圍查詢 (BETWEEN, >, <)✅ 極快 (葉節點即資料)⚠️ 需回表 (雙重 B+ Tree 遍歷)
單點查詢 (PK = ?)✅ 最快✅ 快 (回表 1 次)
頻繁 UPDATE 索引欄位❌ 移動資料頁、頁分裂嚴重✅ 僅修改索引樹
覆蓋索引查詢N/A✅ 避免回表,極速

建議: InnoDB **必須有明確主鍵** (最好自增 BIGINT),業務查詢走覆蓋索引或二級索引回表。

2. 索引失效常見模式 (SARGable 違反)


-- 1. 函數/運算在索引欄上
WHERE YEAR(create_time) = 2024        -- ❌
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' -- ✅

-- 2. 隱式類型轉換
WHERE varchar_col = 123               -- ❌ (字串轉數字)
WHERE varchar_col = '123'             -- ✅

-- 3. 前導萬用字元
WHERE name LIKE '%張%'                -- ❌
WHERE name LIKE '張%'                 -- ✅

-- 4. OR 條件未全覆蓋索引
WHERE a=1 OR b=2                      -- ❌ (通常全掃)
-- 改寫: UNION ALL 或建立 (a,b) 組合索引視情況

-- 5. 不等於 / NOT IN
WHERE status != 1                     -- ❌ (通常全掃)
-- 改寫: WHERE status IN (0,2,3...) 若值集固定

3. 覆蓋索引 實戰範例


-- 查詢: SELECT id, name FROM users WHERE status=1 AND city='Taipei';
-- 優化: 建立覆蓋索引
CREATE INDEX idx_status_city_cover ON users (status, city) INCLUDE (id, name); -- MySQL 8.0+ 支援 INCLUDE
-- 或 5.7: CREATE INDEX idx_status_city_cover ON users (status, city, id, name); -- 欄位順序: WHERE -> SELECT

4. 索引維護與監控

  • `SHOW INDEX FROM tbl;` 檢視 Cardinality (基數) 接近行數 = 高選擇性。
  • `ANALYZE TABLE tbl;` 更新統計資訊 (InnoDB 自動抽樣,手動觸發更準)。
  • `OPTIMIZE TABLE tbl;` 整理碎片、重建索引 (鎖表,慎用生產)。
  • 監控 `Innodb_index_stats` (持久化統計) 確保穩定計畫。

大表索引策略 (千萬/億級)

  1. 分區表 + 局部索引: 按時間 RANGE 分區,每分區獨立索引,維護窗口僅操作熱分區。
  2. 延遲索引建立: 大量導入資料時,先 `DROP INDEX` → `LOAD DATA` → `CREATE INDEX`,避免逐筆維護索引開銷。
  3. 線上 DDL (Online DDL): MySQL 5.6+ `ALGORITHM=INPLACE, LOCK=NONE` 非阻塞加索引 (視操作類型)。
  4. 隱藏索引 (8.0+): `ALTER TABLE t ALTER INDEX idx INVISIBLE;` 驗證無影響後再 `DROP`。
copyright © 萬盛學電腦網 all rights reserved