1. 从一次线上慢查询说起：索引到底在解决什么问题
这一章我们来到 MySQL 学习路线上一个真正决定“快慢”的节点——索引。前面几章都在聊建库、建表、写 SQL，数据量几千条的时候怎么查都行，等你真正面对线上几十万、几百万行的表，一条查询从毫秒变成几秒，问题一大半出在索引上。我最早接手一个订单表优化任务时，SQL 看起来没有任何问题，该返回的字段也不多，但就是慢。后来发现 user_id 上根本没有索引，表里已经攒了 300 多万行，每次查询都在做全表扫描。给这一列加完索引，同样的条件从 2.3 秒降到了 3 毫秒。那一刻我才真正理解“索引是什么、索引解决什么问题”。
1.1 没有索引时 MySQL 在做什么
在 EXPLAIN 执行计划里，最扎眼的通常是 type = ALL，这个值的意思就是全表扫描。MySQL 把整张表从头到尾读一遍，从第一行开始挨个判断 WHERE 条件，符合条件就返回，不符合就跳过，直到最后一行结束。
数据量小的时候这个成本不明显，一万行、几万行的表，InnoDB 一次 IO 读一个页（默认 16KB），整张表可能只占几十个页，几十次 IO 就把全表扫完了。可当表膨胀到几百万行、占好几个 GB，全表扫描要读的页数量就非常恐怖了。更要命的是，这些页在磁盘上的物理位置并不连续，大量随机 IO 会让机械硬盘处于来回寻道的状态，SSD 虽然好一些，但也经不住这种浪费。
全表扫描不是设计成“坏人”的，它是 MySQL 优化器在没有任何可用索引时的兜底方案。问题是它和你在实体书里找一段话完全一样：必须从头翻到尾，中途无法跳跃。你翻一本 500 页的小说，找某个词的时候只能一页一页过，但如果这本书最后有索引，你直接翻到对应页码就行。索引节省的不是 10% 的力气，而是几个数量级的时间差距。
1.2 索引的本质是什么
索引是一种额外的数据结构，它把某一列或多列的值按照一定规则组织起来，并在内部保存指向真实数据行的“地址”或“主键值”。MySQL 拿到查询条件后，先到索引里去定位，而不是直接扫全表，定位到目标记录之后再回原表取完整数据。
我经常把索引类比成新华字典的部首检字表：正文部分按拼音排序，你要查“索”字，先翻拼音 suǒ 对应的页码，顺着页码直接翻到那一页，而不是从第一页开始逐页找。MySQL 的普通二级索引就是这个思路，而主键索引更彻底——数据本身按主键顺序存放，相当于整本书的正文就按页码排好了，你报页码直接翻，连二次定位都不需要。
理解这一点之后，很多直觉就能建立起来：索引不是魔法，它只是用空间换时间，用一部分磁盘空间存储一个“目录”，让 MySQL 能快速定位目标。代价是每次插入、更新、删除数据时，都要同步维护索引树，所以索引不是越多越好，这个话题后面我会单独展开。
1.3 一个最简单却能直观感受的验证实验
与其听我说，不如自己动手验证一把。随便找一张数据量在几十万行以上的表，先看一条查询的执行计划：
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';没建索引时，type 一栏基本是 ALL，rows 是整表行数。然后创建一个普通索引：
ALTER TABLE users ADD INDEX idx_username (username);再执行一次同样的 EXPLAIN，你会发现 type 变成 ref，rows 迅速降到一个很小的数字。别小看这一步，这是建立索引直觉最有效的方式。我见过很多人背了一堆索引理论，遇到真实慢查询还是懵，原因就是没亲手对比过优化前后的执行计划。
2. 为什么选了 B+树：索引底层数据结构拆解
很多人会背“InnoDB 用 B+树”，但真问一句“为什么是它”，往往就答不上来了。这节把这个问题拆开，从数据结构演进的角度讲清楚，以后你再看到任何关于索引的讨论，都不会被术语唬住。
2.1 哈希索引为什么不够用
最容易想到的加速结构其实是哈希表。对索引列做哈希运算，得到一个定长的数字，再根据数字定位到对应的桶。做等值查询时，一次哈希计算加一次数组定位就能命中目标，理论时间复杂度接近 O(1)，速度远快于 B+树。
但哈希表的致命问题是无法处理范围查询。WHERE age > 18、BETWEEN 一段日期、ORDER BY create_time，这些操作依赖数据之间的顺序关系。哈希函数会把有序的输入值打散成看似随机的输出，哈希表内部也没有“按序遍历”的能力。你只能一个一个枚举所有值再做判断，等于退化回全表扫描。
所以 InnoDB 的逻辑索引结构从来不是哈希表，但它内部确实有一个自适应哈希索引（Adaptive Hash Index），这是数据库在内存里为高频热点值自动建立的加速结构，不需要你手动创建，也查不到逻辑上的索引定义。它只是 B+树之上的锦上添花，解决不了范围查询问题。讨论真正可控的索引结构，还是得回到 B+树。
2.2 树形结构演进：二叉树、红黑树、B树、B+树
先看二叉搜索树。假设主键字段是自增 id，数据插入时按顺序增长，二叉搜索树会极不均匀地长成一条链表，树高等于数据行数。查最后一行主键，理论上要访问 N 个节点，每次节点访问都对应一次磁盘 IO，这显然不能接受。
红黑树是平衡二叉搜索树，树高能稳定在 O(logN)。对于几百万行数据，树高大概二十多层，查询一条数据极限情况要访问二十多个节点。每个节点一次磁盘读，二十多次 IO 对数据库来说是灾难级别的成本。虽然节点有机会被缓存在内存里，但只要某一层的数据不在 buffer pool，就要真真实实做一次磁盘 IO，冷数据场景照样扛不住。
B树的关键改进是让每个节点同时存储多个键和多个孩子指针，树从“瘦高”变成“矮胖”。同样数据量，B树通常只需要三四层。MySQL 在 B树基础上做了另一层改进，使用 B+树，差别在于：
- B+树的非叶子节点只存放索引键，不存放数据；
- 全部数据都存在叶子节点上；
- 叶子节点之间用双向链表串联。
别小看这几个差异。非叶子节点不存数据，意味着一个固定大小的页面能塞进更多索引键，树高进一步下降。16KB 的页如果全放 bigint 主键和指针，大约能放一千多个键，三层就能覆盖上千万行。把数据集中在叶子节点，所有查询都必须走到叶子层，查询链路稳定，没有“有时候快有时候慢”的问题。叶子节点串成链表之后，范围查询和排序变得异常方便：找到起点，顺着链表往后扫就行，磁盘顺序读的效率远高于随机 IO。
2.3 三层 B+树到底能覆盖多少数据
这是我最喜欢算的一笔账。InnoDB 默认页大小是 16KB，假设主键是 bigint，占 8 字节，加上指针之类的开销按 6 字节算，每个索引项大约 14 字节。那么一个非叶子页大约能装 16384 / 14 ≈ 1170 个索引项。
再假设一行数据加上事务字段大约 1KB，一个叶子页能装 16 行左右。两层 B+树能覆盖的数据量是 1170 × 16 ≈ 1.8 万行；三层 B+树是 1170 × 1170 × 16 ≈ 2190 万行。
也就是说，一张两千多万行的表，绝大多数查询只通过三次 IO 左右就能定位到目标页。再加上 InnoDB 的缓冲池缓存机制，真实访问成本还能进一步降低。这也是为什么几百万行的表建了索引能从秒级降到毫秒级，结构优势决定了数量级差距。
3. 索引分类与建索引的姿势：主键、二级、复合、全文
聊完底层结构，再看 MySQL 索引的分类就清楚多了。语法层面常见的索引类型有主键索引、唯一索引、普通索引、复合索引、全文索引。它们不是简单的并列关系，理解的关键在于 InnoDB 的数据组织方式。
3.1 聚簇索引与二级索引：两套完全不同的查找逻辑
InnoDB 表本质上是按主键顺序组织的一棵 B+树，这个索引就叫聚簇索引，叶子节点上存放的是整行数据。你建表时指定 PRIMARY KEY，MySQL 就会用它做聚簇索引；如果你不指定主键，InnoDB 会找一个非空唯一索引当主键；再找不到，内部会生成一个隐藏的 6 字节主键。所以主键索引永远存在，只是你感知不到而已。
除了主键之外的其他索引，都叫二级索引（也叫普通索引、非聚簇索引），叶子节点存储的不是整行数据，而是对应行的主键值。查询流程是先通过二级索引找到主键，然后再去聚簇索引里回表取整行数据。这个“回表”动作是性能优化的关键概念，后面会反复提到。
正因为这个结构，主键设计变得非常重要。主键最好用自增 id、雪花 id 这类有序且单调的值，尽量避免随机 UUID 字符串。原因很朴素：聚簇索引的数据物理上按主键排序存放，随机主键会频繁触发页分裂和页重组，插入性能大幅下降，表碎片也多。这是很多人在建表阶段就踩的坑，等线上数据量大了一查 EXPLAIN 才后悔。
3.2 普通索引、唯一索引、复合索引的取舍
普通索引只承担加速查询的任务，允许重复值，适合对非唯一列做条件筛选。唯一索引在普通索引基础上多了唯一性约束，索引列不允许重复值，适合业务上本身就唯一的字段，比如身份证号、手机号、支付流水号。用唯一索引还有一个隐藏收益：数据库层面帮你防住重复数据，应用层可以省掉一部分去重逻辑，减少一次额外查询。
复合索引是日常优化中最重要的索引形式，也叫联合索引。它可以在一个索引里组织多个字段，比如 (user_id, status, create_time)。查询条件同时包含这些列时，有机会一次索引定位到位，而不是建三个单列索引各管各的。单列索引的尴尬在于，WHERE 同时出现两个条件时，优化器大概率只能选择其中一个索引，另一个条件只能回到表里过滤，效率和复合索引完全不在一个量级上。所以建索引时，重点思考的是“哪几个字段经常一起出现在查询里”，而不是“哪个字段经常出现”。
3.3 全文索引与倒排索引
全文索引用的是倒排索引，和 B+树是两套体系。它先把文本内容做分词，记录“词到文档”的映射关系，所以你用它执行 LIKE '%关键词%' 这种模糊搜索时，效果远好于普通索引。普通索引面对前导通配符的 LIKE 基本无能为力，只能全表扫，而全文索引用词表快速定位文档，响应速度能提升几个档次。
MySQL 的全文索引原理和专业搜索引擎一致，但功能相对简单，适合中小规模场景。如果文本量很大、分词需求复杂，我更建议交给专门搜索引擎去处理，别硬让 MySQL 扛高并发全文检索。另外记得，全文索引和普通索引不是替代关系，它们解决的是不同类型的问题。
4. 必须吃透的命中规则：最左前缀、回表、覆盖索引
建了索引却不生效，比没有索引更让人难受。很多朋友问“我明明加了索引，为什么 EXPLAIN 里还是 ALL？”答案通常出在这一节讲的规则上。索引生效的判定并不玄学，核心是 B+树如何利用索引列的顺序来“裁剪”搜索范围。
4.1 最左前缀原则到底怎么理解
复合索引 (a, b, c) 在逻辑上相当于同时创建了 (a)、(a, b)、(a, b, c) 三套索引，查询条件必须包含第一个字段 a，索引才能被使用。原因是索引内部先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。你说的“按条件找数据”，本质上是在这棵排序树上定位起点和终点。
拿电话薄类比：电话薄按“姓氏、名字、中间名”排序，你想找姓“王”的人，顺着索引一定能精确翻到王姓区域。但如果只给一个名字叫“小明”，电话薄就无能为力了，因为“名字”不是第一排序键，你无法在整本字典序结构里直接定位。这就是最左前缀的底层逻辑。
所以设计复合索引字段顺序时，要把查询中最常出现的等值条件放在最前面。比如订单查询总是先定位 user_id，再筛 status 和 create_time，索引顺序 (user_id, status, create_time) 就明显好于 (status, user_id, create_time)。
4.2 回表与覆盖索引的代价差异
二级索引找到主键之后，还需要去聚簇索引取整行数据，这个过程叫回表。每回一次表，就是一次主键查找。如果查询命中了大量二级索引记录，回表次数会成倍放大。看起来只多了一小步，实际可能让查询从毫秒级变成几十毫秒甚至更差。
避免回表最直接的方式是覆盖索引。所谓覆盖，就是查询所需的字段全部包含在索引列里，MySQL 直接在索引页拿到数据返回，不需要再回表。比如 SELECT user_id, status FROM orders WHERE user_id = 10086 AND status = 'PAID'，索引 (user_id, status) 已经带着这两个字段，查询自然不需要回表。但如果 SELECT 里多选了一个 amount，而 amount 不在索引里，回表就无法避免。
日常优化里，我经常针对高频查询，把 SELECT 的列和 WHERE 的列组合成一个覆盖索引。这是性价比最高的优化手段之一，尤其适合读多写少的报表类查询。
4.3 什么时候索引会失效
几个高频失效场景必须刻在脑子里。第一，对索引列使用函数。WHERE DATE(create_time) = '2024-01-01' 看着很正常，但 MySQL 无法在 B+树上对一个函数结果做范围裁剪，它只能把索引列的值全部取出来算一遍再筛选，索引自然失效。正确写法是 create_time >= '2024-01-01' AND create_time < '2024-01-02'。
第二，隐式类型转换。手机号字段是 varchar，你写 WHERE phone = 13800000000，数字和字符串比较时 MySQL 会尝试把索引列转换为数字，导致索引失效。写这种条件时，老老实实加引号。
第三，LIKE 前导通配符。WHERE name LIKE '%张%' 无法利用前缀定位，索引失效；但 name LIKE '张%' 可以走索引，因为 B+树能通过前缀确定搜索边界。
第四，优化器主动放弃。如果字段区分度太低，比如性别字段只有两个值，任何一个值都能匹配表中 50% 的行，优化器评估后发现走索引要大量回表，不如全表扫干脆，于是放弃索引。这种场景不是 MySQL 傻，而是索引确实帮不上忙。
4.4 用 EXPLAIN 看真实执行计划
EXPLAIN 是排查索引问题的必修课。重点看四列：type 表示访问类型，system 到 const、eq_ref、ref、range、index、ALL 依次变差，看到 ALL 基本等于全表扫描；key 是实际使用的索引名，为 NULL 表示没走到索引；rows 是预估扫描行数，越小越好；Extra 里的 Using index 表示走了覆盖索引，Using filesort 表示需要额外排序，Using index condition 表示索引下推已生效。
一条经验：rows 从几十万变成几十，说明索引大概率已经生效；如果 Extra 里还有 Using filesort，说明 ORDER BY 的字段没被索引吃透。拿一次真实查询举例，EXPLAIN 输出显示 type = ref、key = idx_user_status_time、rows = 29、Extra 为空，那这就是一个非常健康的执行计划。
5. 一次订单查询优化的完整链路：从慢查询日志到索引调整
原理讲了一堆，不实操等于白看。这一节我以一个模拟的订单表为例，给出一条完整可复现的优化链路。表结构大概是这样的：
CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, status varchar(16) NOT NULL DEFAULT 'CREATED', create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;假设数据量 500 万行，运营反馈某个订单列表页面打开很慢。接下来按流程走。
5.1 开启慢查询日志，锁定嫌疑 SQL
MySQL 提供了慢查询日志，可以自动把执行时间超过阈值的 SQL 捞出来。排查时先用临时参数打开：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_output = 'TABLE';第一次排查不用纠结日志文件路径，log_output 设为 TABLE 后，慢查询会记录到 mysql.slow_log 表里，直接查这个表就能看到候选 SQL：
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 1;拿到慢 SQL 之后别急着改，先看两条信息：执行次数和单次耗时。频率高且耗时长的查询优先处理，偶尔一次的全表扫描可以先放着。真实环境里，我见过太多人花一整天优化一个一天只跑一次的报表查询，结果核心业务查询仍在跳脚慢，方向比努力更重要。
5.2 拿到 SQL 后先做执行计划“体检”
假设捕获到的 SQL 是：
SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id = 10086 AND status = 'PAID' ORDER BY create_time DESC LIMIT 20;先执行 EXPLAIN：
EXPLAIN SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id = 10086 AND status = 'PAID' ORDER BY create_time DESC LIMIT 20;大概率会看到 type = ALL，rows 接近 500 万，Extra 里有 Using filesort。两个信号同时亮红灯：没有可用索引，且排序要另起炉灶。500 万行全表扫描，再把结果在内存或临时表里排序，慢是必然的。
5.3 设计复合索引并验证效果
这个查询的模式非常典型：user_id 等值、status 等值、create_time 排序。按照最左前缀和“等值在前、排序在后”的规则，建立复合索引：
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);字段顺序的依据是：user_id 和 status 都是等值筛选，放前面；create_time 是排序字段，放最后。这样 MySQL 可以在索引内部就按 create_time 顺序取数据，Using filesort 直接消失。
优化完再看执行计划，type 会变成 ref，key 是 idx_user_status_time，rows 下降到几行，Extra 里不再有 Using filesort。我做过一模一样的实验，优化前 2.3 秒，优化后 30 毫秒以内。如果还想继续压榨，可以把 SELECT 的列也纳入索引组合，形成覆盖索引，不过要小心索引列太多带来的写开销。
这里提醒一句：建完索引不是终点。插入、更新数据时要同步维护索引树，写性能会有折损。对写多读少的表，索引数量必须克制，宁可让查询慢一点，也别把写入拖垮。
5.4 我习惯的优化后检查清单
每次做完索引优化，我都会按这个清单过一遍：
- 再看一次 EXPLAIN，确认 key、rows、Extra 三项符合预期；
- 跑一次真实业务请求，验证端到端延迟达标；
- 对比 buffer pool 命中率，索引冷启动时第一次查询可能仍偏慢，等数据进缓存后再评估；
- 持续观察慢查询日志，确认这条 SQL 不再回潮。
这样一轮下来，绝大多数索引盲区都能被扫平。别嫌流程啰嗦，线上系统出问题，最大的风险不是慢，而是你改了索引之后把别的查询带崩了。每一步都有验证，才能保证安全上线。
6. 我在生产环境积攒的索引实践经验
到了这一节，我不再重复教科书内容，讲点我在真实环境里积攒的规矩和教训。这些事踩过一次就明白，但提前知道了能少踩很多坑。
6.1 不建议建索引的场景
第一类是小表。几千行数据全表扫描的成本本来就极低，一个索引带来的查询加速微乎其微，反而增加了写入开销和磁盘占用。MySQL 优化器面对小表，也经常因为统计信息判断全表扫描更划算，直接放弃索引。
第二类是区分度极低的字段。性别、状态、是否删除这类字段，一个值就能覆盖表中大量数据。索引的目的是缩小搜索范围，如果一个值能匹配三分之一的表，那这个索引的“裁剪能力”就很弱，优化器大概率选择扫全表。
第三类是高频写入的流水表、日志表。这类表的瓶颈通常不是查询，而是写入。每次写入都要维护所有的二级索引，索引数量越多，写入放大越严重。我一般会把这类表的查询需求合并成少数几个复合索引，保住写入吞吐，牺牲部分查询弹性。
6.2 覆盖索引和冗余索引管理
覆盖索引是性能优化里的“作弊器”，但别上瘾。之前见过有人为了让一个报表查询走覆盖索引，把一个 16 个字段的表建了 8 个复合索引，结果写入时 MySQL 被索引维护拖到报警。每个索引都占用磁盘空间，每次写操作都要同步更新，成本是实打实的。覆盖索引只应针对高频核心查询设计，不能每来一个查询就加一个索引。
另外一个高频问题是冗余索引。已经有 (a, b) 联合索引，又单独建一个 (a) 索引。根据最左前缀原则，(a, b) 完全可以覆盖 (a) 的查询场景，单独的 (a) 就是白占空间的冗余索引。这种问题在业务快速迭代时非常常见，新需求加索引、没人删旧索引，时间一长索引数量失控。
我定期巡检时会用 sys.schema_redundant_indexes 这个系统视图，自动找出冗余索引，再逐个确认删除。注意不要盲目全删，有些单独的 (a) 可能是为了别的最左前缀场景，需要结合业务确认。
6.3 一些我在实际踩坑中养成的习惯
我给自己定了几条铁律，供你参考：
- 建索引之前，先确认问题真的出在索引，而不是 SQL 本身写得低效；
- 复合索引字段顺序，永远把等值查询字段放在排序和范围字段前面；
- 尽量让 ORDER BY 字段成为索引的一部分，消灭 Using filesort；
- 大表加索引，选业务低峰期，使用在线 DDL 方式操作，避免长时间锁表影响线上。
还有一个小技巧：如果某个查询已经不慢，但你还想再优化，先看能不能改造成覆盖索引，而不是一股脑加新字段。SELECT 列尽量限制在索引列范围内，每次查询都能少一次主键回表，积少成多，效果非常显著。
最后分享一个小习惯：每次做索引优化，先在暂存环境里用 EXPLAIN 对比优化前后的 rows 和 Extra，确认没副作用后再上生产。MySQL 索引优化没有银弹，但把 B+树结构、最左前缀、回表、覆盖索引这几个底层概念想透了，绝大多数慢查询都能找到明确解法。这一章到这里，索引的底层逻辑就算真正入门了。下一章可以继续沿着执行计划往下聊，也可以去研究事务和锁，看你对哪块更感兴趣。
