MySQL关于EXPLAIN进行Sql优化命令详解

文章目录

• • 前言
  • 基本用法
  • EXPLAIN 输出列解析
  • 关键指标详解
  • • 1. type（访问类型，按性能从优到劣排序）
    • 2. Extra（重要附加信息）
  • 实际使用示例
  • • 示例1：分析简单查询
    • 示例2：分析您的订单查询
  • 高级用法
  • • 1. EXPLAIN ANALYZE (MySQL 8.0+)
    • 2. 格式化输出
    • 3. 查看警告
  • 优化建议

前言

EXPLAIN 是 MySQL 中用于分析查询执行计划的关键工具，它能帮助开发者理解 MySQL 如何执行 SQL 查询，并据此进行性能优化。

基本用法

EXPLAIN SELECT * FROM table WHERE condition;

EXPLAIN 输出列解析

列名 说明 id 查询标识符，相同数字表示同一查询部分 select_type 查询类型（简单查询、子查询等） table 访问的表名 partitions 匹配的分区 type 访问类型（性能关键指标） possible_keys 可能使用的索引 key 实际使用的索引 key_len 使用的索引长度 ref 索引与哪些列或常量比较 rows 估计要检查的行数 filtered 表条件过滤的行百分比 Extra 额外信息（重要优化提示）

关键指标详解

1. type（访问类型，按性能从优到劣排序）

• system：表只有一行数据
• const：通过主键或唯一索引一次找到
• eq_ref：关联查询中主键或唯一索引关联
• ref：非唯一索引查找
• range：索引范围扫描
• index：全索引扫描
• ALL：全表扫描（最差，需优化）

2. Extra（重要附加信息）

• Using index：使用覆盖索引（性能好）
• Using where：服务器在存储引擎检索后过滤
• Using temporary：使用临时表（需优化）
• Using filesort：额外排序（需优化）
• Using join buffer：使用连接缓冲

实际使用示例

示例1：分析简单查询

EXPLAIN SELECT * FROM users WHERE id = 1;

可能结果：

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | users | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

示例2：分析您的订单查询

EXPLAIN SELECT gid, role_id, SUM(ABS(money)) AS sum_money FROM `guild_pay`.`order` WHERE `status` = 1 GROUP BY `role_id`, `gid`;

高级用法

1. EXPLAIN ANALYZE (MySQL 8.0+)

EXPLAIN ANALYZE SELECT * FROM table WHERE condition;

提供实际执行时间等更详细信息

2. 格式化输出

EXPLAIN FORMAT=JSON SELECT * FROM table;

提供JSON格式的详细执行计划

3. 查看警告

EXPLAIN EXTENDED SELECT * FROM table;SHOW WARNINGS;

优化建议

1. 关注 type 列，尽量避免 ALL（全表扫描）
2. 确保查询使用了合适的索引（key 列）
3. 注意 Using filesort 和 Using temporary 警告
4. 比较不同索引方案下的 EXPLAIN 结果
5. 对于复杂查询，使用 EXPLAIN FORMAT=JSON 获取更多细节

通过 EXPLAIN 分析，您可以确定查询是否有效使用了索引，是否存在性能瓶颈，以及如何优化表结构和查询语句。