SQL 优化(三):使用覆盖索引
摘要
今天跟大家分享一个比较常见的 SQL 优化手段——使用覆盖索引。需要特殊说明的是,MySQL 支持多种存储引擎,对索引的支持也不同,这里我们只关注 InnoDB 引擎的 BTree 索引
InnoDB 的索引实现
在介绍覆盖索引之前,我们先简单的聊一聊 InnoDB 的索引实现,InnoDB 的数据文件本身就是索引文件,表结构就是按 B+ Tree 组织的一个索引结构,树的叶子结点的 data 域保存了完整的数据记录,这个节点的 key 是表的主键
除了主键索引外,其他索引的叶子结点的 data 域,不会保存完整的数据记录,而是将主键作为其 data 域
总结一下,InnoDB 表的数据是保存在主键索引中的,其他索引(辅助索引)的叶子结点存储的是记录的主键
分析 SQL 的执行流程
知道了 InnoDB 的索引实现之后,再来看看下面这条 SQL 的执行流程
下面是t_test
表的初始化语句
执行流程如下:
在
k索引
树中,根据k=3
索引到对应的记录,并取得id=300
再到
id索引
(主键索引)中,根据id=300
,找到对应的行数据R3
在
k索引
中,找到下一个值k=500
,取得id=500
再到
id索引
中,根据id=500
,找到对应的行记录R5
最后到
k索引
中,找到下一个值,k=6
,不满足 where 条件,查找结束
在这个过程中,k 索引是辅助索引,只保存了行记录的主键,如果要取得对应行的整行记录,就需要回到主键索引中进行搜索。这个回到主键索引中进行搜索的过程,称之为回表
使用覆盖索引优化 SQL
如果我们执行的语句是
由于只需要查id
的值,而id
的值已经记录在k索引树
上了,所以无需再进行回表操作,也就是说,在这个查询里面,k索引树
已经覆盖了我们的查询需求,我们称之为覆盖索引
由于覆盖索引减少了回表的过程,所以可以显著提高 SQL 的执行效率,所以使用覆盖索引是一种常见的优化手段
基于覆盖索引的思想,假设有下面这种场景,我们需要根据手机号查询用户的姓名,用户表的定义如下:
我们的 SQL 这样写
这时候还是需要进行回表操作,因为idx_mobile索引树
上并没有存储 username,这时候,我们可以修改一下idx_mobile索引
,将其改为mobile
跟username
的联合索引
这样,idx_mobile_username索引树
上也有 username 了,就不用进行回表操作了
当然,索引的维护是有代价的,因此,至于使用idx_mobile_username
覆盖索引,还是使用idx_mobile
进行回表操作,就需要根据实际情况具体分析了
关注公众号:huangxy,一起学习,一起进步
版权声明: 本文为 InfoQ 作者【hungxy】的原创文章。
原文链接:【http://xie.infoq.cn/article/1eeda5d1170579894af20c90a】。文章转载请联系作者。
评论