MySQL实战45讲学习笔记:索引(第五讲)

时间:2022-11-24 09:13:13

一、需要回表的案例

在下面表T中,执行下面语句,需要执行几次树的搜索操作?会扫描多少行?

select * from T where k between 3 and 5

1、初始化语句

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB; insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

2、这条SQL语句的执行流程

MySQL实战45讲学习笔记:索引(第五讲)

1、在 k 索引树上找到 k=3 的记录,取得 ID = 300...
2、再到 ID 索引树查到 ID=300 对应的 R3;
3、在 k 索引树取下一个值 k=5,取得 ID=500;
4、再回到 ID 索引树查到 ID=500 对应的 R4;
5、在 k 索引树取下一个值 k=6,不满足条件,循环结束。

这个过程中回到主键索引树搜索的过程,我们称为回表,可以看到这个查询过程读了K索引树的3条记录
(步骤1、3和5),回表了两次(步骤1、3和5)

二、如何避免回表

在上面的例子中,由于查询结果锁需要的数据只在主键索引上有,所以不得不回表,
那么。有没有可能经过索引优化,避免回表过程?

select ID from T where k between 3 and 5

1、覆盖索引

索引k已经"覆盖了"我们的查询需求。我们称为覆盖索引

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段

2、在一个市民信息表上,是否有必要将身份证号和名字建立联合索引

假设这个市民表的定义是这样的:

CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了,
它可以在这个高频请求上用覆盖索引、不再需要回表查整行记录,减少语句的执行时间

3、回表的缺点

当然、索引字段的维护总是有代价的,建立冗余索引来支持覆盖索引时就需要权衡考虑了,这正是业务DBA,或者称为业务数据架构师的工作

三、最左前缀原则

1、疑问

单独为一个不频繁的请求创建一个索引又感觉有点浪费,应该怎么做呢?

2、解决方案

B+树这种索引结构,可以利用索引的"最左前缀",来定位记录

 

1、为了直观地说明这个概念、我们用(name、age)这个联合索引来分析

MySQL实战45讲学习笔记:索引(第五讲)

1、查到所有名字是“张三”的人

快速定位到ID4,然后向后遍历得到所有需要的结果

2、你要查的是所有名字第一个字是“张”的人

where name like '张%'

查找到第一个符合条件的记录是ID3,然后向后遍历,知道不满足条件为止。
你要查的是所有名字第一个字是“张”的人

不只是索引的全部定义,只要满足最左前缀,就可以可利用索引来加速检索、这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符

3、在建立联合索引的时候,如何安排索引内的字段顺序

1、评估标准

索引的复用能力,

因为可以支持最左前缀,所以已经有了(a,b)这两个联合所用后,一般就不需要单独在b上面建立索引了

2、第一原则是

如果通过调整顺序,可以减少维护一个索引。那么这个顺序往往就是需要优先考虑采用的

3、既有联合查询,又有基于 a、b 各自的查询呢?

同时维护(a,b)(b)这两个索引、这时候我们要考虑的原则就是空间了。

比如上面这个市民表的情况、name字段是比age字段大的,那我就建议你创建一个(name,age)的联合索引和一个(age)的字段索引

四、索引下推

我们还是以市民表的联合索引(name, age)为例。检索出表中:"名字第一个字是长,而年龄是10岁的所有男孩"

1、名字第一个字是长,而年龄是10岁的所有男孩

1、SQL语句是这么写的

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

1、 找到第一个满足条件的记录ID3(这还不错,总比权标扫描要好)
2、判断其他是否满足条件

3、无索引执行流程

MySQL实战45讲学习笔记:索引(第五讲)

5.6之前只能从ID3开始一个一个回表,到主键索引上找出数据航,再对比字段值不去判断age

执行流程

每一个虚线箭头表示回表一次

1、InnoDB 并不会去看 age的值,
2、只是按顺序把name的第一个子是'张'的记录一条取出来回表,因此需要回表4次

4、索引下推执行流程

MySQL实战45讲学习笔记:索引(第五讲)

5.6之后引入的索引下推优化,可以在索引遍历过程中对索引中包含的字段先判断,直接过滤掉不满足条件的记录,减少回表次数
内部就判断了age是否等于10

执行流程

每一个虚线箭头表示回表一次

1、InnoDB 内部就哦按段了age是否等于10,
2、对不等于10的记录,直接判断跳过,在我们这个例子中只需要对ID4、ID4回表2次

五、联合索引的技巧

1、覆盖索引

如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据

2、最左前缀

联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符

3、联合索引

根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。

4、索引下推

like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度