Mysql Explain之 type 详解
Mysql Explain之 type 详解EXPLAIN 是什么EXPLAIN 结果中的type字段1.system2.const3.eq_ref4.ref5.range6.index7.ALL总结EXPLAIN 是什么MySQL 提供了一个 EXPLAIN 命令, 它可以对 SQL 语句进行分析, 并输出 SQL 执行的详细信息, 以供开发人员针对性优化.例如分析一条 SELECT 语句EXP
Mysql Explain之 type 详解
EXPLAIN 是什么
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SQL 语句进行分析, 并输出 SQL 执行的详细信息, 以供开发人员针对性优化.
例如分析一条 SELECT 语句
EXPLAIN SELECT * FROM `user` WHERE id = 1
EXPLAIN 结果中的type字段
Tips:常见的扫描方式
- system:系统表,少量数据,往往不需要进行磁盘IO
- const:常量连接
- eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
- ref:非主键非唯一索引等值扫描
- range:范围扫描
- index:索引树扫描 ALL:全表扫描(full table scan)
type扫描方式由快到慢
system > const > eq_ref > ref > range > index > ALL
1.system
上例中,从系统库mysql的系统标proxies_priv里查询数据,这里的数据在Mysql服务启动时候已经加载在内存中,不需要进行磁盘IO。
官方文档中的解释:该表只有一行(=系统表)。这是const联接类型的特例
2.const
模拟数据
create table user (
id int primary key,
name varchar(20)
)engine=innodb;
insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
Explain分析结果
上例中id是主键(primary key),连接部分是常量1,通过索引一次就能找到,速度非常快
场景:
- 命中主键(primary key)或者唯一索引(unique)
- 被连接的部分是一个常量值(const)
3.eq_ref
模拟数据
create table user (
id int primary key,
name varchar(20)
)engine=innodb;
insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
create table user_balance (
uid int primary key,
balance int
)engine=innodb;
insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);
Explain分析结果
上例中对于前表user表中的每一行(row),对应后user_balance表只有一行被扫描,这类扫描的速度也非常的快
场景:
- 联表(join)查询
- 命中主键(primary key)或者非空唯一索引(unique not null)
- 等值连接
4.ref
模拟数据
同eq_ref模拟数据区别:user_balance表中的主键索引改为普通索引
create table user (
id int primary key,
name varchar(20)
)engine=innodb;
insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
create table user_balance (
uid int,
balance int,
index(uid)
)engine=innodb;
insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);
Explain分析结果
联表查询
由于后表使用了普通非唯一索引,对于前表user表的每一行(row),后表user_balance表可能有多于一行的数据被扫描
单表查询
当id改为普通非唯一索引后,常量的连接查询,也由const降级为了ref,因为非唯一索引所以有多于一行的数据被可能被扫描
ref每一次匹配可能有多行数据返回,虽然它比eq_ref要慢,但它仍然是一个很快的join类型
场景:
- 联表查询
- 普通非唯一索引
5.range
模拟数据
create table user (
id int primary key,
name varchar(20)
)engine=innodb;
insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');
Explain分析结果
between
in>
,>=,<,<=
range比较好理解,它是索引上的范围查询,它会在索引上扫码特定范围内的值
6.index
话外音:当前测试表为InnoDb,MyISAM 内置了一个计数器,count()时它直接从计数器中读
index类型,需要扫描索引上的全部数据,它仅比全表扫描快一点
7.ALL
模拟数据
create table user (
id int,
name varchar(20)
)engine=innodb;
insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');
Explain分析结果
如果id上不建索引,则全表扫描
总结
- type类型从快到慢:system>const>eq_ref>ref>range>index>ALL
- 作为一名合格的后端开发者应该熟悉掌握Explain
- 结合业务建立正确索引,而不是每个字段建立索引(滥用)
更多推荐
所有评论(0)