数据库

阿里面试：MySQL 一个表最多 加几个索引？ 6个？64个？还是多少？

Posted Cola🌀

最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、shein 希音、shopee、百度、网易的面试资格，遇到很多很重要的面试题：

 mysql中，一个表最多只能加多少个索引嘛？一个联合索引最多只能多少列呢？
 索引加多了，会存在哪些问题呢？
 InnoDB存储引擎
 MyISAM存储引擎
 一个表设计多少个索引合理呢？
 索引设计过多存在哪些问题？
 阿里巴巴编程规范中，单表索引数量，建议控制在5个以内，为什么？

前几天 小伙伴面试阿里，遇到了这个问题。但是由于 没有回答好，导致面试挂了。

　

1. 索引基础 ：一个 表的 “数据目录”

1.1 什么是 索引？

  • 没有索引：数据库要扫描整张表，就像你从图书馆第一本书开始找
  • 有索引：直接定位到数据位置，效率提升几十甚至上百倍

索引就是”数据的目录” ， 想象一下你去图书馆找书，没有目录的话你得一本本翻，有了目录就能直接找到想要的书。索引就是数据库的”数据目录”，它能帮你快速定位数据。在MySQL中，索引 是一种特殊的数据结构，通常是B+树，它存储着字段值 和对应记录的主键值。

　

1.2 为什么需要索引？

没有索引时，数据库要执行全表扫描，就像你从图书馆第一本书开始一本本找，数据量越大查询越慢。有索引后，数据库可以直接定位数据位置，效率提升几十甚至上百倍。数据量越大， 索引的价值就大， 在百万级、甚至千万级的 表中，有索引的查询可能只需几毫秒，没索引可能要几秒， 甚至更久。但凡事都有不利的一面，  索引不是万能的，它是以额外存储空间和写入性能为代价换取查询速度的提升，需要权衡利弊。

　

1.3 索引的常见类型

索引的常见类型有：

  • 普通索引：最基本的索引，没有任何限制
  • 唯一索引：要求索引列的值必须唯一
  • 主键索引：特殊的唯一索引，不允许有空值
  • 联合索引：多个列组合的索引

普通索引是最基本的索引类型，没有任何限制，允许重复值和空值。

唯一索引要求索引列的值必须唯一，但允许有空值。

主键索引是特殊的唯一索引，不允许有空值，每个表只能有一个。

联合索引是多个列组合的索引，遵循最左前缀原则。

此外还有全文索引、空间索引等特殊类型。

不同类型的索引适用于不同场景，比如用户名适合用唯一索引，文章内容适合用全文索引。

　　

2. InnoDB存储引擎 的 索引 限制

InnoDB存储引擎 是 MySQL  最常用的存储引擎，InnoDB  作为MySQL5.5后的默认引擎，InnoDB支持事务、行级锁、外键约束等高级功能。InnoDB   的索引采用聚簇索引（主键索引）  +  非聚簇索引 （二级索引） 结合的结构，主键索引的叶子节点直接存储行数据，这使得主键查询特别高效。InnoDB还支持MVCC多版本并发控制，大大提高了并发读写性能。对于大多数业务场景，InnoDB都是最佳选择。InnoDB是MySQL最常用的存储引擎，它就像一辆高性能跑车，既稳定又快速。InnoDB存储引擎  索引数量限制

  • 最多64个普通索引 + 1个主键索引 = 65个
  • 每个索引最多包含16个字段

尼恩对索引 使用建议：

  • 虽然能创建 65个，除非迫不得已，不建议这么干！
  • 就比如说，像一个人能吃10碗饭，不代表就一定要吃10碗。

　

2.1 InnoDB 索引 的 数量限制

根据MySQL官方文档， InnoDB存储引擎  它最多允许 一个表最多   64个二级索引（即非主键索引），官方文档有说明 如下

