索引这个问题我见过太多开发同学栽跟头了。用了一两年MySQLCREATE INDEX没少写EXPLAIN也没少看可真到线上慢查询榜单拉出来发现有索引没走上、走了没用对、甚至索引本身把写入拖垮的比比皆是。今天不聊安装配置也不讲建索引的基础语法就聊进阶使用里最容易让人困惑的几个点联合索引到底怎么设计、WHERE后同时出现a和b时索引怎么建、哪些写法会让索引静默失效、排序和回表怎么权衡、以及线上索引怎么维护。如果你写SQL已经有一定基础但总觉得索引这块“能用但说不透”那可以从头看到尾。1. 先搞清楚索引的底层逻辑再谈优化1.1 InnoDB为什么死磕B树MySQL InnoDB的索引底层是B树这点大家都知道。但很多人并没有真正理解B树到底解决了什么为什么不是哈希表不是红黑树也不是B-树。只有把这个问题想清楚后面所有建索引的决策才有依据。先看数据页。InnoDB默认一个数据页16KB磁盘读写的最小单位就是页不是单行。所有索引结构的设计本质上都是在最小化“读几个页”这个指标因为一次随机IO的代价动辄几毫秒而顺序读取因为磁盘预读和内存Page Cache的存在会便宜非常多。哈希表能做到单点O(1)查找但没法做范围查询也没法维持有序性。红黑树是内存结构树高和节点分裂在磁盘场景下并不划算。B-树的非叶子节点也存数据导致同样容量下存的孩子数量更少树更高IO次数更多。B树把所有数据都集中在叶子节点非叶子节点纯粹当“目录”用非叶子节点只保存索引键值和指向子节点的指针空间利用率高扇出fanout大。叶子节点按索引键值有序排列并且通过双向链表互相连接。从根到叶子每一层的节点数量呈几何级增长层数天然很低。算一笔账。假设主键是bigint8字节再加6字节的行指针一个非叶子节点大约能存 16KB / 14 ≈ 1170 个键值。两层非叶子节点能覆盖 1170×1170 ≈ 137万 个叶子节点如果每个叶子节点按16KB装载数据覆盖上亿行数据最多也就3到4层。换句话说一张千万级甚至亿级的表从根节点出发只要3次左右IO就能定位到叶子页。顺带回应一个大家经常搜的“双向索引”问题。严格说MySQL里没有所谓双向索引B树的叶子节点之间是双向链表这保证了范围查询可以从第一个满足条件的叶子页开始顺着链表顺序读取order by在走索引时不需要额外排序直接按链表顺序返回反向扫描时也能从链表尾部向前遍历。理解了B树的这层设计你就能理解为什么“离散随机主键”是索引的大敌。插入UUID字符串主键时数据会随机在各个位置发生页分裂索引页碎片化严重查询IO次数暴涨这个问题后面还会提到。1.2 回表、覆盖索引与索引下推三者的关系索引为什么能加速查询本质上就是“先用小表查目录再翻正文”。InnoDB里数据默认是聚簇索引组织主键索引的叶子节点保存整行数据而二级索引普通索引、联合索引的叶子节点只保存索引键和主键值。用二级索引查询时第一步先在二级索引树里定位拿到主键值第二步再到主键索引树里取整行数据第二步就叫回表table lookup。回表是一次额外的随机IO这才是二级索引慢的根因。举一个直观的对比假设表有500万行二级索引命中2000行如果全部回表就是2000次随机IO即使每次都命中Page Cache也明显慢于覆盖查询。怎么减少回表三个手段覆盖索引Covering Index如果SQL里select的列全部落在某个索引的字段集合里InnoDB根本不需要回表直接在索引树的叶子节点把数据取完。执行计划的Extra会显示 Using index。索引下推ICPIndex Condition PushdownMySQL 5.6引入。以前存储引擎从二级索引读出记录后直接把整行回表再由Server层过滤WHERE。ICP允许把WHERE里能用索引字段判断的条件优先放到存储引擎层执行先把不满足的行过滤掉再回表减少了回表次数。执行计划的Extra显示 Using index condition。覆盖索引无法覆盖时尽量让where条件把范围收窄缩小回表的行数。这里容易混淆的是覆盖索引与索引下推的区别。覆盖索引是“压根不回表”ICP是“少回表”。很多新手看到Extra里出现 Using index condition以为索引已经很理想了其实它只说明存储引擎帮你过滤了一部分最终大概率还是回了表。用查字典来类比最直观拼音索引是二级索引页码是主键。普通查询就是先查拼音索引拿页码再翻到正文把整行字读完覆盖索引相当于这个字典后面附了“笔画-拼音对照速查表”你要的信息在里面直接有不用翻正文索引下推则是你先在拼音索引区域把明显不是目标字的一批页码划掉再挑着翻正文。同样是查字典翻正文的次数完全不同。2. 联合索引设计与最左前缀原则2.1 where a and b 到底怎么建索引很多同学都在纠结一个问题SQL里同时出现where a ? and b ?到底应该怎么建索引第一反应往往是给a建一个索引再给b建一个索引然后看优化器心情二选一。大概率会翻车。先给结论如果a、b都是等值条件优先建 (a, b) 联合索引而不是两个单列索引。为什么联合索引的定义本身就是按字段顺序排序的第一列a全局有序a相同的情况下b有序。查询时可以先定位a再在a的范围内精确定位b两个等值条件都能命中索引的精确定位避免回表后还要做额外过滤。而两个单列索引优化器最多只能使用其中一个做定位另一个条件只能回表取整行后过滤。即使MySQL支持Index Merge索引合并把两个索引结果做交并集对等值场景也往往不如联合索引高效还会产生额外多路IO。所以除非两个字段各自承担高频独立查询否则不要轻易拆成单列索引。再进一步联合索引内部顺序怎么排有两个维度要考虑区分度Cardinality区分度高的字段放前面。道理很简单索引先按第一列排序第一列的区分度越高定位越精确后面扫描范围越小。比如部门id和员工状态用部门id做首列的联合索引比用状态做首列通常要稳。范围条件放最后因为联合索引在“遇到范围条件”之后右边的字段就无法再利用索引的精确排序和定位了。第二个维度值得展开。假设SQL是where a 100 and b 1如果建 (a, b)索引会先按a做range扫描b的作用会大打折扣如果建 (b, a)b的等值条件先把范围精确锁死a 100就在b1这个小区间内做range效率高很多。所以“范围条件放最后”完全可以理解为谁的能力强谁先上等值字段优先于范围字段。具体到“where a and b”这个最常见场景最优先级排序口诀可以浓缩成三条等值字段优先于范围字段区分度大的优先于区分度小的联合索引不是越多越好要根据实际查询集合来找公共前缀。2.2 order by排序场景下的索引设计排序慢是另一个高频痛点“MySQL排序”这个话题的搜索量一直不低。MySQL排序有两条路一是索引天然有序直接顺着B树叶子链表读就行不需要额外排序二是无法利用索引时走filesort把结果集先取出来再排序数据量大时可能落到磁盘临时文件性能断崖式下降。要让order by走索引设计联合索引时得把排序字段考虑进去。比如这条SQLSELECT id, emp_no, name FROM employee WHERE dept_id 100 AND status 1 ORDER BY create_time DESC LIMIT 20;最理想的联合索引是(dept_id, status, create_time)。查询先用dept_id和status定位到目标行范围而这个范围内的记录天然就是按create_time排好序的所以order by create_time直接复用索引顺序Extra里不会出现Using filesort。这里能排序成功的关键是排序字段必须满足联合索引的“排序版最左前缀原则”前面的等值条件把字段固定住后面的字段才能保持全局有序。同样场景如果你写where dept_id 100 order by status, create_timestatus是等值条件那联合索引 (dept_id, status, create_time) 也能排序通过。但如果写where dept_id 100 order by create_timedept_id用范围后create_time在索引里的顺序就无法保证全局有序了大概率还是要filesort。值得一提的是filesort不等于灾难。如果结果集很小且能全部装入内存sort buffer排序很快但如果出现 Using filesort 且 rows巨大、Extra里还带 Using temporary那就要警惕了。排查思路很简单先看where字段是否用了索引再看order by字段是否满足最左前缀的排序条件。2.3 主键索引与唯一索引的取舍“主键索引和唯一索引的区别”也经常被问到。这俩用处在业务上相似很多同学以为可以互相替代其实差别很大核心区别有三点唯一性约束强度。主键要求非空且唯一一张表只能有一个唯一索引允许NULL值而且MySQL允许同一列存在多个NULLNULL与NULL互不相等。索引类型。InnoDB的主键索引是聚簇索引叶子节点保存整行数据它决定了表数据的物理存储顺序唯一索引只是二级索引叶子节点保存索引键主键值查询通常还要回表。存储与维护代价。主键索引树的规模基本等于表数据本身是所有数据访问的入口唯一索引多一棵独立的树插入和更新都要多做一次唯一性校验高并发写入下开销更明显。基于这些特性实操建议很明确。主键不要用业务敏感字段比如身份证号这种业务字段一旦变更聚簇索引的数据物理位置要跟着动代价极高主键建议用自增整数或雪花算法这类单调递增值避免UUID随机字符串导致的页分裂和碎片唯一索引尽量建在业务真正需要唯一约束的字段上比如订单号、手机号不是为了查询快才建如果只是想加速一个高频等值查询且没有唯一性要求普通二级索引就够了不必上唯一索引。这里多说一句“逻辑主键”和“业务唯一键”可以同时存在。表的主键用id自增业务上让phone或order_no建唯一索引这是最稳妥的组合兼顾了聚簇索引的存储性能和业务唯一性校验。3. 索引失效的典型场景与避坑清单3.1 手写SQL时最容易踩的八个坑索引设计了半天结果SQL一写就把索引弄失效了这种场面在代码评审里太常见了。我总结了一份高频失效清单基本覆盖日常手写SQL的所有坑隐式类型转换。索引列是varchar条件却传数字比如phone 13800138000MySQL会把字符串转成数字再比较索引直接失效。反过来更隐蔽int列用字符串条件时字符串会先被转成数字索引可以走但性能也不保险。最稳的处理就是字段和参数类型严格一致。对索引列使用函数或表达式。where DATE(create_time) 2025-01-01、where YEAR(create_time) 2025、where price * 2 100这些写法都会让优化器放弃索引。正确做法是把函数挪到等号右边比如create_time 2025-01-01 AND create_time 2025-01-02。前导模糊查询。like %keyword无法走索引因为索引按前缀排序无从定位like keyword%则可以走range。违反最左前缀。联合索引 (a, b, c)只写b或只写c条件是无法直接利用索引精确定位的MySQL对这类查询会有一些优化手段但效率通常不如完整的联合索引命中好。or连接导致失效。where a 1 or b 2如果b上没有索引或者a、b不是同一个索引覆盖可能会让整个查询退化。负向查询。not in、!、not like这些往往会让优化器放弃索引但弃不弃有时候取决于数据分布和统计信息。比如一个字段99%的值都是1查! 1时优化器觉得索引还不如全表扫就会自己放弃。对索引列做字符串拼接或cast等价于函数操作。优化器自己“反水”。即使SQL写法没问题如果回表代价太大也就是命中行数在总行数中占比太高优化器会放弃二级索引而选择全表扫描因为全表顺序读比几千次随机回表IO更便宜。关于第8条多说一句这往往是数据分布变化的信号。一张500万行的表某条件统计数据在1000行内走索引很香某天数据变成100万行命中优化器评估后可能就直接ALL了。这时候该做的不是强制索引而是想办法缩小扫描范围或者考虑把高频字段改造成覆盖索引。3.2 用Explain的Extra辨别“半失效”状态很多同学以为执行计划里用到了索引就是万事大吉其实索引是否用“到位”区别很大。我在处理一个慢查询时发现Extra里显示 Using index condition以为索引用得很充分结果深入一看命中了几万行回表压力全在存储引擎层整体还是慢。这里列一下最常见的四种Extra状态和实际含义Extra状态含义是否理想Using index覆盖索引SELECT的列全在索引里不用回表最优Using index condition触发ICP部分WHERE条件下推到存储引擎过滤但仍可能回表较好需关注rowsUsing where索引定位后Server层再做一次行过滤常见于索引无法覆盖全部过滤条件尚可Using filesort结果集需要额外排序需要警惕Using temporary使用了临时表常见于复杂group by/union很影响性能很差实操里我建议多关注 rows 和 key_len。rows是预估扫描行数数字越大说明索引筛选能力越弱key_len表示索引用到了几个字段比如联合索引 (dept_id, status, create_time)dept_id是int非空时key_len是4如果执行计划里key_len只显示4说明后面的status和create_time都没用上这时候就要回到SQL写法或索引顺序上找原因。key_len的计算逻辑并不复杂但真正排查时不需要手算对比一下同一个索引在不同SQL里的key_len就能看出端倪。4. 索引维护与性能调优实战4.1 索引表空间与碎片问题有人把“索引表空间”当成一个很高深的概念来搜其实没那么玄。InnoDB的表默认是一张表一个表空间数据和二级索引都放在同一个.ibd文件里。表空间里既有B树的页面也有段Segment、区Extent、页Page的管理结构。所以索引越多表空间文件就越大这是物理层面的必然。实际运维中大家感受最深的往往是“删除大量数据后表空间大小不变”。因为默认配置下被标记删除的页不会立刻归还给操作系统而是在表空间内部留存复用。这会造成两个后果碎片增多很多页的利用率不高扫描效率下降文件系统层面文件大小撑在那里磁盘看起来永远不见少。处理办法是定期做碎片整理OPTIMIZE TABLE employee;OPTIMIZE TABLE会重建表并整理索引页面效果是表空间显著缩小、查询扫描页数下降。但注意两个坑一是大表OPTIMIZE期间会有锁尽量在业务低峰期执行二是MySQL 8.0之后可以用ALTER TABLE employee ENGINEInnoDB, ALGORITHMINPLACE, LOCKNONE;来实现在线整理减少锁表时间。还有一个容易被忽略的点给大表新增索引不要直接执行ALTER TABLE ... ADD INDEX在8.0以前这会长时间阻塞写入。更平滑的方案是借助pt-online-schema-change这类工具通过触发器把增量变更同步到新表完成后原子切换。如果用的是MySQL 8.0部分DDL已经支持INSTANT / INPLACE算法但依然建议把大表的ALTER操作放到低峰期同时对锁等待做好准备。4.2 一例完整的联合索引优化复盘光讲理论容易飘拿一条实际慢SQL走一遍完整流程更有说服力。假设有一张员工表结构大致如下CREATE TABLE employee ( id BIGINT PRIMARY KEY AUTO_INCREMENT, emp_no VARCHAR(32), dept_id INT NOT NULL, status TINYINT NOT NULL, name VARCHAR(64), create_time DATETIME, KEY idx_dept_id (dept_id) );业务方反馈这个查询很慢要求优化SELECT id, emp_no, name FROM employee WHERE dept_id 100 AND status 1 ORDER BY create_time DESC LIMIT 20;第一步先执行EXPLAIN看现状。最开始时表上只有idx_dept_id单列索引执行计划大概是 typeref、rows几万、ExtraUsing where Using filesort。明明用了索引为什么还慢因为dept_id100这个条件命中行数太多status过滤在Server层做order by还要额外排序最后回表取整行每一步都拖累性能。第二步改造成联合索引(dept_id, status, create_time)。执行计划从 typeref、rows几万变成 rows几千甚至几百Extra变成Using index conditionfilesort消失。这个改造的收益来自三处dept_id和status两个条件都在索引中完成精确定位rows大幅缩小。create_time复用索引顺序彻底消除文件排序。ICP把status的过滤提前到存储引擎回表行数变少。第三步如果SQL里select的列能尽可能收敛到索引字段覆盖索引还能更香。比如业务只需要统计某个部门某状态下的create_time分布把select改成dept_id, status, create_timeExtra就会变成Using index连回表都省了。这里有个很实用的经验线上优化不要一次加一堆索引先explain定位最大的瓶颈点是rows太大、filesort太重、还是回表太频繁改完一个再看效果逐步逼近最优查询计划。4.3 索引不是越多越好说完怎么加必须说怎么砍。很多人一遇到慢查询第一反应就是给WHERE字段补索引结果一年后表上挂了十几个索引更新变慢、表空间膨胀、优化器选择困难。每个二级索引都是一棵独立的B树写操作需要同步维护每一棵树这就是写放大Write Amplification。一张写入频繁的表索引数量从5个涨到10个写入耗时可能翻倍都不止。一个可行的索引治理流程开启慢查询日志把执行频率高、扫描行数大的SQL捞出来。对业务SQL做归一化去掉具体参数值统计同构SQL的执行频次。找出高频SQL里的WHERE、ORDER BY、GROUP BY字段提炼一组能覆盖大多数场景的公共前缀联合索引。借助sys.schema_unused_indexes或performance_schema找出一段时间内从未被使用的索引评估后删除。删除索引后观察一周重点关注慢查询数量和写入性能变化。我在实际项目中见过最典型的情况一张订单表上同时有 idx_user_id、idx_status、idx_create_time 三个单列索引业务高频查询是WHERE user_id ? AND status ? ORDER BY create_time DESC三个索引一个都用不到位。改成(user_id, status, create_time)联合索引后表上索引从三个缩成一个查询和写入同时变快。这就是索引治理的价值。5. 常见问题与排查技巧实录5.1 三个高频问题排查实录问题一加了索引但explain还是ALL。这类问题建议按这个顺序排查先看是否对索引列用了函数或隐式转换再看SQL里有没有or、like前导通配、负向查询然后看联合索引是否满足最左前缀最后看rows占比如果命中行数超过表行数的20%~30%优化器大概率会主动放弃索引。前四步都排除了还不行可以用FORCE INDEX做一次对照实验看强制走索引是不是真的更慢。如果是说明数据分布已经变了该从SQL和索引设计上重新想方案。问题二联合索引和多个单列索引到底选哪个。判断标准是看这张表实际的高频查询是“总是同时用a和b”还是“a、b各自独立查询”。如果高频场景就是where a and b果断用联合索引如果a和b分别有不同的高频独立查询那可以保留两个单列索引再评估是否还需要一个联合索引兜底。同时要留意单列索引和联合索引可以共存MySQL优化器会自己选但索引总数要控制。问题三排序字段无论如何都出现filesort。多半是违反最左前缀的排序要求。记住这个判断方法联合索引 (a, b, c)where里a1、order by b、c有序这是对的where里a范围、order by b有序这通常不可能直接用索引排序order by b、c而where里没有a的等值条件也不会复用索引顺序。如果排序结果集很小filesort也不是大问题真正要防的是filesort temporary组合出现。5.2 给新手的几条实操心得这几年配合开发团队优化SQL我自己沉淀了几条不怎么写进文档里的经验分享出来供参考写SQL时尽量保持“索引列裸奔”。不要在索引列上套函数、做运算、拼字符串所有加工尽量放到条件右侧这是性价比最高的习惯。不要迷信“必须覆盖SELECT所有字段”。覆盖索引虽好但为了一次查询把所有字段都塞进索引往往会带来巨大的写入成本和表空间膨胀多数场景下保证ref 少量回表比强行覆盖更合理。统计信息是会过期的。批量导入、大量删除后记得跑一次ANALYZE TABLE让优化器重新评估基数否则它会拿着过期的统计做出反直觉的计划。线上影响面大的ALTER操作永远先看SHOW PROCESSLIST评估写入压力再用工具平滑执行宁慢勿快。索引命名别偷懒。主键叫PK唯一索引叫uk_表名_字段普通索引叫idx_表名_字段。三个月后回来看代码你会感谢当初的自己。最后再分享一个我在排查慢查询时反复验证过的方法不要一上来就看索引有没有“命中”先看rows这个数字。同样一条SQL从一个走索引但rows十几万的计划变成一个全表扫描但rows几千的计划后者反而更快。索引只是工具最终目标是让扫描行数最小、IO次数最少。这个意识建立起来你在索引进阶这条路上就真的入门了。