MySQL EXPLAIN深度解析：優化SQL性能的核心利器

MySQL EXPLAIN深度解析：優化SQL性能的核心利器

引言：數據庫性能優化的關鍵

在數據庫應用開發中，SQL查詢性能往往是系統瓶頸的關鍵所在。當面對慢查詢問題時，EXPLAIN命令就像數據庫工程師的X光機，能夠透視SQL語句的執行計劃，揭示查詢優化的核心路徑。本文將全面解析MySQL EXPLAIN的使用技巧和優化策略，幫助開發者掌握數據庫性能調優的核心工具，提升系統響應效率。

一、EXPLAIN基礎與使用場景

1.1 EXPLAIN是什么？

EXPLAIN是MySQL提供的用于分析SQL查詢執行計劃的命令。通過該命令可以獲取MySQL執行查詢的詳細步驟，包括表的讀取順序、索引使用情況、數據檢索方式等關鍵信息。

1.2 核心應用場景

  • 定位慢查詢性能瓶頸
  • 驗證索引使用有效性
  • 優化復雜聯表查詢
  • 理解MySQL查詢優化器行為
  • 驗證SQL改寫后的優化效果

1.3 基本使用語法

-- 基本用法
EXPLAIN SELECT * FROM users WHERE age > 30;-- 查看分區信息
EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date > '2023-01-01';-- JSON格式輸出（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;

二、EXPLAIN輸出列深度解析

2.1 id - 查詢標識符

  • 作用：標識SELECT查詢的序列號
  • 解讀規則：
    • 相同id：同一查詢中的子查詢
    • 遞增id：嵌套查詢（id越大優先級越高）
    • NULL：UNION結果聚合操作

2.2 select_type - 查詢類型

類型描述
SIMPLE簡單SELECT查詢（不含子查詢或UNION）
PRIMARY查詢中最外層的SELECT
SUBQUERY子查詢中的第一個SELECT
DERIVEDFROM子句中的子查詢（派生表）
UNIONUNION中的第二個及后續SELECT
UNION RESULTUNION結果的聚合

2.3 table - 訪問的表

  • 顯示查詢涉及的表名
  • 特殊值：
    • <derivedN>：派生表（N為id值）
    • <unionM,N>：UNION結果（M,N為id值）
    • <subqueryN>：物化子查詢

2.4 partitions - 匹配分區

  • 顯示查詢訪問的分區
  • 非分區表顯示NULL
  • 優化點：避免全分區掃描

2.5 type - 訪問類型（關鍵指標）

性能從優到劣排序：

  1. system：系統表，僅一行記錄
  2. const：通過主鍵/唯一索引訪問
    EXPLAIN SELECT * FROM users WHERE id = 1;
  3. eq_ref：聯表查詢中主鍵/唯一索引關聯
  4. ref：非唯一索引等值查詢
    -- 索引: idx_email
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
  5. ref_or_null：類似ref，但包含NULL值查詢
  6. range：索引范圍掃描
    EXPLAIN SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;
  7. index：全索引掃描
  8. ALL：全表掃描（需重點優化）

2.6 possible_keys - 可能使用的索引

  • 顯示查詢可能使用的索引
  • NULL表示無可用索引
  • 注意：該列僅列出相關索引，不代表實際使用

2.7 key - 實際使用的索引

  • 查詢實際采用的索引
  • NULL表示未使用索引
  • 優化點：強制使用索引FORCE INDEX

2.8 key_len - 索引長度

  • 表示索引使用的字節數
  • 計算規則：
    • 字符集：utf8=3字節, utf8mb4=4字節
    • NULL標志：1字節
    • 數值類型：TINYINT=1, INT=4, BIGINT=8
  • 應用：驗證復合索引使用情況

2.9 ref - 索引引用關系

  • 顯示與索引比較的列或常量
  • 常見值：
    • const：常量值
    • func：函數結果
    • NULL：無引用關系
    • 列名：關聯查詢的列

2.10 rows - 預估掃描行數

  • MySQL預估需要掃描的行數
  • 重要優化指標：值越大性能越差
  • 注意：基于統計信息估算，非精確值

2.11 filtered - 過濾百分比

  • 存儲引擎層過濾后，剩余記錄百分比
  • 范圍：0~100%，值越大越好
  • 優化點：低過濾率需考慮索引優化

2.12 Extra - 額外信息（關鍵診斷）

值含義
Using index覆蓋索引掃描（無需回表）
Using whereWHERE條件過濾存儲引擎返回的結果
Using temporary使用臨時表（需優化）
Using filesort額外排序操作（需優化）
Using index condition索引條件下推（ICP優化）
Select tables optimized away使用聚合函數直接訪問索引完成查詢

