精 灵 王


  • 首页

  • 文章归档

  • 所有分类

  • 关于我

  • 搜索
设计模式之美 分布式 Redis 并发编程 个人成长 周志明的软件架构课 架构 单元测试 LeetCode 工具 位运算 读书笔记 操作系统 MySQL 异步编程 技术方案设计 集合 设计模式 三亚 游玩 转载 Linux 观察者模式 事件 Spring SpringCloud 实战 实战,SpringCloud 源码分析 线程池 同步 锁 线程 线程模型 动态代理 字节码 类加载 垃圾收集器 垃圾回收算法 对象创建 虚拟机内存 内存结构 Java

MySQL explain命令分析

发表于 2021-04-15 | 分类于 数据库 | 0 | 阅读次数 204

explain关键字可以模拟MySQL优化器执行SQL语句,可以很好的分析SQL语句或表结构的性能瓶颈。

explain执行效果

mysql> explain SELECT * FROM `db_goods`.`t_shopcart`;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t_shopcart | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 69193 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set (0.04 sec)
  1. id
    select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
  2. select_type
    查询类型
  3. table
    正在访问哪个表
  4. partitions
    匹配的分区
  5. type
    访问的类型
  6. possible_keys
    显示可能应用在这张表中的索引,一个或多个,但不一定实际使用到
  7. key
    实际使用到的索引,如果为NULL,则没有使用索引
  8. key_len
    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
  9. ref
    显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值
  10. rows
    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数
  11. filtered
    查询的表行占表的百分比
  12. Extra
    包含不适合在其它列中显示但十分重要的额外信息

字段详解

id字段

一组数字,表示查询中执行select子句或操作表的顺序。如果有多行时,id值相同,执行顺序从上至下顺序执行;如果值不同,id的值越大优先级越高,越先被执行。

select_type字段

查询中每个select子句的类型;

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

table字段

查询访问的表

partitions字段

匹配的分区

type字段(重要)

表示MySQL在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(8种,从左到右,性能从差到好)

①ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

mysql> explain SELECT * FROM `db_goods`.`t_shopcart`;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t_shopcart | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 69193 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set (0.04 sec)

②index:Full Index Scan,index与ALL区别为index类型只遍历索引树,主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索引。

③range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range

mysql>  explain SELECT * FROM `db_goods`.`t_activity` where activity_Id < 11;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_activity | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)

④ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,属于查找和扫描的混合体。

⑤eq_ref:最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)

⑥const:当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。当主键放入where子句时,mysql把这个查询转为一个常量(高效)

mysql>  explain SELECT * FROM `db_goods`.`t_activity` where activity_Id = 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_activity | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.06 sec)

⑦system:表只有一行记录(等于系统表),这是const类型的特列,平时不大会出现,可以忽略

⑧NULL:MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引

mysql>  explain SELECT min(activity_id) FROM `db_goods`.`t_activity` ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set (0.06 sec)

possible_keys字段

显示可能应用在这张表中的索引,一个或多个,但不一定实际使用到

key字段(重要)

实际使用到的索引,如果为NULL,则没有使用索引

key_len字段

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好

ref字段

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows字段

大致估算出找到所需的记录所需读取的行数

filtered字段

查询的表行占表的百分比

Extra字段(重要)

不适合在其它列中显示但十分重要的额外信息。有以下几种情况:

①Using where:使用了where条件。

②Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序order by 和分组查询group by;

③Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”。

④Using join buffer:该值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

⑤Impossible where:这个值强调了where语句会导致没有符合条件的行,where子句的值总是false,不能用来获取任何元组。

⑥Select tables optimized away:SELECT操作已经优化到不能再优化了(MySQL根本没有遍历表或索引就返回数据了)

⑦Using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!

⑧distinct:一旦mysql找到了与行相联合匹配的行,就不再搜索了

精 灵 王 wechat
👆🏼欢迎扫码关注微信公众号👆🏼
  • 本文作者: 精 灵 王
  • 本文链接: https://jinglingwang.cn/archives/mysql-explain
  • 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!
# 设计模式之美 # 分布式 # Redis # 并发编程 # 个人成长 # 周志明的软件架构课 # 架构 # 单元测试 # LeetCode # 工具 # 位运算 # 读书笔记 # 操作系统 # MySQL # 异步编程 # 技术方案设计 # 集合 # 设计模式 # 三亚 # 游玩 # 转载 # Linux # 观察者模式 # 事件 # Spring # SpringCloud # 实战 # 实战,SpringCloud # 源码分析 # 线程池 # 同步 # 锁 # 线程 # 线程模型 # 动态代理 # 字节码 # 类加载 # 垃圾收集器 # 垃圾回收算法 # 对象创建 # 虚拟机内存 # 内存结构 # Java
我在架构设计和代码开发中的一些常用原则
(一)操作系统-概述
  • 文章目录
  • 站点概览
精 灵 王

精 灵 王

青春岁月,以此为伴

85 日志
14 分类
43 标签
RSS
E-mail
Creative Commons
Links
  • 添加友链说明
© 2022 精 灵 王
渝ICP备2020013371号
0%