索引这东西，网上讲基础的文章一抓一大把，但大多数要么停留在“索引就是目录”这种比喻上，要么直接甩一堆建索引语法让你背。今天这篇我想换个角度，把索引拆开来看：它到底解决了什么问题、为什么MySQL偏偏选了B+树、InnoDB底层到底怎么组织数据，以及你写SQL的时候哪些操作会让索引白建。全程会用实际场景和具体的SQL例子说话，适合已经会写基本SQL、开始琢磨查询性能、或者准备系统过一遍MySQL知识点的同学。这算是我MySQL学习系列的第九篇，前几篇讲了连接、事务、日志这些，这篇集中火力打索引。

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

1.1 全表扫描的代价，为什么查询越写越慢

先说一个场景。你有一张用户表，里面存了几十万行数据，某天你执行了这样一条查询：

SELECT * FROM user WHERE phone = '13800138000';

在没有索引的情况下，MySQL会怎么做？它会从表的第一行开始，把每一行的phone字段都取出来跟条件比较一遍，直到把整张表扫完，把所有满足条件的行都捞出来。这个操作叫全表扫描，复杂度是O(n)。

几十万行数据，机械硬盘时代可能要几十毫秒甚至几百毫秒，SSD时代虽然快很多，但如果你查询频繁且表持续增长，性能会肉眼可见地恶化。更重要的是，全表扫描会把大量无关的数据页加载到内存里，Buffer Pool的热点数据被挤出去，连带着其他查询也变慢。

索引的核心价值就是把这个O(n)的过程变成O(log n)。它相当于给phone字段额外维护了一份有序的“目录”，查询的时候不用从头翻，直接按照目录定位到目标位置附近，然后快速找到对应数据。

1.2 用目录思维理解索引的命中逻辑

很多人说“索引就是书的目录”，这个类比方向是对的，但容易产生误解。书目录的特点是：目录单独占几页，目录里记录的是章节名和页码，你要找某章就翻目录，然后翻到具体页。MySQL的二级索引做的事跟这个很像：它把索引列的值排序存储，每个值后面跟着一个指向实际数据行的指针（准确说是主键值），查询时先查这个“小目录”，拿到指针后再去数据表里取整行。

但有个关键区别：书的目录是按章节名组织的，你如果只知道页码是第100页，想找“这一页属于哪一章”，目录就帮不上忙了。MySQL索引也一样，你只有在查询条件里用到了索引列本身，索引才能发挥作用。如果你对非索引列做条件筛选，目录就废了，只能老老实实全表扫。

还有一个容易忽略的点：索引不仅能加速查询，还能加速排序和分组。因为索引本身是有序结构，如果ORDER BY的字段恰好是索引列，MySQL可以直接按索引顺序扫描输出，省掉filesort这个额外步骤。这个后面细说。

2. B+树为什么成为MySQL索引的默认选择

2.1 从二叉搜索树到平衡树，再到B+树的演进逻辑

先抛个问题：为什么不用二叉搜索树？二叉搜索树在极端情况下会退化成链表，比如插入的数据一直是递增的，树的形状就变成一根斜线，查找复杂度回到O(n)。所以实际应用中会用平衡二叉搜索树，比如AVL树、红黑树，保证左右子树高度差在一定范围内。

但平衡二叉树也有问题：它是二叉树，每个节点最多两个子节点，树的高度会随着数据量增长得很快。假设树高是20，意味着一次查询要访问20个节点。对内存数据结构来说这没问题，但数据库的数据是放在磁盘上的，每次访问一个节点就是一次磁盘I/O，20次I/O就很夸张了。

于是B树登场了。B树是多路搜索树，一个节点可以存多个关键字，能大幅度降低树的高度。比如说一个节点能存几百个关键字，几百万行数据的索引树高度可能只有3到4层，查询最多做几次磁盘I/O就能定位到目标。

B+树是B树的变体，区别主要有两点：第一，B+树的非叶子节点只存索引键值，不存数据本身，所有数据都存放在叶子节点；第二，叶子节点之间通过链表相连，形成了一个有序的链表结构。这两点特征让B+树在数据库场景下比B树更加合适。

