MySQL索引原理与实战：从B+树到复合索引优化

1. 索引到底解决了什么问题

先聊个真实的场景。我刚工作那会儿，公司有个订单查询接口，每天被调用几十万次。某天线上报警，数据库 CPU 直接飙到 100%，一查慢查询日志，发现一条查询跑了整整 12 秒。表里才多少数据？不到 300 万行。当时的 SQL 长这样：

SELECT * FROM orders WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC;

这张表没有索引，所以每次查询都是全表扫描，300 万行数据从头到尾读一遍。更要命的是，这张表每天还在不断插入新数据，全表扫描的时间随着数据量线性增长，到最后几乎没法用。

后来我给user_id和status加了个复合索引，这条查询从 12 秒降到了 30 毫秒。就这一个索引，省了两台数据库服务器。

这事儿之后我就明白了，索引就是数据库的“目录”。你想在一本 1000 页的书里找一个关键词，没有目录就从头翻到尾，有目录直接翻到对应页码就行。MySQL 的索引本质上就是为数据建的目录结构，它把某个列或某几个列的值按特定顺序组织起来，让查询可以快速定位到目标记录，而不是把整张表扫一遍。

很多人对索引的理解就停留在“加了索引查询就快了”，但一遇到实际问题就懵：为什么明明建了索引却没用？为什么a AND b的查询建了(a,b)复合索引还是不生效？为什么有些场景加索引反而拖慢写入？这些问题背后是对索引原理和机制的掌握程度。这篇长文就把 MySQL 索引从底层数据结构到实际调优完整地过一遍。

说句实在话，索引不是越多越好，它是一把双刃剑。用好了是性能利器，用不好就是存储和写入的负担。下面从数据结构开始，一步步拆解清楚。

2. 索引的底层数据结构：B+ 树的来龙去脉

2.1 为什么不是二叉搜索树或哈希表

很多初学者都会疑惑：为什么 MySQL 的 InnoDB 引擎选择 B+ 树作为索引结构，而不是更常见的二叉搜索树或者哈希表？

先说二叉搜索树。它的查找时间复杂度是 O(log n)，理论上不错，但问题出在树的高度上。二叉搜索树每个节点最多只有两个子节点，当数据量达到几百万行时，树的高度会达到 20 到 30 层。这意味着每次查询都要经过 20 到 30 次磁盘 I/O，而每次 I/O 都是毫秒级的操作，累积下来就是几百毫秒，完全无法接受。

再说明盐表。哈希索引的查找速度是 O(1)，听起来无敌，但它有两个致命缺陷。第一，哈希索引不支持范围查询。WHERE age > 18这种条件哈希表没办法处理，因为哈希表是无序的，没法快速遍历一个区间。第二，哈希索引不支持排序，ORDER BY age在哈希索引下依然要走 filesort。而数据库查询里有相当大比例是范围查询和排序场景，所以哈希表只能作为一种辅助索引存在（也就是 InnoDB 的自适应哈希索引）。

B+ 树的设计恰好解决了这两个问题。它是一种多路平衡搜索树，每个节点可以存多个子节点指针，数据量在百万级时树的高度只有 3 到 4 层。而且 B+ 树的叶子节点通过链表相连，天然支持范围查询和排序。

2.2 B+ 树的具体结构和查询过程

B+ 树有两个关键设计：非叶子节点只存索引键值，叶子节点存真实数据和相邻节点的指针。

非叶子节点不存数据，所以每个节点能容纳更多的键值，这直接压低了树的高度。举个例子，假设 InnoDB 每页大小是 16KB，一个索引键占 8 字节，那每个非叶子节点大约能存 16KB / (8 + 6) 大约 1100 个键值（6 字节是子节点指针）。当树的高度为 3 时，叶子节点数量约 1100 的平方，也就是 120 万左右，再乘上每个叶子节点能存的记录数，一张表轻轻松松存下千万级数据，查询只需要做 3 次磁盘 I/O。

查询过程也很直观。比如要查WHERE age = 25，从根节点开始，比对 key 的大小，决定往左还是往右走。走到叶子节点后，在有序链表里做二分查找，找到目标记录。整个过程就像你翻一本书的目录：先看大章节，再看小章节，最后定位到具体页。

