萬盛學電腦網

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

MySQL 索引原理與優化實戰:從 B+ 樹到 EXPLAIN 讀懂執行計畫

索引核心概念

索引是儲存引擎用於快速查找行的資料結構,類似書的目錄。MySQL 索引在**儲存引擎層**實現,不同引擎 (InnoDB、MyISAM) 結構不同。

索引優缺點

優點缺點
大幅加速查詢 (WHERE、JOIN、ORDER BY、GROUP BY)寫入 (INSERT/UPDATE/DELETE) 需維護索引,降低寫效能
支援覆蓋索引 (避免回表)佔用磁碟空間與記憶體 (Buffer Pool)
強制唯一性 (PRIMARY/UNIQUE)索引過多導致優化器選擇困難、統計資訊維護成本

InnoDB B+ 樹索引結構

  • 聚簇索引:主鍵索引,葉節點存完整行資料,資料物理順序 = 索引順序。
  • 二級索引:葉節點存主鍵值,查詢需「回表」查主鍵索引取完整行。
  • 覆蓋索引:查詢欄位全在二級索引中,無需回表,效能極佳。

資料型別選擇黃金法則

  1. 越小越好:INT (4B) 優於 BIGINT (8B);DATE (3B) 優於 DATETIME (8B) / CHAR(10)。
  2. 簡單優於複雜:整數比較快於字串;IP 存 INT UNSIGNED (用 INET_ATON/INET_NTOA)。
  3. 盡量 NOT NULL:NULL 值佔用額外位元圖、複雜化統計與比較;用 0、空串、特殊值替代。

複合索引最左前綴原則

INDEX idx_a_b_c (a, b, c)
-- 可用:WHERE a=1 | WHERE a=1 AND b=2 | WHERE a=1 AND b=2 AND c=3
-- 可用:ORDER BY a | ORDER BY a,b | ORDER BY a,b,c
-- 不可用:WHERE b=1 | WHERE c=1 | ORDER BY b | ORDER BY c

索引像電話簿:先按姓氏 (a) 排,同姓再按名字 (b) 排。不知道姓氏直接找名字,索引無效。

索引類型分類

  • 主鍵索引 (PRIMARY KEY) - 唯一、非空、聚簇
  • 唯一索引 (UNIQUE) - 值唯一、允許一個 NULL
  • 普通索引 (INDEX/KEY) - 允許重複
  • 全文索引 (FULLTEXT) - MySQL 5.6+ InnoDB 支援中文分詞需插件
  • 空間索引 (SPATIAL) - GIS 資料類型

EXPLAIN 核心欄位讀解

欄位關鍵值含義
typesystem > const > eq_ref > ref > range > index > ALL連接類型,越左越好;ref 以上通常用到索引
possible_keys索引名列表理論上可用的索引
key實際使用的索引NULL 表示全表掃描
key_len位元組數實際用到的索引長度,推斷複合索引用了幾欄
rows預估掃描行數越小越好
ExtraUsing index / Using where / Using filesort / Using temporaryUsing index = 覆蓋索引;Using filesort = 需外部排序,需優化

實戰優化清單

  1. 慢查詢先 EXPLAIN 看 type、key、Extra。
  2. WHERE 條件欄位建立索引,高選擇性 (基數大) 欄位優先。
  3. 複合索引遵循「等值 - 範圍 - 排序」欄位順序。
  4. 避免 SELECT *,只查需要欄位,爭取覆蓋索引。
  5. LIKE 'abc%' 可用索引,LIKE '%abc' 或 LIKE '%abc%' 導致全掃。
  6. 欄位上加函數/運算 (WHERE YEAR(dt)=2023) 失效索引 → 改 WHERE dt >= '2023-01-01' AND dt < '2024-01-01'。
  7. OR 條件兩側皆有索引才可能用索引,否則全掃;考慮 UNION ALL 拆分。
  8. 定期 ANALYZE TABLE 更新統計資訊,讓優化器決策更準。
copyright © 萬盛學電腦網 all rights reserved