做 MySQL 开发和维护越久越觉得索引是最值得反复钻研的东西。很多人被面试题里的“最左前缀”“回表”“覆盖索引”背麻了结果一看到“where a and b 应该怎么建索引”这种真实问题反而不知道怎么下手。今天这篇文章聚焦 MySQL 索引使用进阶把联合索引设计、排序优化、索引失效排查、主键索引与唯一索引的选择梳理一遍重点讲清楚每个方案背后的判据和实测经验适合已经能写 SQL、但想真正把它优化到线上的同学。1. 面对 a and b 条件索引到底应该怎么建1.1 一条看似简单的 SQL 引发的索引之争先从一个后台订单查询说起。SELECT * FROM orders WHERE user_id 10086 AND status 1;这条 SQL 拿出来问十个人能吵出四种方案给 user_id 建单列索引、给 status 建单列索引、建联合索引(user_id, status)、建联合索引(status, user_id)。光看这两列都是等值条件好像谁在前都一样实际差别很大。先说单列索引的方案。如果只有 user_id 上的索引MySQL 会先在索引里找到所有 user_id 10086 的主键值然后回表拿到整行数据再用 status 1 过滤掉不符合的行。反过来的单列索引也是一样的逻辑不过是从 status 入手。问题在于orders 表里 user_id 命中几千行status 1 可能是百万行无论从哪个单索引入口扫都要带着大量无用数据走一趟回表。这种方案在数据量小时看不出毛病数据量一大就变成慢查询。联合索引能同时利用两个等值条件。(user_id, status)索引内先按 user_id 精确匹配再按 status 精确匹配最终命中范围会被压缩到很小回表次数少Extra 里也不会出现 Using where 之类的二次过滤标记。两个联合索引之间的差异主要体现在极端情况下如果 status 的区分度远低于 user_id把 status 放前面会让索引树的第一层面临更多重复值扫描范围扩大。所以只要两个条件都是等值查询区分度高的字段在左边的联合索引通常是更稳的选择。1.2 联合索引字段顺序的真实判据我在实际项目里总结出的排序列规则比网上口诀更细一点。等值条件放前面区分度高的字段优先放最左。范围条件放最后比如create_time ?这种条件不能放前面否则后面的字段无法用于精确定位。需要参与排序的字段要跟 WHERE 里的等值条件综合考虑尽量让它排在范围字段之后、且能充分利用索引有序性。如果某个字段经常作为独立查询条件出现那么它必须出现在联合索引的最左位置否则这条独立查询就废了。举个例子。业务上一个典型查询是SELECT * FROM payment_log WHERE user_id 12345 AND pay_type 2 AND create_time 2025-01-01 ORDER BY create_time DESC;这里的三个条件里user_id 和 pay_type 是等值create_time 是范围。合理索引应该长这样(user_id, pay_type, create_time)。前两个字段随便交换顺序问题不大但 create_time 必须在最后因为一旦范围条件前面的字段被固定住create_time 就能利用索引完成排序连 filesort 都省了。如果你手滑建成了(user_id, create_time, pay_type)那 pay_type 等于白放在索引里还可能让排序段被范围条件打断。1.3 用 EXPLAIN 和 key_len 验证你的选择光有规则不够MySQL 有太多例外所以每次调整索引我都要求自己跑一遍 EXPLAIN 再上线。EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND status 1;看重点type 字段如果是 ref说明等值匹配走上了二级索引key 字段显示实际用的索引名rows 是优化器预估的扫描行数Extra 里如果出现 Using index condition说明索引下推在起作用条件下推到存储引擎层提前过滤了一部分行这在联合索引缺列时很常见。再看 key_len这个值能精确告诉你索引到底用了哪些列。比如user_id INT UNSIGNED NOT NULL占 4 字节status TINYINT NOT NULL占 1 字节那(user_id, status)索引在两条等值条件下 key_len 应该是 5。如果 EXPLAIN 出来 key_len 只有 4说明只用了 user_id 这一列status 根本没参与索引匹配你的联合索引设计就有问题。对于可空字段长度还要加 1 字节varchar 还要额外加变长长度字节和字符集字节计算比较绕但非常值得花时间弄懂。2. order by 排序场景下的索引设计与优化2.1 filesort 为什么会成为慢查询元凶排序是索引进阶里另一个高频痛点。MySQL 的排序路径总共就两条一条是利用索引天然有序直接返回结果另一条是把数据取出来之后在内存或磁盘里做额外排序也就是 filesort。filesort 不是一定慢但一旦排序数据量超过sort_buffer_sizeMySQL 就要用临时文件做归并排序磁盘读写一参与性能立刻下一个台阶。我在线上见过一条 SQL本身只查 20 条但因为 WHERE 条件筛出 80 万行要排序耗时直接冲到 3 秒以上最后加了一个联合索引就把排序消除了。核心原理是索引本身就是有序的。比如SELECT * FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;如果只有 user_id 单列索引MySQL 得把所有 user_id 10086 的记录全部捞出来按 create_time 排序再取前 20 条。如果索引是(user_id, create_time)那么索引内部已经按 user_id 分组、组内 create_time 有序优化器顺着索引从后往前扫 20 条就完事了Extra 里不会出现 Using filesort。这里有个细节MySQL 5.7 及以前对 ORDER BY 方向的处理不灵活索引默认是升序但你 ORDER BY 降序时它一样能反向扫描实际效果通常不差。但从 8.0 开始支持降序索引(create_time DESC)这样的索引定义被真正实现了。多列排序方向不一致的情况比如ORDER BY a ASC, b DESC在 5.7 里几乎必然 filesort在 8.0 里可以直接建(a ASC, b DESC)索引来避免。2.2 范围条件与排序字段的相爱相杀排序和范围条件放一起最容易翻车我画个典型场景给你看。SELECT * FROM payment_log WHERE user_id 12345 AND create_time 2025-01-01 ORDER BY user_id, create_time;注意这个 ORDER BY第一个字段是 user_id但它已经由 WHERE 等值条件固定住了所以排序等于只对 create_time 有效。这种情况下索引(user_id, create_time)完全可以支撑。如果 WHERE 里的范围条件恰好在排序字段上比如create_time ? ORDER BY create_time这也 OK因为范围条件用的就是这个字段索引范围内仍然有序。真正的问题长这样SELECT * FROM payment_log WHERE user_id 12345 AND create_time 2025-01-01 ORDER BY amount DESC;索引(user_id, create_time, amount)没用因为 WHERE 里对 create_time 做了范围匹配range 匹配后面的 amount 无法继续保序。你只能在这个索引里尽量把 create_time 放在前面等值字段和排序字段之间不能插入范围字段。这种场景想完全消除 filesort 很难替代方案是让过滤出来的数据量尽量小比如把查询改成先只取主键再延迟关联减少参与排序的字段宽度。2.3 深分页问题怎么用索引救回来分页排序是排序场景里最容易被低估的一个。LIMIT 100000, 20这种写法MySQL 会把前 100020 行全部取出来再丢掉前 100000 行越往后翻越慢。我处理过最严重的一次线上报表分页翻到 300 页以后接口超时率直接超过 30%。索引能帮上忙的只有 ORDER BY id 这种纯主键排序但 LIMIT 大偏移的问题依然存在。优化方案有两个方向。第一个是延迟关联。先只查主键因为索引里只加载主键和排序字段占用空间极小然后再回表把需要的行补齐SELECT p.* FROM ( SELECT id FROM payment_log ORDER BY id LIMIT 100000, 20 ) AS tmp JOIN payment_log p ON p.id tmp.id;第二个是基于游标的分页。把上一页最后一条记录的 id 作为查询条件传进来让排序从一开始就跳过前面的数据SELECT * FROM payment_log WHERE id :last_id ORDER BY id LIMIT 20;这两种方式都能让索引物尽其用。游标方案彻底解决了深分页问题缺点是用户不能随意跳页但绝大多数业务场景其实根本不需要跳页。3. 索引失效的 6 个高发场景与排查方法3.1 这些写法会让索引变成摆设索引失效是面试题的重灾区也是线上事故的高发区。我梳理了 6 个最常见的高发场景挨个说清楚原因。第一类是把索引列包进函数里。WHERE DATE(create_time) 2025-01-01你以为走索引实际 MySQL 对每一行都得先算函数再比较索引树的有序性完全失去意义。改成范围写法create_time 2025-01-01 AND create_time 2025-01-02就能用上索引。第二类是隐式类型转换。这个问题隐藏得很深经常是 varchar 字段被拿来跟数字比较。比如手机号字段是 varchar却写成WHERE mobile 13800138000MySQL 会把 mobile 转换成数字再比较索引失效。但反过来WHERE id 10086id 是数字把字符串转成数字比较还是有优化的空间。规则记住一条索引列不要做任何类型转换。第三类是前导模糊匹配。LIKE %abc无法使用索引因为 B 树按前缀有序。但LIKE abc%可以。业务上确实需要后缀匹配我建议考虑全文索引或者额外维护一个倒排表而不是硬扛%xxx查询。第四类是联合索引不满足最左前缀。索引(a, b, c)你查询条件只写了 b 和 c那索引就废了。很多团队给大表加联合索引之前没捋清所有查询模式的公共前缀导致索引利用率极低。第五类是优化器自身选择放弃索引。区分度极低的列比如 status 字段只有 0/1 两种值当你要查的那部分数据占全表的 30% 以上时优化器会认为索引扫描加回表的代价比全表扫描还高直接放弃索引。这不是故障是优化器的正常判断很多时候你硬要让它用索引反而更慢。第六类是!、NOT IN、IS NOT NULL这类否定查询。它们的处理逻辑各不相同不能一概而论。!在 MySQL 8.0 里如果区分度高可能会走 range 扫描区分度低就直接全表。NOT IN同样如此和优化器估算的行数关系很大。处理否定查询最可靠的方法是先 EXPLAIN 再看要不要改写 SQL。3.2 一句 EXPLAIN 搞定定位排查索引失效我从来不看理论推导直接交给 EXPLAIN 说话。EXPLAIN SELECT * FROM users WHERE mobile 13800138000;执行计划里 type 字段如果是 ALL就说明没走索引。继续看 key 字段是不是 NULLExtra 有没有 Using where。一条索引失效的 SQL通常是 type 从 ref 变成 ALLrows 从预期的几百行暴涨到几十万行。日常排查时我会特别关注三列type、rows、Extra。type 的排序从好到坏大致是const eq_ref ref range index ALLall 基本就代表全表扫描。rows 是优化器的预估虽然不一定精确但对比优化前后数量级就能感受到差距。Extra 里出现Using filesort或Using temporary说明 SQL 存在排序或临时表代价要优先优化。3.3 用覆盖索引把回表成本打下来索引失效的另一个反义词是索引利用过头。有些查询明明走了索引但因为查询列不在索引里每命中一行都要回一次聚簇索引这是隐性的性能黑洞。我用一个实际案例说明。订单表查询只关心 user_id 和 status 两个字段SELECT user_id, status, COUNT(*) FROM orders WHERE user_id 10086 GROUP BY status;如果建了(user_id, status)联合索引这个查询的所有数据都能从索引页直接拿到不需要回表Extra 里会显示Using index。覆盖索引是把查询列的叶子节点直接作为数据源回表次数降为零这是成本最低的优化方式。实际开发中覆盖索引要克制。不能为了覆盖一个高频查询就把二三十个列全塞进索引那样索引体积膨胀、写入变慢反而得不偿失。通常只覆盖高频且列数少的查询比如列表页只需要 id、标题、状态这些短字段。4. 主键索引与唯一索引的取舍以及事务、锁对索引的影响4.1 主键索引和唯一索引根本不是一回事很多人面试被问“主键索引和唯一索引的区别”第一反应就是主键不能为空、唯一索引可以为空。这没错但背后的存储差异才是关键。在 InnoDB 里主键索引就是聚簇索引整张表的数据按主键顺序物理存放二级索引的叶子节点存的是主键值。所以主键不只是约束它直接决定了数据在磁盘上的组织方式。一张 InnoDB 表如果没有显式主键MySQL 会找第一个非空唯一索引当主键找不到就生成隐藏的 6 字节 rowid。隐藏主键带来的问题是复制和运维上的不确定性所以我建议每张表都显式指定主键。唯一索引只是普通二级索引加了一层唯一性约束叶子节点照样存主键值。唯一索引可以有多个主键只能有一个。关于唯一索引的 NULL 值这里有个很容易踩坑的认知MySQL 的多个 NULL 不被视为重复所以唯一索引的列上可以插入多个 NULL业务上想“允许一条空记录并且只允许一条”是做不到的需要额外用生成列或者触发器来兜底。从写入性能角度对比。普通索引可以利用 change buffer 做缓冲插入或者更新时如果目标索引页不在内存可以先只更新 change buffer后面再异步合并大幅减少随机 IO。唯一索引不行因为在写入时必须立刻判断唯一性如果索引页不在内存就得先读出来这就把 change buffer 的收益抹掉了。所以业务上如果只是需要“加速查询”而不是“强制唯一”普通索引往往更划算反过来一旦业务要求并发下不能出现重复记录唯一索引能兜底不能因为省一点写入性能而放弃约束。4.2 InnoDB 行锁的背后其实也是索引这个坑我见过太多人踩了。InnoDB 的行锁是建立在索引之上的不是常识里理解的“锁住某一行”。它会让存储引擎根据索引条件找到实际记录再上锁。看这个 UPDATEUPDATE users SET status 1 WHERE mobile 13800138000;如果 mobile 字段上没有索引这条 SQL 在 InnoDB 里会变成全表扫描把所有满足扫描条件的行锁住。在 RR 隔离级别下它可能锁住的不只是目标行还包括扫描过程中经过的所有记录和间隙。这就是为什么一条不带索引的 UPDATE 会在高并发下瞬间拖垮整个业务。我处理过的一个死锁事故就是两个事务各自更新不同业务数据但 WHERE 条件里都有一个不带索引的字段结果互相锁住了对方需要扫描的间隙。排查死锁用SHOW ENGINE INNODB STATUS看到 LOCK WAIT 涉及的行数特别多第一反应就应该是查索引有没有建对。间隙锁也和索引直接相关。RR 隔离级别下范围查询为了防幻读会锁间隙。如果索引区分度低间隙锁的范围会被放大更可能造成锁冲突和死锁。所以一个字段越需要频繁做范围条件越要有合适的高区分度索引来收紧锁范围。4.3 事务隔离级别和索引设计一起考虑事务隔离级别会影响锁范围锁范围又会影响索引的价值。RC 级别下间隙锁基本被禁用并发反而高但部分场景要牺牲一致性RR 级别是 MySQL 默认间隙锁天然存在。从索引角度看RR 级别下涉及范围扫描的 SQL必须评估锁范围是否可控。一个典型优化是如果业务能接受 RC 级别减少间隙锁冲突如果必须 RR就通过索引尽量把扫描范围缩小。两种手段的核心都是索引因为锁范围跟执行计划走的路径强相关执行计划走全表锁范围就是全表执行计划走上精准索引锁范围就非常小。很多团队把事务和索引当成两个互不相关的优化维度其实它们是一体的。调 SQL 的执行计划不只是为了查询快也为了让锁范围小、死锁概率低。5. 一次线上 SQL 优化的完整实操复盘5.1 从慢查询开始定位用一个我优化过的真实案例把前面所有知识点串起来。线上订单表数据量约 5000 万行慢查询日志频繁记录这条 SQLSELECT * FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;原表上只有 user_id 一个单列索引status 也有单列索引。EXPLAIN 表现是优化器选择了 user_id 索引rows 预估 5 万行左右Extra 里同时出现 Using where 和 Using filesort。这说明 status 过滤和排序都是在回表后做的5 万行数据全部捞出来排序再取 20 条性能自然上不去。5.2 联合索引设计与验证过程我提出把索引调整为(user_id, status, create_time)。推导逻辑很简单user_id 和 status 都是等值条件放在前面create_time 是排序字段放在最后。这样索引内先按 user_id 定位到目标用户的订单再按 status 精确过滤最后 create_time 在组内天然有序ORDER BY 直接复用索引顺序。修改之后 EXPLAIN 的变化非常明显type 从 ref 不变但 key 变为新联合索引rows 从 5 万降到几十行Extra 里 Using filesort 消失。实际查询时间从约 1.8 秒降到 20 毫秒以内。这里有个容易搞错的地方有人可能会想我把索引建成了(user_id, create_time, status)这样排序也利用了status 也能过滤是不是一样实际上如果 status 过滤后留下的行数较多create_time 排序还是会在过滤之前进行因为索引顺序是 user_id - create_time - statuscreate_time 在 status 前面无法保证排序完再过滤。所以设计时坚持“等值条件在前、排序字段在后、范围字段最后”这个顺序是对执行路径最明确的掌控。5.3 这次优化踩过的真实坑第一次上线这个索引时我直接在生产库执行了新增索引的 DDL结果压测期间观察到主库 CPU 有明显波动。原因在于大表加索引会消耗大量磁盘 IO虽然 MySQL 5.6 以后支持在线 DDL但数据量大的表加索引仍然有时间和 IO 成本。后来我改成先在从库加、再切换主从的方式影响就小多了。另一个坑是索引命名不统一。之前团队里有人叫 idx_user_id有人叫 index_status查起冗余索引非常费劲。我后来统一用idx_表名_字段名的方式命名方便后续排查重复索引。第三个坑就是加完索引不等于万事大吉。MySQL 优化器有时会因为统计信息偏差放弃刚建好的高区分度索引。我遇到过 new index 建好后EXPLAIN 依旧走老索引最后通过ANALYZE TABLE刷新统计信息才恢复正常。优化完这条 SQL 之后我顺手用performance_schema查了一下同类慢查询模式发现还有几条WHERE user_id ? ORDER BY create_time DESC的查询没建合适的索引属于同一数据分布下的同类问题也一并提前优化了。6. 常见问题速查表与个人经验总结6.1 高频问题与解决建议问题常见原因解决建议where a and b 怎么建索引不清楚联合索引顺序等值条件放前面区分度高的优先范围条件放最后排序很慢缺少覆盖排序字段的索引让排序字段包含在联合索引中且不要被范围条件打断明明有索引却不走类型转换、函数包裹、不满足最左前缀、低区分度用 EXPLAIN 看 type 和 key逐项排除深分页越来越慢LIMIT 大偏移导致扫描大量无用行延迟关联或基于游标分页唯一索引和普通索引怎么选分不清约束和性能边界业务需要唯一就加唯一索引仅加速查询则用普通索引一条 UPDATE 锁了全表WHERE 条件没有索引给 WHERE 里的条件字段加索引收紧锁范围LIKE %xxx 查不动前导模糊无法走索引考虑全文索引或单独维护倒排数据6.2 我给团队定的几条索引军规索引这个事规则越简单越不容易出错。我压箱底的几条原则分享给你。第一线上任何 SQL 上线之前必须经过 EXPLAIN 检查重点看 type、rows、Extra 三列。没有执行计划的优化都是拍脑袋。第二索引不是越多越好。一个表上超过五六个索引往往说明表结构或查询模式没有设计清楚。冗余索引不仅浪费磁盘空间还拖慢 DML。第三给大表加索引前先评估存储空间和 IO 成本。能压到从库执行就先压从库能错峰就错峰不要跟业务高峰期硬碰。第四时刻记住事务和锁是跟着执行计划走的。索引优化能顺手解决一半以上的锁问题比如锁范围大、死锁频繁、并发上不去。第五定期清理慢查询日志对照索引使用情况做 Review。数据分布会变半年后再看同一批索引可能已经出现新的低效执行计划。我个人更习惯把每次索引优化都留一份记录包括当时的 SQL、表数据量、EXPLAIN 结果、索引 DDL 和优化前后的执行时间。等过几个月数据量翻倍再回看往往能发现新的规律。MySQL 索引没有一劳永逸的方案它是一门需要持续跟数据分布对话的技术多花点时间读懂执行计划回报一定远大于盲目堆索引。