对线面试官 - 如何理解MySQL的索引覆盖和索引下推

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 索引下推是MySQL 5.6引入的优化,允许部分WHERE条件在索引中处理,减少回表次数。例如,对于索引(zipcode, lastname, firstname),查询`WHERE zipcode='95054' AND lastname LIKE '%etrunia%'`时,索引下推先过滤zipcode,然后在索引中应用lastname条件,降低回表需求。索引下推可在EXPLAIN的`Using index condition`中看到。

面试官:了解MySQL的索引吧?

派大星:是的,有了解。

面试官:那你能简单聊聊是什么MySQL的覆盖索引吗?

派大星:可以。

覆盖索引,也就是covering index。指的是一个查询语句的执行只用从索引中就能获取到目标数据,不必从数据表中读取。因此也可称之为实现了索引覆盖

当我们执行一条查询语句符合覆盖索引时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回查表操作,减少I/O并提高了效率。

比如:我们有一张表covering_tabel,其中有一个普通索引idx_key1_key2(key1, key2)。当我们执行SQLselect key1 from covering_table where key1 = "ketvalue"的时候,此时其实就i是通过了覆盖索引进行查询,无需回表。

但是在使用过程中要注意的是:有两种情况是不满足的:

  1. sql的where条件不符合最左前缀匹配原则
  2. SQL查询的字段不属于联合索引

比如如果sql不符合最左前缀匹配,即使是索引覆盖也是无法使用到索引的(会扫描索引树),比如这个SQLselect key1 from covering_table where key2 = "keyvalue"

要是SQL中的查询字段也没有包含在联合索引中,其实也是不会走索引覆盖的。比如:
select key2, key3 from covering_table where key1 = "keyvalue"

面试官:嗯,理解可以,那你知道什么是索引下推吗?

派大星:有了解,索引下推是MySQL在5.6中引入的一种优化技术,默认是开启状态的。当然也可以通过set optimizer_switch = index_condition_pushdown = off进行关闭。

官方文档中大致解释如下:

  • 假设有一个people表中的(zipcode、lastname、firstname)构成一个索引。
    SELECT * FROM people
    WHERE zipcode='95054'
    AND lastname LIKE '%etrunia%'
    AND address LIKE '%Main Street%';
    
    如果要是上述SQL在没有使用索引下推技术,则MySQL会通过 zipcode='95054' 从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%' 和 address LIKE '%Main Street%'; 来判断是否符合条件。

但是如果使用了索引下推技术的话,MySQL则会通过 zipcode='95054' 先返回符合条件的索引,然后根据lastname LIKE '%etrunia%' 来判断索引是否符合条件。如果符合条件,就会根据该索引来定位对应的数据,如果不符合,则直接reject掉,有了索引下推的优化,可以在like条件查询的情况下,减少回表的次数。

需要注意的是:当一条SQL使用到了索引下推时,那么explain的执行计划中的extra字段对应的内容为:Using index condition

这个具体可以参考官方文档:

https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

如图:

image.png

面试官:挺好。那你觉得索引下推只是在Like的情况下吗?官方其实是只提到了Like,这里你有什么想法吗?

派大星:其实,我个人认为在上面的例子以及官网中都是只提到了like,但其实不知有like。因为我认为索引下推其实是解决索引失效带来的效率低的问题的一种手段。

所以在联合索引中,由于某个前导列因为索引失效而要进行扫表并回表时,就可以进行索引下推优化了。

比如:有联合索引a,b。类型都是varchar,下面这个SQL也是可以用到索引下推的。

select d from t where a = "test" and b = 1;

因为上述SQL的字段类型不匹配导致索引失效,但是通过索引下推优化其实是可以减少回表的次数的。

面试官:不错那你知道什么是回表,怎么减少回表的次数吗

派大星:这个了解一些。
在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。

在存储的数据方面,主键(聚簇)索引的B+树的叶子节点直接就是我们要查询的整行数据了。而非主键(非聚簇)索引的叶子节点是主键的值。

那么,当我们根据非聚簇索引查询的时候,会先通过非聚簇索引查到主键的值,之后,还需要再通过主键的值再进行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。

所以,在InnoDB 中,使用主键查询的时候,是效率更高的, 因为这个过程不需要回表。另外,依赖覆盖索引索引下推等技术,我们也可以通过优化索引结构以及SQL语句减少回表的次数。

面试官:嗯,理解的十分透彻。有想法。

派大星:谢谢。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
存储 算法 关系型数据库
【MySQL】索引(重点)-- 详解(下)
【MySQL】索引(重点)-- 详解(下)
|
1天前
|
存储 关系型数据库 MySQL
【MySQL】索引(重点)-- 详解(上)
【MySQL】索引(重点)-- 详解(上)
|
6天前
|
消息中间件 关系型数据库 MySQL
MySQL 到 Kafka 实时数据同步实操分享(1),字节面试官职级
MySQL 到 Kafka 实时数据同步实操分享(1),字节面试官职级
|
6天前
|
机器学习/深度学习 关系型数据库 MySQL
MySQL 到 Greenplum 实时数据同步实操分享,2024年最新【Python面试题
MySQL 到 Greenplum 实时数据同步实操分享,2024年最新【Python面试题
|
8天前
|
SQL 存储 关系型数据库
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(下)
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!
42 2
|
8天前
|
SQL 关系型数据库 MySQL
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(上)
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!
32 2
|
8天前
|
NoSQL 关系型数据库 MySQL
B+树 和 跳表 的结构及区别,不同的用途【mysql的索引为什么使用B+树而不使用跳表?】
B+树 和 跳表 的结构及区别,不同的用途【mysql的索引为什么使用B+树而不使用跳表?】
26 2
|
9天前
|
存储 算法 关系型数据库
MySQL索引详解
MySQL索引详解
16 0
|
9天前
|
存储 SQL 关系型数据库
完蛋!😱 我被MySQL索引失效包围了!
完蛋!😱 我被MySQL索引失效包围了!
|
6天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
83 0

相关产品

  • 云数据库 RDS MySQL 版