EXPLAIN優化 SQL示例

以下通過 6 個真實案例展示如何使用 EXPLAIN 優化 SQL，每個案例包含問題 SQL、EXPLAIN 分析、優化方案和優化后效果對比：

案例 1：全表掃描優化 (type=ALL)

問題 SQL（用戶訂單查詢）：
SELECT * FROM orders 
WHERE user_id = 1005 AND create_date > '2023-01-01';
?? EXPLAIN 分析：
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra  |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | 50萬 | Using where|
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

診斷：

  • type=ALL：全表掃描
  • rows=500,000：掃描 50 萬行
  • key=NULL：未使用索引
🔧 優化方案：
-- 添加復合索引
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_date);
? 優化后 EXPLAIN：
+----+-------------+--------+-------+---------------+------------------+---------+------+------+
| id | select_type | table  | type  | key           | key_len | rows | Extra|
+----+-------------+--------+-------+---------------+---------+------+------+
| 1  | SIMPLE      | orders | ref   | idx_user_create | 8       | 15   | Using index |
+----+-------------+--------+-------+---------------+---------+------+------+

效果：

  • 掃描行數 50萬 → 15 行
  • 查詢時間 2.8秒 → 0.02秒

案例 2：文件排序優化 (Using filesort)

問題 SQL（最新商品查詢）：
SELECT * FROM products 
WHERE category = 'electronics' 
ORDER BY create_time DESC 
LIMIT 20;
?? EXPLAIN 分析：
+----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table    | type | key           | rows | Extra                              |
+----+-------------+----------+------+---------------+------+-------------------------------------+
| 1  | SIMPLE      | products | ref  | idx_category  | 2500 | Using index condition; Using filesort|
+----+-------------+----------+------+---------------+------+-------------------------------------+

診斷：

  • Using filesort：額外文件排序
  • 雖然用了索引，但排序字段未包含
🔧 優化方案：
-- 創建包含排序字段的索引
ALTER TABLE products ADD INDEX idx_cat_time (category, create_time DESC);
? 優化后 EXPLAIN：
+----+-------------+----------+-------+----------------+---------+------+-------------+
| id | select_type | table    | type  | key            | rows    | Extra          |
+----+-------------+----------+-------+----------------+---------+----------------+
| 1  | SIMPLE      | products | ref   | idx_cat_time   | 2500    | Using where    |
+----+-------------+----------+-------+----------------+---------+----------------+

效果：

  • 移除 Using filesort（索引已排好序）
  • 500ms 的文件排序操作 → 0ms

案例 3：子查詢優化 (DEPENDENT SUBQUERY)

問題 SQL（高消費用戶查詢）：
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000
);
?? EXPLAIN 分析：
+----+--------------------+--------+----------------+---------------+-----------------+
| id | select_type        | table  | type           | key           | rows | Extra       |
+----+--------------------+--------+----------------+---------------+-----------------+
| 1  | PRIMARY            | users  | ALL            | NULL          | 10萬 | Using where |
| 2  | DEPENDENT SUBQUERY | orders | index_subquery | idx_user      | 30   | Using where |
+----+--------------------+--------+----------------+---------------+-----------------+

診斷：

  • DEPENDENT SUBQUERY：外查詢每行都執行子查詢
  • 外層全表掃描 10萬行 × 子查詢 30行 = 實際掃描 300萬行
🔧 優化方案：
-- 改為 JOIN 寫法
SELECT u.* 
FROM users u
JOIN (SELECT DISTINCT user_id FROM orders WHERE amount > 1000
) o ON u.id = o.user_id;
? 優化后 EXPLAIN：
+----+-------------+------------+--------+---------------+---------+------+-------+
| id | select_type | table      | type   | key           | rows    | Extra|
+----+-------------+------------+--------+---------------+---------+------+
| 1  | PRIMARY     | <derived2> | ALL    | NULL          | 1500    |       |
| 1  | PRIMARY     | u          | eq_ref | PRIMARY       | 1       |       |
| 2  | DERIVED     | orders     | range  | idx_amount     | 1500    |       |
+----+-------------+------------+--------+---------------+---------+------+

