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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 Tair(兼容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语句减少回表的次数。

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

派大星:谢谢。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
4天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
6天前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
13天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
37 1
|
2天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
10 0
|
3天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
16 0
|
14天前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
27 0
|
21天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
92 6
|
19天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
54 3
Mysql(4)—数据库索引
|
4天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
29 2

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多