深入解析MySQL的EXPLAIN:指标详解与索引优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
全局流量管理 GTM,标准版 1个月
简介: MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。

MySQL 中的 EXPLAIN 语句是一个强大的工具,用于分析和优化 SQL 查询。通过 EXPLAIN,你可以了解 MySQL 查询优化器是如何执行你的查询的,以及是否有可以改进的地方。本文将详细讲解 EXPLAIN 输出的各项指标,并说明如何利用这些指标来优化索引结构和 SQL 语句。

一、EXPLAIN 的基本使用

EXPLAIN 语句用于分析 SELECT 语句的执行计划。当你在 SQL 语句前加上 EXPLAIN 关键字时,MySQL 会返回一张表格,显示查询的执行计划。执行计划揭示了 MySQL 如何访问表、如何使用索引,以及执行查询的顺序。

ini

代码解读

复制代码

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

二、EXPLAIN 输出的关键指标

EXPLAIN 输出通常包含以下几个重要的列,每一列都代表一个关键的指标,用于解读查询的执行方式。

2.1 id

id 列表示查询中每个子查询或表的执行顺序。通常,id 值越大,执行的优先级越高。如果两个 id 相同,则意味着它们可以并行执行。

  • id 相同:表示可以并行执行的部分查询。
  • id 不同:表示执行顺序,id 越大,优先级越高。

2.2 select_type

select_type 列表示查询的类型,它描述了查询中每个 SELECT 子句的性质。常见的 select_type 值包括:

  • SIMPLE:简单查询,不包含子查询或联合查询。
  • PRIMARY:主查询,是最外层的查询。
  • SUBQUERY:子查询,出现在 SELECTWHERE 子句中。
  • UNION:联合查询的一部分。
  • DEPENDENT SUBQUERY:依赖于外部查询的子查询。
  • DERIVED:派生表,即子查询中的临时表。

2.3 table

table 列表示查询中访问的表的名称或别名。如果查询涉及多个表,EXPLAIN 会显示它们的连接顺序。

2.4 type

type 列表示查询中表的访问方式,也称为访问类型。访问类型的性能从好到坏依次为:

  • system:表只有一行数据(系统表)。
  • const:查询某一特定行,性能最好,通常用于主键或唯一索引的查询。
  • eq_ref:对每个前驱记录,查询引擎从后继表中读取最多一条记录,通常用于主键或唯一索引连接。
  • ref:对前驱表的每个记录,查询引擎可能会找到多条匹配的行,通常用于非唯一索引连接。
  • range:通过索引范围扫描查找一部分行。
  • index:全索引扫描,即按索引顺序遍历整个索引,但不扫描表。
  • ALL:全表扫描,性能最差。

优化建议:目标是尽量避免 ALLindex 类型的访问方式,尽量使用 consteq_refrefrange 类型的访问方式。

2.5 possible_keys

possible_keys 列显示查询中可能使用到的索引。这里列出的索引并不一定在查询中实际使用,但它们都是 MySQL 优化器可能考虑的索引。

优化建议:确保查询涉及的列上有合适的索引。如果 possible_keys 为空,表示没有可用的索引,这可能会导致全表扫描。

2.6 key

key 列表示查询实际使用的索引。如果该列为空,表示查询未使用索引,而是进行了全表扫描。

优化建议:确保关键查询使用了适当的索引。可以通过修改查询语句或调整索引结构来改善这一点。

2.7 key_len

key_len 列表示 MySQL 使用的索引长度。该值是由索引的定义和查询条件决定的。

优化建议key_len 越小,查询越高效。在组合索引中,确保最常用的查询条件出现在索引的前缀部分,以减少 key_len 的长度。

2.8 ref

ref 列显示索引的哪些列被用于查询条件中。它描述了索引列与查询条件的关系。

优化建议:确保 ref 列的值与查询条件中的字段一致,特别是在多表连接中,以确保索引被正确使用。

2.9 rows

rows 列表示 MySQL 估算需要读取多少行才能找到查询的结果。这个值越小越好,表示查询更加高效。

优化建议:如果 rows 数量过大,说明可能需要重新设计查询或优化索引。

2.10 filtered

filtered 列表示在索引过滤之后,返回的记录占扫描到的总记录数的百分比。filtered 值越高,说明筛选条件越严格,数据过滤越充分。

优化建议:尽量提高 filtered 的比例,减少返回的无效数据。

2.11 Extra

Extra 列包含查询优化器的额外信息。常见的值有:

  • Using where:表示查询使用了 WHERE 过滤条件。
  • Using index:表示查询只使用了索引,不需要回表查询数据。
  • Using filesort:表示查询需要额外的排序操作,这是一个性能瓶颈。
  • Using temporary:表示查询使用了临时表,这是一个性能瓶颈。

优化建议:尽量避免 Using filesortUsing temporary,可以通过调整查询语句、增加索引或优化表结构来消除这些性能瓶颈。

三、使用EXPLAIN进行索引优化

