写点什么

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

作者:hungxy
  • 2023-06-30
    广东
  • 本文字数:1441 字

    阅读完需:约 5 分钟

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

摘要

今天跟大家分享一个比较常见的 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,一起学习,一起进步

发布于: 刚刚阅读数: 5
用户头像

hungxy

关注

还未添加个人签名 2018-07-19 加入

还未添加个人简介

评论

发布
暂无评论
SQL 优化(三):使用覆盖索引_hungxy_InfoQ写作社区