关键词覆盖索引回表复合索引EXPLAIN深分页优化MySQL优化Using index大家好我是小耶写功课只是为了我踩过的坑你们别再踩了聊到索引优化很多人知道“建索引”但不知道“怎么建才能让查询最快”。今天讲一个进阶却非常实用的概念覆盖索引。用好它可以让你的查询直接从索引拿到数据连表都不需要回性能直接起飞。上周讲了窗口函数和CTE的性能对比有读者留言说那些优化确实好但自己遇到的慢查询大多还是索引没用对。没错索引是SQL性能的基石。覆盖索引是索引优化里“性价比”最高的技巧之一——不增加额外存储成本不改SQL逻辑只调整索引字段顺序就能减少一半的I/O。下面我们从原理开始一步步说清楚。覆盖索引是什么先回顾一下InnoDB索引的结构InnoDB使用B树索引对于二级索引非主键索引叶子节点存储的是索引列的值 主键值。当执行一个查询时如果索引里已经包含了查询需要的所有列InnoDB就可以直接从索引树的叶子节点拿到数据不需要再根据主键回主键索引查整行。这就叫覆盖索引。举个具体例子表orders有列order_id(主键)、user_id、order_amount、order_date。执行查询SELECT user_id, order_amount FROM orders WHERE user_id 12345。如果只在user_id上建索引InnoDB先在user_id索引上找到所有匹配的记录得到主键order_id列表然后根据每个order_id去主键索引回表取出order_amount。这需要「索引扫描行数 回表次数」次I/O。如果建复合索引(user_id, order_amount)InnoDB在复合索引上找到user_id12345的叶子节点叶子节点里已经包含了user_id和order_amount直接返回不需要回表。只需要「索引扫描行数」次I/O。性能提升通常在2~5倍回表成本越高提升越明显。如何判断你的查询是否用上了覆盖索引用EXPLAIN看执行计划Extra列出现**Using index**时说明用上了覆盖索引。注意Using index不是索引类型而是一种执行方式表示“不需要回表”。如果出现Using index condition说明用了索引下推ICP但仍需回表。如果出现Using where说明没有用覆盖索引。掌握这个判断方法之后你就可以主动检查自己的慢查询看看有没有机会通过调整索引来消除回表。深分页场景下的覆盖索引应用日常开发中经常遇到这样的分页查询SELECT id, name, age FROM users ORDER BY id LIMIT 100000, 10。直接这样写MySQL会先扫描前100010行包括回表然后丢弃前100000行只返回最后10行。当偏移量很大时这个操作非常慢。优化的思路是先用覆盖索引快速定位到要取的行的主键范围再回表取完整数据。具体写法SELECT u.id, u.name, u.age FROM users u JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 10) tmp ON u.id tmp.id;内层子查询SELECT id FROM users ORDER BY id LIMIT 100000, 10只用到了主键id主键索引天然就是覆盖索引执行非常快。外层再根据这10个id回表取完整数据只需要10次回表。这种方法称为“延迟关联”性能提升非常明显。使用覆盖索引需要注意什么覆盖索引虽然好用但也不是万能的有几个地方要留心索引不是越宽越好覆盖索引需要把查询中用到的列都放进索引这会增加索引的存储空间。而且索引越“胖”写入INSERT/UPDATE/DELETE时维护索引的成本就越高。只把查询中频繁出现的列放进去不要贪多。**尽量避免SELECT ***SELECT *基本不可能被覆盖因为索引一般不会包含所有列那样索引会巨大。尽量只查询需要的字段这不仅有利于覆盖索引也能减少网络传输。字符串列要小心如果查询中包含了长文本字段如VARCHAR(255)覆盖索引会变得很大可能得不偿失。这种情况下可以权衡是否值得做覆盖。总结覆盖索引是SQL优化工具箱中最趁手的工具之一。它不改变业务逻辑不增加额外的系统复杂度只是让索引设计得更“聪明”。下次写查询时先问问自己这个查询需要的所有列有没有可能全部放进一个索引里如果能性能提升会非常直观。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~参考文献MySQL官方文档《Covering Indexes》《高性能MySQL》第4版第5章索引优化