InnoDB最多允许64个二级索引（非主键索引）， 当然， 还有 加上1个主键索引，总共65个索引。那在InnoDB中，一个表，最多可以有 64+1=65 个索引而对于一个索引，最多有多少列呢？

　

2.2    InnoDB   索引列 的 数量限制

InnoDB  中，一个 索引  最多 能允许  多少个 列 ？结论是： 一个 索引   最多是16列。官方文档也是有说明的：

每个索引最多可以包含16个字段，这意味，  可以创建一个包含16个字段的超级联合索引。但要注意，这些是理论最大值，实际应用中应该远低于这个限制。索引越多，或者一个索引里边的 列越多，  维护成本越高，特别是对于写入频繁的表，过多的索引会严重影响性能。通常建议单表索引不超过5-8个，核心查询字段优先建索引。对于联合索引，字段数最好控制在3-5个以内。建议是 ：  定期使用EXPLAIN分析查询语句，确保索引被正确使用，删除冗余和低效的索引。

　

3. MyISAM存储引擎  的 索引 限制

MyISAM 是 MySQL早期的默认存储引擎，虽然现在用得少了，但在某些场景下仍有价值。MyISAM  适合读多写少的场景。MyISAM 不支持事务和行级锁，但查询速度非常快，特别适合读多写少的场景。

提示：MyISAM的表级锁在写入时会锁定整个表，不适合高并发写入场景。

　

3.1 索引数量限制

MyISAM每个表最多支持64个索引，主键索引不计入此限制。每个索引最多可以包含16个字段，与InnoDB相同。MyISAM的索引使用B-tree结构存储，支持前缀索引，可以只对字段的前N个字符建立索引。MyISAM存储引擎  的 索引 限制 如下：

  • 最多64个索引（主键不算在内）
  • 每个索引最多16个字段

　

3.2 MyISAM 与InnoDB的区别

MyISAM和InnoDB的主要区别包括：

  • MyISAM 不支持事务
  • MyISAM 表级锁（不是行级锁）
  • MyISAM  适合读多写少的场景

MyISAM不支持事务，而InnoDB支持；MyISAM只有表级锁，InnoDB支持行级锁；MyISAM不支持外键，InnoDB支持；MyISAM的崩溃恢复能力较弱，InnoDB更可靠；MyISAM的全文索引较早出现，但现在InnoDB也支持了。选择存储引擎时，如果不需要事务且读多写少，可以考虑MyISAM，否则应该选择InnoDB。

　

4. 索引数量：少即是多

在数据库设计中，索引数量应该遵循”少即是多”的原则。过多的索引不仅不能提高性能，反而会带来各种问题。索引就像书中的目录，一本几百页的书有3-5个目录章节就足够了，如果每页都做一个目录，反而会让查找变得困难。数据库索引也是如此，需要精心设计，只给真正需要的查询条件建立索引。

　

4.1 阿里巴巴规范建议

日常开发中，一个表设计多少个索引合适呢？

阿里巴巴《Java开发手册》技术文档，单表索引数量建议控制在5个以内, 单个索引的字段数不超过5个

阿里巴巴《Java开发手册》建议单表索引数量控制在5个以内，这是基于多年实战经验得出的结论。5个索引对于大多数业务场景已经足够，能够覆盖主要的查询需求。这个建议不是绝对的，对于特别复杂的业务表可以适当增加，但必须有充分的理由。规范还建议单个索引的字段数不超过5个，避免创建过于复杂的联合索引。

总之： 适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

有些时候，不加索引更合适：

  • 数据量少的表，不适合加索引
  • 更新比较频繁的也不适合加索引

　

4.2 为什么 阿里巴巴规范建议 是5个？

阿里巴巴的《Java开发手册》建议单表索引不超过5个，为啥呢？因为，索引  太多的  “副作用”  ：

  • 写数据变慢‌：就像你每写一篇日记，都要在10个不同的目录里更新位置，累不累？
  • 占用空间大‌：每个索引都要单独存一份数据，就像你为了找书方便，买了10本一模一样的字典放家里
  • MySQL会犯选择困难症‌：索引太多，MySQL反而可能选错最快的查询路径
  • 维护成本高‌：备份、迁移数据时，索引越多越慢

