MySQL explain type详解

andyyuduo

　　对于MySQL执行计划的获取，我们可以通过explain方式来查看，explain方式看似简单，实际上包含的内容很多，尤其是输出结果中的type类型列。理解这些不同的类型，对于我们SQL优化举足轻重。
　　一、EXPLAIN 语句中type列的值
类型含义system表只有一行const表最多只有一行匹配，通用用于主键或者唯一索引比较时eq_ref　　每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref如果每次只匹配少数行，那就是比较好的一种，使用=或，可以是左覆盖索引或非主键或非唯一键fulltext全文搜索ref_or_null与ref类似，但包括NULLindex_merge　　表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。
　　这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话
unique_subquery　　在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。
　　PS：所以不一定in子句中使用子查询就是低效的！
index_subquery同上，但把形如”select non_unique_key_column“的子查询替换range 常数值的范围index　　a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；
　　b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；
　　c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；
　　d.如单独出现，则是用读索引来代替读行，但不用于查找
all全表扫描　　二、连接类型部分示例
　　1、all
　　-- 环境描述
　　(root@localhost) [sakila]> show variables like 'version';
　　+---------------+--------+
　　| Variable_name | Value  |
　　+---------------+--------+
　　| version     | 5.6.26 |
　　+---------------+--------+
　　MySQL采取全表遍历的方式来返回数据行，等同于Oracle的full table scan
　　(root@localhost) [sakila]> explain select count(description) from film;
　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+

　　|>　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　| 1 | SIMPLE    | film  | ALL  | NULL      | NULL | NULL   | NULL | 1000 | NULL  |
　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　2、index
　　MySQL采取索引全扫描的方式来返回数据行，等同于Oracle的full index scan

　　(root@localhost) [sakila]> explain select>　　*************************** 1. row ***************************
　　id: 1
　　select_type: SIMPLE
　　table: film
　　type: index
　　possible_keys: NULL

　　key:>　　key_len: 767
　　ref: NULL
　　rows: 1000
　　Extra: Using index
　　1 row in set (0.00 sec)
　　3、  range
　　索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、等的查询
　　等同于Oracle的index range scan
　　(root@localhost) [sakila]> explain select * from payment where customer_id>300 and customer_id
　　key:>　　key_len: 2
　　ref: NULL
　　rows: 2637
　　Extra: Using where
　　1 row in set (0.00 sec)
　　(root@localhost) [sakila]> explain select * from payment where customer_id in (200,300,400)\G
　　*************************** 1. row ***************************
　　id: 1
　　select_type: SIMPLE
　　table: payment
　　type: range

　　possible_keys:>
　　key:>　　key_len: 2
　　ref: NULL
　　rows: 86
　　Extra: Using index condition
　　1 row in set (0.00 sec)
　　4、ref
　　非唯一性索引扫描或者，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
　　(root@localhost) [sakila]> explain select * from payment where customer_id=305\G
　　*************************** 1. row ***************************
　　id: 1
　　select_type: SIMPLE
　　table: payment
　　type: ref

　　possible_keys:>
　　key:>　　key_len: 2
　　ref: const
　　rows: 25
　　Extra:
　　1 row in set (0.00 sec)
　　idx_fk_customer_id为表payment上的外键索引，且存在多个不不唯一的值，如下查询
　　(root@localhost) [sakila]> select customer_id,count(*) from payment group by customer_id
　　-> limit 2;
　　+-------------+----------+
　　| customer_id | count(*) |
　　+-------------+----------+
　　|       1 |     32 |
　　|       2 |     27 |
　　+-------------+----------+
　　-- 下面是非唯一前缀索引使用ref的示例

　　(root@localhost) [sakila]> create index>　　Query OK, 599 rows affected (0.09 sec)
　　Records: 599  Duplicates: 0  Warnings: 0
　　(root@localhost) [sakila]> select first_name,count(*) from customer group by first_name
　　-> having count(*)>1 limit 2;
　　+------------+----------+
　　| first_name | count(*) |
　　+------------+----------+
　　| JAMIE    |      2 |
　　| JESSIE  |      2 |
　　+------------+----------+
　　2 rows in set (0.00 sec)
　　(root@localhost) [sakila]> explain select first_name from customer where first_name='JESSIE'\G
　　*************************** 1. row ***************************
　　id: 1
　　select_type: SIMPLE
　　table: customer
　　type: ref

　　possible_keys:>
　　key:>　　key_len: 137
　　ref: const
　　rows: 2
　　Extra: Using where; Using index
　　1 row in set (0.00 sec)

　　(root@localhost) [sakila]>>　　Query OK, 599 rows affected (0.03 sec)
　　Records: 599  Duplicates: 0  Warnings: 0
　　--下面演示出现在join是ref的示例
　　(root@localhost) [sakila]> explain select b.*,a.* from payment a inner join
　　-> customer b on 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: 599
　　Extra: NULL
　　*************************** 2. row ***************************
　　id: 1
　　select_type: SIMPLE
　　table: a
　　type: ref

　　possible_keys:>
　　key:>　　key_len: 2
　　ref: sakila.b.customer_id
　　rows: 13
　　Extra: NULL
　　2 rows in set (0.01 sec)
　　5、eq_ref
　　类似于ref，其差别在于使用的索引为唯一索引，对于每个索引键值，表中只有一条记录与之匹配。
　　多见于主键扫描或者索引唯一扫描。
　　(root@localhost) [sakila]> explain select * from film a join film_text b
　　-> on a.film_id=b.film_id;
　　+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+

　　|>　　+----+-------------+-------+--------+---------------+---------+---------+----------+------+-------+
　　|  1 | SIMPLE   | b     | ALL   | PRIMARY    | NULL  | NULL  | NULL   | 1000 | NULL  |
　　|  1 | SIMPLE   | a     | eq_ref | PRIMARY    | PRIMARY | 2   | sakila.b.film_id | 1 | Using where |
　　+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------+

　　(root@localhost) [sakila]> explain select>　　+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

　　|>　　+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
　　|  1 | SIMPLE      | film  | const | PRIMARY  | PRIMARY | 2   | const |    1 | NULL  |
　　+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
　　6、const、system：
　　当MySQL对查询某部分进行优化，这个匹配的行的其他列值可以转换为一个常量来处理。
　　如将主键或者唯一索引置于where列表中，MySQL就能将该查询转换为一个常量
　　(root@localhost) [sakila]> create table t1(id int,ename varchar(20) unique);
　　Query OK, 0 rows affected (0.05 sec)
　　(root@localhost) [sakila]> insert into t1 values(1,'robin'),(2,'jack'),(3,'henry');
　　Query OK, 3 rows affected (0.00 sec)
　　Records: 3  Duplicates: 0  Warnings: 0
　　(root@localhost) [sakila]> explain select * from (select * from t1 where ename='robin')x;
　　+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+

　　|>　　+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
　　|  1 | PRIMARY |  | system | NULL | NULL  | NULL    | NULL  |    1 | NULL  |
　　|  2 | DERIVED  | t1     | const  | ename   | ename | 23      | const |    1 | NULL  |
　　+----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+
　　2 rows in set (0.00 sec)
　　7、type=NULL
　　MySQL不用访问表或者索引就可以直接得到结果
　　(root@localhost) [sakila]> explain select sysdate();
　　+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

　　|>　　+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
　　|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
　　+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
　　1 row in set (0.00 sec)