1. 索引到底是什么一次磁盘I/O引发的思考1.1 为什么没有索引会慢先从一个我实际处理的真实问题说起。去年给一个电商后台做性能排查订单表有几十万行数据列表页每次查询都要花两秒多。加了索引之后同样的SQL查询时间直接降到了几十毫秒。这里面起关键作用的就是索引。如果一张表没有索引MySQL要找到某一行数据只能走全表扫描。所谓全表扫描就是从表的第一行开始一行一行地读进内存然后逐条匹配WHERE条件。表里有100万行数据最坏情况下要读100万行表里有1000万行就要读1000万行。这个成本是线性增长的数据量翻一倍查询时间也差不多翻一倍。这里有个很核心的概念叫磁盘I/O。InnoDB存储引擎的数据不是按行存储的而是按页存储的一个页默认是16KB。MySQL做全表扫描时需要把一个个数据页从磁盘加载到内存。磁盘I/O的速度和内存相比慢了大概三到四个数量级。也就是说一次随机磁盘读可能耗时10毫秒左右而一次内存访问只要几十纳秒。所以全表扫描慢的本质不是CPU不够快而是I/O次数太多了。索引解决的就是这个问题它利用一种高效的数据结构让你不需要扫描所有数据页只需要读几个关键节点就能快速定位到目标记录的位置。类比来说没有索引就像在一本没有目录的书里找某个关键词必须一页一页翻有索引就像先翻目录直接跳到对应页码效率完全不在一个量级。1.2 B树为什么能成为索引的事实标准MySQL InnoDB引擎的索引默认使用B树结构。为什么不是二叉树不是哈希表偏偏是B树这要从几个角度去理解。先说二叉搜索树。它的问题很明显数据如果按顺序插入二叉树会退化成一条链表查找复杂度变成O(n)。即便用红黑树这种自平衡二叉查找树每个节点最多只能有两个子节点数据量大时树的高度会非常高。树的高度决定了查询时需要经历的磁盘I/O次数高度为10就要读10次磁盘页这显然不可接受。再说B树。B树是一种多路平衡查找树一个节点能存储多个键值和多个子节点指针所以树能做得更矮。B树中每个节点都可以存放数据这意味着同样大小的节点里存放的键数量变少节点的分支数也会变少一定程度上影响了树的矮化效果。B树在B树的基础上做了两个关键改动改动一非叶子节点只存储索引键值不存储实际数据。这样每个数据页能容纳的键数量大大增加。拿16KB的页来算如果一个键占8字节加上指针占6字节那一个页大约就能容纳1200个键。高度为2的B树大概能存1200个叶子节点每个叶子节点按1KB算能存约16行数据那总共就是200万行左右高度为3时就能存二十多亿行。改动二所有叶子节点通过双向链表相连并且叶子节点本身按键值从小到大排序。这意味着B树天然支持高效的顺序访问和范围查询。你要查某个范围内的所有记录只需要找到范围起点然后沿着叶子节点的链表一路向后遍历即可不需要像B树那样在父子节点间反复回溯。我经常用一个类比来解释B树B树的非叶子节点就像书的目录只告诉你章节在哪一页叶子节点才是正文内容本身。目录做得再大也不会影响正文的查找反而因为目录更精简翻目录的速度更快。1.3 InnoDB中索引和数据是怎么存在一起的InnoDB的索引和数据其实没有办法完全分开讨论因为它是按照聚簇索引clustered index来组织数据的。每张InnoDB表都必须有一个聚簇索引这个聚簇索引的叶子节点存储的是整行完整记录索引的顺序决定了表数据物理存储的顺序。默认情况下聚簇索引是按照主键来建的。如果你建表时没有定义主键InnoDB会找第一个非空的唯一索引作为聚簇索引如果也没有唯一索引它会在后台隐式生成一个6字节的ROWID作为聚簇索引键。除了聚簇索引其他索引统称为二级索引secondary index或辅助索引。二级索引的叶子节点存储的不是完整行记录而是索引列的值加上主键值。这意味着如果通过二级索引查找数据通常需要拿到主键值之后再到聚簇索引中查一次完整行记录这个过程就叫回表。这里有一个非常实际的设计含义二级索引的大小很大程度上取决于主键的大小。主键越长每个二级索引的叶子节点能容纳的记录数就越少索引占用的磁盘空间就越大回表时查聚簇索引的效率也会受影响。所以在InnoDB表里选择主键不是一个可以随便对待的问题后面我会专门用一章来讲主键该怎么选。2. 常见索引分类与选型2.1 普通索引、唯一索引、主键索引到底怎么选MySQL里的索引从功能上划分主要有这么几种。普通索引INDEX是最基础的索引它没有任何限制只负责加速查询。唯一索引UNIQUE INDEX在普通索引的基础上增加了唯一性约束索引列的值不能重复允许为NULL但NULL可以出现多次。主键索引PRIMARY KEY是特殊的唯一索引它不能为NULL而且在一张表里最多只能有一个。用一句话来记区别普通索引只要你需要提速就可以建唯一索引还要保证业务上不允许重复主键索引不仅要保证唯一还要承担聚簇索引组织的责任。实际工作中我经常遇到有人问既然唯一索引和主键索引都有唯一约束为什么不能全部用唯一索引当主键因为主键索引是聚簇索引的入口它直接决定了数据页的物理排布而唯一索引只是在普通二级索引上加了约束两者在存储层面承担的角色完全不同。选型时我的建议是业务查询的核心筛选字段优先考虑普通索引或联合索引需要保证业务唯一性的字段用唯一索引主键尽量用与业务无关的、单值的、有序递增的字段同一张表不要建太多索引否则写入时要维护所有索引树写入速度会明显变慢。2.2 联合索引的最左前缀原则是怎么回事联合索引也叫复合索引是指同时给多个列建立一个索引比如INDEX idx_user_status (user_id, status)。很多人以为只要建了联合索引查询里带任何一列都能用上索引其实这是个误区。联合索引的核心规则是最左前缀原则。这个原则的意思是联合索引的生效顺序是从左往右查询条件中必须包含最左边的列索引才会被使用跳过了左边的列右边的列就无法利用索引。举个具体例子。假设有一个联合索引(a, b, c)WHERE a 1能用索引WHERE a 1 AND b 2能完整用上索引WHERE b 2 AND a 1同样能完整用上索引因为MySQL优化器会做条件下推和重排把查询条件调整成a 1 AND b 2的顺序WHERE a 1 AND c 3只能用到索引中的a列c列无法通过索引定位因为中间缺了bWHERE b 2完全用不上这个联合索引。为什么必须有这个限制因为B树是按索引键顺序存储的(a,b,c)这个联合索引先按a排序a相同再按b排序b相同再按c排序。索引顺序本质上是一个级联排序逻辑缺少前边的排序键后边的键值在树中根本不是一个连续区间自然无法快速定位。最左前缀原则还影响排序。比如SELECT * FROM t ORDER BY a可以用索引ORDER BY a, b也可以用但是ORDER BY b, c用不了。理解了B树的存储顺序这个结论就不需要死记了。2.3 哪些场景适合建索引哪些坚决不适合这些年做过的慢查询优化里我把适合建索引的场景归纳为四类第一类出现在WHERE条件中、频繁作为筛选依据的列。这类列是索引最容易发挥价值的地方。第二类经常用于ORDER BY或GROUP BY的列。索引本身就是有序的如果查询结果可以利用索引顺序直接输出MySQL就可以避免额外的排序操作省掉filesort。第三类多表关联查询中的关联字段。比如订单表和用户表通过user_id关联那user_id就应该有索引否则关联时要用嵌套循环反复扫表代价极高。第四类需要保证唯一性的列如手机号、身份证号等可以用唯一索引既能加速查询又能约束数据。那哪些场景不适合建索引我归纳出了四种第一种数据量太小的表。几百行甚至几千行的数据MySQL直接全表扫描代价不过几次I/O建索引反而要额外维护索引结构性能上没有收益还占磁盘空间。第二种更新频率特别高的列。写入一条记录时MySQL不仅要更新数据页还要同步维护这张表上每一个相关索引的B树。插入、更新、删除越频繁索引维护成本越高可能直接拖垮写入性能。第三种区分度太低的列。比如性别字段只有“男、女”两个取值再往下查也就是两种可能索引的选择性太低。用这种列建索引每一层树节点差不多都会命中大部分页最后优化器往往选择全表扫描。第四种参与了函数运算或表达式运算的列。如果查询时总要写WHERE DATE(create_time) 2024-01-01这种写法索引列被函数包裹后原来的B树顺序就失效了建了也大概率用不上。3. 索引失效的八个经典场景与原理3.1 索引失效场景清单我在面试候选人时发现很多人能背出“索引失效有哪几种情况”这个题但一旦问“为什么”就答不上来。这里我把自己日常开发和排查中总结的八个经典失效场景整理成一张清单每个场景都配上原理说明。场景SQL示例失效原理对索引列使用函数WHERE LEFT(name, 3) abc函数改变了索引列的值B树的键顺序失效隐式类型转换WHERE phone 13800138000phone是字符串类型MySQL会将字符串转数字后再比较模糊匹配以通配符开头WHERE name LIKE %张%无法利用B树的有序性确定起始位置OR条件中存在非索引列WHERE age 18 OR status 1无法把索引列条件与普通列条件合并成一次索引扫描联合索引不满足最左前缀WHERE b 2 AND c 3缺少第一列的排序锚点联合索引范围查询后的列WHERE a 1 AND b 2范围查询导致后续列区间不连续对索引列做计算WHERE age 10 30索引列参与表达式计算原键值顺序失效优化器选择全表扫描小表或统计信息严重失真的情况优化器基于代价估算认为全表扫描成本更低第一类对索引列使用函数是最常见也是最隐蔽的问题。你可能会想WHERE name LIKE 张%能用索引那WHERE LEFT(name, 1) 张为什么就不能原因很简单索引的键值是原始列值不是经过LEFT函数计算后的结果。B树里存储的是“张三”“李四”这种原始字符串函数计算后的结果和原始值之间没有直接的顺序映射关系MySQL只能扫完所有索引值再计算索引自然失效。第四类OR条件的失效我要多说一句。WHERE age 18 OR status 1如果只有age上有索引MySQL无法只走索引找到所有age18的记录再附加status1的记录因为OR要求两个条件满足其一两个分支的集合需要合并而status没有索引可以做快速定位最终就退化成全表扫描。解决办法是给status也建索引或者改写为UNION ALL。3.2 隐式类型转换和范围查询的坑隐式类型转换这个坑我在生产环境里踩过一次印象非常深刻。有一张用户表手机号字段用的VARCHAR类型但查询代码里把手机号当成数字传了进去写成WHERE phone 13800138000。结果索引完全失效全表扫描每次查询耗时好几秒。为什么字符串列和数字比较会失效因为MySQL有一个隐式转换规则当字符串列和数字值比较时它会把字符串列的值转换为数字再比较。这么一转换相当于对索引列套了一层CAST函数键值的顺序自然就乱了。后来我把查询改成WHERE phone 13800138000耗时从秒级降到毫秒级。再看范围查询。有一个联合索引(a, b)执行WHERE a 1 AND b 2从最左前缀原则来看a可以用索引定位但b能不能用取决于a的条件是什么。当a是等值条件时b还能保持有序当a是范围条件时每个满足a条件的分支里b的顺序是全局不一定的无法直接通过索引继续精确定位b2所以b列就失效了。这是联合索引设计中非常重要的一个权衡点到底把等值条件的列放前面还是把范围条件的列放前面要看实际业务里高频查询是哪种形态。3.3 优化器为什么“不听话”有时候你明明建了索引SQL里也没有函数、没有类型转换、符合最左前缀但EXPLAIN一看还是全表扫描。这时候别急着怀疑人生先想想优化器。MySQL的优化器是基于代价模型来选择执行计划的。它估算全表扫描的I/O次数、CPU消耗、内存排序开销等等最后算出总代价。如果它认为走索引的代价比全表扫描高就会放弃索引。典型场景是表数据量很小或者查询条件会命中表中很大比例的数据比如区分度很低的字段。还有一种是统计信息失真。InnoDB通过随机采样来估算索引的区分度如果表长时间没有执行ANALYZE TABLE统计信息可能已经过时优化器会基于错误的数据做出判断。这种情况的排查方法是先执行ANALYZE TABLE 表名;更新统计信息再重新EXPLAIN一次。另外还要注意SELECT *对优化器决策的影响。如果你查的是SELECT *而二级索引里没有全部列MySQL必须回表读取完整行记录。当回表比例很高时它的代价还不如全表扫描低优化器就会放弃二级索引。这也引出了一个优化方向尽量用覆盖索引来减少回表后面我会详细讲。4. 主键设计与聚簇索引的存储细节4.1 自增主键和UUID主键差的不是一点半点主键的选择直接影响聚簇索引的物理结构。InnoDB聚簇索引的叶子节点按主键值顺序排列这就决定了主键的顺序性极其重要。如果使用自增主键每次插入的新记录主键值都比之前的大InnoDB只需要在聚簇索引的最右端追加新页即可不需要移动已有数据。这种情况下的插入性能是最优的。如果使用UUID作为主键麻烦就来了。UUID是随机生成的字符串主键值在全局范围是无序的新插入记录的主键可能落在已有数据页的中间位置。为了保持B树有序InnoDB不得不把原数据页分裂成两半把新记录插入到中间这个过程叫页分裂。页分裂不仅带来额外的I/O和CPU开销还会导致数据页出现碎片和空洞使表空间膨胀。我做一个简单的对比表格帮助理解对比维度自增主键UUID主键插入顺序完全有序追加随机分布页分裂概率极低高二级索引占用小主键短大主键长分布式场景有冲突风险天然全局唯一数据迁移可能冲突无需改造那UUID是不是完全不能用也不是。如果在分布式场景下需要全局唯一的ID又没法用数据库自增我建议不要直接用随机UUID而是用雪花算法Snowflake或改进的顺序UUID。这类ID是趋势递增的虽然不完全连续但能显著减少页分裂。说到底主键的选择是业务需求与存储结构之间的平衡关键是要清楚每种方案的代价。4.2 回表、覆盖索引和索引下推回表这个动作是理解二级索引性能的关键。假设有一张用户表主键是id普通索引建在phone上。执行SELECT * FROM users WHERE phone 138...时MySQL会先通过phone索引找到对应记录的id值然后用这个id去聚簇索引里再查一次才能拿到完整行数据。第二次查询就是回表。如果一次查询命中了50行就可能发生50次回表这是不小的I/O开销。覆盖索引就是用来消除回表的。如果查询需要的所有列都包含在一个二级索引里那么MySQL只需要遍历这个索引本身就可以返回结果不需要回表。比如SELECT phone FROM users WHERE phone 138...phone索引已经包含了phone列和id列要的数据都在索引里直接返回即可。Extra里显示的Using index就是这个意思。索引下推Index Condition PushdownICP也是一个容易被忽略的优化。在没有ICP时MySQL使用二级索引查询会先通过索引把所有可能的记录主键拿回服务层再进行WHERE条件的过滤。而有了ICPMySQL允许在存储引擎层直接根据索引中包含的字段做过滤减少回表次数。从MySQL 5.6开始这个特性默认开启。EXPLAIN的Extra里出现Using index condition就是索引下推生效的标识。这三个机制放在一起理解其实是一个目标能不回表就不回表能少回表就少回表。因为回表意味着一次随机磁盘I/O在机械硬盘时代代价尤其昂贵。4.3 关于页分裂的补充实践主键设计影响页分裂这一点光记住结论不够最好能在实际运维中观察一次。我讲一个常见现象某张表使用UUID主键业务量上去之后发现表空间大小远超实际数据量碎片率也很高。用SHOW TABLE STATUS看到的Data_length明显大于预期或者information_schema里的碎片统计很大就基本上可以判断页分裂和页空洞已经比较严重了。缓解措施除了换主键方案还可以定期做表的整理操作把碎片合并回收。当然更好的方式是从一开始就选择有序的ID生成策略不要等到线上出现性能问题再补救。5. 索引相关的SQL“源码”与面试高频题5.1 可直接复用的索引操作SQL学了这么多原理最终还是要落到SQL上。下面我给出一套自己日常开发和排查索引问题时反复使用的SQL片段可以当作“源码”直接复制调整。-- 建表时直接创建索引 CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, -- 普通索引 INDEX idx_user_id (user_id), -- 唯一索引 UNIQUE INDEX uk_order_no (order_no), -- 联合索引 INDEX idx_user_status (user_id, status) ) ENGINEInnoDB; -- 为已有表添加索引 ALTER TABLE orders ADD INDEX idx_create_time (create_time); -- 查看表的索引信息 SHOW INDEX FROM orders; -- 分析某条SQL是否走索引 EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC; -- 强制走索引仅供测试生产慎用 SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id 1001; -- 删除索引 DROP INDEX idx_create_time ON orders; -- 分析表更新优化器统计信息 ANALYZE TABLE orders;EXPLAIN输出里最需要关注的几个字段是type、key、rows、Extra。type从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描需要警觉。key表示实际使用的索引rows是预估扫描行数Extra如果出现Using index说明是覆盖索引出现Using index condition说明用了索引下推出现Using filesort说明存在额外排序。我建议所有开发同学都把EXPLAIN用熟。拿到一条慢SQL我会习惯性地先看type是不是ALL再对比rows和实际行数最后看Extra里有没有隐性问题。这套流程基本上能解决90%的MySQL单表慢查询。5.2 索引面试高频问答这里我把被问得最多的几个索引问题做个汇总顺便给出参考答案。为什么MySQL选择B树而不是B树B树的非叶子节点比B树更“瘦”只存键不存数据所以同样16KB的页能容纳更多键树高更低I/O次数更少B树的叶子节点用链表串联范围查询和排序效率更高B树的叶子节点之间没有链表范围查询需要重复中序遍历效率差很多。Hash索引比B树快为什么不直接用Hash索引做等值查询确实快时间复杂度接近O(1)但它只能处理等值匹配无法支持范围查询也不支持排序而且可能存在哈希冲突导致性能抖动。所以InnoDB里的哈希索引主要是自适应的由存储引擎根据热数据自动构建不需要也不建议开发人员手动创建。什么情况叫“最佳左前缀”而不是“最左前缀”其实是一个概念表达上以官方文档为准。联合索引从最左边列开始连续匹配才能生效跳过某列后面的列就用不上。理解成“联合索引的生效从最左开始连续”就够了。为什么建议少用SELECT *SELECT *通常不是覆盖索引所有非索引列都要回表。如果业务只需要两三个字段尽量把字段列全或保证字段都被索引覆盖能显著减少回表量。索引一定能让查询变快吗不一定。索引的本质是空间换时间也有维护成本。写多读少的表、数据量太小的表、区分度太低的列建索引可能得不偿失。最终要看优化器的代价评估。5.3 一个完整的慢查询排查实战最后分享一个实战案例把这套方法串起来。某天收到告警用户查询接口出现大量慢SQL单次耗时超过3秒。原始SQL大致是这样SELECT * FROM user_orders WHERE order_no LIKE %20240115% AND status 1;EXPLAIN之后发现typeALL、rows200万order_no上有索引但没被使用。问题一行就能看出来LIKE的通配符在开头导致索引失效。我的处理分了三步第一步先确认业务需求。和产品确认后发现这个页面的搜索框要求是“订单号后6位精确匹配”不是任意位置模糊匹配。那问题就简单了直接把查询改成等值匹配SELECT * FROM user_orders WHERE order_no CONCAT(20240115, 123456);第二步如果确实需要做模糊搜索业务上可以接受的话加一个专门的冗余列存去掉前缀后的短单号再对这个短单号建索引。或者用INSTR函数扫描但那是全表扫不适合大数据量。第三步为了防止后续上线的新查询再次踩坑我把线上搜索接口的查询全部review了一遍凡是LIKE通配符开头的、函数包索引列的、隐式转换的SQL全部整改。半年之后再看慢查询列表这个接口再也没出现过。这段经历让我越来越觉得索引知识不能只停留在“建索引”这一步更重要的是理解数据结构、理解优化器选择、理解业务和存储之间的权衡。只要你把这三个层面的逻辑打通了很多问题都不用背推理就能推出来。我个人在实际操作中的体会是索引优化不是一次性的工作它是和业务增长同步演进的。数据量没上来之前全表扫描也许完全够用等数据量上来以后再小的优化也会被放大成显著的收益。所以建议大家在日常开发中养成先看执行计划的习惯每次都多问一句“这条SQL为什么没走索引”长期积累下来你对MySQL的敏感度一定会比别人高出一截。