MySQL的explain用法 | 魁爷的黑作坊

explain示例1

explain select u_user.id, u_user.name, u_user.id, u_user_dep.*
from u_user
       left join u_user_dep on u_user.id = u_user_dep.user_id
where u_user.name like '%test%';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u_user	index	NULL	name	386	NULL	3752	Using where; Using index
1	SIMPLE	u_user_dep	ref	PRIMARY	PRIMARY	4	portal.u_user.id	1	NULL

explain示例2

explain SELECT id,url,insert_time FROM d_purge WHERE domain_id in(SELECT id FROM d_domain WHERE user_id =1341) AND d_purge.insert_time > '2019-04-01 00:00:00' AND id > 661695994 LIMIT 10000

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	d_purge	range	PRIMARY,domain_id,domain_id_type_insert_time_status,domain_id_status	PRIMARY	4	NULL	1	Using where
1	SIMPLE	d_domain	eq_ref	PRIMARY,user_id,user_id_status	PRIMARY	4	d_purge.domain_id	1	Using where

explain的各个属性含义

名称	含义	可能的值
id	查询的序列号
select_type	查询的类型	普通查询、联合查询、子查询
table	查询表名
type	联合查询使用的类型
possible_key	可能用到的查询	NULL：没有索引，需要查看where子句是否有索引
key	显示MySQL决定用哪个索引	如果没有索引被选择，则显示NULL
key_len	显示MySQL决定使用的键长度	具体见下面
ref	显示哪个字段或常数与key一起被使用
rows	需要遍历多少行才能查到数据
extra	检索方式	详见下面

key_len的相关（比较重要）

这里可以看到联合索引到底用了哪几个列的索引，以优化索引性能

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `age` tinyint(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_NAME_AGE_CREATEDAT` (`name`,`age`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# sql1
SELECT * FROM `user` WHERE name like 'asdf%' ORDER BY id desc;

# sql2
SELECT * FROM `user` WHERE name ='asdf' and age='10' ORDER BY id desc;

• sql1只使用到了索引的NAME部分，在utf8mb4的字符集下，1个字符占用4个字节，再加上两个字节存放字符的长度，还有个字节存放是否为NULL，所以key_len=803
• sql2除了用了索引的NAME部分，还用了age部分，加上tinyint的两个字节，所以key_len=805

extra的相关（贼重要）

• using where;using index; 使用了覆盖索引，不用回表，说明性能不错
• using where 使用了where，估计是直接用主键where，已经在原表查了
• using index condition 使用了where，但是需要回表
• impossible-where 不可能存在的数据
• using filesort 使用了 order by 或 group by 需要排序
• using temporary 使用临时表，一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化
• select tables optimized way 使用聚合函数

一般来说，type显示的访问类型是比较重要的指标，以下表格是从好到坏依次排列。保证查询至少达到range级，最好能达到ref级

值	含义	级别
system	系统表	非常好
const	读常量	非常好
eq_ref	最多匹配一条记录，一般是通过主键访问	非常好
ref	被驱动表索引引用	最好能到这里
fulltext	全文索引检索	还可以
ref_or_null	带空值的索引查询	还可以
index_merge	合并索引结果集	还可以
unique_subquery	子查询中返回的字段是唯一的组合或索引	还可以
index_subquery	子查询返回的是索引，但非主键	还可以
range	索引范围扫描	最低限度
index	全索引扫描	一般不能忍
ALL	全表扫描	坚决不能忍