叶子节点的链表设计是 B+ 树比 B 树更适合数据库的核心原因之一。B 树的叶子节点之间没有链接，范围查询还是得“回溯”去查父节点。B+ 树则直接顺着链表往后扫，一次范围查询的代价几乎等于一次普通查询再加上遍历链表。

2.3 InnoDB 和 MyISAM 的索引存储差异

MySQL 早期主流的 MyISAM 引擎和现在的 InnoDB 引擎在索引存储上的理念完全不一样。MyISAM 的索引文件和数据文件是分开的（所以叫“非聚簇索引”或“二级索引”），索引的叶子节点存的是数据的物理地址，拿到地址再回表取数据。

InnoDB 则是“聚簇索引”的鼻祖。它的主键索引和数据是存在一起的，主键索引的叶子节点直接存整行数据。所以 InnoDB 表一定要有主键，如果你没指定主键，InnoDB 会找一个非空的唯一列当主键，实在找不到就偷偷生成一个 ROWID 当主键。这也是为什么面试喜欢问“为什么 InnoDB 表必须有主键”，本质就是聚簇索引的结构决定的。

聚簇索引带来一个天然优势：按主键范围查询时，数据在磁盘上就是物理有序的，顺序读取速度极快。但副作用是，主键如果是一个 UUID 这种随机字符串，插入时索引会因为频繁的页分裂而效率低下，所以 InnoDB 表强烈建议使用自增整数作为主键。

3. 索引的分类和创建方式

3.1 主键索引、唯一索引、普通索引、全文索引

MySQL 里常见的索引类型按功能分有四种：主键索引、唯一索引、普通索引、全文索引。它们的核心区别在于约束能力和使用场景。

主键索引就是 PRIMARY KEY，一张表只能有一个，它的值不能为空也不能重复。由于 InnoDB 聚簇索引的特性，主键索引直接决定数据的物理存储顺序。需要特别说明的是，主键不仅仅是一个“索引”，它还是数据行的唯一标识。所以能用自增整数就不要用业务字段当主键，比如身份证号虽然能保证唯一，但作为字符串主键会让索引页的利用率变低，写入性能也差。

唯一索引和主键索引的区别在于：唯一索引的值不能重复，但可以为空，而且一张表可以有多个唯一索引。两者在查询性能上几乎没差别，唯一索引末尾多一步去重复检查而已。在线上环境，业务上需要发货单号这种唯一约束但又允许空值的字段，就很适合建唯一索引。

普通索引就是最常见的索引，它不加任何约束，只加速查询。你可以在任意列上建普通索引，但要注意，一个索引本质上是一棵独立的 B+ 树，每多一个索引就多一份存储开销，写入时也要多维护一棵树。所以索引不是越多越好，而是越精准越好。

全文索引是用于全文检索的，早期 MyISAM 支持，InnoDB 在 MySQL 5.6 之后也支持了。它适合LIKE '%关键词%'这种模糊匹配场景。但是说实话，在数据量大且搜索需求复杂的场景下，Elasticsearch 这类专业搜索引擎比 MySQL 全文索引好得多。MySQL 的全文索引应对中小体量的站内搜索够用，但别指望它替代专业搜索引擎。

3.2 单列索引和复合索引的选择

复合索引，也叫联合索引，是索引优化里最容易出问题也最值得花时间研究的部分。它是指在一个索引里包含多个列，比如建立(a, b, c)索引，实际上等于建了(a)、(a, b)、(a, b, c)三个索引的效果，这就是“最左前缀原则”的价值所在。

很多开发者在建复合索引时犯的最典型错误就是盲目跟风。看到查询里出现了A AND B条件就建(A, B)，完全没有考虑字段的区分度和查询频率，结果常常是索引建了但没起到应有的效果，甚至比全表扫描还慢。

建复合索引遵循一个基本策略：把查询里最常出现、区分度最高的字段放在最左边。区分度是指字段值的唯一程度，比如性别字段只有“男”“女”两个值，区分度极低；而订单号的每个值都不同，区分度极高。把高区分度的字段放在左边，可以最大化地削减索引树的搜索范围。

