我所了解的MySQL之三：施行规划

今天MySQL数据库栏目介绍相干施行规划。

今天MySQL数据库栏目介绍相干施行规划。

MySQL 系列的第三篇博客，主要内容是 MySQL 中对于 Explain 施行规划的剖析，假设你已经晓得怎样剖析施行规划，那么关于 SQL 调优也就信手拈来了。

纵不雅众多一二线大厂聘请时的岗亭请求，凡是设计数据库的必然会请求有 SQL 调优的经验，这险些已经成为与 Spring 分庭抗礼的“陈腔滥调文”类面试题。

要想进行 SQL 调优，第一需要晓得 SQL 的施行状况，最直不雅的感觉固然是 SQL 语句施行的工夫，然而除此以外，我们还可以通过施行规划来剖析 SQL 语句的施行状况，从而进行调优。

1. Explain 简述

Explain 语句可以查看 MySQL 是怎样施行这条 SQL 语句的，包含运用索引状况、扫描行数等，这些信息关于 SQL 调优来说十分重要，所以第一得看懂施行规划。

mysql> explain select * from user where name='one';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ref  | a             | a    | 13      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec) 

以上是一条简略查询语句的施行规划，这张表一共有12个字段，离别代表不一样的含义，下面一一论述。

• id: 表示 SQL 施行的次序，值越大，优先级越高。若值雷同，施行次序由优化器决议。
• select_type: 表示 select 查询语句的类型
• table: SQL 语句查询的表名（或该表的又名），也可能是暂时表等不存在的表
• partitions: 查询语句波及的分区信息
• type: 关联类型（拜访类型），决议了 MySQL 是怎样查寻表中行的。机能从最差到最优顺次是 ALL, index, range, index_merge, ref, eq_ref, const, system, NULL
• possible_keys: 展现了查询语句可以运用的所有索引
• key: 展现了优化器决议采纳的索引名称
• key_len: 展现了 MySQL 运用索引长度的字节数
• ref: 在 key 列记载的索引中查寻值所运用的列或常量
• rows: 扫描行数的估值
• filtered: 终究知足查询语句行数占存储引擎返回总行数的百分比
• Extra: 其他施行信息

以上只是对施行规划表各个字段的名词解释，接下来我会通过现实的例子来帮忙大家（我本人）更好地了解其中 select_type, type, key_len, rows, Extra 这些重要的字段。

2. Explain 详述

2.1 示例表构造

第一介绍本文中将用到的示例表表构造以及数据行：

CREATE TABLE `user`  (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `name` varchar(36) DEFAULT NULL COMMENT '姓名',  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',  `email` varchar(36) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`) USING BTREE,  INDEX `idx_age_name`(`age`, `name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1; 

通过函数向表中插入1000000条测试数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()begin 
  declare i int; 
  set i=1; 
  while(i<=1000000)do 
    insert into user(id,name,age,email) values(i, CONCAT('name',i), i % 50, concat(i,'name@email.cn'));    set i=i+1; 
  end while;end 

2.2 select_type in Explain

施行规划中 select_type 字段表示 select 查询语句的类型，常见类型有：

• SIMPLE: 简略的查询语句，不包含子查询和关联，如：

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec) 

2.2.1 PRIMARY

若查询语句中包括任何复杂的子局部，那么最外层局部会被标志为 PRIMARY，如：

mysql> explain select * from user where id=(select id from user where id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec) 

在这条 SQL 语句的施行规划中，首先条施行的 SQL，即 select * from yser where id = (...) 就被标志为 PRIMARY

2.2.2 SUBQUERY

包括在 select 或 where 内容中的子查询会被标志为 SUBQUERY，如上一条示例 SQL 的施行规划中第二条语句，即 select id from user where id=1 的 select_type 就被标志为了SUBQUERY。

2.2.3 DERIVED

包括在 FROM 关键字后的子查询（马上子查询的效果视为「表」），被视为「表」的子查询会被标志为 DERIVED，其效果将被寄存在暂时表中，如：

mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+|  1 | PRIMARY     |  | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | user       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec) 

从施行规划中可以看到，第二条施行的 SQL，即 select id,name,count(*) from user where id=1 的查询类型是 DERIVED。

select_type 一共有12中查询类型，具体释义可以看官方文档-explain_select_type

2.3 type in Explain

type 字段是施行规划中掂量 SQL 非常重要的根据，它展现了 SQL 语句的关联类型（拜访类型），决议了 MySQL 是怎样查寻表中行的。

type 字段的值机能从最差到最优顺次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

为了能更好地了解各个类型的含义，我对上述每一品种型都举出了响应的示例。

并未全部列出，完备的解释可以看官方文档-EXPLAIN Join Types

2.3.1 ALL

ALL 表示全表扫描，意味着存储引擎查寻记载时未走索引，所以它是机能最差的一种拜访类型，如

mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002301 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec) 

可以看到 rows 行的值为1002301，即扫描了全表的所有数据（扫描行数的值现实为预算），要是在生产环境有这样的 SQL，绝对是要优化的。

我们晓得在 where 查询前提中，不该该对查询字段运用函数或表达式（应当写在等号不等号右边），不理解此内容的可以看看我的上一篇博客 —— 我所了解的MySQL(二)索引。

这条查询语句在优化后应当是: select * from user where age=18，去除等号左侧的表达式，优化后的施行规划如下:

mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39360 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec) 

2.3.2 index

index 表示全索引树扫描，因为扫描的是索引树，所以比 ALL 情势的全表扫描机能要好。

同时，因为索引树自身就是有序的，可以以免排序。

mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_age_name | 116     | NULL | 1002301 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+1 row in set, 1 warning (0.00 sec) 

示例查询语句如上所述，当查询前提存在于结合索引 idx_age_name 中，但又没法直接运用该索引（因为最左前缀准则），同时查询列 id,age 也存在于结合索引中，不必通过回表来猎取时，施行规划中的拜访类型 type 列就会是 index。

2.3.3 range

range 表示范畴扫描，正确的说是基于索引树的范畴扫描，扫描的是局部索引树，所以机能比 index 稍好。

需要注意的是，若运用 in 或者 or 时，也可以运用范畴扫描。

mysql> explain select * from user where age>18 and age<20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 36690 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 78720 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec) 

2.3.4 index_merge

index_merge 即索引合并，它表示在查询时 MySQL 会运用多个索引。

MySQL 在 where 语句中存在多个查询前提，而且其中存在多个字段可以离别运用到多个不一样的索引，在这种状况下 MySQL 可以对多个索引树同时进行扫描，最后将它们的效果进行合并，如：

mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+| id | select_type | table | partitions | type        | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4     | NULL | 39361 |   100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+1 row in set, 1 warning (0.00 sec) 

上面这条查询语句中的 id=1 和 age=18 离别运用到了 PRIMARY 主键索引和 idx_age_name 结合索引，最后再将知足这两个前提的记载进行合并。

2.3.5 ref

ref 表示索引拜访（索引查寻），这种拜访类型会涌现在查询前提中以非聚簇索引列的常量值进行查询的状况。

比方在介绍全表扫描中优化后 SQL 的拜访类型就是 ref。

2.3.6 eq_ref

eq_ref 这种拜访类型会涌现在连贯查询时，通过聚簇索引进行连贯的状况，此类型最多只返回一条相符前提的记载。若表的聚簇索引为结合索引，所有的索引列必需是等值查询，如：

mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows    | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+|  1 | SIMPLE      | user1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1002301 |   100.00 | NULL  |
|  1 | SIMPLE      | user2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | all_in_one.user1.id |       1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+2 rows in set, 1 warning (0.00 sec) 

2.3.7 const

const 这种拜访类型会涌现在通过聚簇索引进行常量等值查询的状况，如：

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec) 

2.4 key_len in Explain

在上一篇博客 —— 我所了解的MySQL(二)索引 中 5.2 局部字段匹配 中已经提到过对于索引长度的盘算方式，这里再来总结一下。

2.4.1 字符类型

字符类型的字段若作为索引列，它的索引长度 = 字段定义长度 字符长度 + 可否默许NULL + 可否是变长字段*，其中：

• 字段定义长度 就是定义表构造时跟在字段类型后括号中的数字
• 字符长度 是常数，utf8=3, gbk=2, latin1=1
• 可否默许NULL 也是常数，若字段默许值为 NULL，该值为1，由于 NULL 需要额外的一个字节来表示；不然该值为0
• 可否是变长字段 也是常数，若该字段为变长字段，该值为2；不然该值为0

所谓的变长字段就是 varchar，它所占用的就是字段现实内容的长度而非定义字段时的长度。而定长字段，也就是 char 类型，它所占用的空间就是自定字段时的长度，若超过会被截取。

举个例子，为上述实例表中增加一个字符类型字段的索引。

alter table user add index idx_name(`name`); 

然后通过 name 字段去做查询，查看施行规划。

mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 111     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec) 

可以看到，施行规划中 key_len 一列的值为 111。

依据上述索引长度的盘算公式，name 列字段定义长度为36，字符集类型为默许的 utf8，该字段默许允许 NULL，同时该字段是可变长字段 varchar。

所以 idx_name 索引的索引长度=36*3+1+2=111，恰如施行规划中显示的值。

2.4.2 其他定长类型

关于定长类型的字段，其索引长度与它的数据类型长度是一致的。

数据类型	长度
int	4
bigint	8
date	3
datetime	8
timestamp	4
float	4
double	8

需要注意的是，若该字段允许默许值为 NULL，与字符类型同样，其索引长度也需要加上1。

mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39366 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec) 

如上面这个示例（本示例中索引只用到了 age 字段），age 字段为 int 类型，其索引长度本应为 4，但因为 age 字段默许允许为 NULL，所以它的索引长度就酿成了5。

2.5 rows in Explain

扫描行数在施行规划中其实是一个估值，MySQL 会选中 N 个不一样的索引数据页，盘算均匀值得到单页索引基数，然后再乘以索引页面数，就得到了扫描行数的估值。

扫描行数就是优化器考量索引施行效率的因素之一，个别而言扫描行数越少，施行效率越高。

2.6 Extra in Explain

施行规划中 Extra 字段的常见类型有：

• Using index: 运用了遮盖索引，以以免回表
• Using index condition: 运用了索引下推，具体可以看我的上一篇博客 —— 我所了解的MySQL(二)索引
• Using where: 表示MySQL 会通过 where 前提过滤记载
  • 全表扫描：where 中有该表字段作为搜寻前提
  • 扫描索引树：where 中包括索引字段以外的其他字段作为搜寻前提
• Using temporary: MySQL 在对查询效果排序时会运用暂时表
• Using filesort: 对效果进行外部索引排序（文件排序），排序不走索引
  • 数据较少时在内存中排序，数据较多时在磁盘中排序
  • 尽量以免该信息涌现在施行规划中

相干免费学习举荐：mysql数据库(视频)

以上就是我所了解的MySQL之三：施行规划的细致内容，更多请关注 百分百源码网 其它相干文章！