select version()：
5.7.21
EXPLAIN 是什么
MySQL 提供了一個 EXPLAIN 命令, 它可以對 SQL 語句進行分析, 并輸出 SQL 執(zhí)行的詳細(xì)信息, 以供開發(fā)人員針對性優(yōu)化.
例如分析一條 SELECT 語句
EXPLAIN SELECT * FROM `user` WHERE id = 1
EXPLAIN 結(jié)果中的type字段
Tips：常見的掃描方式
- system：系統(tǒng)表诊霹，少量數(shù)據(jù)漩氨，往往不需要進行磁盤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
上例中御铃，從系統(tǒng)庫mysql的系統(tǒng)標(biāo)proxies_priv里查詢數(shù)據(jù)父款，這里的數(shù)據(jù)在Mysql服務(wù)啟動時候已經(jīng)加載在內(nèi)存中贬墩，不需要進行磁盤IO因妇。
官方文檔中的解釋：該表只有一行（=系統(tǒng)表）悼沿。這是const聯(lián)接類型的特例
2.const
模擬數(shù)據(jù)
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分析結(jié)果
上例中id是主鍵(primary key)丧叽，連接部分是常量1，通過索引一次就能找到期虾，速度非吃快
場景：
- 命中
主鍵(primary key)或者唯一索引(unique) - 被連接的部分是一個
常量值(const)
3.eq_ref
模擬數(shù)據(jù)
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分析結(jié)果
上例中對于前表user表中的每一行（row），對應(yīng)后user_balance表只有一行被掃描镶苞，這類掃描的速度也非常的快
場景：
-
聯(lián)表(join)查詢 - 命中
主鍵(primary key)或者非空唯一索引(unique not null) -
等值連接
4.ref
模擬數(shù)據(jù)
同eq_ref模擬數(shù)據(jù)區(qū)別：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分析結(jié)果
聯(lián)表查詢
由于后表使用了普通非唯一索引喳坠，對于前表user表的每一行(row)，后表user_balance表可能有多于一行的數(shù)據(jù)被掃描
單表查詢
當(dāng)id改為普通非唯一索引后茂蚓，常量的連接查詢壕鹉，也由const降級為了ref剃幌，因為非唯一索引所以有多于一行的數(shù)據(jù)被可能被掃描
ref每一次匹配可能有多行數(shù)據(jù)返回，雖然它比eq_ref要慢晾浴，但它仍然是一個很快的join類型
場景：
- 聯(lián)表查詢
- 普通非唯一索引
5.range
模擬數(shù)據(jù)
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分析結(jié)果
between
in
>,>=,<,<=
range比較好理解负乡，它是索引上的范圍查詢，它會在索引上掃碼特定范圍內(nèi)的值
6.index
話外音：當(dāng)前測試表為InnoDb脊凰，MyISAM 內(nèi)置了一個計數(shù)器抖棘，count()時它直接從計數(shù)器中讀
index類型，需要掃描索引上的全部數(shù)據(jù)狸涌，它僅比全表掃描快一點
7.ALL
模擬數(shù)據(jù)
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分析結(jié)果
如果id上不建索引,則全表掃描
總結(jié)
- type類型從快到慢：system>const>eq_ref>ref>range>index>ALL
- 作為一名合格的后端開發(fā)者應(yīng)該熟悉掌握Explain
- 結(jié)合業(yè)務(wù)建立正確索引切省，而不是每個字段建立索引（濫用）
