覆盖索引，为什么我劝你每次SQL都看看Extra列-大讲堂

那天半夜两点，我被报警短信吵醒——线上服务响应时间瞬间飙升到5秒，CPU使用率100%。我一个鲤鱼打挺（并没有），打开监控一看，慢查询日志里全是同一条SQL。一个简单的select，查users表，where条件一个status字段，只取user_id和avatar。竟然每次平均耗时200ms。我眯着眼看了看explain计划，Extra列：Using where; Using filesort。没有Using index。我骂了一声，啪地敲上alter table，加了(status, user_id, avatar)的联合索引，再次执行explain，Extra变成：Using where; Using index。再压测，QPS直接翻了40倍，耗时降到5ms。那一刻，我想冲下楼跑两圈。这就是覆盖索引的威力。你看，很多人写了多年SQL，还是只会往where条件字段上加单列索引，然后抱怨MySQL不行了。其实啊，问题往往出在没能利用好索引的覆盖特性。索引不光是快速定位，它还能成为数据本身。

回表：性能的隐形杀手

先别急着喷回表，我们先来捋捋InnoDB的索引结构。InnoDB的数据是按主键顺序存储在一棵B+树上的，这颗树叫聚簇索引。而二级索引（你手动创建的普通索引）也是一棵B+树，但叶子节点存储的是索引键值和对应的主键值。当你通过二级索引查询时，如果索引叶子节点里没有你要的字段，那就必须拿着主键值再到聚簇索引里找完整行。这个过程——回表——意味着至少多一次随机IO。如果数据页没缓存在buffer pool，那就是实打实的磁盘随机读。你可以把聚簇索引想象成一个巨大的仓库，货物（数据）按照货号（主键）一排排码好。二级索引呢，就像仓库门口的一本目录，只记载了货号部分信息和对应的完整货号。你根据目录找了半天，发现只有货号，想拿货物详情，还得跑进仓库深处一趟。如果每次都要去仓库，那效率可想而知。而覆盖索引是什么意思？就是你构建的索引里，存放了查询需要的全部字段，MySQL直接在索引这棵树上就取回数据，不用再进仓库。这时候Extra里会显示Using index，说明这个查询被索引覆盖了。听起来美好，但做起来常常要踩坑。

覆盖索引的物理魔法

那么，从物理层面看，覆盖索引为什么能带来质的飞跃？关键在于减少了逻辑读和物理读。以我去年做过的一个优化案例为例：一个订单表orders，3000万行，需要频繁查询某段时间内已支付订单的order_no和amount。原始查询走create_time索引，但select还要amount，索引里没有，必须回表。我用sysbench模拟了1000并发，平均响应时间126ms，99分位高达800ms，磁盘IO util直接飙到95%。通过iostat看到，全是随机IO。后来我创建了一个联合索引 idx(create_time, order_no, amount)，三个字段覆盖了查询。再次压测，同样的并发，平均响应时间下降到4ms，99分位18ms，磁盘IO util降到12%。这个差距，不是一倍两倍，是几十倍啊。原理就是：查询定位到索引叶子节点，直接顺序读取键值对，每个叶子节点里的键值包含了order_no和amount。一次索引扫描，搞定。不用回表，避免了离散的随机IO。从B+树结构来说，相当于把数据冗余了一份在二级索引，以空间换时间——这正是工程美学里经典的trade-off。

但是，空间的代价不是所有人都仔细掂量过。索引大了，写入性能会受影响（每插入或更新一条记录，可能要维护多个索引页），Buffer Pool的命中率也可能下降。所以，覆盖索引虽好，可不能贪多。

我踩过的三个大坑

血泪经验告诉你，覆盖索引落地时最容易栽在这三个地方。 坑一：索引字段顺序不当，导致覆盖失效还背上文件排序 有一次，一个slow query找上我：select status, count(*) from articles where author_id = 100 and status = ‘publish’ group by status; 开发同学建了索引(author_id, status)，说查询计划显示用了索引，但还是慢。我看explain，Extra里有Using where; Using temporary; Using filesort。原来，虽然where条件两个字段都在索引里，但group by status导致需要临时表排序。因为索引是按(author_id, status)排序的，同一个author_id下status是排好序的，但group by status跨author_id时，status就不是有序的了，所以需要额外排序。如果我把索引调整为(status, author_id)，那么where author_id=100 and status=’publish’ 时，虽然最左匹配是status固定，author_id只是范围条件？其实这里status是等值，所以索引可以用于定位。关键是group by status时，数据在索引中已经按status有序了，就不会再有filesort。同时，count(*)也直接从索引获取。调换顺序后，Extra成了Using index。你看到没，仅仅调换字段顺序，不仅覆盖索引生效，还省了排序！所以，建覆盖索引时，一定要考虑查询的where、order by、group by的顺序，尽量让索引符合最左前缀且满足排序需求。 坑二：盲目追求覆盖所有列，索引肥得像头猪 我见过一个研发团队，为了“优化”一个商品详情页的查询，建了一个包含十几个字段的巨型联合索引。select a,b,c,d,e,f from product where category_id=? and status=?; 他们把select后面的字段全扔进了索引。索引大小膨胀到原来的20倍，写入性能暴跌，buffer pool里塞满了这个大索引，淘汰了其他有用的数据页，导致整体命中率下降。后来我们改成了只覆盖核心高频访问字段，其他字段还是回表，写入性能恢复正常，查询速度反而因为内存效率高而提升。这就叫过犹不及。覆盖索引要选择性地覆盖那些最需要避免回表的列，尤其是区分度低但频繁出现的字段，比如状态、类型。对于那些很少访问的长字段（如description、content），就放回表吧。 坑三：优化器也有犯傻的时候，该force就force MySQL优化器基于成本模型选择索引，有时候它会选错。明明你建了完美的覆盖索引，它偏偏要走全表扫描或者另一个索引。因为统计信息（analyze table）不准确，或者认为回表代价不高。我踩过这个坑：一个表400万行，查询可用覆盖索引，rows估计2000，但优化器选了另一个索引，rows估计1500，可是那个索引需要回表，实际执行时大量随机IO，慢得一批。最终通过在SQL里加force index解决了。但force index不是长久之计，因为数据分布可能变化。更好的做法是调整索引的成本估算参数（如index diving、eq_range_index_dive_limit），或者定期analyze table。但紧急情况下，force一下能快速止损。所以，执行计划是用来看的，不是用来信的，需要结合真实IO情况去验证。

覆盖索引就是这样一种看似简单，实则充满魔力的优化手段。它教会我们一个道理：并不是索引越多越好，而是让索引更聪明地工作。下次排查慢查询时，别忘了盯着explain的Extra列，看看有没有Using index。如果没有，再看看select后面列，结合where和排序，是否能设计一个完美的覆盖索引。很多时候，惊喜就藏在那几个字节的调整中。

作者|大讲堂

排版|大讲堂

审核|满满

覆盖索引，为什么我劝你每次SQL都看看Extra列

回表：性能的隐形杀手

覆盖索引的物理魔法

我踩过的三个大坑

文章目录

最新文章

热门标签