萬盛學電腦網

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

MySQL 索引原理與 SQL 優化核心筆記:從 B+ 樹到執行計劃實戰

前言

本文整理自實戰筆記,涵蓋 InnoDB/MyISAM 索引底層結構、索引設計原則、SQL 優化實戰技巧、EXPLAIN 執行計劃深度解讀、覆蓋索引應用等核心知識點,助你構建系統化的 MySQL 優化知識體系。

一、存儲引擎索引架構對比

InnoDB:索引組織表(IOT),聚簇索引

  • B+ 樹葉子節點存 Key + Row Data(主鍵/非空唯一索引)
  • 二級索引葉子節點存 Key + 主鍵值,回表查數據
  • 無主鍵 → 選非空唯一索引 → 否則隱式生成 6 字節 Row ID

MyISAM:非聚簇索引(堆表)

  • 主鍵/二級索引葉子節點均存 Key + 行指針
  • 數據文件與索引文件分離,查詢需「索引 → 指針 → 數據」兩次查找

二、索引設計黃金法則

原則說明反例
業務無關自增主鍵避免業務鍵導致頁分裂、主鍵變更連鎖更新二級索引UUID、業務編碼作主鍵
區分度優先選擇性 = 唯一值/總行數,越接近 1 越好;基數極低(如性別)不建索引WHERE sex='M' 加索引反而全表掃描
最左前綴原則聯合索引 (a,b,c) 支援 a / a,b / a,b,c 查詢WHERE b=1 AND c=2 用不到索引
覆蓋索引SELECT 列全在索引中,避免回表SELECT * 破壞覆蓋索引
拒絕 SELECT *讀取無用列、破壞覆蓋、增加網絡 IO習慣性 SELECT *
字符串前綴匹配LIKE 'abc%' 走索引,LIKE '%abc' 全掃模糊查詢加 % 在前
大批量導入先導數據、後建索引、批量提交邊導邊建索引、單條提交
排序字段加索引ORDER BY 高頻列加索引避免 Filesort大表 ORDER BY 無索引列
慎用 LIMIT offset,rows深分頁掃描 offset+rows 行,改用 ID 游標:WHERE id > last_id LIMIT rowsLIMIT 1000000,10

三、EXPLAIN 執行計劃實戰解讀

EXPLAIN SELECT id,author,date,title,status,name FROM wp_posts;
字段值解讀
id1單查詢無子查詢
select_typeSIMPLE簡單查詢
tablewp_posts訪問表
typeALL全表掃描! 無索引可用
possible_keysNULL無候選索引
keyNULL未使用索引
rows56預估掃描 56 行
ExtraNULL無額外優化信息

優化動作:根據 WHERE/JOIN/ORDER BY 列建立適當索引,將 type 從 ALL 提升至 ref/range/eq_ref。

關鍵 type 等級(由優到劣)

system > const > eq_ref > ref > range > index > ALL

Extra 關鍵詞紅綠燈

  • 🟢 Using index:覆蓋索引,極佳
  • 🟢 Using where:Server 層過濾,正常
  • 🟡 Using temporary:需臨時表(GROUP BY/DISTINCT),優化空間大
  • 🔴 Using filesort:無法用索引排序,需優化

四、覆蓋索引深度應用

定義:索引包含查詢所需所有列,EXPLAIN 顯示 Using index,避免回表讀取數據文件。

-- 優化前
SELECT * FROM orders WHERE user_id=100 AND status=1;

-- 優化後:建立聯合索引 (user_id, status, order_id, amount, create_time)
SELECT order_id, amount, create_time FROM orders WHERE user_id=100 AND status=1;
-- EXPLAIN 顯示 Using index,性能提升數量級

禁忌:覆蓋索引切勿 SELECT *,否則索引膨脹、查詢退化。

五、總結:構建優化肌肉記憶

  1. 看架構:搞清 InnoDB 聚簇 vs MyISAP 堆表,回表成本不同
  2. 設計索引:業務無關主鍵、高區分度、最左前綴、覆蓋索引
  3. 寫 SQL:拒絕 SELECT *、前綴模糊、深分頁、隱式轉換
  4. 驗計劃:EXPLAIN 看 type、key、rows、Extra 四大金剛
  5. 調參數:innodb_buffer_pool_size 佔物理內存 60-80%,innodb_flush_log_at_trx_commit=1 保安全

優化無終點,唯有持續觀測、假設、驗證、沉澱,才能在 MySQL 性能調優之路上行穩致遠。

copyright © 萬盛學電腦網 all rights reserved