MySQL实战45讲学习笔记：第五讲

最新推荐文章于 2024-06-15 16:03:27 发布

wen854854321

最新推荐文章于 2024-06-15 16:03:27 发布

阅读量83

点赞数

文章标签： mysql 学习 sql

原文链接：https://www.cnblogs.com/luoahong/p/10598688.html

版权

一、需要回表的案例

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

1	`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语句的执行流程

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)

二、如何避免回表

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

1	`select` `ID` `from` `T` `where` `k` `between` `3` `and` `5`

1、覆盖索引

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

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

三、最左前缀原则

1、疑问

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

2、解决方案

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

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

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

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

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

1	`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语句是这么写的

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

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

3、无索引执行流程

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

执行流程

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

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

4、索引下推执行流程

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的数据，再进行回表查询，减少回表率，提升检索速度

作者：罗阿红出处：http://www.cnblogs.com/luoahong/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。