三、EXPLAIN優化實戰案例

3.1 案例一：索引失效分析

問題SQL：

SELECT * FROM orders 
WHERE YEAR(order_date) = 2023 
AND status = 'completed';

EXPLAIN輸出：

type: ALL
key: NULL
rows: 100000
Extra: Using where

優化方案：

  1. 避免在索引列使用函數
  2. 創建復合索引(status, order_date)

優化后SQL：

SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND status = 'completed';

優化后EXPLAIN：

type: range
key: idx_status_date
rows: 1500

3.2 案例二：聯表查詢優化

問題SQL：

EXPLAIN SELECT * 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US';

問題輸出：

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY       | NULL | NULL    | NULL | 10000 | Using where |
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 50000 | Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

優化方案：

  1. 為users.country添加索引
  2. 為orders.user_id添加索引
  3. 調整JOIN順序

優化后輸出：

+----+-------------+-------+-------+-----------------+-----------------+---------+--------------+------+-------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref          | rows | Extra       |
+----+-------------+-------+-------+-----------------+-----------------+---------+--------------+------+-------------+
|  1 | SIMPLE      | u     | ref   | idx_country     | idx_country     | 3       | const        | 2000 | Using index |
|  1 | SIMPLE      | o     | ref   | idx_user_id     | idx_user_id     | 4       | db.u.id      | 10   | NULL        |
+----+-------------+-------+-------+-----------------+-----------------+---------+--------------+------+-------------+

3.3 案例三：分頁查詢優化

問題SQL：

SELECT * FROM logs 
ORDER BY create_time DESC 
LIMIT 100000, 10;

EXPLAIN輸出：

type: index
rows: 100010
Extra: Using filesort

優化方案：

SELECT * FROM logs l
JOIN (SELECT id FROM logs ORDER BY create_time DESC LIMIT 100000, 10
) AS tmp USING(id)
ORDER BY create_time DESC;

優化后輸出：

+----+-------------+------------+--------+---------------+---------+---------+-------+--------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows   | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  | 10     |             |
|  1 | PRIMARY     | l          | eq_ref | PRIMARY       | PRIMARY | 4       | tmp.id| 1      |             |
|  2 | DERIVED     | logs       | index  | NULL          | idx_time| 4       | NULL  | 100010 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+-------------+

四、EXPLAIN進階技巧

4.1 JSON格式輸出分析

EXPLAIN FORMAT=JSON 
SELECT * FROM products 
WHERE category_id = 5 AND price > 100;

核心JSON節點解析：

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "45.21"  // 查詢總成本},"table": {"table_name": "products","access_type": "range",  // 訪問類型"possible_keys": ["idx_category_price"],"key": "idx_category_price","used_key_parts": ["category_id","price"], "rows_examined_per_scan": 1250,"rows_produced_per_join": 500,"filtered": "40.00",      // 過濾百分比"index_condition": "((`products`.`price` > 100))","cost_info": {"read_cost": "35.21","eval_cost": "10.00","prefix_cost": "45.21"}}}
}

4.2 EXPLAIN ANALYZE（MySQL 8.0+）

真實執行統計：

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE age > 30);

輸出示例：

-> Nested loop inner join  (cost=1250.25 rows=500) (actual time=2.125..15.321 rows=1500 loops=1)-> Filter: (users.age > 30)  (cost=250.75 rows=100) (actual time=0.875..1.235 rows=150 loops=1)-> Index scan on users using idx_age  (cost=250.75 rows=1000) (actual time=0.532..1.032 rows=1000 loops=1)-> Index lookup on orders using fk_user (user_id=users.id)  (cost=1.25 rows=5) (actual time=0.025..0.035 rows=10 loops=150)

關鍵指標：

  • actual time：實際執行時間（啟動時間…總時間）
  • rows：實際返回行數
  • loops：循環次數

五、索引優化黃金法則

5.1 索引設計原則

  1. 最左前綴原則：復合索引(a,b,c)只能用于：
    • WHERE a=?
    • WHERE a=? AND b=?
    • WHERE a=? AND b=? AND c=?
  2. 避免索引失效場景：
    • 對索引列進行運算或函數操作
    • 使用前導通配符LIKE ‘%value’
    • 隱式類型轉換（如字符串列用數字查詢）
    • OR條件未全覆蓋索引
  3. 覆蓋索引優先：SELECT列盡量包含在索引中
  4. 區分度原則：高區分度列（如ID）放索引左側

