mysql analysis_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

表的存储引擎为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

TEX

by TTH,

version 4.03.

On 29 Dec 2012, 11:12.