所以，现实中的最佳实践：  5个以内最健康。

　　

5. 索引过多会 导致的  “七宗罪”

索引虽然能提高查询速度，但过多索引会带来一系列问题，过度索引带来的性能下降和维护困难 ， 这里 总结为索引的”七宗罪”。理解 “七宗罪” 问题，有助于我们更好地设计索引策略，避免过度索引带来的性能下降和维护困难。

　

5.1 第一宗罪：写入变慢

每次执行INSERT、UPDATE、DELETE操作时，MySQL不仅要修改数据，还要更新所有相关的索引。索引越多，写入操作就越慢。特别是在批量导入数据时，索引会显著降低导入速度。测试表明，一个没有索引的表可能比有10个索引的表写入速度快10倍以上。对于在线web服务系统（如电商平台、 金融交易平台），过多的索引会导致系统吞吐量大幅下降。

　

5.2 第二宗罪：磁盘 空间浪费

占用空间大‌：每个索引都要单独存一份数据，就像你为了找书方便，买了10本一模一样的字典放家里每个索引都需要额外的  磁盘 存储空间。对于InnoDB，索引和数据存储在同一个文件中，索引越多，文件越大。一个包含10个索引的百万级数据表，索引可能占用几GB甚至更多的空间。这不仅增加了存储成本，还会影响备份恢复的速度。

　

5.3  第三宗罪：缓存效率降低

InnoDB使用 Buffer Pool 缓冲池来缓存数据和索引。索引太多会占用大量 Buffer Pool  缓冲池空间，导致数据和索引的缓存命中率下降。当 Buffer Pool  缓冲池无法容纳常用数据时，MySQL就需要频繁地从磁盘读取数据，严重影响性能。合理的索引数量可以让缓冲池缓存更多热点数据。

　

5.4  第4宗罪：锁竞争加剧

在高并发环境下，索引更新会导致锁竞争加剧。特别是当多个事务同时修改同一索引时，可能出现锁等待甚至死锁。InnoDB的行级锁虽然缓解了这个问题，但索引太多仍然会增加锁冲突的概率，影响系统并发性能。

　

5.5  第5宗罪：优化器困惑

MySQL会犯选择困难症‌：索引太多，MySQL反而可能选错最快的查询路径当表中有多个索引时，MySQL优化器需要选择使用哪个索引来执行查询。索引太多会增加优化器做出错误选择的风险，可能导致性能反而下降。比如优化器可能选择区分度不高的索引，或者错误估计索引的选择性。这时就需要使用FORCE INDEX等提示来强制使用特定索引。

　

5.6  第6宗罪：维护困难

索引越多，数据库维护工作就越复杂。ALTER TABLE操作会变得更慢，特别是在大表上添加或删除索引可能需要很长时间。备份恢复也会变慢，因为需要处理更多的索引数据。此外，监控和管理大量索引也需要更多的时间和精力。

　

5.7  第7宗罪：统计信息更新变慢

MySQL使用统计信息来优化查询执行计划。索引越多，收集和维护统计信息所需的时间和资源就越多。在数据变化频繁的表上，过时的统计信息可能导致优化器选择低效的执行计划。虽然可以手动分析表来更新统计信息，但这会增加维护负担。

　

6. 索引使用实战技巧

掌握了索引的基本原理后，建议大家 需要了解一些索引的实战技巧， 帮助我们在实际项目中更好地设计和使用索引。大家对于 索引的使用，存在很多误区，其中  最大的误区是认为”索引越多查询越快”，实际上索引过多会降低整体性能。另一个误区是为所有查询字段都建索引，这会导致索引泛滥。还有人认为联合索引字段顺序无关紧要，实际上顺序对索引效率影响很大。此外，过度依赖自动创建的索引、不评估索引使用效果、不删除无用索引等都是常见问题。

　

