MySQL的explain详解

精选原创

jiangdazheng 2024-05-06 09:50:35 博主文章分类：mysql ©著作权

文章标签 mysql 数据库 sql 字符串 MySQL 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者jiangdazheng的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建数据脚本

有三个表分别为t_order、t_order_detail、t_product表，具体脚本如下：

CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `total_money` double DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`t_order` (`id`, `username`, `phone`, `address`, `total_money`, `create_date`, `update_date`) VALUES (1, 'test', '13812345678', 'test', 100, '2023-11-24 10:47:36', '2023-11-24 10:47:38');
INSERT INTO `test`.`t_order` (`id`, `username`, `phone`, `address`, `total_money`, `create_date`, `update_date`) VALUES (2, 'test1', '13812345678', 'test1', 100, '2023-11-24 10:47:36', '2023-11-24 10:47:38');
INSERT INTO `test`.`t_order` (`id`, `username`, `phone`, `address`, `total_money`, `create_date`, `update_date`) VALUES (3, 'test2', '13812345671', 'test2', 100, '2023-11-24 10:57:20', '2023-11-24 10:57:20');
INSERT INTO `test`.`t_order` (`id`, `username`, `phone`, `address`, `total_money`, `create_date`, `update_date`) VALUES (4, 'test3', '13812345672', 'test3', 100, '2023-11-24 10:57:20', '2023-11-24 10:57:20');
INSERT INTO `test`.`t_order` (`id`, `username`, `phone`, `address`, `total_money`, `create_date`, `update_date`) VALUES (5, 'test4', '13812345673', 'test4', 100, '2023-11-24 10:57:20', '2023-11-24 10:57:20');

CREATE TABLE `t_order_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) DEFAULT NULL,
  `product_id` bigint(20) DEFAULT NULL,
  `price` decimal(18,4) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_product_id` (`order_id`,`product_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`t_order_detail` (`id`, `order_id`, `product_id`, `price`, `num`, `create_date`, `update_date`) VALUES (1, 1, 1, 7999.0000, 1, '2023-11-24 11:06:17', '2023-11-24 11:06:20');
INSERT INTO `test`.`t_order_detail` (`id`, `order_id`, `product_id`, `price`, `num`, `create_date`, `update_date`) VALUES (2, 1, 1, 39.0000, 1, '2023-11-24 11:06:38', '2023-11-24 11:06:40');

CREATE TABLE `t_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(255) DEFAULT NULL,
  `goods_unit` tinyint(4) DEFAULT NULL,
  `price` decimal(18,4) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

1	Iphone15手机	1	7999.0000	2023-11-24 11:02:02	2023-11-27 00:05:15
2	Iphone15手机壳	1	39.0000	2023-11-24 11:03:54	2023-11-27 00:05:18
3	Iphone15手机屏保	1	99.0000	2023-11-24 11:03:56	2023-11-27 00:05:20

mysql> EXPLAIN EXTENDED select * from t_order;

MySQL的explain详解_字符串

在查询中的每个表会输出一行，如果有二个表通过join连接，就会展示2行。

explain两个变种

(1)explain extended:会在explain的基础上提供优化的信息。紧随其后通过show warnings命名可以得到优化后的查询语句，从而看出优化器优化了什么。filtered的值是百分比表示，rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数。

mysql> EXPLAIN EXTENDED select * from t_product where id=1;

MySQL的explain详解_数据库_02

mysql> show warnings;

explain中的列说明

列

id列的编号是select的序列号，有几个select就对应着有几个id,并且id的值是按select出现的顺序增长的。id列值越大，优先级越高；id值一样则从上到下执行，id等于NULL最后执行。

2.select_type列

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

1)simple:简单查询，不包含子查询和union

mysql> EXPLAIN extended select * from t_product where id=1;

MySQL的explain详解_MySQL_03

2)primary:复杂查询中的最外层的select

sql> EXPLAIN extended select * from   (select id from t_product where id=1) a;

MySQL的explain详解_字符串_04

3)subquery:复杂查询中的最外层

mysql> EXPLAIN extended select (select 1 from t_order_detail where id=1) as id from t_order_detail;

4)derived:包含在from子句中的子查询。mysql查询把结果临时放到一个临时表，也叫派生表

mysql> EXPLAIN extended select (select 1 from t_order where id=1) from (select 1 from  t_order_detail where id =1) a;

3.table列

本列代表explain的一行正在访问哪个表。

