Mysql中的explain执行计划详解（1）

来源：互联网发布：手机扫描条码软件编辑：程序博客网时间：2024/04/30 08:22

创建一个表test_explain，并添加入下的数据

mysql> create table test_explain( a int primary key, b int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test_explain value(1,1),(2,2),(3,3),(4,4),(5,5);

explian中的type字段：表示mysql在表中找到所需行的方式，或者叫访问类型，常见的取值有ALL,INDEX ,RANGE,REF,EQ_REF,CONST(SYSTEM),NULL

情况1：type=all,全表扫描，mysql遍历全表来找到匹配的行。

mysql> explain select b from test_explain where b>3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_explain
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)

情况2：type=index,索引扫描，MYSQL遍历整个索引来查询匹配的行

mysql> explain select a from test_explain where a>3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_explain
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 5
filtered: 40.00
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

情况3：type=range,索引扫描范围，常见于<,<=>,>=,between等操作符

mysql> explain select * from test_explain where a>3 and a<5\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_explain
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)

情况4：type=ref,非唯一索引扫描或唯一索引扫描的前缀扫描，返回匹配某个单独值的记录行

首先为之前创建的表test_explain表的列b增加一个非唯一索引，操作如下：

mysql> alter table test_explain add index(b);，接着的实验结果为：

mysql> explain select *from test_explain where b=3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_explain
partitions: NULL
type: ref
possible_keys: b
key: b
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)

情况5：type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是在多表连接中使用primary key或者unique index作为关联条件；注意的前提条件一定是多表连接中；

举例：新建一个表test_explain2

mysql> create table test_explain2( d int primary key,
-> e char(10) unique key,
-> f int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into test_explain2 values(1,'a',1),(2,'b',2);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test_explain2 values(3,'c',3),(4,'d',4);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

接着来进行type=eq_ref的试验验证；

mysql> explain SELECT *from test_explain tt,test_explain2 yy where tt.a=yy.d \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: yy
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tt
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.yy.d
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)

情况6：type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列值可以被优化器在当前查询中当做常量来处理，例如根据主键或者唯一索引unique key进行的查询；

mysql> explain select *from test_explain where a=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_explain
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

情况7：type=NULL，不用访问表或者索引就可以得到结果，如；

mysql> EXPLAIN SELECT 3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
1 row in set, 1 warning (0.00 sec)

0 0