刚接手一个线上订单系统的时候我干过一件蠢事因为慢查询太多一口气给核心表加了七八个索引结果业务高峰期一来订单写入直接堵死数据库CPU飙升到99%连带商品查询也跟着遭殃。当时我就站在工位前盯着监控大屏后台同事疯狂艾特我那种感觉比面试被问倒还难受。后来把多余索引一个个拆掉写入才缓过来。从那以后我对索引的态度就变了MySQL索引确实是优化查询的第一利器但它从来不是免费的午餐更不是越多越好。很多人一看到查询慢就条件反射式地建索引完全不考虑写入开销、存储成本和优化器的判断负担结果往往适得其反。这篇文章我想结合自己这些年在生产环境里踩过的坑把MySQL索引的设计逻辑、取舍标准和实操方法一次讲透。不管你是刚入门的新手还是已经被线上慢查询折磨过的老开发都应该能在里面找到直接能用的思路。1. 先搞明白索引到底帮你做了什么又让你付出了什么1.1 索引提速的核心原理从翻书到查目录要理解索引为什么有用先想想没有索引时MySQL怎么查数据。假设订单表里有几百万行记录你要按user_id找某个用户的所有订单在没有索引的情况下MySQL只能从第一行开始一行一行扫到末尾这叫全表扫描。数据量少的时候无所谓数据量一大每次查询都是几百万次比较慢是必然的。索引本质上就是一个排好序的数据结构MySQL里最常见的InnoDB索引底层是B树。你可以把它想象成一本书的目录没有目录你得一页页翻有了目录你可以直接跳到对应的章节。B树通过在每一层做有序查找把查找次数从“行数N次”压缩到“树的层数”级别通常是三四层。也就是说哪怕表里有几千万行数据走索引查询也就几次磁盘IO的事情。这就是索引的价值所在用极少的查找次数换来接近常数的查询性能。但注意这里的关键词是“有序查找”——索引之所以快是因为它在建立时就把数据排好了序而这个“排序”的工作不是凭空来的。1.2 索引的真实成本每次写入都要同步维护这是很多人建索引时最容易忽略的点索引不是建完就完事了它需要在每次INSERT、UPDATE、DELETE时同步维护。举一个最简单的例子。你有一张user表包含id、name、email三个字段你给name建了索引。现在插入一条新记录MySQL的工作量不只是往主键索引里插一行数据还要额外往name的辅助索引里插入对应的索引项。如果表上有五六个索引每插一条数据就要同步更新五六个B树。插入本身倒还好更麻烦的是如果插入位置导致页分裂或索引页重排开销会成倍增加。对于读多写少的表比如配置表、商品分类表索引多几个影响不大但对于订单、日志、交易流水这类高并发写入的表每多一个索引就意味着每次写入都多一块硬成本。我的经验是流水型表上的索引必须精打细算能用复合索引覆盖多个查询场景的绝不建多个单列索引。1.3 存储与内存成本索引不是只占磁盘空间那么简单每个索引都会对应一个独立的B树存储在磁盘上占用磁盘空间是明摆着的成本。但更隐蔽的成本在内存里InnoDB的缓冲池是有限的内存资源而索引页和数据页都会竞争这块缓冲池。索引越多缓存池里被索引页吃掉的空间就越多数据页能缓存的部分就越少命中率下降查询反而变慢。我遇到过一种典型的场景表本身只有几个常用字段但为了应对各种临时的查询需求前后累计建了十来个索引。磁盘倒是没爆但缓冲池被占得很厉害连主键索引的热点数据都经常被挤出去导致明明很简单的查询反而频繁走磁盘IO。检查之后发现几个冷门索引几乎从没被用到过纯粹是占着内存不干活。所以判断一个索引价值的时候不能只看它有没有用还要看它的使用频率和维护成本。一个每天只用一两次的索引和它是同行既占磁盘又占内存还拖累每次写入这个账必须算清楚。1.4 索引太多还会干扰优化器的选择MySQL在执行查询时会通过优化器决定走哪个索引、怎么连接表而优化器判断的依据之一就是统计信息。如果一张表上有大量索引优化器需要评估的选择就会变多虽然这个开销通常在毫秒级但真正的问题不在这里而在于优化器偶尔会选错索引。我踩过的一个真实坑某张表上有idx_a和idx_b两个索引优化器根据采样统计估算后选择了idx_a但实际数据分布下idx_b的过滤效果更好结果一个本应几十毫秒的查询跑了几秒钟。你可能会说这是统计信息过期的问题但不可否认索引数量越多优化器选错的概率就越高。更麻烦的是当你发现SQL没有走预期的索引时排查起来也很痛苦——你得逐个验证每个索引的选择性再对比实际执行时间。索引少的时候这个问题基本不会出现索引一多各种奇怪的执行计划就开始冒出来了。2. 什么情况下真的不该建索引2.1 小表小数据量索引的收益趋近于零我在培训新人时经常说一句话先看表里有多少数据再决定要不要建索引。对于只有几千行的小表全表扫描本身就是很快的MySQL一次IO就能把整张表读进来走索引反而可能因为要先查索引树再去回表多出几次IO性能不升反降。当然小表也不是绝对不能建索引外键字段、唯一性字段该建还是要建但从“优化查询性能”的角度来说没必要为了一个百万行级别才可能出现的慢查询去给小表加索引。等你真正遇到性能瓶颈再补索引也完全来得及建索引本身很快不存在“事后无法弥补”的问题。实操里我遇到过有人给一张只有几百条记录的省份城市表建了三四个索引理由是“以后数据会涨”。听起来合理但几年过去了这张表还是几百条记录索引纯粹变成了摆设。这种情况的教训是索引设计要基于当前的数据特征和真实的查询模式不要为想象中的未来过度设计。2.2 低区分度字段性别、状态这类字段的索引陷阱区分度是索引设计中一个容易被忽视的概念。简单说区分度就是字段不同值的数量占总行数的比例。区分度越高索引的过滤效果越好区分度越低索引的价值就越低。拿性别字段举例一张用户表里性别无非就是男、女、未知三个值你给性别建索引查某个性别可能还是扫出三分之一的行数MySQL优化器算完这笔账之后通常会发现还不如直接全表扫描来得快。类似的还有状态字段成功/失败/处理中、逻辑删除标记、布尔类型的字段这类低区分度字段建索引十有八九是白建。但这里有个常见误区要注意低区分度字段不能单独建索引不代表它在复合索引里没价值。比如查询条件是“状态成功 AND 创建时间 某个时间点”把状态字段放在复合索引的最前面可以快速过滤掉大部分不相关的数据再配合时间字段精确筛选效果就很好。关键在于组合使用而不是单打独斗。2.3 高并发写入的流水表索引越多写入瓶颈越早到来对于订单表、日志表、操作流水表这类高频写入的表索引数量直接决定你的写入性能天花板。我做过一次压测对比一张写入频率很高的流水表从只保留主键索引到加上四个辅助索引写入吞吐量直接掉了将近40%延迟也从原来的十几毫秒涨到了几十毫秒。原因不难理解前面讲过每次插入都要同步维护所有索引而且随着数据量增长索引页不断分裂、合并写入放大效应会越来越明显。如果再加上事务并发多个事务同时在修改同一个索引页锁竞争也会加剧。对这种表我的建议是能不加索引就不加实在需要索引优先考虑复合索引覆盖多个查询场景并且定期清理那些长期没有命中的索引。很多团队习惯“每接到一个慢查询就加一个索引”结果表上越攒越多最终变成谁也不敢删、谁建谁背锅的局面。正确的做法是统计所有慢查询的共性用一两个复合索引去统一覆盖。2.4 某些时候索引会被优化器直接无视有一种特别让人无语的情况索引建了SQL也符合使用条件但优化器就是不走索引。常见原因包括查询条件里对索引列做了函数运算、隐式类型转换、使用LIKE %xx等非前缀模糊匹配这些我后面会展开讲。这里想提醒的是建索引之前先对照你的SQL写法审查一遍。如果SQL本身就写得让索引无法生效比如在索引列上使用了函数或计算那建索引等于白建。先改SQL写法再谈索引设计顺序别搞反了。3. 复合索引怎么设计才不浪费3.1 最左前缀原则复合索引的灵魂规则复合索引也叫联合索引是生产中应用最多、也最容易用错的索引类型。它最核心的规则就是最左前缀原则查询条件里必须从复合索引的最左列开始匹配索引才会被有效利用。假设你建了一个复合索引(a, b, c)那么它实际可以支持以下几种查询只用a作为查询条件用a和b作为查询条件用a、b、c三个条件组合查询但如果你跳过a直接用b或c作为查询条件这个索引基本就用不上了。这里的原理跟字典的编排方式是一样的就像查《新华字典》得先按拼音排到首字母你不能直接翻到某个字的某个声调那一页去找它。理解了这一点你就知道为什么很多常见的建议是“把区分度最高的字段放在复合索引最前面”——因为最左列是使用频率最高、过滤能力最强的那个起点它的区分度直接决定了索引前几层的剪枝效率。3.2 列顺序的取舍区分度优先但也要兼顾查询频率不过“区分度优先”也不是绝对的我还得补充一个维度查询频率。区分度高但几乎不会被当作查询条件的字段放在最前面意义有限相反区分度稍低但几乎所有查询都会用到的等值条件字段放到最前面往往收益更大。举个例子订单表里有user_id和order_status两个字段order_status区分度很低但业务上所有查询都会带上“状态已完成”这个条件user_id区分度高但只有部分查询用到。如果索引设计成(order_status, user_id)那么所有使用user_id的查询其实也能走索引最左列order_status可以通过后面条件配合而且带状态的查询也能有效过滤。这中间的取舍没有绝对正确的答案核心依据是你们业务的真实查询特征。我的建议是把线上慢查询日志拉出来统计一下哪些字段组合出现频率最高再基于这个频率来排序列顺序而不是拍脑袋决定。3.3 覆盖索引让查询连回表都省了覆盖索引是一个性价比极高的优化技巧但在实际项目里用的人不多。所谓覆盖索引就是查询需要的所有字段都已经包含在索引树里MySQL可以直接从索引中拿到结果不需要再回表去主键树里捞数据。比如你经常执行SELECT user_id, status FROM order_table WHERE create_time 2024-01-01如果有一个(create_time, user_id, status)的复合索引那么MySQL在走这个索引的时候就能把user_id和status直接从索引页里取出来省掉一次回表操作。回表意味着额外的随机IO在大量数据的情况下省掉回表的性能提升非常明显。设计覆盖索引的关键思路是针对高频查询把查询涉及的字段都塞进同一个复合索引里。当然这也会增加索引的存储空间和维护成本所以只建议对真正高频的热点查询做覆盖索引而不是把所有查询都试图“覆盖”掉。索引字段越多B树每个叶子节点能容纳的索引项就越少索引页越多内存占用和维护开销都会上升这个度要把控好。3.4 冗余索引的识别与清理冗余索引是我在生产库里最常看到的浪费类型。冗余索引的典型形态包括已有复合索引(a, b)又单独建了(a)后者就是冗余的已有复合索引(a, b, c)又建了(a, b)后者也是冗余的道理很简单复合索引(a, b)本身就能支持只查a的查询场景单独建(a)完全没有必要。不少团队因为每次优化都“加索引”从不回头梳理导致这种冗余越积越多白白承担了存储和写入成本。清理冗余索引的办法也不难先通过SHOW INDEX FROM table_name查看所有索引再结合业务SQL逐条判断每个索引是否有独立的使用场景。如果某个索引的所有使用场景都能被另一个更宽的复合索引覆盖那它就可以安全删掉。我在清理一个老项目的索引时光冗余索引就删掉了5个表结构瞬间清爽写入性能也有了明显改善。4. 索引失效排查别让你的索引白建4.1 最常见失效场景函数运算、隐式转换、模糊匹配索引失效是面试高频题也是生产环境最折磨人的问题之一。我整理几个自己真实遇到过的高频场景每个都附上原因分析。第一类是函数运算。比如WHERE DATE(create_time) 2024-01-01这种写法会导致索引失效因为MySQL需要对每一行的create_time都先执行DATE()函数才能和常量比较索引的有序性完全失效。解决办法是改写为范围条件WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。这不仅仅是“能不能走索引”的问题改完之后性能提升通常是数量级的。第二类是隐式类型转换。比如字段phone是 varchar 类型但你用WHERE phone 13800138000来查询MySQL会把字段隐式转换为数字再比较索引同样失效。解决办法是保证SQL里的参数类型和字段类型一致手机号就老老实实加引号。第三类是模糊匹配。LIKE %abc这种后缀通配符开头的写法索引无法利用因为B树是按前缀排序的你不知道以什么字符开头就没法走树查找。但LIKE abc%是可以用索引的前缀已经确定可以走范围扫描。4.2 用 EXPLAIN 看懂执行计划遇到索引问题第一反应应该是用EXPLAIN看执行计划而不是瞎猜。这是一个极度重要的习惯我见过太多同事绕来绕去猜问题最后用EXPLAIN一看类型一目了然。一个基础示例EXPLAIN SELECT * FROM order_table WHERE user_id 123 AND create_time 2024-06-01;重点看几个字段type从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就说明在走全表扫描基本可以断定索引没生效。key实际使用的索引名称如果为NULL说明没有索引被使用。rows预估扫描的行数行数越大性能越差。Extra如果出现Using filesort或Using temporary说明排序或分组没有利用索引这也是索引设计的问题信号。EXPLAIN 是排查索引问题的基本功它的价值在于把MySQL优化器的思考过程暴露给你看。看到rows从几百万缩小到几千你会直观感受到索引带来的改变。4.3 查询条件明明有索引却显示没走索引的隐蔽原因有一种情况比较隐蔽字段有索引SQL也看似正常但EXPLAIN显示没走索引。我遇到过两种典型的成因。第一个是联合查询中的字符集不一致。两张表关联字段一个用了utf8mb4一个用了utf8MySQL做连接时需要对其中一列做隐式转换导致索引失效。这类问题很隐蔽因为SQL本身看不出任何问题只能在表结构审查时发现。建议统一所有表的字符集为utf8mb4连带排序规则也保持一致。第二个是OR条件的滥用。比如WHERE user_id 123 OR status 1即使user_id和status各自都有索引MySQL也可能不走索引而是选择全表扫描——因为用OR连接的条件需要分别扫两个索引再合并结果优化器计算后觉得还不如直接全扫。解决办法是把OR拆分写成两个查询用UNION ALL合并或者使用IN替代部分场景的OR。4.4 常见索引问题速查表症状原因排查思路解决方向查询突然变慢SQL写法导致函数运算用EXPLAIN查看type是否为ALL改写SQL为范围查询避免对列做函数运算索引存在但未命中隐式类型转换对比字段类型与SQL参数类型保证类型一致常量加引号LIKE模糊查询慢前后缀模糊匹配检查LIKE写法改写为前缀匹配或引入全文索引排序字段导致filesort缺少覆盖排序字段的索引查看Extra是否出现Using filesort复合索引包含排序字段注意排序方向复合索引不生效违反最左前缀原则检查查询条件的列顺序调整查询条件顺序或重新设计索引列序OR条件连接多索引合并代价高分析OR两侧条件的区分度拆分为UNION ALL或使用IN写入慢冗余索引过多列出全部索引审视必要性清理冗余索引精简索引数量5. 一次完整的索引优化实战记录5.1 从慢查询日志里定位问题SQL去年我接手的一个后台统计系统页面加载越来越慢用户反馈很强烈。接手后的第一步我把慢查询日志开了起来设置阈值200毫秒SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.2;运行一天后收集日志发现超过一半的慢查询都集中在同一张user_visit_log表上表的规模已经到两千多万行。最具代表性的一个SQL是SELECT user_id, COUNT(*) AS visit_count FROM user_visit_log WHERE visit_date 2024-05-01 AND visit_date 2024-06-01 AND channel app GROUP BY user_id ORDER BY visit_count DESC LIMIT 100;这个SQL的目的是统计某个时间段内各渠道的访问用户排行每次执行都要两三秒钟已经明显卡顿。5.2 分析表结构和现有索引我先把这张表的现有索引全部列出来SHOW INDEX FROM user_visit_log;结果发现表上有五个索引idx_visit_date、idx_channel、idx_user_id、idx_visit_date_channel还有一个冗余的idx_channel_visit_date跟idx_visit_date_channel基本一样只是列序相反。看到这里我心里基本有数了索引不少但没有一个是针对这个核心查询设计的。然后我针对慢SQL做了EXPLAIN分析EXPLAIN SELECT user_id, COUNT(*) AS visit_count FROM user_visit_log WHERE visit_date 2024-05-01 AND visit_date 2024-06-01 AND channel app GROUP BY user_id ORDER BY visit_count DESC LIMIT 100;结果显示key用的是idx_visit_date_channelrows预估60多万行Extra里有Using temporary; Using filesort。也就是说MySQL虽然用上了索引但因为查询需要按user_id分组排序还得额外创建临时表性能瓶颈很明显。5.3 制定索引方案并验证效果针对这个SQL真正匹配的索引应该把过滤字段和分组字段都考虑进去。我提出的方案是新建一个复合索引(channel, visit_date, user_id)channel放在最前面因为这个查询里它是等值条件区分度虽然一般但能最先缩小范围visit_date排在第二用于时间范围过滤user_id排在第三可以让GROUP BY user_id直接利用索引的顺序省掉临时表和排序。创建索引ALTER TABLE user_visit_log ADD INDEX idx_channel_date_user (channel, visit_date, user_id);创建后再跑一次EXPLAINtype从原来的ref变成了rangerows从60多万降到了不到2万Extra里的Using temporary; Using filesort彻底消失了。执行时间从原来的2.3秒降到了0.18秒查询性能提升了十倍不止。同时我把冗余的idx_channel_visit_date列序颠倒的那个直接删掉idx_user_id因为还有其他查询在用保留了下来。整个优化动作就两个一个新增索引一个删除冗余索引效果立竿见影。5.4 优化过程里的经验总结这个案例很好地说明了一个道理索引设计不是越多越好而是要贴着核心查询来设计。一个设计得当的复合索引可以同时解决过滤、排序、分组三个问题而一堆各自为战的单列索引往往什么都覆盖不好还拖累写入性能。另外还要提醒一点索引不是加完就完事了每隔一段时间要回来看一眼尤其是表结构和查询模式发生变化的时候。线上业务迭代快三个月前的热点查询可能已经被新功能替代三个月前设计的索引可能已经无人问津。定期清理无用索引、为新的核心查询补索引应该成为常规的运维动作而不是等到慢查询爆发才想起来。我个人在优化过程中的体会是动手之前先花时间搞清楚查询的特征是等值还是范围需不需要排序分组能不能覆盖索引这些问题的答案直接决定索引的列顺序和字段选择。拿一句话总结就是索引是拿来解决问题的不是拿来壮胆的每建一个索引你都应该能说出它服务的是哪一类SQL。