测试数据创建

创建数据

-- 创表
CREATE TABLE `t_user` ( 
	`id` int(11) NOT NULL AUTO_INCREMENT,
	NAME varchar(50) DEFAULT NULL,
	PRIMARY KEY (`id`),
    KEY `idx_name` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table `t_user_balance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  uid int(11) NOT NULL,
  balance int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 插入数据
insert into t_user values(1,'yangl');
insert into t_user values(2,'zhangsan');
insert into t_user values(3,'lisi');
insert into t_user values(4,'wangwu');

insert into t_user_balance values(1, 1, 100);
insert into t_user_balance values(2, 2, 200);
insert into t_user_balance values(3, 3, 300);
insert into t_user_balance values(4, 4, 400);

查看 explain 结果

mysql> EXPLAIN SELECT * FROM t_user AS tuser, t_user_balance AS usba WHERE 1 AND tuser.id = 1 AND tuser.id = usba.uid;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tuser | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | usba  | ref   | idx_uid       | idx_uid | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.04 sec)

mysql>

explain结果列说明

【id列】

mysql> EXPLAIN select * from ( select id from t_user) as tmp;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    4 | NULL        |
|  2 | DERIVED     | t_user     | index | NULL          | PRIMARY | 4       | NULL |    4 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.05 sec)

mysql>

id 列是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。
id 的顺序是按 select 出现的顺序增长的。
id 列的值越大执行优先级越高越先执行，id 列的值相同则从上往下执行，id列的值为NULL最后执行。

【select_type列】

mysql> EXPLAIN select * from ( select * from t_user) as tmp UNION select * from t_user;
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL            |
|    2 | DERIVED      | t_user     | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL            |
|    3 | UNION        | t_user     | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL            |
| NULL | UNION RESULT | <union1,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
4 rows in set (0.03 sec)

mysql>

select_type 列的值标明查询的类型：
- simple ：简单select（不使用union或子查询）。
- primary ：最外面的select。
- union ：union中的第二个或后面的select语句。
- dependent union ：union中的第二个或后面的select语句，取决于外面的查询。
- union result ：union的结果。
- subquery ：子查询中的第一个select。
- dependent subquery ：子查询中的第一个select，取决于外面的查询。
- derived ：导出表的select（from子句的子查询）。

【table列】

table 列的结果表明当前行对应的 select 正在访问哪个表。

当查询的子句中有子查询时， table 列是格式，表示当前的 select 依赖 id=N 结果行对应的查询，要先执行 id 序号 = N 的查询。
当存在 union 时，UNION RESULT 的 table 列的值为 < unionN1 , N2 > ， N1 和 N2 表示参与 union 的 select 行的 id 序号。

【type列】

type 列的结果表明当前行对应的 select 的关联类型或访问类型，也就是优化器决定怎么查找数据表中的行，以及查找数据行记录的大概范围。
该列的取值优化程度的优劣，从最优到最差依次为：null > system > const > eq_ref > ref > range > index > ALL。一般来说，要保证查询达到 range 级别，最好达到 ref 。
- null：不需要查表，直接通过索引就能查出结果
- system：系统表，少量数据，往往不需要进行磁盘IO
- const：常量连接
- eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
- ref：非主键非唯一索引等值扫描
- range：范围扫描
- index：索引树扫描
- ALL：全表扫描(full table scan)

1、null

MySQL 优化器在优化阶段分解查询语句，在优化过程中就已经可以得到结果，那么在执行阶段就不用再访问表或索引。
这时的函数 min ，在索引列 user_id 中选取最小值，可以直接查找索引来完成，不需要执行时再访问数据表。

mysql> EXPLAIN select min(id) from t_user;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.04 sec)

2、system

从系统库 mysql 的系统表 proxies_priv 里查询数据，这里的数据在 Mysql 服务启动时候已经加载在内存中，不需要进行磁盘 IO 。

官方文档中的解释：该表只有一行（=系统表）。这是const联接类型的特例

mysql> EXPLAIN select * from mysql.proxies_priv;
+----+------------+------------+--------+--------------+------+---------+------+-------+-------+
| id | select_type | table       | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+------------+------------+--------+--------------+------+---------+------+-------+-------+
|  1 | SIMPLE     | proxies_priv | system | NULL         | NULL | NULL    | NULL |     1 | NULL  |
+----+------------+------------+--------+--------------+------+---------+------+-------+-------+
1 row in set (0.03 sec)

mysql>

3、const

id是主键(primary key)，连接部分是常量1，通过索引一次就能找到，速度非常快

场景：
- 命中主键 (primary key) 或者唯一索引 (unique)
- 被连接的部分是一个常量值(const)

mysql> EXPLAIN select * from t_user where id = 1;
+----+------------+--------+-------+--------------+----------+---------+-------+-------+-------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref   | rows | Extra |
+----+------------+--------+-------+--------------+----------+---------+-------+-------+-------+
|  1 | SIMPLE     | t_user | const | PRIMARY     | PRIMARY | 4       | const |     1 | NULL  |
+----+------------+--------+-------+--------------+----------+---------+-------+-------+-------+
1 row in set (0.03 sec)

mysql>

3、eq_ref

primary key（主键）或 unique key（唯一键）索引的所有构成部分被 join 使用，只会返回一条符合条件的数据行。这是仅次于 const 的连接类型。
- 场景：
  1. 联表( join )查询
  2. 命中主键(primary key)或者非空唯一索引(unique not null)
  3. 等值连接

mysql> 
mysql> EXPLAIN SELECT * FROM t_user_balance AS usba LEFT JOIN t_user AS tuser on tuser.id = usba.uid;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                       | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
|  1 | SIMPLE      | usba  | ALL    | NULL          | NULL    | NULL    | NULL                      |    1 | NULL  |
|  1 | SIMPLE      | tuser | eq_ref | PRIMARY       | PRIMARY | 4       | test_transaction.usba.uid |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
2 rows in set (0.05 sec)

mysql>

4、ref

ref 与 eq_ref 相比， ref 类型不是使用 primary key （主键）或 unique key （唯一键）等唯一索引，而是使用普通索引或者联合唯一性索引的部分前缀，索引和某个值相比较，可能会找到符合条件的多个数据行。
- 场景：
  1. 联表查询
  2. 普通非唯一索引

普通索引

如下示例，使用的 name 是普通索引

mysql> EXPLAIN select * from t_user WHERE name = 'yangl';
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name      | idx_name | 153     | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.04 sec)

mysql>

关联表查询

mysql> EXPLAIN SELECT * FROM t_user_balance AS usba LEFT JOIN t_user AS tuser on tuser.id = usba.uid;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                       | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
|  1 | SIMPLE      | usba  | ALL    | NULL          | NULL    | NULL    | NULL                      |    4 | NULL  |
|  1 | SIMPLE      | tuser | eq_ref | PRIMARY       | PRIMARY | 4       | test_transaction.usba.uid |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
2 rows in set (0.04 sec)

mysql>

5、range

出现在 in(),between ,> ,<, >= 等操作符中。使用一个索引来查询给定范围的行。

range 比较好理解，它是索引上的范围查询，它会在索引上扫码特定范围内的值

in

mysql> EXPLAIN select * from t_user where id in (1,2);
+----+------------+--------+-------+--------------+----------+---------+------+-------+-----------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+------------+--------+-------+--------------+----------+---------+------+-------+-----------------------+
|  1 | SIMPLE     | t_user | index | PRIMARY     | idx_name | 153     | NULL |     4 | Using where; Using index |
+----+------------+--------+-------+--------------+----------+---------+------+-------+-----------------------+
1 row in set (0.04 sec)

mysql>

between

mysql> EXPLAIN select * from t_user where id BETWEEN 1 and 3;
+----+------------+--------+-------+--------------+----------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+------------+--------+-------+--------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE     | t_user | range | PRIMARY     | PRIMARY | 4       | NULL |     3 | Using where |
+----+------------+--------+-------+--------------+----------+---------+------+-------+-------------+
1 row in set (0.04 sec)

mysql>

<,>,<=,>=

mysql> EXPLAIN select * from t_user where id > 2;
+----+------------+--------+-------+--------------+----------+---------+------+-------+-----------------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+------------+--------+-------+--------------+----------+---------+------+-------+-----------------------+
|  1 | SIMPLE     | t_user | index | PRIMARY     | idx_name | 153     | NULL |     4 | Using where; Using index |
+----+------------+--------+-------+--------------+----------+---------+------+-------+-----------------------+
1 row in set (0.04 sec)

mysql>

6、index

扫描全表索引（ index 是从索引中读取的，所有字段都有索引，而 all 是从硬盘中读取），比ALL要快。

mysql> EXPLAIN select * from t_user ;
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_name | 153     | NULL |    4 | Using index |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql>

7、all

即全表扫描，需要从头到尾去查找所需要的行。一般这种情况下这需要增加索引来进行查询优化了

mysql> EXPLAIN select * from t_user_balance;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user_balance | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.04 sec)

mysql>

总结

type类型从快到慢：system > const > eq_ref > ref > range > index > ALL
作为一名合格的后端开发者应该熟悉掌握 Explain
结合业务建立正确索引，而不是每个字段建立索引（滥用）

【possible_keys 列】

这一列的结果表明查询可能使用到哪些索引。但有些时候也会出现出现 possible_keys 列有结果，而后面的 key 列显示 null 的情况，这是因为此时表中数据不多，优化器认为查询索引对查询帮助不大，所以没有走索引查询而是进行了全表扫描。

如果 possible_keys 列的结果是 null ，则表明没有相关的索引。这时，可以通过优化 where 子句，增加恰当的索引来提升查询性能。

【key 列】

这一列表明优化器实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 null。

【key_len 列】

这一列表明了在索引里使用的字节数，通过这个值可以大致估算出具体使用了联合索引中的前几个列。
key_len计算规则这里不再赘述，不同的数据类型所占的字节数是不一致的。

【ref 列】

这一列表明了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名，如user.user_id

【rows 列】

这一列表明优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。

【Extra 列】

顾名思义，这一列表明的是额外信息，这一列的取值对优化SQL非常有参考意义。常见的重要取值如下：

using index

mysql> EXPLAIN select * from t_user ;
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user | index | NULL          | idx_name | 153     | NULL |    4 | Using index |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql>

所有被查询的字段都是索引列(称为覆盖索引),并且where条件是索引的前导列，出现这样的结果，是性能高的表现。

using where


mysql> EXPLAIN select * from t_user_balance where balance = 110;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user_balance | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql>

被查询的列未被索引覆盖，where条件也并非索引的前导列，表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。

using where Using index

mysql> EXPLAIN select * from t_user WHERE name = 'yangl';
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t_user | ref  | idx_name      | idx_name | 153     | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.04 sec)

mysql>

被查询的列被索引覆盖，并且where条件是索引列之一但是不是索引的前导列，也就是没有办法直接通过索引来查询到符合条件的数据

null

mysql> EXPLAIN select * from t_user_balance where id = 1;
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t_user_balance | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.04 sec)

mysql>

被查询的列没有被索引覆盖，但 where 条件是索引的前导列，此时用到了索引，但是部分列未被索引覆盖，必须通过“回表查询”来实现，不是纯粹地用到了索引，也不是完全没用到索引

using index condition

与 using where 类似，查询的列不完全被索引覆盖，where 条件中是一个前导列的范围；这种情况未能通过示例显现，可能跟MySQL版本有关系。

using temporary

mysql> EXPLAIN select distinct balance from t_user_balance;
+----+-------------+----------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+----------------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | t_user_balance | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary |
+----+-------------+----------------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.04 sec)

mysql>

这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的，用索引来优化。创建临时表的情况：distinct，group by，orderby，子查询等

mysql> EXPLAIN select distinct id from t_user_balance; -- id 是索引列
+----+-------------+----------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+-----------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user_balance | index | PRIMARY,idx_uid | idx_uid | 4       | NULL |    4 | Using index |
+----+-------------+----------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql>

usingfilesort

mysql> EXPLAIN select balance from t_user_balance  order by balance asc;
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t_user_balance | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.04 sec)

mysql>

在使用 order by 的情况下出现，mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时 mysql 会根据连接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。

mysql> EXPLAIN select id from t_user_balance order by id asc; -- id 是索引列
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user_balance | index | NULL          | PRIMARY | 4       | NULL |    4 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.04 sec)

mysql>

参考：

https://www.cnblogs.com/yycc/p/7338894.html

https://juejin.cn/post/6844904149864169486

yangl的博客

MySQL explain详解

测试数据创建

explain结果列说明

【id列】

【select_type列】

【table列】

【type列】

1、null

2、system

3、const

3、eq_ref

4、ref

普通索引

关联表查询

5、range

in

between

<,>,<=,>=

6、index

7、all

总结

【possible_keys 列】

【key 列】

【key_len 列】

【ref 列】

【rows 列】

【Extra 列】

using index

using where

using where Using index

null

using index condition

using temporary

usingfilesort

测试数据创建

explain结果列说明

【id列 】

【select_type列 】

【table列 】

【type列 】

1、null

2、system

3、const

3、eq_ref

4、ref

普通索引

关联表查询

5、range

in

between

<,>,<=,>=

6、index

7、all

总结

【possible_keys 列】

【key 列】

【key_len 列】

【ref 列】

【rows 列】

【Extra 列】

using index

using where

using where Using index

null

using index condition

using temporary

usingfilesort

【id列】

【select_type列】

【table列】

【type列】