mysql 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 = ‘JANE.BENNETT@sakilacustomer.org‘\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` = ‘JANE.BENNETT@sakilacustomer.org‘))
1 row in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。

mysql explain type

标签：mysql