印刻数据库

外观

MySQL EXPLAIN与执行计划分析 ​

EXPLAIN命令基础 ​

语法 ​

sql
-- 基本语法
EXPLAIN SELECT ...;

-- 也可以用于其他语句
EXPLAIN INSERT ...;
EXPLAIN UPDATE ...;
EXPLAIN DELETE ...;

-- 显示格式化的执行计划
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN FORMAT=TREE SELECT ...;  -- MySQL 8.0+

输出格式 ​

  • 传统格式：表格形式展示
  • JSON格式：详细的JSON结构
  • TREE格式：树状结构，更直观

使用场景 ​

  • 分析SQL语句的执行计划
  • 识别查询瓶颈
  • 验证索引使用情况
  • 优化查询语句

执行计划列详解 ​

id ​

  • 查询的标识符
  • 数字越大，执行优先级越高
  • 相同id，执行顺序从上到下
  • 不同id，执行顺序从大到小

select_type ​

  • SIMPLE：简单查询，无子查询或UNION
  • PRIMARY：主查询
  • SUBQUERY：子查询
  • DERIVED：派生表
  • UNION：UNION中的第二个或后续查询
  • UNION RESULT：UNION的结果

table ​

  • 查询的表名
  • <derivedN>：派生表
  • <unionM,N>：UNION结果

partitions ​

  • 匹配的分区
  • 对于非分区表，显示NULL

type ​

  • 访问类型，从优到劣：
    • system：表只有一行
    • const：使用主键或唯一索引等值查询
    • eq_ref：连接查询中，被连接表使用主键或唯一索引
    • ref：使用非唯一索引或唯一索引前缀
    • fulltext：使用全文索引
    • ref_or_null：类似于ref，但是包含NULL值
    • index_merge：使用索引合并优化
    • unique_subquery：子查询中使用唯一索引
    • index_subquery：子查询中使用非唯一索引
    • range：范围查询
    • index：全索引扫描
    • ALL：全表扫描

possible_keys ​

  • 可能使用的索引
  • 多个索引用逗号分隔
  • 显示NULL表示没有可能的索引

key ​

  • 实际使用的索引
  • 显示NULL表示没有使用索引
  • 使用覆盖索引时，会显示相应的索引

key_len ​

  • 使用的索引长度
  • 越短越好
  • 计算规则：
    • 字符串：CHAR(n) = n, VARCHAR(n) = n + 1(或2)
    • 数值：TINYINT = 1, SMALLINT = 2, INT = 4, BIGINT = 8
    • 日期：DATE = 3, DATETIME = 8, TIMESTAMP = 4
    • NULL：加1字节

ref ​

  • 与索引比较的列或常量
  • 显示NULL表示没有使用ref

rows ​

  • 估计扫描的行数
  • 不是精确值
  • 越小越好

filtered ​

  • 过滤后的行数百分比
  • 越大越好
  • 表示符合条件的行数占估计行数的比例

Extra ​

  • 额外信息
  • 重要的值：
    • Using index：使用覆盖索引
    • Using where：使用WHERE条件过滤
    • Using temporary：使用临时表
    • Using filesort：使用文件排序
    • Using join buffer：使用连接缓冲区
    • Using index condition：使用索引条件推送
    • Using MRR：使用多范围读取
    • Using index for group-by：使用索引进行分组

执行计划分析技巧 ​

识别全表扫描 ​

  • type 为 ALL
  • key 为 NULL
  • rows 值较大

识别索引使用问题 ​

  • possible_keys 有值但 key 为 NULL
  • key_len 较短，未使用复合索引的全部列
  • type 为 index，表示全索引扫描

识别排序问题 ​

  • Extra 中包含 Using filesort
  • 尝试添加适当的索引避免文件排序

识别临时表问题 ​

  • Extra 中包含 Using temporary
  • 通常与GROUP BY、DISTINCT、ORDER BY有关
  • 尝试优化查询或添加适当的索引

识别连接问题 ​

  • type 为 ALL 或 index 在连接的表上
  • rows 值较大
  • 尝试为连接列添加索引

索引使用分析 ​

覆盖索引 ​

  • Extra 中包含 Using index
  • 优点：
    • 减少I/O操作
    • 避免回表查询
    • 提高查询性能

索引条件推送 ​

  • Extra 中包含 Using index condition
  • MySQL 5.6+ 特性
  • 优点：减少服务器与存储引擎之间的数据传输

索引合并 ​

  • type 为 index_merge
  • 多个索引的合并使用
  • 适用于多个OR条件的查询

多范围读取 ​

  • Extra 中包含 Using MRR
  • 提高范围查询的性能
  • 减少随机I/O

执行计划案例分析 ​

案例1：简单查询 ​

sql
EXPLAIN SELECT * FROM users WHERE id = 1;

预期结果：

  • type: const
  • key: PRIMARY
  • rows: 1

案例2：范围查询 ​

sql
EXPLAIN SELECT * FROM users WHERE age BETWEEN 18 AND 30;

