从14秒到3毫秒，我用一个索引搞定了六百万数据查询

你有没有过这样的经历：为了优化一条慢SQL，对着表加了七八个索引，结果查询速度没见明显提升，写入性能反而变得越来越差，甚至偶尔还会出现死锁。你翻遍了网上的教程，大家都告诉你“索引能加速查询”，但从来没人说清楚，索引到底该怎么建、建在哪里、建多了会有什么副作用。

我刚做DBA的前两年，也踩过无数这样的坑。曾经为了优化一个报表查询，一口气给订单表加了六个联合索引，结果当天晚上订单提交接口就开始频繁超时，写入TPS直接掉了一半。后来花了整整一夜排查，才发现是过多的索引导致INSERT操作的维护成本指数级上升。从那之后我才明白，索引不是越多越好，它是有成本的，好的索引策略从来不是“为每个查询单独建索引”，而是用最少的索引覆盖最多的查询场景。今天这篇文章，我就把沉淀了多年的索引实战经验全部分享出来，从基础原理到真实案例，帮你彻底搞懂怎么设计出高效、低维护成本的索引体系。

一、先搞懂索引的本质，再动手建索引

很多开发同学建索引完全凭感觉，看到WHERE条件里有什么字段，就直接给什么字段建索引，最后建出来的索引又多又乱，根本发挥不了作用。想要设计出合理的索引，第一步必须先搞懂索引的本质是什么。

MySQL的InnoDB引擎默认使用B+树作为索引的数据结构，这是一种平衡多路查找树，所有的数据都存储在叶子节点上，叶子节点之间用双向链表连接。这种结构最大的优势是，它能把随机IO转化为顺序IO，大大减少磁盘访问的次数。通常来说，一棵高度为3的B+树，就能覆盖千万级别的数据量，查询时只需要3次磁盘IO就能定位到目标数据，这比全表扫描动辄几十万次IO要高效得多。

但很多人不知道的是，InnoDB的索引分为两种：聚簇索引和二级索引。聚簇索引就是主键索引，它的叶子节点直接存储了整行的所有数据。而二级索引的叶子节点只存储了索引字段的值和对应的主键值。这就意味着，当你通过二级索引查询数据时，如果SELECT的字段没有全部包含在索引里，MySQL需要先通过二级索引找到主键，再通过主键去聚簇索引里回表查询完整数据，这个回表操作是有性能开销的。

这也是为什么覆盖索引能大幅提升查询性能的原因——如果索引里已经包含了查询需要的所有字段，MySQL就不需要再回表了，直接从二级索引的叶子节点就能拿到全部数据，性能自然会快很多。我见过很多开发同学建索引时只把WHERE条件里的字段加进去，完全忽略SELECT后面的字段，结果明明建了索引，查询还是要做大量回表操作，性能提升非常有限。

除了回表开销，索引还有写入开销。每次你对表执行INSERT、UPDATE、DELETE操作时，MySQL不仅要修改聚簇索引，还要同步修改所有相关的二级索引。一张表如果有10个二级索引，写入一条数据就要维护11棵B+树，写入性能自然会大幅下降。所以索引不是免费的，每多建一个索引，都是在给写入操作增加负担。我给团队定的规矩是，单张表的二级索引数量绝对不能超过5个，超过这个阈值就要重新梳理索引设计，合并冗余的索引。

二、联合索引的最左前缀原则，90%的人都理解错了

提到联合索引，所有人都会说“最左前缀原则”，但大部分人对这个原则的理解都停留在“从左到右匹配，不能跳过前面的字段”，根本不知道它在实际场景中该怎么灵活运用。我见过太多人建联合索引时，把字段顺序随便排列，结果建出来的索引只能覆盖一两个查询，完全浪费了索引的能力。

最左前缀原则的本质是，联合索引的B+树是按照索引字段的顺序依次排序的。比如你建了一个联合索引(a, b, c)，那么索引树首先会按a字段排序，在a字段值相同的情况下再按b字段排序，a和b都相同的情况下再按c字段排序。这种排序特性，决定了这个索引不仅能支持a、a+b、a+b+c的等值查询，还能支持a字段的范围查询、a字段的排序操作，甚至在特定情况下支持b字段的排序。

