MySQL EXPLAIN 详解:一眼看懂查询计划

ops/2024/12/28 8:29:36/

在日常的数据库开发中,我们经常需要分析 SQL 查询性能,而 EXPLAIN 是 MySQL 提供的利器,可以帮我们快速理解查询计划,优化慢查询。本文将详细解析 EXPLAIN 的输出字段及其含义,并结合实际案例分享优化思路。


一、什么是 MySQL EXPLAIN

EXPLAIN 是 MySQL 提供的一种查询分析工具,用来展示 SQL 查询的执行计划。它能直观地展示每个表的访问方式、索引的使用情况以及查询的执行顺序,帮助我们找出性能瓶颈。

使用方式

EXPLAIN [你的SQL语句];

执行后会返回一张表格,展示查询执行的各项详细信息。


二、EXPLAIN 输出字段详解

以下是 EXPLAIN 的关键输出字段,每一列都表示不同的执行信息。

字段名含义优化建议
id查询的执行顺序和优先级优先优化 id 值大的语句
select_type查询的类型确保派生表和子查询的效率
table当前步骤访问的表注意优化复杂查询中的临时表
partitions匹配的分区分区表优化相关
type表的访问方式(性能优劣排序)避免 ALL 全表扫描
possible_keys查询中可能使用的索引确保查询条件中使用了索引列
key实际使用的索引若为 NULL,需检查是否需要创建索引
key_len使用索引的长度确保索引覆盖了查询条件
ref索引比较对象确保通过条件有效筛选
rows预估需要扫描的行数扫描行数越少,性能越高
filtered返回结果占扫描行数的百分比值越接近 100%,过滤条件越高效
Extra补充信息,如临时表、排序等避免使用临时表、文件排序

三、EXPLAIN 字段详解及优化策略
1. id
  • 含义:表示查询的执行顺序。
  • 特点
    • id 值越大,优先执行。
    • 同一 id 从上到下执行。
  • 优化建议:优先优化 id 值较大的查询。
2. select_type
  • 常见值
    • SIMPLE:简单查询,没有子查询。
    • PRIMARY:最外层查询。
    • SUBQUERY:子查询。
    • DERIVED:派生表(子查询的结果作为临时表)。
    • UNIONUNION 中的查询。
3. type
  • 访问方式性能排序
    • 最佳system > const > eq_ref > ref > range > index > ALL
    • ALL(全表扫描):性能最差,应尽量避免。
  • 优化方法
    • 确保查询条件使用索引,避免全表扫描。
4. Extra
  • 关键信息解读
    • Using index:使用覆盖索引,性能较优。
    • Using where:通过 WHERE 过滤条件筛选数据。
    • Using temporary:使用了临时表,需优化。
    • Using filesort:需要额外的排序步骤,尽量优化索引支持排序。

四、典型优化案例

以下通过实际案例,演示如何使用 EXPLAIN 分析和优化查询计划。

1. 优化全表扫描

原始查询:

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

查询计划:

  • type=ALL 表示全表扫描。
  • 优化方法:在 user_id 列上添加索引。
ALTER TABLE orders ADD INDEX idx_user_id(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
2. 优化排序

原始查询:

EXPLAIN SELECT name FROM users WHERE age > 30 ORDER BY age DESC;

查询计划:

  • Extra=Using filesort 表示需要额外排序。
  • 优化方法:在 age 列上添加索引。
ALTER TABLE users ADD INDEX idx_age(age);
EXPLAIN SELECT name FROM users WHERE age > 30 ORDER BY age DESC;
3. 子查询改写为 JOIN

原始查询:

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

查询计划:

  • select_type=SUBQUERY,执行效率较低。
  • 优化方法:改写为 JOIN 查询。
EXPLAIN SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.age > 30;

五、总结
  1. EXPLAIN 是优化查询性能的重要工具,可以帮助我们清晰了解查询计划。
  2. 重点关注字段:如 typerowsExtra,避免全表扫描和文件排序。
  3. 索引是优化的核心:确保查询条件充分利用索引。

SQL 优化的核心原则

  • 合理设计索引。
  • 避免全表扫描。
  • 避免子查询,尽量使用 JOIN 替代。

**实践是优化的关键!**希望本文能帮你一眼看懂查询计划,并在实际项目中助你优化 SQL 性能!


你可以通过留言分享你的优化案例或遇到的疑难问题,一起交流探讨!


http://www.ppmy.cn/ops/144617.html

相关文章

【华为OD-E卷-机器人活动区域 100分(python、java、c++、js、c)】

【华为OD-E卷-机器人活动区域 100分(python、java、c、js、c)】 题目 现有一个机器人,可放置于 M N 的网格中任意位置,每个网格包含一个非负整数编号,当相邻网格的数字编号差值的绝对值小于等于 1 时,机…

Go语言gRPC与gozero的api

什么是gRPC? gRPC是由Google开发并开源的一个高性能、通用的RPC框架。它基于HTTP/2协议,默认使用Protocol Buffers(简称ProtoBuf)作为接口描述语言(IDL)。 gRPC的主要特点: 高性能:得…

B2HGraphicBufferProducer和H2BGraphicBufferProducer

在 Android 的图形系统中,B2HGraphicBufferProducer 和 BnGraphicBufferProducer 是基于 Binder 机制的两个重要组件,它们负责图形缓冲区的生产接口。二者关系可以理解为 桥接和实现分离,以下是详细说明: 1. B2HGraphicBufferProd…

flink sink kafka

接上文:一文说清flink从编码到部署上线 之前写了kafka source,现在补充kafka sink。完善kafka相关操作。 环境说明:MySQL:5.7;flink:1.14.0;hadoop:3.0.0;操作系统&#…

计算材料学和分子动力学(MD)

文章目录 1. 计算材料学1. 什么是计算材料学2. 计算材料学尺度1. 纳观尺度2. 微观尺度3. 介观尺度4. 宏观尺度 2.分子动力学1.什么是分子动力学1. 历史上第一个分子动力学模拟2.第一个连续势场的分子动力学模拟3.第一个Lennard-Jones势分子动力学模拟 2.分子动力学的并行3.常用…

.net core sdk 项目多版本切换

使用global.json文件指定项目要使用的sdk版本&#xff1a; 在项目根目录下执行cmd命令&#xff08;sdk的版本默认为当前使用的最新的sdk的版本&#xff09; 默认sdk&#xff1a;dotnet new globaljson指定sdk&#xff1a;dotnet new globaljson --sdk-version <version>…

【Git】-- 版本说明

Alpha&#xff1a;是内部测试版,一般不向外部发布,会有很多 Bug .一般只有测试人员使用。Beta&#xff1a;也是测试版&#xff0c;这个阶段的版本会一直加入新的功能。在 Alpha 版之后推出。RC&#xff1a;(Release Candidate) 顾名思义么 ! 用在软件上就是候选版本。系统平台…

QT信号槽

目录 概念 函数原型 实现 3.1 自带信号→自带槽 3.2 自带信号→自定义槽 3.3 自定义信号 信号槽传参 对应关系 5.1 一对多 5.2 多对一 信号槽的优势 信号槽的注意事项 概念 信号和槽是Qt框架在C语言基础上扩展的一种机制&#xff0c;用于对象之间的通信。这一机制类…