预期结果：

  • type: range
  • key: idx_age (如果有年龄索引)
  • rows: 估计的行数

案例3：连接查询 ​

sql
EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id WHERE users.name = 'John';

预期结果：

  • users表：type为ref，使用name索引
  • orders表：type为ref或eq_ref，使用user_id索引

案例4：复杂查询 ​

sql
EXPLAIN SELECT 
    u.name, 
    COUNT(o.id) as order_count 
FROM 
    users u 
LEFT JOIN 
    orders o ON u.id = o.user_id 
WHERE 
    u.age > 20 
GROUP BY 
    u.id 
ORDER BY 
    order_count DESC 
LIMIT 10;

分析：

  • 检查是否使用了适当的索引
  • 注意是否有Using temporary或Using filesort
  • 估计的行数是否合理

JSON格式执行计划 ​

优势 ​

  • 提供更详细的信息
  • 包含成本估算
  • 支持更复杂的分析

示例 ​

sql
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

关键字段 ​

  • query_block：查询块信息
  • cost_info：成本信息
  • table：表信息
  • access_type：访问类型
  • possible_keys：可能的索引
  • key：实际使用的索引
  • key_length：索引长度
  • ref：引用的列
  • rows_examined_per_scan：估计扫描的行数
  • rows_produced_per_join：估计连接产生的行数
  • filtered：过滤比例
  • using_index：是否使用索引

TREE格式执行计划 ​

优势 ​

  • 更直观的树状结构
  • 显示操作的层次关系
  • 更易于理解复杂查询

示例 ​

sql
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE id = 1;

结构 ​

  • 根节点：整个查询
  • 子节点：各个操作
  • 显示操作的顺序和依赖关系

执行计划优化建议 ​

优化全表扫描 ​

  • 添加适当的索引
  • 优化WHERE条件
  • 考虑分区表

优化索引使用 ​

  • 创建合适的复合索引
  • 避免索引失效的情况：
    • 使用函数操作索引列
    • 使用不等于操作符
    • 使用IS NULL或IS NOT NULL
    • 使用LIKE '%xxx'
    • 使用OR连接条件
    • 类型转换

优化排序和分组 ​

  • 添加适当的索引支持排序
  • 避免使用SELECT *，只选择需要的列
  • 考虑使用覆盖索引

优化连接查询 ​

  • 为连接列添加索引
  • 小表驱动大表
  • 考虑使用STRAIGHT_JOIN指定连接顺序

优化子查询 ​

  • 考虑使用JOIN替代子查询
  • 避免相关子查询
  • 考虑使用临时表

工具与辅助 ​

Performance Schema ​

  • 收集执行计划的实际执行数据
  • 与EXPLAIN的估计值对比

Sys Schema ​

  • 提供更友好的视图
  • 简化性能分析

第三方工具 ​

  • pt-visual-explain：将执行计划可视化
  • MySQL Workbench：图形化执行计划分析
  • phpMyAdmin：Web界面查看执行计划

脚本自动化 ​

  • 编写脚本自动分析慢查询日志中的执行计划
  • 定期生成执行计划报告
  • 监控执行计划的变化

常见问题（FAQ） ​

Q1: EXPLAIN显示的行数是精确值吗？ ​

A1: 不是，EXPLAIN显示的rows值是MySQL基于统计信息估算的行数，不是实际执行时的精确行数。统计信息可能会过时，导致估算不准确。可以使用ANALYZE TABLE命令更新表的统计信息，提高估算的准确性。

Q2: 为什么EXPLAIN显示可能使用的索引，但实际没有使用？ ​

A2: 可能的原因：

  • MySQL认为全表扫描比使用索引更快（例如表很小）
  • 索引选择性差，使用索引的成本高于全表扫描
  • 统计信息不准确，导致MySQL做出错误的选择
  • 查询条件不符合索引使用的要求

Q3: 如何优化EXPLAIN中显示的Using temporary和Using filesort？ ​

A3: 优化方法：

  • 添加适当的索引，让排序和分组操作使用索引
  • 调整ORDER BY和GROUP BY的顺序，使其与索引顺序一致
  • 减少SELECT的列数，使用覆盖索引
  • 对于复杂查询，考虑拆分为多个简单查询

Q4: 如何理解EXPLAIN中的key_len字段？ ​

A4: key_len表示使用的索引长度，单位是字节。它反映了索引的使用情况：

  • 对于复合索引，key_len越大，表示使用的索引列越多
  • 可以通过key_len判断是否使用了复合索引的全部列
  • key_len的计算考虑了列类型、长度和是否允许NULL

Q5: MySQL 8.0的执行计划有哪些新特性？ ​

A5: MySQL 8.0的执行计划新特性：

  • 新增TREE格式，提供更直观的树状结构
  • JSON格式更加详细，包含更多执行信息
  • 支持EXPLAIN ANALYZE，执行查询并显示实际执行计划
  • 改进了索引使用的分析能力
  • 提供更多关于查询优化的信息