2.2 InnoDB页结构与磁盘I/O的约束

InnoDB存储引擎操作磁盘的最小单位是页，默认大小是16KB。也就是说，哪怕你只想读一行数据，InnoDB也会把包含这一行的整个页从磁盘读到内存。反过来，索引树的每个节点在物理上就是一个页。

这里就能算出B+树的大致容量了。假设一行数据是1KB，一个16KB的页能存16行数据。如果非叶子节点里每个索引键加指针占16字节，那么一个非叶子节点能存的索引项大约是1000个。三层B+树大概能存多少数据？第一层（根）有1000个指针，第二层有1000×1000个叶子节点指针，第三层是叶子节点，每个叶子节点能存16行，算下来是1000×1000×16，也就是1600万行。

这个推算告诉我们一件很重要的事：绝大多数业务表的索引树高度只有2到3层。2层树只需要一次磁盘I/O去读叶子节点（根节点通常常驻内存），3层树需要两次I/O。这就是为什么索引查询快——索引树的高度决定了磁盘I/O次数，而B+树把高度压得非常低。

2.3 为什么不能直接用哈希索引做主力

你可能也听说过哈希索引。哈希索引查找单个等值条件非常快，理想情况下是O(1)，比B+树的O(log n)还快。那MySQL为什么不用哈希索引做默认选择？

问题出在哈希索引不支持范围查询。哈希表把键值打散后，原来的有序性全丢了，你没法用哈希索引做WHERE age > 18 AND age < 30这种条件，也没法直接用来排序。而范围查询、排序、前缀匹配这些恰恰是业务里最常见的查询形态。

InnoDB其实是有自适应哈希索引的，它会在某些热点查询场景下自动为B+树加一层哈希索引来加速等值查询，但对使用者透明，也不需要你去创建。我们平时说创建索引，指的默认就是B+树索引。

3. 索引的分类与InnoDB中的真实形态

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

按逻辑功能划分，MySQL索引主要分四类。

主键索引：每张表只能有一个，不允许为空。InnoDB里主键索引就是聚簇索引，后面会重点讲。唯一索引：允许为NULL（可以有多个NULL），但非NULL值不能重复。普通索引：没有任何唯一性约束，纯粹为了加速查询。全文索引：针对文本内容做分词匹配，适用于MATCH ... AGAINST ...语法，底层结构不是B+树，而是倒排索引。

实际建表时经常遇到的场景是：业务上有一个唯一业务编号，比如订单号，它应该建唯一索引不是普通索引。两者对优化器的意义不一样，唯一索引在等值匹配时，一旦找到一条记录就可以直接返回，普通索引还需要继续扫描下一条看是否还有重复值。这个差异在大数据量下会积少成多。

全文索引这里多说一句，MySQL的全文索引在中文分词上体验一般，生产环境真要搞搜索，通常会用专门的开源搜索引擎。所以除非你的场景很简单，否则别指望MySQL全文索引解决复杂搜索需求。

3.2 聚簇索引与非聚簇索引，回表到底是怎么回事

InnoDB的聚簇索引是指表本身按照主键构建的B+树，叶子节点存储的是整行的数据。换句话说，这张表的数据就是主键索引本身，数据物理上按主键顺序排列。

除主键外的索引统称二级索引，也叫非聚簇索引。二级索引的叶子节点不存整行数据，存的是索引列的值加主键值。查的时候会有一个两步操作：先到二级索引树里找到对应的主键值，再拿着这个主键值去聚簇索引树里找完整的行。这个第二步就是回表。

举个例子：

CREATE TABLE student ( id INT PRIMARY KEY, student_no VARCHAR(20), name VARCHAR(50), age INT, INDEX idx_student_no (student_no) );

如果执行SELECT * FROM student WHERE student_no = 'S123'，过程是：先查idx_student_no这棵二级索引树，找到student_no等于'S123'的记录的叶子节点，拿到id，再去聚簇索引树里查到id对应的完整行，返回。这里就发生了一次回表。