6.1 哪些情况不加索引？

  • 第一：数据量小的表（如配置表）不需要索引。为啥呢  ？  因为数据量小的表 在查询的时候， 全表扫描可能比索引查找更快。
  • 第二：频繁更新的字段（写多读少的字段），要谨慎加索引。为啥呢  ？ 因为每次更新都需要维护索引。
  • 第三：区分度低的字段（如性别、状态标志），通常不适合单独建索引。为啥呢  ？因为索引效果不明显。
  • 第四：太长的字段（如TEXT）不要加索引。如果一定要加，就要使用前缀索引。
  • 第五： NULL值过多的字段，也不建议 加索引。

　

6.2 如何设计高效索引？

  • 首先分析业务查询模式，优先为高频查询条件建索引。
  • 联合索引要注意字段顺序，区分度高的字段放前面。
  • 避免创建冗余索引，比如已有(a,b)索引就不需要单独建a索引。
  • 定期使用EXPLAIN分析慢查询，优化索引策略。
  • 考虑使用覆盖索引减少回表操作。
  • 对于长字符串，考虑使用前缀索引节省空间。

　

6.3 对 索引进行 定期监控和优化

索引不是建完就一劳永逸的，需要定期监控和优化。建议每月至少检查一次索引使用情况，删除无用索引。

使用SHOW INDEX FROM table命令可以查看表的索引信息，包括索引名称、字段、基数等。
通过sys.schema_unused_indexes  视图可以找出长期未使用的索引。
EXPLAIN命令可以分析查询是否使用了合适的索引。

对于数据变化大的表，定期ANALYZE TABLE更新统计信息。监控索引碎片化程度，必要时重建索引。建立索引变更评审机制，避免随意添加索引。记录索引变更历史，便于问题追踪。对于重要系统，可以考虑使用索引管理工具。

　

7. 索引的 真实案例分享

理论结合实践才能更好掌握索引设计，下面分享两个真实案例。这些案例来自实际项目经验，展示了如何根据具体业务需求设计合理的索引策略，以及不当索引设计可能导致的问题和解决方案。

　

案例1：电商系统用户表 的索引案例分享

主键使用自增user_id，保证写入性能。mobile和email字段建立唯一索引，用于登录和密码找回。register_time建立索引用于新用户分析。last_login建立索引用于活跃用户统计。nickname使用前缀索引支持模糊搜索。避免为gender等低区分度字段单独建索引。定期清理不活跃用户的索引条目。

　

案例2：订单系统   订单表 的索引案例分享

主键使用order_id，分布式系统可以考虑雪花ID。user_id建立索引支持用户查询。create_time建立索引支持时间范围查询。status和payment_type建立联合索引用于订单分析。避免为price等频繁更新的字段单独建索引。考虑使用部分索引只索引未完成订单。定期归档历史订单减少索引大小。

　

8. 总结：索引使用黄金法则

经过前面的详细讲解，我们可以总结出一些索引使用的黄金法则。‘记住这些法则可以帮助我们避免常见的索引设计错误，建立高效的数据库结构。

  • 不是所有字段都需要索引，只为真正需要的查询条件建索引。
  • 联合索引优于多个单列索引，但要注意字段顺序。
  • 区分度高的字段更适合索引，低区分度字段考虑联合索引。
  • 定期维护比盲目添加更重要，及时删除无用索引。
  • 5个以内最健康，超过8个要三思，必须有充分理由。
  • 理解业务查询模式是设计好索引的前提。
  • 监控和优化是持续过程，不是一次性的工作。

这些法则不是死板的教条，而是指导性的原则，在实际应用中需要根据具体情况进行调整。 

　

9.索引为什么会 “存储很多”？

