上周帮一个团队做数据库review发现一个挺典型的场景核心订单表上挂了19个索引其中至少6个是冗余的而另一张每天被高并发查询的日志表却连一个联合索引都没有全靠MySQL硬扫。建索引这件事很多人的状态是——好像应该建但不知道建在哪反正多建几个不吃亏。MySQL索引确实是数据库优化里杠杆效应最明显的操作之一一个合适的索引能把查询从秒级拉到毫秒级一个乱建的索引也能把写入拖垮。这篇文章我不打算重复教科书上的B树图解而是想结合自己这些年做过的索引优化聊聊索引创建中最核心的两个问题到底什么时候该建索引什么时候不该建。希望能给正在做表结构设计、慢查询优化或者准备MySQL面试的朋友一些可以落地的判断标准而不是空泛的索引很重要。1. 索引不是越多越好先搞懂它到底在加速什么1.1 MySQL索引的本质一种用空间换时间的有序结构先回到最基础的问题索引为什么能加速查询本质上MySQL索引是一份独立的、按照一定规则排序好的数据副本。我们平时说的走索引指的是MySQL在搜索时不再像翻字典那样从第一页开始逐行扫描而是通过这份有序副本用二分查找的方式快速定位目标数据。以InnoDB为例默认的B树索引把数据组织成多层结构非叶子节点只存索引键值和指针叶子节点才存完整的数据或主键值层数通常只有3到4层。也就是说即使表里有上千万行走索引查找也只需要几次磁盘I/O。全表扫描则是要读取所有数据页两者的差距在数据量大时是指数级的。这个原理很多人学过但容易忽略一个重要推论索引是有成本的。它占磁盘空间、占内存缓冲池、每次插入、更新、删除都需要同步维护索引结构。很多人只看查询变快了却没算过写入变慢了多少。你在表上建的每一个索引都相当于在每次写操作时多维护一棵B树这个代价在写入密集型场景下会被急剧放大。1.2 索引不是越多越好写入放大与空间浪费我曾经接手过一个订单表累计有19个索引其中有三个索引只在某一次性能排查中被用到过一次其余时间完全闲置。这张表的写入QPS平均在2000左右因为索引过多每次insert要同时更新近20棵索引树磁盘IO压力非常大binlog也明显膨胀。后来我把冗余和低频索引清掉只保留7个关键索引写入耗时从平均45ms降到22ms几乎减少了一半。这个案例想说明的核心观点是索引的价值和代价是同时存在的要不要建索引本质上是一次性价比评估。具体到代价可以从三方面看存储空间每个二级索引都是一份独立的B树副本表越大、索引越多额外占用的空间越可观DML性能每次写操作都要同步更新索引索引越多写入链路越长优化器负担索引过多会让优化器有更多选择但一旦选错执行计划反而可能让查询变得更慢。所以我在判断该不该建索引之前一定会先算一笔账这个索引能省下多少读开销又要付出多少写开销。账算明白了答案往往自己就出来了。1.3 主键索引和二级索引的分工在建索引之前还得先把索引的分类搞清楚特别是要区分主键索引和二级索引。InnoDB是聚簇索引组织表主键索引的叶子节点直接保存整行数据一张表只能有一个主键索引。而二级索引普通索引、联合索引、唯一索引都属于这一类的叶子节点保存的是主键值查询数据时先通过二级索引找到主键再用主键回表查一遍完整数据这个过程叫回表。理解了这个结构就能推导出不少实用结论。比如主键尽量选择自增ID因为B树是按序组织的随机主键会导致页分裂和页碎片再比如如果查询的字段恰好都包含在二级索引中就不需要回表这种索引叫覆盖索引是性能优化里成本最低、收益最明显的方案。有人可能会问那是不是所有表都一定要建主键我的建议是只要用InnoDB最好显式设计主键不要依赖MySQL自动生成隐藏行ID。一个合理的主键设计能让后续所有二级索引都受益因为二级索引的叶子节点存的正是主键值。2. 该建索引的信号这些场景不建就是在烧钱2.1 高频查询触发全表扫描且表已经到了一定量级什么时候该建索引最直接的信号是某个高频查询的执行计划走的是全表扫描typeALL而这表的行数已经明显到了全表扫描很吃力的规模。这个规模没有绝对值但可以给出一些经验参考单表数据量超过几十万行或者单表数据量虽然不大但每行包含较大的text/varchar字段导致全表扫描需要读取大量数据页时就该认真考虑索引了。举个例子之前有个项目一张配置交接记录表只有40万行但因为每条记录有个冗长的content字段平均2KB一次全表扫描要读接近800MB数据耗时1.6秒。这个查询恰恰是管理后台每次打开列表页都会执行的用户体验非常糟糕。后来我在where条件中最常出现的record_type列上建了个普通索引查询耗时直接降到50ms以内。什么情况下没必要因为全表扫描建索引如果表只有几千行数据页总共也就几十个MySQL扫描完也就几毫秒的事索引带来的收益几乎感知不到反而增加了维护成本。所以我的判断标准从来不是有没有全表扫描而是这个全表扫描的频率和代价是不是已经无法接受了。2.2 WHERE、JOIN、ORDER BY、GROUP BY四类场景的建索引思路日常建索引时我习惯把需求分成四类来看每一类的判断重点不一样WHERE条件等值查询和范围查询是索引的主要受益者。等值查询、IN在B树上的定位是最高效的范围查询、、BETWEEN也能通过树的遍历快速缩小区间但要注意后文要说的边界情况。JOIN关联字段在关联查询中驱动表和被驱动表的关联列上都应该有索引。尤其是被驱动表的关联列不加索引会导致嵌套循环里反复全表扫描这种性能问题在数据量大时几乎是灾难。ORDER BY排序索引本身就是有序的如果排序字段能利用索引顺序MySQL可以避免filesort。filesort意味着额外的排序内存和临时文件数据量大时非常慢。GROUP BY分组分组本质上先排序再聚合索引同样能直接提供有序数据省去排序这一步。对于这四类场景建索引的时候还要遵循一个原则同一个索引尽量覆盖多个需求。比如一条SQL里既有WHERE user_id ?又要ORDER BY create_time DESC那联合索引(user_id, create_time)会是最优解而不是分别建两个独立索引。两个独立索引最终只有一个能真正被利用另一个大概率变成冗余索引。2.3 覆盖索引一条查询减少回表的最优解除了直接命中WHERE条件的索引我还特别建议在高频固定查询上做覆盖索引。怎么理解呢假设业务经常要查用户的手机号和昵称条件是user_id那你只需要一个(user_id, mobile, nickname)联合索引查询时索引树里已经包含了这3个字段MySQL可以直接从索引取数完全不需要回表。这个优化对高并发读场景提升非常明显。但要注意联合索引本身的列是有顺序的不是把所有字段堆进去就行。最左前缀原则是联合索引的核心约束只有遵守这个顺序索引才能被高效利用。具体怎么排我放到最后一节详细讲。2.4 慢查询日志里的高频SQL是建索引的源头另一个很实用的判断方法不要靠感觉来建索引而是让数据说话。MySQL的慢查询日志默认是关闭的建议在低峰期开启slow_query_log结合mysqldumpslow或者直接查performance_schema里的events_statements_summary_by_digest就能看到哪些SQL执行次数最多、平均耗时最长。把这些高频慢SQL捞出来反推where条件和排序字段索引该建在哪一目了然。我见过太多团队是上线了之后靠线上报故障才想起来查一下有没有索引这种被动方式成本非常高。提前开慢日志、周期性review是成本最低的索引管理方式。3. 不该建索引的反模式中小表、写多读少、低区分度3.1 数据量小却拼命建索引的配置表综合征和该建没建同样常见的是不该建瞎建。最典型的就是配置表综合征一张只有几百行、甚至几十行的配置表有人习惯性地给每个字段都加上索引。这种表全表扫描的代价本来就趋近于零索引反而带来存储浪费和维护开销。更麻烦的是配置表往往是被高频读取的如果同时还被频繁更新索引维护的成本会进一步放大。记得有一次一张部门配置表只有120行却建了4个索引。虽然写入量不大影响可以忽略但这种习惯一旦带到亿级大表上后果就是灾难。建索引前先问自己一句这张表的数据量到底多大全表扫描真的慢到不能接受了吗如果答案是否定的那这个索引就不该建。3.2 写多读少的表每次写操作都在为索引买单索引是典型的读时享受、写时受罪。如果你的核心场景是写多读少比如埋点日志表、审计流水表、消息队列落库表那么在考虑建索引之前一定要非常谨慎。以埋点日志表为例它每天被写入几百万行但很少被业务查询偶尔有统计分析需求也是跑离线任务完全可以通过离线数仓或者归档表来解决。给这种超高写入表建索引相当于让每一次写入都背上沉重的枷锁。如果确实需要临时查询我的做法是保留一个不加任何索引的原始表用于高速写入同时定期把数据转存到带索引的归档表或分析表把索引成本花在真正需要读取的地方。这种读写分离的索引策略在日志类业务中非常实用。3.3 低区分度列性别、状态、布尔值的索引陷阱在MySQL B树里索引的价值高度依赖数据的区分度。什么叫区分度就是某一列不同取值的数量占行数的比例。用公式来看就是COUNT(DISTINCT col) / COUNT(*)。如果这个比值接近1说明每一行的值都不同索引能快速定位如果接近0说明大量行共享同一个值索引的选择性就很差。典型的低区分度列包括性别、状态、is_deleted布尔值等。在这些列上单独建索引很可能得到相反的效果。举个例子一张用户表有1000万行其中status列只有3个取值正常、冻结、注销。如果你在status上建索引并查询status正常这个条件的行数可能有800万行MySQL的优化器一算发现用索引读取800万条主键再回表的成本远远高于直接全表扫描于是直接放弃索引。这就是为什么很多低区分度索引在实际执行计划里看不到。那低区分度列就完全没用吗也不是。它适合和其他高区分度字段组成联合索引放在靠前或靠后的位置需要具体分析。比如业务经常要查user_id status联合索引(user_id, status)就很有意义因为user_id已经能定位到极少数行status的作用是过滤这些行索引仍然高效。所以低区分度列的出路不是单独建索引而是作为联合索引的一部分发挥作用。3.4 频繁更新的列索引树维护的隐性代价还有一个容易被忽略的坑频繁更新的列适合不适合建索引我的建议是谨慎。假设一张表里有个login_count字段每次用户登录都1你在这个字段上建索引每次update除了更新数据本身还要维护索引树。如果更新频率高索引树的节点分裂和页写入会成为额外的压力点。但这里需要区分情况如果更新是低频的比如几秒钟才更新一次索引压力可忽略如果是高频更新且这个字段还需要被频繁查询那可能需要重新设计表结构比如把计数放到独立的统计表而不是在同一张表上硬扛。索引的取舍真的要考虑业务的实际读写模型。4. 索引生效的判断与失效陷阱EXPLAIN里藏着真相4.1 用EXPLAIN验证索引是否真正生效索引建完之后最重要的动作是验证它是否真的被查询用上了。我不止一次遇到过明明建了索引为什么执行计划还是不用 其实大部分情况不是索引没用而是真实的查询条件让索引没法生效。验证索引的标准方法就是看执行计划。在SQL前面加EXPLAIN关键字能得到一张执行计划表我们主要关注下面几列type访问类型从好到差大概依次是const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描需要警惕看到index是索引全扫描比ALL好一点但也说明索引利用率不高。key实际使用的索引名如果为NULL说明没有使用任何索引。rows预估的扫描行数行数越大往往说明效率越差。Extra这一列信息量非常大后面章节重点说。实操的时候我习惯把候选SQL的执行计划先打出来确认type不是ALL、key不为空然后再看rows是否符合预期。如果type已经是ref或者range且rows远小于表的总行数基本可以认为索引是生效的。下面是一个最简单的验证示例EXPLAIN SELECT user_id, mobile, nickname FROM user WHERE user_id 123;理论上这条SQL只要user_id上有主键或唯一索引type就会显示const或refExtra里还会出现Using index如果字段恰好被索引覆盖说明这次查询的索引设计是健康的。4.2 索引失效的高频场景函数、隐式转换、LIKE前置通配符、OR在实际运维中索引失效的几个高频场景我一个个盘一遍这些也是日常被问得最多的问题。第一对索引列做函数运算。比如WHERE DATE(create_time) 2025-01-01因为把列包在函数里MySQL无法直接使用create_time上的索引。正确的写法是改成范围查询WHERE create_time 2025-01-01 AND create_time 2025-01-02。这背后的原因是B树存储的是原始值函数运算后的结果无法在树中直接定位。第二隐式类型转换。比如手机号字段是varchar查询时却写成WHERE phone 13800138000MySQL会把phone列转换为数字再比较导致索引失效。解决方法是查询参数保持一致的类型或者查询时显式加上引号。这种坑在接口传参时特别容易出现因为前端参数不一定能保证类型。第三LIKE前置通配符。WHERE name LIKE %张三%因为通配符在开头无法利用索引树的有序性而WHERE name LIKE 张三%是可以用索引的。如果业务确实需要模糊搜索建议考虑全文索引或者配合外部搜索方案而不是硬靠普通索引。第四OR条件。当OR连接的条件中只要有一个字段没有索引整个查询往往会退化成全表扫描。比如WHERE user_id 123 OR name 张三如果name上没有索引这个OR就迫使扫描全表。解决思路是把OR拆分成两个查询再UNION或者确保所有OR分支的字段都建了索引。4.3 联合索引的边界范围查询会切断后续字段联合索引还有一个特别容易踩的坑范围查询会切断索引对后续列的作用。还是以(user_id, create_time)联合索引为例如果SQL是WHERE user_id 123 AND create_time 2025-01-01 AND status 1那么这个查询能用到user_id的等值定位也能用到create_time的范围扫描但status这个字段将无法继续走索引因为B树一旦在某个列上进入范围查找后面的列就无法继续维持有序定位了。这不是说联合索引设计有错而是提醒我们设计联合索引时尽量把等值条件的列放在前面范围条件的列放在后面把最常用的过滤列放在最左。同时如果你知道一个查询中的范围列后面还有需要过滤的字段可以用IN代替范围或把范围条件拆出来处理等技巧来优化。比如把create_time 换成IN多个具体时间点在某些场景下反而能继续利用后续列。4.4 Extra列里的隐藏信号Using index、Using filesort与回表最后强烈建议大家认真读EXPLAIN的Extra列。这里面能看到几个关键信号Using index说明查询用到了覆盖索引索引中包含所有需要的字段不需要回表这是最理想的Using index condition表示启用了索引下推ICPMySQL会在索引层面先做部分条件过滤减少了回表数量属于正常且合理Using where表示存储引擎返回数据后Server层还需要再做一次过滤。如果是联合索引导致的部分列失效这一行就会出现Using filesort说明排序没有利用索引需要额外的排序操作数据量大时会明显变慢Using temporary使用了临时表常见于GROUP BY、DISTINCT这类操作没有利用索引。看到这些信号再去反推SQL写法和索引设计才知道问题到底出在哪。举一个真实的例子我之前排查过一个分页接口慢的问题SQL是ORDER BY create_time LIMIT 10表里有几十万条记录create_time上是有索引的Extra却出现了Using filesort。原因是查询里同时存在WHERE条件和一个联合索引(org_id, create_time)优化器为了保证where条件的定位选择了org_id作为索引前缀结果排序没走成索引。最终的解法是把联合索引调整为(create_time, org_id)或者直接给单列create_time再加一个索引让排序走索引问题才彻底解决。5. 建索引前的自检清单列顺序、冗余索引与隐性成本5.1 联合索引的列顺序区分度优先还是查询频次优先这一节集中回答一个高频问题联合索引里多个列的先后顺序到底怎么定网上流传的说法是区分度高的放前面这是有一定道理的但并不完备。更准确的原则是先满足最左前缀需求再考虑区分度。最左前缀原则决定了索引的生效顺序也就是说查询条件里如果只出现联合索引的第二列是无法使用该索引的。所以如果业务上经常只按B列查询那么正确设计是把B放前面否则就白建了一个用不上的联合索引。在此基础上如果多个列都经常作为等值条件出现在WHERE里可以优先把区分度高的列放在前面这样可以在索引树上更早地缩小区间。举个例子一张用户订单表查询条件经常是pay_status user_id create_time三者的组合其中user_id区分度最高每个用户可能有几十到几千个订单pay_status只有几个取值create_time用于排序。理论上的推荐方案是把等值条件列放在前面user_id, pay_status, create_time这样可以一次索引定位到某个用户某状态下的一批订单排序也自然走索引。5.2 冗余索引判定怎么发现建了等于没建的索引冗余索引是索引管理中最容易被忽略的问题。怎么判定冗余核心看索引的列前缀是否被另一个索引包含。比如你有一个索引(a)、一个索引(a, b)那么(a)就是冗余的因为(a, b)已经能覆盖(a)的所有查询场景。还有一种是反向冗余比如(a, b)和(b, a)并不互相冗余因为它们的物理顺序不同适用的查询不同。要发现冗余索引可以查information_schema.statistics表把同一张表的所有索引列拆出来对比也可以用第三方工具如pt-duplicate-key-checker自动扫描。清理冗余索引前先确认业务里没有用到某些特定场景的查询比如(a)索引可能在某个查询中因为索引覆盖而避免了回表删掉之后这个查询反而变慢。稳妥起见先找出使用率极低、又明显被其他索引覆盖的索引再择低峰期删除。5.3 判断索引最终是否值得保留的三条建议最后我把自己在项目里常用的三条建议写下来大家在决定某个索引该不该建、该不该留的时候可以对照第一拿数据说话。开启慢查询日志、查看performance_schema的索引使用统计观察一段时间内哪些索引真的被走到了。不要凭感觉判断一个索引应该有用要看真实的执行计划。第二控制索引总量。单表索引数没有绝对上限但我的经验值是核心大表控制在10个以内普通的业务表5个左右比较健康。超过这个数写放大和优化器选错执行计划的风险都会显著上升。第三给索引设定试用期。新索引上线后不要急着长期保留先观察几个业务周期的慢查询变化。如果查询性能没有明显改善或者执行计划里压根没有用过它果断下线。5.4 删除历史索引的流程与回滚方案如果决定删索引也需要讲究节奏。千万别在业务高峰期直接执行DROP INDEX尤其是大表加锁和重建索引的过程可能会让线上查询出现抖动。我的标准流程是先在低峰期执行一次ALTER TABLE DROP INDEX同时把对应的DDL语句保存好观察一到两个业务周期如果确认没有任何查询报错或变慢再把它从后续的建表脚本和版本管理记录中移除。如果删掉后发现某个隐藏的重要查询变慢立刻用保存的DDL恢复。另外在MySQL 8.0里执行ALTER TABLE操作默认会使用online DDL但受锁和元数据锁的影响仍然建议在压力较低的时段操作。对于超大表可以考虑用pt-online-schema-change这类工具来减少阻塞但这类工具本身也有复杂度不是必须的场景不要轻易上。我在实际运维中还有一个体会是索引的维护应该纳入日常巡检而不是每次等到慢查询报警才开始排查。把慢查询日志、索引使用统计、高频SQL列表这三个数据源串联起来每两周做一次review基本能保证索引方案随时保持在一个健康状态。这个习惯坚持下来比任何最佳实践都管用。好了以上就是我关于MySQL索引建与不建的全部判断逻辑。建索引没有放之四海而皆准的公式但当你理解索引的底层结构、知道自己查询的真实形态、也清楚每一写在为索引付出什么代价时判断本身就会变成一件顺理成章的事。