如果改成SELECT student_no FROM student WHERE student_no = 'S123'，情况就不一样了。查询需要的字段student_no在二级索引的叶子节点里就已经存在，不需要回表，这种情况叫作覆盖索引，后面细聊。

3.3 覆盖索引减少回表的实践经验

覆盖索引并不是一种独立的索引类型，而是“索引包含了查询需要的所有字段”的状态。当二级索引树里能查到全部所需数据，优化器就不再做回表操作，直接返回。

这个特性的实战价值非常大。很多慢查询的根因就是每次都要回表，尤其是在并发高的场景下，回表意味着额外的随机I/O。

举个例子，常见的一个慢查询：

SELECT id, name, age FROM student WHERE name = '张三';

如果只在name字段上建了普通索引，查询流程是：二级索引查name拿到主键id，回表聚簇索引拿name和age，返回。如果改成建联合索引(name, age)，查询需要的id、name、age在二级索引的叶子节点里全都有，就不需要回表了。

你可能会问，age字段不是会加在二级索引叶子节点吗？对，联合索引的叶子节点会包含所有索引列加主键。所以(name, age)这个联合索引能覆盖id、name、age三个字段，因为id是主键，天然就在二级索引里。

这里要区分一个概念：覆盖索引说的是查询字段被索引覆盖，不是索引本身有什么特殊结构。

4. 索引设计的取舍与优化

4.1 选择索引列的考量维度

索引建在哪些列上，这是设计阶段就要想清楚的问题。围绕判断标准，我习惯按优先级看三件事。

第一，过滤性。索引列的区分度越高，索引效果越好。说白了就是这一列有多少种不同的值，比如性别这一列只有两个值，区分度就极低，建了索引也很难把扫描范围缩小。与之相对，身份证号、手机号这种列区分度非常高，适合做索引。

第二，查询频率。索引不为“存在”而存在，而是为查询服务。一个字段哪怕区分度很高，但如果业务上根本没有按它筛选的场景，建了也是白白增加写开销。

第三，更新频率。如果一个字段经常被修改，如表里的状态字段频繁流转，那每一次UPDATE都可能要同步更新索引树，索引维护成本会显著上升。频繁更新的列要谨慎加索引。

另外还有一个很实用的参考值：区分度阈值。实践中我会算一下COUNT(DISTINCT column) / COUNT(column)，这个比值低于20%的列，一般不值得单独建索引。比如一张表有10万记录，某个字段只有1万种不同值，比率是10%，即使按它查询，索引能排除的数据量也有限，尤其是当它和其他条件组合的时候。

4.2 联合索引的列顺序：最左前缀原则

联合索引的列顺序设计，是索引优化里最容易被忽视、也最重要的一环。MySQL联合索引遵循最左前缀原则：一个联合索引(a, b, c)，查询条件里只有使用了a、或a和b、或a和b和c时，索引才可能被用到。跳过了前面的列直接查b或c，索引就无法发挥作用。

这背后其实是B+树的存储方式决定的。联合索引里的记录先按照第一列排序，第一列相同再按第二列排序，以此类推。

所以列顺序的安排原则可以归纳为两点：

• 把等值查询条件放在前面。等值条件下，索引树能精确定位到一个范围，发挥效率最高。
• 把范围查询的列放在后面。因为范围条件下，后面的索引列已经无法保持有序了，放在后面可以让前面的等值列继续发挥作用。

举个例子，业务上有两个高频查询：

SELECT * FROM order WHERE user_id = 123 AND status = 1; SELECT * FROM order WHERE user_id = 123 AND create_time BETWEEN '2024-01-01' AND '2024-01-31';

这时候联合索引设计成(user_id, status, create_time)和(user_id, create_time)哪种更好？要具体情况具体分析。但有一个基本原则：如果两个查询公共的等值列是user_id，那user_id一定要放在联合索引的第一位。在这个基础上，再考虑中间夹一个status是否会影响create_time的范围查询。

这里有个常见的坑：如果在联合索引中间列上加了范围条件，比如(user_id, create_time, status)，查询WHERE user_id = 123 AND create_time > '2024-01-01' AND status = 1时，create_time这个范围条件后面的status就用不到索引了，因为B+树在create_time范围区间内，status是无序的。这个现象叫“范围之后全失效”，严重时会让优化器直接放弃最优路径。