可以把索引理解成 “数据的副本 + 目录”，它的存储量甚至可能超过原始数据：

  1. 每棵索引树都是独立的副本
    比如一张用户表有 id、name、age 三个字段，为 name 和 age 分别建索引，就会生成两棵独立的 B + 树：
    • name 索引树：存储所有 name 的值 + 对应记录的位置
    • age 索引树：存储所有 age 的值 + 对应记录的位置
      每个索引树都需要单独占用磁盘空间，字段越多、索引越多，总存储量越大。
  2. 索引树的 “冗余结构”
    B + 树为了快速查询，除了叶子节点存储实际索引值，中间节点还要存储 “范围标记”（比如 age < 30 指向左子树，age ≥30 指向右子树），这些结构也会占用额外空间。

举个例子：一张 100 万行的表，每行数据 1KB，原始数据约 1GB；如果建 5 个索引，每个索引可能占用 200-500MB，总索引空间可能达到 1-2.5GB，甚至超过原始数据。

　

10.更新数据时，索引是如何更新的？

当你执行 INSERT、UPDATE、DELETE 时，数据库不仅要改原始数据，还要同步更新所有相关的索引。这个过程类似 “改日记 + 同步改所有目录”：

1. 插入数据（INSERT）

比如插入一条 (id=10, name="张三", age=25) 的记录：

  • 先把这条记录写入数据页（类似把日记内容写到某一页）。
  • 然后，为每个索引 “同步更新目录”：
    • id 索引树：在 B + 树中找到 10 对应的位置，插入新节点，同时调整树的结构（保证平衡）。
    • name 索引树：在树中找到 “张三” 对应的位置，插入新节点并调整结构。
    • age 索引树：同样在树中插入 25 对应的节点并调整。
    关键：每个索引都要单独执行一次 “插入 + 树平衡” 操作，索引越多，插入越慢。

　

2. 修改数据（UPDATE）

分两种情况：

  • 修改的字段没有索引：比如只改 age 但 age 没建索引，只需改原始数据，索引不动。
  • 修改的字段有索引：比如改 name 从 “张三” 到 “李四”（name 有索引）：
    • 先改原始数据中的 name 字段。
    • 然后在 name 索引树中：
      ① 删除旧值 “张三” 对应的节点（从树中移除该条目）。
      ② 插入新值 “李四” 对应的节点（重新找到位置并插入）。
      ③ 再次调整树结构，保证平衡。
    关键：修改索引字段时，相当于 “先删后插”，比插入操作更耗时。

　

3. 删除数据（DELETE）

比如删除 id=10 的记录：

  • 原始数据不会立即从磁盘删除（通常做个 “删除标记”）。
  • 索引树中，对应的节点会被标记为 “无效”，并在后续的 “索引优化”（如 InnoDB 的 purge 线程）中清理。
  • 如果删除后索引树出现大量空洞，数据库会在空闲时自动 “整理索引”（类似整理书架，把零散的书归位）。关键：删除虽然不立即清理索引，但标记和后续整理仍会消耗资源。

　

11.为什么索引多了会变慢？

用一个比喻总结：

  • 原始数据是 “日记本正文”，索引是 “不同分类的目录”（按日期、按主题、按关键词等）。
  • 每次写日记（插入）、改内容（更新）、撕页（删除），都要同步修改所有目录。
  • 目录越多（索引越多），每次操作就要花越多时间改目录，甚至可能因为目录太乱（索引树不平衡），导致找东西反而变慢（查询时 MySQL 选错索引）。

这就是为什么阿里巴巴规范建议单表索引不超过 5 个 —— 平衡查询效率和写入 / 存储成本。

　

12. Mysql可以同时用两个以上的单字段索引吗？

InnoDB_Table表 col4 和 col5都有索引，看执行计划，只用了col4的索引

　　　