很多人不知道，联合索引的字段顺序不是随便排的，有两个黄金排序规则。第一个规则是等值查询字段放最左边，范围查询字段放最右边。比如你的查询条件是WHERE a=? AND b>?，那么把a放在左边，b放在右边，这样a的等值条件能快速过滤出小范围数据，b的范围条件可以在这个小范围内继续筛选。如果你反过来把b放在左边，a放在右边，那么b的范围查询之后，后面的a字段就无法用到索引排序了，索引的利用率会大幅下降。

第二个规则是区分度高的字段优先放在左边。区分度指的是字段不同值的数量占总数据量的比例，比如用户ID的区分度几乎是100%，而订单状态的区分度可能只有百分之几。把区分度高的字段放在左边，能在查询最开始就过滤掉绝大多数数据，大幅减少后续需要扫描的索引行数。我之前优化过一个订单查询，原来的联合索引是(order_status, create_time)，查询时要扫描二十万行数据，把字段顺序调换为(create_time, order_status)之后，扫描行数直接降到了两万，性能提升了十倍。

这里我用一个真实的案例演示正确的联合索引设计。假设我们有一个订单表，常见的查询场景有三个：第一个是根据用户ID和订单状态查询订单列表，第二个是根据用户ID和创建时间范围查询订单，第三个是根据用户ID查询订单并按创建时间排序。很多人遇到这种场景，会给每个场景单独建索引，最后建出三个独立的索引，既冗余又占用空间。但实际上，我们只需要建一个联合索引(user_id, order_status, create_time)，就能同时覆盖这三个场景。

第一个场景WHERE user_id=? AND order_status=?，能用到user_id和order_status两个字段的等值匹配，直接在索引里定位数据。第二个场景WHERE user_id=? AND create_time>?，能用到user_id的等值匹配，然后在user_id相同的有序数据里，直接定位create_time的范围。第三个场景WHERE user_id=? ORDER BY create_time，能用到user_id的等值匹配，然后直接利用索引里create_time的有序性完成排序，完全不需要额外的文件排序。一个索引覆盖三个查询，这就是联合索引的威力。

三、覆盖索引实战，把回表开销彻底消灭掉

前面提到过，覆盖索引是指索引里包含了查询需要的所有字段，MySQL不需要再回表查询聚簇索引，直接从二级索引就能拿到全部数据。很多人觉得覆盖索引是高级技巧，很难实现，但实际上只要掌握方法，大部分常见查询都能改造成覆盖索引，性能提升往往能达到几倍甚至几十倍。

我之前遇到过一个非常典型的案例，订单表有五百万条数据，有一个查询是统计某个用户在某个时间范围内的订单总金额。原来的SQL是这样写的：

sql

SELECT SUM(order_amount) FROM orders

WHERE user_id = 12345 AND create_time >= '2024-01-01' AND create_time < '2025-01-01';

当时表上已经有一个普通的二级索引idx_user_id(user_id)，但这条查询的执行时间要两秒多。我用EXPLAIN看执行计划，发现虽然用到了idx_user_id索引，但Extra列没有显示Using index，说明查询过程中做了大量的回表操作。因为二级索引里只有user_id和主键，MySQL需要先通过user_id找到所有相关的主键，再拿着这些主键一个个去聚簇索引里查询order_amount和create_time，五百万数据下这个过程非常慢。

优化方案非常简单，我把原来的单字段索引删掉，新建了一个联合索引：

sql

ALTER TABLE orders ADD INDEX idx_user_time_amount (user_id, create_time, order_amount);

这个索引把WHERE条件里的user_id、create_time，还有SELECT里需要的order_amount全部包含进去了。再次执行EXPLAIN，Extra列直接显示Using index，说明这是一个纯覆盖索引查询，完全不需要回表。查询时间从两秒多直接降到了十毫秒以内，性能提升了两百多倍。

很多人会有疑问，把这么多字段放进索引里，索引体积会不会变得很大？其实要看字段类型，上面案例里的user_id是INT类型占4字节，create_time是DATETIME类型占5字节，order_amount是DECIMAL类型占8字节，一个索引条目总共才17字节，五百万条数据的索引总大小也才几十MB，完全可以全部加载到内存里，根本不会有存储压力。