效果：

  • 執行時間 4.2秒 → 0.3秒
  • 掃描總量 300萬行 → 1500 + 1500行

案例 4：索引覆蓋優化 (回表查詢)

問題 SQL（用戶統計）：
SELECT username, email FROM users 
WHERE register_time BETWEEN '2022-01-01' AND '2022-12-31';
?? EXPLAIN 分析：
+----+-------------+-------+-------+------------------+---------+------+-------+
| id | select_type | table | type  | key              | rows    | Extra       |
+----+-------------+-------+-------+------------------+---------+-------------+
| 1  | SIMPLE      | users | range | idx_register_time| 15000   | Using where |
+----+-------------+-------+-------+------------------+---------+-------------+

診斷：

  • Extra列信息僅顯示 Using where，?沒有出現 Using index?（重要！）
    這表示：
    雖然使用了索引idx_register_time定位數據（type=range證明索引生效）
    但索引未覆蓋所有查詢字段，需回聚簇索引獲取完整行數據
  • 雖然使用了索引，但需要回表查 username, email 字段
  • 潛在優化點：覆蓋索引
🔧 優化方案：
-- 創建包含所有查詢字段的覆蓋索引
ALTER TABLE users ADD INDEX idx_cover_register (register_time, username, email);
? 優化后 EXPLAIN：
+----+-------------+-------+-------+--------------------+---------+------+-------------+
| id | select_type | table | type  | key                | rows    | Extra            |
+----+-------------+-------+-------+--------------------+---------+------------------+
| 1  | SIMPLE      | users | range | idx_cover_register | 15000   | Using where; Using index |
+----+-------------+-------+-------+--------------------+---------+------------------+

效果：

  • Using index：避免回表操作
  • I/O 操作減少 60%
  • 查詢時間 450ms → 120ms

案例 5：JOIN 優化 (錯誤的 JOIN 順序)

問題 SQL（訂單詳情查詢）：
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > 500 AND u.vip_level > 3;
?? EXPLAIN 分析：
+----+-------------+-------+------+---------------+---------+------+----------+-------------+
| id | select_type | table | type | key           | rows    | Extra                        |
+----+-------------+-------+------+---------------+---------+------------------------------+
| 1  | SIMPLE      | u     | ALL  | idx_vip       | 10000   | Using where                  |
| 1  | SIMPLE      | o     | ref  | idx_user      | 25      | Using where                  |
+----+-------------+-------+------+---------------+---------+------------------------------+

診斷：

  • 先掃描 1萬VIP用戶，再關聯訂單
  • 實際訂單篩選條件 amount>500 在關聯后執行
🔧 優化方案：
-- 重寫查詢調整 JOIN 順序
SELECT o.*, u.name 
FROM (SELECT * FROM orders WHERE amount > 500  -- 先過濾大表
) o
JOIN users u ON o.user_id = u.id 
WHERE u.vip_level > 3;
? 優化后 EXPLAIN：
+----+-------------+------------+--------+---------------+---------+------+--------+
| id | select_type | table      | type   | key           | rows    | Extra       |
+----+-------------+------------+--------+---------------+---------+-------------+
| 1  | PRIMARY     | <derived2> | ALL    | NULL          | 8000    |             |
| 1  | PRIMARY     | u          | eq_ref | PRIMARY       | 1       | Using where |
| 2  | DERIVED     | orders     | range  | idx_amount    | 8000    | Using where |
+----+-------------+------------+--------+---------------+---------+-------------+

效果：

  • 減少驅動表數據量：1萬行 → 8000行
  • 總掃描行數：1萬×25=25萬行 → 8000+8000行
  • 執行時間：1.8秒 → 0.4秒

案例 6：分頁深度優化 (大偏移量分頁)

