SQL 优化(三):使用覆盖索引

54 阅读3分钟

摘要

今天跟大家分享一个比较常见的 SQL 优化手段——使用覆盖索引。需要特殊说明的是,MySQL 支持多种存储引擎,对索引的支持也不同,这里我们只关注 InnoDB 引擎的 BTree 索引

InnoDB 的索引实现

在介绍覆盖索引之前,我们先简单的聊一聊 InnoDB 的索引实现,InnoDB 的数据文件本身就是索引文件,表结构就是按 B+ Tree 组织的一个索引结构,树的叶子结点的 data 域保存了完整的数据记录,这个节点的 key 是表的主键

除了主键索引外,其他索引的叶子结点的 data 域,不会保存完整的数据记录,而是将主键作为其 data 域

总结一下,InnoDB 表的数据是保存在主键索引中的,其他索引(辅助索引)的叶子结点存储的是记录的主键

分析 SQL 的执行流程

知道了 InnoDB 的索引实现之后,再来看看下面这条 SQL 的执行流程

select * from t_test where k between 3 and 5

下面是t_test表的初始化语句

CREATE TABLE t_test (
    id INT PRIMARY KEY,
    k INT NOT NULL DEFAULT 0,
    v VARCHAR(50) DEFAULT NULL,
    INDEX k(k) using btree
)  ENGINE=INNODB charset utf8mb4;

insert into t_test values(100,1,'a');
insert into t_test values(200,2,'b');
insert into t_test values(300,3,'c');
insert into t_test values(500,5,'e');
insert into t_test values(600,6,'f');

执行流程如下:

  1. k索引树中,根据k=3索引到对应的记录,并取得id=300
  2. 再到id索引(主键索引)中,根据id=300,找到对应的行数据R3
  3. k索引中,找到下一个值k=500,取得id=500
  4. 再到id索引中,根据id=500,找到对应的行记录R5
  5. 最后到k索引中,找到下一个值,k=6,不满足 where 条件,查找结束

在这个过程中,k索引是辅助索引,只保存了行记录的主键,如果要取得对应行的整行记录,就需要回到主键索引中进行搜索。这个回到主键索引中进行搜索的过程,称之为回表

使用覆盖索引优化 SQL

如果我们执行的语句是

select id from t_test where k between 3 and 5

由于只需要查id的值,而id的值已经记录在k索引树上了,所以无需再进行回表操作,也就是说,在这个查询里面,k索引树已经覆盖了我们的查询需求,我们称之为覆盖索引

由于覆盖索引减少了回表的过程,所以可以显著提高 SQL 的执行效率,所以使用覆盖索引是一种常见的优化手段

基于覆盖索引的思想,假设有下面这种场景,我们需要根据手机号查询用户的姓名,用户表的定义如下:

CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mobile` varchar(45) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

我们的 SQL 这样写

select username from t_user where mobile = '15622000000';

这时候还是需要进行回表操作,因为idx_mobile索引树上并没有存储 username,这时候,我们可以修改一下idx_mobile索引,将其改为mobileusername的联合索引

ALTER TABLE `tmp`.`t_user` 
DROP INDEX `idx_create_time` ,
ADD INDEX `idx_mobile_username` USING BTREE (`mobile`, `username`);

这样,idx_mobile_username索引树上也有 username 了,就不用进行回表操作了

当然,索引的维护是有代价的,因此,至于使用idx_mobile_username覆盖索引,还是使用idx_mobile进行回表操作,就需要根据实际情况具体分析了

关注公众号:huangxy,一起学习,一起进步