SQL优化系列(三) EXPLAIN详解3

一、概述

在日常工作中，我们要看系统中有哪些慢SQL，会开启慢查询去记录一些执行时间比较久的SQL语句，但是这还没完，找到这些慢SQL后，我们要对这些SQL进行分析，比较常用的方法就是使用EXPLAIN去查看这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描等。前两期给大家介绍了EXPLAIN中id、select_type、table这几个字段的内容，今天我们就带大家一起来学习后面的内容。

 

二、type   

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

 

所有的type类型已性能从好到坏排序的话：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

 

常用的类型从好到坏排序：system > const > eq_ref > ref > range > index > ALL。

 

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system。

mysql> EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE id = 1) d1;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者nique key作为关联条件。​​​​​​​

mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    9 | NULL  |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | test.t1.id |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+
2 rows in set (0.00 sec)

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。​​​​​​​

mysql> EXPLAIN SELECT * FROM t1 WHERE col1 = 'a';
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | idx_t1_col1Col2Col3 | idx_t1_col1Col2Col3 | 23      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

range:只检索给定范围的行，使用一个索引来选择行ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。​​​​​​​

mysql> EXPLAIN SELECT * FROM t1 WHERE id between 1 AND 5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE id in (1, 2, 6);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 8       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

index: Full Index Scan，index与ALL区别为index类型只遍历索引树。​​​​​​​

mysql> EXPLAIN SELECT id FROM t1;
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | idx_t1_id2 | 9       | NULL |    9 | Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。​​​​​​​

mysql> EXPLAIN SELECT * FROM t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    9 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)