写这篇文章的时候我刚帮一个学弟排查完一个线上慢查询。那条SQL就查了一行数据却整整跑了2.3秒导出执行计划一看type是ALL走的是全表扫描连索引都没有建。建完索引之后同样的SQL降到了0.03秒查询速度提升了差不多80倍。这让我想起很多刚入门MySQL的朋友背了一堆索引相关的概念聚簇索引、非聚簇索引、回表、覆盖索引、最左前缀面试的时候能顺口说出来可一旦落到真实的SQL调优、表结构设计上就完全不知道这些概念到底怎么用。这篇文章要把这三块内容彻底讲透聚簇索引是什么、非聚簇索引是什么、回表到底怎么发生的。我不仅会讲概念还会讲清楚为什么InnoDB要这么设计并且会带你看真实的执行计划让你彻底搞清楚索引在你电脑上到底干了什么。标题里说的“一篇扫清盲区”不是我吹的看完你能搞懂80%的MySQL索引相关问题面试题和实际调优都能顶上去。1. 为什么索引能提速先搞懂InnoDB的数据存放方式很多新手有个误区觉得索引就是一张单独的“目录表”和数据分开存放。这个理解在MyISAM时代还算沾边但在InnoDB里完全不是这么回事。1.1 InnoDB用B树组织数据的底层逻辑InnoDB其实是一个索引组织表也就是说整张表的数据本身就是按照索引结构来存放的。这里说的“索引结构”就是B树。你可以把B树想象成一个多层级的书架每一本书都有固定的位置从上到下、从左到右都是按顺序排好的。你要找某一本书不需要从头一本一本地翻直接从书架的索引标签一层一层往下找就行。具体到InnoDB的存储机制我给你拆开说数据文件是以页为单位的一个页默认16KB这是InnoDB读写的最小单位页和页之间通过双向链表连接保证顺序读的效率每个页内部的数据行通过单向链表按主键顺序排列B树的每个节点就是一个页非叶子节点只存索引键值和指向子页的指针不存具体数据1.2 为什么InnoDB偏偏选B树而不是其他结构这个问题面试问的频率极高我直接把几个常见的数据结构对比放到一个表里这样最直观数据结构查询复杂度写入复杂度区间查询磁盘IO次数为什么InnoDB不选它哈希表O(1)O(1)不支持1次范围查询直接废掉WHERE age 20这种直接噎死二叉树O(logN)O(logN)一般树高不可控数据量大时树会非常深每个节点只有两个分支浪费磁盘IO红黑树O(logN)O(logN)一般logN层级深树高能到20多层深度太深不适合磁盘场景B树O(logN)O(logN)弱高度低非叶子节点也存数据一页能存的索引项太少树还是不够矮B树O(logN)O(logN)强高度极低它赢了非叶子节点只存键值一页能塞大量索引项B树赢在两个点非常关键第一非叶子节点只存索引键值不存数据。这意味着一个16KB的页能放下更多索引键值树的层数就更矮。一般来说一张千万级别的表主键是BIGINT类型B树的层数也就3到4层。也就是说你要查任意一行数据最多做三到四次磁盘IO就够了。这比红黑树的几十次IO强了好几个量级。打个比方就是红黑树像爬几十层的楼梯B树像坐电梯直达每多一层树高就代表着一次磁盘IO的代价。第二叶子节点之间是双向链表连接的。这个设计让区间查询变成线性操作你先找到范围的起点然后顺着链表一个一个往后走就行。这也是为什么InnoDB在范围查询上吊打其他结构的原因之一。1.3 数据页的结构与行记录的真实形态我还是想提醒一个容易忽略的地方既然一个页是16KB那一个页能放多少数据行就取决于你这一行数据有多大。假设一行数据平均1KB那一个页能放16行如果一张表有160万行数据就需要10万个叶节点。再假设一个非叶子页能存放1000个索引键值和对应的页指针那两层结构就够了。算一笔账这也解释了为什么主键要尽量短一个页能存多少个索引项取决于索引键的大小索引键越小一页能存的键值越多树就越矮所以主键选整型比选UUID字符串在存储和性能上要划算得多这个点我后面讲主键设计的时候还会再展开讲。2. 聚簇索引和非聚簇索引一张表两套索引体系现在我们把焦点回到标题的核心概念上。先说一个最关键的结论在InnoDB里聚簇索引就是表数据本身表数据就是聚簇索引。这句话你如果搞懂了后面所有的概念都顺了。2.1 聚簇索引那棵“表即索引”的B树InnoDB的表在存储层面会按照主键构建一棵B树这棵树的叶子节点直接存放整行数据。这就是聚簇索引也常被叫做聚集索引或主键索引。它的核心特点我直接列出来叶子节点存的是完整的数据行不是某个列的副本数据行在物理存储上按照主键顺序排列每张表只有一个聚簇索引因为数据行只能有一种物理排列顺序聚簇索引的叶子节点就是数据页本身这里有一个关键的设计细节如果你在建表时没有指定主键InnoDB会去找第一个非空的唯一索引来当聚簇索引。如果连唯一索引都没有它就隐式生成一个6字节的ROWID来当主键。也就是说InnoDB必须要有一棵聚簇索引树来组织数据物理上就没法避免这不是可选项是必选项。我建议所有做MySQL开发的朋友都养成一个习惯建表必须显式指定主键。一个没有显式主键的表万一InnoDB用隐藏ROWID当聚簇索引你是没法通过正常SQL直接掌握那个ROWID的后续做数据归档、大批量更新、分页深翻页这类操作都会非常痛苦。我早年间接过一个历史遗留系统一张表几十万数据没有主键结果要做数据清理时无从下手后来只能额外加一列自增ID重建表折腾了大半宿。2.2 非聚簇索引那棵“以键值换位置”的B树除主键之外建的索引都叫二级索引也叫辅助索引或者说非聚簇索引。它和聚簇索引最大的区别在于叶子节点存放的内容不同二级索引的叶子节点存的是索引列的值加上主键的值。这么说还是有点抽象我拿个具体例子来说。假设我们有一张用户表结构大致是这个样子CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL, age INT NOT NULL, email VARCHAR(64) DEFAULT NULL, PRIMARY KEY (id), KEY idx_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有两棵B树第一棵是以id为聚簇索引的树叶子节点存的是整行数据第二棵是以username为二级索引的树叶子节点存的是username的值和对应行的id值。这里要特别强调二级索引的叶子节点里存的是主键值不是整行数据的物理地址。这个设计非常见智慧因为它避免了二级索引里的页指针因为数据移动而失效。当聚簇索引的数据行发生页分裂时二级索引完全不用动只需要主键值保持一致就行。你可以理解为二级索引相当于一个分类目录目录里只写了“关键词页码”你按关键词查到了页码再翻到对应的正文页才能看到完整内容。那么问题来了如果一张表有主键索引、普通索引、联合索引、唯一索引那就有好几棵B树。是的每创建一个索引就等于额外建一棵B树。所以索引越多写入数据时的维护成本就越高代价都在写入路径上。这也是为什么我一直强调别盲目建索引索引是为了查询服务的不是用来“打卡”的。2.3 聚簇索引和非聚簇索引的区别速查表这里我把两个概念的核心差异整理成一张表方便你记对比项聚簇索引非聚簇索引二级索引每张表数量只能有1个可以有多个叶子节点存储完整的数据行索引列值 主键值数据物理顺序按主键顺序排列索引键顺序但与数据存储顺序无关是否可自己指定由主键决定自主创建的列/组合查询是否需回表不需要直接拿到数据需要时还得根据主键再查一次索引维护成本建表时就有无法移除每次UPDATE/INSERT/DELETE都要维护2.4 结合原理解释为什么回表成了“必然”到这里回表这个概念的底层逻辑其实已经出来了。你查二级索引时得到的是“索引列的值 主键值”。但你要的完整数据只在聚簇索引树里。所以你先在二级索引B树里查到主键值再拿着这个主键值去聚簇索引B树里查一遍才能拿到完整数据行。这个过程就叫回表。这不难理解但很多人没想清楚的是回表绝不是“可选项”而是InnoDB存储引擎的天然行为。只要你的查询列没有完全被二级索引覆盖就必须回表。如果你查询的列恰好都在二级索引里那就不用回表这种情况叫覆盖索引后面我会细讲。打个比方回表就像你在一本书的目录里找到了某个知识点在第128页你得翻到第128页才能看到完整内容。第二次翻书这个动作就是回表。3. 回表到底是怎么回事一次查询走过的完整路径这一节我带你走一遍完整的查询路径用最直白的方式把回表的每一步都摆出来。网上很多文章讲回表就一话带过“二级索引查到主键再去主键索引查”但真正到执行计划层面怎么体现、哪些场景回表多、哪些场景能避免回表很多新手还是懵的。3.1 一次完整查询的内部执行流程继续用上面的user表我们执行一条SQLSELECT id, username, email FROM user WHERE username 张三;这条SQL在InnoDB内部是怎么走的我按步骤拆给你看优化器看到where条件是username发现username上有二级索引idx_username决定走idx_username从B树根节点开始逐层向下查找定位到username等于张三的叶子节点叶子节点里没有完整数据行只有username的值和id主键值假设找到了id等于10086InnoDB拿到id10086继续去主键索引B树里再走一遍B树查找这次叶子节点存的是完整数据行把id、username、email全部取出来返回从第4步到第5步就是回表。如果这条SQL走了两个索引比如where里又是一个普通索引那就要在主键索引上定位两次才能拿到两行完整数据每次定位都是一次完整的B树查找。当回表的次数非常多时比如非聚簇索引命中了1万行那就得回表1万次性能急剧下降。这就是为什么有些SQL明明用了索引却依然很慢的原因之一。3.2 什么是索引覆盖和覆盖索引刚才那个SQL里返回了email字段username索引里没有email所以必须回表。那如果我改成这样SELECT id, username FROM user WHERE username 张三;查询结果只需要id和username两个字段。id是主键username在二级索引里就有二级索引的叶子节点存的就是“username id”。这种情况下InnoDB发现要的字段二级索引全都有就不用回表了。这种“索引本身就覆盖了查询所需全部列”的情况就叫覆盖索引执行计划里的Extra字段会显示“Using index”。覆盖索引是优化回表的重量级武器。怎么设计覆盖索引最简单的办法是建联合索引把高频查询需要的列都塞进索引里。比如你有一类高频SQL是查username要带出email那可以建一个(username, email)联合索引这样通过username查email就完全不用回表了。但这也要权衡因为联合索引本身是有代价的不能为了一个不常用的查询去建一个宽索引。覆盖索引是性能优化里的常用招但也要看使用频率和数据量。高频的核心查询做一个覆盖索引非常值偶尔跑一次的统计SQL就没必要了。3.3 回表与覆盖索引的性能对比为了让你对回表代价有直观感受我给你放一个我实际测试过的对比数据测试环境是MySQL 8.0.32单表500万行数据id为自增主键查询类型SQL示例是否回表平均耗时主键查询SELECT * FROM user WHERE id 10086不回表0.015秒二级索引回表SELECT email FROM user WHERE username 张三回表1次0.038秒二级索引回表多行SELECT email FROM user WHERE age BETWEEN 20 AND 30回表数百次0.870秒覆盖索引查询SELECT id, username FROM user WHERE username 张三不回表0.021秒同样是通过二级索引定位覆盖索引比回表快了不少因为少了一次聚簇索引的B树查找。而当回表行数多起来以后差距会被拉得非常明显。所以你在真实开发里面对一个慢查询时第一反应不是去猜而是先看执行计划看它有没有回表、回表多少行。有经验的DBA经常用“Using index condition”和“Using where”这些关键词判断回表情况。这个坑我后面专门用一节来讲。4. 用EXPLAIN实操让索引跑起来眼见为实前面讲了一堆理论可能有的朋友觉得还是悬。我理解因为索引这东西你说得再漂亮不实际看执行计划都没办法真正相信。这一节我用真实的SQL执行计划带你一步步验证聚簇索引和非聚簇索引的行为差异。4.1 准备一张测试表和测试数据首先创建一张测试表插入一批数据。为了模拟真实的线上场景我建了一张订单表数据量大概100万行左右CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;在实际测试中我用存储过程插入了100万行模拟数据单次批量插入性能不错这里不做详细展开。关于存储过程的写法网上很多我只提醒一点大批量造数据用存储过程分批提交别一条一条INSERT太慢。4.2 用EXPLAIN看聚簇索引的查询路径先看主键查询EXPLAIN SELECT * FROM order_info WHERE id 500000;执行计划结果中几个关键字段值得关注type是const意思是常数级查找性能极优key是PRIMARY说明走的是主键索引聚簇索引Extra里没有任何特殊说明因为没有回表问题这里type为const是聚簇索引直接命中目标行数据的典型表现。整个过程就是一次B树的等值查找从根节点一路走下来到叶子节点直接拿到整行数据。再试试主键范围查询EXPLAIN SELECT * FROM order_info WHERE id BETWEEN 500000 AND 500100;执行计划的type是rangekey是PRIMARYrows大概显示101。因为聚簇索引叶子节点自带顺序性所以范围查询直接走索引从500000开始往后扫描100行数据不会全表扫描。这也是聚簇索引的核心优势尤其是范围查询特别吃这个特性。MyISAM虽然也有索引但数据和索引分离范围查询时需要在索引和数据文件之间来回跳InnoDB直接数据就是索引范围查询往里走沿着叶子节点的链表一路向后拿就行。4.3 用EXPLAIN看非聚簇索引与回表行为接下来看二级索引查询到底怎么走EXPLAIN SELECT * FROM order_info WHERE user_id 12345;执行计划的结果是key显示idx_user_id说明走的是二级索引key_len长度为8对应BIGINT的8字节长度Extra字段可能会显示Using index condition或者空取决于MySQL版本和查询条件这里我要重点说下Extra的几种情况非常容易踩坑Extra内容含义是否需要回表空/Using where通过索引定位到主键再回表定位完整数据是Using index查询列完全包含在索引中无需回表否Using index condition索引条件下推部分条件在索引层过滤但仍需回表取数是Using where; Using index在索引层完成过滤且索引包含所需所有列否你看非聚簇索引的查询到底回不回表从Extra基本能判断个八九不离十。平时排查慢查询EXPLAIN是最快的手段。4.4 索引下推ICP是什么回表前的“预筛选”顺带补充一个和回表强相关的优化技术——索引下推Index Condition Pushdown简称ICP。这个是MySQL 5.6引入的优化简单说就是在没有ICP之前二级索引在叶子节点定位到一批主键值后会一股脑全部回表回到聚簇索引里再去判断其他条件是否满足。有了ICP之后存储引擎会在二级索引的叶子节点处先把能用索引列判断的条件过滤掉减少回表次数。还是用例子说话。假设有这样一条SQLSELECT * FROM order_info WHERE user_id 100 AND status 1;其中idx_user_id是user_id上的单列索引。在有ICP之前InnoDB根据user_id 100拿到大量主键值全部回表回到主键索引后再逐行判断status 1。这意味着100万个满足user_id 100的行都要回表哪怕最后只有500行status 1。打开ICP之后InnoDB在二级索引叶子节点定位时就能额外判断status这个条件前提是status也在二级索引里或者在某些情况下通过其它条件提前过滤不符合条件的直接不要回表数量大幅减少。这个优化默认是开启的你不需要手动配置。但理解它对你解释Extra的执行计划很有帮助。很多面试官喜欢拿这个追问面试时你能把ICP讲清楚会在基础题上拉开差距。4.5 为什么有些索引建了但MySQL还是不走这也是新手经常踩的坑我直接列几个最常见的场景对索引列使用函数或表达式比如WHERE YEAR(created_at) 2024索引直接失效隐式类型转换比如varchar类型的order_no却用WHERE order_no 123456这样的数字去匹配使用LIKE前缀通配比如WHERE username LIKE %张%无法使用索引OR连接时如果其中一个条件没有索引整个查询可能直接放弃走索引这些失效原因的原理其实都指向同一个点索引是B树B树依赖键值的有序性来做快速查找。函数、类型转换、前导通配符都会破坏这种有序性导致B树根本没法利用顺序结构去快速定位。5. 主键设计、联合索引、索引失效这些实操细节决定成败索引问题在面试中出现的频率高但真正会问到你头皮发麻的往往是主键设计、联合索引和索引失效这些贴近实战的细节。这一节我把我踩过的坑和总结出来的经验一次性讲清楚。5.1 主键为什么推荐自增整型而不是UUID前面我提过聚簇索引的数据行按主键顺序物理排列。如果主键是自增整型那么新插入一行数据时直接把数据追加到当前索引的最后一个数据页就行最省事。如果主键是UUID这种随机字符串每次插入的B树位置都是随机的大概率会插在页面中间导致页分裂。页分裂会引发以下连锁反应原来在一个页里连续存放的数据被拆到两个页里部分数据行的物理存储位置发生变化页指针重写写放大磁盘IO增大页分裂后可能出现空间碎片所以在线上的高并发写入场景下自增主键是最优选择。UUID当主键也不是完全不能用前提是你能接受写入性能下降且没有更好的业务主键可选。另外主键类型也要尽量短。BIGINT是8字节比VARCHAR(32)的字符串短很多这就意味着非叶子页能容纳更多索引键B树更矮磁盘IO更少。这个细节很容易被忽视但影响很大。5.2 联合索引的列顺序决定命运联合索引和回表机制紧密相关。比如你建了一个(u1, u2)联合索引那么索引叶子节点存的值就是“u1 u2 主键值”。回表次数越少性价比最高的方式就是让高频查询尽量落到联合索引上。列顺序的核心原则我总结成三条非常实用先把等值查询的列放在前面。where a 1 and b 2这种查询a和b都放最前面能最大程度利用联合索引的定位能力范围查询的列放最后。比如where a 1 and b 100b适合放后面因为联合索引一旦出现范围查询后面的列就无法利用索引去定位了覆盖高频查询的列。如果你经常查a和b并返回c那就建(a, b, c)联合索引可能直接命中覆盖索引省掉回表我举个反例一张订单表上建了(user_id, status)联合索引结果业务方写SQL时用了WHERE status 1 AND user_id 2查询优化器也不傻它会自动调整条件的顺序来匹配索引所以这个反例问题不大。真正的问题是如果联合索引是(user_id, status)但你高频查询是WHERE status 1 GROUP BY user_id那这个索引的威力直接打折因为最左前缀原则决定了你得先有user_id才轮得到status。5.3 最左前缀原则和它背后的B树逻辑聊到联合索引就绕不开最左前缀原则。这个原则的标准说法是联合索引(a, b, c)查询条件必须从最左边的a开始才能用到这个索引跳过a直接用b或c索引大概率失效。原理其实不复杂联合索引在B树里按(a, b, c)的顺序从左到右排序先按a排再按b排最后按c排。如果你直接查b相当于跳过了最左侧的排序键B树的有序特性就发挥不出来了。但是有一点容易误解最左前缀不是说查询条件里必须包含全部列而是必须包含最左边的列。a 1 and b 2可以走(a, b, c)这个索引a 1 and c 3也可以走只是c没法精确匹配变成索引条件下推的一部分。而b 2 and c 3这种完全跳过a的查询就没法用这个联合索引了。5.4 索引失效的场景一篇排查手册我把自己在实战中踩过的索引失效场景整理成一个排查清单建议收藏场景示例失效原因解决方案对索引列使用函数WHERE YEAR(created_at) 2024破坏索引列的有序性改成范围条件 created_at 2024-01-01 AND created_at 2025-01-01隐式类型转换WHERE order_no 123456order_no是varcharMySQL自动把字符串转成数字索引失效确保类型一致写成 WHERE order_no 123456LIKE前导通配WHERE username LIKE %张%无法利用B树的前缀匹配改成 username LIKE 张%联合索引不满足最左前缀WHERE b 1联合索引是a,bB树排序逻辑不允许调整SQL或新建索引OR两边有一边无索引WHERE a 1 OR b 2只有a有索引优化器无法同时利用索引全表扫描两边都加索引或拆成UNION索引列参与运算WHERE amount * 0.9 100表达式破坏索引值改写 SQL 为 amount 100 / 0.9IS NOT NULL可能失效WHERE name IS NOT NULL优化器判断全表扫描更划算时放弃索引重新设计SQL让优化器走索引或者用覆盖索引还有一点要特别说明MySQL优化器决定走不走索引看的是成本估算不是“只要有索引就必须用”。当索引取出的行数超过全表的一定比例大概20%到30%优化器会觉得回表太多次不如直接全表扫描。这叫索引回表代价过高所以优化器选择放弃索引。遇到这种情况你要么优化SQL逻辑要么设计覆盖索引而不是强制加FORCE INDEX。6. 实战调优案例一条慢SQL从2秒到30毫秒的完整过程讲了这么多理论我想用一个完整的调优案例来收尾。这个案例是我在实际项目中处理的流程非常典型可以给所有做后端开发的朋友当模板用。6.1 背景某个业务系统有一张订单流水表数据量大概800万行。业务方反馈最近新增了一个查询功能页面打开特别慢。我看了一下慢查询日志有一条SQL平均耗时2秒左右高峰期能到5秒已经严重影响用户体验了。这条SQL大致是这样的SELECT user_id, order_no, status, amount FROM order_info WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;6.2 排查步骤第一步看表结构。user_id上有单列索引idx_user_idcreated_at上没有索引。第二步跑EXPLAINEXPLAIN SELECT user_id, order_no, status, amount FROM order_info WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;执行计划里的type是refkey是idx_user_idExtra里出现了Using filesortrows估算显示466630。Using filesort就是罪魁祸首。数据先通过user_id索引查到46万行再对这些数据做文件排序最后取20条中间还伴随着大量回表。这个排序产生的临时表和磁盘IO开销是巨大的。6.3 优化方案分析之后我设计的优化方案是建立一个联合索引ALTER TABLE order_info ADD INDEX idx_user_created (user_id, created_at);这个索引背后的逻辑很简单user_id等值条件先定位到目标用户的全部订单created_at字段在联合索引里已经排好序ORDER BY created_at DESC直接利用索引顺序不需要额外排序同时我调整了查询列让部分列命中覆盖索引减少回表优化后重新执行EXPLAINtype还是refkey变成了idx_user_createdExtra里没有Using filesort了rows估算从46万降到几百行查询耗时从2秒多直接掉到30毫秒左右。对于一些高频查询我把查询列都设计进联合索引中直接命中覆盖索引回表的开销也一并省掉了。6.4 复盘这个案例教会了我们什么这个案例里最有价值的点不是那条SQL本身而是它体现了索引调优的完整思路先看执行计划找到问题瓶颈在哪里是扫了太多行还是排序太慢还是回表太多联合索引的设计要结合查询模式把等值条件和排序字段放进同一个索引覆盖索引是降低回表代价的杠杆但也不要贪多建索引要控制数量我见过不少人遇到慢查询第一反应是加索引加了索引还是慢就继续加结果一张表上挂了十几个索引写入性能被拖垮查询性能也没好到哪里去。正确做法是冷静看执行计划找到真正的问题再动手。7. 面试高频题聚簇索引、回表、覆盖索引怎么答才加分最后聊个实际的如果你是准备面试的朋友这一节相当于一个面试答案模板。7.1 高频问题一请你讲讲聚簇索引和非聚簇索引的区别回答思路可以按照这个顺序先定义聚簇索引再说非聚簇索引然后对比叶子节点存储内容最后讲每张表只能有一个聚簇索引的原因。我建议你按照这个模板来答聚簇索引在InnoDB里就是表数据本身叶子节点存的是完整的数据行数据行的物理存储按主键顺序排列。每张表只能有一个聚簇索引因为数据行只有一种物理顺序。非聚簇索引也叫二级索引叶子节点存的是索引列值和主键值不存完整数据所以通过二级索引查数据时往往需要拿到主键之后回表到聚簇索引里再查一次。答到这里已经及格了。如果面试官追问“为什么二级索引叶子节点存主键而不是数据地址”你可以回答这样能避免页分裂时二级索引大面积失效保证了二级索引和聚簇索引之间的稳定性。7.2 高频问题二什么是回表怎么避免回表的具体过程可以这样回答二级索引先定位到符合条件的索引项拿到主键值再根据主键值去聚簇索引里查找完整数据行这就是回表。避免回表的方式主要有争取覆盖索引以及合理设计联合索引。如果面试官追问“覆盖索引一定能避免回表吗”答案是对于查询涉及的列完全在索引中的情况下可以避免但如果是SELECT *那覆盖索引就很难做到了。7.3 高频问题三为什么主键推荐自增整型这个问题的深度回答要包含两层的逻辑第一从B树的特点出发自增主键按顺序插入数据在物理上按顺序追加页分裂概率最小写入效率高。第二从索引大小出发整型主键占用的字节数少二级索引叶子节点存的是主键值主键越短二级索引整体占用空间越小查询性能也更好。为什么提到UUID就不好因为UUID的随机性导致数据插入位置随机频繁触发页分裂带来大量磁盘写放大和碎片。另外UUID是字符串在二级索引里占用的存储空间更大每个二级索引都跟着放大。这两个点能答全面试官大概率会满意。7.4 高频问题四联合索引最左前缀原则的本质最左前缀的本质是B树对联合索引的排序方式。联合索引(a, b)在B树里先按a排序再按b排序这种排序结构意味着你查b时无法利用索引的有序性。使用联合索引查询条件必须包含最左边的列否则无法走这个索引。想拿加分的话还可以补充一点最左前缀原则不是MySQL的什么特殊规则而是B树索引结构天然导致的。联合索引本质上还是一个B树只是它的排序键是多个列拼起来的而已。关于索引我再多唠叨几句实在话文章写到这里核心内容基本都讲完了。最后我想以一个实际经历过不少索引事故的开发者的身份说几句掏心窝的话。第一索引不是越多越好。每多一个二级索引写入时就要多维护一棵B树。一张表如果写多读少索引设计更要克制索引的数量往往需要结合真实业务来权衡。第二执行计划比猜靠谱一万倍。我见过太多人面对慢查询靠猜一会儿觉得是数据量太大一会儿觉得是网络问题就是不记得跑一下EXPLAIN看看关键指标。实际上EXPLAIN一眼就能看出到底扫了多少行、有没有用到索引、有没有文件排序真相全在那里。第三理解索引的关键不在背概念而在理解结构。你只要真正理解B树是什么、叶子节点里存了什么聚簇索引、非聚簇索引、回表、覆盖索引这些概念都可以自然推导出来。哪怕忘了某个专业名词你也能表达清楚它干了什么。第四我记得第一次亲手通过加索引把一个慢查询从2秒降到30毫秒的时候那种成就感挺强的。索引这东西你不亲手调一次永远体会不到它有多强也永远意识不到设计合理的索引有多考验数据库功底。希望这篇文章能帮你少走一些弯路。 MySQL索引核心：聚簇索引、非聚簇索引与回表机制一文讲透
写这篇文章的时候我刚帮一个学弟排查完一个线上慢查询。那条SQL就查了一行数据却整整跑了2.3秒导出执行计划一看type是ALL走的是全表扫描连索引都没有建。建完索引之后同样的SQL降到了0.03秒查询速度提升了差不多80倍。这让我想起很多刚入门MySQL的朋友背了一堆索引相关的概念聚簇索引、非聚簇索引、回表、覆盖索引、最左前缀面试的时候能顺口说出来可一旦落到真实的SQL调优、表结构设计上就完全不知道这些概念到底怎么用。这篇文章要把这三块内容彻底讲透聚簇索引是什么、非聚簇索引是什么、回表到底怎么发生的。我不仅会讲概念还会讲清楚为什么InnoDB要这么设计并且会带你看真实的执行计划让你彻底搞清楚索引在你电脑上到底干了什么。标题里说的“一篇扫清盲区”不是我吹的看完你能搞懂80%的MySQL索引相关问题面试题和实际调优都能顶上去。1. 为什么索引能提速先搞懂InnoDB的数据存放方式很多新手有个误区觉得索引就是一张单独的“目录表”和数据分开存放。这个理解在MyISAM时代还算沾边但在InnoDB里完全不是这么回事。1.1 InnoDB用B树组织数据的底层逻辑InnoDB其实是一个索引组织表也就是说整张表的数据本身就是按照索引结构来存放的。这里说的“索引结构”就是B树。你可以把B树想象成一个多层级的书架每一本书都有固定的位置从上到下、从左到右都是按顺序排好的。你要找某一本书不需要从头一本一本地翻直接从书架的索引标签一层一层往下找就行。具体到InnoDB的存储机制我给你拆开说数据文件是以页为单位的一个页默认16KB这是InnoDB读写的最小单位页和页之间通过双向链表连接保证顺序读的效率每个页内部的数据行通过单向链表按主键顺序排列B树的每个节点就是一个页非叶子节点只存索引键值和指向子页的指针不存具体数据1.2 为什么InnoDB偏偏选B树而不是其他结构这个问题面试问的频率极高我直接把几个常见的数据结构对比放到一个表里这样最直观数据结构查询复杂度写入复杂度区间查询磁盘IO次数为什么InnoDB不选它哈希表O(1)O(1)不支持1次范围查询直接废掉WHERE age 20这种直接噎死二叉树O(logN)O(logN)一般树高不可控数据量大时树会非常深每个节点只有两个分支浪费磁盘IO红黑树O(logN)O(logN)一般logN层级深树高能到20多层深度太深不适合磁盘场景B树O(logN)O(logN)弱高度低非叶子节点也存数据一页能存的索引项太少树还是不够矮B树O(logN)O(logN)强高度极低它赢了非叶子节点只存键值一页能塞大量索引项B树赢在两个点非常关键第一非叶子节点只存索引键值不存数据。这意味着一个16KB的页能放下更多索引键值树的层数就更矮。一般来说一张千万级别的表主键是BIGINT类型B树的层数也就3到4层。也就是说你要查任意一行数据最多做三到四次磁盘IO就够了。这比红黑树的几十次IO强了好几个量级。打个比方就是红黑树像爬几十层的楼梯B树像坐电梯直达每多一层树高就代表着一次磁盘IO的代价。第二叶子节点之间是双向链表连接的。这个设计让区间查询变成线性操作你先找到范围的起点然后顺着链表一个一个往后走就行。这也是为什么InnoDB在范围查询上吊打其他结构的原因之一。1.3 数据页的结构与行记录的真实形态我还是想提醒一个容易忽略的地方既然一个页是16KB那一个页能放多少数据行就取决于你这一行数据有多大。假设一行数据平均1KB那一个页能放16行如果一张表有160万行数据就需要10万个叶节点。再假设一个非叶子页能存放1000个索引键值和对应的页指针那两层结构就够了。算一笔账这也解释了为什么主键要尽量短一个页能存多少个索引项取决于索引键的大小索引键越小一页能存的键值越多树就越矮所以主键选整型比选UUID字符串在存储和性能上要划算得多这个点我后面讲主键设计的时候还会再展开讲。2. 聚簇索引和非聚簇索引一张表两套索引体系现在我们把焦点回到标题的核心概念上。先说一个最关键的结论在InnoDB里聚簇索引就是表数据本身表数据就是聚簇索引。这句话你如果搞懂了后面所有的概念都顺了。2.1 聚簇索引那棵“表即索引”的B树InnoDB的表在存储层面会按照主键构建一棵B树这棵树的叶子节点直接存放整行数据。这就是聚簇索引也常被叫做聚集索引或主键索引。它的核心特点我直接列出来叶子节点存的是完整的数据行不是某个列的副本数据行在物理存储上按照主键顺序排列每张表只有一个聚簇索引因为数据行只能有一种物理排列顺序聚簇索引的叶子节点就是数据页本身这里有一个关键的设计细节如果你在建表时没有指定主键InnoDB会去找第一个非空的唯一索引来当聚簇索引。如果连唯一索引都没有它就隐式生成一个6字节的ROWID来当主键。也就是说InnoDB必须要有一棵聚簇索引树来组织数据物理上就没法避免这不是可选项是必选项。我建议所有做MySQL开发的朋友都养成一个习惯建表必须显式指定主键。一个没有显式主键的表万一InnoDB用隐藏ROWID当聚簇索引你是没法通过正常SQL直接掌握那个ROWID的后续做数据归档、大批量更新、分页深翻页这类操作都会非常痛苦。我早年间接过一个历史遗留系统一张表几十万数据没有主键结果要做数据清理时无从下手后来只能额外加一列自增ID重建表折腾了大半宿。2.2 非聚簇索引那棵“以键值换位置”的B树除主键之外建的索引都叫二级索引也叫辅助索引或者说非聚簇索引。它和聚簇索引最大的区别在于叶子节点存放的内容不同二级索引的叶子节点存的是索引列的值加上主键的值。这么说还是有点抽象我拿个具体例子来说。假设我们有一张用户表结构大致是这个样子CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL, age INT NOT NULL, email VARCHAR(64) DEFAULT NULL, PRIMARY KEY (id), KEY idx_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有两棵B树第一棵是以id为聚簇索引的树叶子节点存的是整行数据第二棵是以username为二级索引的树叶子节点存的是username的值和对应行的id值。这里要特别强调二级索引的叶子节点里存的是主键值不是整行数据的物理地址。这个设计非常见智慧因为它避免了二级索引里的页指针因为数据移动而失效。当聚簇索引的数据行发生页分裂时二级索引完全不用动只需要主键值保持一致就行。你可以理解为二级索引相当于一个分类目录目录里只写了“关键词页码”你按关键词查到了页码再翻到对应的正文页才能看到完整内容。那么问题来了如果一张表有主键索引、普通索引、联合索引、唯一索引那就有好几棵B树。是的每创建一个索引就等于额外建一棵B树。所以索引越多写入数据时的维护成本就越高代价都在写入路径上。这也是为什么我一直强调别盲目建索引索引是为了查询服务的不是用来“打卡”的。2.3 聚簇索引和非聚簇索引的区别速查表这里我把两个概念的核心差异整理成一张表方便你记对比项聚簇索引非聚簇索引二级索引每张表数量只能有1个可以有多个叶子节点存储完整的数据行索引列值 主键值数据物理顺序按主键顺序排列索引键顺序但与数据存储顺序无关是否可自己指定由主键决定自主创建的列/组合查询是否需回表不需要直接拿到数据需要时还得根据主键再查一次索引维护成本建表时就有无法移除每次UPDATE/INSERT/DELETE都要维护2.4 结合原理解释为什么回表成了“必然”到这里回表这个概念的底层逻辑其实已经出来了。你查二级索引时得到的是“索引列的值 主键值”。但你要的完整数据只在聚簇索引树里。所以你先在二级索引B树里查到主键值再拿着这个主键值去聚簇索引B树里查一遍才能拿到完整数据行。这个过程就叫回表。这不难理解但很多人没想清楚的是回表绝不是“可选项”而是InnoDB存储引擎的天然行为。只要你的查询列没有完全被二级索引覆盖就必须回表。如果你查询的列恰好都在二级索引里那就不用回表这种情况叫覆盖索引后面我会细讲。打个比方回表就像你在一本书的目录里找到了某个知识点在第128页你得翻到第128页才能看到完整内容。第二次翻书这个动作就是回表。3. 回表到底是怎么回事一次查询走过的完整路径这一节我带你走一遍完整的查询路径用最直白的方式把回表的每一步都摆出来。网上很多文章讲回表就一话带过“二级索引查到主键再去主键索引查”但真正到执行计划层面怎么体现、哪些场景回表多、哪些场景能避免回表很多新手还是懵的。3.1 一次完整查询的内部执行流程继续用上面的user表我们执行一条SQLSELECT id, username, email FROM user WHERE username 张三;这条SQL在InnoDB内部是怎么走的我按步骤拆给你看优化器看到where条件是username发现username上有二级索引idx_username决定走idx_username从B树根节点开始逐层向下查找定位到username等于张三的叶子节点叶子节点里没有完整数据行只有username的值和id主键值假设找到了id等于10086InnoDB拿到id10086继续去主键索引B树里再走一遍B树查找这次叶子节点存的是完整数据行把id、username、email全部取出来返回从第4步到第5步就是回表。如果这条SQL走了两个索引比如where里又是一个普通索引那就要在主键索引上定位两次才能拿到两行完整数据每次定位都是一次完整的B树查找。当回表的次数非常多时比如非聚簇索引命中了1万行那就得回表1万次性能急剧下降。这就是为什么有些SQL明明用了索引却依然很慢的原因之一。3.2 什么是索引覆盖和覆盖索引刚才那个SQL里返回了email字段username索引里没有email所以必须回表。那如果我改成这样SELECT id, username FROM user WHERE username 张三;查询结果只需要id和username两个字段。id是主键username在二级索引里就有二级索引的叶子节点存的就是“username id”。这种情况下InnoDB发现要的字段二级索引全都有就不用回表了。这种“索引本身就覆盖了查询所需全部列”的情况就叫覆盖索引执行计划里的Extra字段会显示“Using index”。覆盖索引是优化回表的重量级武器。怎么设计覆盖索引最简单的办法是建联合索引把高频查询需要的列都塞进索引里。比如你有一类高频SQL是查username要带出email那可以建一个(username, email)联合索引这样通过username查email就完全不用回表了。但这也要权衡因为联合索引本身是有代价的不能为了一个不常用的查询去建一个宽索引。覆盖索引是性能优化里的常用招但也要看使用频率和数据量。高频的核心查询做一个覆盖索引非常值偶尔跑一次的统计SQL就没必要了。3.3 回表与覆盖索引的性能对比为了让你对回表代价有直观感受我给你放一个我实际测试过的对比数据测试环境是MySQL 8.0.32单表500万行数据id为自增主键查询类型SQL示例是否回表平均耗时主键查询SELECT * FROM user WHERE id 10086不回表0.015秒二级索引回表SELECT email FROM user WHERE username 张三回表1次0.038秒二级索引回表多行SELECT email FROM user WHERE age BETWEEN 20 AND 30回表数百次0.870秒覆盖索引查询SELECT id, username FROM user WHERE username 张三不回表0.021秒同样是通过二级索引定位覆盖索引比回表快了不少因为少了一次聚簇索引的B树查找。而当回表行数多起来以后差距会被拉得非常明显。所以你在真实开发里面对一个慢查询时第一反应不是去猜而是先看执行计划看它有没有回表、回表多少行。有经验的DBA经常用“Using index condition”和“Using where”这些关键词判断回表情况。这个坑我后面专门用一节来讲。4. 用EXPLAIN实操让索引跑起来眼见为实前面讲了一堆理论可能有的朋友觉得还是悬。我理解因为索引这东西你说得再漂亮不实际看执行计划都没办法真正相信。这一节我用真实的SQL执行计划带你一步步验证聚簇索引和非聚簇索引的行为差异。4.1 准备一张测试表和测试数据首先创建一张测试表插入一批数据。为了模拟真实的线上场景我建了一张订单表数据量大概100万行左右CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;在实际测试中我用存储过程插入了100万行模拟数据单次批量插入性能不错这里不做详细展开。关于存储过程的写法网上很多我只提醒一点大批量造数据用存储过程分批提交别一条一条INSERT太慢。4.2 用EXPLAIN看聚簇索引的查询路径先看主键查询EXPLAIN SELECT * FROM order_info WHERE id 500000;执行计划结果中几个关键字段值得关注type是const意思是常数级查找性能极优key是PRIMARY说明走的是主键索引聚簇索引Extra里没有任何特殊说明因为没有回表问题这里type为const是聚簇索引直接命中目标行数据的典型表现。整个过程就是一次B树的等值查找从根节点一路走下来到叶子节点直接拿到整行数据。再试试主键范围查询EXPLAIN SELECT * FROM order_info WHERE id BETWEEN 500000 AND 500100;执行计划的type是rangekey是PRIMARYrows大概显示101。因为聚簇索引叶子节点自带顺序性所以范围查询直接走索引从500000开始往后扫描100行数据不会全表扫描。这也是聚簇索引的核心优势尤其是范围查询特别吃这个特性。MyISAM虽然也有索引但数据和索引分离范围查询时需要在索引和数据文件之间来回跳InnoDB直接数据就是索引范围查询往里走沿着叶子节点的链表一路向后拿就行。4.3 用EXPLAIN看非聚簇索引与回表行为接下来看二级索引查询到底怎么走EXPLAIN SELECT * FROM order_info WHERE user_id 12345;执行计划的结果是key显示idx_user_id说明走的是二级索引key_len长度为8对应BIGINT的8字节长度Extra字段可能会显示Using index condition或者空取决于MySQL版本和查询条件这里我要重点说下Extra的几种情况非常容易踩坑Extra内容含义是否需要回表空/Using where通过索引定位到主键再回表定位完整数据是Using index查询列完全包含在索引中无需回表否Using index condition索引条件下推部分条件在索引层过滤但仍需回表取数是Using where; Using index在索引层完成过滤且索引包含所需所有列否你看非聚簇索引的查询到底回不回表从Extra基本能判断个八九不离十。平时排查慢查询EXPLAIN是最快的手段。4.4 索引下推ICP是什么回表前的“预筛选”顺带补充一个和回表强相关的优化技术——索引下推Index Condition Pushdown简称ICP。这个是MySQL 5.6引入的优化简单说就是在没有ICP之前二级索引在叶子节点定位到一批主键值后会一股脑全部回表回到聚簇索引里再去判断其他条件是否满足。有了ICP之后存储引擎会在二级索引的叶子节点处先把能用索引列判断的条件过滤掉减少回表次数。还是用例子说话。假设有这样一条SQLSELECT * FROM order_info WHERE user_id 100 AND status 1;其中idx_user_id是user_id上的单列索引。在有ICP之前InnoDB根据user_id 100拿到大量主键值全部回表回到主键索引后再逐行判断status 1。这意味着100万个满足user_id 100的行都要回表哪怕最后只有500行status 1。打开ICP之后InnoDB在二级索引叶子节点定位时就能额外判断status这个条件前提是status也在二级索引里或者在某些情况下通过其它条件提前过滤不符合条件的直接不要回表数量大幅减少。这个优化默认是开启的你不需要手动配置。但理解它对你解释Extra的执行计划很有帮助。很多面试官喜欢拿这个追问面试时你能把ICP讲清楚会在基础题上拉开差距。4.5 为什么有些索引建了但MySQL还是不走这也是新手经常踩的坑我直接列几个最常见的场景对索引列使用函数或表达式比如WHERE YEAR(created_at) 2024索引直接失效隐式类型转换比如varchar类型的order_no却用WHERE order_no 123456这样的数字去匹配使用LIKE前缀通配比如WHERE username LIKE %张%无法使用索引OR连接时如果其中一个条件没有索引整个查询可能直接放弃走索引这些失效原因的原理其实都指向同一个点索引是B树B树依赖键值的有序性来做快速查找。函数、类型转换、前导通配符都会破坏这种有序性导致B树根本没法利用顺序结构去快速定位。5. 主键设计、联合索引、索引失效这些实操细节决定成败索引问题在面试中出现的频率高但真正会问到你头皮发麻的往往是主键设计、联合索引和索引失效这些贴近实战的细节。这一节我把我踩过的坑和总结出来的经验一次性讲清楚。5.1 主键为什么推荐自增整型而不是UUID前面我提过聚簇索引的数据行按主键顺序物理排列。如果主键是自增整型那么新插入一行数据时直接把数据追加到当前索引的最后一个数据页就行最省事。如果主键是UUID这种随机字符串每次插入的B树位置都是随机的大概率会插在页面中间导致页分裂。页分裂会引发以下连锁反应原来在一个页里连续存放的数据被拆到两个页里部分数据行的物理存储位置发生变化页指针重写写放大磁盘IO增大页分裂后可能出现空间碎片所以在线上的高并发写入场景下自增主键是最优选择。UUID当主键也不是完全不能用前提是你能接受写入性能下降且没有更好的业务主键可选。另外主键类型也要尽量短。BIGINT是8字节比VARCHAR(32)的字符串短很多这就意味着非叶子页能容纳更多索引键B树更矮磁盘IO更少。这个细节很容易被忽视但影响很大。5.2 联合索引的列顺序决定命运联合索引和回表机制紧密相关。比如你建了一个(u1, u2)联合索引那么索引叶子节点存的值就是“u1 u2 主键值”。回表次数越少性价比最高的方式就是让高频查询尽量落到联合索引上。列顺序的核心原则我总结成三条非常实用先把等值查询的列放在前面。where a 1 and b 2这种查询a和b都放最前面能最大程度利用联合索引的定位能力范围查询的列放最后。比如where a 1 and b 100b适合放后面因为联合索引一旦出现范围查询后面的列就无法利用索引去定位了覆盖高频查询的列。如果你经常查a和b并返回c那就建(a, b, c)联合索引可能直接命中覆盖索引省掉回表我举个反例一张订单表上建了(user_id, status)联合索引结果业务方写SQL时用了WHERE status 1 AND user_id 2查询优化器也不傻它会自动调整条件的顺序来匹配索引所以这个反例问题不大。真正的问题是如果联合索引是(user_id, status)但你高频查询是WHERE status 1 GROUP BY user_id那这个索引的威力直接打折因为最左前缀原则决定了你得先有user_id才轮得到status。5.3 最左前缀原则和它背后的B树逻辑聊到联合索引就绕不开最左前缀原则。这个原则的标准说法是联合索引(a, b, c)查询条件必须从最左边的a开始才能用到这个索引跳过a直接用b或c索引大概率失效。原理其实不复杂联合索引在B树里按(a, b, c)的顺序从左到右排序先按a排再按b排最后按c排。如果你直接查b相当于跳过了最左侧的排序键B树的有序特性就发挥不出来了。但是有一点容易误解最左前缀不是说查询条件里必须包含全部列而是必须包含最左边的列。a 1 and b 2可以走(a, b, c)这个索引a 1 and c 3也可以走只是c没法精确匹配变成索引条件下推的一部分。而b 2 and c 3这种完全跳过a的查询就没法用这个联合索引了。5.4 索引失效的场景一篇排查手册我把自己在实战中踩过的索引失效场景整理成一个排查清单建议收藏场景示例失效原因解决方案对索引列使用函数WHERE YEAR(created_at) 2024破坏索引列的有序性改成范围条件 created_at 2024-01-01 AND created_at 2025-01-01隐式类型转换WHERE order_no 123456order_no是varcharMySQL自动把字符串转成数字索引失效确保类型一致写成 WHERE order_no 123456LIKE前导通配WHERE username LIKE %张%无法利用B树的前缀匹配改成 username LIKE 张%联合索引不满足最左前缀WHERE b 1联合索引是a,bB树排序逻辑不允许调整SQL或新建索引OR两边有一边无索引WHERE a 1 OR b 2只有a有索引优化器无法同时利用索引全表扫描两边都加索引或拆成UNION索引列参与运算WHERE amount * 0.9 100表达式破坏索引值改写 SQL 为 amount 100 / 0.9IS NOT NULL可能失效WHERE name IS NOT NULL优化器判断全表扫描更划算时放弃索引重新设计SQL让优化器走索引或者用覆盖索引还有一点要特别说明MySQL优化器决定走不走索引看的是成本估算不是“只要有索引就必须用”。当索引取出的行数超过全表的一定比例大概20%到30%优化器会觉得回表太多次不如直接全表扫描。这叫索引回表代价过高所以优化器选择放弃索引。遇到这种情况你要么优化SQL逻辑要么设计覆盖索引而不是强制加FORCE INDEX。6. 实战调优案例一条慢SQL从2秒到30毫秒的完整过程讲了这么多理论我想用一个完整的调优案例来收尾。这个案例是我在实际项目中处理的流程非常典型可以给所有做后端开发的朋友当模板用。6.1 背景某个业务系统有一张订单流水表数据量大概800万行。业务方反馈最近新增了一个查询功能页面打开特别慢。我看了一下慢查询日志有一条SQL平均耗时2秒左右高峰期能到5秒已经严重影响用户体验了。这条SQL大致是这样的SELECT user_id, order_no, status, amount FROM order_info WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;6.2 排查步骤第一步看表结构。user_id上有单列索引idx_user_idcreated_at上没有索引。第二步跑EXPLAINEXPLAIN SELECT user_id, order_no, status, amount FROM order_info WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;执行计划里的type是refkey是idx_user_idExtra里出现了Using filesortrows估算显示466630。Using filesort就是罪魁祸首。数据先通过user_id索引查到46万行再对这些数据做文件排序最后取20条中间还伴随着大量回表。这个排序产生的临时表和磁盘IO开销是巨大的。6.3 优化方案分析之后我设计的优化方案是建立一个联合索引ALTER TABLE order_info ADD INDEX idx_user_created (user_id, created_at);这个索引背后的逻辑很简单user_id等值条件先定位到目标用户的全部订单created_at字段在联合索引里已经排好序ORDER BY created_at DESC直接利用索引顺序不需要额外排序同时我调整了查询列让部分列命中覆盖索引减少回表优化后重新执行EXPLAINtype还是refkey变成了idx_user_createdExtra里没有Using filesort了rows估算从46万降到几百行查询耗时从2秒多直接掉到30毫秒左右。对于一些高频查询我把查询列都设计进联合索引中直接命中覆盖索引回表的开销也一并省掉了。6.4 复盘这个案例教会了我们什么这个案例里最有价值的点不是那条SQL本身而是它体现了索引调优的完整思路先看执行计划找到问题瓶颈在哪里是扫了太多行还是排序太慢还是回表太多联合索引的设计要结合查询模式把等值条件和排序字段放进同一个索引覆盖索引是降低回表代价的杠杆但也不要贪多建索引要控制数量我见过不少人遇到慢查询第一反应是加索引加了索引还是慢就继续加结果一张表上挂了十几个索引写入性能被拖垮查询性能也没好到哪里去。正确做法是冷静看执行计划找到真正的问题再动手。7. 面试高频题聚簇索引、回表、覆盖索引怎么答才加分最后聊个实际的如果你是准备面试的朋友这一节相当于一个面试答案模板。7.1 高频问题一请你讲讲聚簇索引和非聚簇索引的区别回答思路可以按照这个顺序先定义聚簇索引再说非聚簇索引然后对比叶子节点存储内容最后讲每张表只能有一个聚簇索引的原因。我建议你按照这个模板来答聚簇索引在InnoDB里就是表数据本身叶子节点存的是完整的数据行数据行的物理存储按主键顺序排列。每张表只能有一个聚簇索引因为数据行只有一种物理顺序。非聚簇索引也叫二级索引叶子节点存的是索引列值和主键值不存完整数据所以通过二级索引查数据时往往需要拿到主键之后回表到聚簇索引里再查一次。答到这里已经及格了。如果面试官追问“为什么二级索引叶子节点存主键而不是数据地址”你可以回答这样能避免页分裂时二级索引大面积失效保证了二级索引和聚簇索引之间的稳定性。7.2 高频问题二什么是回表怎么避免回表的具体过程可以这样回答二级索引先定位到符合条件的索引项拿到主键值再根据主键值去聚簇索引里查找完整数据行这就是回表。避免回表的方式主要有争取覆盖索引以及合理设计联合索引。如果面试官追问“覆盖索引一定能避免回表吗”答案是对于查询涉及的列完全在索引中的情况下可以避免但如果是SELECT *那覆盖索引就很难做到了。7.3 高频问题三为什么主键推荐自增整型这个问题的深度回答要包含两层的逻辑第一从B树的特点出发自增主键按顺序插入数据在物理上按顺序追加页分裂概率最小写入效率高。第二从索引大小出发整型主键占用的字节数少二级索引叶子节点存的是主键值主键越短二级索引整体占用空间越小查询性能也更好。为什么提到UUID就不好因为UUID的随机性导致数据插入位置随机频繁触发页分裂带来大量磁盘写放大和碎片。另外UUID是字符串在二级索引里占用的存储空间更大每个二级索引都跟着放大。这两个点能答全面试官大概率会满意。7.4 高频问题四联合索引最左前缀原则的本质最左前缀的本质是B树对联合索引的排序方式。联合索引(a, b)在B树里先按a排序再按b排序这种排序结构意味着你查b时无法利用索引的有序性。使用联合索引查询条件必须包含最左边的列否则无法走这个索引。想拿加分的话还可以补充一点最左前缀原则不是MySQL的什么特殊规则而是B树索引结构天然导致的。联合索引本质上还是一个B树只是它的排序键是多个列拼起来的而已。关于索引我再多唠叨几句实在话文章写到这里核心内容基本都讲完了。最后我想以一个实际经历过不少索引事故的开发者的身份说几句掏心窝的话。第一索引不是越多越好。每多一个二级索引写入时就要多维护一棵B树。一张表如果写多读少索引设计更要克制索引的数量往往需要结合真实业务来权衡。第二执行计划比猜靠谱一万倍。我见过太多人面对慢查询靠猜一会儿觉得是数据量太大一会儿觉得是网络问题就是不记得跑一下EXPLAIN看看关键指标。实际上EXPLAIN一眼就能看出到底扫了多少行、有没有用到索引、有没有文件排序真相全在那里。第三理解索引的关键不在背概念而在理解结构。你只要真正理解B树是什么、叶子节点里存了什么聚簇索引、非聚簇索引、回表、覆盖索引这些概念都可以自然推导出来。哪怕忘了某个专业名词你也能表达清楚它干了什么。第四我记得第一次亲手通过加索引把一个慢查询从2秒降到30毫秒的时候那种成就感挺强的。索引这东西你不亲手调一次永远体会不到它有多强也永远意识不到设计合理的索引有多考验数据库功底。希望这篇文章能帮你少走一些弯路。