問題 SQL（第10000頁數據）：
SELECT id, title FROM articles 
ORDER BY create_time DESC 
LIMIT 10000, 20; -- 跳過10000條
?? EXPLAIN 分析：
+----+-------------+----------+-------+---------------+---------+------+-------------+
| id | select_type | table    | type  | key           | rows    | Extra                 |
+----+-------------+----------+-------+---------------+---------+-----------------------+
| 1  | SIMPLE      | articles | index | idx_create    | 10020   | Using index           |
+----+-------------+----------+-------+---------------+---------+-----------------------+

診斷：

  • rows=10020：實際讀取 10020 行（即使最終只返回20條）
  • 深度分頁性能災難
🔧 優化方案：
-- 基于游標的優化寫法
SELECT id, title 
FROM articles 
WHERE create_time < '2023-06-01'  -- 傳入上一頁的最后時間
ORDER BY create_time DESC 
LIMIT 20;
? 優化效果對比：
方案掃描行數執行時間
原始方案10020320ms
游標方案201.2ms
提升500倍266倍

總結：EXPLAIN 優化路線圖

graph TDA[捕獲問題SQL] --> B[運行EXPLAIN]B --> C{關鍵問題點}C -->|type=ALL| D[添加缺失索引]C -->|Using filesort| E[添加排序索引]C -->|DEPENDENT SUBQUERY| F[改寫為JOIN]C -->|全表掃描| G[添加覆蓋索引]C -->|高rows值| H[優化查詢條件]C -->|Using temporary| I[優化GROUP BY]D & E & F & G & H & I --> J[重新EXPLAIN驗證]J --> K{性能達標？}K -->|是| L[完成]K -->|否| B

通過系統分析 EXPLAIN 結果，遵循 “減少掃描行數” 和 “避免額外操作” 兩大原則，可解決大多數 SQL 性能問題。建議將 EXPLAIN 作為 SQL 上線前的標準檢查項。

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

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

相關文章

「Linux文件及目錄管理」通配符與文件名

「Linux文件及目錄管理」通配符與文件名

「Linux文件及目錄管理」通配符與文件名 知識點解析 通配符是Linux中用于匹配文件名的特殊字符，能高效處理批量文件操作。 常見通配符包括： *：匹配任意字符序列（包括空字符）touch a b ab a123 # 創建測試文件 ls a* # 匹配a, ab, a123?：精確匹配單個字符…
閱讀更多...
服務器配置記錄

服務器配置記錄

1. 獲取服務器IP&#xff0c;用戶&#xff0c;密碼 2. 使用VS Code遠程登錄 下載ssh插件本地cmd執行ssh-keygen -t rsa -b 4096 -C "jt_windows"完成密鑰生成。本地cmd執行type %USERPROFILE%\.ssh\id_rsa.pub查看密鑰并復制。遠程服務器執行以下命令&#xff1a; …
閱讀更多...
Windows 后滲透中可能會遇到的加密字符串分析

Windows 后滲透中可能會遇到的加密字符串分析

在 Windows 后滲透過程中&#xff0c;攻擊者經常會遇到各種加密字符串或數據&#xff0c;這些數據通常用于存儲敏感信息&#xff0c;如憑據、會話票據或配置數據。理解這些加密字符串的類型、加密機制、存儲位置以及解密方法&#xff0c;對于權限提升、橫向移動和持久化至關重要…
閱讀更多...
騰訊云本地專用集群CDC：混合云架構下的分布式云服務實踐

騰訊云本地專用集群CDC：混合云架構下的分布式云服務實踐

摘要 在數字化轉型加速的背景下&#xff0c;企業上云面臨數據合規、低時延、運維復雜度等多重挑戰。騰訊云本地專用集群CDC&#xff08;Cloud Dedicated Cluster&#xff09;通過融合公有云與本地IDC優勢&#xff0c;提供近場云服務解決方案。本文基于IDC行業報告及技術實測數…
閱讀更多...
wpa_supplicant 源碼學習

wpa_supplicant 源碼學習

代碼地址&#xff1a;git clone https://w1.fi/hostap.git 我目前學的的版本是 wpa_supplicant 2.12-devel-hostap_2_11-857-g54930b62b 五月份左右的提交&#xff0c;是較新的代碼 想做白盒測試。最近開始學習 wpa_supplicant 這個工具。 自學了一個多月吧。 整理了一些代碼跳…
閱讀更多...
[學習] C語言＜string.h＞中字符串函數全解析

