1. 聊聊为什么面试官总爱问索引先说实话我在后台看到有人留言问能不能系统整理一下索引相关的面试题刚好最近也在帮团队做技术面试前前后后面了差不多三十个候选人索引这块几乎是必考题。不是面试官偷懒而是索引这个知识点真的太能看出一个人的水平了——你说你懂索引那我问你B树为什么是主流覆盖索引能带来多大的性能提升一条SQL明明走了索引却还是慢问题出在哪这几个问题一抛出去是真懂还是背八股立刻见分晓。这篇内容不会跟你整那些索引是什么的教科书定义直接从面试实战角度出发把高频考点掰开揉碎了讲。不管你是准备校招还是社招或者纯粹想把手上的SQL写得更优雅一点这篇文章都值得认真读完。我尽量用大白话把原理讲清楚配合实际案例让你能真正理解而不是死记硬背。我整理了一下近一年面试中索引相关的高频问题大致可以归成几类数据结构与原理、索引失效场景、SQL优化实战、复合索引设计、以及一些偏门但爱考的冷知识点。下面一个个来。2. 先从最基础的问起索引到底是个什么结构2.1 为什么是B树而不是哈希表面试官问你索引结构十有八九会追问一句为什么用B树。记住哈希索引虽然是O(1)的查询复杂度看起来比B树的O(logN)要快但哈希表有个致命问题——它只能做等值查询做不了范围查询。你在京东上买手机想筛个价格3000到5000的商品哈希索引直接就懵了它没法比较大小。再对比一下其他树结构。红黑树在内存里确实很强但数据库的数据是存在磁盘上的磁盘I/O才是真正的瓶颈。红黑树每个节点最多两个子节点数据量一上来树的高度就很吓人。我举个例子2000万条数据的表红黑树的高度得有二十多层每查一次就要做二十多次磁盘I/O这对数据库来说是不可接受的。B树在这方面做了两个关键优化。第一非叶子节点只存索引键值不存数据一个节点能放下更多的键树就变矮了。InnoDB一个页默认16KB主键如果是bigint占8字节一个页大概能存上千个键2000万条数据的树高也就三到四层。第二叶子节点之间用双向指针串联范围查询拿着起始位置顺着链表往后扫就行了这效率哈希表根本没法比。注意MySQL的InnoDB引擎是聚簇索引数据本身就在主键索引的叶子节点上。你建了二级索引之后叶子节点存的是主键值查的时候还要回表拿数据。这里埋个坑后面讲覆盖索引的时候会详细说。2.2 主键索引和二级索引的区别这个问题看着简单但很多人答不完整。主键索引聚簇索引叶子节点存的是整行数据二级索引叶子节点存的是主键值。所以你在建表的时候选主键要慎重——如果主键是随机UUID插入的时候数据页会频繁分裂性能很难看。这也是为什么现在主流都推荐自增主键或者有序的雪花ID。有个面试追问很常见如果一个表没有主键怎么办答案是InnoDB会选第一个非空的唯一索引作为聚簇索引如果连这个都没有就生成一个隐式的6字节rowid。这个隐藏在背后的机制会影响你对表结构的判断我见过不少新人建表不设主键真的不建议这么干除了性能问题后续数据同步和binlog解析都会很难受。2.3 联合索引的存储结构联合索引其实就是一个多列排序的B树。假设你有索引(a, b, c)物理存储上先按a排序a相同再按b排序b也相同再按c排序。这个排序规则直接决定了最左前缀原则为什么存在——你跳过了a直接用b去查B树就不知道从哪儿开始遍历索引自然就废了。还有一个容易被忽视的点联合索引和单列索引是竞争关系。同一个表你建了(a, b)又建了(a)MySQL优化器大概率会走(a, b)因为它的信息量更大。所以建索引的时候要想着合并别一根一根地插。3. 面试必问索引失效的几种情况3.1 这条SQL为什么没走索引索引失效的题目在面试中出现频率极高就我统计的样本差不多三分之二的候选人会被问到这里。而且这个问题通常会配一道活题给你一条实际SQL让你判断有没有走索引。我把常见的失效场景整理成了一张表都是我实际碰到的场景失效原因示例对索引列使用了函数索引存的是原始值函数破坏了顺序WHERE DATE(create_time) 2025-01-01隐式类型转换优化器认为需要转类型放弃索引WHERE phone 13800138000phone是varchar前缀模糊匹配无法定位起始位置WHERE name LIKE %张索引列参与了运算破坏了索引值的有序性WHERE age 1 30用OR连接非索引列存在一条全表扫描的路径WHERE id 1 OR name 张三联合索引不满足最左前缀无法使用索引树的排序规则索引(a,b)但条件只写了bNOT IN / NOT EXISTS优化器认为回表代价过高WHERE status NOT IN (1, 2)这里我要多说一句不常见的坑。IS NOT NULL在某些数据分布下也可能走全表扫描因为MySQL的优化器是基于代价估算的如果它认为null值占比极低直接扫表反而比走索引快。理解了这一点你就明白索引失效的底层逻辑就三条破坏了有序性、优化器认为走索引代价更高、数据分布太拉胯。3.2 最经典的一道面试题面试官最喜欢拿来当开场白的一道题是select * from user where name like %张三%这个查询怎么优化很多人第一反应说放弃这个查询需求改成搜索引擎这种回答不接地气在实际业务里我们就得靠数据库扛。我当时遇到的场景是用户表800万条数据做模糊搜索确实不走索引全表扫描400多毫秒勉强能撑但已经不好受了。我给面试官的优化思路是这样的第一如果是前缀匹配张三%直接建普通索引就行B树的排序特性可以支持前缀匹配加速。第二如果必须是中缀或后缀匹配可以考虑给表加冗余列存倒序值后缀匹配%三可以用reverse(name) like 三%加速。第三覆盖索引能不能救场要看查询列是否都在索引里比如只查id和name就能走索引下推ICP多少有点效果。第四实在不行就上全文索引或者同步到ES这属于架构层面的方案了。这个回答的完整度基本就能看出候选人的实际经验。纸上谈兵的人只会说加索引有经验的人会说先看explain的type级别再看过滤字段的区分度然后看是否能把随机IO转成顺序IO。4. 高阶必考覆盖索引与索引下推4.1 覆盖索引为什么快覆盖索引指查询的所有字段都在索引里不需要回表。这事的核心收益是省掉了回表的随机I/O。B树的数据库在磁盘上做随机读和顺序读成本相差可能有一个数量级。所以把高频查询的字段塞进索引里是性价比极高的优化手段。我举个具体的例子。订单表有索引(shop_id, order_no)业务上要查某个店铺最近的订单号列表SQL写成select order_no from orders where shop_id 123 order by order_no desc limit 10。由于order_no已经在索引里面MySQL扫描完索引就可以返回结果完全不用碰数据行。要是再加个字段比如order_amount就必须要回表了因为amount不在这个索引里。这也是为什么InnoDB把主键放在二级索引的末尾——每一个二级索引都自动携带了主键字段。你建索引(shop_id)的时候实际上索引结构是(shop_id, id)。所以一个查询如果只需要id和shop_id就算你只建了shop_id的索引它也能实现覆盖索引。这个细节很隐蔽但面试时说出来会让面试官眼前一亮。4.2 索引下推是什么索引下推Index Condition PushdownICP是一块硬骨头。MySQL 5.6引入的优化核心思想是把WHERE条件里面、索引覆盖不到的部分从Server层下推到存储引擎层让存储引擎先把不符合的行过滤掉减少回表次数。网上有很多话术版本我来说个容易理解的场景。联合索引(username, age)查询条件是username like 张% and age 25。如果没有索引下推存储引擎会把所有username以张开头的行都捞出来然后回表取整行数据Server层再对age条件做过滤。如果有索引下推存储引擎在遍历索引的过程中直接就把age不等于25的记录跳过去了只对剩下的少数行回表。这个优化在索引区分度低、回表代价高的场景下收益非常明显。但注意了索引下推对覆盖索引无效因为覆盖索引压根不用回表也就不需要减少回表的逻辑对分区表支持也有限制。面试时能主动提一句ICP开启条件是二级索引、且没有覆盖索引说明你确实使用过explain看到过Using index condition这个标志。5. 最容易被问但容易答飞的倒排索引5.1 倒排索引和数据库索引的关系热门搜索词里倒排序索引出现了好几次这其实是MapReduce经典案例也侧面说明现在面试的广度在增加。倒排索引最典型的应用是全文搜索引擎——比如Elasticsearch、Lucene的核心结构。我得先澄清一个概念倒排索引和普通B树索引是两回事。B树解决的是我有这个主键/键值怎么快速找到数据倒排索引解决的是我有一段文本怎么快速找到包含某个词的所有文档。一个是正向查一个是反向查。倒排索引的结构也不复杂。先对每个文档做分词然后维护一张词表每个词后面挂一个文档ID列表叫posting list。你搜索引这个词搜索引擎查一下词表直接拿到包含索引的所有文档ID像查字典一样快。5.2 MapReduce排序与倒排索引的实现思路这是大数据方向的经典题第1关MapReduce排序-倒排序索引。本质是让你用MapReduce实现一个简单的倒排索引构建过程。我用大白话讲一下流程Map阶段读取每个文档按词分词输出(词, 文档ID)这样的键值对。Partition和Shuffle阶段框架把同一个词的所有键值对送到同一个Reducer并且按照词排序这就是排序的作用。Reduce阶段同一个词会收到来自不同文档的多个记录把这些文档ID汇总成一个列表输出(词, [文档ID1, 文档ID2, ...])。面试里问这个重点其实不是让你手写Hadoop代码而是考察你有没有理解通过MapReduce的排序特性可以把散落在多个节点上的数据按目标key汇聚在一起。这种分布式思维和数据库索引的局部性原理是相通的——都是通过预排序来加速后续查询。6. 实战向一张订单表教你设计索引6.1 用一个业务场景把索引知识串起来面试到最后通常还有一个场景设计题。我拿一个最经典的来演练订单表总数据量3000万核心查询有两个查某个用户最近的订单列表select * from orders where user_id ? order by create_time desc limit 10查某个商家某段时间内的订单统计select count(*) from orders where shop_id ? and create_time between ? and ?第一个SQL比较简单建(user_id, create_time)联合索引就行利用联合索引里create_time的有序性直接倒序取10条完美。第二个SQL看着也简单建(shop_id, create_time)联合索引。但这里有个很大的坑count(*)统计需要遍历所有符合条件的索引条目如果你只需要知道数量更优做法是建立(shop_id, create_time, id)的覆盖索引这样计算count时连回表都省了。这恰恰是覆盖索引优化count的常见套路。再来看一个挖坑题如果第一个SQL里还要加一个and status 1的条件会怎么样理论上联合索引应该是(user_id, status, create_time)还是(user_id, create_time, status)这是一个很多社招候选人都会卡住的点。实际上如果status的区分度不高就几个值把它放中间并不会让索引树变得高效反而破坏create_time的连续性。更好的方案可能是(user_id, create_time)联合索引同时把status作为一个辅助过滤条件因为命中user_id和create_time之后数据量已经很小再用status过滤也没多大开销。提示设计索引的第一步永远是分析真实SQL的where条件和order by/group by字段第二步才是考虑区分度和回表成本。不要一上来就想着把所有条件都塞进一个索引。6.2 索引设计的三个原则和一个反例我总结过一套实战中的索引设计原则分享给你原则一最左前缀优先查询频率高的列放最前面但别把区分度低的列比如性别放第一位。原则二尽可能覆盖高频查询把select的列塞进索引末端让回表消失。原则三控制索引数量一个单表索引建议不要超过5个因为每次写入都要维护索引树索引太多会拖垮写性能。一个反例是我曾经接手过一个业务表16个索引查一条记录都要几百毫秒。为什么因为查询优化器在选择索引的时候需要基于统计信息做判断索引一多估算时间就长而且索引互相干扰导致选择不是最优。后来我们砍到5个联合索引把高频查询全部覆盖效果确实立竿见影。6.3 什么时候该用其他搜索引擎数据库索引不是银弹。当你的查询涉及到全文检索、地理位置搜索、或者需要很复杂的聚合分析时MySQL不是最好的工具。全文检索用Elasticsearch倒排索引天生就是干这个的。地理位置搜索用Redis的GEO或者PostgreSQL的PostGIS。大规模OLAP分析用ClickHouse这类列式存储数据库宽表聚合性能碾压MySQL。面试时遇到索引能解决所有性能问题吗一定别答能。更好的回答是索引解决的是OLTP场景下高频等值、范围查询的加速问题。超出这个场景我们要考虑的是架构层面的选型。7. 面试实战记录一套完整的索引问答流程为了让你更有代入感我模拟一下真实面试现场。面试官坐对面表情平静抛出一张纸有一张用户表 user(id, name, phone, status, created_at)数据量1000万。现有一条查询特别慢select name, phone from user where status 1 order by created_at desc limit 20你会怎么优化这道题我把好的回答路径和踩坑路径都分析一下。踩坑回答直接说给status加索引、给created_at加索引。面试官追问然后呢就没下文了。好的回答分四步先看执行计划。大概率走了status索引然后文件排序filesort因为created_at和status不在同一个索引里。建一个联合索引(status, created_at)这样记录先按status过滤同时created_at有序返回不再需要排序。选字段name和phone不在索引里需要回表。如果这是超级高频的查询可以把联合索引扩展成(status, created_at, name, phone)实现覆盖索引避免回表。最后还要评估status的区分度。如果status是0或1区分度极低MySQL统计后觉得走索引也捞不出来多少值可能直接选择全表扫描了这时就得换个思路比如考虑把高热度数据单独存Redis缓存。这套思路走下来面试官基本就知道你是真的调过优的。尤其是第4步绝大多数背题的人是想不到的。8. 关于索引你必须知道的几个为什么8.1 为什么说主键默认就是索引这里要理解InnoDB的物理存储。表本身就是一棵以主键为key的B树数据行是叶子节点上的值。所以主键天然是聚簇索引不需要额外建。这也是为什么mysql 创建索引的热搜词下面总有人问重复问题——很多人以为主键和索引是两个独立的东西。8.2 为什么无索引的字段偶尔也能查到快结果数据量很小的时候全表扫描也可能比走索引快。这个反常现象背后的逻辑是数据量小一次全表扫描就是几个数据页的顺序读而走索引需要先查索引树再回表是两次随机读。MySQL的优化器会根据表大小估算代价所以小表真的没必要加索引。8.3 为什么InnoDB要额外存储一个唯一索引这个问题偏冷但是爱考原理的面试官会提。唯一索引和普通二级索引的区别在于唯一索引的叶子节点上不只有主键值还存储了这个值在插入时需要检查唯一性的约束。二级索引可以重复唯一索引不能重复。物理上它们存储方式相似但逻辑语义不同。8.4 关于Oracle和MySQL的索引差异热词里有oracle视图加索引、oracle索引相关的问题。Oracle和MySQL的优化器机制有很大不同。Oracle基于CBO基于代价的优化器支持位图索引、函索索引、分区索引等很多类型MySQL的索引类型相对简单主要就是B树和Hash。Oracle视图不会自动使用基表的索引除非视图语句里写了或视图是物化视图这也是面试中容易答错的地方。9. 维护索引的日常功课9.1 定期用工具查看索引状态MySQL里有一条非常经典的分析语句-- 查看索引使用情况 SHOW INDEX FROM orders; -- 分析表优化索引统计信息 ANALYZE TABLE orders; -- 查看特定SQL是否使用了索引 EXPLAIN SELECT * FROM orders WHERE user_id 10086;在explain的输出里重点关注几个字段type从好到坏依次是const eq_ref ref range index ALLrows扫描行数估算Extra如果出现Using filesort或者Using temporary就需要考虑索引优化了。9.2 索引冗余要定期清理开发周期变长之后很多临时建的索引会变成僵尸索引。我曾经上线前排查过一张表发现有两个索引完全互相覆盖等于白白浪费了接近2GB的磁盘空间。定期清理冗余索引应该是DBA日常巡检的固定项目。排查方法也很简单打开慢查询日志收集一段时间结合pt-duplicate-key-checker这类工具去分析。不过对很多中小团队来说可能没有专职DBA那我在附录里给你一个最简易的自查清单查看是否有前缀完全相同的索引查看没有被任何查询使用的索引可以通过performance_schema.table_io_waits_summary_by_index_usage或者开启userstat来观察删除索引之前先在线测试避免高峰期操作9.3 索引失效自查清单我总结了一份避坑清单踩过的坑都在这了确认字段是否有隐式类型转换尤其varchar字段与数字比较确认排序字段和过滤字段是否都在同一个联合索引确认OR连接的条件是否都参与了同一个索引确认是否对索引列使用了内置函数确认联合索引的顺序是否与where条件的顺序匹配不是必须完全一致但最左前缀必须保证这套自查清单在面试的时候直接背出来比你说我懂索引优化有说服力得多。10. 扩展延伸当索引遇到排序和分组10.1 filesort到底有多伤很多SQL慢不是因为查询条件而是因为排序。MySQL在无法利用索引来满足order by的时候就会在内存或磁盘上做文件排序filesort。数据量一大filesort就要把数据分块写到临时文件里再归并排序这个开销极其恐怖。所以在设计索引的时候你要把order by的列跟where条件放在同一个索引里面。比如where user_id ? order by create_time desc联合索引(user_id, create_time)直接告诉你数据已经有序查询直接就快了。10.2 group by其实也耗索引group by本质上分组计数。如果group by的字段不在索引中MySQL会对全表做临时表分组。你可以利用联合索引让分组字段有序这样执行计划里就不会出现Using temporary。举个例子select shop_id, count(*) from orders group by shop_id。shop_id没有索引的话MySQL会把3000万行全部load到内存里建哈希表做分组。给shop_id建了索引之后扫描索引就可以按shop_id的顺序直接累积计数内存占用和执行时间都会大幅下降。11. 最后分享一点个人感受写了这么多我想说点跟面试题稍微不一样的东西。索引这个话题之所以常问常新是因为它连接了计算机的底层原理磁盘I/O、数据结构、排序算法和上层业务设计查询模式、数据分布、读写比例。我在实际调优中最深的体会是不要一上来就动手加索引先花时间看慢查询日志、看explain、了解业务对这个查询的期望响应时间。绝大多数性能问题根本不需要什么高深技巧一张覆盖索引就搞定了真正困难的地方在于怎么在正确的地方放正确的索引同时不拖累写性能和其他查询。还有一个个人经验面试准备索引别只刷索引失效的原因试着把每道题都还原成我遇到过的真实场景。我当时给自己出了一套自测题包括为什么明明建了索引却不用为什么这个索引能覆盖查询但不能覆盖排序为什么增加一个字段后索引突然失效了把这几个为什么彻底搞懂之后再去看网上那些八股文一眼就能看出哪些是真正重要的知识点哪些只是为了凑数。这套思路放到日常工作中同样成立。慢SQL从来不会自己变快但它会诚实地告诉你索引设计哪里出了问题。希望你面试顺利也希望大家生产环境的SQL都健步如飞。