做了几年后端MySQL 相关的性能问题里我印象最深的永远是索引。不管你是刚把 MySQL 装好、准备建第一张业务表还是已经在线上被慢查询折磨过几轮索引都是绕不过去的一关。联合索引、覆盖索引、索引失效这些进阶话题几乎每次面试都会问每套线上系统的性能优化也都跟它们直接相关。今天这篇我把自己在实际项目中验证过的索引原理、设计方法和排查经验完整梳理一遍不写教科书腔尽量全是能直接拿去用的东西。1. 索引到底在解决什么问题1.1 没有索引时SQL是怎么执行的先把底子打好。MySQL 默认的存储引擎是 InnoDB数据并不是乱糟糟地摊在一个大文件里而是按页Page来组织默认一页 16KB。InnoDB 在做查询时会先把目标数据所在的页加载进内存再在页内部逐行匹配。问题来了你怎么知道目标数据在哪个页没有索引的情况下MySQL 只能老老实实从第一个数据页开始一页一页往后翻把整张表所有页都扫一遍逐行判断 WHERE 条件是否满足这就是全表扫描执行计划里的 type 会显示为 ALL。这个过程特别像查一本没有目录的字典你想找“索引”这个词只能从第 1 页翻到最后一页翻到最后一页才看到非常费劲。全表扫描倒也不是一无是处。表特别小的时候它反而是最优策略省掉了索引查找的随机 IO。小表随便扫速度比走索引还快。但数据量一旦上来比如百万行、千万行全表扫描的代价就非常可观一次查询要读几千个数据页磁盘和 CPU 全被拖垮接口延迟直接飙红。这也就是为什么我们常说“SQL 慢十有八九是没走对索引”。1.2 索引的本质额外维护一份“目录”既然直接在数据页上翻阅太慢那就给数据加一份“目录”这就是索引。索引是独立于原始数据之外的一种额外结构它提取出某些列的值并和对应的数据位置在 InnoDB 里通常就是主键值建立映射再按照一定的顺序排列让查询可以快速定位而不必翻遍整张表。为什么不用改原表因为业务表的数据是不断增删改的行本身在磁盘上也没有固定顺序。如果为了加速查询直接在数据页上维护一个有序查找结构那每次 INSERT 和 UPDATE 都要去动数据页的物理布局写入性能会被严重拖累。所以 InnoDB 选择在表之外另建一套有序结构专门服务查询——这就是索引。本质上索引是在用额外的磁盘空间和写入开销换取查询速度的大幅提升典型的空间换时间。1.3 为什么数据库最终选了B树索引可以有很多数据结构实现MySQL 里最常见的 InnoDB 索引用的是 B 树。为什么不是哈希表不是红黑树哈希表的等值查询确实快O(1) 就能定位但它有两个硬伤第一不支持范围查询比如WHERE age 20这种条件哈希结构只能全表扫第二哈希值是无序的无法利用索引排序ORDER BY也帮不上忙。业务 SQL 里最常用的就是范围查询和排序哈希直接出局。再看二叉树和红黑树。二叉树在极端情况下会退化成链表查找效率变成 O(n)红黑树虽然能保持平衡树的高度依然随数据量增长。数据量到百万、千万级时红黑树的高度会很高而数据库的查询瓶颈是磁盘 IO——树每多一层就可能多一次磁盘读取。这一层层的代价在线上的延迟里非常明显。B 树专门为磁盘场景设计它有几个关键特性非叶子节点只存索引键值和指向子节点的指针不存真实数据所以一个页能放下非常多的键值树的高度被压得很低。叶子节点才存数据在 InnoDB 里主键索引的叶子节点存整行数据二级索引的叶子节点存主键值并且叶子节点之间用链表串联范围查询走链表非常高效。树的高度通常只有 3 到 4 层也就是说哪怕几千万行的表从根节点定位到叶子节点最多也就 3 到 4 次磁盘 IO。我经常拿一个估算来向别人说明 B 树的厉害假设主键是 bigint占 8 字节指针占 6 字节一个 16KB 的页大约能存 1170 个索引项假设一行数据约 1KB一个叶子页能放约 16 行。一个三层 B 树大概能存 1170 × 1170 × 16差不多 2190 万行。也就是说两千万行的表从根节点到叶子节点只需要 3 次 IO。这个结论第一次听确实反直觉但这就是 B 树成为数据库索引主流选择的根本原因。注意以上是粗略估算实际页利用率、记录大小都会影响具体值但“B树层数少、磁盘IO稳定”这个结论是可靠的。2. 索引类型与适用场景全拆解2.1 聚簇索引、二级索引与主键的关系InnoDB 里有一对非常基础的概念聚簇索引Clustered Index和二级索引Secondary Index。主键索引就是聚簇索引它的叶子节点直接保存整行数据。所以一张 InnoDB 表只能有一个聚簇索引因为数据行不可能按两种顺序物理存储。如果你建表时没有显式指定主键InnoDB 会找一个非空的唯一列来当主键如果也没有它就会隐式生成一个 6 字节的 rowid 作为聚簇索引。这也是我反复建议大家一定要主动设计主键的原因——让数据库隐式生成的主键不受你控制后续很多操作会很被动。二级索引也叫非聚簇索引是你在业务表上额外创建的普通索引。它的叶子节点不存整行数据而是存索引列的值再加上主键值。当我们通过二级索引查数据时会先在二级索引的 B 树里找到对应主键再拿着这个主键回到聚簇索引里查整行数据这个动作就叫“回表”。回表是有代价的它相当于一次额外的主键查询。这也是为什么有些查询即使走了索引依然觉得不够快。理解了回表你就能理解覆盖索引为什么那么重要下面会细说。2.2 联合索引与最左前缀原则联合索引是 MySQL 进阶里最常考的内容之一。所谓联合索引就是在一张表上把多个列合起来建一个索引比如CREATE INDEX idx_a_b_c ON t(a, b, c)。很多人以为建了联合索引查询的时候只要条件里带上了这三个字段就能用。实际上不是。联合索引在 B 树里的排序规则是先按第一个字段 a 排序a 相同的情况下再按 b 排序b 也相同的情况下再按 c 排序。这就导致了一个非常核心的规则最左前缀原则。最左前缀原则的意思是查询条件里的等值或范围条件必须从联合索引最左边的列开始连续命中索引才能被高效使用。举例来说索引(a, b, c)WHERE a 1能用索引。WHERE a 1 AND b 2能用索引。WHERE a 1 AND b 2 AND c 3能用索引覆盖率最高。WHERE a 1 AND c 3能用到 a但 c 无法直接利用索引做精确定位。WHERE b 2直接用不到这个索引因为 b 在联合索引里不是最左列全局是无序的。WHERE c 3同理用不到。为什么单独查 b 用不上你可以把联合索引想象成一本电话簿先按姓排序、再按名排序。如果你只知道某个人的名而不知道姓那这套按“姓名”排序的目录就帮不上忙因为“名”在整个电话簿里是乱序的。这个类比能帮你记住最左前缀原则。2.3 覆盖索引让查询不“回表”前面说了二级索引的叶子节点存的是主键值。假设有这样一个表CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, age int DEFAULT NULL, phone varchar(20) DEFAULT NULL, PRIMARY KEY (id), KEY idx_name_age (name, age) ) ENGINEInnoDB;执行下面这条 SQLSELECT name, age FROM user WHERE name 张三;MySQL 会去idx_name_age这个索引里找name 张三的记录然后发现要查询的列name和age都整整齐齐地放在索引的叶子节点上根本不需要回表直接就能返回。执行计划里的 Extra 会显示Using index这就是覆盖索引。但如果是SELECT id, phone FROM user WHERE name 张三phone字段不在索引里二级索引的叶子节点上没有MySQL 就必须拿着主键 id 回表去聚簇索引里查 phoneExtra 里看不到Using index而会出现Using where之类的标识。覆盖索引是优化高频查询的利器。很多慢查询其实不需要动表结构只需要把单列索引改成多列联合索引让查询涉及的所有列都覆盖在索引里就能把回表次数直接降到 0。这也是为什么“根据 SQL 来设计索引”这句话如此重要——索引是为了喂饱你的业务查询而不是为了建索引而建索引。2.4 索引下推一个容易被忽略的加速器索引下推Index Condition PushdownICP是 MySQL 5.6 引入的一个优化很多人没注意到。它的作用是在存储引擎层遍历索引时提前把可以在索引层面判断的条件过滤掉减少回表次数。举个例子。有联合索引(name, age)查询SELECT * FROM user WHERE name LIKE 张% AND age 20;在没有索引下推的情况下MySQL 会先在索引里把所有name LIKE 张%的记录找出来然后一条条回表读取完整行数据再在 Server 层判断age 20。假设张%命中了 1000 条记录就要回表 1000 次最后可能只剩 50 条满足条件。开了索引下推后存储引擎在遍历索引时就会先把age 20这个条件也带上索引叶子节点上本来就有 age 字段可以直接判断只有满足条件的 50 条才回表。回表次数从 1000 次降到 50 次性能提升非常明显。执行计划里如果出现Using index condition就说明 ICP 生效了。这个优化对联合索引意义特别大。它意味着即使查询条件没有完全遵循最左前缀原则只要有一部分条件能在索引里过滤也能减少大量无效回表。所以说现在的 MySQL 优化器比很多人想象中要聪明我们在建索引时可以把“哪些条件最有利于在索引里过滤”作为排序依据之一。3. 实操用explain定位索引问题3.1 explain关键字段怎么读理论说了半天到了实战环节第一步永远是认识执行计划。MySQL 提供了 explain 命令用法非常简单EXPLAIN SELECT * FROM orders WHERE user_id 1001;它会返回一行执行计划关键字段我整理成了表格字段含义关注点type访问类型从好到坏system const eq_ref ref range index ALL。看到 ALL 就要警惕key实际使用的索引如果为 NULL说明这条 SQL 没用上任何索引rows预估需要扫描的行数越小越好能直观反映这次查询的“体力活”有多少filtered存储引擎返回数据在 Server 层再过滤的比例百分比越高越好Extra补充信息重点关注 Using filesort、Using temporary、Using index、Using index conditiontype 字段是最直观的警报器。ALL是全表扫描index是扫描整棵索引树虽然比ALL好一点通常也是一种近乎全量的扫描。range代表范围扫描算是比较健康的级别比如WHERE id 100。ref表示走的是普通索引的等值查询非常理想。const则是通过主键或唯一索引等值查询性能最好。Extra 里只要出现Using filesort或者Using temporary大概率就是排序或分组没有用好索引这类 SQL 在数据量一大后会非常拖沓。如果看到Using index说明覆盖索引生效值得庆幸看到Using index condition说明索引下推在帮忙。3.2 一个慢查询优化案例分析直接上一个我简化过的真实场景。有一张订单表 orders已经有两百多万行数据。业务上有这样一个高频查询查某个用户最近一段时间的订单。SELECT * FROM orders WHERE user_id 12345 AND create_time 2024-01-01 00:00:00;第一次执行 explain结果非常难看type 是ALLkey 是NULLrows 直接飙到 268 万。说明每次查询都在做全表扫描接口该慢不慢。当时的表里其实已经有一个user_id单列索引了但 explain 显示它没有被使用。为什么因为优化器发现这条 SQL 里既有user_id又有create_time从user_id索引进去之后还是要把该用户的所有订单都回表查出来再过滤create_time。如果这个用户的订单量很大优化器一算账还不如直接全表扫。优化动作是调整索引设计改成联合索引ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);再次执行 explaintype 变成了rangekey 变成idx_user_createrows 从 268 万降到了几百行。查询秒回。这里有一个很多人会踩的坑既然单列user_id已经建了单列create_time也建了为什么不直接用两个索引SQL 里同时用到两个条件MySQL 确实有可能做索引合并Index Merge但它的稳定性和效率远不如一个联合索引。联合索引(user_id, create_time)先在索引里定位到 user_id再在相同 user_id 内利用 create_time 的有序性做范围定位这一下就把回表量降到了最低。注意加索引前一定要评估业务场景。如果create_time的范围条件非常宽联合索引的效果也会打折。索引设计是跟着查询条件走的没有一个索引能包打天下。3.3 索引设计与创建的几条原则结合上面的案例我把索引设计原则总结成几条可落地的建议第一给区分度高的列建索引。区分度可以粗略用COUNT(DISTINCT 列) / COUNT(*)来衡量。性别只有 0 和 1 两个值区分度极低就算建了索引优化器也可能放弃它走全表扫描因为一个值对应了太多行回表成本太大。而订单号、手机号这类几乎每个值都不同的列区分度极高索引效果就非常好。第二根据 SQL 设计联合索引而不是堆单列索引。很多人喜欢给每个查询字段都建一个单列索引结果一张表十几个索引写入越来越慢查询却不一定变快。正确的做法是先梳理核心 SQL 的 WHERE、ORDER BY、GROUP BY 条件然后设计少数几个能覆盖高频场景的联合索引。第三主键尽量简短而且有序。因为二级索引的叶子节点都存着主键值主键越大二级索引就越大占用空间也越多。自增主键不仅能保持聚簇索引的插入有序避免频繁页分裂还能让每个二级索引都轻量一些。UUID 主键在数据量大的表上写入性能通常都会被有序整型主键甩开一个身位。第四字符串字段考虑前缀索引。长字符串列比如 URL、备注如果整列建索引索引体积巨大。可以用ALTER TABLE t ADD INDEX idx_url(url(20));的方式只对前 20 个字符建索引。代价是可能损失一部分区分度这需要测试和权衡。4. 索引失效的典型场景与排查实录4.1 最常见的六种索引失效场景索引失效是面试高频题也是线上问题重灾区。我整理了一个速查表后面逐个展开场景经典写法失败原因优化方案隐式类型转换WHERE phone 13800138000对索引列做了类型转换参数与列类型保持一致对索引列使用函数WHERE DATE(create_time) 2024-01-01函数处理后的结果无法利用原索引改写为范围条件模糊匹配前导通配符WHERE name LIKE %张无法从有序结构定位起点考虑全文索引或改写or 连接非索引列WHERE a 1 OR b 2需要合并两个结果集给 b 建索引或拆分 SQL违反最左前缀原则联合索引 (a,b)只查 bB树复合排序限制调整字段顺序或另建索引优化器认为全表扫描更快数据量小、区分度低成本计算后放弃索引不一定需要处理属于正常行为先看隐式类型转换。表里 phone 是 varchar 类型但 SQL 写成了数字SELECT * FROM t WHERE phone 13800138000。MySQL 会把 varchar 列转成数字再比较这就等于对索引列做了类型转换索引自然就失效了。这类问题在代码里非常隐蔽尤其是 Java 之类的语言里Long 类型参数传进来SQL 自动拼接后很容易踩中。再看函数操作。WHERE DATE(create_time) 2024-01-01会对每一行的 create_time 都执行一次 DATE 函数索引里存储的是原始时间戳根本没法直接定位。正确写法是把它改成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;这样既能走索引语义也完全一致。MySQL 8.0 里虽然支持函数索引但那是通过新建隐藏列实现的不是让原列的函数操作变得能用索引这点要区分清楚。LIKE %张这种前导通配符失效是因为 B 树按顺序排列只有知道前缀才能快速找到起点而%开头的条件没有确定的起点。不过LIKE 张%是可以走索引的。or 连接条件的情况也经常遇到。WHERE a 1 OR b 2如果 a 有索引而 b 没有MySQL 需要把两个条件的结果合并但 b 那边只能全表扫整个查询就可能退化为全表扫描。一个稳妥的处理是给 b 也建上索引或者把 SQL 拆成两条再 UNION。4.2 快速定位索引失效的方法排查索引失效我的习惯是三步走。第一步先跑 explain看 key 字段。如果 key 为 NULL或者 type 是 ALL基本可以断定这条 SQL 没走索引。再结合 where 条件对照前面说的几个失效场景逐条排查。第二步开启慢查询日志把生产环境的慢 SQL 收集起来。MySQL 里可以这样临时开启SET global slow_query_log ON; SET global long_query_time 1;然后去日志文件里捞那些执行时间超过 1 秒的 SQL用 explain 逐个分析。慢查询日志是发现索引问题最直接的入口。第三步如果 explain 看不出名堂怀疑是优化器自身的选择问题可以用 optimizer trace 查看优化器的详细决策过程SET optimizer_trace enabledon; SELECT * FROM t WHERE phone 13800138000; SELECT * FROM information_schema.OPTIMIZER_TRACE;它会列出优化器为什么选择全表扫描而不是某个索引成本计算过程一目了然。这个方法在工作里帮我解决过好几个“明明有索引却不走”的诡异问题。4.3 实战中踩过的坑分享两个我实际遇到过的问题给大家提前排雷。第一个坑状态字段建了索引却完全不生效。曾经有个订单表order_status 只有 0 和 1 两个值我觉得这个字段是查询高频条件就单独建了索引。结果 explain 一看type 还是 ALL。优化器一算账这个字段区分度太低走索引要回表的行太多成本比全表扫描还高干脆放弃。后来我把这个字段和其他字段一起做成联合索引只在联合条件里发挥作用效果才正常。这个案例再次验证了那个原则不是每个查询字段都值得单独建索引。第二个坑手机号字段隐式转换排查了一个下午。业务反馈一个用户查询接口偶发超时我 explain 之后发现 key 是 NULL第一反应是索引没建好前前后后查了表结构、确认索引存在、重建索引都没用。最后仔细一看 SQL发现参数是 Long 类型传进来的拼出来的 SQL 里手机号变成了数字触发了隐式类型转换。把参数改成字符串后索引立刻生效慢查询消失。从那以后我养成了一个习惯但凡 varchar 列一定检查 SQL 里对应的参数类型。5. 关于索引设计我最后的几点体会5.1 索引不是越多越好很多人有一个误区觉得索引是万能的一张表建了十几个索引保险。实际上索引是有代价的每次 INSERT、UPDATE、DELETE都要同步维护索引树索引越多写入越慢每个索引都占用磁盘空间二级索引的叶子节点里还存了主键空间翻倍而且优化器在面对一堆索引时也会犯难挑错索引反而导致性能下降。我在项目中更倾向于“精而少”的策略。先统计出整个业务里最核心的几十条 SQL再根据这些 SQL 设计少数几个联合索引尽量做到一个索引服务多个相关查询。那种“每个字段单独建索引”的偷懒做法短期看不出问题等数据量上来就会变成噩梦。5.2 一个值得养成的上线习惯最后分享一个我坚持了很多年的习惯上线前把核心 SQL 用 explain 过一遍重点看 type、key、rows 三个字段。只要发现 type 是 ALL 且 rows 在百万级哪怕当前数据量还不大我也会重新审视索引是否到位。这个习惯帮我挡掉了不少线上事故——性能问题最怕的不是慢而是慢在流量高峰才暴露那时候一切优化都显得仓促。索引这门技术原理说难并不难难的是在真实场景里保持克制和严谨。B 树、聚簇索引、联合索引、覆盖索引、索引下推、失效场景这些概念串起来其实就是一条主线让 SQL 尽量少读数据页少回表少做无用功。把这条主线想通了遇到再复杂的慢查询你也能从容拆解。