各字段含义与结果解读
id：执行计划的标识 ID，这里为 1，表示只有一个查询执行单元。
select_type：查询类型，SIMPLE 表示这是简单查询（非子查询、非联合查询等复杂场景 ）。
table：涉及的表，这里是 InnoDB_Table。
partitions：查询涉及的分区，[NULL] 表示表未分区。
type：MySQL 在表中找到所需行的方式（即访问类型），ref 表示利用索引查找，且索引列与常量比较（效率较高的一种方式，比 ALL（全表扫）好很多 ）。
possible_keys：MySQL 认为可能能用于此查询的索引，这里是 col4_index,col5_index，即 col4 和 col5 字段各自的索引。
key：实际选择用于查询的索引，这里是 col4_index，说明优化器最终选了 col4 的索引来执行查询。
key_len：使用的索引长度，7 表示索引字段参与查询的长度（需结合字段类型推断，比如 col4 可能是字符类型，按编码计算后占用 7 字节 ）。
ref：与索引比较的内容，const 表示是常量（即查询条件里 col4 = '1' 中的 '1' 是常量 ）。
rows：MySQL 认为它执行查询时必须检查的行数，这里 1 表示预计只需扫描 1 行就能找到结果。
filtered：表示返回结果的行占需扫描行的百分比，100.0 说明符合条件的行占比高（结合 rows=1，就是这 1 行都符合条件 ）。
Extra：额外信息，Using where 表示查询使用了 WHERE 条件过滤数据。‘

整体执行逻辑
优化器评估后，选择 col4_index 索引，用 col4 = '1' 快速定位数据（借助 ref 类型的索引查找 ）。
找到对应行后，再用 col5 = '2' 条件过滤（Using where 体现这一步 ），最终返回结果。
由于只用到 col4 的索引，col5 的索引虽在 possible_keys 但未被选中，可能是优化器认为用 col4 索引配合 WHERE 过滤更高效。

在 MySQL 中，当col4和col5是单独的单字段索引，而非复合索引时，优化器一般一次查询只用 1 个单字段索引，无法同时用到两个，主要是由以下几方面原因决定的：

1. 索引的数据结构和查找原理

MySQL 中最常用的索引类型是 B + 树索引。对于单字段索引，B + 树是基于单个字段的值构建的。

  • 索引查找过程：以col4_index为例，当使用这个索引进行查找时，MySQL 会从 B + 树的根节点开始，按照树的结构逐步向下找到匹配col4值的叶子节点，从而定位到对应的行记录（或者记录的主键值，然后再通过主键回表获取完整数据）。在这个过程中，它只利用了col4字段的信息。
  • 多索引同时使用的冲突：如果要同时使用col4_index和col5_index，就相当于要同时在两棵独立的 B + 树上进行查找。这两棵树是基于不同字段构建的，没有直接的关联关系，无法直接将两个索引的查找结果高效地合并起来定位到满足两个条件的行记录。因为每个索引只能提供一个维度的筛选，而 MySQL 很难在不造成大量额外开销的情况下，同时利用这两个独立的筛选结果去快速定位到最终符合两个条件的行。

　

2. 优化器的成本估算和选择策略

MySQL 的查询优化器会对不同的执行计划进行成本估算，选择成本最低的那个来执行。

  • 成本计算因素：优化器在评估是否使用某个索引时，会考虑很多因素，如扫描的行数、随机 I/O 的次数、CPU 的消耗等。对于单字段索引，当有多个单字段索引可供选择时，优化器会认为使用其中一个索引进行初步筛选，然后再通过 WHERE 条件过滤其他条件的成本，要低于同时尝试使用多个单字段索引并合并结果的成本。
  • 合并成本高：如果尝试同时使用两个单字段索引，就需要额外的操作来合并两个索引的查找结果，比如对两个索引找到的行记录集合进行交集运算。这个合并过程可能会涉及到大量的内存操作、排序操作等，会带来较高的成本。相比之下，选择一个筛选能力更强（区分度更高，能过滤掉更多数据）的单字段索引，先大幅缩小数据范围，再通过 WHERE 条件过滤另一个条件，整体的执行成本更低。

　

