前几天帮同事排查一条线上慢查询SQL很简单就是按状态和时间段查订单表八十几万行跑了3.2秒。我第一时间看索引索引建了四五个EXPLAIN一执行却显示typeALL全表扫描。这场景我见过太多次了不是没建索引而是索引建错了、建多了或者SQL写法让优化器根本没法用索引。最后我只加了一个复合索引同样的查询降到了40毫秒。这个差距不是个例MySQL索引设计得好不好可以把同一个查询的耗时拉开两三个数量级。这篇文章我想把MySQL索引相关知识完整梳理一遍从索引的底层存储结构为什么InnoDB选B树到主键索引、普通索引、唯一索引、复合索引的选型再到最左前缀原则、索引失效的典型场景以及用EXPLAIN验证索引是否生效的完整排查思路。内容适合三类人刚学MySQL、面试前想系统过一遍索引考点的工作中经常被慢查询折磨的以及准备给线上大表优化索引又怕踩坑的。目录就按我实际排查问题的思路组织算是把这些年踩过的坑一次性讲清楚。1. 索引的本质字典的目录页是怎么工作的1.1 没有索引时MySQL在做一件笨事很多初学者以为查不到就走全表扫描查到了就走索引其实没这么简单。在没有索引的情况下MySQL对一张表做查询只能全表扫描full table scan。所谓全表扫描就是从表的第一页开始把每一行都读出来逐行跟WHERE条件比较匹配的就留下不匹配的就丢掉。假设一张表有800万行你要查 order_no A10086MySQL要老老实实读800万行做800万次比较然后返回命中的那一行。这里有个关键点InnoDB的最小读取单位是页page默认16KB。全表扫描意味着要把所有数据页都读到内存里过一遍即使你的目标是其中一行。数据量小的时候感觉不明显一旦表上了百万行或者千万行这种查询就会在慢查询日志里扎堆出现DBA半夜给你打电话就是这种场景。1.2 索引其实就是一份有序的目录索引做的事情本质上和字典的目录一样把某个字段的值单独拎出来按照一定顺序排好再存上一个页码——在MySQL里这个页码就是主键值。有了这个有序结构查找过程就变成先在这个有序集合里定位到目标值再按照页码去拿完整的那一行。定位有序集合用的是类似二分查找的思路一次能排除一半数据所以查询代价从遍历N行降到比较log2N次。索引在InnoDB里不是单独存在的一张表而是一棵B树。后面我会单独讲B树的结构。这里先记住它的核心特征索引列的值是有序的索引的叶子节点保存了指向完整行的通道这个通道在InnoDB里就是主键值。1.3 一组可以自己验证的数字我习惯用一个简单的模型来估算索引的收益。假设orders表800万行每行数据约200字节磁盘IO延迟约1ms每读一个页算一次IO无索引全表扫描约需要读 800万×200/16KB≈97656个数据页即使按顺序预读打个折也要几十到几百毫秒条件再复杂一点就到秒级。有索引等值查询B树三层高定位一个键大约3次IO找到主键后再回表读1次数据页总共4到5次IO毫秒级完成。这就是80万行数据3.2秒变40毫秒的底层逻辑。下一节我会解释为什么B树能把800万行装进三层树里这是面试必问也是理解索引优化的分水岭。2. InnoDB为什么选B树聚簇索引、二级索引与回表2.1 B树的结构与容量B树是B树的一种变体MySQL InnoDB引擎的索引和表数据都基于它。一棵B树长这样非叶子节点只存索引键和指向子节点的指针不存数据叶子节点存数据聚簇索引或主键值二级索引并且叶子节点之间用链表串起来整棵树按照索引键有序排列。为什么不用哈希表因为InnoDB要支持范围查询、排序、前缀匹配。哈希索引只能做等值查询一个范围查询就把哈希表pass了。为什么不用红黑树或AVL树因为树太高。红黑树虽然是平衡的但它每个节点只存一个键数据量一大树高就是二三十层每层一次磁盘IO性能没法看。为什么不用B树B树的非叶子节点也存数据同样容量下能存的键更少树更高。而且B树的叶子节点没有链表串联范围查询要反复从根往下走效率远不如B树在叶子链表上顺序扫描。容量算一笔账InnoDB一个页16KB去掉页头页尾可用空间按15KB算。如果索引键是bigint占8字节指针占6字节一个非叶子节点能放下约 15000/14≈1071个键。假设每行数据200字节一个叶子页能放约80条记录。那么一棵三层B树能索引的记录数大约是1071×1071×80≈9172万。也就是说九千万行的表用三层B树依然只要三次磁盘IO就能定位到叶子页。这就是B树最迷人的地方容量指数增长IO次数几乎不变。2.2 聚簇索引主键和数据是同一棵树InnoDB里有一句容易让人误解的话索引即数据。准确表述是InnoDB把整张表的行数据直接存在主键索引的叶子节点上。也就是说主键索引的B树叶子节点里存的是完整的一行记录而不是一个主键→地址的映射。这个结构叫聚簇索引clustered index。因为数据和索引长在一起InnoDB表在物理存储上就只能有一个聚簇索引。这也是为什么InnoDB强烈建议每张表都要建主键如果你不建InnoDB会先找一个非空的唯一索引当主键实在没有就生成一个6字节的隐藏rowid当主键。隐藏rowid对业务透明却在主从复制、数据迁移、二级索引统计时带来各种隐性麻烦。所以建表时显式定义一个自增主键是最省心的习惯。这也直接解释了面试题里为什么主键推荐用自增整数而不用UUID——关键点其实是聚簇索引的物理排序后面我会单独展开。2.3 二级索引与回表除主键索引外其他索引统称二级索引secondary index。二级索引的叶子节点存的是索引键 主键值不是完整行。你给user_id建一个普通索引查询时的动作分两步在user_id的B树上找到目标键拿到主键id回到主键索引的B树按id再查一次取出完整行。第二步就是常说的回表。回表不是免费的每回表一次大约多一次主键索引的随机IO。如果一个二级索引匹配到5000行就要回表5000次。优化方向自然就出来了要么让二级索引更瘦减少匹配行数要么干脆别回表。2.4 覆盖索引让查询不下树如果一个查询需要的所有列都包含在二级索引里那就不需要回表了。比如SELECT order_no FROM orders WHERE user_id 1001;如果存在复合索引(user_id, order_no)那么user_id和order_no全在索引里查询在二级索引上就能拿到全部结果连主键都不用回。EXPLAIN的Extra列会显示Using index性能极好。覆盖索引是线上性能优化里最常用的一招把高频查询的查询列塞进索引换取不回表。代价是索引变大、写入变慢所以只对真正高频的查询做。我们后面讲复合索引设计时会重点用它。3. 索引类型与选型你要的到底是哪一种索引3.1 四类索引一次看清索引类型核心特点数量限制典型场景主键索引聚簇索引数据存在它的叶子节点非空唯一每表只能1个每张表都必须有唯一索引值不允许重复允许NULL且多个NULL可多个order_no、身份证号、手机号等业务唯一字段普通索引只加速查询无约束可多个高频查询字段全文索引对文本分词后索引可多个大文本字段的模糊搜索这个表看似简单但选错的人不少。最常见的错误是把业务上唯一的字段建成了普通索引。少一个唯一约束等于把数据正确性的兜底交给了应用层。反过来也不建议给表里所有字段都建唯一索引唯一性校验本身有开销高并发插入时唯一索引的检查会成为热点。3.2 唯一索引的两个坑第一个坑MySQL允许唯一索引里有多个NULL。因为NULL在MySQL里被定义为未知未知不等于未知所以两个NULL不会冲突。如果业务上要求手机号要么不填填了就不能重复这个语义是符合的但如果要求手机号必须有且唯一那要在应用层配合非空约束。第二个坑重复值检查对写入性能影响明显。在插入或更新时唯一索引需要额外做一次冲突检测。批量导入几百万数据时如果目标表已有唯一索引导入速度可能慢好几倍。此时常见做法是导入后用INSERT语句配合ON DUPLICATE KEY UPDATE批量处理或干脆导入前禁用相关索引、导入后重建大表慎用在线DDL另说。3.3 前缀索引大字段的取舍对于很长的字符串字段比如email、日志消息、文章摘要直接建完整列索引很浪费空间。MySQL支持前缀索引index(email(10))表示只取email字段前10个字符建索引。前缀索引能显著缩小B树体积但有两个限制不能用于覆盖索引因为索引里没有完整列值也不能用于ORDER BY排序需要完整值。怎么定前缀长度用选择性判断SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10, COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15 FROM user;选择性接近1说明前缀区分度高80%以上基本可用。如果10个字符的选择性已经和全列差不多就没必要取更长省下来的空间都是真金白银。3.4 索引选择性决定一个字段值不值得建索引选择性 去重后的值数量 / 总行数。一个只有男/女两个值的字段选择性是2/100万0.0002%单独建索引几乎没有意义因为它过滤不掉多少数据优化器大概率还是全表扫描。真正值得建独立索引的是那些选择性高、又高频出现在WHERE里的字段比如user_id、order_no。判断方法很简单SELECT COUNT(DISTINCT user_id) / COUNT(*) AS sel FROM orders;低于某个阈值经验上20%~30%视数据分布而定的字段不要单独建索引可以放进复合索引当配角或者干脆不建。4. 复合索引与最左前缀原则为什么SQL明明带了两个条件却只用了一个索引4.1 复合索引的排序逻辑决定了天机复合索引也叫联合索引是一个索引里包含多个列但它的存储方式不是各存各的而是把多个列组合起来排序。以(a, b, c)为例B树的键先按a排序a相同再按b排序b再相同按c排序。这个逻辑和电话本按姓氏、名字排序一模一样先按姓氏排同姓的按名字排。这个排序规则直接推导出最左前缀原则一个查询能用到复合索引当且仅当它从最左边的列开始连续命中若干列。因为索引的全局顺序依赖最左列跳过了最左列索引里的相对有序性对查询条件就完全失效了。4.2 哪些写法能用上哪些不能仍然以索引(a, b, c)为例SQL条件是否用索引说明WHERE a1用ref命中最左列WHERE a1 AND b2用ref命中abWHERE a1 AND b2 AND c3用ref全部命中WHERE b2不用跳过了a索引顺序无意义WHERE a1 AND c3部分用只有a能定位c在索引层过滤靠ICP减少回表WHERE a1 AND b2部分用a走rangeb的定位条件在a的范围之后失效这里容易被误判的是WHERE a1 AND c3很多人以为字段都在复合索引里一定全用上。实际上c确实在索引里但索引按a、b、c排序在a固定而b未限定时c在索引里是无序的没法用B树的定位能力。MySQL只是把c当作普通过滤条件配合ICP在索引层过滤这比全表扫描好但没发挥出最左原则的全部功力。4.3 索引列顺序怎么设计综合多年经验复合索引的列顺序我一般按这个优先级排等值条件的列排最前选择性高的列排前面范围条件、、BETWEEN的列放最后高频查询优先排序需求也不要忽略。举个实际设计例子。订单表查询最常用的是WHERE statusPAID AND create_time BETWEEN ...。如果建(create_time, status)则范围字段在前status的等值条件无法再收紧索引如果建(status, create_time)则先按status精确到一个小集合再在集合内按create_time范围扫描显然更优。前提是status的选择性不能太低如果status只有两种值单独放最前意义不大但配合create_time做范围扫描仍有价值。4.4 排序字段与索引的关系B树天然有序所以ORDER BY如果和索引顺序一致MySQL可以直接走索引顺序输出避免filesort。filesort意味着MySQL要把结果集再排一遍小结果集还好大结果集非常伤。比如索引(status, create_time)ORDER BY status, create_time就能直接输出。MySQL 8.0还支持降序索引定义时写ASC/DESC排序方向也能匹配。这个点常被忽略EXPLAIN里看到Using filesort时第一反应就该检查排序字段能不能塞进现有索引。5. 索引失效的典型场景每一个我都排查过不止一次5.1 对索引列做函数或运算最常见的失效写法WHERE YEAR(create_time) 2023索引列被函数包裹后无法按原值定位索引失效。正确的等价写法是范围条件WHERE create_time 2023-01-01 AND create_time 2024-01-01同理不要对索引列做运算比如WHERE price * 1.1 100把运算挪到右边WHERE price 100 / 1.1。这个原则本质是让索引列以原样参与比较任何包一层的行为都会破坏B树的有序性。5.2 隐式类型转换这是最隐蔽的一类。字段phone是varchar查询写成WHERE phone 13812345678MySQL把字符串类型的phone字段转成数字去比较等于对索引列套了一个转换函数索引失效。注意反过来数字字段和字符串比较通常不受影响因为转换作用在常量上。稳妥的原则应用层和SQL里都保持类型一致不要依赖MySQL的隐式转换。这个坑在用户表、订单表里出现频率极高值得养成写SQL时先看字段类型的习惯。5.3 左模糊匹配B树按前缀有序LIKE keyword%可以走索引LIKE %keyword却不行——最左字符未知无法定位起始位置。业务里那种搜索包含某关键词的需求靠这个索引是没解的。小表可以接受全表扫大表就应该考虑全文索引、ES等专用方案或者干脆存反转字符串来支持后缀查询。这个坑在日志表和商品搜索里特别常见别指望MySQL单靠普通B树索引解决所有模糊搜索。5.4 OR条件WHERE name张三 OR age18如果只有name有索引MySQL没法在一半走索引、一半扫全表之间自由切换干脆全表扫描。两个条件都有索引时MySQL可能用index_merge合并两个索引结果但这也依赖优化器判断不如改成UNION ALL把两个查询拆开或者设计一个能同时覆盖两边的复合索引。遇到OR条件慢查询第一件事就是看两边字段是不是都有独立索引。5.5 NOT IN、!、NOT LIKE不等于类条件本质上是排除一小部分但B树的定位能力建立在等于、范围上对排除并不友好。优化器往往选择全表扫描因为扫描代价可能低于多次随机IO。如果业务确实要排除性查询且排除比例很小可以考虑反向设计用正向命中的结果集去处理。5.6 优化器认为全表扫描更快这是最反直觉的一条索引存在优化器也识别到了但rows估算显示扫描比例超过某个阈值经验上约20%~30%它认为回表的随机IO比顺序扫描更贵于是选ALL。这不算索引失效而是优化器的成本决策。解决方向不是强制索引FORCE INDEX只适合临时验证而是缩小结果集加限制条件、优化分页、改造查询逻辑等。5.7 IS NULL的误区很多文章说IS NULL一定失效其实不准确。MySQL对IS NULL的索引支持取决于版本和统计信息等值类IS NULL很多时候能走索引但IS NOT NULL在很多场景下确实放弃索引因为绝大多数行的那列都非空走索引反而要回表更多次。这个属于成本判断不必背结论跑一下EXPLAIN看type和rows最靠谱。5.8 复合索引没走最左前缀这个前面已经详述。失效场景里出现频率最高单独列出来提醒建了(a,b,c)查询只用b这不是索引坏了是设计时没对齐真实查询。优化方向不是加新索引而是调整复合索引的列顺序或者干脆改造SQL让条件按最左顺序出现。6. EXPLAIN实战一条慢查询的完整排查链路6.1 先看懂EXPLAIN的关键列排查索引问题第一步永远是EXPLAIN。我重点看这几列列含义判断标准type访问类型从好到坏system const eq_ref ref range index ALL。看到ALL/index就要警惕key实际使用的索引NULL表示没用到rows预估扫描行数和表总行数对比如果接近表大小就是全表扫Extra附加信息Using filesort、Using temporary是最常见的性能杀手其中type是最直观的typeref说明走了一个二级索引等值匹配typerange说明走了范围扫描typeALL就是全表扫描。达不到ref或range时说明索引没使上劲。6.2 Extra列的常见值Using index覆盖索引查询不需要回表最优Using index condition用了索引下推ICP条件在索引层过滤后再回表次优但很好Using whereMySQL在拿到索引结果后还要再用WHERE过滤通常是索引没覆盖全条件Using filesort需要额外排序尽量消灭Using temporary用了临时表常见于GROUP BY、DISTINCT、多表join。注意Extra里同时出现Using where和Using index不矛盾前者表示有过滤动作后者表示无需回表。6.3 一个真实案例的完整排查过程假设线上有慢查询SELECT id, order_no, amount FROM orders WHERE status PAID AND user_id 88721 ORDER BY create_time DESC LIMIT 20;第一步EXPLAIN结果typeALLrows800000ExtraUsing where; Using filesort。一眼就能定问题没走任何索引全表扫描还额外排序。第二步分析字段。status选择性极低user_id选择性极高create_time用于排序。设计思路必须让user_id作为等值定位列create_time顺势参与排序order_no和amount考虑覆盖。于是建复合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); 与上面等价二选一即可。这里顺序为什么是(user_id, status, create_time)而不是(status, user_id, create_time)因为user_id选择性远高于status而且status可能被优化器认为过滤比例太低。先按user_id定位到一个用户的所有订单再在用户内部按status过滤再按create_time排序整个查询都能在索引内完成。第三步再EXPLAINtyperefrows从800000降到该用户的订单数比如几十Extra里的Using filesort消失因为create_time在索引内有序。如果还想省掉回表把查询列order_no、amount也放进索引变成(user_id, status, create_time, order_no, amount)Extra会显示Using index或Using index condition级别覆盖查询达到最优。这个排查链路是我处理线上慢查询的标准动作EXPLAIN → 看type/rows/Extra → 按选择性排列复合索引 → 再看Extra确认覆盖情况。不要上来就猜索引没建一步步看数据。7. 索引设计的实战心得与建议7.1 索引不是越多越好这是我最想强调的。索引是读加速、写减速的典型每多一个索引INSERT、UPDATE、DELETE都要同步维护对应B树写入放大是实打实的索引还占用磁盘和缓冲池更麻烦的是索引越多优化器选错执行计划的概率越大索引统计信息稍微不准就可能出现明明有索引却不用的诡异情况。我在线上见过一张表建了14个索引业务写入慢到告警。经验上单表索引5个以内比较稳妥热点大表只对真正的慢查询建索引。7.2 冗余索引排查复合索引(a, b)建好后单列索引(a)就是冗余的——因为(a, b)的最左列a已经能支持axxx这类查询。同理唯一索引和普通索引重复的情况也经常出现。MySQL 8.0的sys库里直接有冗余索引报告SELECT * FROM sys.schema_redundant_indexes\G也可以查未使用索引SELECT * FROM sys.schema_unused_indexes;定期清理这两类索引是DBA和开发者都该做的日常操作。7.3 主键要设计好否则页分裂教做人聚簇索引的叶子节点按主键顺序排列。如果主键是自增整数新数据总是追加在树的最右端页填满就新开一页几乎不会引起页分裂写入效率很高。如果主键是UUID或随机字符串新行的主键随机插入到树中间频繁触发页分裂——既要移动页内数据又要更新页指针写入性能急剧下降索引碎片率持续升高。所以能自增就自增除非有强分片或安全需求才考虑有序UUID或雪花ID。这是面试常考也是线上建表的底气。7.4 索引下推ICPMySQL 5.6之后的白嫖福利回到前面WHERE a1 AND c3的例子。没有ICP时MySQL只能靠a定位到一批记录逐个回表再把c3筛掉。有ICP后c3这个条件被下推到索引引擎层在回表之前就先过滤掉不满足c的记录回表次数大幅减少。MySQL 5.6默认开启EXPLAIN里看到Using index condition就是触发了。想用好ICP核心是让过滤条件尽量落在索引列上这也是复合索引多设计一两个配角列的原因——它们不参与定位却能在索引层过滤。7.5 给刚要动手优化索引的你的最后建议第一永远先用EXPLAIN确认现状再动手别凭感觉加索引。第二对已经很大的表加索引不要直接执行裸ALTER TABLE用在线DDL工具如gh-ost或至少确认MySQL 8.0的INPLACE算法可以平滑执行避免锁表。生产环境一把锁下去后果是真的惨。第三索引优化是反复迭代的过程加完索引不等于结束观察一周慢查询日志看查询是否消失、写入是否有退化及时回滚不合适的索引。这些经验多数是我在线上踩坑换来的希望你能直接用上。最后再分享一个个人习惯我优化MySQL索引时始终把查询先写到最简放在第一位。很多索引花式失效根子其实是SQL写得太绕。先把SQL逻辑理清楚再谈索引设计——顺序反了优化永远追着问题跑。如果这篇文章里的哪个场景正好命中你的线上问题建议按EXPLAIN的排查链路完整走一遍大概率十分钟内就能定位到根因。
MySQL索引优化实战：从全表扫描3.2秒到毫秒级查询
前几天帮同事排查一条线上慢查询SQL很简单就是按状态和时间段查订单表八十几万行跑了3.2秒。我第一时间看索引索引建了四五个EXPLAIN一执行却显示typeALL全表扫描。这场景我见过太多次了不是没建索引而是索引建错了、建多了或者SQL写法让优化器根本没法用索引。最后我只加了一个复合索引同样的查询降到了40毫秒。这个差距不是个例MySQL索引设计得好不好可以把同一个查询的耗时拉开两三个数量级。这篇文章我想把MySQL索引相关知识完整梳理一遍从索引的底层存储结构为什么InnoDB选B树到主键索引、普通索引、唯一索引、复合索引的选型再到最左前缀原则、索引失效的典型场景以及用EXPLAIN验证索引是否生效的完整排查思路。内容适合三类人刚学MySQL、面试前想系统过一遍索引考点的工作中经常被慢查询折磨的以及准备给线上大表优化索引又怕踩坑的。目录就按我实际排查问题的思路组织算是把这些年踩过的坑一次性讲清楚。1. 索引的本质字典的目录页是怎么工作的1.1 没有索引时MySQL在做一件笨事很多初学者以为查不到就走全表扫描查到了就走索引其实没这么简单。在没有索引的情况下MySQL对一张表做查询只能全表扫描full table scan。所谓全表扫描就是从表的第一页开始把每一行都读出来逐行跟WHERE条件比较匹配的就留下不匹配的就丢掉。假设一张表有800万行你要查 order_no A10086MySQL要老老实实读800万行做800万次比较然后返回命中的那一行。这里有个关键点InnoDB的最小读取单位是页page默认16KB。全表扫描意味着要把所有数据页都读到内存里过一遍即使你的目标是其中一行。数据量小的时候感觉不明显一旦表上了百万行或者千万行这种查询就会在慢查询日志里扎堆出现DBA半夜给你打电话就是这种场景。1.2 索引其实就是一份有序的目录索引做的事情本质上和字典的目录一样把某个字段的值单独拎出来按照一定顺序排好再存上一个页码——在MySQL里这个页码就是主键值。有了这个有序结构查找过程就变成先在这个有序集合里定位到目标值再按照页码去拿完整的那一行。定位有序集合用的是类似二分查找的思路一次能排除一半数据所以查询代价从遍历N行降到比较log2N次。索引在InnoDB里不是单独存在的一张表而是一棵B树。后面我会单独讲B树的结构。这里先记住它的核心特征索引列的值是有序的索引的叶子节点保存了指向完整行的通道这个通道在InnoDB里就是主键值。1.3 一组可以自己验证的数字我习惯用一个简单的模型来估算索引的收益。假设orders表800万行每行数据约200字节磁盘IO延迟约1ms每读一个页算一次IO无索引全表扫描约需要读 800万×200/16KB≈97656个数据页即使按顺序预读打个折也要几十到几百毫秒条件再复杂一点就到秒级。有索引等值查询B树三层高定位一个键大约3次IO找到主键后再回表读1次数据页总共4到5次IO毫秒级完成。这就是80万行数据3.2秒变40毫秒的底层逻辑。下一节我会解释为什么B树能把800万行装进三层树里这是面试必问也是理解索引优化的分水岭。2. InnoDB为什么选B树聚簇索引、二级索引与回表2.1 B树的结构与容量B树是B树的一种变体MySQL InnoDB引擎的索引和表数据都基于它。一棵B树长这样非叶子节点只存索引键和指向子节点的指针不存数据叶子节点存数据聚簇索引或主键值二级索引并且叶子节点之间用链表串起来整棵树按照索引键有序排列。为什么不用哈希表因为InnoDB要支持范围查询、排序、前缀匹配。哈希索引只能做等值查询一个范围查询就把哈希表pass了。为什么不用红黑树或AVL树因为树太高。红黑树虽然是平衡的但它每个节点只存一个键数据量一大树高就是二三十层每层一次磁盘IO性能没法看。为什么不用B树B树的非叶子节点也存数据同样容量下能存的键更少树更高。而且B树的叶子节点没有链表串联范围查询要反复从根往下走效率远不如B树在叶子链表上顺序扫描。容量算一笔账InnoDB一个页16KB去掉页头页尾可用空间按15KB算。如果索引键是bigint占8字节指针占6字节一个非叶子节点能放下约 15000/14≈1071个键。假设每行数据200字节一个叶子页能放约80条记录。那么一棵三层B树能索引的记录数大约是1071×1071×80≈9172万。也就是说九千万行的表用三层B树依然只要三次磁盘IO就能定位到叶子页。这就是B树最迷人的地方容量指数增长IO次数几乎不变。2.2 聚簇索引主键和数据是同一棵树InnoDB里有一句容易让人误解的话索引即数据。准确表述是InnoDB把整张表的行数据直接存在主键索引的叶子节点上。也就是说主键索引的B树叶子节点里存的是完整的一行记录而不是一个主键→地址的映射。这个结构叫聚簇索引clustered index。因为数据和索引长在一起InnoDB表在物理存储上就只能有一个聚簇索引。这也是为什么InnoDB强烈建议每张表都要建主键如果你不建InnoDB会先找一个非空的唯一索引当主键实在没有就生成一个6字节的隐藏rowid当主键。隐藏rowid对业务透明却在主从复制、数据迁移、二级索引统计时带来各种隐性麻烦。所以建表时显式定义一个自增主键是最省心的习惯。这也直接解释了面试题里为什么主键推荐用自增整数而不用UUID——关键点其实是聚簇索引的物理排序后面我会单独展开。2.3 二级索引与回表除主键索引外其他索引统称二级索引secondary index。二级索引的叶子节点存的是索引键 主键值不是完整行。你给user_id建一个普通索引查询时的动作分两步在user_id的B树上找到目标键拿到主键id回到主键索引的B树按id再查一次取出完整行。第二步就是常说的回表。回表不是免费的每回表一次大约多一次主键索引的随机IO。如果一个二级索引匹配到5000行就要回表5000次。优化方向自然就出来了要么让二级索引更瘦减少匹配行数要么干脆别回表。2.4 覆盖索引让查询不下树如果一个查询需要的所有列都包含在二级索引里那就不需要回表了。比如SELECT order_no FROM orders WHERE user_id 1001;如果存在复合索引(user_id, order_no)那么user_id和order_no全在索引里查询在二级索引上就能拿到全部结果连主键都不用回。EXPLAIN的Extra列会显示Using index性能极好。覆盖索引是线上性能优化里最常用的一招把高频查询的查询列塞进索引换取不回表。代价是索引变大、写入变慢所以只对真正高频的查询做。我们后面讲复合索引设计时会重点用它。3. 索引类型与选型你要的到底是哪一种索引3.1 四类索引一次看清索引类型核心特点数量限制典型场景主键索引聚簇索引数据存在它的叶子节点非空唯一每表只能1个每张表都必须有唯一索引值不允许重复允许NULL且多个NULL可多个order_no、身份证号、手机号等业务唯一字段普通索引只加速查询无约束可多个高频查询字段全文索引对文本分词后索引可多个大文本字段的模糊搜索这个表看似简单但选错的人不少。最常见的错误是把业务上唯一的字段建成了普通索引。少一个唯一约束等于把数据正确性的兜底交给了应用层。反过来也不建议给表里所有字段都建唯一索引唯一性校验本身有开销高并发插入时唯一索引的检查会成为热点。3.2 唯一索引的两个坑第一个坑MySQL允许唯一索引里有多个NULL。因为NULL在MySQL里被定义为未知未知不等于未知所以两个NULL不会冲突。如果业务上要求手机号要么不填填了就不能重复这个语义是符合的但如果要求手机号必须有且唯一那要在应用层配合非空约束。第二个坑重复值检查对写入性能影响明显。在插入或更新时唯一索引需要额外做一次冲突检测。批量导入几百万数据时如果目标表已有唯一索引导入速度可能慢好几倍。此时常见做法是导入后用INSERT语句配合ON DUPLICATE KEY UPDATE批量处理或干脆导入前禁用相关索引、导入后重建大表慎用在线DDL另说。3.3 前缀索引大字段的取舍对于很长的字符串字段比如email、日志消息、文章摘要直接建完整列索引很浪费空间。MySQL支持前缀索引index(email(10))表示只取email字段前10个字符建索引。前缀索引能显著缩小B树体积但有两个限制不能用于覆盖索引因为索引里没有完整列值也不能用于ORDER BY排序需要完整值。怎么定前缀长度用选择性判断SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10, COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15 FROM user;选择性接近1说明前缀区分度高80%以上基本可用。如果10个字符的选择性已经和全列差不多就没必要取更长省下来的空间都是真金白银。3.4 索引选择性决定一个字段值不值得建索引选择性 去重后的值数量 / 总行数。一个只有男/女两个值的字段选择性是2/100万0.0002%单独建索引几乎没有意义因为它过滤不掉多少数据优化器大概率还是全表扫描。真正值得建独立索引的是那些选择性高、又高频出现在WHERE里的字段比如user_id、order_no。判断方法很简单SELECT COUNT(DISTINCT user_id) / COUNT(*) AS sel FROM orders;低于某个阈值经验上20%~30%视数据分布而定的字段不要单独建索引可以放进复合索引当配角或者干脆不建。4. 复合索引与最左前缀原则为什么SQL明明带了两个条件却只用了一个索引4.1 复合索引的排序逻辑决定了天机复合索引也叫联合索引是一个索引里包含多个列但它的存储方式不是各存各的而是把多个列组合起来排序。以(a, b, c)为例B树的键先按a排序a相同再按b排序b再相同按c排序。这个逻辑和电话本按姓氏、名字排序一模一样先按姓氏排同姓的按名字排。这个排序规则直接推导出最左前缀原则一个查询能用到复合索引当且仅当它从最左边的列开始连续命中若干列。因为索引的全局顺序依赖最左列跳过了最左列索引里的相对有序性对查询条件就完全失效了。4.2 哪些写法能用上哪些不能仍然以索引(a, b, c)为例SQL条件是否用索引说明WHERE a1用ref命中最左列WHERE a1 AND b2用ref命中abWHERE a1 AND b2 AND c3用ref全部命中WHERE b2不用跳过了a索引顺序无意义WHERE a1 AND c3部分用只有a能定位c在索引层过滤靠ICP减少回表WHERE a1 AND b2部分用a走rangeb的定位条件在a的范围之后失效这里容易被误判的是WHERE a1 AND c3很多人以为字段都在复合索引里一定全用上。实际上c确实在索引里但索引按a、b、c排序在a固定而b未限定时c在索引里是无序的没法用B树的定位能力。MySQL只是把c当作普通过滤条件配合ICP在索引层过滤这比全表扫描好但没发挥出最左原则的全部功力。4.3 索引列顺序怎么设计综合多年经验复合索引的列顺序我一般按这个优先级排等值条件的列排最前选择性高的列排前面范围条件、、BETWEEN的列放最后高频查询优先排序需求也不要忽略。举个实际设计例子。订单表查询最常用的是WHERE statusPAID AND create_time BETWEEN ...。如果建(create_time, status)则范围字段在前status的等值条件无法再收紧索引如果建(status, create_time)则先按status精确到一个小集合再在集合内按create_time范围扫描显然更优。前提是status的选择性不能太低如果status只有两种值单独放最前意义不大但配合create_time做范围扫描仍有价值。4.4 排序字段与索引的关系B树天然有序所以ORDER BY如果和索引顺序一致MySQL可以直接走索引顺序输出避免filesort。filesort意味着MySQL要把结果集再排一遍小结果集还好大结果集非常伤。比如索引(status, create_time)ORDER BY status, create_time就能直接输出。MySQL 8.0还支持降序索引定义时写ASC/DESC排序方向也能匹配。这个点常被忽略EXPLAIN里看到Using filesort时第一反应就该检查排序字段能不能塞进现有索引。5. 索引失效的典型场景每一个我都排查过不止一次5.1 对索引列做函数或运算最常见的失效写法WHERE YEAR(create_time) 2023索引列被函数包裹后无法按原值定位索引失效。正确的等价写法是范围条件WHERE create_time 2023-01-01 AND create_time 2024-01-01同理不要对索引列做运算比如WHERE price * 1.1 100把运算挪到右边WHERE price 100 / 1.1。这个原则本质是让索引列以原样参与比较任何包一层的行为都会破坏B树的有序性。5.2 隐式类型转换这是最隐蔽的一类。字段phone是varchar查询写成WHERE phone 13812345678MySQL把字符串类型的phone字段转成数字去比较等于对索引列套了一个转换函数索引失效。注意反过来数字字段和字符串比较通常不受影响因为转换作用在常量上。稳妥的原则应用层和SQL里都保持类型一致不要依赖MySQL的隐式转换。这个坑在用户表、订单表里出现频率极高值得养成写SQL时先看字段类型的习惯。5.3 左模糊匹配B树按前缀有序LIKE keyword%可以走索引LIKE %keyword却不行——最左字符未知无法定位起始位置。业务里那种搜索包含某关键词的需求靠这个索引是没解的。小表可以接受全表扫大表就应该考虑全文索引、ES等专用方案或者干脆存反转字符串来支持后缀查询。这个坑在日志表和商品搜索里特别常见别指望MySQL单靠普通B树索引解决所有模糊搜索。5.4 OR条件WHERE name张三 OR age18如果只有name有索引MySQL没法在一半走索引、一半扫全表之间自由切换干脆全表扫描。两个条件都有索引时MySQL可能用index_merge合并两个索引结果但这也依赖优化器判断不如改成UNION ALL把两个查询拆开或者设计一个能同时覆盖两边的复合索引。遇到OR条件慢查询第一件事就是看两边字段是不是都有独立索引。5.5 NOT IN、!、NOT LIKE不等于类条件本质上是排除一小部分但B树的定位能力建立在等于、范围上对排除并不友好。优化器往往选择全表扫描因为扫描代价可能低于多次随机IO。如果业务确实要排除性查询且排除比例很小可以考虑反向设计用正向命中的结果集去处理。5.6 优化器认为全表扫描更快这是最反直觉的一条索引存在优化器也识别到了但rows估算显示扫描比例超过某个阈值经验上约20%~30%它认为回表的随机IO比顺序扫描更贵于是选ALL。这不算索引失效而是优化器的成本决策。解决方向不是强制索引FORCE INDEX只适合临时验证而是缩小结果集加限制条件、优化分页、改造查询逻辑等。5.7 IS NULL的误区很多文章说IS NULL一定失效其实不准确。MySQL对IS NULL的索引支持取决于版本和统计信息等值类IS NULL很多时候能走索引但IS NOT NULL在很多场景下确实放弃索引因为绝大多数行的那列都非空走索引反而要回表更多次。这个属于成本判断不必背结论跑一下EXPLAIN看type和rows最靠谱。5.8 复合索引没走最左前缀这个前面已经详述。失效场景里出现频率最高单独列出来提醒建了(a,b,c)查询只用b这不是索引坏了是设计时没对齐真实查询。优化方向不是加新索引而是调整复合索引的列顺序或者干脆改造SQL让条件按最左顺序出现。6. EXPLAIN实战一条慢查询的完整排查链路6.1 先看懂EXPLAIN的关键列排查索引问题第一步永远是EXPLAIN。我重点看这几列列含义判断标准type访问类型从好到坏system const eq_ref ref range index ALL。看到ALL/index就要警惕key实际使用的索引NULL表示没用到rows预估扫描行数和表总行数对比如果接近表大小就是全表扫Extra附加信息Using filesort、Using temporary是最常见的性能杀手其中type是最直观的typeref说明走了一个二级索引等值匹配typerange说明走了范围扫描typeALL就是全表扫描。达不到ref或range时说明索引没使上劲。6.2 Extra列的常见值Using index覆盖索引查询不需要回表最优Using index condition用了索引下推ICP条件在索引层过滤后再回表次优但很好Using whereMySQL在拿到索引结果后还要再用WHERE过滤通常是索引没覆盖全条件Using filesort需要额外排序尽量消灭Using temporary用了临时表常见于GROUP BY、DISTINCT、多表join。注意Extra里同时出现Using where和Using index不矛盾前者表示有过滤动作后者表示无需回表。6.3 一个真实案例的完整排查过程假设线上有慢查询SELECT id, order_no, amount FROM orders WHERE status PAID AND user_id 88721 ORDER BY create_time DESC LIMIT 20;第一步EXPLAIN结果typeALLrows800000ExtraUsing where; Using filesort。一眼就能定问题没走任何索引全表扫描还额外排序。第二步分析字段。status选择性极低user_id选择性极高create_time用于排序。设计思路必须让user_id作为等值定位列create_time顺势参与排序order_no和amount考虑覆盖。于是建复合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); 与上面等价二选一即可。这里顺序为什么是(user_id, status, create_time)而不是(status, user_id, create_time)因为user_id选择性远高于status而且status可能被优化器认为过滤比例太低。先按user_id定位到一个用户的所有订单再在用户内部按status过滤再按create_time排序整个查询都能在索引内完成。第三步再EXPLAINtyperefrows从800000降到该用户的订单数比如几十Extra里的Using filesort消失因为create_time在索引内有序。如果还想省掉回表把查询列order_no、amount也放进索引变成(user_id, status, create_time, order_no, amount)Extra会显示Using index或Using index condition级别覆盖查询达到最优。这个排查链路是我处理线上慢查询的标准动作EXPLAIN → 看type/rows/Extra → 按选择性排列复合索引 → 再看Extra确认覆盖情况。不要上来就猜索引没建一步步看数据。7. 索引设计的实战心得与建议7.1 索引不是越多越好这是我最想强调的。索引是读加速、写减速的典型每多一个索引INSERT、UPDATE、DELETE都要同步维护对应B树写入放大是实打实的索引还占用磁盘和缓冲池更麻烦的是索引越多优化器选错执行计划的概率越大索引统计信息稍微不准就可能出现明明有索引却不用的诡异情况。我在线上见过一张表建了14个索引业务写入慢到告警。经验上单表索引5个以内比较稳妥热点大表只对真正的慢查询建索引。7.2 冗余索引排查复合索引(a, b)建好后单列索引(a)就是冗余的——因为(a, b)的最左列a已经能支持axxx这类查询。同理唯一索引和普通索引重复的情况也经常出现。MySQL 8.0的sys库里直接有冗余索引报告SELECT * FROM sys.schema_redundant_indexes\G也可以查未使用索引SELECT * FROM sys.schema_unused_indexes;定期清理这两类索引是DBA和开发者都该做的日常操作。7.3 主键要设计好否则页分裂教做人聚簇索引的叶子节点按主键顺序排列。如果主键是自增整数新数据总是追加在树的最右端页填满就新开一页几乎不会引起页分裂写入效率很高。如果主键是UUID或随机字符串新行的主键随机插入到树中间频繁触发页分裂——既要移动页内数据又要更新页指针写入性能急剧下降索引碎片率持续升高。所以能自增就自增除非有强分片或安全需求才考虑有序UUID或雪花ID。这是面试常考也是线上建表的底气。7.4 索引下推ICPMySQL 5.6之后的白嫖福利回到前面WHERE a1 AND c3的例子。没有ICP时MySQL只能靠a定位到一批记录逐个回表再把c3筛掉。有ICP后c3这个条件被下推到索引引擎层在回表之前就先过滤掉不满足c的记录回表次数大幅减少。MySQL 5.6默认开启EXPLAIN里看到Using index condition就是触发了。想用好ICP核心是让过滤条件尽量落在索引列上这也是复合索引多设计一两个配角列的原因——它们不参与定位却能在索引层过滤。7.5 给刚要动手优化索引的你的最后建议第一永远先用EXPLAIN确认现状再动手别凭感觉加索引。第二对已经很大的表加索引不要直接执行裸ALTER TABLE用在线DDL工具如gh-ost或至少确认MySQL 8.0的INPLACE算法可以平滑执行避免锁表。生产环境一把锁下去后果是真的惨。第三索引优化是反复迭代的过程加完索引不等于结束观察一周慢查询日志看查询是否消失、写入是否有退化及时回滚不合适的索引。这些经验多数是我在线上踩坑换来的希望你能直接用上。最后再分享一个个人习惯我优化MySQL索引时始终把查询先写到最简放在第一位。很多索引花式失效根子其实是SQL写得太绕。先把SQL逻辑理清楚再谈索引设计——顺序反了优化永远追着问题跑。如果这篇文章里的哪个场景正好命中你的线上问题建议按EXPLAIN的排查链路完整走一遍大概率十分钟内就能定位到根因。
