专栏首页java相关资料看完这篇文章,99%的人都会使用Mysql Explain工具

看完这篇文章,99%的人都会使用Mysql Explain工具

Explain工具介绍

EXPLAIN是MySQl必不可少的一个分析工具,主要用来测试sql语句的性能及对sql语句的优化,或者说模拟优化器执行SQL语句。

注意:本文基于mysql5.7进行操作,各个版本的mysql使用Explan会有微小的差异

前期掌握点:

什么是mysql的二级索引

mysql中每个表都有一个聚簇索引(clustered index ),除此之外的表上的每个非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。 以InnoDB来说,每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键,该主键索引是聚集索引。如果你不定义为您的表的主键 时,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚集索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

示例表

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (2, '华为电脑');
INSERT INTO `order` VALUES (1, '小米电脑');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '黎明1', 25, '2020-09-09 22:49:08', '2020-09-09 22:49:10');
INSERT INTO `user` VALUES (2, '黎明2', 25, '2020-09-09 22:49:20', '2020-09-09 22:49:22');
INSERT INTO `user` VALUES (3, '黎明3', 25, '2020-09-09 22:49:29', '2020-09-09 22:49:30');

-- ----------------------------
-- Table structure for user_order
-- ----------------------------
DROP TABLE IF EXISTS `user_order`;
CREATE TABLE `user_order`  (
  `id` int(11) NOT NULL,
  `oid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `ramark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_user_order_id`(`oid`, `uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_order
-- ----------------------------
INSERT INTO `user_order` VALUES (1, 1, 1, NULL);
INSERT INTO `user_order` VALUES (2, 1, 2, NULL);
INSERT INTO `user_order` VALUES (3, 2, 1, NULL);

接下来开始对explan结果集列详细解释

EXPLAIN select * from `order` where id = 2

1.id列

id列的编号是 select 的序列号,有几个select 就有几个id,并且id的顺序是按 select 出现的顺序增长的。 id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。

2.select_type列

select_type:表示对应行是简单查询还是复杂查询

  • simple:简单查询。查询不包含子查询和union
  • primary:复杂查询中最外层的 select
  • subquery:包含在 select 中的子查询(不在 from 子句中)
  • derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
  • union:在 union 中的第二个和随后的所有select

下面这个例子主要来了解(simple)查询类型

EXPLAIN select * from `order` where id = 1

下面这个例子主要来了解(primary、subquery、derived)几种类型

set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
explain select (select 1 from user where id = 1) from (select * from `order` where id = 1) ud;

其实在这里我们可以看到id列,id值最大先执行,那么肯定是先执行是派生表查询,接着第二步执行子查询,最后是最外层的select查询

下面这个例子主要来了解(union)查询类型

explain select * from user union all select * from `order`
set session optimizer_switch='derived_merge=on'; #还原默认配置

3.table列

这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时,table列是 <derivenN> 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。 当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的 select 行id。

具体例子可参考,上面第二张图的table列

4.type列

这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。 依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL (system效率最高,All效率最低) 一般来说,得保证查询达到range级别,最好达到ref

NULL: mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。 例如:在索引列中选取最小值,可以单独查找索引树来完成,不需要在执行时访问表(简单来说,表的数据是以b+树格式存储的,而且叶子节点的data值是有序的,那么通过Min函数,可以直接再索引里面找到最小的值,无需查询表了,所以这里的type才会为null,所以这种性能是非常高的)

explain select min(id) from user

const, system: mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。 用于 primary key (主键索引)或 unique key (唯一索引) 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。system是const的特例,表里只有一条元组匹配时为system

--执行如下语句,结果要和图中一样的话, #得先关闭mysql5.7新特性对衍生表的合并优化 set session optimizer_switch='derived_merge=off';  还原配置:set session optimizer_switch='derived_merge=on';
explain select * from (select * from `order` where id = 1) tmp;

关于下图中,给出优化的sql,显示查询的是dual表,这个是mysql的自带的空表,像我们上面的sql,mysql会直接将数据解析到空表中,查询返回出去,所以效率才会高

eq_ref: primary key (主键索引) 或 unique key (唯一索引) 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type

explain select * from user_order left join `order` on user_order.oid = `order`.id

ref: 相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行记录。

  1. 简单 select 查询,name是普通索引(非唯一索引)
explain select * from `order` where name = '小米电脑';

2.关联表查询,idx_user_order_id是oid和uid的联合索引,这里使用到了user_order的左边前缀oid部分。(这里底层是使用了覆盖索引查询,因为我的连表查询条件中,两个id都是被索引关联着,这样的话,可以直接通过辅助索引就能定位到记录,而不需要再通过辅助索引的id再去主键索引中查询其他的记录了)

explain select oid from `order` left join user_order on `order`.id = user_order.oid

range: 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行记录。

explain select * from user where id > 1;

index: 扫描全索引就能拿到结果,一般是扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接对二级索引的叶子节点遍历和扫描,速度还是比较慢的,这种查询一般未使用覆盖索引,二级索引一般比较小,所以这种通常比ALL快一些。

explain select * from `order`;

ALL:即全表扫描,扫描你的聚簇索引(什么是聚簇索引文章开头有解释)的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from user;

5.possible_keys列

这一列显示查询可能使用哪些索引来查找。

6.key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

7.key_len列

这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。 举例来说,film_actor的联合索引 idx_user_order_id 由 oid 和 uid 两个int列组成,并且每个int是4字节。通 过结果中的key_len=4可推断出查询使用了第一个列:uid列来执行索引查找。

explain select * from user_order where oid = 2;

8.ref列

这一列显示了在key列记录的索引中,表查找值所用到的列或常量

9.rows列

这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。

10.Extra列

这一列展示的是额外信息。常见的重要值如下:

  • Using index:使用覆盖索引
  • Using where:使用 where 语句来处理结果,并且查询的列未被索引覆盖(简单理解为where条件查询的字段没有添加索引)
  • Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围;
  • Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索 引来优化。
  • Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
  • Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 基于责任链模式实现网关权限框架

    黎明大大
  • 一文带你深入理解Mysql索引底层数据结构与算法

    首先看一下,在数据库没有加索引的情况下,SQL中的where语句是如何查找目标记录的,首先看到下图的Col2字段,如果我们要查找where col2 = 89的...

    黎明大大
  • ELK日志收集原理+es集群+elk搭建+本地目录文件读取,搭建分布式日志收集系统

    如果没有输入信息表示没有安装。 如果安装可以使用rpm -qa | grep java | xargs rpm -e --nodeps 批量卸载所有带有Jav...

    黎明大大
  • MyBatis框架教程「实践与工具类封装」

    上一篇文章我们学习了MyBatis框架的环境搭建以及对sqlsessionfactory有个大致的了解,这篇文章就要运用搭建好的环境进行增删改查并且指出一些细节...

    用户1093975
  • Spring Boot(六)集成 MyBatis 操作 MySQL 8

    MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC代码和手动设置参数以及获取结果集。

    Java中文社群_老王
  • 性能评测:MyBatis 与 Hibernate 的性能差异

    核心都是将关系型数据库和数据转成对象型。当前流行的方案有Hibernate与myBatis。

    芋道源码
  • 利用 Travis 自动部署博客

    集成 主要是用来将多个用户的开发模块构建成一个可运行版本;而 持续集成 则是在集成之上,尽量将每一次提交都进行一次构建,这个个过程就是 持续集成 。

    zucchiniy
  • 从零开始重新认识 MyBatis

    这里 MyBatis 提供多种配置方式,这里我们都采取了最优的方式配置了,注释掉的为其他配置方式。

    wsuo
  • 索引与慢sql剖析

    `user_id` int(9) NOT NULL AUTO_INCREMENT,

    虞大大
  • [GO] golang练习项目-gorm与mysql的增删查改操作

    陶士涵

扫码关注云+社区

领取腾讯云代金券