做 MySQL 优化的人大概都有过这种经历明明数据量不大SQL 也没写错页面就是慢得像蜗牛。问了一圈最后发现最简单的解决方案往往就是那几个看起来不起眼的索引。我自己在实际项目里排查过很多慢查询十个里面有八个问题都出在索引没建对、没用到或者干脆被写失效了。所以这一章我在前面基础内容之上把索引这块拿出来专门讲透——既说清楚它背后的原理也把覆盖索引、索引下推这些高级玩法梳理一遍最后还会附上我自己踩过的坑以及一套可以直接用的避坑速查表。不管你是刚学 MySQL 的初学者还是已经在业务里写过不少 SQL、想系统提升一下的开发者这篇文章都值得认真过一遍。我会尽量用大白话解释概念再配合实际的 SQL 示例和参数讲解让大家看完就能在自己项目里用起来。1. 先搞清楚索引到底在解决什么问题1.1 索引的本质一本“预排序”的目录很多人把索引理解成书的目录这个类比其实相当准确。没有目录的时候你想在一本书里找到某个关键词只能从第一页翻到最后一页一页一页地找有了目录你直接翻到对应的页码就行。数据库里的全表扫描Full Table Scan就是那个一页一页翻书的过程而索引就是那个提前做好的、按顺序排列的目录。MySQL 里最常见的 InnoDB 引擎索引使用的是 B 树结构。树就好比一个多层级的目录第一层是目录页第二层是子目录页最底下那层是真正的数据页码。每一层的节点都会记录下一层的区间范围这样查找数据时不需要遍历整棵树只需要沿着树的层级逐步缩小范围最后定位到具体的叶子节点。1.2 为什么是 BTree而不是哈希、跳表或者二叉树很多刚接触索引的朋友会问为什么一定用 B 树用 HashMap 不是更快吗这里有一个关键背景——MySQL 的数据是存在磁盘上的而一次磁盘 IO 的花费比内存操作慢好几个数量级。HashMap 在内存里确实能做到近乎 O(1) 的查找但它不支持范围查询也不支持排序操作而业务里的 SQL 往往离不开BETWEEN、、和ORDER BY哈希索引直接歇菜。B 树相比普通二叉树最大的优势是它的“胖”。每个节点可以存储大量子节点指针意味着整棵树的高度很低。常见的 InnoDB 索引树高度只有 3 到 4 层也就是说即使表里有几千万行数据从根节点出发到叶子节点也只需要 3 到 4 次磁盘 IO。而一个高度几十层的二叉树每查一次就是几十次 IO数据库早疯了。对比一下几种常见结构的适用情况结构查找速度范围查询排序能力磁盘友好度数据库使用场景哈希表极快不支持不支持一般Memory 引擎、某些精确等值场景二叉树不确定支持支持差不适用于大数据量表B 树较快支持支持较好老版本 MySQL 相关存储引擎B 树稳定支持支持叶子节点有序链表很好InnoDB、MyISAM 默认方案从表里能直观看到B 树把范围查询、排序和磁盘友好都占齐了这也是它成为关系型数据库主流索引结构的原因。1.3 聚簇索引和二级索引你要懂的一张底层图InnoDB 的数据文件本身就是按主键索引组织的这个索引叫聚簇索引clustered index。聚簇索引的叶子节点直接存的是整行数据。所以如果你给一张表定义了主键那主键索引就是聚簇索引如果你没定义主键InnoDB 也会隐藏地生成一个 6 字节的列来作为隐含主键。除了主键以外的索引统一叫二级索引secondary index也常叫普通索引或辅助索引。二级索引的叶子节点存的并不是完整行数据而是对应的主键值。这里就引出了一个极其关键的流程回表。假设我们有张t_user表主键是id另外在name字段上建了一个普通索引。执行SELECT * FROM t_user WHERE name 张三时MySQL 会先去name索引的 B 树里找到符合条件的叶子节点拿到它的主键值比如 id100然后再用 id100 去聚簇索引的 B 树里查一次才能把完整的数据行读出来。这个第二次查询的过程就叫回表。回表不是必然发生的——只要查的字段都能从二级索引里拿到MySQL 就会选择不回表这就是后面要讲的覆盖索引。很多 SQL 慢慢就慢在一路回表回了几十万行。2. 设计索引前先把这几件事想明白2.1 列区分度不高建了索引也是事倍功半很多新手有个误区以为“给 WHERE 后面的字段建索引查询就一定快”。真实情况是哪怕建了索引优化器也有可能会放弃它尤其是当这个字段的区分度很差时。什么叫区分度简单说就是这列值的种类多不多。把它量化的话可以用这个公式估算COUNT(DISTINCT col) / COUNT(*)结果越接近 1区分度越高。比如一个gender字段只有 0、1、2 三个值区分度是 3/N在十亿行的表里几乎等于 0。如果查询WHERE gender 1优化器算一下发现可能扫出上亿行觉得不如干脆全表扫描这时候索引就直接失效了。实践经验建议建索引的情况不建议建索引的情况字段区分度高如手机号、邮箱、身份证号只有几个固定值的状态字段WHERE 条件中的高频字段几乎不参与查询的字段ORDER BY、GROUP BY 使用到的字段大文本、超长字符串可以用前缀索引后面说JOIN 连接字段写入极其频繁、需要控制索引数量的表2.2 复合索引的最左前缀法则实际业务中很少有查询只用一个条件。比如WHERE status 1 AND create_time 2024-01-01 AND category_id 5。这时候你要考虑的就不是单列索引而是复合索引。复合索引遵循最左前缀法则查询条件里必须从复合索引最左边的列开始匹配索引才会被有效利用。比如我在(a, b, c)三个字段上建了一个复合索引那么WHERE a 1能走索引WHERE a 1 AND b 2能走索引WHERE a 1 AND b 2 AND c 3能走索引WHERE b 2 AND c 3不能走索引因为跳过了最左边的 aWHERE a 1 AND c 3只能部分走索引b 字段没法用。所以设计复合索引时字段顺序极其重要。通常的做法是“等值条件优先范围条件放后面”把区分度最高的字段放在最左边。这样才能让索引最大程度地生效也避免建一堆无用冗余的单列索引。2.3 索引不是免费的礼物不少团队的习惯是看到查询慢就“咔咔”给每个字段都建个索引。这其实是在埋雷。索引是有成本的而且成本是双重性的空间成本每个索引都是一棵独立的 B 树树越庞大占用的磁盘空间越多写入成本每次INSERT、UPDATE、DELETEMySQL 都要同步维护这些索引树索引越多写入越慢。另外还有一块隐形开销容易被忽视查询优化器需要从多个索引里做成本估算。索引过多时优化器算来算去可能反而选出糟糕的执行计划。我见过有的表上堆了十几个索引结果写入时直接卡到崩溃。所以理想状态是用尽量少的索引覆盖尽量多的查询场景必要时通过复合索引一箭多雕。3. 高级运用不只是“建个索引”那么简单3.1 覆盖索引查询效率翻倍的秘密覆盖索引Covering Index指的是一个索引里已经包含了查询所需要的全部字段MySQL 只需要扫描这个索引不需要再回表查聚簇索引。这种情况下Extra 字段往往会显示Using index。先看一个例子。表t_order上有主键id有字段order_no、user_id、amount我们在(user_id, amount)上建了一个复合索引。执行SELECT user_id, amount FROM t_order WHERE user_id 10时因为查询列只有user_id和amount这两个字段都包含在上面的二级索引中MySQL 直接从索引树叶子节点取值返回完全不回表。这就是覆盖索引的价值。实际调优时优先把高频率查询的字段都塞进索引里能有奇效。但要注意覆盖索引会额外增加索引体积所以只建议为高频查询做这种设计不是每个查询都值得。3.2 索引下推ICP把过滤动作提前到索引层索引下推算是我个人比较喜欢的一个优化因为它是 MySQL 5.6 之后引入的、默认开启的自动优化很多人用上了却未必感知到。给一个场景表里有复合索引(name, age)执行SELECT * FROM t_user WHERE name LIKE 张% AND age 20。没有索引下推时MySQL 的行为是先根据name LIKE 张%在索引树里找到所有姓张的记录然后挨个回表把整行数据捞回来再在服务层判断age 20过滤掉不符合的记录。 启用索引下推后MySQL 会在索引遍历的过程中直接把age 20这个条件也一并判断掉。姓张但年龄不等于 20 的记录根本不会回表。索引下推减少的是回表次数也就是减少了大量的随机磁盘 IO。这个优化对范围条件下的复合索引特别有效。你可以在执行计划的 Extra 列里看到Using index condition字样说明 ICP 生效了。3.3 前缀索引大字段也能救如果有一个字段是类似邮箱、URL、长描述这种字符串整个字段建立索引既浪费空间又因为索引体积过大导致树高度增加、查询变慢。这时候可以用前缀索引只对字段的前 N 个字符建立索引。比如邮箱字段email正常邮箱前缀部分区分度已经很高。我们可以先实验出合适的 N 值SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS diff5, COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS diff6, COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS diff7 FROM t_user;算出每个前缀长度的区分度找到一个“区分度提升不明显但长度可以更短”的平衡点。比如 diff6 是 0.90diff7 是 0.91diff8 是 0.92那选 7 或者 8 就比较合理。前缀索引的代价是它不能用做覆盖索引也不能用于ORDER BY排序因为索引中只存了一部分字符。所以设计时要权衡好。4. 索引失效的避坑指南4.1 那些会悄悄毁掉索引的写法索引建好了但 SQL 一写索引就失效了。这个环节我总结了几个高频“凶手”每个都是真实项目里验证过的。第一左侧模糊匹配。LIKE %关键词或者LIKE %关键词%没法走索引因为 B 树是有序的它靠最左边的字符来确定搜索区间。但LIKE 关键词%是可以走索引的。所以能用右边模糊尽量别用左边模糊。第二对索引列做函数运算或者计算。WHERE YEAR(create_time) 2024不会走索引因为它必须在每一行上先执行函数才能判断条件是否成立而WHERE create_time 2024-01-01 AND create_time 2025-01-01就是标准的范围查找可以走索引。第三隐式类型转换。如果字段是 varchar查询时却传了数字MySQL 会在底层把字段转成数字再做比较相当于对索引列加了函数索引自然失效。比如WHERE mobile_no 13800138000如果mobile_no是 varchar 类型就危险了。正确做法是显式传字符串WHERE mobile_no 13800138000。第四复合索引没遵循最左前缀。前面说过第一个条件跳过了最左列整个索引都用不上。第五OR 连接非索引列。WHERE id 1 OR name 张三如果name没有索引数据库可能选择全表扫描来完成这个或逻辑。尽量用UNION ALL拆成两个查询或者给 OR 两边的字段都加索引。第六NOT IN、NOT LIKE。在很多场景下这类否定式条件也会让索引失效能改成NOT EXISTS或者范围匹配效果往往更好。4.2 一条索引失效的真实事故说个我自己在工作里踩过的坑。有一张订单表订单号字段order_no是 varchar 类型也建了唯一索引。有一次排查慢日志发现有个 SQL 平均执行了 300 多毫秒按理说唯一索引等值查询应该在一毫秒以内。后来我把 SQL 拿出来一看SELECT * FROM t_order WHERE order_no 20240815000123;问题就出在等号右边是数字而不是字符串MySQL 对 varchar 字段做了隐式转换。执行计划确认后发现 key 是 NULL走的就是全表扫描。修正成字符串写法之后执行时间立刻掉到了 0.4 毫秒。这个事故给了我一个非常深的印象不要在索引列上做任何破坏原值语义的事情。无论是函数、计算、隐式转换还是字符串拼接都可能让索引秒变废纸。4.3 常见错误写法与正确写法对照错误写法问题改进写法WHERE name LIKE %张左侧模糊导致索引失效WHERE name LIKE 张%WHERE YEAR(create_time) 2024索引列套函数WHERE create_time 2024-01-01 AND create_time 2025-01-01WHERE mobile 13800138000字符串字段隐式转数字WHERE mobile 13800138000WHERE status 0 OR amount 100OR 连接非索引字段拆成两条 SQL 后 UNION或给两个字段都建索引复合索引(a,b,c)条件只有b和c违反最左前缀调整索引列顺序或补上 a 的条件5. 用 EXPLAIN 看穿执行计划5.1 执行计划里到底要看哪几列当 SQL 慢下来第一步永远是分析执行计划。用法很简单在 SELECT 前加 EXPLAIN 关键字EXPLAIN SELECT * FROM t_order WHERE order_no 20240815000123;输出结果里有几个关键列需要重点盯住。type列访问类型从好到差大概是system、const、eq_ref、ref、range、index、ALL。看到ALL就说明是全表扫描这是最大的警报信号ref和range都是比较健康的状态const是主键或唯一索引等值查询时的顶级表现。key列实际用到的索引名称。如果是 NULL说明这条查询没用索引要立刻回头检查 SQL 写法。rows列预估扫描行数。它不是精确值但数量级非常有参考意义。同样的查询条件从rows 200万优化到rows 20性能提升就是实打实的。Extra列这里的信息量很大。看到Using filesort就说明 MySQL 需要在内存或磁盘里额外排序通常是因为ORDER BY的字段没有命中索引应该想办法优化看到Using temporary说明要用临时表常见于 GROUP BY 或去重也是需要警觉的看到Using index是好事用上了覆盖索引看到Using index condition是好事表示索引下推生效。5.2 慢查询日志怎么开生产环境里不可能每一条 SQL 都手动 EXPLAIN更实际的做法是打开慢查询日志让数据库替你筛查。在 MySQL 8.0 里可以这样确认当前的慢日志配置SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log_file;如果慢日志没开用下面这几条把它打开注意 8.0 的long_query_time默认是 10 秒你可以按需调低SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow-query.log;设置完后可以用mysqldumpslow工具做初步汇总分析它能把相似的慢 SQL 按执行次数、总耗时排序快速定位到最值得优化的那批语句mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log如果有条件更推荐用 Percona Toolkit 里的pt-query-digest它输出的报告会更直观能按响应时间比例、访问次数等维度做完整排行。每周看一次慢日志已经能避免很多线上事故了。6. 常见问题快查与经验总结6.1 一问一答式的排查清单问为什么我明明建了索引EXPLAIN 里却不走先看 SQL 有没有违反最左前缀再看索引列有没有被函数或隐式转换包裹然后看字段的区分度是否太低导致优化器认为直接全表扫更划算。排查顺序建议按照这三步来。问IS NULL和IS NOT NULL会走索引吗要看优化器的具体成本估算。nullable 的字段有时IS NULL条件走索引效果不错有时则不行。实践中建议尽量给字段加上NOT NULL DEFAULT约束这能让索引行为更可预测。问为什么ORDER BY很慢优先检查排序字段有没有被索引覆盖并确认ORDER BY字段顺序和复合索引列顺序一致。如果排序字段的索引列已经在 WHERE 条件里用掉了排序时可以用索引序直接输出效率最高。问小表要建索引吗其实没必要纠结。几百行的表全表扫描本来就比走索引快因为优化器自己会判断。建不建索引以 EXPLAIN 的实际结果为准不要为了安全感盲目加索引。6.2 我在实际项目里维护索引的习惯最后聊一点维护层面的个人习惯。索引建完不是一劳永逸的我会每隔一段时间做一次冗余索引清理。最常出现的是已经有了(a, b)复合索引又单独建了一个a的索引。这种就是冗余完全可以让复合索引自己覆盖。清理思路很简单先查出来所有索引定义SHOW INDEX FROM t_user;然后人工判断哪些索引可以被其他复合索引替代。比如你能看到(name, age)复合索引存在时单独的name索引十有八九就是冗余的可以直接DROP INDEX掉。这能让写入更快也减少碎片。另一个建议是新上线一条查询前先在测试环境跑一遍EXPLAIN确认执行计划已经命中预期索引再上生产。这一步看似多花几分钟但能避免无数个线上慢查询事故。我在实际踩过那次隐式转换的坑之后就再也没有跳过这一步。数据库索引这门手艺说到底就是“少回表、少扫描、少排序”。理解清楚 B 树的结构和优化器的判断逻辑真正动手优化时你的思路会清晰很多。希望这篇文章里写到的原理、排查流程和那些经验教训能在你自己的项目里派上用场。