SQL 优化（三）：使用覆盖索引

huangxy
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索引，将其改为mobile跟username的联合索引

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，一起学习，一起进步

avatar
文章
阅读
粉丝