|NO.Z.00149|——————————|BigDataEnd|——|Java&MySQL.高级.V21|——|MySQL.v22|EXPLAIN_type字段介绍|

一、[EXPLAIN_type字段介绍]

### --- type介绍
~~~     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

——>        system : 表仅有一行 (等于系统表)。这是const连接类型的一个特例,很少出现。
——>        const :表示通过索引 一次就找到了, const用于比较 primary key 或者 unique 索引. 因为只匹配一行数据,所以如果将主键 放在 where条件中, MySQL就能将该查询转换为一个常量

mysql> EXPLAIN SELECT * FROM L1 WHERE L1.id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | L1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

~~~     # eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配. 常见与主键或唯一索引扫描

EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id ;
mysql> EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id ;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | L1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | L2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_explain.L1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+

~~~     ref : 非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 这是比较常见连接类型.
~~~     # 未加索引之前

EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
mysql> EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | L1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | L2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

~~~     # 加索引之后

CREATE INDEX idx_title ON L2(title);
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
mysql> CREATE INDEX idx_title ON L2(title);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
+----+-------------+-------+------------+------+---------------+-----------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | ALL  | NULL          | NULL      | NULL    | NULL                  |    1 |   100.00 | Using where |
|  1 | SIMPLE      | L2    | NULL       | ref  | idx_title     | idx_title | 303     | test_explain.L1.title |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-----------------------+------+----------+-------------+

~~~     # range : 只检索给定范围的行,使用一个索引来选择行。

EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);

mysql> EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

——>        key显示使用了哪个索引. where 子句后面 使用 between 、< 、> 、in 等查询, 这种范围查询要比全表扫描好
——>        index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分组

EXPLAIN SELECT * FROM L1 ORDER BY id;
mysql> EXPLAIN SELECT * FROM L1 ORDER BY id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | L1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+

~~~     # ALL : 对于每个来自于先前的表的行组合,进行完整的表扫描。
~~~     # 一般来说,需要保证查询至少达到 range级别,最好能到ref

mysql> EXPLAIN SELECT * FROM L1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | L1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---