MySQL的explain用法

Author Avatar
WoodyXiong 11月 22, 2020
  • 在其它设备中阅读本文章

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 bygroup 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 全表扫描 坚决不能忍