4.3 索引数量与写性能的平衡

索引不是越多越好，这是一个新手必须建立的观念。每次INSERT、UPDATE、DELETE操作，InnoDB不仅仅要维护聚簇索引，还要同步维护表上所有的二级索引。索引越多，写入时的维护量越大，磁盘占用也越高。

一个我常用的评估方式是：单表索引控制在5到6个以内，公用的查询模式用联合索引覆盖，避免同一个字段在不同索引里反复出现。

比如你有三个查询，分别要按status、status + create_time、status + type筛选，有些人会建三个索引(status)、(status, create_time)、(status, type)。其实没必要，因为后两个已经覆盖了第一个查询的需求。只保留两个联合索引就够了。

另外一个经常被问的问题：到底要不要给外键建索引？从数据完整性来说外键约束是要的，但如果你用了外键，MySQL会强制在对应列创建一个索引。所以很多DBA的实践是业务上自己管理关联关系，不用外键约束，避免MySQL隐式建索引导致冗余。这个属于团队规范问题，没有绝对对错，但你要知道那个隐式索引是存在的。

5. 使用EXPLAIN验证索引命中情况

5.1 EXPLAIN关键字段解读

建完索引后，怎么确认SQL真的用上了？用EXPLAIN。

EXPLAIN SELECT * FROM student WHERE student_no = 'S123';

返回结果里最需要关注这么几列：

type字段表示访问类型，从好到差依次是system > const > eq_ref > ref > range > index > ALL。ALL就是全表扫描，这是你绝对不想看到的。range代表用索引做了范围扫描，ref是等值匹配的普通索引，const是主键或唯一索引的等值匹配，性能都非常好。看到ALL的时候基本可以断定这条SQL有问题。

key字段显示的是最终选用的索引。有时候你建了索引但这里显示NULL，说明这个查询没走索引。possible_keys会列出所有可能被用到的索引，key等于其中一个或几个，代表优化器从中选了一个。

rows字段是优化器预估的需要扫描的行数。这个数字越大，查询成本越高。Extra字段里如果出现Using filesort或者Using temporary，说明查询涉及排序或分组没有走索引，这个也需要重点排查。

一个常见的场景是：同一个SQL，数据量小的时候EXPLAIN结果可能显示走全表扫描更快，优化器就不选索引了。这不一定代表索引失效，因为几百行数据的全表扫描确实比走索引快。这种时候别急着给SQL加FORCE INDEX，先确认是不是数据量测试环境跟生产环境差异导致的。

5.2 常见的索引失效场景汇总

以下这些场景我全部在真实环境里踩过，整理成表格方便对照。

这里面最容易被忽略的是隐式类型转换。比如表里phone字段是varchar类型，但查询传入的是一个数字常量，MySQL会把字段转换成数字再比较。一旦对索引列做了转换，就和函数操作一样，索引直接失效。我见过很多线上慢查询都是因为类型不匹配导致的，排查半天才发现是少了一对引号。

还有一个高阶但重要的场景：IN和OR在很多情况下是可以走索引的，优化器会转成range访问方式，不必一看到OR就紧张。真正要注意的是OR混用了无索引列，那可能直接拖垮整个查询。遇到这种SQL，要么给关联列补索引，要么拆成两个SQL再用UNION串起来。

5.3 实际优化案例

举一个我最近处理的案例。某业务模块有个查询，表里有一千多万行数据，用户反馈页面响应特别慢。

SELECT order_id, amount, status FROM payment_order WHERE merchant_id = 1234 AND pay_time >= '2024-06-01' AND pay_time < '2024-07-01' ORDER BY amount DESC;

最初的索引是(merchant_id, pay_time)。EXPLAIN显示type为range，rows约20万，Extra里出现了Using filesort。问题在最后一行的ORDER BY amount DESC，排序字段amount不在索引里，MySQL只能先把筛选出来的20万行加载到临时内存排序，这个排序就是慢的根源。

