mysql explain type

explain执行计划中type字段分为以下几种：

ALL        INDEX        RANGE        REF        EQ_REF        CONST,SYSTEM        NULL

从左至右，性能从最差到最好

 

type = ALL，全表扫描，MYSQL扫描全表来找到匹配的行

（因为film表中rating不是索引）

mysql> explain extended select * from film where rating > 9\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: film

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1024

     filtered: 100.00

        Extra: Using where

1 row in set, 1 warning (0.00 sec)

 

type = index，索引全扫描，MYSQL遍历整个索引来查找匹配的行。（虽然where条件中没有用到索引，但是要取出的列title是索引包含的列，所以只要全表扫描索引即可，直接使用索引树查找数据）

mysql> explain select title from film\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: film

         type: index

possible_keys: NULL

          key: idx_title

      key_len: 767

          ref: NULL

         rows: 1024

        Extra: Using index

1 row in set (0.00 sec)

 

type = range ，索引范围扫描，常见于<、<=、>、>=、between等操作符（因为customer_id是索引，所以只要查找索引的某个范围即可，通过索引找到具体的数据）

mysql> explain select * from payment where customer_id > 300 and customer_id < 350\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: payment

         type: range

possible_keys: idx_fk_customer_id

          key: idx_fk_customer_id

      key_len: 2

          ref: NULL

         rows: 1294

        Extra: Using where

1 row in set (0.01 sec)

 

type = ref ，使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

 

（1）使用非唯一性索引customer_id单表查询

mysql> explain select * from payment where customer_id = 350\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: payment

         type: ref

possible_keys: idx_fk_customer_id

          key: idx_fk_customer_id

      key_len: 2

          ref: const

         rows: 23

        Extra:

1 row in set (0.00 sec)

 

（2）使用非唯一性索引联表查询（由于customer_id在a表中不是主键，是普通索引（非唯一），所以是ref）

mysql> explain select b.*, a.* from payment a ,customer b where a.customer_id = b.customer_id\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 541

        Extra:

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: ref

possible_keys: idx_fk_customer_id

          key: idx_fk_customer_id

      key_len: 2

          ref: sakila.b.customer_id

         rows: 14

        Extra:

2 rows in set (0.00 sec)  

 

type = eq_ref，相对于ref来说就是使用的是唯一索引，对于每个索引键值，只有唯一的一条匹配记录（在联表查询中使用primary key或者unique key作为关联条件）

（在film和film_text中film_id都是主键，即都是唯一索引）

mysql> explain select * from film a ,film_text b where a.film_id = b.film_id\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1000

        Extra:

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: eq_ref

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 2

          ref: sakila.b.film_id

         rows: 1

        Extra: Using where

2 rows in set (0.00 sec)

 

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

mysql> explain select * from film  where film_id = 1\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: film

         type: const

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 2

          ref: const

         rows: 1

        Extra:

1 row in set (0.02 sec)

 

注释：如果上表中film表中只有一行数据，那么type就是system。

 

 

type = NULL，MYSQL不用访问表或者索引就直接能到结果。

mysql> explain select 1 from dual  where 1\G （dual是一个虚拟的表，可以直接忽略）

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: NULL

         type: NULL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: No tables used

1 row in set (0.00 sec)

 

mysql> select 1+1 from dual;

+-----+

| 1+1 |

+-----+

|   2 |

+-----+

1 row in set (0.05 sec)

 

explain extended

mysql> explain extended select sum(amount) from customer a ,payment b where 1 = 1 and a.customer_id = b.customer_id and email = '[e-mail]'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: a

         type: ALL

possible_keys: PRIMARY

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 541

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: b

         type: ref

possible_keys: idx_fk_customer_id

          key: idx_fk_customer_id

      key_len: 2

          ref: sakila.a.customer_id

         rows: 14

     filtered: 100.00

        Extra: 

2 rows in set, 1 warning (0.00 sec)

 

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = '[e-mail]'))

1 row in set (0.00 sec)