MySql之Explain详解

Caixr
222 阅读10分钟

写在前面

继续上篇的mysql索引后，今天来复习一下MySQL中的explain工具。

Explain工具

Explain是MySQL中自带的关键字，可以用来模拟执行Sql语句，通过返回的结果可以分析查询语句或是结构的性能瓶颈。通常是在select前面添加explain关键字，执行查询会返回执行计划的信息，而不是执行这条sql语句。

Explain的两个变种

1）explain extended：会在explain的基础上额外提供一些查询优化的信息，紧随其后通过show warnings 可以得到优化后的查询语句（一般仅仅做参考）。

explain extended select * from film where id = 1;
show warnings;

image.png

image.png
2）explain partitions：如果查询的是分区表的话，会显示查询访问的分区。
以上两种一般不常用。
通常是直接使用explain

Explain结果列解释

explain select * from film where id = 1;
通过使用以上的语句会得出如下图的结果： image.png
来具体分析一下每个列所代表的意思：
id：表示的是select的序号，有几个select就有几个id，id是按照select出现的顺序增长。id越大执行的优先级越高，相同id则从上往下执行，id为null最后执行。
select_type：表示的是对应行是简单还是复杂的查询，通常有以下几种：
1）simple：简单查询。查询里面不包含子查询和union查询。如上图所示。
2）primary：复杂查询中最外层的select。
3）subquery：包含在select中的子查询（不在from字句中） 4）derived：包含在from中的子查询。mysql 会将结果存放在一个临时表中 举个例子：

set session optimizer_switch='derived_merge=off';
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
set session optimizer_switch='derived_merge=on';

image.png 依据上图来分析一下：首先看id越大执行优先级越高，所以会先执行select * from film where id = 1，看到是查询film表，其次会执行select 1 from actor where id = 1，查询的是actor表，最后执行的是最外层的select语句，查询的表是derived3,3表示的是id。
5）union：在union中的第二个和随后的select 举个例子：

explain select 1 union all select 1

image.png table：表示访问的表。当from有子查询的时候就会出现格式，N表示的是id，会先执行id=N的查询。当有union时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。
type：这是极为重要的一列。表示的是关联类型或访问类型，即Mysql决定如何查找表中的行，查找数据行记录的大概范围。从优到差分别为：system>const>eq_ref>ref>range>index>all。
通常需要保证查询达到range级别，最好可以达到ref。
1）NULL：表示在执行阶段不需要访问表或者索引。例如：使用min(索引id) EXPLAIN select min(id) FROM film
2）const,system：system是const的特列，MySQL会对查询的某部分进行一个优化，将其转换成一个常量，用于primary key或unique key的所有列与常数比较时，表最多只有一个匹配行，只需要读取1次，速度非常快。
例如：

EXPLAIN EXTENDED select * FROM film where id = 1;
show warnings;

image.png image.png
3）eq_ref：primary key或unique key索引的索引部分被连接使用，最多只会返回一条记录。
例如：

explain select * from film_actor left join film on film_actor.film_id = film.id;

image.png
4）ref：相比于eq_ref,ref不使用唯一索引，而是使用普通索引或者唯一性索引的前缀部分，索引要和某个值比较，可能会找到多行。 例如：

explain select * from film where name = 'film1'; --name是非唯一索引
explain select film_id from film left join film_actor on film.id = film_actor.film_id; --film_id和actor_id是联合索引

image.png

image.png 5）range：通常出现在in,between,>,<,>=等范围查询的操作中。使用一个索引来检索给定范围的行。
6）index：扫描索引就能拿到结果，一般是扫描某个二级索引，不会从根节点开始扫描，直接对二级索引的叶子节点遍历和扫描，速度比较慢，这种查询一般使用覆盖索引，二级索引通常来说会比较小，会比All快一些。
7）ALL：全表扫描，会扫描聚簇索引的所有叶子节点，速度慢。
possible_keys：查询过程中可能会使用到的索引。在使用explain时候会发现有时会出现possible_keys不为Null,而key列是NULL的情况，这是因为表中的数据不多的情况下，mysql会直接全表查询，使用索引来查询帮助不大。
key：即查询过程中使用到的索引键。 key_len：表示查询语句使用到索引的字节数，通过这个值可以算出具体使用了索引中的哪些列。
例如： explain select * from film_actor where film_id = 2;

