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

server/2024/12/19 17:37:45/

在日常的数据库开发中,我们经常需要分析 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/server/151495.html

相关文章

vue计时器

实现一个倒计时功能用于下单后的计时 代码 倒计时组件 import { ref, onUnmounted } from vue import { computed } from vue import dayjs from dayjsexport const useCountDown () > {// 响应式数据let timer nullconst time ref(0)// 格式化为时分秒const formatTi…

Go 错误处理

Go 错误处理 Go 语言在设计时考虑了错误处理的重要性,提供了一套简洁而强大的错误处理机制。本文将深入探讨 Go 中的错误处理方式,包括错误类型的定义、错误处理的基本模式、以及最佳实践。 错误类型定义 在 Go 中,错误是一个接口&#xf…

C#多线程

C#中的多线程编程是开发高效并发应用程序的关键技术之一,它允许程序同时执行多个任务,从而提升应用程序的响应速度和性能。为了更好地理解C#中的多线程使用和定义,我们可以从以下几个方面来探讨:线程的基本概念、创建线程的方法、…

之前使用vue-element-admin框架开发的项目无法启动,可能是这个原因

最近运行之前的项目,发现无法正常启动,可能有以下几种情况: 一、版本问题 报错: this[kHandle] new _Hash(algorithm, xofLen); Error: error:0308010C:digital 因为在 node V17 版本发布了 OpenSSL3.0 对算法…

单例模式的简单应用

单例模式主要是为了确保只有单个对象被创建,主要解决一个类的对象频繁地创建与销毁 我们通过如下示例来了解单例模式的作用,以及实现方案 如上图,我们只要点击一次"普通模式"的菜单,即会创建一个新的窗体对象。 而我们…

C++ 的衰退复制(decay-copy)

目录 1.什么是衰退复制(decay-copy) 1.1.推导规则 1.2.LWG issue 929 1.3.想象中的 decay_copy 2.decay-copy 与 auto 2.1.为什么引入衰退复制 2.2. 成为 C 23 的语言特性 3.应用场景 4.总结 1.什么是衰退复制(decay-copy&#xff0…

如何理解OSI七层模型?从是什么、如何划分、传输过程是什么?

目录 OSI七层模型概述 1.1 什么是OSI七层模型1.2 OSI七层模型的七个层级1.3 OSI七层模型的作用OSI七层模型的具体划分 2.1 应用层(Application Layer)2.2 表示层(Presentation Layer)2.3 会话层(Session Layer)2.4 传输层(Transport Layer)2.5 网络层(Network Layer)…

【操作系统】每日 3 题(七十一)

✍个人博客:https://blog.csdn.net/Newin2020?typeblog 📣专栏地址:https://blog.csdn.net/newin2020/category_12820365.html 📚专栏简介:在这个专栏中,我将会分享操作系统面试中常见的面试题给大家~ ❤️…