先说一个我印象很深的场景某内部系统里一张订单表只有十多万行按时间和状态做分页查询居然跑了接近3秒。当时第一反应是服务器配置太低后来把SQL拉出来看问题根本不在机器而是MySQL索引已经乱成筛子——表里只有一个主键剩下全是全表扫描连最基本的组合查询都没索引可用。这个案例几乎代表了大多数人遇到MySQL索引时的状态知道要建索引但不知道为哪条SQL建、建在哪些列上、为什么有时候建了索引还是慢。这篇内容我打算从底层结构讲到设计套路再讲最常见的踩坑场景最后给出一套可以照着用的排查思路。不管你是刚接手项目的新手还是被线上慢查询折磨过的开发应该都能找到能直接落地的东西。1. 先从根上理解MySQL索引到底在解决什么问题1.1 没有索引的时候查询就像在没有页码的字典里翻字在没有索引的情况下MySQL要做的是全表扫描。所谓全表扫描就是把表的聚簇索引叶子节点上的数据页从头到尾读一遍逐行判断是否满足where条件。数据量小的时候感觉不明显一旦表到了几十万、几百万行这个动作就非常直观了读的页数越多磁盘IO越多查询自然越慢。我习惯用字典来类比。字典有拼音目录和偏旁部首目录你不会从第一页开始一页页翻到最后去找一个字因为那要检查每一个字。数据库也一样如果查询条件上没有索引存储引擎只能老老实实遍历每一行记录把不满足条件的行丢掉最后把满足条件的行返回。哪怕最后只返回一行它也要把整张表扫完才知道“没有别的了”。这也是为什么很多慢查询明明只查一条数据却慢到难以接受。问题不在单次匹配的成本而在“必须确认没有遗漏”的扫描范围太大了。1.2 索引的本质用额外的存储空间换查询速度索引本质上是一份额外的数据结构它把字段值和对应行位置之间的关系重新组织一遍。这样查询时先到索引里快速定位再根据定位信息找到原始数据不用全表遍历。但这份“额外”是有代价的。每建一个索引写入数据的时候就要多维护一份结构 insert、update、delete 都需要同步修改所有相关索引。所以索引不是白送的加速器它是用写入性能和磁盘空间换读取性能。一个表建十个索引等于每次写入要同时维护十份副本写入慢是必然的。理解了这一点就不会盲目“给所有字段都加索引”。线上很多表的写入性能问题其实不是服务器不行而是索引冗余到每次插入都要更新大量索引页。设计索引的第一原则永远是先看业务到底有哪些查询路径再决定索引怎么建。1.3 为什么主流用B树而不是哈希表或二叉树MySQL的InnoDB引擎里索引默认使用B树结构。有人会问哈希表查找不是更快吗确实等值查询下哈希索引的复杂度可以到O(1)看起来非常理想。但业务查询很少只有等值条件你总会遇到范围查询、排序、分组。哈希表天然不支持按范围遍历数据在哈希结构里是无序的想查“某个区间内的数据”只能逐个碰撞。二叉树虽然有序但树的高度会随着数据量增加而变高每一层都可能产生一次磁盘IO在千万级数据下表现并不好。B树是多叉平衡树层数非常低。InnoDB中一个页默认16KB配合主键值三到四层就能容纳千万级甚至亿级数据。再加上所有叶子节点通过链表串联范围查询和排序可以直接沿着链表顺序读效率非常高。这就是它成为关系型数据库默认选择的根本原因。2. 再说底层InnoDB里索引到底是怎么存的2.1 聚簇索引主键决定了数据行在磁盘上的物理存放方式InnoDB里表本身就是按照主键索引组织的这个索引叫聚簇索引。聚簇索引的叶子节点里保存的是一整行数据也就是说主键的顺序基本决定了数据行在表空间里的物理存储顺序。这个设计的好处是按主键查询时直接通过B树定位到叶子节点拿到完整记录不需要二次查找。代价则是如果主键不是顺序递增的插入时可能频繁触发页分裂造成数据页碎片和随机IO。所以InnoDB表强烈建议使用自增主键或有序ID不要用随机UUID做主键这是我见过最多人踩的坑之一。还有一点必须记住二级索引的叶子节点不存整行数据存的是索引列的值和主键值。这个设计是为了节省空间但也带来了回表问题。2.2 二级索引与回表为什么有时候查询反而慢假如你在user_id字段上建了一个普通索引那么这棵B树的叶子节点只保存user_id值和对应的主键id。执行一条where user_id 789的查询流程是先到user_id这个二级索引树中定位到记录得到主键id。再用主键id去聚簇索引树中查一次拿到完整行数据。第二步就是回表。如果查询结果集很大比如命中了十万行就意味着要回表十万次。每一次回表都是一次随机IO性能会非常难看。这也是很多开发困惑的地方明明条件上有索引为什么查询还是慢很可能就是回表次数太多。减少回表有两个思路一个是让索引覆盖查询所需要的全部列也就是覆盖索引另一个是尽量避免一次性查太多行比如加limit、改分页策略。理解了回表你就能真正读懂执行计划里的很多细节。2.3 覆盖索引让查询“免回表”覆盖索引并不是一种独立的索引类型而是“索引本身已经包含了查询需要的全部字段”这种情况。举个例子SELECT order_no FROM user_order WHERE user_id 123;如果表上有联合索引(user_id, order_no)那么查询直接从二级索引的叶子节点中拿到order_no根本不用回主键索引捞整行。执行计划里会出现Using index这个词常被称为“索引覆盖”。我优化SQL时会先看select列表里到底需要哪些字段然后用联合索引去尽量包含这些字段。特别是高频查询哪怕多冗余一两个字段到索引里换来的是稳定的毫秒级响应。当然也不能无限加字段索引列越多存储和写入成本越高一般控制在三四个列以内比较合理。2.4 联合索引的最左前缀原则联合索引(a, b, c)会按照a、b、c的顺序构建一棵B树先按a排序a相同再按b排序b相同再按c排序。所以查询条件是否能命中这个索引取决于是否从a开始连续匹配。能用where a 1、where a 1 and b 2、where a 1 and b 2 and c 3。部分能用where a 1 and c 3只能用索引来过滤ac没法直接通过索引过滤。完全不能用where b 1 and c 2因为最左边的a没有条件约束。很多慢查询就是栽在这里。之前见过一个例子开发建了(start_time, end_time, user_id)的联合索引实际却经常单独查user_id结果索引完全用不上。设计联合索引时必须想清楚业务里最常见的查询条件组合把最常出现且需要等值匹配的列放在最前面。3. 动手设计从业务字段到索引落地的完整套路3.1 动手前先盘清楚数据特征建索引之前我先看三样东西数据量、区分度、写入频率。数据量决定到底要不要建索引。几千行的配置表就算全表扫描也很快为了这种表建一堆索引纯属增加负担。区分度决定索引值不值比如sex字段只有“男/女”两种值区分度极低就算建了索引优化器也大概率觉得“走索引还不如全表扫”。写入频率决定能承受多少索引维护成本核心流水表写入量大索引就必须少而精而不是多多益善。区分度可以用这条SQL验证SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM user_order;如果结果接近1说明这个列几乎每条记录都不同很适合做索引。如果结果接近0就要慎重。低区分度字段不是完全不能用而是不能单独放在联合索引的左侧放在后面配合高区分度字段使用效果才更好。3.2 联合索引的列顺序怎么排联合索引顺序没有绝对标准但有几个经验优先级等值查询的列优先。区分度高的列尽量靠前。经常用于排序和分组的列放在后面利用索引顺序避免额外排序。范围查询的列放在最后避免范围条件打断后面列的索引利用。举个例子假设业务查询长这样SELECT * FROM user_order WHERE user_id 123 AND status PAID AND create_time 2024-01-01 AND create_time 2024-02-01 ORDER BY id LIMIT 10;user_id和status都是等值条件应该放在联合索引前面create_time是范围条件放后面。一个比较合理的设计是(user_id, status, create_time)。这样MySQL能先用等值条件精确定位一部分数据再用范围条件扫叶子节点进而避免把整张大表翻一遍。这条SQL比很多人喜欢建的“单列索引各建一个”要高效得多。多个单列索引之间未必能自动组合使用反而可能让优化器做很多无谓的判断。3.3 该建与不该建索引的判断标准我给自己定了几条硬规矩where、order by、group by、join on里出现的字段才有资格进入索引候选。频繁更新的列不要随便建索引更新索引页的代价太高。同一个表上的索引不要重复比如已经有了(a, b)就不要再去建单独的a索引前者已经覆盖了后者的能力。大文本和超长varchar字段不要直接索引可以考虑前缀索引。前缀索引是指只取字符串的前N个字符建索引比如对url或备注字段取前20个字符。它能显著减少索引体积。但要注意前缀索引不能用于覆盖索引也可能影响排序精度。用不用要看业务是否允许近似匹配。3.4 用EXPLAIN验证索引是否真的被用上索引建完之后真正能证明它有效的工具是EXPLAIN。最精简的方法是把要优化的SQL前面加上EXPLAIN然后看几个关键列EXPLAIN SELECT order_no FROM user_order WHERE user_id 123 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 10;执行结果里type字段常见值从好到差大致是const、ref、range、index、ALL。看到ALL基本就是全表扫描是最危险信号看到index表示遍历了整棵索引树虽然比ALL好一点但也不一定高效。key字段表示优化器实际用到的索引名rows是估算扫描行数越小越好Extra里如果出现Using filesort或Using temporary说明排序、分组没能完全利用索引需要重点优化。Using index则表示覆盖索引生效这是最理想的状态之一。我不会只看建了索引就收工而是每次执行EXPLAIN都确认type、rows、Extra三个指标。这套验证流程十分钟不到却能在上线前拦截掉大量慢查询。4. 索引失效的高频场景与慢查询排查4.1 函数包裹和隐式类型转换让索引突然失效索引字段一旦被函数处理B树里存储的原始值就无法直接参与匹配了。比如SELECT * FROM user_order WHERE DATE(create_time) 2024-01-01;这里对create_time调用了DATE函数即使create_time上有索引优化器也很难使用因为它需要把每一行的create_time都计算一遍才能对比。正确写法是范围查询SELECT * FROM user_order WHERE create_time 2024-01-01 AND create_time 2024-01-02;隐式类型转换是更隐蔽的问题。如果order_no是varchar类型查询却用了数字SELECT * FROM user_order WHERE order_no 123456;MySQL会把字符串列转成数字再比较相当于对列做了隐式函数处理同样会导致索引失效。排查时要注意字段类型和参数类型的匹配尽量让SQL里的字面量类型和列类型一致。4.2 LIKE、OR、NOT IN这些写法的使用边界最常见的模糊查询问题就是左前缀通配SELECT * FROM user_order WHERE order_no LIKE %123456%;因为通配符在最前面索引无法按前缀匹配只能全量扫描。但如果通配符只在末尾就是另一个人了SELECT * FROM user_order WHERE order_no LIKE 2024%;这种前缀匹配是可以使用索引的。OR条件要小心。如果OR连接的条件里有一部分没有索引优化器可能选择全表扫描。比如where user_id 1 or status PAID只有user_id有索引时就很难直接用索引优化整个OR。遇到这种情况可以尝试拆成多个查询用union all合并或者确保OR两边的条件都具备合适的索引。NOT IN、!这类否定条件通常也不太适合走索引因为它们要排除的记录太多扫描还更简单。还有一个容易被忽略的点字段允许NULL时索引处理会变得更复杂。is null和is not null在某些条件下也可能放弃索引。设计表结构时尽量给字段设置NOT NULL和默认值既是规范问题也是性能问题。4.3 ORDER BY和GROUP BY同样需要索引配合很多人只关注where条件却忘了排序和分组。order by和group by如果无法利用索引顺序MySQL就得先把结果集放到临时表里排序也就是Extra里的Using filesort。filesort不是不能用但数据量一大内存放不下就会落到磁盘临时文件慢得令人怀疑人生。想让order by走索引最简单的方法是让排序列是联合索引的一部分并且排序方向和索引顺序一致。比如索引是(user_id, create_time)那where user_id 1 order by create_time就是理想状态。反例是where create_time 2024-01-01 order by user_id索引顺序和排序顺序错位性能通常不好。group by其实也类似。分组列在联合索引中靠前使用时可以边扫描边统计避免临时表。如果SQL里出现Using temporary就要回头检查索引设计是否覆盖了分组条件。4.4 一条慢SQL排查的完整链路我在线上排查慢查询时流程基本是固定的从慢查询日志里捞出最耗时的SQL看执行次数和执行时间。用EXPLAIN看type、key、rows、Extra搞清楚当前是扫描全表还是回表太多。确认查询涉及的表结构和索引现状。按业务条件设计联合索引优先覆盖等值字段再考虑排序和范围字段。加上索引后再跑EXPLAIN对比rows和Extra是否有明显改善。如果索引不能解决问题重写SQL改分页方式、减少select的列、拆复杂查询或者把压到单条SQL里的计算移到业务代码里。有一次线上查询优化前执行计划里type是ALLrows显示扫了60多万行原因是where条件的两个字段各建了索引但并没有形成联合覆盖。重建了一个两列联合索引后rows降到几百查询时间从两秒多降到几十毫秒。整个过程花了不到半小时但效果比升配服务器明显得多。4.5 索引问题速查表现象可能原因解决思路查询慢但字段有索引回表次数多用覆盖索引或减少返回列type是ALL条件列无有效索引为查询条件建联合索引Extra出现Using filesort排序未利用索引调整联合索引顺序匹配order byExtra出现Using temporary分组或去重未利用索引把分组列纳入索引前缀索引突然不生效隐式类型转换或函数处理修正SQL避免对列做运算LIKE左侧通配导致不走索引索引无法前缀匹配改造查询或用全文检索方案OR中某条件无索引优化器放弃索引拆SQL或为所有条件合理建索引这张表是我每次给团队做代码审查时都会用的基础检查项。大部分索引问题最后都能归到这几类里。5. 复盘几条值得长期坚持的索引习惯我个人的体会是MySQL索引的最大难点不在“会不会建”而在“能不能稳定地用对”。线上环境里一次紧急变更不可能等你慢慢看所以最好在开发阶段就养成几个习惯。第一个习惯写任何一条稍复杂的SQL写完就顺手EXPLAIN一下看一眼rows和Extra。不要等到上了生产才想起来检查。第二个习惯索引设计跟着核心查询走不要跟着字段走。先把业务最高频的SQL列出来按频率给这些SQL设计联合索引效果远好于给每个字段单独建索引。第三个习惯定期清理冗余索引。同一个字段出现在好几个索引的左侧时通常意味着可以合并。删索引前先用information_schema里的统计信息确认使用频率避免误删。最后再分享一个很小的习惯每次上线前我会让开发和DBA一起过一遍慢查询日志哪怕只是十分钟。这个动作看起来简单其实能发现大量索引设计上的漏网之鱼。很多人觉得索引是DBA的事但实际上SQL是开发写的字段是开发定的索引设计真正的决策者就是开发自己。把这块能力补上线上数据库会省很多心。