我之前遇到一个挺典型的线上问题一张订单表几百万行查询条件里的字段明明都建了索引EXPLAIN 也显示用了索引但接口就是慢。后来仔细一看发现 SQL 写法让索引在优化器眼里等于没有——这让我意识到会建索引和会用索引完全是两回事。MySQL 索引这块很多资料讲的是“怎么建”但真正决定查询性能的往往是“索引在什么条件下能被高效利用、什么条件下会失效、以及底层到底发生了什么”。这篇文章就围绕MySQL 索引的高级使用展开不讲建索引的基础语法重点讲三件事索引的底层数据结构到底如何影响查询路径、哪些场景会让索引失效以及根因是什么、如何利用覆盖索引、索引下推、主键设计、排序优化等手段把索引的价值榨干。适合有 MySQL 基础、想提升 SQL 优化能力、或者正在排查慢查询的开发者。1. 走索引不等于查得快先搞懂 B 树帮你省了什么1.1 一个真实的“无效索引”案例我接手过一个查询接口核心 SQL 大概是这样的SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE status PAID AND created_at 2024-01-01 ORDER BY amount DESC LIMIT 20;表上有一个联合索引idx_user_status_created(user_id, status, created_at)也有单列索引idx_created_at(created_at)。按很多人的理解status和created_at都有索引可依查询应该很快。但实际情况是每次查询都扫了八十多万行耗时接近 3 秒。看 EXPLAIN 输出idtypekeyrowsExtra1indexidx_user_status_created863204Using where; Using filesortkey字段有值但type是index表示全索引扫描相当于把整颗 B 树从上到下完整走了一遍而不是通过树的分支快速定位。为什么因为联合索引的最左列是user_id这条 SQL 里没有user_id条件优化器无法从 B 树根部开始沿着指针定向查找只能把整棵索引树的所有叶子节点翻一遍再逐个过滤status和created_at。这个案例最容易误导人的地方就在于EXPLAIN 里显示key不为空不代表索引被高效利用。type是index还是ref/range差距是几十倍甚至上百倍的。1.2 B 树的三层结构与磁盘 IO 账本要理解走索引为什么快、全索引扫描为什么慢得回到 B 树本身。InnoDB 的页大小默认是 16KBB 树每个节点就是一个页。以 bigint 主键为例非叶子节点里每一条索引记录大概是“键值 子页指针”合计约 13 字节。这样算下来一个非叶子节点大约能存储 16KB / 13 ≈ 1200 个指针三层 B 树根节点 1200 个分支第二层 1200 × 1200 个分支第三层叶子节点能覆盖 1200 × 1200 × 1200 ≈ 17 亿条记录。所以三层 B 树就能支撑十亿级的数据量。查询一条记录时从根节点出发经过 3 次磁盘 IO 就能定位到叶子节点。如果走二级索引再加上一次回表查聚簇索引也就是 4~6 次 IO。对于机械硬盘来说一次随机 IO 大约 10ms6 次也就是 60ms对于 SSD 这个数字更小大概几毫秒。但如果是全索引扫描比如type index数据库会把整棵索引树从根到叶子全部读一遍。几百万行数据的索引树叶子节点可能有几万个页每页都要读一次这就是慢的根源。索引优化的本质就是尽量减少需要访问的页数量把随机 IO 次数压到个位数。1.3 key_len 是判断索引利用率的“照妖镜”很多人看 EXPLAIN 只看type和rows我建议额外关注key_len。它表示查询中实际使用的索引长度字节数通过它可以反推 SQL 真正用了联合索引中的哪几列。以常见的 utf8mb4 字符集为例INT4 字节BIGINT8 字节VARCHAR(n)n × 4 2字节变长字段需要 2 字节记录长度如果字段允许 NULL再加 1 字节CHAR(n)n × 4字节假设索引是idx_user_status_created(user_id INT, status VARCHAR(20), created_at DATETIME)三个字段都是 NOT NULL。那么只用user_idkey_len 4用了user_id和statuskey_len 4 20×4 2 86三个全部用上key_len 86 8 94所以当你看到key_len 4说明联合索引只用了第一列后面的列其实没参与定位。这是排查“索引明明建了但没用全”最直接的手段。2. 索引失效的四种典型场景与根因修正2.1 最左前缀失效与 MySQL 8.0 的跳跃扫描联合索引遵循最左前缀原则索引(a, b, c)可以用于a、ab、abc三种条件组合。但实际开发中SQL 条件往往不是按照索引列顺序写的。-- 索引 idx(a, b, c) SELECT * FROM t WHERE b x AND c y;这条 SQL 没有带a条件按传统逻辑b和c完全用不上这个联合索引。不过在 MySQL 8.0.13 及之后优化器引入了INDEX SKIP SCAN跳跃扫描机制如果联合索引前导列的“不同值个数”不多而后列选择性又高优化器可以自动拆分成多个子查询把前导列的每个值都扫描一遍。EXPLAIN 里会看到type range且Extra中有Using index for skip scan。跳跃扫描并不是所有场景都能触发它依赖两个前提前导列的基数distinct 值数量足够小非前导列的条件能显著过滤数据。我自己的判断标准是前导列不同值小于 100 时可以指望一下跳跃扫描前导列是用户 ID、订单号这种高基数列就别指望了老老实实把条件补全。2.2 隐式类型转换相当于给字段套了函数这是一个高频踩坑点。表里的字段是VARCHAR类型但应用层传来的参数是数字比如SELECT * FROM user WHERE phone 13800138000;phone是VARCHAR(20)MySQL 会把两边都转成浮点数再比较相当于执行了CAST(phone AS float)。一旦索引列被函数包裹B 树上有序排列的字符串就无法按原有顺序参与定位索引自然失效。排查方法很简单看 EXPLAIN 的key是否为NULL或者rows是否接近全表。更严谨的做法是记住规则字段是VARCHAR参数就用字符串字段是数字类型参数就用数字不要用WHERE numeric_col 123这种看似无所谓、实际可能绕远路的写法。类似的还有字符集不一致的问题。比如表字符集是utf8mb4连接字符集是utf8关联查询时 MySQL 也会对列做隐式转换索引失效。跨表 JOIN 时两张表的关联字段类型、字符集、排序规则必须一致这是我从线上教训里总结出来的铁律。2.3 范围查询和 LIKE 前通配为什么会断链对于联合索引(a, b, c)SELECT * FROM t WHERE a 1 AND b 100 AND c 5;a可以用等值定位b可以用范围定位但c 5无法再进一步参与索引定位。为什么因为 B 树上(a, b, c)的排列顺序是先按a再按b最后按c。当b是一个范围时c在索引中的顺序已经不再有序无法继续二分查找只能把b 100范围内的所有记录全部取出来再用c 5逐行过滤。注意这不是“索引失效”只是“索引没有用透”。a和b的过滤能力依然生效只是c变成了普通过滤条件。解决办法是把范围条件放在联合索引的最后一列调整索引顺序为(a, c, b)这样等值条件a、c都能精确定位b作为范围收尾。LIKE 前通配是另一个经典问题SELECT * FROM t WHERE name LIKE %mysql%;因为开头就是%无法确定索引有序链表的起点位置只能全量扫描。如果业务确实需要中间模糊匹配优先考虑全文检索或搜索引擎不要硬扛索引。如果只是后前缀匹配name LIKE mysql%因为前缀确定索引依然可以用。2.4 OR、NOT IN 与优化器的数据分布博弈OR条件的经典误区是只要带 OR索引就废。准确说法是OR 两侧字段都建了索引时MySQL 可能会使用index_merge索引合并优化对多个索引分别扫描再合并结果。SELECT * FROM t WHERE a 1 OR b 2;如果a和b都有单列索引优化器可能同时走两个索引然后取并集回表EXPLAIN 里type index_merge。但这里有个更深的问题OR 本身会显著扩大结果集如果两个条件的选择性都不高优化器大概率直接全表扫描因为“回表 去重”的开销可能大于全表顺序读。NOT IN、NOT EXISTS同样依赖数据分布。如果查询结果集很小优化器仍可能走索引但如果“不等于”的数据占比太高索引也就失去了意义。不要死记“什么写法一定失效”要结合 rows 和实际扫描行数判断。3. 覆盖索引与索引下推一次索引访问解决战斗3.1 回表到底贵在哪InnoDB 的聚簇索引叶子节点存的是整行数据二级索引叶子节点存的是“索引键 主键值”。所以通过二级索引查数据大体分两步在二级索引 B 树上定位到叶子节点拿到主键用主键回到聚簇索引再查一次取回整行记录。第二步就是回表。如果命中的行数很多比如 100 条就要执行 100 次主键查找。虽然聚簇索引的查找很快但次数一多随机 IO 成倍增加。所以优化思路就变成了想办法让查询所需的所有列都已经存在于二级索引中这样连回表都可以省掉。这就是覆盖索引。3.2 构造覆盖索引的套路假设有一个高频查询SELECT user_id, amount FROM orders WHERE status PAID AND created_at 2024-01-01;如果现有索引是(user_id, status, created_at)这个查询其实只用了status和created_at而且amount不在索引里还得回表。但如果把索引调整成(status, created_at, user_id, amount)注意这里user_id和amount是作为“携带列”出现的——它们不参与查询定位只为了让索引覆盖查询需要的所有字段。EXPLAIN 里看到Extra Using index就代表这条 SQL 完全走了覆盖索引没有回表。这是我认为性价比最高的优化手段之一一次索引访问解决全部需求IO 次数直接减半。3.3 ICP 索引下推5.6 之后就该白捡的性能MySQL 5.6 引入了索引下推Index Condition Pushdown这个特性经常被忽略。它的作用简单说在没有 ICP 之前二级索引定位到一批主键后需要先用主键回表再用 WHERE 条件过滤有了 ICP存储引擎在拿到这批主键时会先用索引中包含的列把 WHERE 条件先过滤一遍减少回表次数。比如SELECT * FROM user WHERE age 30 AND name LIKE %张%;索引是(age, name)。age 30能定位name LIKE %张%虽然不能用来定位但name字段存在于索引中存储引擎可以直接在索引层过滤掉大量name不匹配的记录只对少数命中的记录回表。EXPLAIN 里Extra Using index condition就是 ICP 生效的标志。这个优化完全由 MySQL 自动完成但能不能生效取决于 WHERE 条件里的过滤字段是否存在于索引中。所以设计联合索引时除了考虑查询定位还要把“可能用于过滤的字段”也放进索引让它有机会参与 ICP。4. 主键索引和唯一索引一个负责聚簇一个负责约束4.1 聚簇索引的叶子节点就是整行数据主键索引在 InnoDB 里和其他索引有本质区别主键索引的叶子节点存的是完整行记录而不是主键值。这意味着数据本身按照主键顺序物理排列在聚簇索引中。因此主键的选择直接决定数据页的物理写入顺序。如果主键是自增的新插入的行总是追加到当前数据页末尾页利用率高很少触发页分裂如果主键是 UUID 或随机字符串新插入的行会随机落在某个数据页中间导致频繁页分裂、页碎片增加、写放大严重。一个很直观的压测结论同样百万级数据UUID 主键表的写入吞吐量通常只有自增主键表的 50%~70%而且表空间占用更大。业务上最终都要有唯一标识但物理主键和业务唯一键完全可以分开设计。我会用自增主键或雪花 ID 作为聚簇主键把业务单号、订单号这类唯一标识建成唯一索引。4.2 唯一索引的隐藏代价每次写入都要先查重普通二级索引和唯一索引在查询性能上差别不大真正拉开差距的是写入路径。普通二级索引的写入可以借助change buffer当目标数据页不在缓冲池时更新操作可以暂存在内存中的 change buffer 区域后续再合并落盘避免每次写入都触发磁盘 IO。唯一索引则不行。因为插入一条记录前数据库必须立即判断是否违反唯一约束而判断的依据是目标数据页上的现有记录。如果这个页不在内存里就必须先把页读出来检查等于一次随机 IO 无法避免。所以高并发写入场景尽量多用普通索引少建业务上非必要的唯一索引必须保证唯一性的字段如订单号、手机号该建唯一索引还是要建但要把“写入成本上升”纳入容量评估。4.3 用 order_no 还是自增 id 做主键我见过很多新手把订单号设置为主键理由是“业务上订单号天然唯一”。但从存储角度看订单号如果是 varchar(32)每一行二级索引里的主键值也要跟着占 32 字节如果主键是 bigint只占 8 字节。数据量大了之后所有二级索引的体积都会因为“主键值”变大而膨胀占用更多内存和磁盘。而且订单号通常是随机或带序列的写入时容易造成页分裂。我的推荐方案是使用自增 id 或雪花算法生成的全局唯一 id 作为主键订单号加唯一索引两者各司其职。5. 索引怎么喂饱 ORDER BY 和 GROUP BY5.1 filesort 出现时数据库在干什么ORDER BY 排序有两种实现方式利用索引的有序性直接返回或者先把结果集加载到 sort buffer 里做文件排序filesort。后者在数据量大、内存不足时会把中间结果写到磁盘临时文件性能断崖式下降。判断标准很简单EXPLAIN 的Extra中如果出现Using filesort就要警惕。当排序字段恰好是索引列且排序方向与索引顺序一致时数据读出来就是排好序的Using filesort不会出现。5.2 联合索引列顺序与排序的一致性索引(a, b)表示数据先按a排序a相同时再按b排序。所以下面的 SQL 可以完美利用索引SELECT * FROM t WHERE a 1 ORDER BY b;因为a是等值条件b的排序顺序在索引内是确定的。但下面的写法就不行SELECT * FROM t WHERE b 1 ORDER BY a;b不是索引的第一列既不能用最左前缀定位排序也跟索引顺序不一致必然 filesort。另一个容易忽略的点是排序方向索引(a, b)默认都是升序ORDER BY a DESC, b DESC可以走反向扫描但如果写成ORDER BY a DESC, b ASC方向不一致优化器就没法直接复用。5.3 MySQL 8.0 的降序索引和函数索引从 MySQL 8.0 开始支持降序索引和函数索引。降序索引解决的是ORDER BY a DESC, b ASC这类混合排序需求函数索引解决的是条件里带函数的场景CREATE INDEX idx_lower_name ON user((LOWER(name))); SELECT * FROM user WHERE LOWER(name) mysql;以前只能靠改写 SQL 或引入冗余字段现在可以原汁原味建索引。5.4 GROUP BY 避免临时表GROUP BY 本质是先分组再聚合如果分组字段与索引顺序一致MySQL 可以直接基于索引有序性做分组不需要临时表。EXPLAINExtra中出现Using temporary时通常意味着分组字段顺序和索引顺序不一致。比如索引是(status, created_at)GROUP BY status, created_at没问题GROUP BY created_at, status就会触发临时表。6. 加索引前的维护成本账表空间与冗余索引6.1 索引不是免费的它要占真实空间每建一个二级索引就是多一棵 B 树多一份完整的“索引键 主键”数据拷贝。以VARCHAR(32)唯一索引为例utf8mb4 下每条索引记录约32×421135字节1000 万行的表单这个索引就要占 1.3GB 以上还没算 B 树节点的页内部碎片和分裂开销实际占用至少按 1.5 倍估算。索引占空间不只是磁盘问题更关键的是缓冲池buffer pool被多个索引瓜分。数据页加载进内存是按需的索引越多同一片内存要服务的结构越多热数据能被缓存的比例越少。6.2 怎样快速找到冗余索引MySQL 8.0 的sys.schema_redundant_indexes可以直接列出重复索引例如idx_a(a)和idx_a_b(a, b)就属于冗余关系前者能做的后者都能做。对于没有 sys 库的版本可以手动查information_schema.statistics按表分组看索引列前缀是否重叠。我通常还会关注几乎未被使用过的索引从慢查询日志和performance_schema的表 IO 统计里看哪些索引长期没有读操作。它们的存在只会拖慢 INSERT/UPDATE/DELETE该删就删。6.3 我平时加索引的完整流程我的固定流程分五步第一从慢查询日志或监控平台抓到最耗时的 SQL第二用 EXPLAIN 分析当前执行计划记录 type、rows、Extra、key_len第三针对 SQL 条件设计候选索引先看等值条件有哪些再看排序和分组条件最后补覆盖列第四在测试库用真实数据量验证索引效果对比优化前后响应时间第五上线前用optimizer_trace确认优化器确实选择了新索引而不是走了旧索引。SET optimizer_trace enabledon; -- 执行目标 SQL SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;这一步能直接看到优化器在选索引时的成本和放弃旧索引的原因排查问题比单纯看 EXPLAIN 精准得多。加索引本身很简单难的是把“查询条件、索引结构、执行计划”三者对齐。我在实际项目中反复体会到很多线上慢查询不是缺索引而是索引和数据访问路径不匹配看完这篇你应该能自己把 key_len、type、Extra 串起来做一次完整诊断了。