5.2 索引選擇策略

場景推薦索引類型
等值查詢B-Tree索引
范圍查詢B-Tree索引
全文搜索FULLTEXT索引
地理位置查詢SPATIAL索引
JSON字段查詢多值索引/函數索引
高并發寫入場景精簡索引

六、執行計劃分析流程

6.1 標準分析路徑

  1. 查看type列：確認訪問類型（目標至少達到range級別）
  2. 檢查key列：驗證是否使用預期索引
  3. 分析rows列：評估掃描行數是否合理
  4. 研究Extra列：識別額外操作（如排序、臨時表）
  5. 審查filtered：評估過濾效率
  6. 驗證key_len：檢查索引使用完整性
  7. 查看partitions：分區使用是否合理

6.2 優化決策樹

  1. 發現ALL類型？
    • 檢查WHERE條件是否可索引化
    • 考慮添加必要索引
  2. 發現Using temporary？
    • 優化GROUP BY/ORDER BY子句
    • 添加復合索引覆蓋排序字段
  3. 發現Using filesort？
    • 確保ORDER BY使用索引排序
    • 增大sort_buffer_size
  4. rows值過大？
    • 優化查詢條件減少掃描范圍
    • 考慮分頁或分區表
  5. filtered過低？
    • 改進查詢條件選擇性
    • 增加復合索引包含過濾字段

七、EXPLAIN常見誤區

7.1 誤解與糾正

常見誤解事實真相
rows是精確值基于統計信息的估算值，可能與實際有偏差
索引越多越好每個索引增加寫操作開銷，需平衡讀寫比例
覆蓋索引不需要回表當查詢列不全在索引中時仍需回表
Using index一定最優全索引掃描（index類型）可能比全表掃描更慢
主鍵查詢總是const類型當使用函數或表達式操作主鍵時可能降級

7.2 統計信息的重要性

  • innodb_stats_persistent：持久化統計信息
  • ANALYZE TABLE：手動更新統計信息
  • 統計信息不準的場景：
    • 表數據大幅變化后
    • 索引選擇性高的表
    • 分區表的分區剪裁不準
-- 更新表統計信息
ANALYZE TABLE orders;

八、性能優化全景圖

8.1 優化層次模型

  1. SQL語句層：
    • 避免SELECT *
    • 優化WHERE條件順序
    • 減少子查詢嵌套
  2. 索引層：
    • 創建合適索引
    • 刪除冗余索引
    • 定期優化索引
  3. 架構層：
    • 讀寫分離
    • 分庫分表
    • 緩存策略
  4. 參數配置層：
    • 調整buffer_pool_size
    • 優化sort_buffer_size
    • 配置join_buffer_size

8.2 監控工具鏈

工具用途
PERFORMANCE_SCHEMA實時監控SQL執行
SHOW PROFILES查看SQL各階段耗時
SHOW STATUS查看數據庫運行狀態
Slow Query Log記錄慢查詢日志
pt-query-digest慢查詢日志分析工具

九、未來發展趨勢

9.1 MySQL優化器演進

  1. 直方圖統計信息（MySQL 8.0+）
    ANALYZE TABLE orders UPDATE HISTOGRAM ON amount;
  2. 代價模型優化：更精確的IO/CPU成本計算
  3. 并行查詢：提升分析型查詢性能
  4. 機器學習優化：基于AI的索引建議

9.2 EXPLAIN增強方向

  1. 可視化執行計劃：圖形化展示查詢路徑
  2. 實時優化建議：自動生成優化方案
  3. 多版本對比：不同優化方案執行計劃對比
  4. 云原生集成：與云數據庫控制臺深度整合

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/89694.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/89694.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/89694.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

Sentinel配置Nacos持久化

Sentinel配置Nacos持久化

前言&#xff1a; Sentinel在使用控制臺時進行配置是純內存操作&#xff0c;并沒有提供默認的持久化措施&#xff0c;一旦服務重啟會導致配置的流控、熔斷等策略失效。Sentinel官方提供了多種持久化方式如&#xff1a;Redis、Zookeeper、Etcd、Nacos以及其他方式等。此文以Naco…
閱讀更多...
Java學習第五十五部分——在軟件開發中的作用

Java學習第五十五部分——在軟件開發中的作用

目錄 一. 前言提要 二. 主要作用 1. 跨平臺能力&#xff08;核心優勢&#xff09; 2. 企業級應用開發&#xff08;主導領域&#xff09; 3. 安卓應用開發&#xff08;關鍵角色&#xff09; 4. 大數據處理&#xff08;重要組件&#xff09; 5. 嵌入式系統 & IoT 6. 桌…
閱讀更多...
Keil編譯文件格式轉換全解析

