Mysql 索引命中级别分析

文章出处

Mysql索引命中级别

结果值从好到坏依次是:

  1. system/const, system在mysql8上好像没有了。一般主键唯一结果查询时会是const。
  2. eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
  3. ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。
  4. range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index
  5. index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序
  6. 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  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  1. 仅仅能查到一条数据的SQL,用于primary key 或 unique的索引(其他索引类型不属于)。
  2. 主键查询只有一条数据的情况,类型为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类型测试

  1. 查询索引中的所有数据。
  2. 查询索引的所有数据,其中name就是索引
  3. 直接走了索引,不需要回溯
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类型测试

  1. 查询表中的所有数据,或者根据不是索引的字段查询。
  2. 也就是走全表扫描了, 不能走索引。
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类型总结:

  1. system/const:结果只有一条数据。
  2. eq_ref:结果多条,但是每条数据都是唯一的。
  3. ref:结果多条,但是查询到的数据可以是多条,且数据可以重复。
  4. range:根究索引字段进行范围查询。
  • 2
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值