[學習] C語言＜string.h＞中字符串函數全解析

C語言<string.h>中字符串函數全解析 在 C 語言中&#xff0c;字符串處理是程序開發中的重要組成部分。C 標準庫 <string.h> 提供了一系列函數用于操作字符數組&#xff08;即字符串&#xff09;。這些函數以 str 開頭&#xff0c;功能強大、使用廣泛&#xff0c;掌…
閱讀更多...
OJ搭建：Judge0服務器、DeepSeek服務接入簡介

OJ搭建：Judge0服務器、DeepSeek服務接入簡介

序 各大OJ平臺上有很多很好的資源&#xff0c;但作為自己的“備課本”總有各種不便&#xff0c;教學生時間久了總是有一些自己的題目和想法&#xff0c;這在教初學的學生時非常突出。所以&#xff0c;很多年前就搞了一些嘗試&#xff0c;包括&#xff1a;在機房搭建ubuntu服務器…
閱讀更多...
Java的鎖機制問題

Java的鎖機制問題

鎖機制 1.鎖監視器 在 Java 并發編程中&#xff0c;鎖監視器&#xff08;Monitor&#xff09; 是對象內部與鎖關聯的同步機制&#xff0c;用于控制多線程對共享資源的訪問。以下是核心要點&#xff1a; &#x1f512; 監視器的核心組成 獨占區&#xff08;Ownership&#xff…
閱讀更多...
老鳳祥的AI智能眼鏡：讓智慧更近生活

老鳳祥的AI智能眼鏡：讓智慧更近生活

在科技進步的潮流中，人工智能技術不斷為我們的生活增添色彩。近日，有關字節跳動旗下的火山引擎與中國珠寶品牌老鳳祥合作開發 AI 智能眼鏡的消息引發了廣泛關注。這款與眾不同的眼鏡因其獨特的功能及技術支持，已經在業內引起了極大反響。 AI眼鏡：老年群體的智能好幫手 根…
閱讀更多...
Kotlin 中為什么沒有靜態變量和靜態方法—不用static？

Kotlin 中為什么沒有靜態變量和靜態方法—不用static？

Kotlin 的設計核心是&#xff1a; 一切皆對象&#xff1a;消除 static 的「非對象」特性&#xff0c;用 companion&#xff08;對象&#xff09;和頂層函數&#xff08;包級對象&#xff09;替代&#xff0c;讓代碼更統一。避免全局狀態濫用&#xff1a;static 成員是全局可見…
閱讀更多...
VSCode性能調優：從卡頓到絲滑的終極方案

VSCode性能調優：從卡頓到絲滑的終極方案

? 核心價值 "這套配置使某金融核心系統VS Code內存占用從8GB降至1.2GB，加載速度提升15倍" —— 2024某銀行效能優化報告 ?? 性能瓶頸拆解 一、百萬行項目優化方案 ?? 黃金配置參數 // settings.json（核彈級優化） {"files.watcherExclude": {"…
閱讀更多...
以云織夢，渡數濟世：輝瑞與亞馬遜云科技共譜醫藥新樂章

以云織夢，渡數濟世：輝瑞與亞馬遜云科技共譜醫藥新樂章

胖頭陀科技 編輯&#xff1a;沐由 【導讀】“用合規的數據來幫助患者&#xff0c;成為患者回歸健康的一味新藥。”當下&#xff0c;在數字洪流的浪潮中&#xff0c;這味“良藥”正沿著云和AI的脈絡&#xff0c;奔向有需求的千家萬戶…… 如果說到Pfizer&#xff0c;估計十個人…
閱讀更多...
SpringBoot后端開發知識點總結（持續更新）

SpringBoot后端開發知識點總結（持續更新）

