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

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