MySQL 性能优化之 explain type 字段

创建表

create table s1 (
    id int auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    common_field varchar(100),
    primary key(id),
    index idx_key1(key1),
    unique index idx_key2(key2),
    index idx_key3(key3),
    index idx_key_part(key_part1, key_part2, key_part3)
);

create table s2 (
    id int auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    common_field varchar(100),
    primary key(id),
    index idx_key1(key1),
    unique index idx_key2(key2),
    index idx_key3(key3),
    index idx_key_part(key_part1, key_part2, key_part3)
);
insert into s1(id, key2) values(1,1);

const

主键或唯一二级索引等值匹配。

主键

mysql> explain select * from s1 where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

唯一二级索引

mysql> explain select * from s1 where key2 = 2;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref

连接查询时，被驱动表通过主键或唯一二级索引列等值匹配。

主键

mysql>  explain select * from s1 inner join s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | s1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.s2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

唯一二级索引

mysql>  explain select * from s1 inner join s2 on s1.key2 = s2.key2;
+----+-------------+-------+------------+--------+---------------+----------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key      | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+----------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | s2    | NULL       | ALL    | idx_key2      | NULL     | NULL    | NULL           |    1 |   100.00 | Using where |
|  1 | SIMPLE      | s1    | NULL       | eq_ref | idx_key2      | idx_key2 | 5       | testdb.s2.key2 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+----------+---------+----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

ref

普通二级索引列与常量等值匹配时。

mysql>  explain select * from s1 where key1 = 'abc';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

range

使用索引范围查询时。

in

mysql>  explain select * from s1 where key1 in ('abc','xyz');
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 403     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

大于/小于

mysql>  explain select * from s1 where key1 > 'abc' and key1 < 'xyz';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 403     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index

使用覆盖索引，但需要扫描全部的索引记录时。

备注：where key_part3 = 'a'不能使用到联合索引，但select的是id, key_part1都包含在联合索引中，所以联合索引可以使用（key字段中使用到了idx_key_part）。

mysql>  explain select id, key_part1 from s1 where key_part3 = 'a';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | s1    | NULL       | index | idx_key_part  | idx_key_part | 1209    | NULL |    2 |    50.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

ALL

全表扫描

mysql>  explain select * from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

效率

const>eq_ref>ref>range>index>ALL

show warnings\G