开发者

Mysql查看执行计划、explain关键字超详细讲解

目录
  • 一、什么是explain
  • 二、explain基础用法
    • 1. 基本语法
    • 2. 常用参数
    • 3. 输出示例
  • 三、执行计划字段深度解析
    • 3.1、id 列
    • 3.2、select_type 类型
    • 3.3、type 访问类型(性能关键指标)
    • 3.4、key_len 计算规则
    • 3.5、Extra 重要信息
  • 四、实战案例分析
    • 五、高级技巧与最佳实践
      • 5.1、jsON格式输出分析
      • 5.2、执行计划可视化工具
      • 5.3、优化器提示
      • 5.4、统计信息管理
    • 六、常见误区与注意事项
      • 七、EXPLAIN 执行计划优化路线图

        一、什么是explain

        EXPLAIN 是 mysql 提供的用于分析 SQL 查询执行计划的工具,通过该命令可以获取查询优化器选择的执行路径。

        总结: explain可以查看sql执行时是否有使用到索引,关联查询时可以查看sql的执行顺序。

        Explain被称为执行计划,在语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,模拟MySQL优化器来执行SQL语句,执行查询时,会返回执行计划的信息,并不执行这条SQL。(注意,如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中)。

        在 MySQL 8.0 版本后,新增 EXPLAIN ANALYZE 功能,可提供实际执行统计信息。

        二、explain基础用法

        1. 基本语法

        EXPLAIN [FORMAT = {TRADITIONAL|JSON|TREE}] SELECT ...;

        解释:explain+sql语句,执行后就可以查看该sql的执行顺序,是否使用索引等信息。

        2. 常用参数

        • FORMAT:指定输出格式(默认传统表格)
        • PARTITIONS:显示分区信息
        • ANALYZE:实际执行并收集统计(8.0+)

        3. 输出示例

        EXPLAIN SELECT * FROM employees WHERE last_name = 'tom';

        输出结果示例:

        +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

        | id | select_type | table     | partiti编程ons | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

        +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----编程------+-------------+

        |  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3000 |    10.00 | Using where |

        +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

        典型输出包含以下核心列:

        列名说明关键值
        id查询序列号相同id按顺序执行,不同id从大到小执行
        select_type查询类型SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION
        table访问的表名<unionM,N> 表示联合查询结果
        partitions匹配的分区分区表可见具体分区
        type访问类型system > const > eq_ref > ref > range > index > ALL
        possible_keys可能使用的索引显示候选索引列表
        key实际使用的索引NULL 表示未使用索引
        key_len索引长度复合索引实际使用部分长度
        ref索引比较的列const/库名.表名.列名
        rows预估扫描行数基于统计信息估算
        filtered过滤百分比100% 表示完全匹配索引
        Extra附加信息Using filesort/Using temporary/Using index

        三、执行计划字段深度解析

        3.1、id 列

        • 查询执行顺序标识
        • 相同 id 表示同级别执行
        • 数字越大优先级越高
        • NULL 表示结果集合并

        3.2、select_type 类型

        类型描述
        SIMPLE简单查询(无子查询或UNION)
        PRIMARY外层查询
        SUBQUERY子查询中的第一个SELECT
        DERIVED派生表(FROM子句中的子查询)
        UNIONUNION中的第二个或之后的SELECT
        UNION RESULTUNION的结果

        3.3、type 访问类型(性能关键指标)

        按性能从优到劣排序:

        system > const > eq_ref > ref > range > index > ALL

        类型扫描方式出现场景
        system系统表单行记录MyISAM引擎统计表
        const主键/唯一索引等值查询WHERE id = 1
        eq_ref唯一索引关联查询JOIN使用主键或唯一索引
        ref非唯一索引查找普通二级索引查询
        range索引范围扫描BETWEEN、IN、> 等范围查询
        index全索引扫描覆盖索引但需扫描全部索引
        ALL全表扫描无可用索引或需要读取大部分数据

        1. type 访问类型详解(性能从优到劣)

        • system:系统表单行访问(内存表)
        • const:主键或唯一索引等值查询
           EXPLAIN SELECT * FROM users WHERE id = 1;
        • eq_ref:关联查询主键匹配
           EXPLAIN SELECT * FROM orders 
           JOIN users ON orders.user_id = users.id;
        • ref:非唯一索引等值查询
           CREATE INDEX idx_age ON users(age);
           EXPLAIN SELECT * FROM users WHERE age = 30;
        • range:索引范围扫描
           EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
        • index:全索引扫描
           EXPLAIN SELECT COUNT(*) FROM users USE INDEX(idx_age);
        • ALL:全表扫描(需重点优化)

        3.4、key_len 计算规则

        索引使用长度的计算方法:

        key_len = 
          (字符列长度 * 字符集bytes) + 
          (是否NULL? 1:0) + 
          (变长类型? 2:0)

        示例:VARCHAR(255) utf8mb4 可为NULL列

        (255*4) + 1 + 2 = 1023 bytes

        3.5、Extra 重要信息

        含义
        Using index覆盖索引扫描(无需回表)
        Using where存储引擎返回数据后在Server层过滤
        Using temporary使用临时表(常见于GROUP BY/ORDER BY)
        Using filesort额外排序操作(需优化索引或调整排序方式)
        Using index condition索引条件下推(ICP优化)
        Select tables optimized away通过索引直接获取统计信息(如MIN/MAX)

        四、实战案例分析

        案例1:索引失效分析

        EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023;

        输出结果:

        type: ALL

        key: NULL

        Extra: Using where

        问题诊断:对列使用函数导致索js引失效

        优化方案:

        ALTER TABLE orders ADD INDEX idx_order_date (order_date);
        SELECT * FROM orders 
        WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

        案例2:连接查询优化

        EXPLAIN 
        SELECT e.name, d.department_name 
        FROM employees e
        JOIN departments d ON e.dept_id = d.id;

        输出显示:

        +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

        | id | select_type |www.devze.com table | type   | possible_keys | key     | key_len | ref          | rows | Extra       |

        +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

        | 1  | SIMPLE      | e     | ALL    | dept_id       | NULL    | NULL    | NULL         | 1000 |             |

        | 1  | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | company.e.dept_id | 1   | Using index |

        +----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

        优化建议:为 employees 表的 dept_id 字段添加索引

        五、高级技巧与最佳实践

        5.1、JSON格式输出分析

        EXPLAIN FORMAT=JSON SELECT ...;

        可获取更详细的成本估算信息:

        {
          "query_block": {
            "cost_info": {
              "query_cost": "1.20"
            },
            "table": {
              "Access_type": "range",
              "rows_examined_per_scan": 500,
              "rows_produced_per_join": 500,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.50",
                "eval_cost": "0.70",
                "prefix_cost": "1.20"
              }
            }
          }
        }

        5.2、执行计划可视化工具

        推荐使用:

        • MySQL Workbench Visual Explain
        • Percona Toolkit 的 pt-visual-explain
        • 在线工具:https://explain.dalibo.com/

        5.3、优化器提示

        强制使用指定索引:

        SELECT * FROM table USE INDEX (index_name) ...

        5.4、统计信息管理

        ANALYZE TABLE table_name;  -- 更新统计信息
        SHOW INDEX编程客栈 FROM table_name; -- 查看索引基数

        六、常见误区与注意事项

        • rows 列是估算值,实际值可能偏差较大
        • 索引覆盖不代表高效,需结合扫描行数判断
        • 并非所有 Using filesort 都需要优化,小数据量排序是正常现象
        • 强制索引可能适得其反,需结合数据分布考虑
        • 连接顺序不一定按书写顺序,优化器会自动选择最佳顺序

        七、EXPLAIN 执行计划优化路线图

        • 检查 type 列是否达到 range 级别以上
        • 确认 possible_keys 和 key 是否合理
        • 分析 key_len 是否充分利用索引
        • 检查 rows 估算值是否过大
        • 查看 Extra 列是否有警告信息
        • 验证 filtered 百分比是否过低
        • 对比优化前后的执行计划差异

        到此这篇关于Mysql查看执行计划、explain关键字详解(超详细) 的文章就介绍到这了,更多相关mysql查看执行计划内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)!

        0

        上一篇:

        下一篇:

        精彩评论

        暂无评论...
        验证码 换一张
        取 消

        最新数据库

        数据库排行榜