Mysql索引命中级别
结果值从好到坏依次是:
- system/const, system在mysql8上好像没有了。一般主键唯一结果查询时会是const。
eq_ref
:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件- ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。
- range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index
- index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序
- all:全表扫描
准备工作
创建教师表
CREATE TABLE teacher (
id INT ( 5 ),
NAME VARCHAR ( 20 ),
card_id INT ( 5 )
);
创建教师卡信息表
CREATE TABLE teacher_card (
id INT ( 5 ),
remark VARCHAR ( 20 )
);
插入教师信息
insert into teacher values(1, '张三', 1);
insert into teacher values(2, '李四', 2);
insert into teacher values(3, '王五', 3);
插入教师卡信息
insert into teacher_card values(1, '张老师的卡');
insert into teacher_card values(2, '李老师的卡');
insert into teacher_card values(3, '王老师的卡');
const
类型测试
测试前添加主键索引
alter table teacher add constraint pk_teacher_id primary key(id);
衍生表只有一条数据的主查询;衍生表通过主键查询只有一条数据,再通过这条数据进行主查询。
EXPLAIN select * from teacher where id=1
得到执行计划结果:
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
- 仅仅能查到一条数据的SQL,用于primary key 或 unique的索引(其他索引类型不属于)。
- 主键查询只有一条数据的情况,类型为const
eq_ref
类型测试
给teacher_card添加主键
alter table teacher_card add constraint pk_teacher_card_id primary key(id);
分析对teacher表进行索引唯一查询
explain select t.card_id from teacher t, teacher_card tc where t.card_id = tc.id;
explain select t.card_id from teacher t left join teacher_card tc on t.card_id = tc.id;
得到执行计划结果
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | tc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mysql_ref_index_test.t.card_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------------+------+----------+-------------+
唯一性索引,表索引与另外表的主键进行关联,两张表之间每条数据要一一对应(每个都要一一对应,不能一个对应多个,不能没有对应),查询的数据在表中是唯一性,不能有重复。
ref
类型测试
非唯一线性索引,对于每个索引键的查询返回的数据为0或多条。
给teacher
表的name
的字段添加索引
alter table teacher add index index_teacher_name (name);
- 根据name = 张三查询出两条数据
- 根据name索引直接查询出来的值为ref类型。
sqlexplain select * from teacher where name = '张三';
执行计划结果:
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | ref | index_teacher_name | index_teacher_name | 83 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
range
类型测试
- 检查指定范围行,where后面是一个范围查询 (between、in、>、)
- 查看range类型的索引
in
的情况
explain select * from teacher t where t.id in (1, 2);
执行计划结果
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
between
的情况
explain select * from teacher where id between 1 and 2;
执行计划结果
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | teacher | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
比较情况
explain select * from teacher where id < 3;
执行计划结果
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | teacher | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
范围查询指定的索引,其索引类型为range:
index
类型测试
- 查询索引中的所有数据。
- 查询索引的所有数据,其中
name
就是索引 - 直接走了索引,不需要回溯
explain select name from teacher;
执行计划
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | teacher | NULL | index | NULL | index_teacher_name | 83 | NULL | 3 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
all
类型测试
- 查询表中的所有数据,或者根据不是索引的字段查询。
- 也就是走全表扫描了, 不能走索引。
EXPLAIN select * from teacher;
执行计划结果
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
type类型总结:
system
/const
:结果只有一条数据。eq_ref
:结果多条,但是每条数据都是唯一的。ref
:结果多条,但是查询到的数据可以是多条,且数据可以重复。range
:根究索引字段进行范围查询。