首页 > 数据库 >MYSQL explain详解

MYSQL explain详解

时间：2022-12-09 18:06:30浏览次数：43  
标签：explain 详解 user MYSQL tb id select subject

MySQL explain

  • ​MySQL explain​
  • ​​explain作用​​
  • ​​explain的执行效果​​
  • ​​explain包含的字段​​
  • ​详细内容​
  • ​​id字段​​
  • ​​select_type字段​​
  • ​​type字段​​
  • ​​table字段​​
  • ​​possible_keys字段​​
  • ​​key字段​​
  • ​​key_len字段​​
  • ​​ref字段​​
  • ​​row字段​​
  • ​​partitions字段​​
  • ​​filtered字段​​
  • ​​Extra字段​​

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

2021年3月5日17:33:33

explain作用

  1. 表的读取顺序如何
  2. 数据读取操作有哪些操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间是如何引用
  6. 每张表有多少行被优化器查询

...

explain的执行效果

mysql> explain select * from tb_user where id = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

explain包含的字段

  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字段

1. id相同

explain select * from tb_subject sub,tb_user us,tb_user_subject usu where sub.id=usu.subject_id and us.id=usu.user_id and us.id=1;

2. id不同

​​如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行​​

读取顺序：tb_user > tb_user_subject

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1);

3. id相同又不同

​​id如果相同，则从上往下顺序执行。id不同，则id值越大，越先执行​​

explain select * from tb_subject sub LEFT JOIN tb_user_subject usu on sub.id=usu.subject_id where usu.user_id=1 union all select * from tb_subject sub LEFT JOIN tb_user_subject usu on sub.id=usu.subject_id where usu.user_id=1;

select_type字段

1. SIMPLE

​​简单查询，不包含子查询或union查询​​

explain select * from tb_subject sub,tb_user us,tb_user_subject usu where sub.id=usu.subject_id and us.id=usu.user_id and us.id=1;

2. PRIMARY

​​查询中若包含任何复杂的子查询，最外层查询则被标记为主查询PRIMARY​​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1)

3. SUBQUERY

​​在select或where中包含子查询​​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1)

4. DERIVED

​​在from列表中包含的子查询被标记为DERIVED​​

explain select name from (select * from tb_user us where id=1) tmp;

5. UNION

​​若第二个select出现在union之后，则被标记为UNION​​

explain select * from tb_user where id=1 union select * from tb_user where id=2;

6. UNION RESULT

​​从UNION表获取结果的select​​

explain select * from tb_user where id=1 union select * from tb_user where id=2;

type字段

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差

备注：掌握以下10中常见的即可

NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

1. NLL

​​MySQL能够在优化阶段分解查询语句，在执行阶段不用再访问表或索引​​

explain select min(id) from tb_user;

2. system

​​表中只有一行记录，这是const类型的特例，平时不太会出现，可以忽略​​

没有实例

3. const

​​表示通过索引一次就找到了，const用于比较primary key或unique索引，​​

explain select * from tb_system where id=1;

4. eq_ref

​​唯一性索引扫描，对于每个索引键，表中只有一条记录与之对应，常见于主键或唯一索引扫描​​

explain select subject_id from tb_user_subject usu LEFT JOIN tb_subject su on usu.subject_id=su.id;

5. ref

​​非唯一性索引扫描，返回匹配某个单独值得所有行​​

explain select subject_id from tb_subject su LEFT JOIN tb_user_subject usu on usu.subject_id=su.id;

6. ref_or_null

​​类似ref，但是可以搜索值为NULL的行​​

explain select * from tb_user_subject usu where subject_id=1 or subject_id is null;

7. index_merge

​​表示使用了索引合并的优化方法​​

explain select * from tb_user_subject where id = 1 or subject_id='2';

8. range

​​只检索给定范围的行，使用一个索引来选择行​​

explain select * from tb_user where id BETWEEN 1 and 3;

9. index