3. 历史设计和性能权衡

  • 设计初衷：MySQL 在设计之初，为了保证查询优化器的复杂度可控以及执行效率的稳定性，就采用了这种优先选择单个单字段索引（在多个单字段索引中选择一个最合适的）的策略。这样可以简化优化器的决策逻辑，减少优化过程中的不确定性。
  • 性能表现：在大多数实际场景中，选择单个区分度高的单字段索引，先进行初步筛选，再结合 WHERE 条件过滤，已经能够满足性能需求。同时使用多个单字段索引并合并结果的情况，在实际中往往会因为合并操作的复杂性导致性能反而不如选择单个合适索引的情况。

　

例如，假设有一张orders表，customer_id和order_date字段上分别有单字段索引。如果查询SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01'，优化器会评估customer_id索引和order_date索引哪个能更有效地缩小数据范围。如果customer_id索引能筛选出较少的行，优化器就会选择customer_id索引，然后再通过 WHERE 条件过滤order_date，而不是同时尝试使用两个索引并合并结果。

不过需要注意的是，随着 MySQL 版本的不断演进，在某些特定场景下，也有一些优化技术（如索引下推等）可以在一定程度上更好地利用多个单字段索引，但总体原则上，一次查询优先使用一个单字段索引的情况仍然是比较常见的。

若想让 col4 和 col5 同时用索引，需创建 复合索引（联合索引 ），如：

CREATE INDEX idx_col4_col5 ON InnoDB_Table (col4, col5);

　　

那如果 利用 col4_index　找到了col4字段、 如何再找到col5呢？

当使用 col4_index 找到匹配 col4 条件的行后，MySQL 会通过以下步骤处理 col5 的条件：

1. 通过 col4_index 定位到行数据（或主键）

  • InnoDB 场景：col4_index 是二级索引，其 B + 树叶子节点存储的是 col4 的值 + 对应行的 主键值（而非完整行数据）。
    所以，第一步是通过 col4_index 快速找到所有满足 col4 = '1' 的主键值（比如 id = 100, 101, 102）。
  • 回表获取完整行数据：拿到主键值后，MySQL 会再通过 主键索引 查找对应的完整行数据（包含 col5 等所有字段）。这个过程称为 “回表”。

　

2. 过滤 col5 条件

获取完整行数据后，MySQL 会在内存中对这些行执行 WHERE col5 = '2' 的过滤：

  • 遍历所有通过 col4_index 找到的行，检查每行的 col5 字段是否等于 '2'。
  • 只保留同时满足 col4 = '1' 和 col5 = '2' 的行，最终返回结果。

　

示例流程拆解

假设表结构：id (主键), col4, col5, ...，且 col4 有单独索引：

  1. 查 col4_index：找到 col4 = '1' 对应的主键 id = 100, 101, 102。
  2. 回表查主键索引：通过 id = 100, 101, 102 找到完整行数据：
    • id=100：col4='1', col5='2'
    • id=101：col4='1', col5='3'
    • id=102：col4='1', col5='2'
  3. 过滤 col5：保留 col5='2' 的行（id=100 和 id=102），返回结果。

　

关键特点

  • col5_index 未被使用：因为此时 col5 的过滤是在回表后内存中完成的，无需再查 col5_index（查索引反而会增加开销）。
  • 效率取决于回表行数：如果 col4 = '1' 匹配的行数很少（比如只有 2 行），内存过滤很快；如果行数很多（比如 10 万行），则过滤效率会下降。

　

如何优化？

若想避免回表后的内存过滤，可创建 (col4, col5) 复合索引：

  • 复合索引的叶子节点存储 col4 + col5 + 主键，查询时可直接通过索引筛选 col4 = '1' AND col5 = '2'，无需回表过滤，效率更高。