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

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈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