mysql的执行计划详解

最新推荐文章于 2023-04-19 23:25:05 发布

小泽在路上J

最新推荐文章于 2023-04-19 23:25:05 发布

阅读量246

点赞数

分类专栏： mysql 文章标签： mysql

原文链接：https://www.bilibili.com/video/BV1zJ411M7TB

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

本篇文章的目的是为了学习Explain的基本用法

一、expain是什么？

使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句，通过expalin对sql的解析，我们可以针对性的解决sql的运行效率，提高sql的性能。常用的explian的字段一共有10个字段，分别是id、select_type、table、type、possible_key、key、key_len、ref、rows、extra。本篇文章会详细的介绍这10个字段。

二、准备工作

CREATE TABLE `t_user`  (
  `id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户账号',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户姓名',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Compact;

INSERT INTO `t_user` VALUES ('1', 'super', '3fec173b-c597-4a52-9778-535f7c51ef62', '超级管理员');
INSERT INTO `t_user` VALUES ('2', 'admin', '3fec173b-c597-4a52-9778-535f7c51ef62123456', '系统管理员');
INSERT INTO `t_user` VALUES ('3', 'stu1', '3fec173b-c597-4a52-9778-535f7c51ef62', '学生1');
INSERT INTO `t_user` VALUES ('4', 'stu2', '3fec173b-c597-4a52-9778-535f7c51ef62', '学生2');
INSERT INTO `t_user` VALUES ('5', 't1', '3fec173b-c597-4a52-9778-535f7c51ef62', '老师1');

CREATE TABLE `t_role`  (
  `id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `role_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色名称',
  `role_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色code',
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `role_name`(`role_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = Compact

INSERT INTO `t_role` VALUES ('5', '学生', 'student', '学生');
INSERT INTO `t_role` VALUES ('6', '老师', 'teacher', '老师');
INSERT INTO `t_role` VALUES ('7', '教学管理员', 'admin', '教学管理员');
INSERT INTO `t_role` VALUES ('8', '超圾管理员', 'super', '超圾管理员');

CREATE TABLE `user_role`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `role_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色id',
  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `role_id`(`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户角色表' ROW_FORMAT = Compact;

INSERT INTO `user_role` VALUES (1, '1', '5');
INSERT INTO `user_role` VALUES (2, '1', '7');
INSERT INTO `user_role` VALUES (3, '2', '8');
INSERT INTO `user_role` VALUES (4, '3', '9');
INSERT INTO `user_role` VALUES (5, '4', '8');
INSERT INTO `user_role` VALUES (6, '5', '10');

三、ID

ID 的作用是搭配table字段读取mysql读取表的顺序 
  1、ID相同  从上到下读取
  2、ID不同  从大到小读取

3.1、ID相同

EXPLAIN SELECT * FROM t_user u,t_role r,user_role ur WHERE  u.id=ur.user_id AND r.id=ur.role_id

在这里插入图片描述

3.2、ID不同

EXPLAIN SELECT * FROM t_role  WHERE id =( SELECT role_id  FROM user_role WHERE user_id =(SELECT id FROM t_user WHERE NAME='学生2') )

在这里插入图片描述

3.3 id有相同也有不同

EXPLAIN SELECT * FROM t_role r,(SELECT * FROM  user_role WHERE ID=2) ur WHERE r.id=ur.role_id

在这里插入图片描述

四、select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：
SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT  效率依次降低

4.1、SIMPLE

简单的select查询，查询中不包含子查询或union查询。

EXPLAIN SELECT * FROM t_role

在这里插入图片描述

4.2、PRIMARY

查询中若包含任何复杂的子部分，最外层查询为PRIMARY，也就是最后加载的就是PRIMARY。

EXPLAIN SELECT * FROM user_role  WHERE user_id =(SELECT id FROM t_user WHERE NAME='超级管理员')

在这里插入图片描述

4.3、SUBQUERY

在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

EXPLAIN SELECT *,(SELECT NAME FROM t_user WHERE id=1) name FROM user_role ur WHERE  ur.user_id=1;

在这里插入图片描述

4.4、DERIVED

在from列表中包含的子查询会被标记为DERIVED(衍生)

EXPLAIN SELECT u.* FROM user_role,(SELECT * FROM t_user WHERE NAME='超级管理员') u  WHERE user_role.user_id=u.id

在这里插入图片描述

4.5、UNION

UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；从UNION表获取结果的SELECT被标记为：UNION RESULT

EXPLAIN  SELECT * FROM t_user WHERE id=1 UNION  SELECT * FROM t_user WHERE id=2

在这里插入图片描述

五、table

显示查询的表名称 如果是null 则不涉及对表的操作;如果是<derived N>则表示是临时表
N就是执行计划的ID 表示结果来自于这个查询产生

六、type

表示按照某种类型来查询，例如按照索引类型查找，按照范围查。经常遇到的有6中
all、index、range、ref、eq_ref、const 从左往右性能依次提高。
一般来说，得保证查询至少达到 range 级别，最好能达到 ref

6.1、all

全表扫描

EXPLAIN SELECT * FROM t_user WHERE `name` ='学生2'

在这里插入图片描述

6.2、index

sql使用了索引但是没用通过索引进行过滤，扫描了整张索引树

EXPLAIN SELECT Id FROM t_user

在这里插入图片描述

6.3、range

一般where 语句中出现 between、<、>、in 等的查询这种范围扫描

EXPLAIN SELECT * FROM user_role WHERE id BETWEEN 2 AND 5

在这里插入图片描述

6.4、ref

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

EXPLAIN SELECT * FROM user_role WHERE user_id='2'

在这里插入图片描述

6.5、eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。一般出现关联查询

EXPLAIN SELECT * FROM t_user u,user_role ur WHERE  u.id=ur.id

在这里插入图片描述

6.6、const

通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。

EXPLAIN SELECT * FROM t_user WHERE username='super'

在这里插入图片描述

七、possible_keys

MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

八、key

实际使用的索引。如果为NULL，则没有使用索引

九、key_len

表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。
在不损失精确性的情况下，长度越短越好显示的是索引字段的最大长度，并非实际使用长度

9.1、计算方式

计算规则：
1、如果允许为NULL 加一个字节
2、一般情况key_len等于索引类型的长度 int是4个字节、bigint是8个字节
3、字符串类型的key_len和字符编码集有关  utf-8乘以3，gbk乘以2 
   3.1、varchar(10) 
    以utf-8为例  10*3=30 如果允许为NULL需要再加一个字节 
    还需要1~2的字节记录存储值的长度  30+1+2=33
   3.2  char(10)
        以utf-8为例  10*3=30 如果允许为NULL需要再加一个字节
        因为char是定长 所以不用记录长度  30+1=31

十、ref

使用哪个列或常数与key一起从表中选择行

十一、rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好

十二、Extra

记录不适合放在其他11项里的额外信息 常用用的
use where、use index、Using filesort、Using temporary, use join buffer

12.1、use where

使用where过滤 条件过滤
（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；

EXPLAIN SELECT * FROM t_user WHERE NAME='学生1'

在这里插入图片描述

12.2、use index

表明使用了索引覆盖 避免回表

EXPLAIN SELECT username FROM t_user WHERE username='super'

在这里插入图片描述

12.3、Using filesort

文件排序，对不是索引的字段进行排序（全字段排序 和rowid排序）  需要优化

EXPLAIN SELECT username FROM t_user  ORDER BY NAME

12.4、Using temporary

在查询过程中产生了临时表用于保存中间结果 需要优化

EXPLAIN SELECT username FROM t_user  GROUP  BY NAME

在这里插入图片描述

12.5、use join buffer

1、使用连接查询的时候关联字段最好可以使用索引
2、用小结果集驱动大结果集 减少外层循环次数 减少内层连接次数

小泽在路上J

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql的执行计划详解

文章目录前言一、expain是什么？二、准备工作三、ID3.1、ID相同3.2、ID不同3.3、ID相同和不同四、select_type总结前言本篇文章的目的是为了学习Explain的基本用法一、expain是什么？使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句，通过expalin对sql的解析，我们可以针对性的解决sql的运行效率，提高sql的性能。常用的explian的字段一共有10个字段，分别是id、select_type、table..
复制链接

扫一扫