3.1 分析查询计划

通过 EXPLAIN 输出的 typekeyrows 等列,可以分析查询的执行计划。如果发现使用了全表扫描 (ALL),或者 rows 数量过大,说明查询可能有优化空间。

3.2 索引覆盖与优化

  • 覆盖索引:在 EXPLAIN 输出中,如果 Extra 列显示 Using index,说明查询已经使用了覆盖索引,可以避免回表查询,提高查询效率。为了实现覆盖索引,可以在查询中选择包含索引的列,或者在创建索引时覆盖更多查询条件。
  • 组合索引:对于涉及多个列的查询,可以创建组合索引,并确保最常用的查询条件放在组合索引的前缀部分。组合索引的顺序对查询的效率影响很大。

3.3 调整查询语句

  • 简化查询:避免复杂的子查询,尽量将子查询转化为连接查询。在 EXPLAIN 中查看 select_type 是否包含 SUBQUERYDEPENDENT SUBQUERY,如果有,可以考虑重写查询语句。
  • 避免排序与临时表:通过索引排序或提前筛选数据,避免 Using filesortUsing temporary。例如,可以在需要排序的列上建立索引,或者通过限制查询范围来减少排序操作。

3.4 使用分析工具

MySQL 提供了一些工具,可以帮助分析 EXPLAIN 输出并优化查询。例如:

  • ANALYZE TABLE:分析表中的索引统计信息,帮助优化器做出更好的查询计划。
  • OPTIMIZE TABLE:重新整理表数据,减少碎片,提高查询效率。
  • SHOW PROFILE:详细分析查询执行过程,定位性能瓶颈。

四、实战案例:EXPLAIN 优化实例

假设我们有一个电商数据库,包含以下两个表:orderscustomers。我们要优化以下查询:

sql

代码解读

复制代码

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
AND c.customer_name LIKE 'A%';

4.1 初步分析

sql

代码解读

复制代码

EXPLAIN SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
AND c.customer_name LIKE 'A%';

假设 EXPLAIN 输出如下:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE o range order_date order_date 5 NULL 500 100.00 Using where
1 SIMPLE c ALL PRIMARY NULL NULL NULL 10000 10.00 Using where; Using join buffer (Block Nested Loop)

4.2 问题分析

  • orders:使用了 order_date 索引,typerange,表示通过索引范围扫描找到匹配的订单,rows 列显示了需要扫描 500 行。这部分的执行效率相对较好。
  • customers:采用了全表扫描 (ALL),并且使用了连接缓冲区 (join buffer),表明未有效使用索引。rows 列显示了需要扫描 10000 行,这是性能瓶颈所在。

4.3 优化策略

  1. 优化 customers 表的查询
  • 针对 customer_name LIKE 'A%' 的查询条件,考虑创建 customer_name 列的索引。因为 LIKE 查询中使用的前缀是固定字符(A%),因此可以利用索引来加速查询。
  • 创建索引的 SQL 语句:

scss

  • 代码解读
  • 复制代码
CREATE INDEX idx_customer_name ON customers(customer_name);
  1. 重新执行 EXPLAIN 并验证结果
  • 再次使用 EXPLAIN 分析查询语句,确保 customers 表不再使用全表扫描,且 key 列显示为 idx_customer_name
  1. 假设优化后的 EXPLAIN 输出如下:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE o range order_date order_date 5 NULL 500 100.00 Using where
1 SIMPLE c ref idx_customer_name idx_customer_name 62 const 100 100.00 Using where
  1. 经过优化后,customers 表的访问类型变为 ref,并使用了新的索引 idx_customer_name,查询行数大幅减少,性能得到了明显提升。

五、总结

MySQL 的 EXPLAIN 工具提供了一个清晰的窗口,让你可以窥探查询优化器的决策过程。通过详细分析 EXPLAIN 输出的各项指标,你可以识别出性能瓶颈,并采取适当的措施优化索引结构和 SQL 语句。关键在于:

  1. 理解各项指标的含义:了解 typekeyrowsfiltered 等列的作用和表现。
  2. 合理设计索引:根据查询模式优化索引结构,使用覆盖索引、组合索引等技术提高查询效率。
  3. 避免常见的性能陷阱:如全表扫描、排序操作、临时表的使用等,通过优化查询和表结构来消除这些瓶颈。

通过有效利用 EXPLAIN,你可以显著提升 MySQL 数据库的查询性能,为应用程序提供更加流畅的用户体验


转载来源:https://juejin.cn/post/7405152755818283059

相关文章
|
4天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
1天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
1月前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
107 22
 MySQL秘籍之索引与查询优化实战指南
|
16天前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
47 22
|
17天前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
|
20天前
|
存储 关系型数据库 MySQL
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
|
1月前
|
SQL 关系型数据库 MySQL
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
74 16
|
1月前
|
SQL 关系型数据库 MySQL
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
37 7
|
1月前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
114 10
|
21天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
162 0

推荐镜像

更多