刚接手一个线上系统的时候我遇到过一条查询跑两秒多的SQL全表扫描扫了三百多万行而业务要求响应在一百毫秒内。当时的我连EXPLAIN都不会看被DBA指着鼻子说你这表连索引都没建对。从那以后我把MySQL索引从数据结构到执行计划彻底啃了一遍也踩了不少坑。这篇东西就是基于这些经验整理出来的想把索引这件事从头到尾讲透适合刚入门的朋友也适合那些会用索引但说不清原理的开发同学。我不会只给你一堆结论而是把为什么也讲明白为什么B树成为主流、为什么最左前缀会失效、为什么覆盖索引能省一次回表。这些都是面试高频题更是日常调优真正用得上的底层逻辑。1. 慢SQL的根源全表扫描为什么那么贵1.1 磁盘IO才是第一成本很多写业务代码的同学对数据库性能的认知停留在加了索引就快没加索引就慢。这没错但不够。真正决定查询快慢的是数据从磁盘读到内存的次数也就是磁盘IO。MySQL默认的数据页大小是16KBInnoDB存储引擎的数据以页为单位组织。一次全表扫描意味着要把表所在的每一条记录所在的页都读一遍。假设一行记录平均200字节一个16KB的页大概能装下80行三百万行就要37500个页。就算这些页都在缓冲池里也要走一遍全部页的扫描逻辑如果其中一部分不在内存里就要走磁盘IO一次随机读磁盘的耗时大概是10毫秒级别碰上机械盘更慢。索引干的事情本质上是把大海捞针式的查找变成按目录翻书式的定位。有了合适的索引MySQL可以只读取符合条件的极少数记录所在的页IO次数从几万次降到几次。1.2 没有索引时MySQL是怎么找数据的在没有可用索引的情况下InnoDB只能从第一条记录开始一条一条往下扫判断每一行是否满足WHERE条件这个过程就是全表扫描typeALL。这不是MySQL笨而是它确实没有更好的信息可以用。有人说那我可以把查询结果缓存起来啊。且慢查询缓存早已在MySQL 8.0中被移除因为在高并发写入场景下缓存失效太频繁收益远小于维护成本。真正的出路就是让存储引擎能用更少的IO定位数据这就是索引存在的意义。1.3 换个角度理解索引是空间换时间的典型索引本质上是额外存储的一种数据结构它占用磁盘空间带来写入时的维护开销但换来读取时的大幅提速。这和字典的部首目录、书本的目录页是一个道理目录本身也占了几页纸但你不会因为多这几页纸而觉得书变贵因为你翻目录要快得多。理解了这一点后面很多问题就有了统一答案为什么不能建太多索引因为写入时每多一个索引就要多维护一棵树。为什么不要在大字段上建索引因为树会变得又大又深。2. 数据结构选型推演B树凭什么成为主角2.1 候选者逐个淘汰的过程索引的底层数据结构历史上出现过Hash、二叉树、红黑树、B树最后B树胜出。我们来逐个看它们淘汰出局的原因。Hash索引对索引列计算Hash值定位速度极快O(1)复杂度。但它只支持等值查询、IN、某些情况下一碰到范围查询、、BETWEEN就无能为力了因为Hash值是无序的。另外它不支持最左前缀匹配不支持用索引排序。业务查询里范围查询太常见所以Hash索引只能作为辅助比如InnoDB的内存自适应Hash索引咱平时建索引用不上它。普通二叉树/红黑树二叉树在极端情况下会退化成链表红黑树虽然能保持平衡但它的问题是一个节点只存一个键值树的层数随数据量增长太快。MySQL的数据是存磁盘的每往下一层就要做一次磁盘IO。两百万行数据红黑树大概要二十多层意味着最坏情况下要二十多次磁盘IO这个成本不可接受。B树多路平衡查找树一个节点可以存多个键值每个节点有多个子树分支层数被大幅压低了。B树已经解决了层数太深导致IO太多的问题为什么还是败给了B树关键在于B树的每个节点既不存索引键又存实际数据而且为了容下数据每个节点的索引键数量变得有限。更致命的是B树做范围查询时需要在中序遍历过程中不断在树的各层节点间来回跳转等于不断发起随机IO。2.2 B树的胜出点两条核心差异B树和B树的结构上只有两个核心差异恰恰是这两个差异让MySQL选择了它第一非叶子节点只存键值不存数据。这样单个节点能容纳的键值数量大幅增加。InnoDB一个页16KB假设一个索引键8字节加上指针6字节左右一个叶子节点能存放上千个键三层B树就能支撑上千万行数据。这意味着绝大部分查询只需要两到三次磁盘IO就能命中。第二叶子节点通过双向链表串起来。所有数据都存在叶子节点并且叶子节点之间按顺序链接。范围查询只需要找到起点然后沿着链表顺序往下读即可连续IO的性能远好于随机IO。ORDER BY排序在很多场景下也能直接利用这个有序链表省掉一次filesort。2.3 关于B树的两个补充认知关于树的高度三层B树大概能存放多少数据我们粗算一下。非叶子节点的每个条目大约占键值比如BIGINT做主键是8字节加指针约6字节按14字节算一个16KB页大约能放1170个条目。假设叶子节点每行数据1KB一个页放16行两层非叶子节点加一层叶子节点总共能放大约1170×1170×16也就是两千多万行。三层满的状态下只要3次IO非常高效。关于页的分裂与合并B树插入时如果节点满了会发生页分裂把一个节点的部分数据挪到新页中删除导致节点过空时也可能发生页合并。这个机制意味着索引列的乱序插入会带来大量页分裂碎片化严重影响性能。这也是为什么主键推荐自增而不是UUID——UUID随机性高插入时页分裂频繁同时导致索引树节点之间逻辑相邻但物理离散查询性能也会打折扣。3. 索引家族全景从主键到二级索引每一类都派什么用场3.1 聚簇索引InnoDB的表即索引很多人背概念时把主键索引和聚簇索引划等号严格说聚簇索引的定义更宽。InnoDB中聚簇索引的叶子节点就是整行数据表数据就是按主键顺序组织在这棵B树上的。所以InnoDB表实际上是一棵以主键为索引键的大B树。如果没有显式主键InnoDB会找第一个非空的UNIQUE列作为聚簇索引要是还没有就自动生成一个隐藏的ROWID。这个设计解释了为什么建表时强烈建议显式指定自增主键主键是物理存储顺序依据自增保证顺序插入页分裂最少业务列的随机值做主键则会导致频繁的页分裂和移动。3.2 二级索引从书签到正文的两次定位非聚簇索引也叫二级索引、辅助索引是另外建立的B树它的叶子节点存储的是索引键值加主键值不是完整行数据。当你通过二级索引查询时先在这棵索引树上找到主键再拿着主键去聚簇索引树里找完整行这个过程叫回表。这里有个关键认知每一条二级索引都隐藏地包含主键列。假设你给name建了一个索引索引的叶子节点上实际存的是name和id两个字段。这意味着如果查询只需要name和id就不需要回表直接从索引里取就行了。这个特性正是覆盖索引后面细讲能省IO的根本原因。3.3 各类索引的适用场景与禁忌普通索引INDEX最常用的索引类型不强制唯一性适用于WHERE、JOIN、ORDER BY中频繁使用的列。允许重复值这正好对应一个姓氏有很多人的场景。唯一索引UNIQUE INDEX和普通索引的区别是列值不能重复。它除了加速查询还承担了完整性约束的作用比如用户表的手机号、订单表的订单号。注意唯一索引不能为空值可重复InnoDB允许同一列有多个NULL值。主键索引PRIMARY KEY唯一索引的特例且不允许NULL。一个表只能有一个主键但可以有多个唯一索引。前缀索引对于TEXT、VARCHAR等大字段可以只对列的前N个字符建索引大幅缩小索引体积。代价是可能区分度不够后文会讲怎么确定前缀长度。全文索引FULLTEXT用于中文分词、搜索场景的倒排索引注意它是倒排索引而不是B树。MySQL自带的全文索引在中文场景下分词能力一般生产上大家更常借助专门的搜索引擎这里不展开。合理选择索引类型本身就能避免很多问题。给一个低区分度的列比如性别建普通索引查询优化器扫一遍算了半天发现走索引跟全表扫描差不多甚至更差最后还是选全表扫描这个索引等于白建。4. 失效场景大起底索引写了但查询用不上多半是这几个原因4.1 最左前缀失效复合索引的第一列被破坏复合索引a, b, c相当于建立了a、ab、abc三套索引逻辑但它的根基是最左前缀原则查询条件里必须从第一个列开始连续匹配且中间不能断开。最经典的失效场景就是跳过第一列比如WHERE b1 AND c2这样的查询无法使用这个复合索引。为什么回顾B树的结构复合索引的键值按a排序a相同再按b排序b相同再按c排序。如果不知道a的范围你就无法确定b这个字段在树里哪个区间是连续的自然只能全表扫描。同理WHERE a1 AND c2会走索引吗会但只用到a1的索引部分c2的部分没生效要在a确定的区间内做过滤。实操经验设计复合索引时把等值查询列放在前面范围查询列放在后面。比如经常执行WHERE status1 AND create_time2024-01-01复合索引应该建status, create_time因为status是等值条件能最大化利用索引的有序性。4.2 隐式类型转换你以为查的是数字MySQL看到的是字符串经典的坑索引列是varchar类型查询条件却写成了WHERE phone13800138000。MySQL会对列做隐式转换相当于把索引列包了一层CAST函数导致索引失效。判断时注意索引列参与了函数或者表达式运算索引就会失效。因为B树存储的是原始列值不是函数处理后的值优化器无法直接利用树的有序性去定位。同样的还有WHERE DATE(create_time)2024-01-01 这种对索引列使用函数的情况。正确做法是改写成范围查询WHERE create_time2024-01-01 AND create_time2024-01-02。这不仅能走索引还能用上索引的range定位能力。4.3 LIKE模糊查询前缀匹配能走中间通配符直接放弃WHERE name LIKE 张%是可以走索引的因为B树按照字典序排列张%对应一个明确的字典区间MySQL可以在树中快速定位到起点然后顺序扫描。但WHERE name LIKE %张或者WHERE name LIKE %张%就失效了因为通配符在开头时优化器不知道起点在哪只能全表扫描。顺带提一嘴能用前缀匹配的就别用后缀匹配实在需要全文搜索的场景请考虑全文索引而不要指望普通索引。4.4 联合索引中的范围列右边全部作废复合索引a, b, c如果查询是 WHERE a1 AND b10 AND c2。a用的是等值b用了范围查询c还能用上索引吗不能。因为b10范围内的c字段在B树里是无序的。这个规律在B树的复合键排序规则下是必然的。很多面试题喜欢问这个实际设计中也要记住尽量把范围查询列放到复合索引的最后。如果你的查询模式固定是a1 and b between and c2那复合索引应该设计成a, c, b把范围列b放最后。4.5 负向查询与OR场景优化器的预算选择WHERE status 1、NOT IN、NOT LIKE这类负向查询在某些情况下会被优化器放弃索引。原因是负向查询往往匹配大量行走索引反而要做大量回表随机IO不如全表扫描顺序读高效。OR条件的坑更微妙WHERE a1 OR b2即使a和b各自都有索引MySQL也未必能合并使用两个索引除非用到**索引合并Index Merge**优化。早期版本对OR的处理比较弱经常直接全表扫。如果你确认a、b都有独立索引且区分度高可以通过EXPLAIN观察Extra列是否显示Using union或Using intersect来判断是否走了索引合并。更稳妥的做法是改写为UNION ALL分支查询或者重新设计索引。4.6 关于IS NULL和IS NOT NULL不是绝对失效要看数据分布有个流传很广的说法是IS NULL不走索引这在MySQL较新版本里并不绝对。优化器会基于索引统计信息估算返回行数极小时可能用索引返回行数多时选择全表扫描。所以别死记结论要用EXPLAIN实测。这也引出一个通用的调优态度一切以执行计划为准不要在脑子里背一堆注定会过时的铁律。5. 聚簇索引、回表与覆盖索引用好这三者的关系查询能快一个量级5.1 回表的成本账比你想象中贵刚才说了二级索引叶子节点存的是索引键主键值。如果你查询的列不在这个二级索引里就得靠主键到聚簇索引树里再查一次完整行。一次回表就是一次聚簇索引树的B树搜索大约是两到三次磁盘IO。如果我们通过二级索引筛选出一条记录回表一次还好但如果筛选出几千几万条就要回表几千几万次这是真正的性能灾难。这里有个真实对比一张一千万行的订单表WHERE user_id12345 ORDER BY create_time DESC LIMIT 20。如果你只建了(user_id)单列索引MySQL需要先取出该用户的所有订单可能几百上千条每个都回表拿到整行再在内存里排序取前20条。如果我改成(user_id, create_time)复合索引叶子节点上已经按create_time排好序直接按索引顺序取出前20条记录回表连filesort都省了。查询耗时从几百毫秒直接降到几毫秒。5.2 覆盖索引把查询列塞进索引树覆盖索引指的是查询所需的全部列都能在二级索引树里直接拿到不用回表。Extra列显示Using index就代表发生了覆盖索引。举个例子表里有a、b、c三列复合索引(a, b)执行SELECT a, b FROM t WHERE a1。因为索引叶子节点有a和b而且二级索引里还附带主键id所以这个查询完全不需要碰聚簇索引树。这里面有个容易被忽略的小知识只要二级索引的键列和主键列cover住查询列就算覆盖索引。所以SELECT的列越少越容易借助覆盖索引。很多开发习惯SELECT *这会让覆盖索引失效被迫回表拿所有列。5.3 索引下推ICPMySQL 5.6后的重要优化有了复合索引(a,b)查询WHERE a1 AND b10。在MySQL 5.6之前InnoDB会先用a1从索引树定位到一批记录然后逐条回表取出整行再对b10做过滤。5.6引入的ICPIndex Condition Pushdown把b10的过滤下推到存储引擎层在索引遍历过程中直接判断索引字段b是否满足条件满足的才回表。所以这条SQL在5.6以后的版本里回表的次数会大幅减少Extra列通常显示Using index condition。这个优化提醒我们复合索引的设计不仅影响定位效率还影响回表次数。把过滤条件里最常用的列放进复合索引往往能同时享受ICP的收益。5.4 一个辅助判断技巧看Extra列的三个关键词平时用EXPLAIN看执行计划时Extra列的几个关键词要能一眼识别Using index覆盖索引全程没回表最优。Using index condition走了索引下推部分过滤在索引层完成回表次数减少很好。Using where索引定位后还需要回表拿数据再做一次过滤注意这通常意味着有额外的非索引列条件。Using filesort排序没用上索引在临时表做了排序小数据量还好大表是性能杀手。Using temporary用了临时表常见于GROUP BY、DISTINCT、多表JOIN等场景。这个表观之外再叠加type列综合判断就能对一条慢SQL的性能问题有个八九不离十的判断。6. 让EXPLAIN开口说话执行计划的阅读方法与实战判断6.1 执行计划里到底该看哪几列EXPLAIN是MySQL调优最基础的工具在SQL前面加上EXPLAIN就能看到优化器选定的执行路线。列很多不是每列都重要我的习惯是重点看这几列type列访问类型的等级排序基本是 system const eq_ref ref range index ALL。system和const表示通过主键或唯一索引精准定位到一行这是最优的ref是普通等值匹配range是范围扫描比ref多扫描一点但还能接受index表示扫描了整棵索引树ALL是全表扫描需要警惕。key列实际用到的索引。很多人只看这一列以为用了索引就万事大吉其实关键要看type和rows。用了索引但typeindex等于把整棵索引树从头扫到尾比全表扫描也好不到哪去。rows列优化器估算的需要扫描的行数。这个值越大SQL越危险。它只是估算值不一定精准但用来对比不同写法的成本高低很有参考价值。Extra列包含了前面提到的Using index、Using where、Using filesort等关键信号。6.2 实战案例同一个查询三种写法的执行计划差异假设订单表有user_id、status、create_time三列都建了单列索引。查询一SELECT * FROM orders WHERE user_id123 AND status1。优化器可能选择用户ID索引ref先定位用户的所有订单再回表对status过滤Extra显示Using where。这意味着user_id索引生效但status条件没被利用。查询二SELECT user_id, status, create_time FROM orders WHERE user_id123。如果建立了(user_id, create_time)复合索引这个查询的查询列正好都在索引里Extra显示Using index不需要回表性能最优。查询三SELECT * FROM orders WHERE DATE(create_time)2024-01-01。create_time索引列被函数包裹typeALL全表扫描rows很大。改写成范围查询后typerangerows大幅下降。每遇到一个慢SQL我都建议把几种候选写法都EXPLAIN一遍比较rows和Extra选择执行计划最合理的那个。别凭感觉调优让优化器用执行计划告诉你答案。6.3 关于索引统计信息为什么有时候执行计划不听话有一种让人困惑的场景明明有索引EXPLAIN显示没走或者走了却选了另一个索引。这跟优化器依赖的索引统计信息有关。InnoDB通过采样估算索引的区分度cardinality如果统计信息过期优化器就可能做出错误的判断。解决办法很简单ANALYZE TABLE 表名可以重新统计索引信息。另一种情况是优化器比较了走索引和全表扫描的成本后真的觉得全表扫描更划算——比如这个列区分度太低。这时候骂优化器笨是没有用的要做的是提升索引本身的质量或调整查询写法。7. 索引设计实战从选字段到排顺序一张表该怎么规划索引7.1 索引字段选择的三个原则第一区分度优先。区分度高的字段才能快速把范围缩小到极小比如用户名、手机号、订单号。区分度极低的字段如性别、状态码单独建索引通常没什么用而是应该作为复合索引的靠前等值列。第二高频查询优先。不是所有字段都要建索引而是那些在WHERE、JOIN、ORDER BY、GROUP BY中反复出现的字段。如果一个字段只出现在SELECT列表里它就不应该进索引——除非你想做覆盖索引。第三选择性计算。可以用SELECT COUNT(DISTINCT col)/COUNT(*) FROM table估算选择性值越接近1越好。对TEXT、VARCHAR长字段先算不同前缀长度的区分度确定合适的前缀长度再建前缀索引。7.2 复合索引的列顺序等值在前范围在后这是个反复强调的原则核心依据是B树的有序性等值条件下复合索引可以继续利用下一列的有序性一旦遇到范围条件这一列之后的所有索引列都无法用于定位和排序。举例说明业务上最常见的查询是WHERE user_id? AND status? AND create_time BETWEEN ? AND ?。复合索引应该建成(user_id, status, create_time)。前两列等值能精确定位分区第三列范围用range扫描效率非常高。7.3 索引不是越多越好每个索引都有写入成本很多刚接触索引的同学喜欢给每个出现在WHERE里的字段都建一个单列索引这是错误的。原因有三写入时每个索引都要同步更新插入、更新、删除性能下降索引占用磁盘空间优化器面对过多可选索引时选错索引的概率也增加。一个表保留四五个索引通常足够。设计时要问自己这些索引的查询覆盖是否重叠比如已经建了(a,b)复合索引还有必要单独建a单列索引吗通常不需要因为复合索引的最左前缀可以覆盖a单列查询的场景。举个例子我接手过一个表二十多个索引很多字段被建了两三遍单列索引。整理后保留五六个写入性能提升了30%多查询没有受到影响。删索引前务必确认没有漏掉任何一条依赖它的业务SQL。7.4 主键的设计自增整数永远是最稳的选择关于主键业内讨论误解很多但结论相对统一InnoDB表首选自增整数主键。原因已经在B树的部分讲过——插入顺序性和主键有序性天然一致避免页分裂和页移动。业务主键如订单号、身份证可以被设计为逻辑主键用UNIQUE约束保证唯一性同时用自增id作为InnoDB的聚簇索引键。实践中这种逻辑主键物理自增主键的组合能有效兼顾业务语义和存储引擎物理组织。7.5 关于排序的索引设计ORDER BY能不能走索引取决于排序字段是否和索引列顺序一致以及排序方向是否一致。比如索引是(a, b)ORDER BY a, b就能走索引ORDER BY b就会filesort因为b在树上的顺序被a主导。ORDER BY a DESC, b DESC在MySQL 8.0中也能倒序利用索引因为8.0支持降序索引。如果一条SQL既要WHERE过滤又要ORDER BY排序优先把WHERE的等值列放索引前面ORDER BY列放索引后面这样过滤排序都用上了索引是性能上最理想的组合。8. 索引优化的几个深入方向不走全表扫描之后还有哪些提升空间8.1 索引条件下推、MRR和自适应Hash的配合聊到MySQL 5.6以后的优化器除了前面提到的索引下推ICP还有个**MRRMulti-Range Read**优化值得知道。简单说MRR会把多条需要回表的记录的主键先收集起来排序再统一去聚簇索引里回表把多次随机IO变成更接近顺序IO的形式大幅减少磁盘寻道开销。另外InnoDB还有一个自适应哈希索引Adaptive Hash Index它会在内存中为频繁访问的索引页建立Hash映射把B树上的等值查找从多次树遍历变成一次Hash定位。我们无需手动控制也不需要为它建任何东西理解即可。8.2 索引统计信息与ANALYZE的时机前面提过ANALYZE TABLE可以更新索引统计信息。那么什么时候需要主动执行经验和直觉告诉我大批量数据导入、大量UPDATE删除后、以及明显感觉执行计划反常时。正常情况下InnoDB会在打开表时自动采样统计但表数据发生剧烈变化时自动采样的频率往往跟不上手动ANALYZE是低成本高收益的保养动作。8.3 SQL改写索引到位之后查询写法也要跟进索引建好了不等于所有SQL都会变快。MySQL优化器虽然越来越智能但还没到能自动重写一切SQL的程度。几个常见的改写思路WHERE条件里有OR且无法走索引时改写为UNION ALL避免在索引列上做数学运算、函数处理分页深翻页场景把LIMIT 2000000, 20改写为基于上次查询最大ID的WHERE id上次最大ID LIMIT 20因为后者可以一直沿着索引走JOIN时被驱动表的关联列上要有索引否则驱动表每一行都触发被驱动表全表扫描后果是灾难级的。8.4 监控索引的实际使用情况MySQL的Performance Schema和sys.schema_unused_indexes可以帮助找到从未被使用过的索引。这类索引不仅浪费空间还拖慢写入。定期跑一下SELECT * FROM sys.schema_unused_indexes把长期无查询命中的索引清理掉是DBA阶段的日常活儿。还有个更直接的方法用SHOW INDEX FROM 表名查看索引基数cardinality和区分度。如果基数明显异常低这个索引基本是无效的。9. 最后再分享几条保命的索引经验按老规矩最后说几点纯实操层面的体会算是给前面理论的落地补充。第一每建一个索引都要有对应的SQL场景。用假设某天有人问你这个索引是为哪条SQL建的来自检。答不上来说明这个索引大概率该删。第二上线前用慢日志和EXPLAIN做一次全面体检。在测试环境造出足够大且分布贴近生产的数据模拟真实查询跑一遍重点看rows、type、Extra三列。很多线上事故都是因为测试环境数据量太小里头的索引问题完全暴露不出来。第三关于索引失效的说法别全信网上的旧知识。MySQL版本更新迭代很快早期版本的优化器行为在不同版本之间变化很大。一切以当前版本下EXPLAIN的实际输出为准自己动手验证一次比背一百条结论都有用。第四把索引和业务查询模式绑定在一起设计。不要等SQL慢了才回头补索引而是在开始设计一张新表时先列出这张表最核心的3到5条查询语句然后针对这些查询设计复合索引。事后补索引不仅痛苦还可能因为数据量太大导致加索引锁表。第五如果一条SQL怎么调都慢问题可能不在索引而在SQL设计本身。比如必须要对三张大表做复杂JOIN和聚合。这时候要跳出来思考能不能拆SQL、提前汇总、做宽表或使用其他存储方案而不是继续在索引世界里打转。索引能解决很多问题但解决不了所有问题。希望这篇对索引的系统性梳理能帮你在面对慢SQL时少一些焦躁多一些笃定。数据结构、执行计划、索引设计这三件事串起来就是MySQL索引从理论到落地的完整脉络。