MySQL索引原理与优化：从B+树到EXPLAIN慢查询排查指南

索引这东西网上讲基础的文章一抓一大把但大多数要么停留在“索引就是目录”这种比喻上要么直接甩一堆建索引语法让你背。今天这篇我想换个角度把索引拆开来看它到底解决了什么问题、为什么MySQL偏偏选了B树、InnoDB底层到底怎么组织数据以及你写SQL的时候哪些操作会让索引白建。全程会用实际场景和具体的SQL例子说话适合已经会写基本SQL、开始琢磨查询性能、或者准备系统过一遍MySQL知识点的同学。这算是我MySQL学习系列的第九篇前几篇讲了连接、事务、日志这些这篇集中火力打索引。1. 索引到底解决了什么问题1.1 全表扫描的代价为什么查询越写越慢先说一个场景。你有一张用户表里面存了几十万行数据某天你执行了这样一条查询SELECT * FROM user WHERE phone 13800138000;在没有索引的情况下MySQL会怎么做它会从表的第一行开始把每一行的phone字段都取出来跟条件比较一遍直到把整张表扫完把所有满足条件的行都捞出来。这个操作叫全表扫描复杂度是O(n)。几十万行数据机械硬盘时代可能要几十毫秒甚至几百毫秒SSD时代虽然快很多但如果你查询频繁且表持续增长性能会肉眼可见地恶化。更重要的是全表扫描会把大量无关的数据页加载到内存里Buffer Pool的热点数据被挤出去连带着其他查询也变慢。索引的核心价值就是把这个O(n)的过程变成O(log n)。它相当于给phone字段额外维护了一份有序的“目录”查询的时候不用从头翻直接按照目录定位到目标位置附近然后快速找到对应数据。1.2 用目录思维理解索引的命中逻辑很多人说“索引就是书的目录”这个类比方向是对的但容易产生误解。书目录的特点是目录单独占几页目录里记录的是章节名和页码你要找某章就翻目录然后翻到具体页。MySQL的二级索引做的事跟这个很像它把索引列的值排序存储每个值后面跟着一个指向实际数据行的指针准确说是主键值查询时先查这个“小目录”拿到指针后再去数据表里取整行。但有个关键区别书的目录是按章节名组织的你如果只知道页码是第100页想找“这一页属于哪一章”目录就帮不上忙了。MySQL索引也一样你只有在查询条件里用到了索引列本身索引才能发挥作用。如果你对非索引列做条件筛选目录就废了只能老老实实全表扫。还有一个容易忽略的点索引不仅能加速查询还能加速排序和分组。因为索引本身是有序结构如果ORDER BY的字段恰好是索引列MySQL可以直接按索引顺序扫描输出省掉filesort这个额外步骤。这个后面细说。2. B树为什么成为MySQL索引的默认选择2.1 从二叉搜索树到平衡树再到B树的演进逻辑先抛个问题为什么不用二叉搜索树二叉搜索树在极端情况下会退化成链表比如插入的数据一直是递增的树的形状就变成一根斜线查找复杂度回到O(n)。所以实际应用中会用平衡二叉搜索树比如AVL树、红黑树保证左右子树高度差在一定范围内。但平衡二叉树也有问题它是二叉树每个节点最多两个子节点树的高度会随着数据量增长得很快。假设树高是20意味着一次查询要访问20个节点。对内存数据结构来说这没问题但数据库的数据是放在磁盘上的每次访问一个节点就是一次磁盘I/O20次I/O就很夸张了。于是B树登场了。B树是多路搜索树一个节点可以存多个关键字能大幅度降低树的高度。比如说一个节点能存几百个关键字几百万行数据的索引树高度可能只有3到4层查询最多做几次磁盘I/O就能定位到目标。B树是B树的变体区别主要有两点第一B树的非叶子节点只存索引键值不存数据本身所有数据都存放在叶子节点第二叶子节点之间通过链表相连形成了一个有序的链表结构。这两点特征让B树在数据库场景下比B树更加合适。2.2 InnoDB页结构与磁盘I/O的约束InnoDB存储引擎操作磁盘的最小单位是页默认大小是16KB。也就是说哪怕你只想读一行数据InnoDB也会把包含这一行的整个页从磁盘读到内存。反过来索引树的每个节点在物理上就是一个页。这里就能算出B树的大致容量了。假设一行数据是1KB一个16KB的页能存16行数据。如果非叶子节点里每个索引键加指针占16字节那么一个非叶子节点能存的索引项大约是1000个。三层B树大概能存多少数据第一层根有1000个指针第二层有1000×1000个叶子节点指针第三层是叶子节点每个叶子节点能存16行算下来是1000×1000×16也就是1600万行。这个推算告诉我们一件很重要的事绝大多数业务表的索引树高度只有2到3层。2层树只需要一次磁盘I/O去读叶子节点根节点通常常驻内存3层树需要两次I/O。这就是为什么索引查询快——索引树的高度决定了磁盘I/O次数而B树把高度压得非常低。2.3 为什么不能直接用哈希索引做主力你可能也听说过哈希索引。哈希索引查找单个等值条件非常快理想情况下是O(1)比B树的O(log n)还快。那MySQL为什么不用哈希索引做默认选择问题出在哈希索引不支持范围查询。哈希表把键值打散后原来的有序性全丢了你没法用哈希索引做WHERE age 18 AND age 30这种条件也没法直接用来排序。而范围查询、排序、前缀匹配这些恰恰是业务里最常见的查询形态。InnoDB其实是有自适应哈希索引的它会在某些热点查询场景下自动为B树加一层哈希索引来加速等值查询但对使用者透明也不需要你去创建。我们平时说创建索引指的默认就是B树索引。3. 索引的分类与InnoDB中的真实形态3.1 主键索引、唯一索引、普通索引、全文索引的区分按逻辑功能划分MySQL索引主要分四类。主键索引每张表只能有一个不允许为空。InnoDB里主键索引就是聚簇索引后面会重点讲。唯一索引允许为NULL可以有多个NULL但非NULL值不能重复。普通索引没有任何唯一性约束纯粹为了加速查询。全文索引针对文本内容做分词匹配适用于MATCH ... AGAINST ...语法底层结构不是B树而是倒排索引。实际建表时经常遇到的场景是业务上有一个唯一业务编号比如订单号它应该建唯一索引不是普通索引。两者对优化器的意义不一样唯一索引在等值匹配时一旦找到一条记录就可以直接返回普通索引还需要继续扫描下一条看是否还有重复值。这个差异在大数据量下会积少成多。全文索引这里多说一句MySQL的全文索引在中文分词上体验一般生产环境真要搞搜索通常会用专门的开源搜索引擎。所以除非你的场景很简单否则别指望MySQL全文索引解决复杂搜索需求。3.2 聚簇索引与非聚簇索引回表到底是怎么回事InnoDB的聚簇索引是指表本身按照主键构建的B树叶子节点存储的是整行的数据。换句话说这张表的数据就是主键索引本身数据物理上按主键顺序排列。除主键外的索引统称二级索引也叫非聚簇索引。二级索引的叶子节点不存整行数据存的是索引列的值加主键值。查的时候会有一个两步操作先到二级索引树里找到对应的主键值再拿着这个主键值去聚簇索引树里找完整的行。这个第二步就是回表。举个例子CREATE TABLE student ( id INT PRIMARY KEY, student_no VARCHAR(20), name VARCHAR(50), age INT, INDEX idx_student_no (student_no) );如果执行SELECT * FROM student WHERE student_no S123过程是先查idx_student_no这棵二级索引树找到student_no等于S123的记录的叶子节点拿到id再去聚簇索引树里查到id对应的完整行返回。这里就发生了一次回表。如果改成SELECT student_no FROM student WHERE student_no S123情况就不一样了。查询需要的字段student_no在二级索引的叶子节点里就已经存在不需要回表这种情况叫作覆盖索引后面细聊。3.3 覆盖索引减少回表的实践经验覆盖索引并不是一种独立的索引类型而是“索引包含了查询需要的所有字段”的状态。当二级索引树里能查到全部所需数据优化器就不再做回表操作直接返回。这个特性的实战价值非常大。很多慢查询的根因就是每次都要回表尤其是在并发高的场景下回表意味着额外的随机I/O。举个例子常见的一个慢查询SELECT id, name, age FROM student WHERE name 张三;如果只在name字段上建了普通索引查询流程是二级索引查name拿到主键id回表聚簇索引拿name和age返回。如果改成建联合索引(name, age)查询需要的id、name、age在二级索引的叶子节点里全都有就不需要回表了。你可能会问age字段不是会加在二级索引叶子节点吗对联合索引的叶子节点会包含所有索引列加主键。所以(name, age)这个联合索引能覆盖id、name、age三个字段因为id是主键天然就在二级索引里。这里要区分一个概念覆盖索引说的是查询字段被索引覆盖不是索引本身有什么特殊结构。4. 索引设计的取舍与优化4.1 选择索引列的考量维度索引建在哪些列上这是设计阶段就要想清楚的问题。围绕判断标准我习惯按优先级看三件事。第一过滤性。索引列的区分度越高索引效果越好。说白了就是这一列有多少种不同的值比如性别这一列只有两个值区分度就极低建了索引也很难把扫描范围缩小。与之相对身份证号、手机号这种列区分度非常高适合做索引。第二查询频率。索引不为“存在”而存在而是为查询服务。一个字段哪怕区分度很高但如果业务上根本没有按它筛选的场景建了也是白白增加写开销。第三更新频率。如果一个字段经常被修改如表里的状态字段频繁流转那每一次UPDATE都可能要同步更新索引树索引维护成本会显著上升。频繁更新的列要谨慎加索引。另外还有一个很实用的参考值区分度阈值。实践中我会算一下COUNT(DISTINCT column) / COUNT(column)这个比值低于20%的列一般不值得单独建索引。比如一张表有10万记录某个字段只有1万种不同值比率是10%即使按它查询索引能排除的数据量也有限尤其是当它和其他条件组合的时候。4.2 联合索引的列顺序最左前缀原则联合索引的列顺序设计是索引优化里最容易被忽视、也最重要的一环。MySQL联合索引遵循最左前缀原则一个联合索引(a, b, c)查询条件里只有使用了a、或a和b、或a和b和c时索引才可能被用到。跳过了前面的列直接查b或c索引就无法发挥作用。这背后其实是B树的存储方式决定的。联合索引里的记录先按照第一列排序第一列相同再按第二列排序以此类推。所以列顺序的安排原则可以归纳为两点把等值查询条件放在前面。等值条件下索引树能精确定位到一个范围发挥效率最高。把范围查询的列放在后面。因为范围条件下后面的索引列已经无法保持有序了放在后面可以让前面的等值列继续发挥作用。举个例子业务上有两个高频查询SELECT * FROM order WHERE user_id 123 AND status 1; SELECT * FROM order WHERE user_id 123 AND create_time BETWEEN 2024-01-01 AND 2024-01-31;这时候联合索引设计成(user_id, status, create_time)和(user_id, create_time)哪种更好要具体情况具体分析。但有一个基本原则如果两个查询公共的等值列是user_id那user_id一定要放在联合索引的第一位。在这个基础上再考虑中间夹一个status是否会影响create_time的范围查询。这里有个常见的坑如果在联合索引中间列上加了范围条件比如(user_id, create_time, status)查询WHERE user_id 123 AND create_time 2024-01-01 AND status 1时create_time这个范围条件后面的status就用不到索引了因为B树在create_time范围区间内status是无序的。这个现象叫“范围之后全失效”严重时会让优化器直接放弃最优路径。4.3 索引数量与写性能的平衡索引不是越多越好这是一个新手必须建立的观念。每次INSERT、UPDATE、DELETE操作InnoDB不仅仅要维护聚簇索引还要同步维护表上所有的二级索引。索引越多写入时的维护量越大磁盘占用也越高。一个我常用的评估方式是单表索引控制在5到6个以内公用的查询模式用联合索引覆盖避免同一个字段在不同索引里反复出现。比如你有三个查询分别要按status、status create_time、status type筛选有些人会建三个索引(status)、(status, create_time)、(status, type)。其实没必要因为后两个已经覆盖了第一个查询的需求。只保留两个联合索引就够了。另外一个经常被问的问题到底要不要给外键建索引从数据完整性来说外键约束是要的但如果你用了外键MySQL会强制在对应列创建一个索引。所以很多DBA的实践是业务上自己管理关联关系不用外键约束避免MySQL隐式建索引导致冗余。这个属于团队规范问题没有绝对对错但你要知道那个隐式索引是存在的。5. 使用EXPLAIN验证索引命中情况5.1 EXPLAIN关键字段解读建完索引后怎么确认SQL真的用上了用EXPLAIN。EXPLAIN SELECT * FROM student WHERE student_no S123;返回结果里最需要关注这么几列type字段表示访问类型从好到差依次是system const eq_ref ref range index ALL。ALL就是全表扫描这是你绝对不想看到的。range代表用索引做了范围扫描ref是等值匹配的普通索引const是主键或唯一索引的等值匹配性能都非常好。看到ALL的时候基本可以断定这条SQL有问题。key字段显示的是最终选用的索引。有时候你建了索引但这里显示NULL说明这个查询没走索引。possible_keys会列出所有可能被用到的索引key等于其中一个或几个代表优化器从中选了一个。rows字段是优化器预估的需要扫描的行数。这个数字越大查询成本越高。Extra字段里如果出现Using filesort或者Using temporary说明查询涉及排序或分组没有走索引这个也需要重点排查。一个常见的场景是同一个SQL数据量小的时候EXPLAIN结果可能显示走全表扫描更快优化器就不选索引了。这不一定代表索引失效因为几百行数据的全表扫描确实比走索引快。这种时候别急着给SQL加FORCE INDEX先确认是不是数据量测试环境跟生产环境差异导致的。5.2 常见的索引失效场景汇总以下这些场景我全部在真实环境里踩过整理成表格方便对照。场景示例为什么失效对索引列使用函数WHERE YEAR(create_time) 2024函数改变了索引列值B树无法直接定位隐式类型转换WHERE phone 13800138000phone是varchar字符串列和数字比较时,MySQL对列做转换索引失效前导模糊查询WHERE name LIKE %张%左侧不确定无法从B树根节点定位联合索引缺左列索引(a,b)查询只用b不满足最左前缀原则范围列后面的条件索引(a,b)查询a1 AND b2 AND c3b是范围条件c无法继续使用索引OR连接的非索引列WHERE id1 OR name张name无索引优化器可能选择全表扫描这里面最容易被忽略的是隐式类型转换。比如表里phone字段是varchar类型但查询传入的是一个数字常量MySQL会把字段转换成数字再比较。一旦对索引列做了转换就和函数操作一样索引直接失效。我见过很多线上慢查询都是因为类型不匹配导致的排查半天才发现是少了一对引号。还有一个高阶但重要的场景IN和OR在很多情况下是可以走索引的优化器会转成range访问方式不必一看到OR就紧张。真正要注意的是OR混用了无索引列那可能直接拖垮整个查询。遇到这种SQL要么给关联列补索引要么拆成两个SQL再用UNION串起来。5.3 实际优化案例举一个我最近处理的案例。某业务模块有个查询表里有一千多万行数据用户反馈页面响应特别慢。SELECT order_id, amount, status FROM payment_order WHERE merchant_id 1234 AND pay_time 2024-06-01 AND pay_time 2024-07-01 ORDER BY amount DESC;最初的索引是(merchant_id, pay_time)。EXPLAIN显示type为rangerows约20万Extra里出现了Using filesort。问题在最后一行的ORDER BY amount DESC排序字段amount不在索引里MySQL只能先把筛选出来的20万行加载到临时内存排序这个排序就是慢的根源。我当时的优化思路分两步。第一步观察业务对amount排序的需求是不是必须的。确认肯定要做后我把联合索引改成了(merchant_id, pay_time, amount)。这样索引里已经按merchant_id和pay_time做了范围筛选筛选完成的结果集中amount字段依然是排好序的Using filesort直接消除。改完之后的EXPLAIN结果type还是range但Extra里不再有Using filesort查询耗时从原来的2秒多降到了300毫秒以内。这里有一个很微妙的点联合索引(merchant_id, pay_time, amount)中pay_time是范围条件amount排在范围条件后面为什么还能利用索引排序因为优化器在这里并不需要利用amount做筛选而是要利用它做排序。即便范围条件让amount无法用于过滤但索引本身留在扫描范围内的记录已经按amount排序了所以排序是可以直接使用的。这个细节非常值得记下来很多人理解“范围之后全失效”的时候过度泛化导致放弃了合法的排序优化空间。这个案例总结下来就一句话EXPLAIN看到Using filesort优先想怎么把排序字段加进索引里让索引替代排序。6. 我的经验与小技巧6.1 创建索引时容易忽略的事情几个容易踩的细节在这集中说一下。字符串索引要控制长度。如果索引列是VARCHAR(255)B树里每个索引项都要占用空间页能容纳的索引项变少树的高度就可能增加。对值域较长的字段比如URL、备注文本可以考虑用前缀索引比如INDEX idx_url (url(64))。但要注意前缀索引无法使用覆盖索引来避免回表因为叶子节点里存的是前缀而不是完整值。主键最好用自增或趋势递增的值。这是InnoDB聚簇索引的结构决定的。如果主键是随机字符串每次插入新行时B树为了维持有序性可能要频繁移动数据页产生页分裂性能会明显下降。用自增主键时新记录总是插在末尾顺序写效率最高。这也是为什么很多生产表会额外加一个自增id作为主键而不是直接用业务编号。不要在大字段上建索引。除了前面前缀索引的问题大字段意味着索引树更大缓存命中率更低。备份与迁移的排序影响。有些DBA会用ALTER TABLE ... ORDER BY ...试图改变表的物理顺序但需要注意的是在InnoDB里这张表的物理顺序实际由主键决定ALTER TABLE之后的排序效果会被后续插入覆盖掉。不要指望这个命令做索引优化。6.2 索引维护与监控索引不是建完就一劳永逸的。业务随着时间推移查询模式会变原来高效的索引可能慢慢失去价值而新出现的慢查询可能缺一个索引。我习惯每季度做一次索引巡检流程大致是三步。第一步开启慢查询日志找到出现频率最高的慢SQL。第二步对每条慢SQL执行EXPLAIN看type、rows、Extra这几个关键指标。第三步结合业务判断是加索引、调整联合索引列顺序、还是重写SQL。还有一个常见动作是清理无用索引。不知道某个索引还有没有在用可以借助performance_schema里的table_io_waits_summary_by_index_usage表来统计索引使用次数。长期为0的索引就要考虑删掉减少写维护成本。这里提醒一句删索引之前先在测试环境跑一轮压测确认查询性能没有回退。因为优化器的选择有时候并不完全可预测尤其是查询条件组合特别复杂的场景。最后分享一个小技巧。如果你经常要排查一条SQL为什么会走错索引可以试试EXPLAIN ANALYZEMySQL 8.0它能真实执行SQL并返回每一步的实际耗时和行数比单纯看预估的EXPLAIN更直观。我第一次用的时候还挺震惊的原来优化器对行数的预估在某些复杂场景下会偏差这么大靠猜不如直接实测。索引这个知识点理论是一层实践是另一层。把B树结构、聚簇索引、最左前缀、回表与覆盖索引这几个核心概念吃透再配合EXPLAIN反复验证自己的判断慢查询优化基本就拿下大半了。