Keil編譯文件格式轉換全解析

目錄 介紹 Keil自帶常用命令概覽 fromelf介紹 Keil可燒錄文件概述 核心差異概覽 .axf文件獲取 .hex文件獲取 .bin文件生成 ?編輯 補充:生成可執行文件的匯編代碼&#xff08;.asm文件&#xff09; Keil自帶常用命令詳解 核心功能?? ??格式轉換?? ??輸出路…
閱讀更多...
World of Warcraft [CLASSIC] The Ruby Sanctum [RS] Halion

World of Warcraft [CLASSIC] The Ruby Sanctum [RS] Halion

World of Warcraft [CLASSIC] The Ruby Sanctum [RS] Halion 紅玉圣殿海里昂 清小怪小德可以睡龍類 打完3個副官就激活 【海里昂】 第一階段&#xff1a;外面環境&#xff08;現實位面&#xff09;火抗光環 第二階段&#xff1a;內場環境&#xff08;暗影環境&#xff09;暗…
閱讀更多...
Excel基礎：格式化

Excel基礎：格式化

Excel格式化是指對單元格內容進行視覺呈現方式的調整&#xff0c;它不改變實際數據內容&#xff0c;但能顯著提升數據的可讀性&#xff0c;根據格式應用的范圍&#xff0c;格式可分為"單元格格式"和"數據格式"。本文介紹了這兩類格式的主要設置方法&#x…
閱讀更多...
基于STM32單片機車牌識別系統攝像頭圖像處理設計的論文

基于STM32單片機車牌識別系統攝像頭圖像處理設計的論文

摘 要 本設計提出了一種基于 32 單片機的車牌識別系統攝像頭圖像處理方案。該系統主要由 STM32F103RCT6 單片機核心板、2.8 寸 TFT 液晶屏顯示、攝像頭圖像采集 OV7670、蜂鳴器以及 LED 電路組成。 在車牌識別過程中&#xff0c;STM32F103RCT6 單片機核心板發揮著關鍵的控制作用…
閱讀更多...
React hooks——useCallback

React hooks——useCallback

一、簡介useCallback 是 React 提供的一個 Hook&#xff0c;用于緩存函數引用&#xff0c;避免在組件重新渲染時創建新的函數實例&#xff0c;從而優化性能。1.1 基本語法const memoizedCallback useCallback(() > {// 函數邏輯},[dependencies] // 依賴項數組 );第一個參數…
閱讀更多...
跨個體預訓練與輕量化Transformer在手勢識別中的應用：Bioformer

跨個體預訓練與輕量化Transformer在手勢識別中的應用：Bioformer

目錄 一、從深度學習到邊緣部署&#xff0c;手勢識別的新突破 &#xff08;一&#xff09;可穿戴設備 邊緣計算 個性化醫療新可能 &#xff08;二&#xff09;肌電信號&#xff08;sEMG&#xff09;&#xff1a;手勢識別的關鍵媒介 &#xff08;三&#xff09;挑戰&#…
閱讀更多...
無線調制的幾種方式

無線調制的幾種方式

不同的調制方式在 頻譜效率、抗干擾能力、功耗、實現復雜度 等方面存在顯著差異。以下是主要調制方式的對比分析&#xff1a;一、調制方式的核心區別調制方式原理頻譜效率抗干擾能力功耗典型應用AM改變載波振幅低差&#xff08;易受噪聲影響&#xff09;較高廣播電臺FM改變載波…
閱讀更多...
五分鐘系列-網絡性能測試工具iperf3

五分鐘系列-網絡性能測試工具iperf3

目錄 一、iperf3 是什么&#xff1f; 二、為什么需要 iperf3&#xff1f; 三、iperf3 的主要測量指標 四、安裝 iperf3 五、基礎使用模式&#xff08;命令行參數示例&#xff09; 1. 啟動 Server 端 (必須) 2. 啟動 Client 端進行測試 (最常見) 基本 TCP 測試 (10秒) …
閱讀更多...
LiFePO4電池的安全詳解

LiFePO4電池的安全詳解

一、電池的制作過程 鋰離子電池的五大核心組成部分：正極、負極、電解液、隔膜和外殼。 正極：把正極材料（如LiFePO?）+ 導電劑（如炭黑）+ 粘結劑（如PVDF）混合成漿料，涂覆在鋁箔上，然后烘干、輥壓。 負極：把負極材料（如石墨）+ 導電劑 + 粘結劑混合成漿料，涂覆在銅…
閱讀更多...
S7-200 SMART PLC： SMART 200 CPU 密碼設置及權限設置方式

