上一篇讲了 B+树的结构和查找,顺带提了一句:聚簇索引的叶子存整行,二级索引的叶子只存索引列 + 主键。这篇文章把这句话的后果講清楚——为什么"通过二级索引查到数据"经常意味着走两棵树,以及这多出来的一趟(回表)在真实数据库里到底贵在哪。
同样不是背答案:下面的页面访问次数对比,是在本机 MySQL 9.7.1 上,用 performance_schema 实测出来的真实数字。
一张有二级索引的表,物理上其实是好几棵独立的 B+树,共享同一份数据靠的是"叶子里存了什么"。
整张表只有一份数据,以主键顺序组织成 B+树。叶子节点里躺着这一行的所有列。
每建一个二级索引,就是另起一棵更瘦的 B+树,叶子里只放"被索引的列 + 这一行的主键",不放整行。
如果查询要的列超出了二级索引叶子里存的那几列,就必须拿刚查到的主键,再走一遍聚簇索引这棵树,才能拿到完整行。
storage/innobase/row/row0sel.cc · mysql-server @ mysql-9.7.1, L793 —— "回表"在真实源码里就是这一个函数 /** Retrieves the clustered index record corresponding to a record in a non-clustered index. Does the necessary locking. @return DB_SUCCESS or error code */ static dberr_t row_sel_get_clust_rec(...)
换句话说,"回表"不是一个比喻——InnoDB 源码里确实有一个专门的函数,名字就叫"根据二级索引记录,去取对应的聚簇索引记录"。每次调用它,都是一次额外的、独立的 B+树查找。
还是上一篇的 10 行玩具数据。查询条件 WHERE user_id = 101,但要拿 status——这一列不在二级索引里,所以必须回表。
玩具树的"12 页 vs 3 页"是缩小版。下面回到上一篇那张 49.7 万行的 orders 表,跑两条只差一个字段的查询,实测差距。
CREATE TABLE orders (id BIGINT PK, user_id BIGINT, status TINYINT, amount, created_at, note ...) KEY idx_user_id (user_id) -- 只有 user_id,查其他列必须回表 KEY idx_user_status (user_id, status) -- 多带一列 status,刚好覆盖下面第二条查询 SELECT * FROM orders WHERE user_id = 12345; -- 用 idx_user_id,需要回表 SELECT user_id, status FROM orders WHERE user_id = 12345; -- 用 idx_user_status,覆盖索引,无需回表
EXPLAIN ANALYZE 已经能从执行计划的措辞上看出区别——注意第二条计划里多出来的那个词:
-> Index lookup on orders using idx_user_id (user_id = 12345) (cost=4.2 rows=12) (actual time=0.176..0.285 rows=12 loops=1) -> Covering index lookup on orders using idx_user_status (user_id = 12345) (cost=2.22 rows=12) (actual time=0.042..0.0515 rows=12 loops=1)
"Covering index lookup" 里的 Covering 就是优化器在告诉你"这次不用回表"。同样 12 行结果,实测执行时间从 0.285ms 降到 0.0515ms,快了 5.5 倍——这还是数据已经全部缓存在内存里的情况,如果叶子页要从磁盘读,差距只会更大。
换成 performance_schema.global_status 里的 Innodb_buffer_pool_read_requests(缓冲池逻辑页访问次数,命中缓存也会计数),在同一个会话里查询前后各测一次:
| 查询 | 是否回表 | 命中行数 | 页访问次数(实测) |
|---|---|---|---|
| SELECT * ... idx_user_id | 是 | 12 | 63 |
| SELECT user_id,status ... idx_user_status | 否(覆盖索引) | 12 | 16 |
| SELECT * ... idx_user_id(换一个 user_id 复测) | 是 | 11 | 60 |
| SELECT user_id,status ... idx_user_status(同上复测) | 否 | 11 | 16 |
两次复测结果一致:回表版本比覆盖索引版本多访问 ~45~47 个页面,和"每命中一行、多做一次独立的聚簇索引树查找"这个模型对得上——上一篇测出 PRIMARY 索引树高是 3 层,11~12 行每行多花约 4 次页访问,量级吻合。
既然回表贵在"多走一趟树",最直接的优化就是让二级索引本身就装下查询需要的所有列——这样查询在第一棵树里就能拿到答案,连第二棵树都不用碰。
本文用的 idx_user_status (user_id, status) 就是一个覆盖索引——但只覆盖"查 user_id 和 status"这一种查询形态。多加一列能覆盖更多查询,但索引本身会变宽、写入(INSERT/UPDATE 这一列时)要维护的索引也多了一个,不是免费的。
如果实在做不到完全覆盖,MySQL 5.6+ 还有一个折中:索引条件下推(ICP, Index Condition Pushdown)——把 WHERE 里能在二级索引列上直接判断的条件,提前在二级索引这一层过滤掉,只有真正通过筛选的行才会触发回表,而不是"先回表拿到所有列,再筛选"。这能减少回表次数,但没有消除回表本身,这里不展开,留作后续话题。
上一篇的结论是:一次树内查找最多访问 h(树高)个页面。这一篇把它扩展成:
一次"需要回表"的二级索引查询,最坏页访问次数 ≈ h(二级索引) + 命中行数 × h(聚簇索引)
命中行数越多,回表的代价越是线性叠加——这也是为什么"索引扫描返回的行数"和"是否需要回表"两件事经常被放在一起讨论:命中 1 行时,多一次树查找可能感觉不出来;命中几万行时,几万次独立的聚簇索引树查找,就是实打实的几万次额外页访问。