目錄 1. 常用易混淆注解解釋1.1 Resource和Autowired注解的區別1.2 PathVariable和RequestParam注解的區別 2. Mybatis-Plus高級特性2.1 強大的通用CRUD接口2.2 代碼生成器 3. IDEA實用快捷鍵4. 前后端聯調關鍵點4.1 代碼示例4.2 聯調要點4.3 調試技巧 1. 常用易混淆注解解釋 …
閱讀更多...
電腦商城--用戶收貨管理

電腦商城--用戶收貨管理

新增收貨地址 1 新增收貨地址-創建數據表 1.使用use命令先選中store數據庫。 USE store; 2.在store數據庫中創建t_address用戶數據表。 CREATE TABLE t_address (aid INT AUTO_INCREMENT COMMENT 收貨地址id,uid INT COMMENT 歸屬的用戶id,name VARCHAR(20) COMMENT 收貨人姓…
閱讀更多...
開發者避坑：接入Flux-Kontext API實現文生圖、圖生圖功能

開發者避坑：接入Flux-Kontext API實現文生圖、圖生圖功能

在數字化浪潮背景下&#xff0c;人工智能&#xff08;Artificial Intelligence, AI&#xff09;技術正加速重塑圖像創作領域。智創聚合API平臺近日宣布整合Flux-Kontext系列模型&#xff0c;通過API接口支持圖生圖和文生圖功能&#xff0c;為開發者及創作者提供高效解決方案。此…
閱讀更多...
.Net Core 獲取與bin目錄相同文件路徑的文件

.Net Core 獲取與bin目錄相同文件路徑的文件

在 .NET Core 中&#xff0c;您可以使用以下方法來獲取與 bin 目錄相同的文件路徑。通常&#xff0c;bin 目錄是應用程序編譯后生成的輸出目錄&#xff0c;您可以使用 AppContext.BaseDirectory 或 Directory.GetCurrentDirectory() 來獲取該目錄的路徑。 以下是一些常用的方法…
閱讀更多...
RN（React Native）技術應用中常出現的錯誤及解決辦法

RN（React Native）技術應用中常出現的錯誤及解決辦法

React Native 作為跨平臺開發框架&#xff0c;在實際應用中可能會遇到一些常見的錯誤。以下是React Native 技術應用中常出現的錯誤及解決辦法&#xff1a; 1. 網絡請求失敗&#xff08;Network Request Failed&#xff09; 原因&#xff1a; 請求地址不正確網絡權限未配置i…
閱讀更多...
Java 21 的虛擬線程與橋接模式：構建高性能并發系統

Java 21 的虛擬線程與橋接模式：構建高性能并發系統

Java 21 的虛擬線程與橋接模式&#xff1a;構建高性能并發系統 &#x1f31f; 嗨&#xff0c;我是IRpickstars&#xff01; &#x1f30c; 總有一行代碼&#xff0c;能點亮萬千星辰。 &#x1f50d; 在技術的宇宙中&#xff0c;我愿做永不停歇的探索者。 ? 用代碼丈量世界&…
閱讀更多...
HTML5 火焰字體效果教程

HTML5 火焰字體效果教程

HTML5 火焰字體效果教程 這里寫目錄標題 HTML5 火焰字體效果教程前言項目概述基本原理項目結構詳細實現步驟1. HTML結構2. CSS樣式3. JavaScript實現 代碼詳解1. 初始化設置2. 粒子系統3. 生成粒子4. 動畫循環5. 交互控制 擴展和優化建議總結完整代碼 前言 在這篇教程中&#…
閱讀更多...
SMOTE-XGBoost實戰：金融風控中欺詐檢測的樣本不平衡解決方案

SMOTE-XGBoost實戰：金融風控中欺詐檢測的樣本不平衡解決方案

1. 行業問題背景 &#xff08;1&#xff09;金融欺詐檢測的特殊性 在支付風控領域&#xff0c;樣本不平衡是核心痛點。Visa 2023年度報告顯示&#xff0c;全球信用卡欺詐率約為0.6%&#xff0c;但單筆欺詐交易平均損失高達$500。傳統機器學習模型在此場景下表現堪憂&#xff1…
閱讀更多...
最新文章

Copyright @ 2022~2023