聊到数据库性能优化十有八九到最后都会落在索引上。但索引不是“建了就快”这么简单它背后是一整套数据结构与算法的权衡为什么InnoDB非要用BTree联合索引的最左前缀到底怎么理解where a and b这种条件应该怎么建索引排序为什么经常出现Using filesort哪些写法会让索引直接白建这篇文章就把MySQL索引的底层结构、查询算法和真实建索引经验一起讲透适合刚把MySQL装好准备建第一个表的同学也适合在线上被慢SQL折腾了一整夜、想彻底搞明白“为什么没走索引”的同行。1. 为什么MySQL最终选型BTree从“没索引”说起1.1 没有索引的日子全表扫描的真实成本假设你在一张2000万行的用户表上执行select * from users where phone 13800138000在没有索引的情况下MySQL只能从聚簇索引的第一个叶子页开始沿着叶子节点的链表一路把整棵BTree的叶子页读一遍逐行匹配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的页能存放的键数量明显变少分支因子变小树照样会变高。BTree把这些坑全规避了非叶子节点只存键和指针叶子节点才存数据一排叶子节点通过链表串联。这样根节点能塞下上千个键树高在千万级数据下依然只有3层左右范围查询直接顺着叶子链表往后扫不用来回跳节点插入和删除也只要局部调整叶子页。MySQL最终把BTree作为InnoDB和MyISAM索引的默认结构不是偶然而是拿实际IO模型和查询特点一一比对后的结果。刚开始学索引的同学最容易忽略“磁盘IO次数”这个概念。每一个树节点的读取都对应至少一次物理IO。树的高度就是查询需要碰盘的次数所以树越矮查询越快这也是BTree被选中的最本质原因。2. BTree究竟长什么样页、三层结构、聚簇索引与回表2.1 16KB一页三层树能存多少数据InnoDB默认把数据按16KB一个页来组织。我们把一个16KB页当节点页内可以存放很多“键指针”的组合。假设主键是8字节的bigint指针占6字节组合之后14字节一个根页大约能放16 * 1024 / 14 ≈ 1170个条目。如果是三层BTree根节点下面有1170个中间节点中间节点再各指向1170个叶子页最多就能拥有1170 * 1170 ≈ 137万个叶子页。假设业务表平均一行数据是1KB那一个叶子页能放16行总数据量约137万 * 16 ≈ 2190万行。换句话说2000万级的表只要走主键查询三次磁盘IO之内就能定位到目标行如果走辅助索引辅助索引树可能也是三四层再加一次回表最坏也就五六次IO。这个量级和全表扫描动辄扫描几十万页的IO完全不是一个概念。每次面试被问到“为什么说BTree适合数据库”这套计算过程就是最好的回答。2.2 聚簇索引和辅助索引主键索引与唯一索引的本质区别InnoDB里数据行本身就被锁在“以主键为键”的BTree里这棵树的叶子节点存的是整行完整记录它叫聚簇索引。没建主键时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 最左前缀到底是怎么来的联合索引本质上仍然是排序后的BTree只是排序规则变成了“先按第一列排第一列相同再按第二列排依此类推”。组合索引(col_a, col_b)的数据顺序可以理解为字典序所有记录先按col_a分组每组内部再按col_b有序。查询条件如果只带了col_bMySQL没办法在这个两列排序的结构里直接跳过第一列定位所以最左前缀原则的根因是“索引排序规则”本身。这带来两个直接结论查询必须从第一列开始用联合索引(a,b)能支持where a ?、where a ? and b ?不能直接支持where b ?。范围条件右侧的列会失效。比如where a 100 and b 1MySQL用联合索引定位到a100的记录后b1这个条件无法再利用索引快速过滤因为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列是varcharSQL写成where phone 13800138000MySQL在比较时会把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 索引数量不是越多越好索引能加速查询但每个索引都是一棵独立的BTree。写入时除了更新数据页所有索引都要同步维护插入一个普通索引数据页就多一次写放大索引数量一多写入性能会肉眼可见地下滑。另外索引也占磁盘数据量大的表上一个多余的索引可能占用几十GB空间。所以我建议定这样几条规矩能复用联合索引的前缀就不要单独建单列索引一张表超过五六个索引要开评审会线上删除索引前先观察一周相关慢SQL确认没有查询依赖再动手。压测数据库写入性能时也先把冗余索引清掉不然测试结果根本没有参考价值最后写瓶颈到底出在锁竞争还是索引维护都分不清楚。反直觉但真实索引帮助的是少数高频查询拖累的是每一次写入。宁可为3个高频查询建3个精准联合索引也不要为了10个低频查询堆10个单列索引。5.2 聚簇索引排序与主键选择为什么随机主键坑写性能InnoDB的聚簇索引把数据按主键物理顺序排列主键最好是有序递增的。自增id作为主键时新插入的行基本都追加在BTree最右侧的叶子页里页分裂少写入路径有规律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系统已经够用。索引不是越复杂越好也不是建了就一劳永逸它是在读性能、写性能、磁盘开销之间做取舍。搞清楚底层为什么是BTree、联合索引为什么讲最左前缀、什么情况下索引会失效远比套几个建索引模板重要。最后说一个我自己的习惯线上加索引前先在测试环境用相同数据量复现慢SQL把EXPLAIN前后的type和rows变化记录到变更单里上线后隔天再看一批慢日志。索引的收益不是建的时候算出来的是用一段时间的慢查询量衡量出来的。BTree的树高、页大小、回表次数这些底层概念看着像理论真到排查慢SQL和设计表结构的关键时刻每一条都能救命。 
长河君 · 2026/10/6 3:40:05 · 阅读 1.2k MySQL索引底层原理与实战：B+Tree、联合索引、失效排查全解析
聊到数据库性能优化十有八九到最后都会落在索引上。但索引不是“建了就快”这么简单它背后是一整套数据结构与算法的权衡为什么InnoDB非要用BTree联合索引的最左前缀到底怎么理解where a and b这种条件应该怎么建索引排序为什么经常出现Using filesort哪些写法会让索引直接白建这篇文章就把MySQL索引的底层结构、查询算法和真实建索引经验一起讲透适合刚把MySQL装好准备建第一个表的同学也适合在线上被慢SQL折腾了一整夜、想彻底搞明白“为什么没走索引”的同行。1. 为什么MySQL最终选型BTree从“没索引”说起1.1 没有索引的日子全表扫描的真实成本假设你在一张2000万行的用户表上执行select * from users where phone 13800138000在没有索引的情况下MySQL只能从聚簇索引的第一个叶子页开始沿着叶子节点的链表一路把整棵BTree的叶子页读一遍逐行匹配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的页能存放的键数量明显变少分支因子变小树照样会变高。BTree把这些坑全规避了非叶子节点只存键和指针叶子节点才存数据一排叶子节点通过链表串联。这样根节点能塞下上千个键树高在千万级数据下依然只有3层左右范围查询直接顺着叶子链表往后扫不用来回跳节点插入和删除也只要局部调整叶子页。MySQL最终把BTree作为InnoDB和MyISAM索引的默认结构不是偶然而是拿实际IO模型和查询特点一一比对后的结果。刚开始学索引的同学最容易忽略“磁盘IO次数”这个概念。每一个树节点的读取都对应至少一次物理IO。树的高度就是查询需要碰盘的次数所以树越矮查询越快这也是BTree被选中的最本质原因。2. BTree究竟长什么样页、三层结构、聚簇索引与回表2.1 16KB一页三层树能存多少数据InnoDB默认把数据按16KB一个页来组织。我们把一个16KB页当节点页内可以存放很多“键指针”的组合。假设主键是8字节的bigint指针占6字节组合之后14字节一个根页大约能放16 * 1024 / 14 ≈ 1170个条目。如果是三层BTree根节点下面有1170个中间节点中间节点再各指向1170个叶子页最多就能拥有1170 * 1170 ≈ 137万个叶子页。假设业务表平均一行数据是1KB那一个叶子页能放16行总数据量约137万 * 16 ≈ 2190万行。换句话说2000万级的表只要走主键查询三次磁盘IO之内就能定位到目标行如果走辅助索引辅助索引树可能也是三四层再加一次回表最坏也就五六次IO。这个量级和全表扫描动辄扫描几十万页的IO完全不是一个概念。每次面试被问到“为什么说BTree适合数据库”这套计算过程就是最好的回答。2.2 聚簇索引和辅助索引主键索引与唯一索引的本质区别InnoDB里数据行本身就被锁在“以主键为键”的BTree里这棵树的叶子节点存的是整行完整记录它叫聚簇索引。没建主键时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 最左前缀到底是怎么来的联合索引本质上仍然是排序后的BTree只是排序规则变成了“先按第一列排第一列相同再按第二列排依此类推”。组合索引(col_a, col_b)的数据顺序可以理解为字典序所有记录先按col_a分组每组内部再按col_b有序。查询条件如果只带了col_bMySQL没办法在这个两列排序的结构里直接跳过第一列定位所以最左前缀原则的根因是“索引排序规则”本身。这带来两个直接结论查询必须从第一列开始用联合索引(a,b)能支持where a ?、where a ? and b ?不能直接支持where b ?。范围条件右侧的列会失效。比如where a 100 and b 1MySQL用联合索引定位到a100的记录后b1这个条件无法再利用索引快速过滤因为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列是varcharSQL写成where phone 13800138000MySQL在比较时会把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 索引数量不是越多越好索引能加速查询但每个索引都是一棵独立的BTree。写入时除了更新数据页所有索引都要同步维护插入一个普通索引数据页就多一次写放大索引数量一多写入性能会肉眼可见地下滑。另外索引也占磁盘数据量大的表上一个多余的索引可能占用几十GB空间。所以我建议定这样几条规矩能复用联合索引的前缀就不要单独建单列索引一张表超过五六个索引要开评审会线上删除索引前先观察一周相关慢SQL确认没有查询依赖再动手。压测数据库写入性能时也先把冗余索引清掉不然测试结果根本没有参考价值最后写瓶颈到底出在锁竞争还是索引维护都分不清楚。反直觉但真实索引帮助的是少数高频查询拖累的是每一次写入。宁可为3个高频查询建3个精准联合索引也不要为了10个低频查询堆10个单列索引。5.2 聚簇索引排序与主键选择为什么随机主键坑写性能InnoDB的聚簇索引把数据按主键物理顺序排列主键最好是有序递增的。自增id作为主键时新插入的行基本都追加在BTree最右侧的叶子页里页分裂少写入路径有规律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系统已经够用。索引不是越复杂越好也不是建了就一劳永逸它是在读性能、写性能、磁盘开销之间做取舍。搞清楚底层为什么是BTree、联合索引为什么讲最左前缀、什么情况下索引会失效远比套几个建索引模板重要。最后说一个我自己的习惯线上加索引前先在测试环境用相同数据量复现慢SQL把EXPLAIN前后的type和rows变化记录到变更单里上线后隔天再看一批慢日志。索引的收益不是建的时候算出来的是用一段时间的慢查询量衡量出来的。BTree的树高、页大小、回表次数这些底层概念看着像理论真到排查慢SQL和设计表结构的关键时刻每一条都能救命。