每日MySQL之021：EXPLAIN输出中的Join Types

假定有以下表的创建语句

mysql> CREATE TABLE t1(id int, age int, KEY `idx1` (`id`));
mysql> CREATE TABLE `t2` ( `id` int(11),`name` char(15));
mysql> CREATE TABLE t3(id int not null primary key, name char(30));

mysql> INSERT INTO `t1` VALUES (1001,24),(1002,25),(1003,43),(1004,44),(1006,31),(1005,23);
mysql> INSERT INTO `t2` VALUES (1002,'name1'),(1005,'name2'),(1007,'name3'),(1010,'name4');
mysql> INSERT into t3 VALUES(10086, 'Mobile'),(10011, 'Unicom'),(95583,'CommercialBank'),(12355,'taxiBue'),(1010,'aa');
           

EXPLAIN输出中的type列描述了表的join方式，下面列出了join方式，效果从好到差：

• system

表中只有一行（=系统表）。这是const类型的一个特例。(未能模拟）

• const

表里最多只有一行匹配。如果把 PRIMARY KEY 或者 UNIQUE 索引和常数比较，就可能会使用const，示例：

mysql> EXPLAIN SELECT * FROM t3 where ID = 10011;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
           

• eq_ref

One row is read from this table for each combination of rows from the previous tables.只有索引为 PRIMARY KEY 或者 UNIQUE NOT NULL 时才会使用：

mysql> EXPLAIN SELECT * FROM t3, t2 WHERE t3.id = t2.id; 
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        |    4 |   100.00 | Using where |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test1.t2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
           

• ref

和eq_ref类似，但索引不能是 PRIMARY KEY 和 UNIQUE index:

mysql>  EXPLAIN SELECT * FROM t1 where ID = 10011;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx1          | idx1 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; 
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL        |    4 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx1          | idx1 | 5       | test1.t2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
           

• fulltext

使用了 FULLTEXT index. (未能模拟）

• ref_or_null

和ref类似，外多加一项判断是否为空值：

mysql> EXPLAIN SELECT * FROM t1 where ID = 10011 or ID = NULL;
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref_or_null | idx1          | idx1 | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
           

• index_merge

使用了index merge时(未能模拟）

• unique_subquery

This type replaces eq_ref for some IN subqueries of the following form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.(未能模拟）

• index_subquery

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique

indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)(未能模拟）

• range

使用了索引，并且读取的数据是一个范围内的数据时：

mysql> EXPLAIN SELECT * FROM t1 WHERE ID < 1004;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx1          | NULL | NULL    | NULL |    6 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE ID > 1004;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx1          | idx1 | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
           

• index

SQL只访问索引列时：

mysql> EXPLAIN SELECT id  FROM t1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx1 | 5       | NULL |    6 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
           

• ALL

A full table scan is done for each combination of rows from the previous tables：

mysql> EXPLAIN SELECT * FROM t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)