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

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 索引下推是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
相关文章
|
2天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
24天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
17天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
4天前
|
SQL 关系型数据库 MySQL
阿里面试:1000万级大表, 如何 加索引?
45岁老架构师尼恩在其读者交流群中分享了如何在生产环境中给大表加索引的方法。文章详细介绍了两种索引构建方式:在线模式(Online DDL)和离线模式(Offline DDL),并深入探讨了 MySQL 5.6.7 之前的“影子策略”和 pt-online-schema-change 方案,以及 MySQL 5.6.7 之后的内部 Online DDL 特性。通过这些方法,可以有效地减少 DDL 操作对业务的影响,确保数据的一致性和完整性。尼恩还提供了大量面试题和解决方案,帮助读者在面试中充分展示技术实力。
|
11天前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
15天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
76 1
|
18天前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
16天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0
|
3月前
|
存储 Java
【IO面试题 四】、介绍一下Java的序列化与反序列化
Java的序列化与反序列化允许对象通过实现Serializable接口转换成字节序列并存储或传输,之后可以通过ObjectInputStream和ObjectOutputStream的方法将这些字节序列恢复成对象。
|
7天前
|
存储 算法 Java
大厂面试高频:什么是自旋锁?Java 实现自旋锁的原理?
本文详解自旋锁的概念、优缺点、使用场景及Java实现。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:什么是自旋锁?Java 实现自旋锁的原理?

相关产品

  • 云数据库 RDS MySQL 版