当然覆盖索引也不是万能的，如果你要SELECT的字段很多，比如十几个大字段，全部放进索引里会导致索引体积膨胀，写入维护成本大幅上升，这种场景下就不适合用覆盖索引。一般来说，覆盖索引最适合用在统计类、列表类、分页类的查询里，这些场景通常只需要返回少量关键字段，用覆盖索引的性价比最高。

四、索引失效的常见陷阱，这些坑我都替你踩过了

很多时候你明明建了索引，但查询还是走了全表扫描，这不是MySQL故意和你作对，大概率是你不小心踩中了索引失效的陷阱。我整理了日常工作中遇到最多的六个陷阱，每一个都有真实的线上案例，帮你彻底避开这些坑。

第一个陷阱是对索引列使用函数或者运算。很多人喜欢写WHERE YEAR(create_time) = 2025，或者WHERE price * 1.1 > 100，这种写法会直接导致索引失效。因为索引里存储的是字段的原始值，函数运算之后的值是无法和索引里的原始值匹配的。正确的写法是把运算移到常量那边，比如把YEAR(create_time) = 2025改写成create_time >= '2025-01-01' AND create_time < '2026-01-01'，这样就能正常用到索引了。我之前遇到过一个报表查询，开发同学在日期字段上套了DATE函数，导致全表扫描了三百万行数据，改完写法之后查询时间从三十秒降到了两百毫秒。

第二个陷阱是隐式类型转换。这是最容易被忽视的一个坑，比如phone_number字段是VARCHAR类型，你写WHERE phone_number = 13800138000，传入的是数字类型，MySQL会自动把phone_number字段转换成数字再做比较，字段被函数隐式包裹，索引直接失效。正确的写法是给传入的数字加上引号，写成WHERE phone_number = '13800138000'。我排查过一个持续了一周的慢查询问题，最后发现就是这个原因，开发同学觉得数字加不加引号都一样，结果直接导致索引失效。

第三个陷阱是LIKE查询以百分号开头。很多人写模糊搜索时直接写WHERE title LIKE '%关键词%'，百分号放在最前面，B+树索引是从左到右有序的，开头就是通配符的话，MySQL根本没办法利用索引的有序性快速定位数据，只能全表扫描。如果业务允许，尽量把通配符放在后面，写成WHERE title LIKE '关键词%'，这样就能正常用到索引。如果必须要前后都加通配符的全文搜索，就不要用普通索引了，直接用全文索引或者接入Elasticsearch。

第四个陷阱是OR条件两边的字段没有都建索引。很多人写WHERE a=? OR b=?，结果只给a建了索引，没给b建索引，这种情况下MySQL会直接放弃使用索引，走全表扫描。因为OR的逻辑是两边只要有一个条件满足就返回，一边走索引另一边全表扫描的话，优化器评估下来还不如直接全表扫描效率高。解决方案是给a和b都单独建索引，MySQL会用索引合并的方式，把两个索引的结果取并集，或者直接用UNION把两个条件拆成两条查询，分别走各自的索引。

第五个陷阱是复合索引跳过中间字段。比如你建了联合索引(a, b, c)，查询条件直接写WHERE c=?，跳过了前面的a和b，这种情况下索引是用不到的。因为索引是先按a排序再按b排序，跳过a和b的话，c字段是完全无序的，没办法利用索引快速筛选。很多开发同学建了联合索引之后，以为后面的字段也能单独用，结果发现索引根本没生效，就是踩了这个坑。

第六个陷阱是数据量太小，优化器主动放弃索引。很多新手会遇到这种情况，明明建了正确的索引，但EXPLAIN显示还是走全表扫描。这不是索引失效，而是当表只有几百行数据的时候，优化器评估下来，全表扫描比走索引再回表的开销更小，所以主动选择了全表扫描。这种情况完全不用紧张，等后续数据量涨上来，优化器自然会选择使用索引。

五、索引冗余清理，让你的表轻装上阵

很多项目跑了两三年之后，表上的索引会变得非常多，很多都是早期为了临时优化某个查询建的，后来业务迭代了，查询场景早就不存在了，这些索引就变成了冗余索引，白白占用存储空间，拖慢写入性能。我每年都会给线上所有表做一次索引冗余清理，通常清理完之后，写入性能能提升百分之二十以上。

