MySQL 性能优化之 explain type 字段
MySQL 性能优化 About 8,052 words创建表
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
Views: 2,334 · Posted: 2022-05-14
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...