S7-200 SMART PLC： SMART 200 CPU 密碼設置及權限設置方式

在工業自動化控制中&#xff0c;SMART 200 CPU 的安全穩定運行直接關系到整個系統的可靠運作&#xff0c;而組態系統安全則是保障 SMART 200 CPU 安全的核心環節。通過合理設置密碼及相關安全參數&#xff0c;能為 SMART 200 CPU 構建一道堅固的防護屏障&#xff0c;有效保護用…
閱讀更多...
Datawhale AI數據分析 作業

Datawhale AI數據分析 作業

一、 貸款批準預測數據集1. 數據探索與理解prompt 1:這是訓練數據&#xff0c;目的是貸款批準預測數據集上訓練的深度學習模型生成的數據&#xff0c;旨在使用借款人信息預測貸款批準結果&#xff0c;它通過模擬真實貸款審批場景&#xff0c;幫助金融機構評估借款人風險。 請展…
閱讀更多...
100條常用SQL語句

100條常用SQL語句

一、基本查詢語句 查詢所有數據&#xff1a; SELECT * FROM 表名; 查詢特定列&#xff1a; SELECT 列名1, 列名2 FROM 表名; 條件查詢&#xff1a; SELECT * FROM 表名 WHERE 條件; 模糊查詢&#xff1a; SELECT * FROM 表名 WHERE 列名 LIKE ‘模式%’; 排序查詢&#xff1a; …
閱讀更多...
Visual Studio編譯WPF項目生成的文件介紹

Visual Studio編譯WPF項目生成的文件介紹

文章目錄一、Summarize主要輸出文件1. **可執行文件 (.exe)**2. **程序集文件 (.dll)**3. **PDB 文件 (.pdb)**資源與配置文件1. **XAML 編譯文件 (.baml)**2. **資源文件 (.resources)**3. **應用程序配置文件 (.config)**依賴文件1. **引用的 NuGet 包**2. **引用的框架程序集…
閱讀更多...
「Chrome 開發環境快速屏蔽 CORS 跨域限制詳細教程」*

「Chrome 開發環境快速屏蔽 CORS 跨域限制詳細教程」*

Chrome 開發環境快速屏蔽 CORS 跨域限制【超詳細教程】 &#x1f4e2; 為什么需要臨時屏蔽 CORS&#xff1f; 在日常前后端開發中&#xff0c;我們經常會遇到這樣的報錯&#xff1a; Access to fetch at https://api.example.com from origin http://localhost:3000 has been …
閱讀更多...
Linux命令大全-df命令

Linux命令大全-df命令

一、簡介df&#xff08;英文全拼&#xff1a;display free disk space&#xff09; 命令用于顯示或查看文件系統&#xff08;或磁盤&#xff09;的空間使用情況&#xff0c;包括總容量、已用空間、可用空間、使用率和掛載點等信息。二、語法df [選項]... [文件]...參數參數說明…
閱讀更多...
《程序員修煉之道》第一二章讀書筆記

《程序員修煉之道》第一二章讀書筆記

最近在看《程序員修煉之道&#xff1a;通向務實的最高境界》這一本書&#xff0c;記錄一下看書時的一點淺薄感悟。務實程序員不僅是一種技能水平的體現&#xff0c;更是一種持續修煉、不斷反思并主動承擔責任的過程。對自己的行為負責是務實哲學的基石之一。在重構CRM時面對文檔…
閱讀更多...
ArcGISPro應用指南：使用ArcGIS Pro創建與優化H3六邊形網格

ArcGISPro應用指南：使用ArcGIS Pro創建與優化H3六邊形網格

H3 是由 Uber 開發的一個開源地理空間分析框架&#xff0c;旨在通過將地球表面劃分為等面積的六邊形網格來支持各種地理空間數據分析任務。每個六邊形單元在 H3 系統中都有一個獨一無二的標識符&#xff0c;即 H3 指數。這種網格系統不僅能夠覆蓋全球&#xff0c;而且適用于任何…
閱讀更多...
xss-dom漏洞

xss-dom漏洞

目錄 靶場搭建 第一關 第二關 第三關 第四關 第五關 第六關 第七關 第八關 靶場下載地址&#xff1a;https://github.com/PwnFunction/xss.pwnfunction.com 靶場搭建 將文件用clone 下載到ubuntu&#xff0c; 然后進入 cd xss.pwnfunction.com/hugo/ 這個目錄下 hu…
閱讀更多...
最新文章

Copyright @ 2022~2023