3.3 创建索引的具体语法和操作

实际创建索引的 SQL 语法比较简单，但很多细节值得反复确认。最基本的两种方式：

-- 方式一：建表时指定 CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(50) NOT NULL, `email` VARCHAR(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_username` (`username`), KEY `idx_email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 方式二：ALTER TABLE 添加 ALTER TABLE `users` ADD INDEX `idx_email_username` (`email`, `username`);

这里有个比较容易忽略的点：索引命名规范。虽然 MySQL 不强制要求，但你要是接手过那种索引名毫无规律的项目，维护起来真能让人崩溃。我个人的习惯是：主键索引就叫 PRIMARY，唯一索引用uk_字段名前缀，普通索引用idx_字段名前缀，复合索引用idx_字段1_字段2把所有参与字段列出来。这样在SHOW INDEX FROM table的返回结果里一眼就能看出每个索引的用途。

创建索引时有个参数值得关注：USING BTREE。InnoDB 引擎下索引默认就是 B+ 树，MySQL 也支持 HASH 索引，但只在 MEMORY 引擎里才有实际意义，InnoDB 下指定的 HASH 会被自动忽略。另外，前缀索引也是一个有用的优化手段，比如对长字符串建索引时可以只取前 N 个字符，减少索引占用空间，但要注意前缀长度选择不当会导致区分度下降。

4. 覆盖索引、回表与索引下推

4.1 回表查询的代价

理解了 InnoDB 的聚簇索引结构，就能自然理解“回表”这个概念。假设表里有主键 id，还有一个普通索引idx_user_id建在 user_id 列上。你执行SELECT * FROM orders WHERE user_id = 100，MySQL 会先通过idx_user_id这棵二级索引树找到所有符合条件的主键 id，然后再拿着这些主键 id 去聚簇索引树里查完整的行数据。

这个第二步就是“回表”。一次回表意味着一次随机 I/O，如果命中了 1000 行就要回表 1000 次，性能损耗相当可观。

那怎么避免回表？答案是覆盖索引。如果查询的字段全部包含在索引的键里，那 MySQL 在二级索引树上就能拿到所有需要的数据，压根不需要回表。比如你执行SELECT user_id, status FROM orders WHERE user_id = 100，而且存在(user_id, status)这个复合索引，那 MySQL 直接在索引的叶子节点上就能读出来这两个字段，省掉回表开销。

这也是很多资深 DBA 建议“能把 SELECT * 改成 SELECT 指定字段就改成指定字段”的原因之一。除了减少网络传输和内存占用，还可能让原本需要回表的查询变成覆盖索引查询。

4.2 索引下推是怎么“下推”的

索引下推是 MySQL 5.6 引入的优化，英文叫 Index Condition Pushdown，简称 ICP。理解它需要先看一个对比场景。

假设有复合索引(age, city)，执行查询：

SELECT * FROM users WHERE age > 20 AND city = '上海';

根据最左前缀原则，age可以用上索引，但city因为跳过了一个范围条件，并不能完整地走索引过滤。在没有 ICP 的旧版本里，MySQL 的做法是：先从索引树里把所有age > 20的记录找出来，然后每条都回表，拿到完整行数据后再在服务层判断city = '上海'。这个回表量非常大，可能取回了几万条数据，最后只留下几百条。

有了 ICP 之后，MySQL 会在存储引擎层把city = '上海'这个条件下推到索引遍历的过程中。也就是说，在遍历索引时，通过索引里已有的 city 值先判断一次，不符合的直接跳过，只有同时满足age > 20 AND city = '上海'的数据才会回表。回表次数锐减，查询速度自然大幅提升。

ICP 优化默认是开启的，它最典型的受益场景就是复合索引里第二列及之后的条件过滤。这也是为什么我一直强调复合索引列顺序要慎重，因为 ICP 虽然能帮你挽回一部分性能，但能走完整最左前缀还是比依赖 ICP 强得多。

4.3 通过 EXPLAIN 看执行计划

检查一条 SQL 是否用上了覆盖索引、是否触发了回表，最有效的方式是看执行计划。EXPLAIN 是每个搞 MySQL 的人都必须熟练掌握的工具。

EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100;

关键看Extra这一列。如果出现Using index，说明这条查询用了覆盖索引，没有回表。如果出现Using index condition，说明触发了索引下推。如果出现Using where但前面的 key 不为空，通常意味着索引只定位了一部分数据，剩下的条件在回表后过滤了。

我自己排查慢 SQL 的标准流程是：先EXPLAIN看 type 和 key，再根据rows估算扫描行数，最后结合 Extra 来判断是否要继续优化索引。type 列的取值从好到差依次是 system、const、eq_ref、ref、range、index、ALL，只要能到 range 以上通常问题不大，一旦看到 ALL 就是全表扫描，得重点排查。

5. 复合索引与最左前缀原则的实战经验

5.1 最左前缀原则到底怎么理解

最左前缀原则是复合索引最核心的规则，也是面试必考题。它的表述很简单：复合索引的生效顺序必须从索引最左边的列开始，不能跳过中间的列。

假设有一个复合索引(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 列无法走完整索引，但有 ICP 可能还能过滤一些）

以下查询则用不上索引（或只能部分使用）：

• WHERE b = 2（没带 a，直接失效）
• WHERE c = 3（没带 a 和 b，直接失效）
• WHERE b = 2 AND c = 3（同理失效）

为什么会有这样的规则？还是回到 B+ 树的结构。复合索引在排序时先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。这种“字典序”的组织方式决定了只有从 a 开始的条件才能借助索引的有序性快速定位。如果直接从 b 开始定位，就相当于在电话簿只知道对方名字而不知道姓氏，根本没法用目录快速查找。

5.2 高频提问：“where a and b 应该怎么建索引”

这是被问得最多的问题，因为 SQL 里两个条件并列是再常见不过的场景。比如热词里的“mysql where条件a and b，应该怎么建索引”，答案不是一句“建 (a,b) 复合索引”就完了，还得看两个条件是什么类型。

先分情况讨论。如果 a 和 b 都是等值查询（a = 1 AND b = 2），建(a, b)还是(b, a)其实差别不大，因为等值查询不涉及到范围截断，两个字段的顺序不改变索引可用性。但对单点查询的性能而言，把区分度更高的字段放前面能更快收敛目标区间。

如果 a 是等值、b 是范围查询（a = 1 AND b > 100），那就必须把等值字段 a 放在前面，范围字段 b 放后面。因为一旦 b 这种范围条件走了索引，它后面的字段就都失效了，但 a 的等值条件不受影响。顺序反过来的话，b 的范围条件会让 a 的等值判断失效，整体性能会差很多。

如果 a 和 b 都是范围查询，那就只能选择一个字段走索引，另一个字段回表后用 USING WHERE 过滤。这时候要选区分度更高、过滤效果更好的那个字段建在左边，另一个只能靠 ICP 或者干脆建两个单列索引后让优化器自己选择。

在这个场景里还有一个常见的优化技巧：如果 a 的区分度极高（比如订单号），b 的区分度极低，那可以考虑把 b 作为索引的一部分写到覆盖索引里，这样既能让 b 的过滤条件（在 ICP 下）生效，又可能做到覆盖索引避免回表。这个做法适合那种 WHERE 里两个字段都出现、SELECT 里也只有这两个字段的轻量查询。

5.3 复合索引设计的一个完整案例

举个例子。一个订单表，最常用的查询是查某个用户某天之后的订单：

SELECT id, order_no, amount FROM orders WHERE user_id = 588 AND create_time >= '2024-01-01' ORDER BY create_time DESC;

这个表里 user_id 区分度很高，create_time 范围查询。按上面的原则，索引应该建(user_id, create_time)。create_time 放后面是因为它作为范围查询，放在后面不会影响前面的等值条件使用。同时这个查询只取 id、order_no、amount 三个字段，如果再把 order_no 加进索引里变成(user_id, create_time, order_no)，amount 无法避免回表但 order_no 可以覆盖，EXPLAIN 能看到 Using index condition 加部分 Using index。

再看这个表的写入是否频繁。如果线上同时有大量插入，每多一个字段进索引都意味着插入时要做更多工作，所以这个索引的职责要平衡好。把查询出现频率最高、数据量最大的那条 SQL 优化到位，比贪多求全地建大复合索引更实际。

5.4 排序和 GROUP BY 里的复合索引

复合索引不仅能优化 WHERE 过滤，还能优化 ORDER BY 和 GROUP BY。因为索引本身就是有序的，如果排序字段和索引顺序能匹配上，MySQL 直接按索引顺序读出来就是排好序的结果，不需要额外 filesort。

最典型的就是ORDER BY create_time DESC配合WHERE user_id = 588。上面(user_id, create_time)索引天然就是按 user_id 分组、组内按 create_time 排序的，查询走索引后结果已经有序，EXTRA 里看不到 filesort。

但注意，如果排序方向不一致，比如索引是升序而查询是ORDER BY create_time DESC，MySQL 8.0 支持倒序索引才有办法直接利用，早期版本就需要 filesort。加上这种方向性问题在处理复合索引时更麻烦，比如ORDER BY a ASC, b DESC，索引(a, b)就没法严格满足双向排序需求。

GROUP BY 的优化逻辑类似，分组字段正好是索引的最左侧列时，MySQL 可以通过索引做“松散索引扫描”来避免临时表和文件排序。这也是为什么统计类查询如果能在字段上设计好索引，性能提升会非常明显。

6. 索引失效的常见场景和排查方法

6.1 你建的索引为什么没用上

索引失效是实战中最伤脑筋的问题：建了索引，EXPLAIN 一看 type 是 ALL，完全没走。这里面有规律可循，最常见的几个坑我用一张表整理出来。

每一项背后都是 B+ 树有序性的问题。函数和运算会把索引键的值变成另一个值，导致索引树的二分查找无法按原有序性进行；隐式类型转换意味着索引列的原本数据和传入参数的类型不匹配，MySQL 只能把每一条索引值都转一遍再比；OR 条件则是因为 MySQL 无法在单棵索引树上同时完成两个列的搜索条件合并。

6.2 隐式类型转换的坑，我踩过不止一次

这里展开说下隐式类型转换，因为它的隐蔽性特别强。热词里就有“mysql ssl 连接错误”和一堆数据库工具问题，但隐式转换导致索引失效是比连接错误更常见的业务事故。

有个典型例子：表里手机号字段是 varchar 类型，查询写的是WHERE phone = 13800138000。很多新手觉得这没问题，SQL 的=两侧一个字段一个数字，值对得上就查呗。可 MySQL 的隐式类型转换规则是：字符串和数字比较时，字符串会被转换为数字。也就是说，MySQL 要把表里每一行的 phone 字符串都转成数字再和 13800138000 比较，索引列的隐式函数处理直接导致索引失效。

而且这类问题在生产环境出了之后很难排查，因为本地数据量小，全表扫描也就几十毫秒，完全没感觉。上了生产几百万行数据，慢查询日志一拉，才发现是这种低级但高频的错误。

排查方法很简单：EXPLAIN看 type 是不是 ALL，再看 SQL 里有没有参数类型和字段类型不一致的情况。养成习惯，写 SQL 时先DESC table看看字段类型，再决定参数怎么传。现在 ORM 框架里很多查询是自动生成 SQL 的，参数类型要对齐好，否则很容易踩坑。

6.3 优化器说不用就是不用的场景

有些时候你确实没犯上面任何错误，索引还是没被使用。这是因为 MySQL 的优化器自己判断“走索引还不如全表扫描快”。

最典型的是低区分度字段。比如性别字段，只有男、女两个值，查询条件WHERE gender = '男'可能匹配全表一半数据。这种情况下走索引需要大量回表，还不如直接全表扫描，优化器的成本估算会选择 ALL。

另一个常见场景是大范围查询。WHERE id > 1这种条件虽然完美匹配主键索引，但结果集几乎是全表，优化器会放弃索引直接扫表。有些资料说“范围查询导致索引失效”，其实不是索引失效，而是优化器认为走索引不划算。

这类问题怎么处理？如果是低区分度字段，看业务是否能接受为它建立索引合并（Index Merge）策略，即多个单列索引分别扫描后再合并结果集，或者直接接受全表扫描，毕竟低区分度字段的全表扫描成本也不算太高。如果是大范围查询，那要检查业务逻辑是不是漏了必要的边界条件，比如是不是应该加上时间范围限制。

7. 索引维护和 SQL 优化的实操经验

7.1 慢查询日志和索引分析工具

搞清楚索引是否有效，不能靠猜，要基于数据来诊断。慢查询日志是最直接的入口。MySQL 默认慢查询阈值是 10 秒，对互联网应用来说太宽了，我通常在生产环境调成 1 秒甚至 500 毫秒。

-- 查看当前慢查询设置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询日志（临时生效） SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;

日志里打印出来的慢 SQL，先用 EXPLAIN 分析，再结合SHOW INDEX FROM table看表上有哪些索引可用。很多时候你会发现项目早期建了一堆冗余索引，比如单独建了(a)索引又建了(a, b)，前者完全多余，是典型的资源浪费。

information_schema.statistics表可以用来写诊断 SQL，一次查出所有索引的字段组成和基数（cardinality）。基数太低说明索引列的区分度不足，这类索引往往对查询帮助不大，可以考虑删掉。

7.2 索引的存储开销与写入代价

索引带来的最大负作用是写入变慢。每插入一条记录，除了往主键索引树里写数据之外，每个二级索引树也要同步插入一个索引节点。如果表里有 5 个索引，一次插入就要写 6 棵树，事务提交时这些树的变更都要刷到磁盘。数据量越大，索引树的深度越深，插入成本越明显。

所以我在线上环境一向的态度是：尽量用最少的索引覆盖最多的查询模式。不要一个查询加一个索引，而是分析所有慢查询的共性，用复合索引统一覆盖。比如你有一堆查询条件，但它们都带 user_id，那核心索引就围绕 user_id 来设计，再把不同的次要条件按频率依次加入。

可以通过SHOW INDEX FROM table查看索引的基数。基数太低说明索引列的区分度不足，这类索引往往对查询帮助不大，可以考虑删除。

7.3 大表的索引重建和在线 DDL

一旦索引设计不合理，生产环境上要改索引，最大的担心是表锁和长时间阻塞业务。早期的 MyISAM 时代 ALTER TABLE 会全程锁表，InnoDB 的在线 DDL 特性（MySQL 5.6 以后）已经能解决大多数场景。

-- 添加索引（算法为 INPLACE） ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time), ALGORITHM=INPLACE, LOCK=NONE;

LOCK=NONE表示允许 DDL 期间并发读写，不会阻塞业务。但即使如此，大表的索引创建还是会消耗大量 IO 资源，建议在业务低峰期操作，或者使用 gh-ost 这类工具把 DDL 操作拆成小块同步到影子表里。

特别提醒：DROP INDEX 之前一定先确认它没有被任何查询依赖。我犯过的错误是删了一个看似没用的索引，结果某条报表 SQL 从 300ms 直接退化到 9 秒。后来养成了删索引前先查慢查询日志、确认最近一周没有依赖这个索引的慢 SQL 才开始动手的习惯。

8. 高频问题排查实录

这个部分汇总一些我在社区和实际工作中反复遇到的问题，有点像“相亲角”一样把常见疑难杂症集中挂出来，方便对号入座。

8.1 主键索引和唯一索引的区别，别再说“一样”了

很多人回答这个问题时就说“唯一索引允许 NULL，主键不允许”，这只说对了一小半。在 InnoDB 的聚簇索引结构下，主键索引决定数据的物理存储顺序，而唯一索引只是逻辑上的唯一约束。主键的叶子节点存的是整行数据，唯一索引的叶子节点存的是主键值，查询时还需要回表。这个本质区别意味着：主键删除成本极高，因为要移动数据页；而唯一索引删除只是动索引树的节点。

另外，“唯一索引允许 NULL”在 MySQL 里有个特殊情况：一个表可以有多个 NULL 值，因为 NULL 不等于任何值，包括另一个 NULL。而主键索引因为是 NOT NULL 加唯一，所以完全不允许任何形式的重复或者空值。

8.2 为什么 MySQL 建议主键用自增整数

这个建议的本质原因在前面聚簇索引部分已经说透了。自增主键保证新插入的数据在主键索引树上是往“最右边”追加的，不需要大量移动已有数据，页分裂的频率也最低。如果用 UUID 这种随机字符串做主键，每次插入都可能落在索引树的中间位置，触发大量页分裂和索引重组，数据页的利用率还会降低。

有一种具体的对比案例我实操过。一张 1000 万行的日志表，自增 int 主键的情况下，插入峰值能到每秒 5000 行；改成 UUID varchar(36) 主键后，峰值直接掉到每秒 800 行左右，差距就是这么明显。

8.3 哪些场景用会导致索引失效

这个问题的标准答案就是第 6 章那张表里的内容，但有一个细节容易被忽略：MySQL 的优化器对索引成本的决定是数据相关的。同一个 SQL，在表数据量小的时候走全表扫描，数据量大了之后反而会走索引。所以“索引失效”经常是个动态现象，你开发时看着 EXPLAIN 正常，上了生产数据量大了才发现走了 ALL。这就是为什么监控慢查询、周期性检查执行计划这么重要。

另一个容易忽略的是“索引列参与比较时合并了多个范围条件”。比如WHERE a > 1 OR a < 0这种条件从数学上可以合并成一个全量范围，但实际上优化器会老老实实把两个范围 UNION，如果数据分布差，可能出现一条 SQL 扫多个区间的现象，效率反而不如全表扫。

8.4 排序变慢是因为没用上索引吗

排序慢有两种情况：一种是完全没用上索引，MySQL 走 filesort 把结果集放到内存或临时文件里排序；另一种是用上了索引但排序字段本身不在索引覆盖范围内。

如果在 EXPLAIN 的 Extra 中看到Using filesort，说明排序没有走索引。要优化，就是把 ORDER BY 的字段加入某个合适的复合索引，并确保 WHERE 条件的前缀字段和它顺序匹配。比如已经建了(user_id, create_time)索引，WHERE user_id = 123 ORDER BY create_time DESC就能直接走索引顺序。

但如果查询里排序字段前面还有个范围条件，比如WHERE create_time > '2024-01-01' ORDER BY user_id，而索引是(create_time, user_id)，那 user_id 的排序就用不上了。这种时候重新设计索引顺序，或者改写 SQL 去配合现有索引，都得看实际查询频率来权衡。

8.5 MySQL 索引在事务和锁里的角色

这个经常被人忽略。索引是 InnoDB 行锁的基础。InnoDB 的行锁不是“给数据行上锁”，而是“在索引记录上加锁”。如果一条 UPDATE 语句的 WHERE 条件没有索引，InnoDB 无法在索引上定位记录，就只能退化成对全表做扫描并给每条扫描到的记录加锁，表面上锁了一堆行，实质上已经接近表锁的并发表现。

我遇到过一个线上事故：一条 UPDATE 因为没有索引，在并发场景下把整张 200 万行的表锁住了，所有读写全部阻塞，数据库连接数瞬间爆掉。排查到最后就是 WHERE 的字段没建索引。所以判断一个字段该不该建索引时，不仅要看它是否出现在查询里，还要看它是否出现在 UPDATE/DELETE 的 WHERE 条件里。索引既是查询的加速器，也是锁的定位器。

8.6 工具类问题和索引的关系

热词里出现了 navicat、数据库工具、连接错误这些词，虽然不是索引本身的内容，但有一个共同点：使用图形化工具查看和设计索引时，直观性和准确性不可兼得。Navicat 这类工具设计索引确实方便，点几下就出来了，但你得清楚工具帮你在后台执行了什么样的 DDL。我建议团队内部统一用 SQL 脚本管理表结构变更，不要用 GUI 直接改生产库，否则没有任何记录，后续审计和回滚都无从谈起。

9. 索引选型与调优的综合思路

9.1 一个索引设计流程，能解决 80% 的问题

做索引设计这么多年，我总结了一个固定流程，遇到新业务表就按这个走，基本不会出大问题。

第一步，收集查询模式。把业务里跑得最多的所有 SQL 列出来，标注 WHERE 条件、JOIN 条件、ORDER BY、GROUP BY、覆盖查询字段。

第二步，筛选高优先级字段。统计每个字段在查询里出现的频率和区分度。出现频率高、区分度高的字段优先进索引。

第三步，设计复合索引。优先覆盖最热点查询的 WHERE 等值加排序组合，不要贪多。一个复合索引能覆盖尽量覆盖多个相似查询。

第四步，用 EXPLAIN 验证。把核心 SQL 跑一遍 EXPLAIN，确认 type 不低于 range，Extra 没有出现 Using filesort 这种需要优化的情况。

第五步，上线后监控。慢查询日志持续观察至少一周，确认优化后的查询没有退化，同时观察写入性能没有明显回退。

这五步里最容易出错的是第二步。很多开发只关注字段出现频率，忽视了区分度，结果把性别这类低区分度字段放进了复合索引左边，导致整个索引价值大打折扣。

9.2 索引不是银弹，表设计远比索引重要

说了这么多索引技巧，我必须泼一盆冷水：索引能解决的是“已有查询模式下的性能问题”，而不是“错误表设计带来的结构性问题”。

最常见的错误表设计是字段类型选错。明明存数字却用 varchar，明明存日期却用字符串，这类表无论怎么建索引都有先天缺陷。比如日期用字符串存储，范围查询BETWEEN '2024-01-01' AND '2024-01-31'在字符串类型下是按字典序比较，一旦日期格式不统一（有些是 '2024-1-1'，有些是 '2024-01-01'），结果就完全不对，索引也帮不上忙。

再比如大字段。一张表里塞了几个 TEXT 字段，行数据特别长，每个数据页能存的记录数就少，聚簇索引的扫描效率自然低。这种表本身就该考虑垂直拆表，把大字段拆到附属表里，主表只保留高频查询需要的列。索引设计在这种场景下只是治标，表结构调整才是治本。

9.3 什么时候应该放弃索引

不是所有查询都需要索引。当出现以下信号时，完全可以考虑不加索引：

• 表数据量在万级以内，全表扫描本身就是毫秒级。
• 字段区分度极低，比如状态字段只有两三个取值。
• 查询结果经常占到全表的 20% 以上，走索引的回表成本已经高于全表扫描。
• 写入远大于查询，而查询本身压力也不大。

这类判断需要结合具体场景，不能拍脑袋。比如“万级以内不加索引”不是绝对真理，如果这个表是频繁 JOIN 的维表，JOIN 条件上有索引能避免驱动表每行去查被驱动表时的全表扫描，这种场景下即使数据量小也值得建索引。核心原则始终是：让优化器有更廉价的执行路径可走，而不是为了“有索引”这个形式去建。

9.4 索引调优的长期主义

最后聊点务虚的。索引设计不是一锤子买卖，随着业务发展，查询模式一直在变。今天的热点查询明天可能就不用了，今天没人用的索引明天可能变成核心路径的加速器。

所以我会建议团队定期做索引健康检查：每个季度拉一次慢查询日志，看看哪些 SQL 的响应时间在缓慢上升，结合表数据量的增长来分析是不是索引的区分度下降或者页分裂严重了。长周期的数据变化会让索引效果慢慢劣化，比如时间字段的基数在持续变大，原来的索引策略可能从“高效”变成“低效”。

在一个业务系统里，做索引调优最忌讳的就是“改完就跑”，运维数据要持续跟。一个健康的数据库，慢查询数量应该是稳定或下降的；如果慢查询曲线持续上升，即使单条 SQL 看起来还没到告警线，也要提前介入排查了。

我个人在实际操作中的体会是，索引调优更像一门“平衡艺术”。你要在查询加速和写入成本之间找平衡，也要在单条 SQL 的极致优化和整体系统的稳定之间找平衡。不要为了展示技术实力而堆砌索引，也不要在查询已经扛不住的时候还坚持不建索引。每建一个索引，就问自己三个问题：它覆盖了哪些高频查询？它会不会成为某些写入路径的瓶颈？如果删掉它，哪些 SQL 会变慢？三个问题都有明确答案，这个索引才值得留在线上。