MySQL索引原理详解：从B+树到执行计划与索引优化实战

MySQL索引原理图文详解这个标题我盯着看了一会儿。有人拿它当面试八股有人把它当成慢查询优化的救命稻草但真正能把索引讲明白的人不多。索引这个词在MySQL里实在太常被提起建表时要加索引SQL优化时要看索引甚至Navicat里点几下也能加索引可一旦问到“它为什么快”“它什么时候会失效”很多同学就开始含糊了。这篇文章我不想从概念定义开始而是想站在一个普通后端开发、运维甚至面试者的角度把MySQL索引原理从底层数据结构到执行计划再到底层设计细节完整串一遍。无论你是刚入行的小白还是写了几年SQL的老手看完之后都应该能回答“一个没有索引的查询走到哪一步”“B树到底存了什么”“为什么明明建了索引还是不生效”这几个问题。我自己做性能调优这些年多少慢查询都是栽在索引失效的细节上下面的内容基本是一个一个坑踩出来的希望对你有用。1. 索引为什么快从一次查询说起1.1 没有索引时MySQL在做什么先来模拟一个最普通的场景有一张用户表里面有一百万行数据我只想查name 张三的那条记录。在没有索引的情况下InnoDB存储引擎会怎么做别想得太复杂它就是老老实实把这张表的数据从头到尾读一遍一行一行比对name字段。这就是我们常说的全表扫描。你可以把它理解成翻一本没有目录的新华字典我想找“猫”字没有任何捷径只能从第一页开始一页一页翻到最后一页。运气好“猫”在第一页运气不好“猫”在最后一页那就得整个人工遍历完一整本字典。全表扫描的时间复杂度是 O(n)数据量越大查询越慢。这就是为什么当你SELECT * FROM user WHERE name张三的时候如果表里只有几百行你可能完全感觉不到慢但如果表里有一千万行这个查询可能就会跑到几百毫秒甚至几秒。很多线上故障就是这么来的。更麻烦的是你翻完一遍之后如果下次再查“李四”还得再从头翻一遍没有任何缓存可以利用。1.2 索引让查询路径发生了哪些变化加索引之后情况完全不同。索引会在存储引擎之上单独建立一套数据结构这套结构把字段值和对应行的位置做了映射MySQL就可以先在这套结构里做精确定位或范围搜索找到目标记录之后再回头去拿整行数据。同样是查name 张三加了索引之后MySQL不再需要扫描所有行它会直接走进索引树里进行快速查找。这个查找过程的时间复杂度可以近似看作 O(log n)对于一百万行数据来说大概只需要几十次磁盘IO甚至更少。你想想一万次IO和几十次IO的差别那就是天壤之别。这里你可能会问既然索引这么好为什么不给每一列都建索引因为索引本质上是拿空间换时间。每个索引都是一棵独立的B树它需要占用额外的磁盘空间而且每次插入、修改、删除数据索引结构也要同步更新。如果你的表写入频繁而查询很少那你建一堆索引只会拖慢写入速度。这就是索引设计的第一个原则只给必要的查询建索引。2. 索引的底层数据结构2.1 为什么选B树而不是哈希表MySQL的索引默认使用B树但很多人没想过一个更基础的问题为什么不用哈希表哈希表确实在等值查询场景下更快理论时间复杂度是 O(1)也就是一次哈希计算就能定位到数据。但现实中的查询往往没有那么简单我们经常要查范围比如WHERE age BETWEEN 20 AND 30或者做排序ORDER BY age。哈希表的底层是散列表数据是无序的遇到范围查询就只能一个个遍历效率极低。B树则不一样叶子节点上的数据天然有序而且叶子节点之间通过链表串联起来查一个范围就相当于在一个有序链表上滑动代价小得多。那为什么不用普通的二叉树或者二叉搜索树呢因为树高的问题。二叉搜索树的查找次数取决于树的深度对一百万行数据来说理想情况下深度大约是20层这看起来还能接受。但如果MySQL要去读每一层的节点每一层都可能是一次磁盘IO20次磁盘IO已经非常慢了。而且二叉树每个节点只存一个键值节点的利用率太低数据量一大树就会变得很高。B树则不同它的每个节点可以存储很多个键值和指针一次IO可以把大量索引项加载进来树的高度通常只有三层左右几百万、几千万的数据量都能压在三层树里。这里有一张简化的示意图你可以感受一下B树的结构[ 58 | 100 ] / | \ [ 1, 20, 34 ] [ 60, 77, 90 ] [ 110, 125, 150 ] ... ... ... ...根节点只存少量键值用于路由下面的内部节点继续做指向数据全部集中在叶子节点并且叶子节点之间用链表连起来。你要的效果就是三层以内的访问一次定位。2.2 B树是如何组织数据的InnoDB中的数据是按页存储的每一页默认大小是16KB。B树的每一个节点在物理上就对应一个页。根节点、内部节点和叶子节点的大小虽然都是16KB但里面存的内容完全不一样。内部节点和根节点只存键值和指向子节点的指针它们不存实际的数据行。这样设计是有讲究的一个16KB的页如果全部用来存键值和指针大约可以存一千个以上的索引项那么一个三层的B树就能够管理十亿级别的数据量。如果内部节点也存整行数据树高会急剧增加IO次数直接翻倍索引的性能优势就没了。叶子节点是真正存数据的地方。对于主键索引来说叶子节点存放的就是完整的用户数据行。我在建表的时候通常指定一个主键idInnoDB就会自动为主键建立聚簇索引这个聚簇索引的叶子节点保存了整行的所有字段。如果表没有主键InnoDB会自己找一列非空的唯一字段作为主键再找不到就生成一个隐藏的主键。B树的叶子节点还有一个特别重要的特性它们是按索引键值排序的而且互相之间通过双向链表连接。所以你执行一条WHERE age BETWEEN 20 AND 30的语句MySQL一旦定位到最小的20岁用户就可以顺着叶子节点的链表一直往后扫直到超过30岁为止。这种设计让范围查询和排序查询都变得非常流畅。2.3 主键索引与二级索引的差异很多新手会把“索引”当成一个抽象概念但实际使用中你至少要分清聚簇索引和二级索引。聚簇索引就是主键索引它的叶子节点直接包含整行数据。每条记录只会属于一个聚簇索引所以你在建表时设置主键InnoDB就会根据主键来组织整张表的物理存储顺序。二级索引也叫辅助索引或普通索引是你在其他列上建立的索引。二级索引的叶子节点并不包含整行数据它只包含两样东西当前索引的键值以及主键值。比如你给name建了一个普通索引叶子节点里存的是name和id。当你用name去查数据时MySQL会先在二级索引里找到对应的name和id然后再拿着这个id到主键索引里找完整的行。这个过程就是常说的“回表”。为什么二级索引不直接保存数据行的物理地址因为B树在插入、删除、分裂时数据行的物理位置会频繁变化。如果二级索引里保存的是物理地址每移动一次数据就要更新一次二级索引维护成本太高。而保存主键值就稳定得多无论数据行挪到哪里主键值都不会变。理解了这一点你也就理解了为什么二级索引查询往往需要“两次搜索”先在二级索引中找到主键再回聚簇索引拿数据。3. 从执行计划看索引的真实行为3.1 用EXPLAIN读懂一次索引命中的过程说再多理论都不如直接在SQL语句前面加一个EXPLAIN看得清楚。这是我在排查SQL性能时第一个使用的工具。我先建一个简单的用户表CREATE TABLE user ( id bigint NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, age int NOT NULL, city varchar(50) NOT NULL, PRIMARY KEY (id), KEY idx_name_age (name, age) ) ENGINEInnoDB;然后执行EXPLAIN SELECT id, name, age FROM user WHERE name 张三 AND age 18;执行计划中几个重要字段你应该重点关注。第一是type它表示MySQL在表里找到目标行的访问方式。常见值的排序大概是这样systemconsteq_refrefrangeindexALL。如果你看到ALL那就是全表扫描通常意味着索引没有生效看到ref或range说明走了索引但还有优化空间看到const或eq_ref说明查询条件非常精准。第二是key它显示MySQL实际选择使用的索引名称。如果这个字段是NULL那说明优化器没有选到任何索引这里就要警惕了。第三是rows它是个估算值表示MySQL认为需要扫描多少行才能找到结果。如果rows非常大就算key不为空也要想一下是不是查询条件太宽了。还有一条比较实用的经验不要只在开发环境用EXPLAIN生产环境的慢查询日志里找出来的SQL全部都要拿EXPLAIN过一遍。很多时候开发环境数据量太小索引失效也看不出来生产环境一千万行数据立刻现出原形。3.2 回表、覆盖索引与索引下推理解了EXPLAIN就要开始关注回表和覆盖索引了。直接改一下上面的查询EXPLAIN SELECT * FROM user WHERE name 张三 AND age 18;这个查询和上一个查询的唯一区别是把查询字段从id, name, age改成了*。执行计划可能会有变化重点是看Extra列。如果Extra列出现Using index说明这个查询完全在二级索引中就完成了不需要回表这是最理想的情况。如果Extra列没有这个提示说明MySQL在二级索引中找到目标记录后还要拿着主键回到聚簇索引里去读取其他字段这就叫回表。回表不一定是坏事但如果每条命中记录都要回表而命中结果集又很大那性能就会直线下降。比如你查name匹配了十万行那就要回表十万次每次回表都是一次随机IO。覆盖索引的意思是说查询需要的所有字段都在同一个二级索引叶子节点里MySQL根本不需要回表。举个例子你的二级索引是idx_name_age(name, age)你只查name,age,id那么二级索引本身就包含这三个字段直接返回即可。所以在写项目代码时我会尽量不写毫无节制的SELECT *而是明确写出业务需要的字段这样才有机会做到覆盖索引。还有一个容易被忽略的特性叫索引下推英文缩写ICP。MySQL 5.6版本之后开始支持。当你有联合索引(name, age)查询条件里name用到了索引age过滤条件就会被“下推”到存储引擎层在读取二级索引时就过滤掉不符合age的叶子节点减少回表次数。你会在Extra列看到Using index condition这其实是好事。它说明MySQL已经尽量在索引层就完成工作了剩下的回表次数已经压缩到最小。4. 索引失效我踩过的那些坑4.1 最左前缀法则到底是怎么失效的联合索引是MySQL索引优化里的重头戏也是最容易踩坑的地方。我有一个联合索引(name, age, city)如果你完全按照这个顺序写条件WHERE name 张三 AND age 18 AND city 北京那这个联合索引可以完整命中的所有三层效果最好。但如果你写成WHERE name 张三 AND city 北京中间跳过了age那索引就只用到name这一层city虽然也在联合索引里但MySQL已经没法继续用它过滤了。这就是所谓的最左前缀法则。很多人的误区是以为“只要查询条件里有索引的第一列整个索引就一定能被用上”。这句话要打一个折扣第一列name确实能触发索引但不代表后面的age和city都能被用上。只有在查询条件中从联合索引最左侧开始连续命中索引才能发挥最大效果。还有几种情况会让最左前缀直接失效。比如LIKE查询时只有LIKE 张%这种前缀匹配能走索引LIKE %张因为通配符在前面索引无从途用只能全表扫描。又比如你对索引列做了运算或函数操作最左前缀也可能失效。这类问题在改造老项目的时候特别常见线上SQL已经写死了不能轻易改我通常建议优先调整索引顺序其次才考虑改写SQL。4.2 隐式转换、函数操作与排序场景下面这几个问题是参加“MySQL面试题”时经常翻车的点也是我实际排查线上故障时见过最多的类型。第一个是隐式类型转换。表里有一个字段mobile varchar(20)它本身建了索引但查询时如果写成SELECT * FROM user WHERE mobile 13800138000注意mobile是varchar条件却是整数型MySQL会尝试把字段列转换为数值类型这一转索引就废了。同类的问题还有日期字段传年月日字符串、字符编码不一致导致连接时转换等。判断起来也简单执行EXPLAIN看key是否是NULL如果是再去检查字段类型和条件类型是否一致。第二个是函数操作。索引列一旦套上了函数优化器就没法直接使用B树上的原始值了。比如SELECT * FROM user WHERE DATE(create_time) 2024-01-01虽然create_time上有索引但DATE()函数调用之后MySQL无法按原始的create_time值去索引树中搜索只能先取出所有create_time再做计算最后过滤。正确处理一般是改成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。这样索引就能被正常使用。第三个是排序场景。当你在ORDER BY里用了索引字段MySQL可以直接从B树叶子节点的链表顺序读取这叫做内部有序扫描。但如果你排序的字段不符合索引最左顺序或者排序方向不一致MySQL就只好把所有结果集找出来再进行一次额外的排序这时EXPLAIN的Extra列会出现Using filesort。Using filesort不是说出现了磁盘临时文件而是指MySQL没有利用索引天然的有序性必须自己重新排序它在数据量大的时候代价非常高。我印象比较深的一次排查是线上有个订单列表接口SQL里同时有WHERE条件和ORDER BY条件单独看每一个字段都有索引但优化器就是选了全表扫描。最后发现是因为排序字段和WHERE条件没法共用同一个索引MySQL需要另起炉灶做filesort代价比全表扫描还高。解决方式很直接把WHERE和ORDER BY涉及的字段重新设计成一个联合索引让这个索引同时兼顾过滤和排序查询瞬间就快了十倍。5. 索引设计实战这样建索引才合理5.1 区分度、基数与选择性很多人只关心“怎么加索引”却忽略了“这一列到底适不适合加索引”。判断标准里面最重要的一个指标是区分度。区分度也叫选择性计算公式是字段的去重值数量除以总行数。举个例子一个性别字段只有“男”和“女”两个值那一百万条数据里区分度只有两万分之一非常低。如果你在这样一个字段上建普通索引MySQL扫描索引后可能命中五十万行然后再回表五十万次这比直接全表扫描还傻。所以像性别、状态、是否删除这类枚举值极少的字段我一般不建议单独建索引。反过来像name、mobile、email这类字段值基本不重复区分度极高索引筛选效率就非常好。在业务里我会把高频查询条件里区分度高的字段放最前面这样B树每走一层就能快速排除大量数据。如果字段本身很长比如存文章摘要的text字段直接建普通索引会浪费大量空间而且索引页能容纳的索引项变少B树会变高。这种情况下可以考虑前缀索引比如KEY idx_content(content(20))只对字段前20个字符建立索引。前缀索引同样能加速等值查询和模糊前缀匹配但它有一个短板无法用于排序也无法做到覆盖索引。同时前缀选择过短还可能让区分度不足需要实测确认长度。5.2 联合索引设计要点与字段顺序聊完了单列再聊多列。联合索引的字段顺序是整个索引设计的核心。我在实际项目里通常遵循两条经验。第一条等值条件的字段排在前面范围条件的字段排在后面。比如业务查询经常是WHERE age 18 AND create_time BETWEEN 2024-01-01 AND 2024-01-31那联合索引建议建(age, create_time)。因为等值条件可以精确定位到B树的某一小段范围条件在等值条件确定后顺着叶子链表接着扫就行。如果反过来建(create_time, age)MySQL虽然也能用索引但在第一步的范围扫描时可能会扫一大片再对age做过滤效率会差很多。第二条把区分度高的字段放在前面。如果联合索引是(name, status)而status的区分度非常低那索引失效的概率会更大。我们把name放前面B树第一层就能按高区分度迅速收敛然后status在小区间里做过滤整体效率最高。另外建索引别忘了考虑覆盖索引的业务收益。如果一个查询高频出现而且需要的字段不多我会有意识把这些字段组合成一个联合索引让查询在二级索引内直接完成避免回表。但这个度要把握好不是每一条SQL都值得给它定制一个索引。索引数量增加写入、更新成本都会上升磁盘空间占用也会增加。我的底线是单个表上的索引数尽量控制在五个以内除非业务有非常强烈的查询需求。创建索引的常用语法很简单ALTER TABLE user ADD INDEX idx_age_name (age, name); CREATE INDEX idx_age_name ON user (age, name); DROP INDEX idx_age_name ON user;还有一种少加索引的写法是用ALTER TABLE给主键加约束这个我就不展开了。我想强调的是加索引之前先在测试环境导入接近生产的数据量用EXPLAIN验证一遍再决定是否上线。不要上了生产才发现索引没用白折腾一次变更。6. 常见问题速查与排查技巧6.1 索引相关面试题速答这里把一些常见的MySQL索引面试题整理成一张速查表面试前翻一翻很有用。问题一句话答案为什么用B树不用哈希表B树支持范围查询和排序哈希表只适合等值匹配为什么用B树不用B树B树非叶子节点不存数据页能容纳更多索引项树高更低叶子节点有序链表范围查询更方便聚簇索引和二级索引的区别聚簇索引叶子存整行二级索引叶子只存索引键和主键什么是回表二级索引查到主键后再到聚簇索引取整行数据的过程覆盖索引是什么查询字段全部在二级索引叶子节点中无需回表联合索引最左前缀是什么查询条件必须从联合索引最左侧连续命中否则后面字段可能失效索引失效的常见场景隐式类型转换、字段函数运算、LIKE前置通配符、范围条件后面的字段这表虽然简单但你把这些内容用口语讲给面试官听比你背出一整段教科书定义要强得多。面试官更在意的是你能不能把逻辑串起来。6.2 排查索引问题的几条经验最后分享几条排障经验。很多人一遇到SQL慢就急着加索引其实正确的排查顺序应该是先开慢查询日志把慢SQL捞出来然后逐条用EXPLAIN分析再决定改索引还是改SQL。我常用的工具有mysqldumpslow和performance_schema中的表可以把高频慢查询按执行次数排序优先处理影响最大的那几条。拿到SQL之后第一步看EXPLAIN的type字段如果是ALL先别急着加索引看看是不是字段类型不匹配、函数运算导致失效。如果确认是查询条件本身没有可用的索引这时候才去建索引。有一次我排查一个线上问题EXPLAIN显示type refkey也命中了Rows估算只有几百行但接口还是慢。后来发现问题是每一条命中记录都要回表而查询字段里有十几个大字段每次回表都要读取完整数据页。解决方案不是加索引而是把SQL里的SELECT *改成只查必要的字段并且把查询字段调整成覆盖索引的一部分性能立刻恢复正常。还有一个很容易踩的坑统计信息不准确。MySQL优化器选择索引是基于采样统计的如果表数据频繁变更统计信息没及时更新优化器可能选错索引。这时候可以执行ANALYZE TABLE user;让MySQL重新统计。也有时候优化器确实选了索引但代价估算反而比全表扫描高那就要考虑改写SQL比如强制索引FORCE INDEX(idx_name_age)但这种方式我一般只在紧急修复时使用长期方案还是要理清表结构和查询逻辑。我个人在实际操作中的体会是MySQL索引原理不是靠背出来的而是靠一次次执行计划堆出来的。你不要把B树想成一个很玄的东西它就是一本带目录和页脚的书MySQL先翻目录找到页码再翻到正文那一页读内容。你真正要盯住的只有三个点走没走索引、走了多少行、有没有额外的排序和回表。这三点只要每一次查SQL都带着问题去看时间长了你会发现自己对慢查询的判断力提升得非常快。最后再分享一个小技巧每次建完索引都用老SQL、新SQL两条语句对比跑一遍记录执行时间上线后也持续观察一周慢查询日志确认效果稳定才算真正完事。MySQL索引这件事慢工出细活急不来。