MySQL--explain执行计划详解

什么是执行计划？

SQL的执行计划，通俗来说就是SQL的执行情况，一条SQL语句扫描哪些表，那个子查询先执行，是否用到了索引等等，只有当我们知道了这些情况之后才知道，才可以更好的去优化SQL，而这个过程MySQL帮助我们生成好了，这就是执行计划。

执行计划的作用？

• 可以看到表的读取顺序。
• 可以直观看到索引的使用情况。
• 可以看到每张表中的数据查询情况，有多少条数据被扫描获取。
• 可以看到查询表数据的类型。

一句话概括，可以帮我们写出优雅、高性能的SQL。

准备数据：

#创建user表
CREATE TABLE `user` (`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',`user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户姓名',`user_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户工号',`age` tinyint DEFAULT NULL COMMENT '用户年龄',`address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户地址',`hobby` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户爱好',PRIMARY KEY (`id`) USING BTREE,KEY `index_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';#准备数据
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(1, '张三', 'TC-00000001', 25, '湖北', '篮球');
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(2, '李四', 'TC-00000001', 26, '湖南', '足球');
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(3, '王五', 'TC-00000001', 23, '广东', '电影');#创建索引
create index  index_name on user(user_name);

查看user表中的所有索引：

show index from user;

结果：

查看执行计划的语法：

• explain + SQL查询语句。
• desc + SQL查询语句，估计有部分同学不知道desc 这个关键字还可以查看执行计划。

使用explain 查看SQL执行计划：

# 
explain select * from user where user_name='张三';;

执行计划：

使用desc 查看SQL执行计划：

# 
desc select * from user where user_name='张三';;

执行计划：

根据对比可以看出explain、desc 都可以看SQL的执行计划。

执行计划各个指标的说明：

• id：SQL的执行顺序，id越大优先级越高，越先被执行。
• select_type：每个select子句的类型。
  SIMPLE，简单的查询，不包含子查询或者union查询。
  PRIMARY，最外层查询，查询中包含任何复杂的子查询，则最外层查询被标记为PRIMARY。
  DERIVED，衍生的，在from列表中包含子查询，其类型会别标记为DERIVED。
• table：表名。
• type：访问类型，需要重点关注，直接体现了SQL语句的性能，常见的类型 system->const->eq_ref->ref->range->index->all，性能依次从好到差。
  const、system：主键索引或唯一索引的所有列与常量比较时，表里最多有一个匹配行，读取一次。
  eq_ref：命中主键索引或者唯一索引，查询结果最多返回一条数据。
  ref：使用普通索引或者唯一索引的部分前缀，索引和某个值对比的时候，会查询到多行结果。
  range：只检索给定范围的行。
  index：只遍历索引树。
  all：全表扫描，MySQL会遍历全表找到匹配的数据行，性能最差。
• piossible_keys：可能用到的索引。
• key：实际查询用到的索引。
• key_len：索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。
• rows：表示MySQL根据表情况及索引使用情况，估算找到目标数据需要扫描的行数，这个值越小越好。
• filtered：表示经过条件过滤后剩余记录所占百分比，这个数据越大越好。
• Extra：Extra有以下几个常用的值。
  Using index，查询的列被索引覆盖（覆盖索引），并且where筛选条件是索引的前导列，是查询性能高的表现。
  Using where，查询的列没有被索引覆盖，where筛选条件不是索引的前导列。
  Using where Using index，查询的列没有被索引覆盖，但是where筛选条件是所有列之一但不是索引前导列。
  NULL，查询的列没有被索引覆盖，但是where查询条件使用了索引前导列。
  Using index condition，查询条件虽然用到了索引列，但是有部分条件无法使用索引列，先会使用索引列的条件搜索一遍，在使用其他条件搜索。
  Using temporay，MySQL需要建立一张临时表来处理数据，常出现在分组或排序查询中，这种情况是需要优化的。
  Using filesort，文件排序，MySQL会对查询结果进行外部排序，而无法使用索引排序，这种情况也是要优化的。

key_len说明：
key_len：表示索引使用的字节数，通过这个值可以算出索引使用了哪些列，不同类型的数据在MySQL中占用的字节数如下，供参考。
字符串类型：

• char(n)：n字节。
• varchar(n)：如果使用utf-8编码，占用字节数为3n+2，2是用存储字符串长度。
• varchar(n)：如果使用utf8mb4编码，占用字节数为4n+2，2是用存储字符串长度。

数值类型：

• tinyint：1字节。
• smallint：2字节。
• int：4字节。
• bigint：8字节。

时间类型：

• date：3字节。
• timestamp：4字节。
• datetime：8字节。

如果字段允许为空，则额外需要一个字节去记录是否为空。

如有不正确的地方请各位指出纠正。