MySQL Optimizer Analysis——Join types

 

MySQL Optimizer Analysis——Join types

Louis Hust

 

0  前言

通过EXPLAIN查看执行计划，其输出中较为重要的列为type列，此列决定了操作此表的类型，在MySQL内部，此列对应了 enum join_type。这里分别针对Manual中列举出来的各种不同的type，进行实例显示，让大家更加明确的了解各个type的实际意义， 有利于加深大家对explain输出的理解。

 

由于MySQL5.6对Optimizer的代码进行了重构，条理上更加清晰，故本实验的环境是:

Server version: 5.6.6-m9-debug-log Source distribution

 

0  Join Type实例讲解

 

0.0  system

Manual上的说法是表只有一行数据即为system类型，说的过于模糊，因为我试过innodb的表（默认引擎），全表查询根本不是system， 这不科学，经过代码的查看，发现并没这么简单，要想join type为system，有两点要求：

1. 表中数据行数小于等于1
    
2. 表的存储引擎为MyISAM，HEAP或ARCHIVE
    

第一条和manual一致，第二条是通过代码查看发现的，只有上述的三种引擎，其ha_table_flag才包含HA_STATS_RECORDS_IS_EXACT， 这个标志表示统计信息中统计的行数是精确的，不是模糊的。此标志决定了system类型的判断，点到为止， 具体的可以查看代码make_join_statistics。下面给出一个实例：

 

mysql> create table t1(c1 int, c2 int) engine = myisam;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.03 sec)

mysql> explain select * from t1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

 

0.0  const

此种类型的join type表示最多只有一个匹配的行，优化器判断一个表为const后，会读取相应的行，在之后的优化过程中会直接用读取出的常量代替 语句中引用到的此表的个列。当出现主键或唯一键和常量进行等值比较时，便会产生const。

mysql> create table t1(c1 int primary key, c2 int unique);
Query OK, 0 rows affected (0.26 sec)

mysql> insert into t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where c1=2;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where c2=2;
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | const | c2            | c2   | 5       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
1 row in set (0.01 sec)

 

0.0  eq_ref

eq_ref类型，首先要确定的是它是一种ref，即reference，在ref属性上又添加了等值（eq）的属性，即为eq_ref，这种类型 是较为常见的一种比较好的join type。用于根据primary key或者unique not null索引进行等值判断，当然不再是和const进行比较， 如果和const进行比较，那么就是const类型的表了。 而是和前面一张表的一条记录进行比较（MySQL的join是nest loop类型的，且是left deep树，故表的join是从左到右一条一条记录进行匹配的）。

 

mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t1 values(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t2(c1 int primary key, c2 int unique not null);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t2 values(1,1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.12 sec)

mysql> explain select * from t1, t2 where t1.c1=t2.c1;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL      |    2 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | opt.t1.c1 |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select * from t1, t2 where t1.c1=t2.c2;
+----+-------------+-------+--------+---------------+------+---------+-----------+------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref       | rows | Extra       |
+----+-------------+-------+--------+---------------+------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL | NULL    | NULL      |    2 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | c2            | c2   | 4       | opt.t1.c1 |    1 | Using index |
+----+-------------+-------+--------+---------------+------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

 

0.0  ref

ref相比叫eq_ref少了eq，即不需要等值的结果只有一行，但是还是需要reference，可以和const直接进行比较，也可以根据reference table中的列进行比较，即可以根据索引进行匹配，但匹配结果不许要唯一。

mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values(1,1), (2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t2(c1 int, c2 int, primary key(c1,c2));
Query OK, 0 rows affected (0.29 sec)

mysql> insert into t2 values(1,1), (2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain select * from t1,t2 where t1.c1=t2.c1;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL    | NULL    | NULL      |    2 | Using where |
|  1 | SIMPLE      | t2    | ref  | PRIMARY       | PRIMARY | 4       | opt.t1.c1 |    1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

 

0.0  ref_or_null

ref_or_null和ref相似，但是多了null的判断，where条件中可以对相应的索引key is null的判断。

mysql> create table t1(c1 int, c2 int, key(c1, c2));
Query OK, 0 rows affected (0.42 sec)

mysql> explain select * from t1 where c1=1 or c1 is null;
+----+-------------+-------+-------------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref_or_null | c1            | c1   | 5       | const |    2 | Using where; Using index |
+----+-------------+-------+-------------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

 

0.0  index_merge

index_merge是针对一个表的，根据where条件，需要利用到多个index分别进行查询，然后进行merge，merge操作就包括三种： intersect，union和sort_union。

mysql> create table t1(c1 int, c2 int, key(c1), key(c2));
Query OK, 0 rows affected (0.21 sec)
mysql> delimiter /
mysql> drop procedure if exists p1;
    -> /
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> create procedure p1(count int)
    -> begin
    -> set @i=1;
    -> repeat
    -> set @i=@i+1;
    -> insert into t1 values(@i,@i);
    -> until @i > count
    -> end repeat;
    -> end
    -> /
Query OK, 0 rows affected (0.03 sec)

mysql> call p1(1000);
    -> /
Query OK, 1 row affected (1.00 sec)

mysql> commit;
    -> /
Query OK, 0 rows affected (0.36 sec)
ql> delimiter ;
mysql> explain select * from t1 where c1=100 or c2 = 100;
+----+-------------+-------+-------------+---------------+-------+---------+------+------+---------------------------------+
| id | select_type | table | type        | possible_keys | key   | key_len | ref  | rows | Extra                           |
+----+-------------+-------+-------------+---------------+-------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | t1    | index_merge | c1,c2         | c1,c2 | 5,5     | NULL |    2 | Using union(c1,c2); Using where |
+----+-------------+-------+-------------+---------------+-------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

由于计划的选择需要统计IO和CPU代价，故需要初始化一些数据，才能使得index_merge的代价相对其他的计划是代价最小的。

 

0.0  range

range即范围查询，当然也需要利用到索引，主索引或者二级索引。

mysql> create table t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.18 sec)
mysql> explain select * from t1 where c1 > 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

 

0.0  index

二级索引上的全索引扫描，比主索引的全表扫描效率稍好，因为二级索引的I/O代价相对主索引较小。

mysql> create table t1(c1 int, c2 int, c3 varchar(100), key(c1, c2));
Query OK, 0 rows affected (0.23 sec)
mysql> explain select c1,c2 from t1 where c2 > 10;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | c1   | 10      | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

 

0.0  all

all表示主索引全表扫描，是性能最差的，如果计划中看到有大表的ALL，尽量通过建立二级索引或者一些参数设置，改变其执行计划。

mysql> drop table t1;
Query OK, 0 rows affected (0.08 sec)
mysql> create table t1(c1 int primary key, c2 int unique, c3 int)
    -> ;
Query OK, 0 rows affected (0.25 sec)
mysql> explain select * from t1 where c3 = 100;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

 

---

File translated from T_EX by T_TH, version 4.03.
On 29 Dec 2012, 11:12.