看到你点了这个标题进来我想你大概率是写SQL时遇到了性能瓶颈——明明数据量不大但查询慢得离谱或者你已经知道“要建索引”但每次建索引都靠猜where后面跟了俩条件就不知道怎么排字段顺序。这类问题在开发岗面试里高频出现工作三五年的人也可能栽在上面。这篇文章就把 MySQL 索引讲透从数据结构为什么要选 B 树到主键索引和唯一索引到底差在哪再到联合索引的字段排列顺序怎么定、哪些写法会让索引直接失效最后附上我用 EXPLAIN 排查慢 SQL 的完整思路。内容偏进阶但我会把每个“为什么”掰开讲哪怕你现在只会CREATE INDEX看完也能在下次建索引时说出个一二三来。1. 索引到底解决了什么问题1.1 数据扫描的真正瓶颈先说个最基础的场景一张表里有一千万行记录你要执行SELECT * FROM user WHERE phone 13800138000。没有索引的时候MySQL 只能把这张表的每一行都读一遍逐行比对phone字段的值这个过程叫全表扫描。一千万行听起来不多但每行数据在磁盘上是分散存储的InnoDB 默认一页 16KB一千万行可能要占几十万个数据页把这几十万页从磁盘搬到内存里比对耗时基本在秒级以上。有了索引之后MySQL 会通过一种特殊的数据结构去定位目标记录。这个结构不是把全表数据重新抄一遍而是专门维护了一套“目录”只需要在这个目录里做几次查找就能定位到目标数据页的位置然后再去读那一页的数据。这套“目录”就是索引它牺牲了一部分写入开销和存储空间换来了查询时磁盘扫描量的大幅下降。这里要注意索引不是“让查询变快”这么简单它解决的本质问题是减少磁盘 IO 次数。整个索引设计的一切优化最终都围绕“少读点磁盘”展开。1.2 为什么偏偏是 B 树索引可以用很多数据结构实现哈希表、二叉树、红黑树、B 树、B 树。MySQL InnoDB 引擎最终选择了 B 树这个选择背后是一套非常现实的权衡。哈希表的结构是键值对等值查询WHERE id 100最快只要 O(1)。但哈希表最大的问题是——它是无序的天然不支持范围查询WHERE id 100这种 SQL 在哈希索引上会退化成全表扫描。而且哈希表无法利用前缀匹配LIKE abc%这类查询也没法用。所以哈希索引只适用于极少数等值查询场景在 InnoDB 里被设计成自适应哈希索引只是锦上添花成不了主流。再看二叉搜索树。理论上查询复杂度是 O(log n)但它有个致命的问题——数据插入顺序会影响树的形状。如果你插入的数据是递增的二叉树就会退化成一条链表查询复杂度直接变成 O(n)。红黑树通过自平衡解决了这个问题但树的高度仍然较高。一千万数据量下红黑树高度大约 30 层意味着每次查询要经过可能 20~30 次磁盘 IO——每次从磁盘读取一个节点都是一次 IO这个代价非常昂贵。B 树和 B 树都是多路平衡查找树一个节点可以存储多个子节点树的高度远低于红黑树。一千万数据量的 B 树树高通常只有 3~4 层这意味着最多 3~4 次磁盘 IO 就能找到目标数据。B 树和 B 树的区别在于B 树的节点既存储数据也存储索引而 B 树的所有数据都存放在叶子节点非叶子节点只存索引键值。这个设计带来两个巨大优势一是非叶子节点能以同样的内存/页空间容纳更多索引键树被压得更矮二是叶子节点之间通过指针相连形成一个有序链表做范围查询时只需要从起始位置顺序往后扫不需要回父节点反复搜索这在数据库里是压倒性的优势。所以你可以这么理解B 树就是专门为数据库磁盘预读和范围扫描这两个核心场景打磨出来的数据结构。1.3 一张图理解 B 树索引结构用生活场景来类比B 树索引就像一本新华字典。字典的部首目录相当于非叶子节点它只记录“某个拼音/部首在第几页”并不写出字的具体解释。真正的字和释义全部集中在正文页面中——对应 B 树的叶子节点。你要查一个字先查目录逐层缩小范围最后翻到正文那一页。如果你想查所有“张”字开头的词条因为有页码指针串联你翻到第一个“张”字的页面沿着页码顺序往下翻到最后一个“张”字页面就行了一页都不用跳。InnoDB 里的聚簇索引主键索引就是这种结构叶子节点直接存储整行数据非叶子节点只存主键值。所以 InnoDB 表必须要有主键没有主键时它会选一个非空唯一索引代替实在没有就自动生成一个隐藏主键。这也是为什么你建表时没指定主键MySQL 也能正常工作——它在后台悄悄给你补了一个 6 字节的 ROWID。2. 索引类型主键索引、唯一索引、普通索引到底怎么选2.1 主键索引还是唯一索引主键索引和唯一索引在定义上都强调了“值不能重复”很多人因此误以为它们差别不大但这个想法在面试和实际排查中都很容易吃亏。主键索引的叶子节点存的是整行数据它决定了数据在物理存储上的排列顺序一张表只能有一个主键索引。因为数据页里的记录就是按主键值顺序排列的如果你插入一条主键值比已有数据小的记录InnoDB 要做数据页的拆分和重排这个操作的代价很高。所以只要没有特殊的分布式 ID 需求我强烈建议用自增整型作为主键——这样每天插入的数据永远是追加在末尾不会触发页分裂写入性能最稳定。唯一索引只保证逻辑上“值不重复”它本质上是二级索引。二级索引的叶子节点不存整行数据只存“当前索引列的值 主键值”。所以通过唯一索引查询数据时要先用索引找到主键值再拿主键回聚簇索引里查完整行这个过程叫回表。回表意味着一次查询最多要访问两棵 B 树比主键索引多一次磁盘 IO。那唯一索引和普通索引怎么取舍核心区别在写入性能和查询性能上。唯一索引因为要保证唯一性每次插入时都要检查是否冲突这个检查会带来额外的开销但反过来如果业务上确实需要保证某列不重复比如手机号、身份证号用代码在应用层做唯一性检查很容易出现并发竞态此时唯一索引是唯一可靠的选择。我的经验是没有唯一性约束需求的字段一律用普通索引不要顺手加 UNIQUE。写入性能有差别并且在批量导入大量数据时差别会被放大。2.2 二级索引与覆盖索引的差别二级索引最容易被忽视的一个价值是——它可能让你完全避免回表。举个例子你执行SELECT id, phone FROM user WHERE phone 13800138000而phone上正好有索引。此时二级索引的叶子节点上记录的是“phone 值 主键 id 值”而你查询要的id和phone这两列正好都在索引里存着MySQL 扫描完索引就能直接返回结果完全不需要回表查聚簇索引。这种“查询列全部命中索引”的情况就叫覆盖索引是 SQL 优化里性价比最高的一种手段。实际工作中我经常专门去构造覆盖索引。比如表里有order、user_id、status三列order表动辄几百 GB只关心user_id在某段时间内的订单量那就建一个(user_id, create_time)的联合索引查询只需要user_id和create_time这两列存储引擎扫索引就能得到全部结果不碰数据行快得离谱。反过来要尽量避免的是SELECT *配合二级索引。SELECT *意味着你要拿全部列二级索引根本覆盖不了每次命中索引后必须回表拿完整行如果命中的记录数非常多比如一万行就是一万次回表一万次随机磁盘 IO性能反而比全表扫描还差。这也是我排查慢查询时见到的第一大误区——很多人以为建了索引就万事大吉忽略了回表代价。2.3 索引不是免费的这部分想强调一个经常被忽略的事实索引是要付出“写代价”的。每建一个索引就相当于在 InnoDB 里多维护一棵 B 树。每次执行INSERT、UPDATE、DELETE除了改数据本身还要把涉及到的每个索引都同步更新一遍。数据量大、索引多的时候一条更新语句可能被拖慢好几倍。所以我见过有人给一张表一口气建了七八个索引查询是快了但写入能把业务拖死。更隐蔽的问题在存储空间。二级索引的叶子节点虽然只存“索引值 主键”但基数大的字段比如varchar(255)的 URL做索引时占用的磁盘空间非常可观。建索引前先看两个指标字段的区分度高不高比如性别这种只有 2 个值的字段区分度太差建了索引也过滤不了多少数据还可能因为回表次数太多比全表扫描还慢以及这列是不是高频出现在WHERE、ORDER BY、GROUP BY、JOIN条件里。只有高频且高区分度的字段才值得建索引。3. 联合索引与 SQL 调优实操3.1 联合索引的字段顺序怎么排这是索引领域被问烂了、也最容易答错的问题。核心规则叫最左前缀原则联合索引(a, b, c)在检索时MySQL 会优先用 a 来定位在 a 相同的基础上再用 b 定位接着才是 c。也就是说联合索引能被用到的前提是 SQL 的where条件里包含了索引的最左列。但面试里光会背“遵循最左前缀”没用真正的高手在回答“where a and b应该怎么建索引”时会给出这样一套完整思路。第一步看等值条件和范围条件。假设你的 SQL 是WHERE a 1 AND b 10那么索引应该建在(a, b)上而且要保证等值条件的列放在前面。原因是B 树在叶子节点上是有序的先按第一列排序第一列相同再按第二列排序。如果索引是(b, a)那叶子节点是按 b 排序的而b 10是个范围b 相同或者落在同一范围里的记录之间 a 虽然有序但整个查询要先扫出一大批 b 范围数据再回表过滤 a效率差得多。如果索引是(a, b)先定位 a1 的子树在这个子树里 b 已经按顺序排列好了直接二分定位 b10 的起点顺着叶子链表扫一段就行——既命中索引又避免大量回表。第二步看 ORDER BY。联合索引的另一个大作用是消除排序。MySQL 拿到一批数据后如果需要按某列排序且这列上没有索引用就会把结果集放到临时表里做 filesort大批量数据时这个过程非常慢。如果联合索引的字段顺序恰好匹配 SQL 里的ORDER BY顺序那么扫描索引本来就是有序的MySQL 根本不用额外排序。所以建(a, b)索引时如果业务里高频出现WHERE a ? ORDER BY b这个顺序就很完美如果高频出现WHERE b ? ORDER BY a那就尴尬了——b 在索引第二列等值条件能匹配到子树但这棵子树内部是按 b 再按 a 排序的你要的按 a 排序没法直接顺着索引扫出来。这种情况下如果是只针对少数分布filesort 代价还能接受但数据量一大就得考虑把索引顺序调整成(b, a)。第三步看区分度。区分度高的列如手机号、订单号放到前面。你可能会想那如果WHERE a 1 AND b 2两个都是等值条件顺序是不是无所谓实测下来大部分时候确实无所谓但如果你了解 InnoDB 的索引统计信息——优化器会参考索引的区分度Cardinality来估算扫描行数区分度高意味着估算出来的筛选行数少优化器更大概率会选择走这个索引而不是全表扫描。所以即便等值条件的字段顺序不影响索引命中的扫描成本也会影响优化器的判断。这一点在分析“为什么我建了索引但 EXPLAIN 里没走索引”时尤其重要。3.2 哪些写法会导致索引失效顺序排好了索引也建了结果 SQL 里一个不小心索引还是用不上。这个问题的排查经验我建议你直接背下来。第一类是对索引列做了计算或函数操作。比如WHERE DATE(create_time) 2024-01-01MySQL 无法直接利用create_time上的索引因为索引里存的是原始的 datetime 值没法对“被 DATE() 函数处理后的值”做二分定位。正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02。同样的坑还包括WHERE id 1 100、WHERE LENGTH(phone) 11、WHERE LEFT(name, 1) 张等原则上只要索引列参与了运算索引就会失效。第二类是隐式类型转换。典型场景是phone字段是varchar类型SQL 里写成WHERE phone 13800138000数字会自动转成字符串再比较MySQL 对索引列做了 CAST索引失效。反过来如果字段是 int参数传字符串字符串会转会数字那又是另一番局面。实际工作中我最常见的隐式转换发生在关联查询里user_id字段是bigint关联表的user_id却是varcharJOIN 条件里类型不一致索引直接废掉。第三类是LIKE前缀通配符。WHERE name LIKE %张%前面有通配符B 树没法利用有序性从中间开始定位索引失效但WHERE name LIKE 张%这种后缀通配是可以的因为索引数可以对“张”做范围定位。所以在 Elasticsearch 等全文检索引擎出现之前很多系统都用name LIKE 张%做分页搜索就是为了能蹭上索引。第四类是OR 条件。WHERE a 1 OR b 2如果 a 和 b 上分别有单列索引MySQL 在 5.x 版本通常无法同时使用两个单列索引做 OR 合并会退化成全表扫描。新版本支持了索引合并Index Merge但效果不稳定最稳妥的做法是把 OR 改写成两个查询 UNION或者建联合索引又或者直接换成IN列表。第五类是NOT IN、! 等负向查询。这类操作本质上是“排除一部分”B 树只擅长正向定位一段连续范围负向查询往往要走全索引扫描才能判断哪些不满足。如果负向条件筛选掉的数据比例很大优化器可能还是会选择全表扫描如果筛选比例小索引也许能起一点作用但效果不如正向条件明确。相比之下NOT EXISTS写法经常能更聪明地利用索引这是另一个话题了。3.3 用 EXPLAIN 验证你的索引建完索引SQL 改完了怎么确认真的生效了EXPLAIN是你绕不开的第一工具。看到执行计划第一眼看type列。这个列从好到差排const、eq_ref、ref、range、index、ALL。ALL就是全表扫描看到这个就说明你的索引没被用上或者根本没这个索引index表示全索引扫描虽然扫描的是索引但依然不理想range是范围扫描还算可以ref是非唯一等值匹配正常水平const和eq_ref是最优的直接定位到唯一行。第二眼看key列它显示实际用到的索引名。有时你建了索引但key显示为空或者用的不是你预期的那个索引这说明优化器判断走这个索引不如全表扫描。原因是估算的扫描行数太多回表代价太高。第三眼看rows列它显示优化器预估要扫描的行数。这个数越小越好。如果rows上百万但表里就不到一万行说明统计信息没更新可以ANALYZE TABLE刷一下如果优化器还是选错索引可以用FORCE INDEX强制指定——但这是治标长期还得靠调整索引设计让优化器自己选对。第四眼看Extra列这里藏着几个非常重要的关键词。Using index表示已经满足覆盖索引条件连回表都免了最优Using index condition是索引下推ICPInnoDB 在索引层面就过滤了部分不满足条件的行减少回表次数这个也比较好Using filesort表示需要额外排序说明你的 ORDER BY/GROUP BY 没吃到索引红利Using temporary更糟用到了临时表一般伴随 filesort是性能杀手。我自己的习惯是新写一条 SQL 时先EXPLAIN看一眼 type 是ALL还是range再顺手把Using filesort查一查这几乎能提前拦截掉八成线上性能问题。3.4 常见慢查询优化心法排查慢查询我走了不少弯路后才理清一整套顺序。第一步是别急着加索引先定位问题 SQL。开启慢查询日志是标配MySQL 里配置slow_query_logON并把long_query_time设为 1 秒然后用mysqldumpslow工具把高频慢 SQL 汇总排个序看看哪些 SQL 出现次数最多、累计执行时间最久。很多时候性能瓶颈不在单条 SQL而在某条 SQL 被高频执行——比如一个前端列表接口每次请求都触发一次 2 秒的 SQL一天上千万次请求这比一条跑了 10 秒但一天只跑几次的报表 SQL 更该优先优化。第二步是对每一条慢 SQL 做“拆解式分析”先看where条件里的谓词列全部单独拎出来评估区分度再看select的字段里有没有能通过覆盖索引框住的部分再往下看有没有join、order by、group by涉及的列。这一轮分析做完建索引的方向基本就定了。第三步是想着“能不能少读数据”。比如分页深翻页问题业务代码里写LIMIT 100000, 20MySQL 需要把前 10 万行全扫出来再丢弃即使有索引这 10 万行回表的 IO 也跑不掉。常规优化法是用覆盖索引把SELECT id, name, ...改成先只查主键SELECT id FROM table WHERE ... LIMIT 100000, 20再用主键JOIN回原表取完整行这样回表只发生在最后 20 行上。实测下来百万级表深翻页性能能提升一到两个数量级。4. 索引失效排查与实战踩坑记录4.1 排序场景中的索引设计日常需求里有个高频坑WHERE条件用得挺好但是一加ORDER BY性能就崩了。比如表结构是(user_id, status, create_time)业务 SQL 是SELECT id, amount, status FROM order_table WHERE user_id 123 ORDER BY create_time DESC LIMIT 20;很多人下意识会建一个(user_id, status)索引因为WHERE条件里这两个字段都在。但查询要求按create_time倒序排序而(user_id, status)索引里叶子节点是按user_id、status排的create_time并不在其中MySQL 只能把命中的结果先丢进临时表排序再取出前 20 条——在大数据量下性能非常难看。正确的设计是改为(user_id, create_time)联合索引让WHERE中的等值列user_id打头排序字段create_time紧跟其后。这样 InnoDB 扫描索引时先定位到user_id123的子树这棵子树内部已经按create_time有序排列直接倒序读取前 20 条连排序都省了EXPLAIN里也不会再看到Using filesort。如果是ORDER BY create_time DESC LIMIT 20且没有WHERE条件那就得靠单独的create_time索引了。这种场景我见过不少业务上查“最新动态”只取 20 条如果没索引就是全表扫描排序全表数据量一大就很明显。建一个(create_time)索引后直接倒序扫索引头 20 条性能逆天。但要注意这种“只排序不过滤”的大表场景下二级索引无法覆盖的列依然会引发大量回表所以尽量保证SELECT列也在这个索引里。4.2 一个线上索引优化的完整复盘我之前处理过一张订单流水表单表数据量接近 8000 万行。业务方反馈某个统计接口耗时超过 30 秒接口里有一条关键 SQLSELECT COUNT(*), status FROM order_flow WHERE merchant_id M10086 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY status;我先用EXPLAIN看执行计划type 是ALL全表扫描。这个没问题因为原来的索引只有主键这张表连一个二级索引都没建。问题出现了建索引前我特意看了业务上的查询模式发现merchant_id的区分度很高几万个商家create_time则跨度很大。按常理索引应该建在(merchant_id, create_time)上。但这里有个容易被忽略的细节——查询里还有GROUP BY status。我如果只建(merchant_id, create_time)虽然扫描的数据量会大幅下降只扫 M10086 这一个商家的数据但GROUP BY status依然要在这堆数据里做一次额外的临时分组排序。真正更好的方案是建联合索引(merchant_id, create_time, status)这样不仅WHERE条件吃到了索引GROUP BY status也能在索引有序性上直接聚合因为索引的最左列merchant_id是常量叶子节点上只展现了“相同 merchant_id create_time 有序 status 有序”对相同的 merchant_id 而言status 的有序性也天然满足分组要求直接顺序扫描即可完成分组不需要临时表排序。这个案例我想传达的核心是联合索引的字段不仅要服务WHERE还要把ORDER BY、GROUP BY里需要的列排进索引让索引一口气完成过滤、排序、分组三件事。4.3 索引基数与优化器的决策陷阱EXPLAIN里的rows经常骗人。有一次我查一张千万级会员表WHERE vip_level 3这种条件vip_level 只有 5 个枚举值基数很低但表里 40% 是 vip_level3 的记录。建了(vip_level)索引EXPLAIN出来 type 却是ALL走全表扫描。原因很简单——优化器估算要扫几十万行还要回表拿整行数据它觉得全表扫描顺序读更快。这种情况你可能会“解决”成FORCE INDEX但我建议你再想想是不是真的非要靠这个低区分度字段过滤如果业务场景确实需要按 vip_level 分组统计我会建议改用覆盖索引方案比如在这种统计场景常用的写法是新建一个(vip_level, created_at)的联合索引让 SELECT 涉及的vip_level、created_at、COUNT(*)全部命中索引在扫描时完全不回表几千行索引数据扫描完就能出结果速度快得惊人。这段经历告诉我一个字段本身区分度不高时不一定不能建索引关键是让索引配合查询列变成覆盖索引把回表开销完全消掉。5. 索引设计的几条实战心得项目做多了慢慢总结出几条朴素但实用的经验专门列在这里。第一一张表的索引数量最好是 5 个以内超过 7 个就要认真评估。索引不是多多益善每次写操作都要更新所有索引索引太多会把写性能直接拖垮。有些 DBA 会强制单表不超过 5 个索引虽然略有教条但方向是对的。第二优先设计复合索引少搞一堆单列索引。一条WHERE a ? AND b ? AND c ?三条单列索引加起来MySQL 大概率只会用一个索引少数版本会做 index merge但不可控其余两个用不上还是用不上。如果换成(a, b, c)一个联合索引三列全部秒收。平时设计索引就从业务 SQL 的真实写法倒推按字段出现频率和区分度排顺序。第三尽量用整型做主键避免用 UUID 之类的高随机值。UUID 的随机性会导致页分裂频繁、索引树膨胀查询性能也受限于非连续性。如果业务确实需要全局唯一 ID可以用雪花算法之类的主键生成方案能保证 ID 整体递增又兼具全局唯一性。第四每季度整理一次慢查询日志把长期未使用的索引找出来。我用sys.schema_unused_indexes这个视图来查这视图是 MySQL 自带性能诊断工具能直接列出从未被任何查询用到的索引看到了就果断删掉——这种索引纯粹是负债白白拖慢写入、白白占用磁盘。第五别忽略ANALYZE TABLE的重要性。InnoDB 的优化器统计信息不是实时更新的批量导入大量数据后统计信息可能过期导致优化器做出了错误的索引选择。执行一次ANALYZE TABLE能把统计信息刷新让索引选择恢复正常。这个操作在排障时十有八九能用上而且它本身开销极低只是重新整理统计信息不重建索引推荐在排查任何索引失效问题时先做一步。第六复合索引里范围条件的列尽量往后放。这不是可选项是刻在骨子里的原则。有一条经典原则索引上如果有范围查询范围列后面的列就用不上索引的定位能力了只能做索引内过滤。所以WHERE a 1 AND b 10 AND c 3这样的 SQL索引理想顺序是(a, c, b)而不是(a, b, c)——a、c适合做等值定位b放最后做范围扫描。如果业务的查询模式里b是高频范围查询那建议专门优化 SQL 或者为(a, b)单独设计索引不要塞进三列联合索引里拖累后面的c。6. 结尾我自己的习惯是每次写 SQL 前先花十秒钟在脑子里过一遍这个查询过滤性好不强、区分度高不高、要不要回表、排序能不能走索引。等真正动手建索引时先把WHERE等值条件排前面、范围条件往后放、ORDER BY/GROUP BY列尽量嵌入索引尾部最后用EXPLAIN验证一遍。这套流程跑下来绝大多数线上查询性能问题都能在开发阶段就提前掐掉不用等上线后被慢查询日志追着打。最后分享一个我踩过很久的坑改 SQL 优化时别只盯着单条语句看。有时候一条查询慢了根本原因不在它本身而是上游另一个接口一次性查出太多数据再在应用层循环调用变成了 N1 查询。索引能解决单条 SQL 的扫描成本但解决不了业务逻辑上反复发起的海量查询。遇到这种情况优先改代码减少数据库交互次数——用小结果集 JOIN 代替循环查询、用批量查询代替逐条调用往往比加什么索引都见效快。索引重要但它只是整个性能优化体系里的一环别把所有宝都压在它身上。