我当时的优化思路分两步。第一步，观察业务对amount排序的需求是不是必须的。确认肯定要做后，我把联合索引改成了(merchant_id, pay_time, amount)。这样索引里已经按merchant_id和pay_time做了范围筛选，筛选完成的结果集中amount字段依然是排好序的，Using filesort直接消除。

改完之后的EXPLAIN结果，type还是range，但Extra里不再有Using filesort，查询耗时从原来的2秒多降到了300毫秒以内。

这里有一个很微妙的点：联合索引(merchant_id, pay_time, amount)中，pay_time是范围条件，amount排在范围条件后面，为什么还能利用索引排序？因为优化器在这里并不需要利用amount做筛选，而是要利用它做排序。即便范围条件让amount无法用于过滤，但索引本身留在扫描范围内的记录已经按amount排序了，所以排序是可以直接使用的。这个细节非常值得记下来，很多人理解“范围之后全失效”的时候过度泛化，导致放弃了合法的排序优化空间。

这个案例总结下来就一句话：EXPLAIN看到Using filesort，优先想怎么把排序字段加进索引里，让索引替代排序。

6. 我的经验与小技巧

6.1 创建索引时容易忽略的事情

几个容易踩的细节在这集中说一下。

字符串索引要控制长度。如果索引列是VARCHAR(255)，B+树里每个索引项都要占用空间，页能容纳的索引项变少，树的高度就可能增加。对值域较长的字段，比如URL、备注文本，可以考虑用前缀索引，比如INDEX idx_url (url(64))。但要注意，前缀索引无法使用覆盖索引来避免回表，因为叶子节点里存的是前缀而不是完整值。

主键最好用自增或趋势递增的值。这是InnoDB聚簇索引的结构决定的。如果主键是随机字符串，每次插入新行时，B+树为了维持有序性可能要频繁移动数据页，产生页分裂，性能会明显下降。用自增主键时，新记录总是插在末尾，顺序写效率最高。这也是为什么很多生产表会额外加一个自增id作为主键，而不是直接用业务编号。

不要在大字段上建索引。除了前面前缀索引的问题，大字段意味着索引树更大，缓存命中率更低。

备份与迁移的排序影响。有些DBA会用ALTER TABLE ... ORDER BY ...试图改变表的物理顺序，但需要注意的是在InnoDB里，这张表的物理顺序实际由主键决定，ALTER TABLE之后的排序效果会被后续插入覆盖掉。不要指望这个命令做索引优化。

6.2 索引维护与监控

索引不是建完就一劳永逸的。业务随着时间推移，查询模式会变，原来高效的索引可能慢慢失去价值，而新出现的慢查询可能缺一个索引。

我习惯每季度做一次索引巡检，流程大致是三步。第一步，开启慢查询日志，找到出现频率最高的慢SQL。第二步，对每条慢SQL执行EXPLAIN，看type、rows、Extra这几个关键指标。第三步，结合业务判断是加索引、调整联合索引列顺序、还是重写SQL。

还有一个常见动作是清理无用索引。不知道某个索引还有没有在用，可以借助performance_schema里的table_io_waits_summary_by_index_usage表来统计索引使用次数。长期为0的索引就要考虑删掉，减少写维护成本。

这里提醒一句：删索引之前先在测试环境跑一轮压测，确认查询性能没有回退。因为优化器的选择有时候并不完全可预测，尤其是查询条件组合特别复杂的场景。

最后分享一个小技巧。如果你经常要排查一条SQL为什么会走错索引，可以试试EXPLAIN ANALYZE（MySQL 8.0+），它能真实执行SQL并返回每一步的实际耗时和行数，比单纯看预估的EXPLAIN更直观。我第一次用的时候还挺震惊的，原来优化器对行数的预估在某些复杂场景下会偏差这么大，靠猜不如直接实测。

索引这个知识点，理论是一层，实践是另一层。把B+树结构、聚簇索引、最左前缀、回表与覆盖索引这几个核心概念吃透，再配合EXPLAIN反复验证自己的判断，慢查询优化基本就拿下大半了。