聊到数据库性能优化，十有八九到最后都会落在索引上。但索引不是“建了就快”这么简单，它背后是一整套数据结构与算法的权衡：为什么InnoDB非要用B+Tree？联合索引的最左前缀到底怎么理解？where a and b这种条件应该怎么建索引？排序为什么经常出现Using filesort？哪些写法会让索引直接白建？这篇文章就把MySQL索引的底层结构、查询算法和真实建索引经验一起讲透，适合刚把MySQL装好准备建第一个表的同学，也适合在线上被慢SQL折腾了一整夜、想彻底搞明白“为什么没走索引”的同行。
1. 为什么MySQL最终选型B+Tree：从“没索引”说起
1.1 没有索引的日子：全表扫描的真实成本
假设你在一张2000万行的用户表上执行select * from users where phone = '13800138000'，在没有索引的情况下，MySQL只能从聚簇索引的第一个叶子页开始，沿着叶子节点的链表一路把整棵B+Tree的叶子页读一遍，逐行匹配phone字段等于目标值的记录。磁盘顺序读虽然快，但2000万行数据意味着动辄几百MB甚至上GB的IO量，单次查询轻松超过秒级。线上接口如果每天都跑这种SQL，数据库的磁盘IO和CPU基本都会被拖垮。
索引的本质是给数据做一份“快速定位”的目录。数据库把字段值和对应的存储位置组织成另一种结构，查询时先查目录再查数据，把扫描范围从全表缩小到几条记录。但目录结构不能乱选，它要同时满足等值查询、范围查询、顺序遍历、插入删除稳定这几个要求，还得能在磁盘上高效工作。随便拿一种内存里好用的数据结构过来，未必适合数据库的磁盘IO模型。
1.2 哈希、AVL树、红黑树、B-Tree为什么都没上位
很多人第一次学索引时会想：既然HashMap查找是O(1)，为什么不用哈希做索引？单纯等值查询哈希确实最快，但order by phone、phone > '13000000000'这类范围查询就彻底废了，因为哈希表里的数据分布是无序的，压根没法做有序遍历。所以哈希索引只能在Memory引擎和InnoDB的自适应哈希索引场景下作为补充，不能当主力。
平衡二叉树（AVL树）和红黑树的问题更典型：它们每个节点存储的数据太少，树高随着数据量增长增长得很快。2000万数据量下红黑树高度接近50层，每查一次就要在磁盘上做几十次随机IO，而一次随机IO的耗时要几十上百微秒起步。树本来是为了减少比对次数，但节点之间的指针跳跃在磁盘场景下是致命的。B-Tree已经把每个节点扩展成一块能存几十上百个键的页，可它还有个毛病：非叶子节点也存完整数据行，导致同一块16KB的页能存放的键数量明显变少，分支因子变小，树照样会变高。
B+Tree把这些坑全规避了：非叶子节点只存键和指针，叶子节点才存数据，一排叶子节点通过链表串联。这样根节点能塞下上千个键，树高在千万级数据下依然只有3层左右；范围查询直接顺着叶子链表往后扫，不用来回跳节点；插入和删除也只要局部调整叶子页。MySQL最终把B+Tree作为InnoDB和MyISAM索引的默认结构，不是偶然，而是拿实际IO模型和查询特点一一比对后的结果。
刚开始学索引的同学最容易忽略“磁盘IO次数”这个概念。每一个树节点的读取，都对应至少一次物理IO。树的高度就是查询需要碰盘的次数，所以树越矮，查询越快，这也是B+Tree被选中的最本质原因。
2. B+Tree究竟长什么样：页、三层结构、聚簇索引与回表
2.1 16KB一页，三层树能存多少数据
InnoDB默认把数据按16KB一个页来组织。我们把一个16KB页当节点，页内可以存放很多“键+指针”的组合。假设主键是8字节的bigint，指针占6字节，组合之后14字节，一个根页大约能放16 * 1024 / 14 ≈ 1170个条目。如果是三层B+Tree，根节点下面有1170个中间节点，中间节点再各指向1170个叶子页，最多就能拥有1170 * 1170 ≈ 137万个叶子页。
假设业务表平均一行数据是1KB，那一个叶子页能放16行，总数据量约137万 * 16 ≈ 2190万行。换句话说，2000万级的表，只要走主键查询，三次磁盘IO之内就能定位到目标行；如果走辅助索引，辅助索引树可能也是三四层，再加一次回表，最坏也就五六次IO。这个量级和全表扫描动辄扫描几十万页的IO完全不是一个概念。每次面试被问到“为什么说B+Tree适合数据库”，这套计算过程就是最好的回答。
2.2 聚簇索引和辅助索引：主键索引与唯一索引的本质区别
InnoDB里数据行本身就被锁在“以主键为键”的B+Tree里，这棵树的叶子节点存的是整行完整记录，它叫聚簇索引。没建主键时，InnoDB会找第一个不包含null的唯一索引来当聚簇索引；实在都没有，就在内部生成一个6字节的rowid。很多新人会忽略这个机制，实际排查表结构时一旦发现没有主键，就要高度怀疑是不是InnoDB偷偷加了不可见的rowid，这会带来数据页布局不可控、变更管理混乱等一系列麻烦。
辅助索引的叶子节点存储的并不是整行数据，而是“索引列的值+主键值”。比如你在phone列上建了idx_phone，查询辅助索引定位到phone后，拿到的其实是对应的主键id，必须再用这个id回聚簇索引里捞一次完整行，这个过程叫回表。MyISAM则是另一套思路，它的索引和数据文件分离，所有索引的叶子都只存“记录的物理地址”，不管是不是主键，查完都必须回到数据文件取行。所以在MyISAM里，主键索引和普通索引的检索过程没有本质差异。
那主键索引和唯一索引有什么区别？主键索引可以理解为唯一索引加上“非空且数据按它物理存储”的约束。唯一索引只保证列值不重复，数据仍然按主键聚簇排列。如果一张表只有主键索引，查询大多走主键，物理顺序和逻辑顺序一致，范围读取非常顺；如果业务想通过普通索引查询，却只需要select主键id，覆盖索引就可以免去回表这个步骤。
2.3 覆盖索引：干脆不回表
举一个我经常举的例子：select id, name from t where name = '张三'，如果表上只有idx_name(name)索引，流程是：先查idx_name，找到主键id，再回聚簇索引拿name字段。如果把索引升级成(name, id)联合索引，那么辅助索引的叶子节点里本来就有name和id，查询需要的数据在索引树里全都有，MySQL根本不用回表。EXPLAIN里会看到Using index，这就是覆盖索引生效。
覆盖索引的价值不只是少了一次回表，更关键的是辅助索引的叶子页通常比聚簇索引页能容纳更多记录，扫描同样范围内的记录时，覆盖索引带来的IO量要少得多。高并发场景下优化一条慢SQL，如果能把它改成覆盖索引查询，效果往往比增加内存buffer还要明显。所以设计索引时，可以多看一眼select列表里到底需要哪些字段，能不能全部收进索引里。
3. 联合索引、where a and b怎么建、排序怎么做：从实战入手
3.1 最左前缀到底是怎么来的
联合索引本质上仍然是排序后的B+Tree，只是排序规则变成了“先按第一列排，第一列相同再按第二列排，依此类推”。组合索引(col_a, col_b)的数据顺序，可以理解为字典序：所有记录先按col_a分组，每组内部再按col_b有序。查询条件如果只带了col_b，MySQL没办法在这个两列排序的结构里直接跳过第一列定位，所以最左前缀原则的根因是“索引排序规则”本身。
这带来两个直接结论：
- 查询必须从第一列开始用，联合索引(a,b)能支持
where a = ?、where a = ? and b = ?，不能直接支持where b = ?。 - 范围条件右侧的列会失效。比如
where a > 100 and b = 1，MySQL用联合索引定位到a>100的记录后，b=1这个条件无法再利用索引快速过滤，因为a大于100的记录内部b并不是全局有序的。
这里的边界判断很关键。我记得有次排查线上慢查询，SQL是select * from t where create_time >= '2024-01-01' and status = 1，联合索引建的是(create_time, status)。结果EXPLAIN显示走了索引，但rows扫描了12万行。问题就出在create_time是范围条件，它把status的过滤能力给掐断了。这种情况应该把等值条件的status放在联合索引前面，范围条件的create_time放后面。
3.2 一条SQL告诉你怎么建联合索引
热搜里有个典型的提问场景：“where a and b，应该怎么建索引”。我直接用一个订单表例子说明。假设SQL是：
select * from order_table where user_id = 2024001 and order_status = 2 order by create_time desc limit 20;如果按“第一个出现的条件”建(user_id, order_status)，确实能走索引，但结果排序字段create_time不在索引里，大概率出现Using filesort。更稳的做法是直接建联合索引：
alter table order_table add index idx_user_status_time (user_id, order_status, create_time desc);这样等值条件的user_id、order_status负责快速定位，create_time负责保证排序，查询不需要额外排序，还能配合limit做索引扫描。如果业务里还有单独的where user_id = ?查询，idx_user_status_time左边的前缀也能直接覆盖，不需要再重复建一个单一user_id索引。也就是说，在建索引前多问一句“这个索引还能顺带服务哪些查询”，比看到一个条件就加一个索引聪明得多。
建联合索引的顺序，业内有个很实用的判断顺序：先放等值条件列，再放范围条件列，最后放排序列。每多一个等值列，前面的过滤粒度就细一层，索引越窄，扫描量越小；范围列一旦用上，右侧的等值条件就废了；排序列放进来，能省掉filesort，同时还能避免排序内存压力。
3.3 ORDER BY和GROUP BY怎么蹭索引
MySQL排序最怕Using filesort：当排序结果放不进sort_buffer_size时，要把中间结果先落盘成临时文件，再用归并排序多轮合并，这个过程的IO和CPU开销都非常可观。索引天然有序，所以ORDER BY如果和索引列顺序完全匹配，就能跳过排序直接按索引顺序读取。
能用索引排序需要满足两个关键条件：排序字段顺序必须等于索引列顺序，前导列如果是等值条件，则从第一个非等值列开始排也行；排序方向要么全升序要么全降序，MySQL 8.0虽然支持索引降序扫描，但混搭的方向还是很难吃上索引。GROUP BY本质是分组排序，原理和ORDER BY一样，只要分组字段也是索引前缀，就能直接按索引分段扫描，避免建临时表。
这里我必须提醒一句：很多慢SQL不是死在where条件，而是死在排序和分组。建索引前一定要习惯性地看一眼EXPLAIN的Extra字段，只要看到Using filesort或者Using temporary，就要往“是否可以用索引替代排序”的方向想。我见过太多次，一个排序字段加入联合索引后，查询时间从800毫秒直接掉到20毫秒的例子。
4. 索引失效的几种常见场景，以及EXPLAIN排查实录
4.1 常见的五个失效写法
第一个是函数操作。where DATE(create_time) = '2024-01-01'这类写法，哪怕create_time上有索引也用不上，因为索引树里的键值是原始日期，不是DATE函数算出来的结果。正确写法是改成范围比较create_time >= '2024-01-01 00:00:00' and create_time < '2024-01-02'，把函数剥离到等号右边。
第二个是隐式类型转换。比如phone列是varchar，SQL写成where phone = 13800138000，MySQL在比较时会把varchar隐式转成数字，索引列上相当于套了一层转换函数，索引自然失效。手机号字段尤其容易踩这个坑，字符串查数字没问题，数字查字符串就有风险。
第三个是模糊匹配前导通配符。where name like '%张'没法用idx_name，因为尾部通配符张%才可以走前缀匹配；前导通配符要求扫描全部字符串前缀，相当于退化成全索引扫描，数据和行数一多照样慢。
第四个是OR连接了非索引条件。where name = '张三' or age < 18，如果age不是索引，优化器为了保证结果集完整，往往放弃name索引直接全表扫描。能用UNION拆开两条等值查询，或者给两个条件都建上索引，就能让优化器重新考虑执行计划。
第五个是优化器认为全表扫更便宜。这不是语法问题，但经常被人误认成“索引失效”。小表数据就一页时，全表扫描的IO成本可能比走索引回表还低，优化器就把索引甩了。遇到这种情况，可以先看表有多少行，再做analyze table更新统计信息，再重新EXPLAIN，很多时候统计信息过期才是罪魁祸首。
4.2 用EXPLAIN判断有没有真走索引
排查索引问题，EXPLAIN是第一工具。别只看key字段有没有值，要看完整链路。type字段从好到差大致是：const、eq_ref、ref、range、index、ALL。const代表主键等值查询，直接定位一条；eq_ref常见于join的主表关联；range表示索引范围扫描；index表示虽然用了索引但扫了整棵索引树；ALL就是最常见的全表扫描，看到ALL基本意味着没吃到索引红利。
rows字段也很关键。它表示优化器预估需要扫描的行数，如果rows和表总行数差不多，即使key有值，也可能是在做索引全扫描。Extra字段里出现Using index是好消息，出现Using index condition是走了索引下推，出现Using filesort就要回过来查排序，出现Using temporary基本说明查询临时表逃不掉了。对慢SQL逐字段比对，比直接上来甩一句“没走索引”有说服力得多。
我自己的习惯是，把慢查询日志或者测试环境里的可疑SQL，一个个用EXPLAIN跑一遍，然后把type、rows、Extra三列单独摘出来做对比。同一张表的不同查询，哪个字段吃索引，哪个字段在拖后腿，一眼就清楚。改完索引或SQL结构后，还要重新EXPLAIN一遍，确认rows是下降而不是上升，再决定要不要上线。
4.3 InnoDB的隐藏优化：自适应哈希索引、MRR、索引下推
讲完失效，再提三个能帮索引“加分”的底层机制。InnoDB自适应哈希索引（AHI）会根据高频等值查询自动把部分索引页面映射到哈希表，加速等值访问，但它不归用户管，完全是引擎内部基于访问模式生成的。如果线上有大量等值查询且内存足够，能看到和innodb_adaptive_hash_index相关的状态数值在涨，说明索引命中率不错。
MRR全称Multi-Range Read，专门解决回表随机IO问题。辅助索引扫描出大量主键id后，如果直接回主键树随机读，每次都跳页，代价很高；MRR会先把id收集起来排序，再按聚簇索引的物理顺序批量回表，把随机IO变成顺序IO。这个机制对select * from t where date_col between ...这类返回大量行的场景特别有用。
索引条件下推（ICP）是MySQL 5.6引入的，意思是把部分索引列的过滤下推到存储引擎层。比如联合索引(a,b)，SQL里where a = 1 and b like 'x%'，没有ICP之前，引擎只会用a筛选出记录回表再过滤b；有了ICP，直接在索引内判断b的前缀，减少回表次数。日常调优不用手动开启，MySQL默认就是开启状态，但要知道EXPLAIN里的Using index condition是在帮你于索引层提前干活。
5. 真实工程里的索引设计经验：从建索引到事务一致性
5.1 索引数量不是越多越好
索引能加速查询，但每个索引都是一棵独立的B+Tree。写入时除了更新数据页，所有索引都要同步维护，插入一个普通索引，数据页就多一次写放大；索引数量一多，写入性能会肉眼可见地下滑。另外索引也占磁盘，数据量大的表上一个多余的索引可能占用几十GB空间。
所以我建议定这样几条规矩：能复用联合索引的前缀，就不要单独建单列索引；一张表超过五六个索引要开评审会；线上删除索引前，先观察一周相关慢SQL，确认没有查询依赖再动手。压测数据库写入性能时，也先把冗余索引清掉，不然测试结果根本没有参考价值，最后写瓶颈到底出在锁竞争还是索引维护，都分不清楚。
反直觉但真实：索引帮助的是少数高频查询，拖累的是每一次写入。宁可为3个高频查询建3个精准联合索引，也不要为了10个低频查询堆10个单列索引。
5.2 聚簇索引排序与主键选择：为什么随机主键坑写性能
InnoDB的聚簇索引把数据按主键物理顺序排列，主键最好是有序递增的。自增id作为主键时，新插入的行基本都追加在B+Tree最右侧的叶子页里，页分裂少，写入路径有规律；UUID这类随机主键会让每次插入都落在不同的中间位置，触发大量页分裂、页重组和碎片，写入一多，磁盘随机写和锁竞争直接加重。
从查询角度看，随机主键还会把聚簇索引的叶子页打散，导致范围扫描和回表的局部性变差。我之前接手过一个业务表，主键从自增int改成了业务UUID，压测下来写入TPS掉了近三分之一，后来回滚才恢复。除非你有强烈的分库分表或数据合并诉求，否则默认保持自增主键是更稳妥的路线。
5.3 MVCC与索引的配合：事务隔离不是靠锁硬扛
MySQL的事务处理并不只是在索引上加一堆排他锁这么简单。InnoDB每个聚簇索引行里都隐藏着DB_TRX_ID和DB_ROLL_PTR两个字段，前者记录最近修改这行的事务id，后者指向undo log里的旧版本链。当普通查询执行时，会拿当前事务id和这些版本信息对比，从而隐藏未提交版本，这就是MVCC多版本并发控制的大体机制。
索引在这种机制下承担的不只是定位，还担负了版本可见性判断的基础。两个事务并发修改同一行时，锁住的依然是索引记录和间隙，但读用快照，写用当前读，读写之间才能尽量错开。这也是为什么隔离级别从读已提交切到可重复读时，索引相关的间隙锁范围会变大，并发写可能减弱的根本原因。面试聊到“索引”和“事务”，能把这层关系讲清楚，说明不是背概念，而是真调过并发的坑。
5.4 一张速查表收尾：建索引前先问这六个问题
最后给一张我在评审索引时用的自检表，建任何索引前都过一遍：
| 问题 | 判断思路 |
|---|---|
| 这个索引服务于哪几条高频SQL？ | 没有对应业务的索引都是负担 |
| 等值条件列和范围条件列各是什么？ | 等值列放前面，范围列放后面 |
| 需要order by/group by的列在索引里吗？ | 排序列尽量进索引，避免filesort和临时表 |
| 查询能覆盖索引吗？ | select的列都在索引里，回表成本为0 |
| 列的区分度够高吗？ | 区分度低于10%的字段慎建单列索引 |
| 写入放大能不能接受？ | 写多读少的表，索引能少就少 |
这张表不一定覆盖所有极端场景，但对绝大多数OLTP系统已经够用。索引不是越复杂越好，也不是建了就一劳永逸；它是在读性能、写性能、磁盘开销之间做取舍。搞清楚底层为什么是B+Tree、联合索引为什么讲最左前缀、什么情况下索引会失效，远比套几个建索引模板重要。
最后说一个我自己的习惯：线上加索引前，先在测试环境用相同数据量复现慢SQL，把EXPLAIN前后的type和rows变化记录到变更单里，上线后隔天再看一批慢日志。索引的收益不是建的时候算出来的，是用一段时间的慢查询量衡量出来的。B+Tree的树高、页大小、回表次数这些底层概念，看着像理论，真到排查慢SQL和设计表结构的关键时刻，每一条都能救命。
