2025 版 mysql索引使用技巧说实话这几年面试的时候被问得最多的问题翻来覆去还是那几个MySQL 索引为什么用 B 树最左前缀原则你理解吗联合索引怎么设计哪些场景会导致索引失效很多同学背得滚瓜烂熟但真到了线上环境面对一张几百万行的表where 后面挂了一长串条件该怎么建索引却完全没头绪。这其实就是典型的理论全会实战全废。这篇文章我想从一线实际运维和优化的角度把 MySQL 索引这几年的实践心得重新梳理一遍重点回答那些网上讲得少、但实战最要命的问题索引设计前要准备什么、联合索引到底怎么排字段顺序、排序和分组怎么蹭索引、哪些坑最容易踩。不管你是刚入门的学生还是已经写了几年 SQL 的老手这篇文章的目标只有一个——读完能直接拿去用用起来能真正把慢查询干掉。1. 内容整体设计与思路拆解1.1 索引设计前先想清楚的三个问题动手建索引之前我强烈建议你先回答三个问题而不是一上来就 CREATE INDEX。这三个问题你回答清楚了索引方案基本就定了一大半。第一个问题是这张表的查询模式是什么。说白了就是你要搞清楚 SQL 到底怎么写的。是等值查询多还是范围查询多有没有 ORDER BY 和 GROUP BY有没有 JOIN这些字段是单独出现还是经常一起出现在 where 条件里我见过太多人建索引的时候完全不看业务 SQL纯粹凭感觉给每个字段都来一个单列索引结果查询优化器经常谁都不选最后全表扫描。第二个问题是这张表的更新频率有多高。索引的本质是用空间换时间但很多人忽略了索引对写入的影响。每建一个索引就等于给这张表多维护一棵 B 树INSERT、UPDATE、DELETE 的时候都要同步维护。如果是一张高频写入的表索引建多了写入性能直接崩给你看。我印象很深的一次生产事故就是给一张每秒写入几千次的日志表加了五个索引结果从库同步延迟从几秒一路涨到几十分钟。第三个问题是区分度够不够。区分度就是某个字段不同值的数量占总行数的比例。性别字段只有两个值区分度极低建索引基本没用因为查询优化器会觉得还不如全表扫。而订单号这种字段几乎每条记录都不一样区分度极高建索引收益就非常大。这其实就是为什么我建议所有人在建索引前先自己对目标字段的区分度有个大概判断。1.2 为什么是 B 树而不是哈希或二叉树这个问题很多文章都在解释但我想换个角度从为什么实际建索引的时候要考虑这些特性来理解。如果你完全不了解底层原理也能用索引但遇到很多疑难问题会非常被动。比如你建了 (a,b) 联合索引查 where b 1索引却不生效你不懂 B 树的最左前缀原理就只能靠背结论。B 树的三个核心特性直接决定了索引怎么设计。第一非叶子节点不存数据只存键值加上页目录结构一个 16KB 的页能装海量键值三层 B 树就够支撑千万行数据这意味着索引查找的 IO 次数极其稳定。第二叶子节点通过双向链表串联形成了天然的有序结构这使得ORDER BY 和范围查询可以顺着链表顺序扫不需要额外的排序操作。第三B 树的插入和删除有分裂和合并机制保证树的平衡性这意味着索引维护成本是可控且可预测的。哈希索引能比 B 树更快地等值查找但它对范围查询无能为力也不支持排序。二叉树在极端情况下会退化成链表磁盘 IO 次数不可控。B 树把等值、范围、排序三种常见查询模式都照顾到了这也是 MySQL 最终选择它的根本原因。理解了这些你就能明白一个非常核心的结论索引设计的第一原则是让查询条件尽可能命中最左前缀同时利用索引天然的有序性来消除额外的排序和回表。后面的所有建索引技巧本质上都是围绕这句话展开的。2. 核心细节解析与实操要点2.1 联合索引的字段顺序到底怎么排这个问题网上讨论特别多但讲清楚的人真不多。我先直接给结论再解释为什么。联合索引 (a,b,c) 在 B 树里的排序规则是先按 a 排a 相同再按 b 排b 相同再按 c 排。这就导致一个查询要命中这个索引必须从 a 开始连续匹配不能跳过中间的字段。这就是最左前缀原则。基于这个机制字段顺序的排列规则可以归纳为以下三条。第一条区分度高的字段放在最左边。因为联合索引在 B 树里是按顺序逐级排序的最左边的字段决定了第一层分支的区分能力。区分度高的字段前置能更快缩小扫描范围。比如用户ID和订单状态通常用户ID前置。第二条等值条件放前面范围条件放后面。为什么因为联合索引一旦遇到范围条件比如 、、BETWEEN后续字段就无法用于索引定位了只能向前扫描。等值条件不会中断索引的连续性所以先等值后范围能让前面多个等值字段都参与索引定位效率最高。第三条需要排序的字段放在合适位置。联合索引的天然顺序可以替代 ORDER BY 的排序操作但前提是你的排序字段必须符合索引的顺序方向。比如索引是 (a,b)ORDER BY a,b 直接秒杀ORDER BY b,a 就完全用不上。我举个例子说明。假设订单表经常执行这样一条SQLSELECT * FROM orders WHERE user_id 123 AND status 1 AND created_at BETWEEN 2025-01-01 AND 2025-01-31 ORDER BY id;这个查询有三个等值/范围条件和排序需求。基于上面的原则user_id 和 status 都是等值条件created_at 是范围条件所以联合索引应该设计成 (user_id, status, created_at)。至于 id它本来就是主键B 树的叶子节点上已经带着主键值不需要专门为它加索引。注意把范围条件字段放在联合索引的最后能最大程度利用索引定位同时避免一个范围条件把后面所有字段都废掉的情况。你只要记住等值条件随便排范围条件尽量靠后即可。2.2 覆盖索引让查询不再回表回表是 InnoDB 里一个非常关键的概念。InnoDB 的主键索引聚簇索引的叶子节点存的是整行数据而二级索引的叶子节点只存索引字段和主键值。所以如果你用二级索引查先找到主键值再用主键去聚簇索引里找整行数据。这第二次查找就是回表。回表本身不慢但如果命中的行数特别多比如几千上万行那性能就非常难看了。而覆盖索引的思路是让查询所需的字段全部包含在索引中这样直接从二级索引里就能拿到所有数据根本不需要回表。举个例子SELECT order_no, user_id FROM orders WHERE user_id 123;如果只建了一个 user_id 的单列索引那么查出来的每一条记录都要回表去取 order_no。但如果建的是 (user_id, order_no) 联合索引order_no 就在索引叶子节点上查询完直接返回回表彻底消失。从执行计划上看Extra 字段如果是 Using index就说明走了覆盖索引。如果是 Using index condition说明走了索引下推但没有完全覆盖。如果是 Using where通常意味着回表后还有过滤。这个技巧在实际优化中出镜率极高。我经常跟团队说做查询优化的时候不要光盯着 where 条件SELECT 后面的字段列表同样值得注意。把高频查询的返回字段一起纳入索引设计往往能带来意想不到的收益。2.3 单列索引和条件组合的博弈很多新手会有个疑问既然联合索引这么强大那单列索引是不是完全没有存在价值也不是。单列索引的价值在于灵活和低成本。对于一张查询模式非常多样化的表比如电商的商品表用户可能按品牌查、按价格查、按分类查也可能任意两个条件组合查。这种情况下如果你把每种组合都建一个联合索引索引数量会爆炸式增长写放大严重。这时候更务实的方案是给高频独立条件建单列索引让优化器自己去 index merge 或者选择最优路径。MySQL 5.6 之后引入了 Index Merge 优化优化器可以把多个单列索引的扫描结果做交集或并集合并。比如 where keyboard xxx OR type yyy如果两个字段都有单列索引优化器可能分别扫两个索引再合并。但这里有个大坑Index Merge 未必比一个联合索引快因为它要扫描多个索引还要做归并开销并不小。所以对于高频组合查询比如 where a 1 AND b 2依然应该建联合索引 (a,b)而不是依赖两个单列索引的合并。我自己的经验判断是单列索引适合查询条件组合多但每种组合频率不高的场景联合索引适合某一组条件经常固定一起出现的场景。两者不冲突关键看业务实际怎么查。3. 实操过程与核心环节实现3.1 用 EXPLAIN 验证索引是否真正生效很多同学建完索引之后最常问的问题是怎么知道索引到底有没有生效其实方法极其简单就是看执行计划。执行计划就是 SQL 语句执行前的作战方案MySQL 优化器会告诉你它打算怎么查走哪个索引、扫多少行、需不需要排序、会不会回表。在 SQL 前面加上 EXPLAIN 关键字就能看到。EXPLAIN SELECT order_no, user_id FROM orders WHERE user_id 123 AND status 1;执行结果里我让所有开发者必须学会看以下四个字段。字段含义我重点关注什么type访问类型从好到差依次是 system、const、eq_ref、ref、range、index、ALL见到 ALL 就说明全表扫描基本要优化key实际使用的索引看是否用上了我们建的索引如果为 NULL 说明没用上rows预估扫描的行数数值越小越好如果 rows 接近表总行数说明索引基本没起作用Extra额外信息Using filesort 意味着要额外排序Using temporary 意味着临时表Using index 是覆盖索引这些都要特别关注比如 EXPLAIN 结果里出现 Using filesort说明 ORDER BY 没走到索引查询需要额外做一次排序操作。数据量小还好数据量大时这个排序的代价可能比查询本身还高这时候就该考虑把排序字段加入索引。还有一种情况是 type 是 range说明走了范围扫描索引确实生效了但如果返回行数特别多回表代价也会很大。这时候需要结合 rows 字段判断如果 rows 已经是几十万行说明这个索引虽然生效了区分度不够整体性能仍然可能不理想。提示EXPLAIN 只是预估不是实际执行结果。MySQL 8.0 提供了 EXPLAIN ANALYZE会真正执行 SQL 并输出每个步骤的实际耗时遇到预估和实际差异大的情况果断用起来。3.2 where a and b 组合条件的索引设计实操回到热搜词里那个高频问题where 条件是一个 AND b应该怎么建索引我用一张用户表来演示完整的实操过程。假设表结构是CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50), city VARCHAR(50), age INT, created_at DATETIME, KEY idx_city_age (city, age) ) ENGINEInnoDB;这里我建了 (city, age) 联合索引理由就是前面说的两条原则city 和 age 都是等值条件不是范围city 的区分度虽然不算超高但相对稳定age 放后边做二次过滤。现在来验证效果EXPLAIN SELECT * FROM users WHERE city 杭州 AND age 25;执行计划会显示 key 是 idx_city_agetype 是 refrows 大幅减少说明索引生效。因为两个条件都是等值联合索引能精确定位到一个很小的范围。再看一个变种如果条件变成EXPLAIN SELECT * FROM users WHERE age 25 AND city 杭州;注意SQL 里条件顺序不影响索引。优化器会做条件重排所以这个查询一样能命中 idx_city_age。很多新手以为 where 条件顺序和索引字段顺序必须一致其实完全不是这么回事优化器不是傻子它会自动把条件调整到和索引匹配的姿势。再来看一个反面教材。如果表上只有一个 age 单列索引没有 city 索引那么EXPLAIN SELECT * FROM users WHERE city 杭州 AND age 25;执行计划大概率走上 ALL 全表扫描或者 age 索引后回表再过滤。原因是单列索引无法同时利用两个条件精确定位最后还是得看优化器的判断。这就是为什么 where 里经常一起出现的字段要优先考虑联合索引而不是各建各的。3.3 排序和分组场景怎么蹭索引ORDER BY 是慢查询的重灾区因为排序是一个非常昂贵的操作。当数据量大时MySQL 会把数据加载到内存或磁盘上进行 filesort代价极高。但如果你能让排序字段直接走索引filesort 就直接消失了。什么情况下 ORDER BY 能走索引核心就一条排序字段的顺序和方向必须和索引列完全一致不能跳列。举个例子索引是 (a,b)下面这些情况走索引不额外排序ORDER BY a用最左第一列排序OKORDER BY a, b按索引顺序排OKORDER BY a DESC, b DESC方向一致且从前往后也可能走索引优化器会倒序扫这些情况不走索引需要额外排序ORDER BY b跳过了 a违反最左前缀ORDER BY b, a顺序反了ORDER BY a DESC, b ASC方向不一致GROUP BY 和 ORDER BY 底层排序逻辑很像。GROUP BY 也是先分组再聚合如果能走索引优化器就直接按索引顺序扫描天然分好组性能提升非常明显。不过要特别注意GROUP BY 有时候会产生临时表如果 Extra 显示 Using temporary说明你要小心翼翼地优化了。比如索引是 (a,b)执行 GROUP BY a, c其中 c 不在索引里MySQL 就需要临时表来分组这个代价不小。还有一个小细节在 InnoDB 里空字符串和 NULL 在索引中的行为不同排序时 NULL 默认排在前面。如果你的业务排序要求 NULL 排最后可能需要特殊处理但这种场景一般不强求走索引直接 filesort 反而更简单。4. 常见问题与排查技巧实录4.1 索引用不上先看这五个经典场景下面这五个场景是实战中索引明明建了但就是不生效的五大元凶挨个对照排查基本能解决 90% 的问题。第一个是对索引列做了运算或函数操作。比如SELECT * FROM users WHERE YEAR(created_at) 2025;YEAR() 函数套在 created_at 上索引直接失效因为索引里存的是原始时间值不是年份值优化器没法做匹配。正确写法是范围条件SELECT * FROM users WHERE created_at 2025-01-01 AND created_at 2026-01-01;第二个是隐式类型转换。比如手机号字段在表里是 varchar你查询的时候写了数字SELECT * FROM users WHERE phone 13800138000;MySQL 会把 phone 转成数字再比较索引就用不上了。解决办法是查询时用字符串形式匹配SELECT * FROM users WHERE phone 13800138000;第三个是LIKE 前缀模糊查询。最左前缀原则决定了 abc% 能走索引但 %abc 和 %abc% 走不了。原因是 B 树按前缀有序但无法从中间字符开始匹配。遇到这种需求数据量大的话考虑全文索引或 Elasticsearch。第四个是联合索引没遵守最左前缀。比如索引是 (a,b)你直接查 where b 1索引肯定用不上。这没什么好说的只能靠改查询或调整索引字段顺序来适配。第五个是优化器判断走索引不如全表扫描。这是最容易被忽略的情况。当你要查的行数占总行数的比例很大时一般超过 20%~30%优化器会认为回表代价比全表扫描更大索性放弃索引。比如字段区分度太低或者范围条件太大都会触发这个判断。注意还有隐式字符集不一致的问题。两张表 JOIN 时如果两个表的关联字段字符集不同比如 utf8mb4 和 latin1MySQL 需要做转换索引可能无法使用。建表时统一字符集能避免很多隐蔽问题。4.2 慢查询日志的打开姿势线上排查慢查询第一步永远是找到那些拖后腿的 SQL。MySQL 的慢查询日志就是干这个的。从 MySQL 5.7 开始慢查询日志默认关闭需要手动设置。可以在配置文件里写也可以动态设置SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;第一行开启慢查询日志第二行把阈值设成 1 秒超过 1 秒的 SQL 就会被记录第三行把没有用索引的查询也记进日志。这三条命令对排查最有用尤其是第三条能帮你快速发现业务里那些完全没走索引的查询。日志确定之后用 mysqldumpslow 工具可以做汇总分析按照执行次数、耗时排序快速找到最高频的慢查询。我在实际工作中通常按总耗时排序因为它等于 执行次数 × 平均耗时总耗时高的 SQL 才是真正影响系统整体性能的元凶。单次虽然不慢但被调用几千次积少成多一样会拖垮系统。4.3 索引维护碎片和冗余的清理索引不是建完就一劳永逸的它是需要维护的。两个最常见的维护场景是碎片清理和冗余索引排查。先看碎片。B 树在频繁的 INSERT 和 DELETE 后页会出现碎片索引变得不紧凑IO 效率下降。碎片严重时即使索引逻辑没变查询性能也可能明显退化。处理方式是执行 OPTIMIZE TABLEOPTIMIZE TABLE users;这个命令会重建表整理数据和索引把碎片收拢。但注意它会锁表大数据量下要挑业务低峰期执行。MySQL 5.7 之后的版本可以在线执行但仍然会占用不少 IO 和临时空间千万别在产品高峰期乱跑。再看冗余索引。很多情况下(a,b) 联合索引已经覆盖了 a 单列索引的功能但如果你之前给 a 单独建过索引那索引就冗余了。冗余索引浪费存储空间还在每次写入时增加额外维护成本。排查方法也很简单SELECT * FROM sys.schema_redundant_indexes;MySQL 5.7 的系统库 sys 自带这个视图直接列出所有冗余索引照着删就完事了。删索引之前要仔细核对确认没有其他 SQL 依赖那个单列索引。5. 工具选型与优化思路延伸5.1 执行计划和 EXPLAIN ANALYZE 的实战对比传统 EXPLAIN 是预估EXPLAIN ANALYZE 是实测这两者的区别意味着什么我举个例子你就懂了。比如一个查询EXPLAIN 预估扫描 100 行实际执行可能扫了 10 万行因为统计信息没更新或者数据分布产生了偏移。如果你只看预估会被表象误导以为索引没问题实际上查询已经慢得离谱了。EXPLAIN ANALYZE 会真正执行 SQL把每一步操作的实际耗时、实际行数都打印出来格式是树状的EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 123 AND status 1;输出里每行都带着实际耗时和实际行数你可以精确看到瓶颈在哪一步。是索引查找慢还是回表慢还是排序慢一目了然。有一个细节要注意EXPLAIN ANALYZE 是真正执行了查询所以会真实地消耗资源也会返回数据。对于只读查询没问题但对于修改操作可以对 SELECT 之外的部分语句做 ANALYZE一定要谨慎别把线上的 UPDATE 真跑一遍。5.2 索引下推和索引跳跃扫描这两个特性了解的人相对少但实战价值很高。索引下推Index Condition PushdownICP是 MySQL 5.6 引入的。简单说以前处理 where 条件的时候先把索引命中的记录都回表取出来再一条条过滤。有了 ICP 之后MySQL 会在索引层就先过滤掉不满足条件的记录减少回表次数。执行计划里 Extra 出现 Using index condition 就说明 ICP 生效了。索引跳跃扫描Index Skip Scan是 MySQL 8.0 引入的优化适用于联合索引最左列区分度低、中间列或后续列查询频率高的场景。比如索引是 (gender, age)查询 where age 25优化器可以跳过gender 直接扫描 age。但注意这个特性有前提条件不是所有查询都能用。我在实践中发现它适用于最左列只有很少几个不同值的情况如果最左列区分度高跳跃扫描的无功扫描反而更慢。这两个特性说明MySQL 的索引优化能力一直在进化。但这不意味着你可以随便建索引优化器再智能也要有合适的索引可以选。6. 场景速查表与索引设计决策总结结合所有思路和实操我把最常用的索引设计判断浓缩成一张速查表。平时写 SQL 或者 review 别人建的索引时对照这张表过一遍大方向基本不会跑偏。业务场景推荐的索引设计策略设计背后的核心原因where a 1 AND b 2联合索引 (a, b)等值条件连续匹配精确定位到最小范围where a 1 AND b 10联合索引 (a, b)a 前置等值条件参与索引定位范围条件做边界扫描where a 1 ORDER BY b联合索引 (a, b)索引天然有序直接消除 filesortSELECT a, b WHERE a 1联合索引 (a, b) 或覆盖索引查询字段全部在索引中不需要回表多个独立条件随机组合高频条件分别建单列索引让优化器灵活选择或 index merge避免索引爆炸低区分度字段如性别、状态不单独建索引回表代价大于全表扫描走索引反而更慢高频率 UPDATE 的表只保留必要索引尽量合并每个索引都是额外的 B 树维护成本写放大严重like abc% 查询普通索引前缀匹配符合 B 树有序性like %abc% 查询全文索引或搜索引擎中间匹配无法走 B 树索引只能扫描这张表背后的逻辑很集中索引设计永远围绕减少扫描范围、消除回表、消除排序这三个目标展开。任何一个索引方案你都可以拿这三个标准来检验它是不是最优解。7. 写在最后一次真实调优的回顾我在实际工作中最常说的一句话是先看执行计划再谈索引设计。分享一个我印象很深的案例。一张订单表有 800 万行查询条件是 WHERE status 1 AND created_at BETWEEN 某天 AND 某天原始 SQL 跑了 2.3 秒。加了 (status, created_at) 联合索引后降到 40 毫秒但执行计划仍然显示有回表。我又把索引改成 (status, created_at, order_no) 覆盖索引彻底消除了回表耗时稳定在 12 毫秒左右。这个案例的核心其实不是联合索引和覆盖索引本身多神奇而是每一步优化都建立在执行计划反馈的基础之上。加索引之前先看 EXPLAIN 的 type、rows、Extra 三个字段加索引之后再对比一次用数据说话而不是凭感觉优化。最后补一个小技巧MySQL 8.0 的 EXPLAIN ANALYZE 比传统 EXPLAIN 更直观可以直接看到实际耗时。遇到慢 SQL先开慢查询日志拿到 SQL跑一遍 EXPLAIN ANALYZE锁定瓶颈再动手改索引或 SQL最后回归验证。这套流程我已经用了好几年大概率你也会觉得好用。