当from中有子查询时，table列是<derivenN>格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。

当有union时,UNION RESULT的table列的值为<unitonA,B>,A和B表示参与union的select行中的id

4.type列

本列表示关联类型或访问类型,MySQL通过哪种方式查找表中的行。

性能从最优到最差分别为：system>const>eq_ref>ref>range>index>ALL

建议能达到range级别，最好能到ref级别

NULL:mysql在优化阶段分解查询语句，不用到执行阶段访问表或索引。例如：在索引中选取最大值，可以单独查找索引，不需要在执行时候访问表

mysql> EXPLAIN extended select max(id) from t_order;

system,const:MySQL能对查询的某部分进行优化并将其转化成一个常量(通过show warnings来查看结果)。用于primary key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表中只一个元素匹配时为system

mysql> explain extended select * from (select * from t_product where id = 1) tmp;

msql> show warnings;

eq_ref:primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。除了const之外最好的连接类型，简单的select查询不会出现这种type。

mysql>  explain extended select * from t_order_detail  LEFT JOIN t_order on t_order_detail.order_id=t_order.id;

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

1)简单select查询，username是普通索引（非唯一索引）

mysql> ALTER TABLE `test`.`t_order` 
ADD INDEX `idx_username`(`username`) USING BTREE;
ALTER TABLE `test`.`t_order` 
ADD INDEX `idx_username`(`username`) USING BTREE;

2)关联表查询,idx_order_product_id是order_id和product_id联合索引，这里使用到了t_order_detail的左边前缀product_id部分。

mysql> explain extended select * from t_order_detail  left join t_product  on t_order_detail.product_id=t_product.id;

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

mysql> explain extended select * from t_order where id>1;

index:扫描全索引取得结果，通常扫描哪个二级索引，本种索引不会索引树节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以通常比ALL要快一些。

ALL:全表扫描，扫描聚簇索引的所有子节点。通常本种情况下要创建索引来增加优化。

mysql> explain extended select * from t_order;

5.possible_keys列

本列展示可能使用中哪个索引来检索

6.key列

本列展示最后采用了优化后的索引对表的访问

key计算规则：

字符串，char(n)和varchar(n)，n均代表字符数，而不是字节数，如果是utf-8，一个数字或者字母占一个字节，一个汉字占3个字节

char(n):如果为中文长度就是3n
varchar(n):如果为中文长度为3n+2字节，加的2字节是用来存储字符串长度，因为varchar是变长字符串

数值类型

tinyint:1个字节
smallint:2个字节
int:4个字节
bigint:8个字节

时间类型

date:3个字节
timestamp:4字节
datetime:8字节

如果字段允许为NULL，要多加1位为记录是否为NULL

索引最大长度为768字节，当字符串过长时，mysql会做一个左前缀索引的处理，将前半部分提取出来做索引。

7.key_len列

本列展示mysql在索引中使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

8.ref列

本列展示key列记录中，表查找值所用的列或者常量，一般为:const(常量),字段名:(例：t_order.id)

9.row列

本列是mysql估算要读取并检测的行数，注意这个不是结果集里的行数

10.filtered列

本列是一个百分比的值，rows*filtered/100大致能估算出将要和explain中前一个表进行连接的行数

11.extra列

本列展示一些额外信息.

1)Using index:使用索引覆盖

mysql> explain extended select id from t_order where id=1;

MySQL的explain详解_MySQL_05

2)Using where:使用where来处理结果，结果中的列没有让索引覆盖。

mysql> explain extended select id from t_order where username='b';

MySQL的explain详解_数据库_06

3)Using index condition:查询不完全让索引覆盖，where是一个前导列的范围。

mysql> explain extended select * from t_order where id > 1;

MySQL的explain详解_字符串_07

4)Using temporary:MySQL创建一张临时表来处理查询。如果进行这种情况建议最好是优化，第一时间考虑用索引来优化。

mysql> explain extended select DISTINCT username from t_order ;

5)Using filesort:将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况也要考虑用索引来优化。

mysql> explain extended select* from t_product order by goods_name ;

MySQL的explain详解_数据库_08

6)Slelect table optimized away:使用某些聚合函数（比如:max、min）来访问存在索引的某个字段

mysql> explain extended select max(id) from t_product  ;

MySQL的explain详解_MySQL_09

下一篇：Java学不下去?老司机告诉你应该怎么来学好Java

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