image.png 因为idx_film_actor_id是联合索引由film_id和actor_id组成，并且每个索引字节数为4字节。通过key_len=4可知，查询语句使用了第一个列，也就是film_id来执行索引查找。
key_len计算规则如下：

  • 字符串，char(n)和varchar(n),n代表字符数，而不是字节数,如果是utf-8，一个数字或字母占1个字节，一个汉字占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
    ref：表示索引查找时候所用到的列或常量，常见的有：const、字段名
    rows：表示的是估算要读取并检测的行数。
    Extra：表示的是额外信息。
    常见的值如下：
    1）Using Index：使用覆盖索引
    覆盖索引的定义：通俗来讲就是，sql语句在执行的时候有使用到索引，并且如果说select的后面查询字段，都可以从这个索引树中找到，这种情况就是使用到了覆盖索引。一般来说覆盖索引，是针对非主键索引，也就是二级索引，整个查找过程中，通过二级索引就能够查找到需要的字段，就不需要再去主键索引树里面去查找。 例如：
explain select film_id from film_actor where film_id = 1;
explain select * from film_actor where film_id = 1;

image.png

image.png 如上图一和二分别对应的上面的两个sql语句，因为film_id是二级索引，在select后面查找的字段只有film_id，所以，在索引树中，只需要查找二级索引树，就可以找到需要的字段。第二个语句中select后面查找的*是所有字段，在二级索引树中，只有包含主键索引的值，因此需要根据主键索引再去到主键索引树中去查找其他字段。
2）using where：使用where语句来处理结果，并且查询的列未被索引覆盖。
例如：

explain select * from actor where name = 'a';

image.png 3）using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围。
例如：

 explain select * from film_actor where film_id > 1;

image.png 因为select * 中并不是所有的列被索引覆盖，where条件后面的film_id是索引。
4）using temporary：表示需要创建一张临时表来处理查询。这种情况一般需要优化，可以通过索引来优化。
5）using filesort：将用外部表排序而不是索引排序，数据小时从内存排序，否则需要在磁盘完成排序，可以通过索引来优化。
6）Select tables optimized away：使用某些函数，比如max,min来访问存在索引的某个字段
例如：

explain select min(id) from film;

image.png

索引最佳使用原则

全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

image.png

image.png

image.png 这三个查询的ref列都是const，表示是常量，type是ref，效率高。

最左前缀原则

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager'

再之前的文章中解释过。

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

image.png

image.png

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。is null,is not null 一般情况下也无法使用索引。

like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

解决like'%字符串%'索引不被使用的方法？
1）使用覆盖索引，查询字段必须是建立覆盖索引字段 2）如果不能使用覆盖索引则可能需要借助搜索引擎（ES）

字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000; --索引失效

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

范围查询优化

explain select * from employees where age >=1 and age <=2000;

image.png 没走索引的原因是mysql内部优化器会根据检索比例、表大小等因素来评估是否使用索引。例如上面，可能是由于单次查询数据量过大导致优化器最终选择不走索引。优化方法：可以将大的范围拆分为多个小范围。

explain select * from employees where age >=1 and age <=1000; 
explain select * from employees where age >=1001 and age <=2000;

image.png

最后

image.png

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

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

-- ----------------------------
-- Records of film
-- ----------------------------
INSERT INTO `film` VALUES (3, 'film0');
INSERT INTO `film` VALUES (1, 'film1');
INSERT INTO `film` VALUES (2, 'film2');

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for film_actor
-- ----------------------------
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor`  (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_film_actor_id`(`film_id`, `actor_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

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

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

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

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (1, 'lilei', 350);
INSERT INTO `account` VALUES (2, 'hanmei', 16000);
INSERT INTO `account` VALUES (3, 'lucy', 2400);
INSERT INTO `account` VALUES (4, 'lily', 700);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for actor
-- ----------------------------
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor`  (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci 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 actor
-- ----------------------------
INSERT INTO `actor` VALUES (1, 'a', '2021-09-07 20:33:23');
INSERT INTO `actor` VALUES (2, 'b', '2021-09-08 14:23:19');
INSERT INTO `actor` VALUES (3, 'c', '2021-09-08 14:23:29');

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '入职时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age_position`(`name`, `age`, `position`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 157044 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工记录表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
avatar
文章
阅读
粉丝