从一次夜班故障聊起索引设计为什么是门手艺活上周三凌晨两点我被值班电话叫醒。线上订单表的一个统计查询把CPU打到99%慢查询日志里全是同一类SQL——按状态和时间段拉取订单列表单次执行12秒。检查后发现这张表在半年内被陆续加了11个索引其中好几个字段组合完全重叠。我当时第一反应不是优化SQL而是先看执行计划优化器在这么多索引里反复纠结最后选了个最差的。这事不是个例。很多团队对索引的态度是两个极端要么建表时一个索引都不加上线后全表扫描被骂要么逮着查询就加索引不加思考到最后表上挂了十几个索引写入变慢、存储膨胀、优化器犯迷糊。所以我想把这几年做索引设计的经验整理一下核心就一句话索引不是越多越好真正高效的索引结构是设计出来的不是堆出来的。这篇文章不会讲那些烂大街的B树原理图解我想从一个实际从业者的角度聊清楚三件事索引到底在解决什么问题为什么索引多了反而坏事以及面对一张真实业务表怎么一步步设计出一套经得起生产环境考验的索引结构。适合刚接触索引优化的开发也适合被线上索引问题折磨过的DBA。1. 索引设计的本质先搞懂索引在跟谁打交道1.1 索引的本质是“找数据”的目录但它有自己的脾气数据库索引和书的目录非常像。你想在一本500页的书里找“索引设计”这个主题没有目录就得一页页翻有目录直接翻到对应页码。但索引比目录更复杂的地方在于它需要配合底层存储结构工作——在MySQL InnoDB里这个底层结构是B树。B树有几个特性决定了索引的设计逻辑数据都存储在叶子节点并且叶子节点之间通过双向指针连接非叶子节点只存索引键值和指向子节点的指针。这就带来两个直接结果等值查询和范围查询都很快因为走到叶子节点就能拿到数据范围查询靠指针顺序遍历即可每次写入都要维护树结构插入、删除、更新都可能触发节点分裂、合并这些操作是要付出代价的。理解了这一点你就能明白索引设计的第一性原理索引是用“写入时的维护成本”换“查询时的扫描成本”用“存储空间”换“查询时间”。任何索引设计都是在做这个权衡。你建的每个索引都意味着每次INSERT、UPDATE、DELETE要多维护一棵B树磁盘上要多占一份空间这账是躲不掉的。1.2 回表、覆盖索引和最左前缀三个绕不开的关键词要把索引设计好有几个基础概念必须刻在脑子里因为后面你做的每一个决定都跟它们有关。第一个是回表。InnoDB的主键索引聚簇索引的叶子节点直接存整行数据而普通索引二级索引的叶子节点存的是索引列的值加上主键值。所以用普通索引查询时先通过索引找到主键再用主键去聚簇索引里找完整行这就是回表。回表本身不一定是坏事但如果你的查询要命中几千行每行都回表一次那性能就崩了。第二个是覆盖索引。如果查询的列全部包含在某个二级索引的字段里那么直接扫描这个索引就能拿到所有需要的列不需要回表。这被称为覆盖索引优化是索引设计里的重要优化手段。后面我会讲到怎么刻意设计覆盖索引来消除回表。第三个是最左前缀原则。联合索引a, b, c本质上建立的是一棵以a、b、c排序的B树它可以支持a、ab、abc三种前缀查询但查询条件里如果跳过a直接查b或者跳过b直接查c索引就用不上了。这决定了联合索引字段顺序的排列是一个必须认真对待的问题。这三个概念共同指向一个核心认知索引不是“建了就生效”的装饰品它是被查询条件精准驱动的一种物理结构。你的字段顺序选错、字段冗余过多、覆盖不完整都可能让一个看似合理的索引实际发挥不出作用。1.3 区分度被绝大多数人忽略的索引第一指标我见过太多人设计联合索引时把建表语句里的字段挨个往索引里塞完全不看字段值的分布情况。结果建出来的索引比如性别状态区分度极低优化器看了一眼就放弃使用它直接走全表扫描。区分度这个词指的是某个字段不同值的数量占总行数的比例。比如一张100万行的表status字段只有3种取值待支付、已支付、已取消那么status的区分度就是3/1000000极低。本质上B树在查找时是靠逐层比较来缩小范围的区分度越低每层能排除的数据越少最终要扫的叶子节点越多索引的价值也就越低。这里有个实用的判断方法执行SELECT COUNT(DISTINCT column) / COUNT(*) FROM table比例越高代表区分度越好。我们通常认为区分度低于20%的字段不适合单独作为索引第一位但可以作为联合索引里的辅助字段用来过滤剩余范围。后面讲联合索引设计时我会把这一条作为排列字段顺序的核心依据之一。说了这么多基础概念是为了让大家明白索引设计不是拍脑袋它的每一步都应该有据可依。下面聊聊反面案例一个索引泛滥成灾的表到底是怎么把性能搞垮的。2. 索引泛滥的代价为什么不是越多越好2.1 索引开销的账本写入放大、存储膨胀、优化器选择困难很多开发对索引开销没有体感觉得无非是硬盘多点占用但现在SSD也不贵问题不大。这种想法会吃大亏因为索引的开销远不止存储空间这一项。写放大是最直接的问题。一张表每建一个二级索引每次INSERT就要多写一棵B树。如果一张表有10个索引一次行插入实际上要写11棵树如果是UPDATE还要看更新字段是否涉及索引列涉及的话需要同时维护多棵树。我曾经做过一次压测对一张5索引的2000万行表做批量更新比只有主键索引的同结构表慢了一倍还多。这就是索引带来的写放大高频写入的业务场景下这个成本会被放大得极其明显。存储膨胀也同样可观。MySQL的二级索引本身需要存储索引列的值和主键值为了支持长度较大的字段还会增加一些额外字节。10个索引用下来索引空间经常能和表数据空间平起平坐有些表索引甚至反超数据体积。你跑到SHOW TABLE STATUS一看Data_length和Index_length一比就知道问题有多严重。还有一个被忽略得比较深的坑是优化器选择困难。优化器不是神它依赖统计信息和成本估算来决定走哪个索引。索引越多统计信息越复杂优化器要在十几个候选索引里选择一个本身就有开销而且还可能因为统计信息滞后选错索引产生前文说的那种“有索引不走偏选最差的”情况。生产环境里最恐怖的不是没有索引而是大量无用索引干扰了优化器的判断。2.2 最典型的反面案例一张订单表上的11个索引我来说说开头提到的那个事故。那张订单表结构大致是这样订单ID主键、用户ID、订单状态、订单金额、支付时间、创建时间、商家ID。半年里每个业务方各建各的索引最后挂了11个单列或联合索引。我实际把索引列出来后发现至少有3个索引是彻底冗余的比如idx_status订单状态单独存在区分度只有几个值基本没用idx_status_createtime订单状态创建时间跟后面要说的idx_createtime_status顺序不同但部分场景下功能重叠还有两个时间字段各自单独建的索引导致范围查询时优化器在两个时间索引之间来回纠结。结果就是高频的“按商家拉一段时间内订单”的查询被迫在多个索引中选了一个先走商家索引再对结果集做状态过滤和排序扫描行数直接飙到几百万。而正确的姿势应该是为这类高频查询设计一个联合索引精确匹配商家ID再用时间范围过滤一步到位。核心问题是索引太多彼此干扰谁都没能盖出最优路径。2.3 优秀的索引设计是“覆盖高频查询”的系统不是列表合集既然索引不是越多越好那么反过来思考该怎么做我的答案是索引设计不该从字段出发而该从查询出发。把线上日志、慢查询记录、业务代码里的条件全部捞出来统计出高频查询的模式把高频SQL抽出来分析它的WHERE条件、ORDER BY、GROUP BY、JOIN条件然后为这些“有价值的查询”设计索引。索引是给查询服务的查询是索引的唯一目的。同时要建立审查机制任何想新增的索引都必须回答三个问题——这个索引覆盖了哪个具体查询它是否与其他索引功能重叠如果必须加它是通过调整已有索引的字段顺序能解决的不满足就坚决不加。这套机制做下来你会发现绝大多数表有5个以内的索引就足够支撑业务了。3. 设计一套真正高效索引的完整流程3.1 第一步收集与分析查询模式画出高频查询清单索引设计的第一个里程碑不是写CREATE INDEX而是把查询收集齐。具体做法分几步先从线上慢查询日志里拉出TOP 20的SQL再让研发从业务代码里把核心接口的SQL提出来同时把高频报表统计的SQL也汇总进来。把这些查询分门别类整理按执行频率和耗时排序重点标注那些“单次执行不慢但每秒执行几百次”的查询——这类查询才是索引设计的核心服务对象。我在实际项目里通常会建一个查询模式表字段包括SQL模板、执行频率、耗时、涉及表、WHERE条件、ORDER BY字段、是否需要回表。整理完后把WHERE和ORDER BY的字段组合圈出来这就是候选索引的字段池。这个过程非常关键但很多人跳过了直接凭感觉建索引这就跑偏了。举个实际的例子假设我们梳理出一张表有三个高频查询按用户ID查最近订单列表需要按创建时间倒序SELECT * FROM orders WHERE user_id ? ORDER BY created_at DESC LIMIT 20;按商家ID和状态查订单需要按创建时间倒序SELECT * FROM orders WHERE merchant_id ? AND status ? ORDER BY created_at DESC LIMIT 50;按订单状态统计某时间段的订单数SELECT COUNT(*) FROM orders WHERE status ? AND created_at BETWEEN ? AND ?;这三条SQL就是设计索引的依据。如果你能画出这样的清单就已经完成了80%的索引设计工作。剩下的是如何排列组合字段顺序的问题。3.2 第二步利用区分度与等值条件决定联合索引字段顺序联合索引字段顺序为什么重要因为最左前缀原则决定了索引只能在字段顺序上“从头连续匹配”。你要让查询能用上联合索引WHERE条件里涉及的字段必须从联合索引的最左列开始连续命中。所以字段顺序的设计目标只有一个让每个高频查询能用最少的索引覆盖到最多的查询条件。排序的逻辑我一般按以下优先级排列先放等值查询字段区分度高的优先WHERE里的等值条件字段user_id、merchant_id这种是最佳候选因为等值命中会直接定位到一段连续区域区分度越高定位越精准再放范围查询字段ORDER BY字段本质上也是范围排序。比如created_at放在等值字段后面可以让索引天然按时间排好序省掉一次filesort最后才是低区分度字段比如status如果前面等值条件已经定位得很小了把它放后面后续过滤就行但不要把它放在第一位。回到刚才那三条SQL显然对查询1联合索引(user_id, created_at)是最优解user_id等值命中created_at倒序扫描即可对查询2联合索引(merchant_id, status, created_at)是合理的merchant_id和status等值精确命中created_at帮忙排序对查询3联合索引(status, created_at)可以做成实际上的覆盖查询虽然status区分度低但它配合created_at范围后扫描范围大幅缩小且从索引直接计数就不用回表了。不要觉得这里太简单很多人挂在字段顺序上就是因为没把等值、范围、排序的逻辑理清楚。我们一步一步分析最终落到代码上时就知道每个字段都有明确的位置逻辑。3.3 第三步善用覆盖索引让SQL免回表在设计时要刻意观察SQL查询出的列是否有可能把查询列“包进索引”里形成覆盖索引从而省掉回表。比如一个高频查询SELECT user_id, status, created_at FROM orders WHERE user_id ? AND created_at ?;如果只建(user_id, created_at)索引查询返回的status列不在索引里每行都要回表如果命中的行数比较多回表开销就很扎眼。这时把索引调整为(user_id, created_at, status)就能让索引覆盖这组查询列不需要回表。加一个字段没多少成本性能却提升一个等级。我在线上实践中很多接口最终快得惊人的秘诀就是覆盖索引。但要留意覆写不要过度不要为了覆盖一个不重要的字段硬把一个很长的VARCHAR塞进索引。覆盖索引的代价同样是写入维护和存储消耗权衡好收益和成本。3.4 第四步对索引去重大胆删掉冗余设计完“应该有什么”还要回头删掉“不该留下来凑数”的索引。这一步是我在优化线上表时最重要的习惯毕竟索引不只靠设计还要靠减法。去重逻辑主要看三点前缀重复、字段重复、使用率为零。前缀重复指的是(a, b)和(a)的索引前者已经完全覆盖后者的查询能力后者可以删除。字段重复是相同字段组合创建了顺序不同的索引比如(a, b)和(b, a)这两者实际服务的能力不同不一定是冗余——但如果某索引自上线以来从未被使用说明它的组合并没有价值。使用率为零可以通过performance_schema.table_io_waits_summary_by_index_usage来查看索引的读取次数统计得非常直观没有读的索引就是纯浪费。实际操作中我会把这些索引集中起来列出“建议删除”清单然后在一个低峰时段先以ALGORITHMINPLACE方式删除。注意生产环境的删索引有锁表和耗时风险尽量放到流量低谷执行且每次只删一个观察业务指标再删下一个。3.5 第五步生产环境验证看执行计划说话设计完索引不是终点验证才是。每一次索引调整后都要跑一下核心SQL去确认EXPLAIN的输出看是不是用上了预期索引Rows估算是否大幅下降Extra是否出现Using index或Using index condition等关键标志。有一次我调整索引后发现明明新索引建好了EXPLAIN却依然走全表扫描。后来一查是统计信息没跟上执行了ANALYZE TABLE后优化器才识别到新索引。这个细节非常值得记下来索引建完不一定立即被优化器认可统计信息更新时间点很关键。如果在EXPLAIN里看到Using filesort说明索引没有覆盖ORDER BY字段需要检查字段排列是否把排序字段放到了最后看到Using temporary则说明GROUP BY或DISTINCT没有完全被索引覆盖通常需要把GROUP BY字段也尽量纳入索引末尾。用EXPLAIN作为校验工具比看执行时间判断靠谱得多因为执行时间会受到缓存和负载干扰而执行计划能直接告诉你索引有没有生效。4. 实战排坑索引设计中的常见错误与排查技巧4.1 索引失效的几个经典场景你大概率都中过招设计完索引和验证完执行计划索引还有不少容易写坏的地方。我把实战中最常见的几个索引失效问题列出来每个都对应着实际的踩坑经历。函数修饰索引列是一个非常高频的错误。比如WHERE DATE(created_at) 2025-01-01这种写法会让索引无法命中因为索引存储的是原始值不是DATE函数的结果。解决办法是改成范围条件created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00索引就能生效了。隐式类型转换同样致命字段是VARCHAR却传入数字或者字段是INT却传入字符串都会让MySQL放弃索引。最左前缀的使用错误就不用多说了前面讲联合索引时重点强调过。还有范围条件放在联合索引中间比如在等值字段中间插了一个查询会导致后续字段用不上索引。很多人以为WHERE a 1 AND b 10 AND c 2建(a,b,c)索引就能完全命中实际上c字段索引会在b范围扫描之后失效。这种情况下需要把索引顺序调整成(a,c,b)把范围条件放到最后才能让等值字段先精确命中。另外LIKE%关键字%这类前模糊查询也必然失效目前最优解法是搜索引擎或全文本索引这些细节都属于索引设计的“排雷清单”。4.2 一个真实案例从全表扫描到毫秒级返回的优化全过程有一个用户订单列表的接口数据量约600万行。最初的SQL长这样SELECT order_id, user_id, amount, status, created_at FROM order_detail WHERE user_id 455123 AND status PAID ORDER BY created_at DESC LIMIT 20;最初表上只有一个主键索引这个查询每次都要全表扫描过滤再加filesortp95耗时在1.8秒左右用户反馈页面转圈严重。我拿到这个SQL后没有急着建索引而是先看user_id的区分度– 600万行里有约15万不同用户区分度约2.5%能定位到比较小的范围。接下来设计联合索引(user_id, created_at, status)因为user_id是等值条件放第一位created_at是排序字段可以走索引排序status放在最后不影响前缀匹配。建索引后EXPLAIN显示type从ALL变为ref扫描行数从600万降到几十行Extra里明确显示Using index condition表示索引条件下推已经不需要全表扫描了。接口耗时从1.8秒掉到30毫秒以内。这个案例不复杂但它的核心在于不盲目加索引而是先看SQL的等值、范围、排序再设计字段顺序中间还包括了用覆盖索引避免回表的那一层考量。4.3 索引维护的日常巡检清单像体检一样对待每张核心表好的索引设计不是一次性的上线后要当成“身体指标”一样定期复查。我给自己维护的每张核心表都建了一份巡检计划频率根据表的写入频率和查询频率来定通常是每周或每两周一次。巡检清单包括几项核心内容执行SHOW INDEX FROM table查看所有索引定义找结构冗余执行performance_schema.table_io_waits_summary_by_index_usage看各索引的读取次数找出零使用索引执行SHOW TABLE STATUS对比Data_length和Index_length如果索引体积接近甚至超过数据体积说明索引膨胀严重再结合慢查询日志看是否有新的高频查询还没有被索引覆盖。巡检务必做到两点第一任何索引变更都走和建表一样的评审流程第二每次变更保留前后性能对比形成记录。这会让团队避免很多“删了索引又加上”的无用功。5. 不同场景下的索引策略进阶5.1 高并发写入场景索引多一分写入慢一分如果是交易、日志采集这类写多读少的场景索引设计的原则会更加收敛。比如秒杀系统的库存表每次更新都伴随行锁竞争如果表上挂了三四个二级索引更新时的索引维护会明显放大锁竞争窗口。这种表通常走主键直接更新二级索引要控制到最少。日志采集表也类似写入频率极高但查询相对简单一般只有按时间范围或按设备查询一个时间联合索引可能就满足需求。这时候多一个索引就是纯负担。我见过日志表建了七八个分析型索引写入性能下降了四成最后砍掉了一大半写入才回到正常水平。记住写多读少场景里索引宁缺毋滥。5.2 大表归档与实时查询混合场景索引要配合存储策略线上订单、消息记录这类表增长非常快通常归档和实时数据并存。索引设计要考虑数据生命周期和归档策略的配合。如果表里既有大量已归档的历史数据又有少量实时数据但所有数据仍在一个表里索引的维护成本和磁盘占用会越来越夸张。实际工程里通常建议核心热数据表只保留近期数据历史数据通过归档表或冷热分离存储解决这样表的索引体积和写入压力都大幅度降低。有些团队在冷热分离后实时表只用几个关键索引就能支撑住这是长期演进后的必然结果。5.3 高频等值查询与范围查询的组合在索引覆盖之间找平衡如果业务里同时存在“按用户查”和“按时间范围统计”两种查询是否要分别建两个索引我的建议是看频率不要无脑各建一个。如果两个查询频率都高各建一个没问题但如果“按时间范围统计”只是每天一次的低频报表却为了它建一个时间索引进而影响高并发核心查询的写入这就很不划算。在索引覆盖之间找平衡本质就是给不同查询排优先级。优先保证高频查询执行计划的稳定性——有些查询即使慢也只慢在凌晨没必要干扰白天的核心链路。这种取舍感是索引设计成熟与否的重要分水岭。我会在每次设计时问自己这个低频查询的耗时值得用几个高频写入的延迟来换吗6. 给未来的你索引重构的落地与反思6.1 接口变更或重构时把索引变更纳入研发流程索引不是DBA一个人的事研发人员在每次接口变更、SQL改写时都应该顺手评估索引需求。我们在团队里定了规矩代码评审时要附上本次变更涉及到的SQL及对应的索引影响分析。新SQL没走索引没关系但要说明理由要么已经设计好了索引方案要么确认数据量级下全表扫描可接受。这样做的最大价值是把索引设计的门槛埋到架构评审里去避免上线后才发现慢查询再回头补索引。一次补索引虽然也能解决但往往会在灰度环境、生产环境里产生额外的验证成本。6.2 从设计到运维索引生命力在于持续调整索引结构是有生命周期的。业务变了查询模式变了索引的价值也会随之变化。某个曾经极其关键的联合索引在业务调整后可能就彻底没有使用了。与其把它留在表上增加维护负担不如趁定期巡检把它去掉。我的经验是每次季度复盘时把核心表的索引清单重新扫一遍任何使用频率为零的索引都标记为“待观察”在下一次变更时顺手处理。这样一步步走下来索引数量才真正回归到业务需要的最小集合。每条核心业务线的性能指标也在一次次调整里保持健康。6.3 我踩过最深的坑以为索引是万能的后来明白优化是循环的我做索引优化最早期有个执念只要能加快查询就值得加索引。结果有一次把一个统计报表的各种维度组合全建上了索引一个12字段的表挂了9个索引。线上查询确实快了一些但每到业务高峰写入延迟就开始报警磁盘占用也飙升。后来把表拆了冗余统计数据把索引删到只剩4个才真正稳定下来。那次之后我彻底明白了索引设计本质上是一种工程权衡它的核心指标不是“快不快”而是“稳不稳”——在查询、写入、存储之间找到最稳定的平衡点才是这套结构真正的价值所在。这几年的经验沉淀下来我对索引的态度也慢慢变得更克制少建精建勤审视。也希望这篇文章能帮你少走一点弯路下次对着建索引需求时先问自己一句这个索引真的有资格留在表上吗
从一次夜班故障聊起索引设计为什么是门手艺活上周三凌晨两点我被值班电话叫醒。线上订单表的一个统计查询把CPU打到99%慢查询日志里全是同一类SQL——按状态和时间段拉取订单列表单次执行12秒。检查后发现这张表在半年内被陆续加了11个索引其中好几个字段组合完全重叠。我当时第一反应不是优化SQL而是先看执行计划优化器在这么多索引里反复纠结最后选了个最差的。这事不是个例。很多团队对索引的态度是两个极端要么建表时一个索引都不加上线后全表扫描被骂要么逮着查询就加索引不加思考到最后表上挂了十几个索引写入变慢、存储膨胀、优化器犯迷糊。所以我想把这几年做索引设计的经验整理一下核心就一句话索引不是越多越好真正高效的索引结构是设计出来的不是堆出来的。这篇文章不会讲那些烂大街的B树原理图解我想从一个实际从业者的角度聊清楚三件事索引到底在解决什么问题为什么索引多了反而坏事以及面对一张真实业务表怎么一步步设计出一套经得起生产环境考验的索引结构。适合刚接触索引优化的开发也适合被线上索引问题折磨过的DBA。1. 索引设计的本质先搞懂索引在跟谁打交道1.1 索引的本质是“找数据”的目录但它有自己的脾气数据库索引和书的目录非常像。你想在一本500页的书里找“索引设计”这个主题没有目录就得一页页翻有目录直接翻到对应页码。但索引比目录更复杂的地方在于它需要配合底层存储结构工作——在MySQL InnoDB里这个底层结构是B树。B树有几个特性决定了索引的设计逻辑数据都存储在叶子节点并且叶子节点之间通过双向指针连接非叶子节点只存索引键值和指向子节点的指针。这就带来两个直接结果等值查询和范围查询都很快因为走到叶子节点就能拿到数据范围查询靠指针顺序遍历即可每次写入都要维护树结构插入、删除、更新都可能触发节点分裂、合并这些操作是要付出代价的。理解了这一点你就能明白索引设计的第一性原理索引是用“写入时的维护成本”换“查询时的扫描成本”用“存储空间”换“查询时间”。任何索引设计都是在做这个权衡。你建的每个索引都意味着每次INSERT、UPDATE、DELETE要多维护一棵B树磁盘上要多占一份空间这账是躲不掉的。1.2 回表、覆盖索引和最左前缀三个绕不开的关键词要把索引设计好有几个基础概念必须刻在脑子里因为后面你做的每一个决定都跟它们有关。第一个是回表。InnoDB的主键索引聚簇索引的叶子节点直接存整行数据而普通索引二级索引的叶子节点存的是索引列的值加上主键值。所以用普通索引查询时先通过索引找到主键再用主键去聚簇索引里找完整行这就是回表。回表本身不一定是坏事但如果你的查询要命中几千行每行都回表一次那性能就崩了。第二个是覆盖索引。如果查询的列全部包含在某个二级索引的字段里那么直接扫描这个索引就能拿到所有需要的列不需要回表。这被称为覆盖索引优化是索引设计里的重要优化手段。后面我会讲到怎么刻意设计覆盖索引来消除回表。第三个是最左前缀原则。联合索引a, b, c本质上建立的是一棵以a、b、c排序的B树它可以支持a、ab、abc三种前缀查询但查询条件里如果跳过a直接查b或者跳过b直接查c索引就用不上了。这决定了联合索引字段顺序的排列是一个必须认真对待的问题。这三个概念共同指向一个核心认知索引不是“建了就生效”的装饰品它是被查询条件精准驱动的一种物理结构。你的字段顺序选错、字段冗余过多、覆盖不完整都可能让一个看似合理的索引实际发挥不出作用。1.3 区分度被绝大多数人忽略的索引第一指标我见过太多人设计联合索引时把建表语句里的字段挨个往索引里塞完全不看字段值的分布情况。结果建出来的索引比如性别状态区分度极低优化器看了一眼就放弃使用它直接走全表扫描。区分度这个词指的是某个字段不同值的数量占总行数的比例。比如一张100万行的表status字段只有3种取值待支付、已支付、已取消那么status的区分度就是3/1000000极低。本质上B树在查找时是靠逐层比较来缩小范围的区分度越低每层能排除的数据越少最终要扫的叶子节点越多索引的价值也就越低。这里有个实用的判断方法执行SELECT COUNT(DISTINCT column) / COUNT(*) FROM table比例越高代表区分度越好。我们通常认为区分度低于20%的字段不适合单独作为索引第一位但可以作为联合索引里的辅助字段用来过滤剩余范围。后面讲联合索引设计时我会把这一条作为排列字段顺序的核心依据之一。说了这么多基础概念是为了让大家明白索引设计不是拍脑袋它的每一步都应该有据可依。下面聊聊反面案例一个索引泛滥成灾的表到底是怎么把性能搞垮的。2. 索引泛滥的代价为什么不是越多越好2.1 索引开销的账本写入放大、存储膨胀、优化器选择困难很多开发对索引开销没有体感觉得无非是硬盘多点占用但现在SSD也不贵问题不大。这种想法会吃大亏因为索引的开销远不止存储空间这一项。写放大是最直接的问题。一张表每建一个二级索引每次INSERT就要多写一棵B树。如果一张表有10个索引一次行插入实际上要写11棵树如果是UPDATE还要看更新字段是否涉及索引列涉及的话需要同时维护多棵树。我曾经做过一次压测对一张5索引的2000万行表做批量更新比只有主键索引的同结构表慢了一倍还多。这就是索引带来的写放大高频写入的业务场景下这个成本会被放大得极其明显。存储膨胀也同样可观。MySQL的二级索引本身需要存储索引列的值和主键值为了支持长度较大的字段还会增加一些额外字节。10个索引用下来索引空间经常能和表数据空间平起平坐有些表索引甚至反超数据体积。你跑到SHOW TABLE STATUS一看Data_length和Index_length一比就知道问题有多严重。还有一个被忽略得比较深的坑是优化器选择困难。优化器不是神它依赖统计信息和成本估算来决定走哪个索引。索引越多统计信息越复杂优化器要在十几个候选索引里选择一个本身就有开销而且还可能因为统计信息滞后选错索引产生前文说的那种“有索引不走偏选最差的”情况。生产环境里最恐怖的不是没有索引而是大量无用索引干扰了优化器的判断。2.2 最典型的反面案例一张订单表上的11个索引我来说说开头提到的那个事故。那张订单表结构大致是这样订单ID主键、用户ID、订单状态、订单金额、支付时间、创建时间、商家ID。半年里每个业务方各建各的索引最后挂了11个单列或联合索引。我实际把索引列出来后发现至少有3个索引是彻底冗余的比如idx_status订单状态单独存在区分度只有几个值基本没用idx_status_createtime订单状态创建时间跟后面要说的idx_createtime_status顺序不同但部分场景下功能重叠还有两个时间字段各自单独建的索引导致范围查询时优化器在两个时间索引之间来回纠结。结果就是高频的“按商家拉一段时间内订单”的查询被迫在多个索引中选了一个先走商家索引再对结果集做状态过滤和排序扫描行数直接飙到几百万。而正确的姿势应该是为这类高频查询设计一个联合索引精确匹配商家ID再用时间范围过滤一步到位。核心问题是索引太多彼此干扰谁都没能盖出最优路径。2.3 优秀的索引设计是“覆盖高频查询”的系统不是列表合集既然索引不是越多越好那么反过来思考该怎么做我的答案是索引设计不该从字段出发而该从查询出发。把线上日志、慢查询记录、业务代码里的条件全部捞出来统计出高频查询的模式把高频SQL抽出来分析它的WHERE条件、ORDER BY、GROUP BY、JOIN条件然后为这些“有价值的查询”设计索引。索引是给查询服务的查询是索引的唯一目的。同时要建立审查机制任何想新增的索引都必须回答三个问题——这个索引覆盖了哪个具体查询它是否与其他索引功能重叠如果必须加它是通过调整已有索引的字段顺序能解决的不满足就坚决不加。这套机制做下来你会发现绝大多数表有5个以内的索引就足够支撑业务了。3. 设计一套真正高效索引的完整流程3.1 第一步收集与分析查询模式画出高频查询清单索引设计的第一个里程碑不是写CREATE INDEX而是把查询收集齐。具体做法分几步先从线上慢查询日志里拉出TOP 20的SQL再让研发从业务代码里把核心接口的SQL提出来同时把高频报表统计的SQL也汇总进来。把这些查询分门别类整理按执行频率和耗时排序重点标注那些“单次执行不慢但每秒执行几百次”的查询——这类查询才是索引设计的核心服务对象。我在实际项目里通常会建一个查询模式表字段包括SQL模板、执行频率、耗时、涉及表、WHERE条件、ORDER BY字段、是否需要回表。整理完后把WHERE和ORDER BY的字段组合圈出来这就是候选索引的字段池。这个过程非常关键但很多人跳过了直接凭感觉建索引这就跑偏了。举个实际的例子假设我们梳理出一张表有三个高频查询按用户ID查最近订单列表需要按创建时间倒序SELECT * FROM orders WHERE user_id ? ORDER BY created_at DESC LIMIT 20;按商家ID和状态查订单需要按创建时间倒序SELECT * FROM orders WHERE merchant_id ? AND status ? ORDER BY created_at DESC LIMIT 50;按订单状态统计某时间段的订单数SELECT COUNT(*) FROM orders WHERE status ? AND created_at BETWEEN ? AND ?;这三条SQL就是设计索引的依据。如果你能画出这样的清单就已经完成了80%的索引设计工作。剩下的是如何排列组合字段顺序的问题。3.2 第二步利用区分度与等值条件决定联合索引字段顺序联合索引字段顺序为什么重要因为最左前缀原则决定了索引只能在字段顺序上“从头连续匹配”。你要让查询能用上联合索引WHERE条件里涉及的字段必须从联合索引的最左列开始连续命中。所以字段顺序的设计目标只有一个让每个高频查询能用最少的索引覆盖到最多的查询条件。排序的逻辑我一般按以下优先级排列先放等值查询字段区分度高的优先WHERE里的等值条件字段user_id、merchant_id这种是最佳候选因为等值命中会直接定位到一段连续区域区分度越高定位越精准再放范围查询字段ORDER BY字段本质上也是范围排序。比如created_at放在等值字段后面可以让索引天然按时间排好序省掉一次filesort最后才是低区分度字段比如status如果前面等值条件已经定位得很小了把它放后面后续过滤就行但不要把它放在第一位。回到刚才那三条SQL显然对查询1联合索引(user_id, created_at)是最优解user_id等值命中created_at倒序扫描即可对查询2联合索引(merchant_id, status, created_at)是合理的merchant_id和status等值精确命中created_at帮忙排序对查询3联合索引(status, created_at)可以做成实际上的覆盖查询虽然status区分度低但它配合created_at范围后扫描范围大幅缩小且从索引直接计数就不用回表了。不要觉得这里太简单很多人挂在字段顺序上就是因为没把等值、范围、排序的逻辑理清楚。我们一步一步分析最终落到代码上时就知道每个字段都有明确的位置逻辑。3.3 第三步善用覆盖索引让SQL免回表在设计时要刻意观察SQL查询出的列是否有可能把查询列“包进索引”里形成覆盖索引从而省掉回表。比如一个高频查询SELECT user_id, status, created_at FROM orders WHERE user_id ? AND created_at ?;如果只建(user_id, created_at)索引查询返回的status列不在索引里每行都要回表如果命中的行数比较多回表开销就很扎眼。这时把索引调整为(user_id, created_at, status)就能让索引覆盖这组查询列不需要回表。加一个字段没多少成本性能却提升一个等级。我在线上实践中很多接口最终快得惊人的秘诀就是覆盖索引。但要留意覆写不要过度不要为了覆盖一个不重要的字段硬把一个很长的VARCHAR塞进索引。覆盖索引的代价同样是写入维护和存储消耗权衡好收益和成本。3.4 第四步对索引去重大胆删掉冗余设计完“应该有什么”还要回头删掉“不该留下来凑数”的索引。这一步是我在优化线上表时最重要的习惯毕竟索引不只靠设计还要靠减法。去重逻辑主要看三点前缀重复、字段重复、使用率为零。前缀重复指的是(a, b)和(a)的索引前者已经完全覆盖后者的查询能力后者可以删除。字段重复是相同字段组合创建了顺序不同的索引比如(a, b)和(b, a)这两者实际服务的能力不同不一定是冗余——但如果某索引自上线以来从未被使用说明它的组合并没有价值。使用率为零可以通过performance_schema.table_io_waits_summary_by_index_usage来查看索引的读取次数统计得非常直观没有读的索引就是纯浪费。实际操作中我会把这些索引集中起来列出“建议删除”清单然后在一个低峰时段先以ALGORITHMINPLACE方式删除。注意生产环境的删索引有锁表和耗时风险尽量放到流量低谷执行且每次只删一个观察业务指标再删下一个。3.5 第五步生产环境验证看执行计划说话设计完索引不是终点验证才是。每一次索引调整后都要跑一下核心SQL去确认EXPLAIN的输出看是不是用上了预期索引Rows估算是否大幅下降Extra是否出现Using index或Using index condition等关键标志。有一次我调整索引后发现明明新索引建好了EXPLAIN却依然走全表扫描。后来一查是统计信息没跟上执行了ANALYZE TABLE后优化器才识别到新索引。这个细节非常值得记下来索引建完不一定立即被优化器认可统计信息更新时间点很关键。如果在EXPLAIN里看到Using filesort说明索引没有覆盖ORDER BY字段需要检查字段排列是否把排序字段放到了最后看到Using temporary则说明GROUP BY或DISTINCT没有完全被索引覆盖通常需要把GROUP BY字段也尽量纳入索引末尾。用EXPLAIN作为校验工具比看执行时间判断靠谱得多因为执行时间会受到缓存和负载干扰而执行计划能直接告诉你索引有没有生效。4. 实战排坑索引设计中的常见错误与排查技巧4.1 索引失效的几个经典场景你大概率都中过招设计完索引和验证完执行计划索引还有不少容易写坏的地方。我把实战中最常见的几个索引失效问题列出来每个都对应着实际的踩坑经历。函数修饰索引列是一个非常高频的错误。比如WHERE DATE(created_at) 2025-01-01这种写法会让索引无法命中因为索引存储的是原始值不是DATE函数的结果。解决办法是改成范围条件created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00索引就能生效了。隐式类型转换同样致命字段是VARCHAR却传入数字或者字段是INT却传入字符串都会让MySQL放弃索引。最左前缀的使用错误就不用多说了前面讲联合索引时重点强调过。还有范围条件放在联合索引中间比如在等值字段中间插了一个查询会导致后续字段用不上索引。很多人以为WHERE a 1 AND b 10 AND c 2建(a,b,c)索引就能完全命中实际上c字段索引会在b范围扫描之后失效。这种情况下需要把索引顺序调整成(a,c,b)把范围条件放到最后才能让等值字段先精确命中。另外LIKE%关键字%这类前模糊查询也必然失效目前最优解法是搜索引擎或全文本索引这些细节都属于索引设计的“排雷清单”。4.2 一个真实案例从全表扫描到毫秒级返回的优化全过程有一个用户订单列表的接口数据量约600万行。最初的SQL长这样SELECT order_id, user_id, amount, status, created_at FROM order_detail WHERE user_id 455123 AND status PAID ORDER BY created_at DESC LIMIT 20;最初表上只有一个主键索引这个查询每次都要全表扫描过滤再加filesortp95耗时在1.8秒左右用户反馈页面转圈严重。我拿到这个SQL后没有急着建索引而是先看user_id的区分度– 600万行里有约15万不同用户区分度约2.5%能定位到比较小的范围。接下来设计联合索引(user_id, created_at, status)因为user_id是等值条件放第一位created_at是排序字段可以走索引排序status放在最后不影响前缀匹配。建索引后EXPLAIN显示type从ALL变为ref扫描行数从600万降到几十行Extra里明确显示Using index condition表示索引条件下推已经不需要全表扫描了。接口耗时从1.8秒掉到30毫秒以内。这个案例不复杂但它的核心在于不盲目加索引而是先看SQL的等值、范围、排序再设计字段顺序中间还包括了用覆盖索引避免回表的那一层考量。4.3 索引维护的日常巡检清单像体检一样对待每张核心表好的索引设计不是一次性的上线后要当成“身体指标”一样定期复查。我给自己维护的每张核心表都建了一份巡检计划频率根据表的写入频率和查询频率来定通常是每周或每两周一次。巡检清单包括几项核心内容执行SHOW INDEX FROM table查看所有索引定义找结构冗余执行performance_schema.table_io_waits_summary_by_index_usage看各索引的读取次数找出零使用索引执行SHOW TABLE STATUS对比Data_length和Index_length如果索引体积接近甚至超过数据体积说明索引膨胀严重再结合慢查询日志看是否有新的高频查询还没有被索引覆盖。巡检务必做到两点第一任何索引变更都走和建表一样的评审流程第二每次变更保留前后性能对比形成记录。这会让团队避免很多“删了索引又加上”的无用功。5. 不同场景下的索引策略进阶5.1 高并发写入场景索引多一分写入慢一分如果是交易、日志采集这类写多读少的场景索引设计的原则会更加收敛。比如秒杀系统的库存表每次更新都伴随行锁竞争如果表上挂了三四个二级索引更新时的索引维护会明显放大锁竞争窗口。这种表通常走主键直接更新二级索引要控制到最少。日志采集表也类似写入频率极高但查询相对简单一般只有按时间范围或按设备查询一个时间联合索引可能就满足需求。这时候多一个索引就是纯负担。我见过日志表建了七八个分析型索引写入性能下降了四成最后砍掉了一大半写入才回到正常水平。记住写多读少场景里索引宁缺毋滥。5.2 大表归档与实时查询混合场景索引要配合存储策略线上订单、消息记录这类表增长非常快通常归档和实时数据并存。索引设计要考虑数据生命周期和归档策略的配合。如果表里既有大量已归档的历史数据又有少量实时数据但所有数据仍在一个表里索引的维护成本和磁盘占用会越来越夸张。实际工程里通常建议核心热数据表只保留近期数据历史数据通过归档表或冷热分离存储解决这样表的索引体积和写入压力都大幅度降低。有些团队在冷热分离后实时表只用几个关键索引就能支撑住这是长期演进后的必然结果。5.3 高频等值查询与范围查询的组合在索引覆盖之间找平衡如果业务里同时存在“按用户查”和“按时间范围统计”两种查询是否要分别建两个索引我的建议是看频率不要无脑各建一个。如果两个查询频率都高各建一个没问题但如果“按时间范围统计”只是每天一次的低频报表却为了它建一个时间索引进而影响高并发核心查询的写入这就很不划算。在索引覆盖之间找平衡本质就是给不同查询排优先级。优先保证高频查询执行计划的稳定性——有些查询即使慢也只慢在凌晨没必要干扰白天的核心链路。这种取舍感是索引设计成熟与否的重要分水岭。我会在每次设计时问自己这个低频查询的耗时值得用几个高频写入的延迟来换吗6. 给未来的你索引重构的落地与反思6.1 接口变更或重构时把索引变更纳入研发流程索引不是DBA一个人的事研发人员在每次接口变更、SQL改写时都应该顺手评估索引需求。我们在团队里定了规矩代码评审时要附上本次变更涉及到的SQL及对应的索引影响分析。新SQL没走索引没关系但要说明理由要么已经设计好了索引方案要么确认数据量级下全表扫描可接受。这样做的最大价值是把索引设计的门槛埋到架构评审里去避免上线后才发现慢查询再回头补索引。一次补索引虽然也能解决但往往会在灰度环境、生产环境里产生额外的验证成本。6.2 从设计到运维索引生命力在于持续调整索引结构是有生命周期的。业务变了查询模式变了索引的价值也会随之变化。某个曾经极其关键的联合索引在业务调整后可能就彻底没有使用了。与其把它留在表上增加维护负担不如趁定期巡检把它去掉。我的经验是每次季度复盘时把核心表的索引清单重新扫一遍任何使用频率为零的索引都标记为“待观察”在下一次变更时顺手处理。这样一步步走下来索引数量才真正回归到业务需要的最小集合。每条核心业务线的性能指标也在一次次调整里保持健康。6.3 我踩过最深的坑以为索引是万能的后来明白优化是循环的我做索引优化最早期有个执念只要能加快查询就值得加索引。结果有一次把一个统计报表的各种维度组合全建上了索引一个12字段的表挂了9个索引。线上查询确实快了一些但每到业务高峰写入延迟就开始报警磁盘占用也飙升。后来把表拆了冗余统计数据把索引删到只剩4个才真正稳定下来。那次之后我彻底明白了索引设计本质上是一种工程权衡它的核心指标不是“快不快”而是“稳不稳”——在查询、写入、存储之间找到最稳定的平衡点才是这套结构真正的价值所在。这几年的经验沉淀下来我对索引的态度也慢慢变得更克制少建精建勤审视。也希望这篇文章能帮你少走一点弯路下次对着建索引需求时先问自己一句这个索引真的有资格留在表上吗