​​Full index Scan，Index与All区别：index只遍历索引树，通常比All快，因为索引文件通常比数据文件消息，也就是虽然all和index都是读全表，但index是从索引中读的，而all是从硬盘读的​​

explain select id from tb_user;

10. ALL

​​Full Table Scan，将遍历全表以找到匹配行​​

explain select * from tb_user;

table字段

数据来自哪张表

possible_keys字段

显示可用应用在这张表中的索引，一个或多个，查询涉及到的字段若润在索引，则该索引将被列出，但不一定被实际使用

key字段

实际使用到的索引，如果为NULL，则没有使用索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引进出现在key列表

key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确度的情况下，长度越长越好，key_len显示的值为索引字段最大的可能长度，并非实际使用长度，即key_len是根据定义计算而得，不是通过表内索引出

ref字段

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

row字段

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

partitions字段

匹配的分区

filtered字段

查询的表行占表的百分比

Extra字段

包含不适合在其他列显示但十分重要的额外信息

1. Using filesort

说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作成为 [文件排序]

explain select * from tb_user order by name;

2. Using temporary

​​使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by和分组查询group by​​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1) union all select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1);

3. Using index

​​表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，​​

explain select subject_id from tb_user_subject usu where usu.user_id= (select id from tb_user us where id=1);

4. Using where

​​使用了where条件​​

5. Using join buffer

​​使用了连接缓存​​

explain select * from tb_subject sub,tb_user us,tb_user_subject usu;

6. impossible where

​​where自居的值总是false，不能用来获取任何元组​​

explain select * from tb_user where id=1 and id=3;

7. distinct

​​一旦MySQL找到了与行相联合匹配的行，就不再搜索了​​

explain select distinct(name) from tb_user us left join tb_user_subject sub on sub.user_id=us.id;

8. Select tables optimized away

​​select操作已经优化到不能再优化了，MySQL没有遍历表或索引就返回数据了​​

explain select min(id) from tb_user;

使用的数据表

