【3】明明建了索引，为什么 MySQL 还是慢？一文带你理清 InnoDB 存储引擎

有些慢查询最让人别扭的地方不是它慢而是它看上去本来不该慢。比如一张订单表明明已经建了联合索引EXPLAIN里也确实看到了命中的key条件过滤看起来没跑偏排序字段也放进了索引里。可一到数据量上来查询时间还是不稳定偶尔一抖就让人怀疑索引不是已经用上了吗怎么还会这样主问题先定成这条查询SELECT*FROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;这类查询最容易把人带进一个误区只要命中了索引剩下的事情就应该很轻。真实情况没有这么简单。命中索引只说明入口合理执行器更容易先找到候选记录但如果查询真正想拿的是一整行数据后面仍可能要再多走一步回聚簇索引取整行。这一步往往就是很多“明明有索引还是不够快”的根源。问题也正是从这里开始变得有意思二级索引叶子页里到底存了什么为什么还不够为什么有些查询会回表有些查询却能直接结束如果只是把返回列改一下代价为什么会立刻变样再往下追页是什么、记录到底长什么样、BTree为什么这样组织、随机主键为什么更容易把写入拖得更碎这些问题就会一个接一个冒出来。这篇文章不打算从术语表开始背定义而是顺着这条查询一路往下拆先看InnoDB怎么把数据放进页里再看二级索引叶子页到底保存了什么最后再回到查询路径和写入路径把“回表”“覆盖索引”“页分裂”这些平时常被单独记忆的名词放回同一套结构里讲清楚。先把那个最别扭的现象立住要把这个问题讲透先得把它钉在一张具体表和一条具体查询上。先把表结构压到最小只保留这次讨论真正需要的部分CREATETABLEuser_order(idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENT,user_idBIGINTUNSIGNEDNOTNULL,statusTINYINTNOTNULL,created_atDATETIMENOTNULL,amountDECIMAL(10,2)NOTNULL,sku_countINTNOTNULL,remarkVARCHAR(255)DEFAULTNULL,detail_json JSONDEFAULTNULL,PRIMARYKEY(id),KEYidx_user_status_created(user_id,status,created_at))ENGINEInnoDB;这张表上最容易让人误判的查询就是前面的Q1SELECT*FROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;第一眼看条件并不离谱过滤条件WHERE user_id 10001 AND status 1正好命中联合索引前两列排序条件ORDER BY created_at DESC排序列也在联合索引里返回行数LIMIT 20返回行数也不大如果只看这些字面条件很容易得出一个过于乐观的判断这条查询应该已经很轻了。可一旦数据量上来事情经常不是这样。原因不在“索引没用上”而在“索引用上之后事情还没结束”。把Q1和Q2放在一起看那个别扭感会更明显SELECTuser_id,status,created_atFROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;这两条查询的过滤条件几乎一样用的也是同一个联合索引。真正不同的只是返回列。查询过滤条件返回列直觉上的差别真正的差别Q1相同*看起来只是“多拿几个字段”执行器需要把完整记录再取出来Q2相同user_id, status, created_at看起来只是“少拿几个字段”二级索引叶子页里的内容已经够用问题真正卡人的地方就在这里性能差异往往不在“有没有命中索引”而在“命中之后还要不要继续回聚簇索引取整行”。后面真正要回答的问题可以先压成三句二级索引叶子页里到底放了什么为什么有时够用有时不够用为什么SELECT *会把问题从“命中索引”推到“还得回聚簇索引取整行”这一步在物理上到底多读了什么如果这几个问题不先拆开回表、覆盖索引、页分裂这些词会一直显得零散一旦把路径串起来它们其实都在讲同一件事InnoDB怎么组织数据执行器又怎么沿着这套结构移动。先从“页”这一层看别再把表想成很多行排在一起很多人第一次接触数据库存储结构时脑子里的默认画面都差不多一张表里面排着很多行查询命中某一行就把那一行拿出来。这个画面在逻辑上不算错但拿它解释性能现象会很快失效。InnoDB的真实组织方式更接近下面这条层级tablespace - segment - extent - page - record先别急着被这几个词压住。这里真正需要抓住的不是五个术语的定义而是谁才是后续所有现象都绕不开的主角。答案是page。页可以先粗暴理解成一个固定大小的存储块常见默认大小是16KB。无论是查数据、改数据还是把热点内容放进缓存InnoDB真正频繁搬运和管理的核心都不是“抽象的一行”而是页。这个视角一旦立住很多话会突然变具体。比如刚才那句“Q1命中索引之后还要再多走一步”如果只站在行的角度这句话很抽象。可一旦换成页的角度它其实是在说先读了一批二级索引相关的页发现这些页里的信息还不够返回完整结果于是又去读了聚簇索引相关的页“多走一步”不再是空话而是“又多读了一批页”。图 1InnoDB数据页与记录物理结构存储层级里其余几层可以先抓到够用的粒度层级先怎么理解这篇文章为什么需要它tablespace更大的物理存储容器防止把“表”想成一份平铺直叙的文本文件segment同类页的逻辑分组说明主键索引和二级索引并不是混在一起乱放extent一批连续页的分配单位说明InnoDB并不是一页一页地零碎管理所有空间page最核心的读写与缓存单位解释回表、覆盖索引、页分裂都绕不开它record页里的单条记录解释最终真正返回给查询的是什么如果只保留一句最关键的话那就是表面上看到的是“查询在拿行”底层真正频繁发生的是“执行器在读页”。这也是为什么很多性能现象不能只靠 SQL 字面意思解释。SELECT *看起来只是“列多一点”可一旦它让执行器必须多读很多页代价就完全是另一回事了。页这个视角还会顺手拆掉几个很常见的误解。误解 1命中索引就意味着代价封顶真实情况是命中索引通常只说明第一步找候选项更高效后面还要不要继续回聚簇索引取整行要看二级索引叶子页里的信息是否已经够用。误解 2数据库在“读一行”是一种物理动作更准确的说法是执行器会读到某些页再从页里定位和提取记录。行是逻辑视角页才是更关键的物理视角。误解 3页分裂只是“某次插入慢一点”可一旦明白页是核心单位就会知道页分裂影响的不只是那一次写入还会改变后续很多页的布局和访问局部性。所以后面无论讲查询还是写入都默认回到同一个起点不是先问“这条 SQL 看起来怎么样”而是先问“这件事最后落到了哪些页上”。再把“一条记录到底长什么样”说够知道页是什么之后还差最后一块关键拼图页里那条记录到底是什么形状。如果把记录想成“几列字段拼起来”很多现象会解释不透。InnoDB里一条记录至少可以用下面的最小模型理解行头信息业务列变长列与NULL相关的辅助信息行内/行外数据关系这里最重要的不是去背很多底层字段名而是先把两个直觉建立起来直觉 1记录不是裸业务列记录不只由业务列组成还会带必要元信息用于在页内组织、定位、判断长度和状态。以user_order为例user_id、status、created_at、amount这些当然是业务列但一条记录不只由它们组成。为了让记录能在页里被组织、定位、判断长度和状态周围还会带着必要的元信息。也正因为如此行大小从来不是“几个字段类型简单相加”那么朴素。直觉 2并不是所有列都以同样轻的方式待在记录里普通列通常更“轻”长字段更容易把记录的读取代价放大甚至引入行内/行外的差异。像amount、sku_count这种普通列理解起来相对直接。可一旦碰到remark、detail_json这类变长或较重列情况就开始变化。记录里可能只保留一部分必要信息而把更重的内容放到行外结构去承载。后面如果真的要把这些列读出来代价自然也更容易被放大。图 2长字段走溢出页把user_order表映射到这个最小模型里关系会清楚很多列或信息在这篇文章里的角色为什么它重要id主键聚簇索引按它组织回表最终也靠它定位user_id, status, created_at联合索引列Q1/Q2/Q3的过滤与排序都围绕它们展开amount, sku_count, remark普通返回列用来说明“整行记录”不只包含索引列detail_json较重长列用来说明为什么回表之后的代价还会继续变重行头与辅助信息隐含结构用来拆掉“记录只有业务字段”的误解这样再回头看Q1问题就会更具体SELECT*FROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;这条查询真正想拿到的是整行记录但二级索引叶子页通常只提供“索引列 主键值”这类定位信息所以SELECT *很容易把路径推进到“还得回聚簇索引取整行”。把Q1、Q2、Q3放在一起看差异会更鲜明Q1需要完整记录所以必须回聚簇索引取整行Q2只要user_id, status, created_at这些列刚好已经在二级索引里于是可以直接结束Q3虽然第一步路径和Q1接近但因为返回列里还包含detail_json这样的较重长列回聚簇索引之后的负担会更明显所以SELECT *更容易变重并不是因为星号在语法层面“看起来不优雅”而是因为它把查询目标直接升级成了“必须拿到完整记录”。长列也不是因为“名字长”才更麻烦而是因为它们会改变记录本身的重量甚至改变行内/行外的数据关系。执行器真正要付出的不只是“多看一个字段”而是“回聚簇索引之后要处理更重的数据”。到这里前面的几个问题就开始收束到同一条线上页解释了“为什么代价最后总要落回读了多少页”记录解释了“为什么命中二级索引之后还可能不够”长列解释了“为什么回表之后的代价还可能继续放大”后面再讲查找路径就不需要再从零起步了。那时真正要问的已经不是“索引是什么”而是“执行器已经拿到了什么还差什么所以为什么要继续往下走”。索引命中之后执行器到底走了哪条路页和记录都站稳之后接下来只差把Q1的路径真正走一遍它到底慢在了哪一步。先把一句最容易说模糊的话说准确Q1并不是“索引没用上”而是“索引用上之后执行器还得继续回聚簇索引取整行”。把这条路径按执行顺序拆开事情就清楚很多WHERE user_id 10001 AND status 1先命中idx_user_status_created执行器先走这棵二级索引的BTree一路落到满足条件的叶子页先拿到候选项发现这些候选项还不够直接返回完整结果因为查询写的是SELECT *于是还得回聚簇索引再根据主键值把完整记录拿出来如果只用一句话概括Q1的查找路径其实就是先靠二级索引找到谁再回聚簇索引把整行真正拿回来。这里最值得停一下的地方是“候选项到底是什么”。很多人会把“命中二级索引”直接脑补成“已经拿到数据了”。这一步其实并没有那么彻底。二级索引叶子页里更像是放了一份更轻的定位信息索引列本身加上能把执行器带回聚簇索引的主键值。它解决的是“先找到谁”不是“整行已经在手里”。图 3聚簇索引页与二级索引叶子页的结构差异所以Q1真正的执行逻辑更接近下面这个过程条件命中 idx_user_status_created - 进入二级索引 BTree - 落到叶子页 - 拿到符合条件的索引项 主键值 - 发现结果还不够 - 回到聚簇索引 - 取出完整记录图 4Q1查询的查找路径这样再看“回表”这个词就不会再像黑话了。它不是某个神秘优化器动作本质上只是执行器已经在二级索引里找到了候选记录但因为还缺整行所以必须根据主键再回聚簇索引补齐。为什么这一步会显著影响代价因为它不是简单的“多执行一句逻辑判断”而是很可能意味着多读了一批页。前面已经建立过页的视角现在可以把它落回路径里二级索引阶段读的是二级索引相关的页回表阶段读的是聚簇索引相关的页如果候选记录较多这种“先一批、再一批”的读取就会被放大也就是说Q1的问题从来不是“有没有索引”而是“读完二级索引页之后还要不要继续读聚簇索引页”。这也是为什么EXPLAIN里看到key还不足以下结论key只说明入口代价取决于路径是否能在叶子页结束。把Q1和Q3放到一起看这件事会更直观。SELECTid,user_id,status,created_at,detail_jsonFROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;Q3的前半段路径和Q1没有本质区别一样先命中idx_user_status_created一样先走二级索引树一样先落到叶子页真正的差异出现在回聚簇索引之后。Q3需要把detail_json拿出来所以更容易显得重。它提醒了一件常被忽略的事命中同一个索引并不代表总代价相同。真正决定代价的是命中索引之后还要继续拿什么。到这里几个概念之间的关系也开始变得顺手聚簇索引保存整行记录二级索引更像轻量入口回表表示“入口命中了但整行还没拿全”真正让人理解路径的不是把这几个名词分别背下来而是知道它们在一条查询里先后扮演什么角色。把回表和覆盖索引放在一起看差异就会突然清楚单独讲“回表”读起来容易变成一句抽象结论。单独讲“覆盖索引”也很容易被误听成一种特殊索引类型。把Q1和Q2放在一起这两个词一下就会具体很多。先把两条查询再并排看一次-- Q1SELECT*FROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;-- Q2SELECTuser_id,status,created_atFROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;过滤条件一样排序条件一样索引入口一样。真正不同的还是返回列。维度Q1Q2索引入口idx_user_status_createdidx_user_status_created叶子页里先拿到什么索引列 主键值索引列本身就够结果是否需要回表需要不需要物理差异还要再读聚簇索引页可以在二级索引叶子页结束这张表里最关键的并不是“有没有命中索引”而是“叶子页里的内容到底够不够”。Q1的问题在于二级索引叶子页里的信息不够完整执行器必须继续回聚簇索引取整行。Q2的优势在于查询真正要的只有user_id, status, created_at而这些列刚好都已经在idx_user_status_created里。执行器在叶子页上就已经把结果拿齐了于是路径到这里可以直接结束。这就是覆盖索引最值得抓住的物理含义覆盖索引省掉的不是过滤动作而是“回聚簇索引把整行再取一遍”这一步。所以“覆盖索引”不是某种单独创建出来的索引类型它描述的是一条查询和当前索引之间的关系。只要这条查询需要的列恰好都在当前索引里这条查询就被这个索引覆盖了。这个视角也能顺手拆掉两个常见误会覆盖索引不是某种“更高级的索引”只是这条查询的返回列刚好都在索引里回表不是“又扫了一遍表”而是拿着主键值回聚簇索引做精确定位如果一定要用一句更接地气的话来区分回表先拿到“这条记录是谁”再回聚簇索引把整份内容补齐覆盖索引在入口那一步结果就已经够了这也是为什么很多优化建议最后都会落到“别一上来就SELECT *”。原因不只是代码风格而是当返回列收缩之后查询有机会从“必须回聚簇索引取整行”变成“可以直接被索引覆盖”。当然这里也不能把覆盖索引写成没有代价的万能解。一旦为了更多查询被覆盖而把索引做得很宽索引本身也会变重写入维护成本也会跟着上来。真正合理的判断不是“覆盖索引永远更好”而是这条查询是不是高频它是不是真的值得为少掉一次回聚簇索引取整行而付出更宽索引的代价返回列是否本来就能自然收缩而不是为了覆盖强行把索引设计得很别扭所以回表和覆盖索引放在一起讲最后得到的不是一个口号而是一条更可操作的线先看命中了什么索引再看叶子页里已经有什么最后判断结果是否还缺整行。如果结果还缺那就是回表问题。如果结果已经齐了那才谈得上覆盖索引。写入为什么也会被这些结构反过来影响如果这套结构只影响查询主键设计和页分裂就不会成为后面这些工程问题。如果前面几章只停在查询路径会给人一种错觉这些存储结构好像只影响读不影响写。真实情况正好相反。同一套页、记录和BTree结构也会反过来决定写入到底平不平滑。最常见的一组对照就是顺序主键和随机主键。可以先把差别压成两句话顺序主键插入位置更集中更像持续往树尾部追加随机主键插入位置更分散更像不断往树中间插队假设还是同一张订单表业务逻辑没变索引也没多加只是主键策略从自增BIGINT换成随机UUID。表面上看这不过是“新插入一条记录时主键值的生成方式不同”。可一旦把动作落到BTree上差别就会立刻放大。顺序主键更像什么更像持续往树的尾部写。因为新主键值越来越大执行器大多会把新记录插到当前最右侧叶子页附近。只要那个页还没满插入通常就比较平滑。路径虽然同样要先定位叶子页但定位结果高度集中局部性也更好。随机主键则更像不断往树的中间插队。新主键值没有明显顺序执行器每次插入前都要先判断这条记录按键值排序应该落到哪一个叶子页结果往往不是稳定地落在树尾部而是可能散到很多不同位置。这样一来插入位置更分散命中的目标页也更容易到处跳。这件事真正难受的地方不只是“定位更麻烦”而是它更容易把写入推到页分裂那一步。页分裂的过程如下插入请求到来执行器先定位目标叶子页检查这个页还有没有空间如果没满直接插入如果满了就需要分裂原页里一部分记录搬到新页新记录插入到正确位置父节点更新知道这次结构变化图 5页分裂过程如果只从当前这一次插入来看页分裂像是“多做了一点维护”。但真正麻烦的地方在于它不是一个只影响当下的局部动作。页一旦分裂常见后果是页数变多了页布局更碎了范围扫描更不连续了缓存局部性更差了上层节点维护压力也更重了所以“页分裂不只是插入慢一点”的真正的含义是它会把一次写入代价扩散成后续读写路径的结构成本。这也是为什么顺序主键和随机主键的差别不能只用“谁更快”这么粗的口径描述。更准确的说法应该是顺序主键更容易形成稳定的尾部写入路径随机主键更容易把写入分散到树中很多位置插入越分散页分裂、页搬移和后续碎片问题就越容易被放大这里也不能把结论写成“随机主键一定不能用”。很多业务就是需要全局唯一、去中心化生成或者特定主键策略。结构分析真正能提供的不是替业务拍板而是把代价说清楚如果选随机主键接受的是更分散的写入局部性如果追求更宽的索引设计接受的是更重的索引维护成本如果经常读长列接受的是回聚簇索引之后更重的记录读取查询路径和写入路径看起来是两件事最后却都落回同一套结构查询在决定还要不要继续取整行写入在决定要不要继续维护一棵有序的树。最后收束成工程判断而不是空泛原则到这里原理层已经够用了。最后真正需要留下的不是再重复一遍概念而是几条碰到类似现象时能直接拿来判断的线索。1. 先区分“命中索引”和“结果够不够返回”看到查询走了索引不要立刻得出“问题已经解决”的判断。更该追问的是命中的是聚簇索引还是二级索引二级索引叶子页里的信息够不够直接返回如果不够是不是还要回表2. 优化查询时先看能不能少拿而不是先想再建什么Q1和Q2的差别已经说明了很多时候影响代价的不是条件而是返回列。如果业务上根本不需要整行那比起上来再补一个索引更应该先问SELECT *能不能收缩哪些列是这次查询真正要返回的当前索引是否已经足够覆盖这些列3. 优化写入时先看写入路径是不是过于分散写入抖动、页分裂频繁、扫描越来越碎这些问题往往不是孤立故障而是同一个结构代价在不同地方冒头。如果插入模式天然更像“不断往树中间插队”那就要接受页分裂概率更高布局更容易变碎后续读写局部性更差这类问题不是调一个参数就能彻底消失很多时候只能通过更合适的主键策略和查询裁剪把代价放到更能接受的位置。4. 长列问题别只当成“字段大一点”Q3之所以比Q2更容易显得重不是因为它语法更复杂而是因为回聚簇索引之后要处理的记录更重。所以一旦查询经常把长列一起带出来就应该额外警惕这次读取真的需要这些列吗这些列能不能延后拿是否值得为高频场景设计更有针对性的查询路径5. 不要把优化建议写成绝对命令“不要用随机主键”“一定做覆盖索引”“不要查长列”这些说法都太粗了。结构分析真正提供的是取舍视角而不是统一答案现象更该先追问什么可能的方向不能忽略的代价明明命中索引还是慢是不是还在回表裁剪返回列、争取覆盖索引索引可能变宽写入维护更重查询读长列更重回表后是不是还在拿较重记录延后读取长列、分离高频字段代码路径更复杂接口设计可能变化随机主键写入抖动插入位置是不是过于分散调整主键策略或接受代价做容量规划主键生成方式可能受业务约束页分裂后扫描变碎写路径是不是持续在树中间插入降低频繁分裂概率优化整体结构设计很多问题只能缓解不能彻底消灭把这些判断线索重新落回最初那条 SQL前面的结构关系会更集中地显出来。最后把全文再收回到开头那条查询SELECT*FROMuser_orderWHEREuser_id10001ANDstatus1ORDERBYcreated_atDESCLIMIT20;它最值得记住的地方不是某个EXPLAIN字段也不是“回表”这两个字本身而是它把一整串结构问题都暴露出来了数据怎么放进页记录到底长什么样二级索引叶子页里到底有什么为什么查询有时还得继续回聚簇索引取整行为什么同样的结构又会反过来影响写入把这几件事串起来之后回表、覆盖索引、页分裂、长列代价就不再是分散知识点而是同一套存储结构在不同路径上的表现。小结命中索引只解决“先找到谁”真正的差异往往出现在叶子页之后还要不要继续回聚簇索引取整行回表、覆盖索引、页分裂和长列代价看起来分散落到底层其实都在讲同一套页与记录结构