Mysql中的explain执行计划详解(1)

时间:2022-01-30 03:49:26

创建一个表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)