判断冗余索引的标准很简单：如果存在索引(a, b)，那么索引(a)就是完全冗余的，因为(a, b)的前缀部分已经可以覆盖a的查询场景，单独建一个a的索引完全没有必要。我之前在一个订单表里发现了七个索引，其中有三个都是完全冗余的，删掉之后索引总大小减少了一半，订单提交接口的平均响应时间从两百毫秒降到了八十毫秒。

清理冗余索引的时候一定要小心，不能想当然直接删。我一般会先在测试环境验证，确认删掉索引之后所有相关查询的执行计划没有变差，再在线上执行。而且执行的时候要选业务低峰期，避免锁表影响线上服务。对于大表，我会用在线DDL的方式删除索引，避免长时间锁表导致业务阻塞。

六、真实案例复盘，从全表扫描到毫秒级优化

最后我用一个完整的线上案例，把上面所有的索引策略串起来，带你看一个真实的慢查询是怎么一步步优化到毫秒级的。

去年我们有一个用户后台的订单统计页面，用户反馈加载要等十几秒。我拿到对应的SQL，表上有六百万条订单数据，原来的SQL是这样的：

sql

SELECT order_id, order_sn, create_time, order_status, order_amount

FROM orders

WHERE user_id = 67890

AND YEAR(create_time) = 2024

AND order_status IN ('已支付', '已发货')

ORDER BY create_time DESC

LIMIT 20;

当时表上只有一个主键索引，没有任何二级索引，执行计划显示type是ALL，全表扫描六百万行数据，查询时间十四秒。

第一步优化，我先把YEAR函数去掉，改成范围查询，避免索引失效：

sql

SELECT order_id, order_sn, create_time, order_status, order_amount

FROM orders

WHERE user_id = 67890

AND create_time >= '2024-01-01'

AND create_time < '2025-01-01'

AND order_status IN ('已支付', '已发货')

ORDER BY create_time DESC

LIMIT 20;

第二步优化，设计联合索引。按照最左前缀原则，等值条件user_id放最左边，然后是order_status，最后是create_time。同时为了实现覆盖索引，把SELECT需要的order_sn、order_amount也放进索引里。最终创建的索引是：

sql

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, create_time, order_sn, order_amount);

第三步验证执行计划，type变成了ref，key使用了新建的索引，rows只需要扫描几十行，Extra显示Using index，完全覆盖索引查询，不需要回表。最终查询时间从十四秒降到了三毫秒，用户打开页面几乎是秒出结果。

整个优化过程没有修改业务逻辑，没有拆分表，只是通过一个精心设计的联合索引，就把性能提升了四千多倍。这就是好的索引策略的威力。

写在最后，索引设计从来不是什么玄学，它是有章可循的。你不需要掌握多么高深的数据库原理，只要理解B+树的排序特性，用好最左前缀原则，优先用联合索引覆盖多个查询场景，尽量实现覆盖索引消灭回表，避开那些常见的索引失效陷阱，你就能设计出高效、低维护成本的索引体系。下次再遇到慢查询，不要急着随便加索引，先停下来想一想，这个索引是不是最优的，能不能用一个索引覆盖更多场景，这样你写出来的索引，才是真正能解决问题的好索引。

💡注意：本文所介绍的软件及功能均基于公开信息整理，仅供用户参考。在使用任何软件时，请务必遵守相关法律法规及软件使用协议。同时，本文不涉及任何商业推广或引流行为，仅为用户提供一个了解和使用该工具的渠道。

你在生活中时遇到了哪些问题？你是如何解决的？欢迎在评论区分享你的经验和心得！

希望这篇文章能够满足您的需求，如果您有任何修改意见或需要进一步的帮助，请随时告诉我！

感谢各位支持，可以关注我的个人主页，找到你所需要的宝贝。

博文入口：山峰哥-CSDN博客 复制到【浏览器】打开即可,宝贝入口：常用软件 宝贝：精品文件

作者郑重声明，本文内容为本人原创文章，纯净无利益纠葛，如有不妥之处，请及时联系修改或删除。诚邀各位读者秉持理性态度交流，共筑和谐讨论氛围～