IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓ 		图片批量下载器
↓批量下载图片,美女图库↓ 		图片自动播放器
↓图片自动播放器↓ 		一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> MySQL Explain详解 -> 正文阅读

[大数据]MySQL Explain详解

最近代码review时，因一个SQL语句的性能问题和同学产生争论。程序员的优点是大家都认事实，用explain查看结果，轻松解决争议。

原因

之所以想写explain，有以下几个原因：

  1. 最近看《MySQL45讲》里面有很多场景都用explain解释

  2. 前些日子因一个SQL产生争议，使用explain进行验证

  3. 网上很多资料，只讲explain各个字段的含义，却没有对应实例，大家感受不深

  4. 对explain虽有一些了解，但没有特别深入

基于这些原因，整理一下explain各字段含义。

表结构和数据

表结构

数据库中创建两张表trace_sp_info和trace_spinfo2，两个表结构完成一致：

mysql> show create table trace_sp_info;

CREATE TABLE `trace_sp_info` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sp_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '服务商id',
  `sp_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务商名称',
  `type` tinyint DEFAULT '0' COMMENT '服务商类型：0供应链服务商 1审核服务商 2溯源码供应商',
  `type_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务商类型名称',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 0未激活 1激活 2失效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
  `update_by` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_spid` (`sp_id`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='服务商类型';

数据

mysql> select * from trace_sp_info;

图片

mysql> select * from trace_sp_info2;

图片

MySQL版本

mysql> select version();

图片

字段详解

mysql> explain select * from trace_sp_info;

图片

通过explain结果，可以看到有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra字段。

id

含义

代表SQL语句的执行顺序

  1. id如果相同，可以认为是同一组，从上往下顺序执行

  2. id值越大，优先级越高，越先执行；对于子查询，id的序号会递增

实例

同id

mysql> explain select * from trace_sp_info2 where id in (select id from trace_sp_info where id = 2);

图片

不同id

mysql> explain select * from trace_sp_info2 where id = (select id from trace_sp_info where id = 2);

图片

select_type

含义

select查询的类型，主要用以区别普通查询和联合查询、子查询之类的复杂查询。有如下类型：

  1. SIMPLE：简单SELECT，不使用UNION或子查询等

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

  3. UNION：UNION中的第二个或后面的SELECT语句

  4. DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

  5. UNION RESULT：UNION的结果，union语句中第二个select开始后面所有select

  6. SUBQUERY：子查询中的第一个SELECT，结果不依赖于外部查询

  7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询

  8. DERIVED：派生表的SELECT, FROM子句的子查询

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

工作中常看到SIMPLE、PRIMARY、SUBQUERY。

实例

SIMPLE

mysql> explain select * from trace_sp_info;

图片

PRIMARY+SUBQUERY

mysql> explain select * from trace_sp_info2 where id = (select id from trace_sp_info where id = 2);

图片

table

含义

表示这一步SQL所访问的表名称，即这一行的数据来源于哪张表。有时不是真实的表名字，可能是别名，也可能是第几步执行的结果的简称

实例

别名

mysql> explain select * from trace_sp_info s1, trace_sp_info2 s2 where s1.id=s2.id;

图片

partitions

含义

如果查询是基于分区表的话，会显示查询将访问的分区

type

含义

对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。通过type可以快速查看SQL性能。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

  1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

  2. index：Full Index Scan，index与ALL区别为index类型只遍历索引树

  3. range：只检索给定范围的行，使用一个索引来选择行

  4. ref：表示表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，查指定值，而不是范围

  5. eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

  6. const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

  7. NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

实例

ALL

mysql> explain select * from trace_sp_info;

这种需求只能遍历全表

图片

index

mysql> explain select id from trace_sp_info;

只需遍历非聚簇索引idx_update_time，直接获取到id，不用回表，实现了覆盖索引的功能。

从这里也能推断出，如果MySQL选择不同索引树，输出的id顺序可能不一致。

图片

range

mysql> explain select * from trace_sp_info where id >1;

优化器利用主键索引，实现树搜索，能够快速定位到id>1的位置，然后从大于1的位置开始遍历

图片

ref

mysql> explain select * from trace_sp_info where update_time =‘2021-08-31 18:03:5’;

图片

eq_ref

mysql> explain select * from trace_sp_info s1,trace_sp_info2 s2 where s1.sp_id=s2.sp_id and s1.sp_id >=1;

一般是primary key或者 unique key作为关联条件

图片

const

mysql> explain select * from trace_sp_info where id=1;

图片

NULL

mysql> explain select max(id) from trace_sp_info;

图片

possible_keys

含义

显示可能应用的索引，一般一个或者多个。

SQL语句涉及到的字段上若存在索引，则该索引被列出，但是不一定被实际使用。

如果该列是NULL，则没有相关的索引。

实例

mysql> explain select id from trace_sp_info where id >1;

图片

key

含义

key列显示MySQL实际决定使用的键（索引），大概率包含在possible_keys中。

key的值不一定是possible-keys的子集。SQL语句中的字段，若没有涉及到索引，MySQL选择覆盖索引，则该索引仅出现在key列表中。

如果没有选择索引，键是NULL。

要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

实例

覆盖索引

mysql> explain select id from trace_sp_info;

图片

force index

使用force index可以解决MySQL选择索引错误的问题。

mysql> explain select id from trace_sp_info where id >1;

图片

mysql> explain select id from trace_sp_info force index (idx_update_time) where id >1;

图片

key_len

含义

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

key-len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

实例

mysql> explain select * from trace_sp_info force index (id) where update_time>0;

图片

mysql> explain select * from trace_sp_info where id=1;

图片

ref

含义

显示索引的哪一列被使用了。

有时候会是一个常量，表示哪些列或常量被用于查找索引列上的值。

实例

const

mysql> explain select * from trace_sp_info where update_time =‘2021-08-31 18:03:5’;

图片

指定列

mysql> explain select * from trace_sp_info s1,trace_sp_info2 s2 where s1.sp_id=s2.sp_id and s1.sp_id >=1;

表明trace_sp_info的sp_id列的值被用于查找trace_sp_info2上sp_id对应的值。

图片

rows

含义

估算结果集行数，表示MySQL根据表统计信息及索引选用情况，估算找到所需记录需要读取的行数。

表统计信息中，有一个名词为基数，表示一个索引上不同的值的个数。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

实例

mysql> explain select * from trace_sp_info;

图片

filtered

含义

filtered表示返回结果的行数占需读取行数的百分比，filtered列的值越大越好，也是估算值

实例

100%

mysql> explain select * from trace_sp_info;

图片

50%

mysql> explain select * from trace_sp_info where status=0;

图片

Extra

含义

该列包含MySQL解决查询的详细信息，如是否使用文件等，主要有以下几种情况：

  1. Using where：使用where过滤条件

  2. Using temporary（十死无生的提示，极大影响mysql性能，需要尽快优化）：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by。group by一定要遵循所建索引的顺序。

  3. Using filesort（九死一生的提示，需要尽快优化）：当Query中包含 order by 操作，而且无法利用索引完成

    的排序操作称为“文件排序”。排序的时候最好遵循所建索引的顺序否则就可能会出现Using filesort。

  4. Using Index(效率不错)：

    表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错

    如果同时出现Using Where，表明索引被用来执行索引键值的查找

    如果没有同时出现Using Where，表明索引用来读取数据而非执行查找工作

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

  6. Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

  7. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

  8. No tables used：Query语句中使用from dual 或不含任何from子句

其中Using filesort，Using temporary，Using index最为常见，出现前两种表示是需要优化的地方，出现第三种表示索引效率不错。

实例

Using where

mysql> explain select * from trace_sp_info where type=0;

图片

Using filesort

mysql> explain select * from trace_sp_info order by type ;

图片

Using temporary

mysql> explain select count(*),type from trace_sp_info group by type;

图片

mysql> explain select count(*) from trace_sp_info group by update_time;

图片

Using index

mysql> explain select id from trace_sp_info where update_time =‘2021-08-31 18:03:5’;

图片

Using where+Using index

mysql> explain select id from trace_sp_info where update_time > 0;

图片

总结

虽然explain有很多字段，但大家可以重点关注type、rows和Extra。通过改变索引或者改变SQL，让性能更好。

今后写SQL的时候，都可以用explain检查一下，既能查看性能，也能检查是否与自己想法一致。

资料

  1. MySQL Explain详解

  2. 004–Explain实战详解：id分析

  3. explain执行计划详解

  4. https://blog.csdn.net/weixin_42288943/article/month/2017/08

  5. MySQLexplain之Extra介绍

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/

图片

往期文章回顾：

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言
  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技：还在苦于ETL？Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch：如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2021-09-13 09:20:26  更:2021-09-13 09:21:21 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 -2024/11/23 19:52:20-

图片自动播放器
↓图片自动播放器↓ 		TxT小说阅读器
↓语音阅读,小说下载,古典文学↓ 		一键清除垃圾
↓轻轻一点,清除系统垃圾↓ 		图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码