/*
Navicat MySQL Data Transfer

Source Server         : loca
Source Server Version : 50624
Source Host           : localhost:3306
Source Database       : explain-learn

Target Server Type    : MYSQL
Target Server Version : 50624
File Encoding         : 65001

Date: 2021-03-05 17:30:25
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_subject
-- ----------------------------
DROP TABLE IF EXISTS `tb_subject`;
CREATE TABLE `tb_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_name` varchar(255) DEFAULT NULL COMMENT '学科名',
  `subject_teacher` varchar(255) DEFAULT NULL COMMENT '学科老师姓名',
  `subject_code` varchar(255) DEFAULT NULL COMMENT '学科代码',
  PRIMARY KEY (`id`),
  KEY `subject_code` (`subject_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_subject
-- ----------------------------
INSERT INTO `tb_subject` VALUES ('1', '语文', '张三', '1000');
INSERT INTO `tb_subject` VALUES ('2', '数学', '李四', '2000');
INSERT INTO `tb_subject` VALUES ('3', '英语', '王五', '3000');
INSERT INTO `tb_subject` VALUES ('4', '物理', '赵六', '4000');
INSERT INTO `tb_subject` VALUES ('5', '化学', '田七', '5000');
INSERT INTO `tb_subject` VALUES ('6', '生物', '李八', '6000');

-- ----------------------------
-- Table structure for tb_system
-- ----------------------------
DROP TABLE IF EXISTS `tb_system`;
CREATE TABLE `tb_system` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `desc` varchar(255) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_system
-- ----------------------------
INSERT INTO `tb_system` VALUES ('1', '我只有一行');

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `user_no` varchar(11) DEFAULT NULL COMMENT '工号，唯一',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_no` (`user_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'henry', '18', '0001');
INSERT INTO `tb_user` VALUES ('2', 'lucy', '20', '0002');
INSERT INTO `tb_user` VALUES ('3', 'jack', '66', '0003');

-- ----------------------------
-- Table structure for tb_user_subject
-- ----------------------------
DROP TABLE IF EXISTS `tb_user_subject`;
CREATE TABLE `tb_user_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `subject_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subject_id` (`subject_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user_subject
-- ----------------------------
INSERT INTO `tb_user_subject` VALUES ('1', '1', '1');
INSERT INTO `tb_user_subject` VALUES ('2', '2', '1');
INSERT INTO `tb_user_subject` VALUES ('3', '3', '3');
INSERT INTO `tb_user_subject` VALUES ('4', '1', '4');
INSERT INTO `tb_user_subject` VALUES ('5', '2', '4');
INSERT INTO `tb_user_subject` VALUES ('6', '3', '6');
INSERT INTO `tb_user_subject` VALUES ('7', '1', '6');
INSERT INTO `tb_user_subject` VALUES ('8', '2', '5');
INSERT INTO `tb_user_subject` VALUES ('9', '3', '4');
INSERT INTO `tb_user_subject` VALUES ('10', '1', '3');
INSERT INTO `tb_user_subject` VALUES ('11', '2', '2');
INSERT INTO `tb_user_subject` VALUES ('12', '3', '1');

标签：explain,详解,user,MYSQL,tb,id,select,subject
From： https://blog.51cto.com/u_15907961/5926318

相关文章

  • docker 中安装mysql
    1、查看当前docker中的容器dockerimagesdockerps-a 2、dockerpull mysql   ---拉取mysql镜像3、dockerrun--namemysqlbyeddy-itd-p3306:3306--res......
  • golang mysql查询textRows和binaryRows解惑
    1.问题之前写了一套统一mysql返回数据的解析库：rows,err:=ms.dbInst.Query(s,args...) //执行SQL语句，比如select*fromusersiferr!=nil{panic(err)}......
  • Mysql读写分离
    目录：1、主从复制Mysql的复制类型Mysql主从复制的工作过程2、读写分离什么是读写分离为什么要读写分离什么时候要读写分离主从复制与读写......
  • docker 下 拉取mysql 镜像失败解决办法
    一、问题Docker拉取mysql镜像，发生报错。dockerpullmysql:8.0.221报错信息：Errorresponsefromdaemon:Gethttps://registry-1.docker.io/v2/:net/http:TLShandsha......
  • Mysql中的B+tree索引
    BTree意思是多路平衡查找树，它是一种数据结构。MySQL的InnoDB和MyISAM存储引擎，都是使用它来存储索引。BTree可细分为B-Tree和B+Tree，B+Tree是B-Tree的升级版。MySQL的InnoDB......
  • ON DUPLICATE KEY UPDATE 导致mysql自增主键ID跳跃增长
    一.问题点:1.如果mysql表只是设置了联合主键且不包含自增的id,则使用ONDUPLICATEKEYUPDATE不会有问题2.如果mysql表设置了自增主键id,则使用ONDUPLICATEKEYUP......
  • 详解redis网络IO模型
    前言"redis是单线程的"这句话我们耳熟能详。但它有一定的前提，redis整个服务不可能只用到一个线程完成所有工作，它还有持久化、key过期删除、集群管理等其它模块，redis会通......
  • mysql数据库为什么不建议select * from操作
    为什么select*from不是好的选择？在这里只是针对innodb数据库引擎来说明。我每次看到select*fromtableName时，我都觉得有没有必要？取出全部的列会让优化器无法完成索引覆盖......
  • 静态配置、request对象方法，pycharm连接mysql，django连接mysql，django ORM
    目录静态文件配置静态文件相关配置form表单request表单pycharm连接数据库django连接数据库ORM简介ORM基本操作ORM基本语句静态文件配置1.编写一个用户登录页面2.静态文......
  • 部门mysql操作
     usetest_db;--删除表droptableifexistst1_profit;droptableifexistst1_salgrade;droptableifexistst1_emp;droptableifexistst1_dept;--......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99