Mysql 执行计划之 type 类型

Mysql 执行计划之 type 类型

type列

连接类型

• system 该表只有一行, const 类型的特例。
• const 最多匹配一行，在查询开始时读取，因为只有一行，所以被认为恒定不变的（constants），因为只读一次，所以非常快。

​ 出现在基于主键或唯一键的等值查询

• eq_ref 表关联时如果表关联字段是主键或唯一索引字段，会是该情况。这是最好的表联接类型。
• ref 用于 = 匹配，但是关联字段不是主键或唯一键
• fulltext 使用FULLTEXT 索引执行联接。
• ref_or_null 这种连接类型类似于 ref，但是MySQL还会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询

​ 如：SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

• index_merge 使用索引合并优化。参考：8.2.1.3, “Index Merge Optimization”.
• unique_subquery 在in子查询中类似 eq_ref。如：value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery 类似于 unique_subquery。但子查询非唯一
• range 指定范围查询。ref 列是 NULL。操作符 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()
• index 类似 ALL，但只有索引树被扫描。两种情况：1.索引覆盖，这时 Extra列显示 Using index。 2.以索引排序全表扫描
• ALL 全表扫描

案例

create table t_type1(id int auto_increment primary key,uid int,name varchar(20)) engine='myISAM';
insert into t_type1 values(1,1,'cym');

create table t_type2 (id int auto_increment primary key,uid int,name varchar(20)) engine='myISAM';
insert into t_type2 values(1,1,'cym');
insert into t_type2 values(2,2,'cym');

-- ALL
desc select * from t_type2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_type2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

-- const
desc select * from t_type2 where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_type2 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

-- system
desc select * from t_type1;
+----+-------------+---------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_type1 | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+------+---------+------+------+----------+-------+

-- index
desc select id from t_type2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_type2 | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

-- range
alter table t_type2 add key(name);
desc select * from t_type2 where id>1;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_type2 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

--ref
desc select * from t_type2 where name='cym';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_type2 | NULL       | ref  | name          | name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+

-- eq_ref
alter table t_type2 add unique key(uid);
desc select t1.id,t2.name from t_type1 t1,t_type2 t2 where t1.uid=t2.uid and t1.id>1;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL       |    2 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | uid           | uid     | 5       | cym.t1.uid |    1 |   100.00 | NULL                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+------------------------------------+
-- 非唯一列关联时
desc select t1.id,t2.name from t_type1 t1,t_type2 t2 where t1.name=t2.name and t1.id>1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index condition                                   |
|  1 | SIMPLE      | t2    | NULL       | index | name          | name    | 83      | NULL |    2 |    50.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
desc select t1.id,t2.name from t_type1 t1,t_type2 t2 where t1.name=t2.name and t2.name='cym';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index condition                                   |
|  1 | SIMPLE      | t2    | NULL       | index | name          | name    | 83      | NULL |    2 |    50.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------+

-- - ref_or_null
desc select * from t_type2 where uid=2 or uid is null;
+----+-------------+---------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type        | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_type2 | NULL       | ref_or_null | uid           | uid  | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+