什么是 MySQL 的“回表”?怎么减少回表的次数?
索引结构
要搞明白这个问题,需要大家首先明白 MySQL 中索引存储的数据结构。这个其实很多小伙伴可能也都听说过,B+Tree 嘛!
B+Tree 是什么?那你得先明白什么是 B-Tree,来看如下一张图:
![img](https://i-blog.csdnimg.cn/blog_migrate/c2fe675db285027427d42c71cf34d254.png)
前面是 B-Tree,后面是 B+Tree,两者的区别在于:
- B-Tree 中,所有节点都会带有指向具体记录的指针;B+Tree 中只有叶子结点会带有指向具体记录的指针。
- B-Tree 中不同的叶子之间没有连在一起;B+Tree 中所有的叶子结点通过指针连接在一起。
- B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针,搜索效率不稳定;B+Tree 中,一定要到叶子结点中才可以获取到具体记录的指针,搜索效率稳定。
基于上面两点分析,我们可以得出如下结论:
- B+Tree 中,由于非叶子结点不带有指向具体记录的指针,所以非叶子结点中可以存储更多的索引项,这样就可以有效降低树的高度,进而提高搜索的效率。
- B+Tree 中,叶子结点通过指针连接在一起,这样如果有范围扫描的需求,那么实现起来将非常容易,而对于 B-Tree,范围扫描则需要不停的在叶子结点和非叶子结点之间移动。
对于第一点,一个 B+Tree 可以存多少条数据呢?以主键索引的 B+Tree 为例(二级索引存储数据量的计算原理类似,但是叶子节点和非叶子节点上存储的数据格式略有差异),我们可以简单算一下。
计算机在存储数据的时候,最小存储单元是扇区,一个扇区的大小是 512 字节,而文件系统(例如 XFS/EXT4)最小单元是块,一个块的大小是 4KB。InnoDB 引擎存储数据的时候,是以页为单位的,每个数据页的大小默认是 16KB,即四个块。
基于这样的知识储备,我们可以大致算一下一个 B+Tree 能存多少数据。
假设数据库中一条记录是 1KB,那么一个页就可以存 16 条数据(叶子结点);对于非叶子结点存储的则是主键值+指针,在 InnoDB 中,一个指针的大小是 6 个字节,假设我们的主键是 bigint ,那么主键占 8 个字节,当然还有其他一些头信息也会占用字节我们这里就不考虑了,我们大概算一下,小伙伴们心里有数即可:
16*1024/(8+6)=1170
即一个非叶子结点可以指向 1170 个页,那么一个三层的 B+Tree 可以存储的数据量为:
1170*1170*16=21902400
可以存储 2100万 条数据。
在 InnoDB 存储引擎中,B+Tree 的高度一般为 2-4 层,这就可以满足千万级的数据的存储,查找数据的时候,一次页的查找代表一次 IO,那我们通过主键索引查询的时候,其实最多只需要 2-4 次 IO 操作就可以了。
大家先搞明白这个 B+Tree。
两类索引
大家知道,MySQL 中的索引有很多中不同的分类方式,可以按照数据结构分,可以按照逻辑角度分,也可以按照物理存储分,其中,按照物理存储方式,可以分为聚簇索引和非聚簇索引。
我们日常所说的主键索引,其实就是聚簇索引(Clustered Index);主键索引之外,其他的都称之为非主键索引,非主键索引也被称为二级索引(Secondary Index),或者叫作辅助索引。
对于主键索引和非主键索引,使用的数据结构都是 B+Tree,唯一的区别在于叶子结点中存储的内容不同:
- 主键索引的叶子结点存储的是一行完整的数据。
- 非主键索引的叶子结点存储的则是主键值。叶子结点不包含行记录的全部数据;非主键的叶子结点中,除了用来排序的key还包含一个bookmark;该书签存储了聚集索引的key。
这就是两者最大的区别。
所以,当我们需要查询的时候:
- 如果是通过主键索引来查询数据,例如
select * from user where id=100
,那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。 - 如果是通过非主键索引来查询数据,例如
select * from user where username='javaboy'
,那么此时需要先搜索 username 这一列索引的 B+Tree,搜索完成后得到主键的值,然后再去搜索主键索引的 B+Tree,就可以获取到一行完整的数据。
对于第二种查询方式而言,一共搜索了两棵 B+Tree,第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree,这个过程就是所谓的回表。
从上面的分析中我们也能看出,通过非主键索引查询要扫描两棵 B+Tree,而通过主键索引查询只需要扫描一棵 B+Tree,所以如果条件允许,还是建议在查询中优先选择通过主键索引进行搜索。
众所周知在InnoDB引用的是B+树索引模型,这里对B+树结构暂时不做过多阐述,很多文章都有描述,在第二问中我们对索引的种类划分为两大类主键索引和非主键索引,那么问题就在于比较两种索引的区别了,我们这里建立一张学生表,其中包含字段id设置主键索引、name设置普通索引、age(无处理),并向数据库中插入4条数据:("小赵", 10)("小王", 11)("小李", 12)("小陈", 13)
-
create
table `student` (
-
`id`
int(
11)
NOT
NULL AUTO_INCREMENT COMMENT
'自增主键',
-
`name`
varchar(
32)
COLLATE utf8_bin
NOT
NULL COMMENT
'名称',
-
`age`
int(
3) unsigned
NOT
NULL
DEFAULT
'1' COMMENT
'年龄',
-
primary key (`id`),
-
KEY `I_name` (`name`)
-
) ENGINE
=InnoDB;
-
-
INSERT
INTO student (name, age)
VALUES("小赵",
10),("小王",
11),("小李",
12),("小陈",
13);
这里我们设置了主键为自增,那么此时数据库里数据为
![image-20220306184151569](https://i-blog.csdnimg.cn/blog_migrate/e7261d833534806ce07ae7bec434fcd7.png)
每一个索引在 InnoDB
里面对应一棵B+树,那么此时就存着两棵B+树。
![img](https://i-blog.csdnimg.cn/blog_migrate/0c0e1ce083dea106af5d367ce16370fa.png)
可以发现区别在与叶子节点中,主键索引存储了整行数据,而非主键索引中存储的值为主键id, 在我们执行如下sql后
SELECT age FROM student WHERE name = '小李';
流程为:
- 在
name
索引树上找到名称为小李的节点id
为 03 - 从
id
索引树上找到id
为 03的节点 获取所有数据 - 从数据中获取字段命为
age
的值返回 12
在流程中从非主键索引树搜索回到主键索引树搜索的过程称为:回表,在本次查询中因为查询结果只存在主键索引树中,我们必须回表才能查询到结果,那么如何优化这个过程呢?引入正文覆盖索引
覆盖索引
就是把单列的非主键 索引 修改为 多字段 的联合索引, 在一棵索引数上。 就找到了想要的数据, 不需要去主键索引树上,再检索一遍 这个现象,称之为 索引覆盖.
覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。
- 如何使用是覆盖索引?
之前我们已经建立了表student
,那么现在出现的业务需求中要求根据名称获取学生的年龄,并且该搜索场景非常频繁,那么先在我们删除掉之前以字段name
建立的普通索引,以name
和age
两个字段建立联合索引,sql命令与建立后的索引树结构如下
-
# 删除之前的非主键索引
-
alter
table student
drop index I_name;
-
# 添加非主键索引
-
alter
table student
add index I_name_age(name, age);
那在我们再次执行如下sql后:
select age from student where name = '小李';
流程为:
- 在
name
,age
联合索引树上找到名称为小李的节点 - 此时节点索引(非主键索引)里包含信息
age
直接返回 12
- 如何确定数据库成功使用了覆盖索引呢?
当发起一个索引覆盖查询时,在explain
的extra
列可以看到using index
的信息:
这里我们很清楚的看到Extra
中using index
表明我们成功使用了覆盖索引。
覆盖索引避免了回表现象的产生,从而减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是性能优化的一种手段。
- 那么不用主键索引就一定需要回表吗?
不一定!
如果查询的列本身就存在于索引中,那么即使使用二级索引,一样也是不需要回表的。
举个例子,我有如下一张表:
![image-20220306183637330](https://i-blog.csdnimg.cn/blog_migrate/4a10908e9a9105989d77b0672cb7d1e8.png)
uname
和 address
字段组成了一个复合索引,那么此时,虽然这是一个非主键索引,但是索引树的叶子节点中除了保存主键值,也保存了 address
的值。
我们来看如下分析:
explain select uname,address from user where uname='javaboy';
![image-20220306183854336](https://i-blog.csdnimg.cn/blog_migrate/cf718e00e5f40a5cfcb6c988f3ed2fbc.png)
可以看到,此时使用到了 uname
索引,但是最后的 Extra
的值为 Using index
,这就表示用到了索引覆盖扫描(覆盖索引),此时直接从索引中过滤不需要的记录并返回命中的结果,这一步是在 MySQL 服务器层完成的,并且不需要回表。
哪些场景可以利用索引覆盖来优化SQL?
- 全表
count
查询优化
直接:
-
select
count(name)
from
user;
-
不能利用索引覆盖。
添加索引:
-
alter
table
user
add key(name);
-
就能够利用索引覆盖提效。
- 列查询回表优化
这个例子不再赘述,将单列索引(name
)升级为联合索引(name
, sex
),即可避免回表。
- 分页查询
将单列索引(name
)升级为联合索引(name, sex)
,也可以避免回表。
如何创建有效的索引
- 如果需要索引很长的字符串,此时需要考虑前缀索引
前缀索引即选择所需字符串的一部分前缀作为索引,这时候,需要引入一个概念叫做索引选择性,索引选择性是指不重复的索引值与数据表的记录总数的比值,可以看出索引选择性越高则查询效率越高,当索引选择性为1时,效率是最高的,但是在这种场景下,很明显索引选择性为1的话我们会付出比较高的代价,索引会很大,这时候我们就需要选择字符串的一部分前缀作为索引,通常情况下一列的前缀作为索引选择性也是很高的
如何选择前缀
- 计算该列完整列的选择性,使得前缀选择性接近于完整列的选择性
- 使用多列索引
尽量不要为多列上创建单列索引,因为这样的情况下最多只能使用一星索引,这样的话,不如去创建一个全覆盖索引,在多列上创建单列索引大部分情况下并不能提高 MySQL 的查询性能,MySQL 5.0 中引入了合并索引,在一定程度上可以表内多个单列索引来定位指定的结果,但是 5.0 以前的版本,如果 where
中的多个条件是基于多个单列索引,那么 MySQL 是无法使用这些索引的,这种情况下,还不如使用 union
。
- 选择合适的索引列顺序
经验是将选择性最高的列放到索引最前列,可以在查询的时候过滤出更少的结果集。
但这样并不总是最好的,如果考虑到 group by
或者 order by
等情况,再比如考虑到一些特别场景下的 guest 账号等数据情况,上面的经验法则可能就不是最适用的
- 覆盖索引
所谓覆盖索引就是指索引中包含了查询中的所有字段,这种情况下就不需要再进行回表查询了
覆盖索引对于 MyISAM 和 InnoDB 都非常有效,可以减少系统调用和数据拷贝等时间.
Tips:减少 select *
操作
- 使用索引扫描来做排序
MySQL 生成有序的结果有两种方法:通过排序操作,或者按照索引顺序扫描;使用排序操作需要占用大量的 CPU 和内存资源,而使用 index
性能是很好的,所以,当我们查询有序结果时,尽量使用索引顺序扫描来生成有序结果集。
怎样保证使用索引顺序扫描?
- 索引 列 顺序和
ORDER BY
顺序一致 - 所有列的排序方向一致
- 如果关联多表,那么只有当
ORDER BY
子句引用的字段全部为第一张表时,才能使用索引做排序,限制依然是需要满足索引的最左前缀要求
- 压缩索引
MyISAM 中使用了前缀压缩技术,会减少索引的大小,可以在内存中存储更多的索引,这部分优化默认也是只针对字符串的,但是可以自定义对整数做压缩
这个优化在一定情况下性能比较好,但是对于某些情况可能会导致更慢,因为前缀压缩决定了每个关键字都必须依赖于前面的值,所以无法使用二分查找等,只能顺序扫描,所以如果查找的是逆序那么性能可能不佳
- 减少重复、冗余以及未使用的索引
MySQL 的唯一限制和主键限制都是通过索引实现的,所以不需要在同一列上增加主键、唯一限制再创建索引,这样是重复索引
再举个例子,如果已经创建了索引(A,B),那么再创建索引(A)的话,就属于重复索引,因为 MySQL 索引是最左前缀,所以索引(A,B)本身就可以使用索引(A),但是创建索引(B)的话不属于重复索引
尽量减少新增索引,而应该扩展已有的索引,因为新增索引可能会导致 INSERT、UPDATE、DELETE 等操作更慢
可以考虑删除没有使用到的索引,定位未使用的索引,有两个办法,在 Percona Server 或者 MariaDB 中打开 userstates 服务器变量,然后等服务器运行一段时间后,通过查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查询到每个索引的使用频率
- 索引和锁
InnoDB 支持行锁和表锁,默认使用行锁,而 MyISAM 使用的是表锁,所以使用索引可以让查询锁定更少的行,这样也会提升查询的性能,如果查询中锁定了1000行,但实际只是用了100行,那么在 5.1 之前都需要提交事务之后才能释放这些锁,5.1 之后可以在服务器端过滤掉行之后就释放锁,不过依然会导致一些锁冲突
- 减少索引和数据碎片
- 首先我们需要了解一下为什么会产生碎片,比如 InnoDB 删除数据时,这一段空间就会被留空,如果一段时间内大量删除数据,就会导致留空的空间比实际的存储空间还要大,这时候如果进行新的插入操作时,MySQL 会尝试重新使用这部分空间,但是依然无法彻底占用,这样就会产生碎片
- 产生碎片带来的后果当然是,降低查询性能,因为这种情况会导致随机磁盘访问
- 可以通过
OPTIMIZE TABLE
或者重新导入数据表来整理数据
什么是索引下推
假设有这么个需求,查询表中“名字第一个字是张,性别男,年龄为10岁的所有记录”。那么,查询语句是这么写的:
select * from tuser where name like '张%' and age=10 and ismale=1;
根据前面说的“最左前缀原则”,该语句在搜索索引树的时候,只能匹配到名字第一个字是‘张’的记录(即记录ID3),接下来是怎么处理的呢?
当然就是从ID3
开始,逐个回表,到主键索引上找出相应的记录,再比对age
和ismale
这两个字段的值是否符合。
但是!MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数。
下面图1、图2分别展示这两种情况。
![img](https://i-blog.csdnimg.cn/blog_migrate/1341d4bf7b93c5404ede99d67b696f34.png)
![img](https://i-blog.csdnimg.cn/blog_migrate/f7d5dc6baf5c9ca9b934fe0cfe066d06.png)
图 1 中,在 (name,age)
索引里面我特意去掉了 age
的值,这个过程 InnoDB 并不会去看 age 的值,只是按顺序把“name
第一个字是’张
’”的记录一条条取出来回表。因此,需要回表 4 次。
图 2 跟图 1 的区别是,InnoDB 在 (name,age)
索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。
如果没有索引下推优化(或称ICP优化
),当进行索引查询时,首先根据索引来查找记录,然后再根据where条件来过滤记录;在支持ICP优化后,MySQL会在取出索引的同时,判断是否可以进行where条件过滤再进行索引查询,也就是说提前执行where
的部分过滤操作,在某些场景下,可以大大减少回表次数,从而提升整体性能。