Explain 执行计划

Explain 执行计划

概述

示例

mysql> select * from user ;
+----+----------+----------+---------------------+
| id | username | password | date                |
+----+----------+----------+---------------------+
|  1 | guo      | 111      | 2018-05-20 09:55:47 |
|  2 | root     | aaa      | 2018-05-20 09:55:50 |
|  3 | 11111    | 22222    | 2018-05-20 09:55:51 |
|  4 | 1        | 1        | 2018-05-20 09:55:56 |
|  5 | a        | b        | 2018-05-20 09:55:54 |
|  7 | a        | b        | 2018-05-20 09:56:58 |
+----+----------+----------+---------------------+
6 rows in set

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 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set

说明

• id ：DML 语句的执行顺序，id 值越大优先级别越高；若包含子查询，则子查询的优先级别要高于主查询
• key_len : 索引长度，用户判断是否使用到了索引，使用了多少列
  
  • 如果是单列索引，长度是一样的；如果是组合索引，长度为该查询中使用到了多少列
  • key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中

类型分类	数据类型	计算公式	备注说明
可空	null	+1	如果字段可空索引长度 + 1
字符	char	length * 编码格式	UTF-8:3 ; GBK：2 ；latin:1
字符	varchar	length * 编码格式 + 2	UTF-8:3 ; GBK：2 ；latin:1
数值	int	4	-
数值	tinyint	1	-
时间	date	3	-
时间	timestamp	4	-

mysql> desc test ;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(10)     | NO   | PRI | NULL              | auto_increment              |
| name        | varchar(10) | NO   |     | NULL              |                             |
| sex         | char(1)     | YES  |     | NULL              |                             |
| age         | tinyint(3)  | NO   |     | NULL              |                             |
| create_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| update_time | date        | NO   |     | NULL              |                             |
+-------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set

mysql> alter table test add index idx_name 
(`name`,`sex`,`age`,`create_time`,`update_time`);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id from test where id = 1 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set

mysql> explain select * from test where name = "1" ;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_name      | idx_name | 32      | const |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set

-- key_len : 10*3 +2

mysql> explain select * from test where name = "1" and sex = "1" ;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_name      | idx_name | 36      | const,const |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------+------+----------+-------------+
1 row in set
-- key_len : 10 * 3 + 3 + 1
mysql> explain select * from test where name = "1" and sex = "1" and age = 1 ;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_name      | idx_name | 37      | const,const,const |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
1 row in set

mysql> explain select * from test where name = "1" and sex = "1" and age = 1 and create_time = "2018-07-29 00:00:00" ;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_name      | idx_name | 41      | const,const,const,const |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------------+------+----------+-------------+
1 row in set

mysql> explain select * from test where name = "1" and sex = "1" and age = 1 and create_time = "2018-07-29 00:00:00" and update_time = "2018-07-29" ;
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_name      | idx_name | 44      | const,const,const,const,const |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------------------+------+----------+-------------+
1 row in set

• filtered : 返回结果的行占需要读到的行(rows列的值)的百分比
  
  • filtered值只对index和all的扫描有效
  • filtered的值真正有效的场合要where条件中出现的列上建有索引，又要执行计划不走索引的range或ref扫描，而走全表扫描(all)或覆盖索引(index)扫描，所以可以说这个filtered列几乎无用