1. 从一条慢SQL说起索引为什么能提速先讲个真实场景。之前接手过一个线上订单查询接口数据量大概800万行业务方反馈某个列表页越来越慢从最初的几十毫秒退化到2.7秒。当时第一反应是看执行计划结果发现走了全表扫描type列是ALL扫描行数接近全部数据。那个表压根就没建索引。后来给查询条件涉及的列补了联合索引接口耗时直接掉到40毫秒以内。差距是几十倍靠的就是索引。很多人对MySQL索引的理解停留在建了索引就快这个层面但实际工作中索引用得好不好直接决定了你是让数据库干活还是让数据库死扛。索引为什么能快核心原因是它改变了数据查找的方式——从从头翻到尾变成按目录找页码。而支撑这套机制的就是B树。这篇文章聚焦几个核心话题B树到底解决了什么问题、回表是怎么发生的、覆盖索引为什么能救命、最左匹配原则到底怎么理解以及索引失效的常见坑。适合刚接触索引的同学也适合写了几年SQL但没系统梳理过索引原理的开发者。看完全文你能做到三件事解释清楚索引的结构原理、写出能命中索引的SQL、一眼看出哪些SQL注定走不了索引。2. MySQL索引优化详解从B树到回表、覆盖索引与最左匹配原则2.1 索引的本质是什么索引是一种数据结构目的是加速数据检索。你可以把它理解成书籍的目录——没有目录的书你要找某个知识点就得一页页翻有了目录直接翻到对应页码就行。数据库里的数据存储在磁盘上磁盘IO的速度比内存慢几个数量级所以减少磁盘IO次数是数据库性能优化的核心目标之一。索引能在这一环节发挥作用因为它让数据库不需要扫描全表就能定位到目标数据。全表扫描意味着要把表里的每一行数据都读一遍数据量一大磁盘IO次数直线上升。有了索引数据库先查索引结构找到目标数据所在的位置再去读取对应的数据行。整个过程磁盘IO次数大幅减少查询自然就快了。MySQL默认的存储引擎是InnoDBInnoDB的索引结构就是B树。B树不是MySQL的发明而是一种广泛应用在数据库和文件系统中的数据结构。它和二叉树、红黑树这些结构的最大区别在于它是为磁盘场景设计的——能够大幅降低树的层高从而降低IO次数。后面会详细展开这个设计思想。2.2 索引不是越多越好很多新手的误区是既然索引能加速那就把所有可能用到的列都加上索引。这种做法是错的。索引是有成本的而且成本不止一种。第一是存储成本。每个索引都是一棵B树数据量一大索引占用的磁盘空间相当可观。第二是写入成本。每次INSERT、UPDATE、DELETE操作数据库不仅要更新数据本身还要同步维护表上的每一个索引。索引越多写入越慢。第三是优化器成本。查询时优化器需要评估走哪个索引最合适索引太多会增加优化器的判断负担极端情况下还可能导致优化器选错索引。所以索引设计的原则是按需创建为高频查询服务而不是为万一用得上服务。后面讨论索引失效的场景时你会发现某些索引建了实际用不上那基本就是纯浪费。3. B树结构拆解为什么偏偏是它3.1 B树到底长什么样B树是一种多路平衡搜索树。跟二叉树相比它的每个节点可以存储多个键值并且可以有多个子节点。简单画一下B树的结构叶子节点存储实际的数据记录InnoDB中叶子节点存的是整行数据或主键值非叶子节点只存储键值和指向子节点的指针不存数据所有叶子节点通过双向链表连接形成一个有序的链表结构所有数据都存放在叶子节点上且按顺序排列这跟B树有个关键区别B树的每个节点都能存数据而B树只有叶子节点存数据。这个区别带来的优势是B树的非叶子节点能容纳更多的键值树的高度更矮查询时的磁盘IO次数更少。还有一个容易被忽视的优势B树的叶子节点是有序链接的这让范围查询变得非常高效。查一个范围的数据只需要定位到范围的起点然后顺着链表往后遍历就行。而B树的叶子节点之间没有这种连接范围查询要多做一些回溯操作。3.2 为什么B树而不是红黑树或跳表这是索引原理面试中几乎必问的一个问题。先看红黑树。红黑树是二叉平衡树每个节点最多有两个子节点。在数据量大的情况下树的高度会很高。假设数据量是1000万红黑树的高度大约是log2(1000万)接近24层。每访问一层节点如果该节点不在内存里就要产生一次磁盘IO。24层意味着最坏情况下要24次磁盘IO这个代价是数据库承受不起的。而B树呢每个节点能存储多个键值通常一个节点大小对应一个磁盘页约16KB一个节点里能塞下几百个键值。同样是1000万条数据B树的高度通常只有3到4层。查询时最多3到4次磁盘IO就能定位到目标数据效率比红黑树高一个数量级。再看跳表。跳表在内存数据库中确实用得不错比如Redis的有序集合就是用跳表实现的。但跳表在磁盘场景下的表现远不如B树。跳表是链表结构节点之间通过指针相连在磁盘上存储时无法保证物理连续性随机访问的IO开销很大。B树的节点被设计成按磁盘页存储节点内部物理连续读取一个节点只需要一次磁盘IO这天然契合磁盘的读取机制。所以结论很清晰B树能在IO次数和查询效率之间达到最优平衡这是数据库选择B树作为索引结构的根本原因。3.3 InnoDB中的主键索引与辅助索引InnoDB的索引分成两类主键索引聚簇索引和辅助索引二级索引。主键索引的叶子节点存的是整行数据。InnoDB表本身就是一个按主键组织的B树表数据就存放在主键索引的叶子节点上。这也是聚簇索引名字的由来——数据行和索引聚在一起。所以InnoDB表必须有主键如果没有显式定义主键InnoDB会找一个非空唯一列作为主键找不到的话会隐式生成一个6字节的rowid作为主键。辅助索引的叶子节点存的是索引列的值加上主键值而不是整行数据。这里有个重要推论通过辅助索引查询时如果只需要索引列和主键列的数据那么直接从辅助索引的叶子节点就能拿到不需要回表。如果需要索引列之外的其他列数据那就要拿主键值去主键索引里再查一次这个过程叫回表。关于主键索引和唯一索引的区别可以这样理解主键索引是聚簇索引决定了数据行的物理存储顺序唯一索引只是普通的辅助索引只是加上了唯一约束。主键索引一张表只能有一个唯一索引可以建多个。主键不允许为NULL唯一索引允许NULL而且多个NULL值是被允许的MySQL中的UNIQUE约束不限制多个NULL。提示InnoDB推荐使用自增整数主键因为数据按主键顺序存储自增主键能让插入操作顺序进行减少页分裂的概率。用UUID这类随机值做主键数据插入时可能要频繁移动位置、分裂页面写入性能会明显下降。4. 回表原理与覆盖索引一次查询的真实旅程4.1 回表是怎么发生的用一个具体例子演示回表过程。假设有一张用户表userCREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, name varchar(50) DEFAULT NULL, age int DEFAULT NULL, city varchar(50) DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name) ) ENGINEInnoDB;执行下面的查询SELECT * FROM user WHERE name 张三;执行过程是先在辅助索引idx_name的B树里查找name 张三的记录。辅助索引的叶子节点存的是name值和对应的主键id值。找到id 123举例后拿着这个主键id去主键索引里再查一次。主键索引的B树里id 123对应的叶子节点存的就是整行数据。返回结果。第2步就是回表。每次回表相当于一次额外的B树查找数据量越大回表带来的IO开销越明显。如果查询条件是WHERE name 张三但我们只查询name和id这两个字段比如SELECT id, name FROM user WHERE name 张三;那就不需要回表了。因为辅助索引idx_name的叶子节点已经包含了name和id查询所需的所有数据都在这个索引里InnoDB不会再去查主键索引。这种索引本身就覆盖了查询所需的全部列的情况就叫覆盖索引。4.2 覆盖索引的正确使用姿势覆盖索引的精髓是尽量让查询的列和条件列落在同一个索引里。这样就不需要回表查询效率会大幅提升。什么情况下适合用覆盖索引常见场景有两种。第一高频查询固定返回某些列。比如列表页只展示id、title、status三列那就建一个包含这三个列的联合索引。第二统计类查询。比如COUNT某个条件下的记录数如果索引叶子节点里正好包含条件列不需要回表就能完成统计。在设计覆盖索引时有一个需要权衡的点索引列越多索引体积越大写入成本越高。所以不是索引里的字段越多越好而是要在覆盖常用查询和控制索引体积之间找平衡。经验是先看慢查询日志里哪些SQL高频出现再针对这些SQL设计索引。4.3 一个覆盖索引优化的真实对比之前处理过一个内容表表结构大概是这样CREATE TABLE article ( id bigint NOT NULL AUTO_INCREMENT, cat_id int DEFAULT NULL, title varchar(200) DEFAULT NULL, status tinyint DEFAULT NULL, create_time datetime DEFAULT NULL, PRIMARY KEY (id), KEY idx_cat_status (cat_id, status) ) ENGINEInnoDB;原业务SQL是查某个分类下的文章列表只取id和titleSELECT id, title FROM article WHERE cat_id 10 AND status 1 ORDER BY id DESC LIMIT 20;这条SQL需要回表取title字段。后来把索引改成cat_id, status, title变成了覆盖索引查询EXPLAIN的Extra字段从Using where变成了Using index查询耗时降了一倍多。这就是覆盖索引的实战价值。但要注意覆盖索引也不是万能的——如果你在查询里SELECT了索引之外的列覆盖索引就失效了。所以用覆盖索引有一个隐含约束你的查询列要尽量稳定不要今天查三列明天查五列。4.4 辅助索引如何避免回表这是搜索引擎上高频出现的一个问题。答案核心就一条让辅助索引覆盖你的查询列。具体做法是把查询中经常出现的列添加到辅助索引的叶子节点上也就是联合索引。查询时只SELECT索引中已有的列。避免在SELECT中随意加索引外的列。实际业务里不可能所有查询都只查索引列回表也并非完全是坏事。如果回表次数不多数据量可控性能影响就不会太大。真正要避免的是大量回表——比如一个查询扫描了10万行索引记录每行都要回表一次那10万次磁盘随机读足以把数据库拖垮。5. 最左匹配原则联合索引的使用边界5.1 最左匹配原则是怎么定义的联合索引也叫复合索引是多个列组成的索引比如(col1, col2, col3)。最左匹配原则说的是MySQL在联合索引中查找数据时会按照索引定义时列的顺序从左到右逐列匹配查询条件。只有在左侧列匹配查询条件时右侧的列才能继续参与索引查找。具体来说对于索引(a, b, c)WHERE a 1能使用索引只用到了a列。WHERE a 1 AND b 2能使用索引用到a和b列。WHERE a 1 AND b 2 AND c 3能使用索引用到三列。WHERE b 2不能使用索引除非全扫描或走其他索引。WHERE c 3不能使用索引。WHERE a 1 AND c 3能使用索引但只用到a列c列无法利用索引。为什么会有这个限制因为B树的联合索引是按第一个列、第二个列、第三个列的顺序排序的。也就是说先按a排序a相同再按b排序b相同再按c排序。在这样一个复合排序规则下如果查询条件不包含最左侧列就无法通过索引快速定位。可以类比一下词典的编排规则中文词典先按拼音首字母排首字母相同再按第二个字母排依次类推。如果你只知道一个词的第二个字母你是没法通过词典的目录快速找到这个词的。这个就是最左匹配原则的本质。5.2 最左匹配在排序和分组中的应用最左匹配原则不只作用于WHERE条件也作用于ORDER BY和GROUP BY。对于联合索引(a, b, c)SELECT * FROM t WHERE a 1 ORDER BY b;这里用到了索引的排序效果。因为在索引中a 1的记录按b有序排列所以ORDER BY b可以直接利用索引顺序不需要额外的排序操作Filesort。但如果是SELECT * FROM t WHERE a 1 ORDER BY c;那c列的顺序就无法利用索引了因为索引里b列是c的上级排序维度跳过b直接按c排序顺序并不保证。此时数据库需要对结果重新排序。GROUP BY同理。GROUP BY中出现的列也必须符合最左匹配原则才能利用索引消除临时表和排序操作。5.3 调整索引顺序的正确姿势理解了最左匹配原则你就知道联合索引的列顺序不是随便定的。常见的索引设计经验是把等值查询的列放在前面范围查询的列放在后面。因为范围查询、、BETWEEN之后的列无法利用索引继续过滤。举个例子如果想优化这样的查询SELECT * FROM t WHERE a 1 AND b 100 AND c 5;如果把索引建成(a, b, c)那a用了等值匹配b能利用索引做范围扫描但c就无法利用索引了。如果把索引建成(a, c, b)那a和c都能用索引过滤b只在最后做范围扫描。后者的过滤效果通常更好。这里涉及一个反直觉的点范围查询的列放在最后是因为它的条件会打断索引的有序性导致后续列无法继续用索引定位。索引匹配的机制是精确定位优先范围匹配是一段区间区间之后没法继续精确匹配所以把等值列放在前面更合理。6. 索引失效场景哪些SQL注定用不上索引6.1 常见索引失效场景速查平时排查SQL性能最常碰到的就是SQL没走索引。下面整理一份高频失效场景清单每条都附上原因和优化建议。失效场景示例原因优化建议LIKE左模糊WHERE name LIKE %张B树按左前缀排序左侧不定的字符串无法定位改成右模糊张%或用全文索引、搜索引擎兜底隐式类型转换WHERE phone 13800138000phone是varchar字符串列和数字比较时MySQL会对列做类型转换参数类型与列类型保持一致或显式CAST对索引列使用函数WHERE DATE(create_time) 2024-01-01函数破坏了索引列的原始顺序改成范围条件create_time 2024-01-01 AND create_time 2024-01-02索引列参与运算WHERE age 1 30表达式改变列值B树中无法按原值匹配WHERE age 29OR连接非索引列WHERE name a OR status 1status无索引OR两侧若有一侧无法走索引整条SQL往往放弃索引拆分SQL或对OR两侧列都建索引NOT IN / NOT EXISTSWHERE id NOT IN (...)范围取反无法利用索引有序性改写为LEFT JOIN等方案IS NOT NULLWHERE name IS NOT NULL非空判断无法通过索引快速定位比例极高时优化器直接全表扫视实际数据分布决定数据稀疏时可不处理前导列缺失WHERE b 2联合索引(a,b)但条件不含a违反最左匹配原则调整索引列顺序或新增适配的索引这张表里LIKE左模糊和隐式类型转换是我实际排查中遇到最多的情况。尤其是隐式类型转换很多人写了一年SQL都没意识到这个问题——索引列是varchar查参数传了数字MySQL底层会把索引列转成数字去比较索引就废了。6.2 优化器放弃索引的隐藏原因有些情况是SQL写法没问题但优化器就是不走索引。最典型的场景是回表成本太高。如果查询用了辅助索引但SELECT涉及的列在索引里找不到需要回表。当这个条件匹配的行很多比如占了全表的30%以上优化器会认为反正要大量回表还不如全表扫描来得快。这时候即使索引可用优化器也会选择全表扫描。另外一个常见原因是区分度太低。像gender列只有男、女两个值用索引查性别男可能查出一半的数据这种情况下索引不划算。遇到这类情况你的处理方向不是逼优化器走索引而是改变查询结构。比如改成覆盖索引查询、加过滤条件缩小范围或者拆成多个查询。6.3 MySQL排序场景的索引优化排序是另一个容易踩坑的点。ORDER BY在索引列上一般能利用索引有序性但要满足两个条件排序列符合最左匹配原则且排序方向一致。如果ORDER BY的方向和索引定义方向不一致比如索引是(a ASC, b ASC)你写ORDER BY a DESC, b DESCMySQL有时候也会走索引文件逆序扫。但如果是ORDER BY a ASC, b DESC混合方向那索引排序就用不上了大概率出现Using filesort。说到FilesortMySQL的排序有两种实现方式如果要排序的数据量小会在内存中做快速排序数据量大会使用磁盘上的归并排序。无论哪种都是额外消耗。所以设计索引时高频排序查询的排序列最好包含在索引中排序列顺序也要符合最左匹配原则。6.4 一个关于JOIN的索引建议JOIN的关联字段是否走索引直接影响关联查询的效率。join时MySQL会从驱动表逐行取数据然后去被驱动表里匹配这个过程类似于查询。如果被驱动表的关联字段没有索引每次匹配都要做全表扫描性能会很差。所以JOIN优化的核心是被驱动表的关联字段必须建索引。这条原则比选哪个表做驱动表更优先。之前处理过一个三表JOIN的慢查询关联字段完全没索引Join Type是ALL三张表都是几十万行一条查询跑了10秒。给被驱动表的关联字段补上索引后查询降到几百毫秒。7. 索引优化实战EXPLAIN怎么读、索引怎么设计7.1 EXPLAIN执行计划快速解读索引优化绕不开EXPLAIN。它的作用是查看SQL的执行计划MySQL会告诉你它打算怎么执行这条SQL——先读哪张表、用哪个索引、扫描多少行。核心关注字段有这几个type访问类型。从好到差依次是system const eq_ref ref range index ALL。达到range及以上基本问题不大ALL意味着全表扫描。key实际使用的索引名。为NULL表示没走索引。rows预估扫描行数。数值越小越好。Extra额外信息。出现Using filesort和Using temporary要警惕说明排序或分组没用上索引。一个比较典型的优化案例SQL执行计划显示type ref, rows 100, Extra Using index condition。这个状态是可以接受的。如果显示type ALL, rows 800000, Extra Using where那就必须处理了。用EXPLAIN做索引优化正确节奏是先看type再看key然后看rows和Extra。如果type是ALL说明SQL压根没走索引如果key指向某个索引但Extra里有Using filesort说明排序是额外做的还能再优化。7.2 慢日志和优化器追踪的辅助定位除了EXPLAIN还有两个工具也很实用。第一个是慢查询日志。MySQL可以把执行时间超过指定阈值的SQL记录到日志里。线上环境建议开启慢日志把long_query_time设置为1秒或更短隔几天看一眼基本能找出性能瓶颈。第二个是Optimizer Trace。MySQL 5.6以上版本支持追踪优化器的决策过程可以看到优化器为什么选择了某个索引或拒绝了某个索引。这个工具适合在EXPLAIN结果让你困惑的时候使用可以帮你进一步了解优化器的真实想法。7.3 从零设计一套索引的完整步骤网上有很多索引设计的银弹建议但实际项目里每个表的情况都不一样。我自己做索引设计一般按这个流程走收集核心查询把业务系统里的SQL全部梳理一遍标出高频查询、慢查询和事务关键操作。分析查询模式每个查询的WHERE条件哪些列是等值的、哪些是范围的ORDER BY和GROUP BY涉及哪些列。确定索引列顺序等值列放前面范围列放中间排序列放最后。排序需求可以一起并入联合索引。评估覆盖索引收益高频查询如果只需要少数几列考虑把这几列加入索引做成覆盖索引。控制索引数量单表索引数量一般控制在5个以内超过这个数量写入性能大概率会受影响。验证执行计划逐个查询跑EXPLAIN看type、key、rows和Extra是否符合预期。线上灰度验证索引变更建议先在测试环境压测确认无性能回退后再到线上执行。这套流程跑过很多次基本能保证在业务正常迭代的前提下把索引建到最优解附近。7.4 索引维护与常见操作注意点索引建完之后还需要维护。随着数据持续写入B树会发生页分裂索引碎片会增加。碎片多了索引的查询效率会下降。处理方式是定期执行OPTIMIZE TABLE重建表和索引回收碎片空间。在线对大表做索引变更时要小心。MySQL 5.7以上版本支持在线DDL多数索引操作不会锁表但如果并发写入非常高还是建议放在业务低峰期做。为了安全起见大表加索引前最好先做备份或先在从库上验证。另外一个容易被跳过的问题是查看索引是否真的被使用。MySQL有个index_statistics分析工具但平时最简单的方式还是查慢日志。如果一个索引建了很久慢日志里完全没有相关SQL用到它那这个索引可能是多余的可以考虑删除——毕竟每个索引都在拖慢写入速度。8. 实操经验总结我踩过的那些索引坑文章写到最后分享几条我做索引优化踩过的坑和经验都是实实在在用线上故障换来的。第一个坑模糊查询的伪优化。有次业务反馈某个模糊查询很慢开发给name字段建了索引但SQL写的是LIKE %关键词%索引压根用不上。这种场景的根本解法是全文索引MySQL FULLTEXT或者引入搜索引擎比如ESMySQL普通索引解决不了前导模糊匹配问题。第二个坑OR条件导致索引失效。一条SQL里有OR连接两个字段一个字段有索引一个字段没有整条SQL直接走全表扫描。当时排查了很久才找到这个原因。解决方式是把SQL拆成两条用UNION ALL合并。这是SQL改写里最实用的一招。第三个坑联合索引顺序搞反。建的索引和查询完全不匹配字段都在索引里但顺序不对——比如条件用b和c索引建的是(a, b, c)因为没包含a整条查询走不了索引。后来调整字段顺序重新建了索引查询效率直接翻倍。这个教训就是设计联合索引前一定要先看SQL的WHERE条件。第四个坑优化器选择了错误的索引。某些情况下表上有多个索引优化器根据统计信息判断走A索引但实际业务数据分布和统计信息不一致走A索引反而更慢。这时候可以用FORCE INDEX强制指定索引或者更推荐的方式是重建统计信息ANALYZE TABLE。索引优化是一个持续的、需要不断验证的过程业务在变数据分布也在变没有一劳永逸的索引方案。每次加新功能、写新SQL之前都带上执行计划跑一遍你就能避免大多数线上性能问题。我觉得做索引优化最有价值的能力不是死记硬背哪些规则而是理解它底层的数据结构——明白了B树的组织方式和回表机制很多规则你根本不需要背顺着结构推导就能得出正确答案。这也是我在这一篇里花了大量篇幅讲原理的原因。 MySQL索引优化：从B+树到回表、覆盖索引与最左匹配
1. 从一条慢SQL说起索引为什么能提速先讲个真实场景。之前接手过一个线上订单查询接口数据量大概800万行业务方反馈某个列表页越来越慢从最初的几十毫秒退化到2.7秒。当时第一反应是看执行计划结果发现走了全表扫描type列是ALL扫描行数接近全部数据。那个表压根就没建索引。后来给查询条件涉及的列补了联合索引接口耗时直接掉到40毫秒以内。差距是几十倍靠的就是索引。很多人对MySQL索引的理解停留在建了索引就快这个层面但实际工作中索引用得好不好直接决定了你是让数据库干活还是让数据库死扛。索引为什么能快核心原因是它改变了数据查找的方式——从从头翻到尾变成按目录找页码。而支撑这套机制的就是B树。这篇文章聚焦几个核心话题B树到底解决了什么问题、回表是怎么发生的、覆盖索引为什么能救命、最左匹配原则到底怎么理解以及索引失效的常见坑。适合刚接触索引的同学也适合写了几年SQL但没系统梳理过索引原理的开发者。看完全文你能做到三件事解释清楚索引的结构原理、写出能命中索引的SQL、一眼看出哪些SQL注定走不了索引。2. MySQL索引优化详解从B树到回表、覆盖索引与最左匹配原则2.1 索引的本质是什么索引是一种数据结构目的是加速数据检索。你可以把它理解成书籍的目录——没有目录的书你要找某个知识点就得一页页翻有了目录直接翻到对应页码就行。数据库里的数据存储在磁盘上磁盘IO的速度比内存慢几个数量级所以减少磁盘IO次数是数据库性能优化的核心目标之一。索引能在这一环节发挥作用因为它让数据库不需要扫描全表就能定位到目标数据。全表扫描意味着要把表里的每一行数据都读一遍数据量一大磁盘IO次数直线上升。有了索引数据库先查索引结构找到目标数据所在的位置再去读取对应的数据行。整个过程磁盘IO次数大幅减少查询自然就快了。MySQL默认的存储引擎是InnoDBInnoDB的索引结构就是B树。B树不是MySQL的发明而是一种广泛应用在数据库和文件系统中的数据结构。它和二叉树、红黑树这些结构的最大区别在于它是为磁盘场景设计的——能够大幅降低树的层高从而降低IO次数。后面会详细展开这个设计思想。2.2 索引不是越多越好很多新手的误区是既然索引能加速那就把所有可能用到的列都加上索引。这种做法是错的。索引是有成本的而且成本不止一种。第一是存储成本。每个索引都是一棵B树数据量一大索引占用的磁盘空间相当可观。第二是写入成本。每次INSERT、UPDATE、DELETE操作数据库不仅要更新数据本身还要同步维护表上的每一个索引。索引越多写入越慢。第三是优化器成本。查询时优化器需要评估走哪个索引最合适索引太多会增加优化器的判断负担极端情况下还可能导致优化器选错索引。所以索引设计的原则是按需创建为高频查询服务而不是为万一用得上服务。后面讨论索引失效的场景时你会发现某些索引建了实际用不上那基本就是纯浪费。3. B树结构拆解为什么偏偏是它3.1 B树到底长什么样B树是一种多路平衡搜索树。跟二叉树相比它的每个节点可以存储多个键值并且可以有多个子节点。简单画一下B树的结构叶子节点存储实际的数据记录InnoDB中叶子节点存的是整行数据或主键值非叶子节点只存储键值和指向子节点的指针不存数据所有叶子节点通过双向链表连接形成一个有序的链表结构所有数据都存放在叶子节点上且按顺序排列这跟B树有个关键区别B树的每个节点都能存数据而B树只有叶子节点存数据。这个区别带来的优势是B树的非叶子节点能容纳更多的键值树的高度更矮查询时的磁盘IO次数更少。还有一个容易被忽视的优势B树的叶子节点是有序链接的这让范围查询变得非常高效。查一个范围的数据只需要定位到范围的起点然后顺着链表往后遍历就行。而B树的叶子节点之间没有这种连接范围查询要多做一些回溯操作。3.2 为什么B树而不是红黑树或跳表这是索引原理面试中几乎必问的一个问题。先看红黑树。红黑树是二叉平衡树每个节点最多有两个子节点。在数据量大的情况下树的高度会很高。假设数据量是1000万红黑树的高度大约是log2(1000万)接近24层。每访问一层节点如果该节点不在内存里就要产生一次磁盘IO。24层意味着最坏情况下要24次磁盘IO这个代价是数据库承受不起的。而B树呢每个节点能存储多个键值通常一个节点大小对应一个磁盘页约16KB一个节点里能塞下几百个键值。同样是1000万条数据B树的高度通常只有3到4层。查询时最多3到4次磁盘IO就能定位到目标数据效率比红黑树高一个数量级。再看跳表。跳表在内存数据库中确实用得不错比如Redis的有序集合就是用跳表实现的。但跳表在磁盘场景下的表现远不如B树。跳表是链表结构节点之间通过指针相连在磁盘上存储时无法保证物理连续性随机访问的IO开销很大。B树的节点被设计成按磁盘页存储节点内部物理连续读取一个节点只需要一次磁盘IO这天然契合磁盘的读取机制。所以结论很清晰B树能在IO次数和查询效率之间达到最优平衡这是数据库选择B树作为索引结构的根本原因。3.3 InnoDB中的主键索引与辅助索引InnoDB的索引分成两类主键索引聚簇索引和辅助索引二级索引。主键索引的叶子节点存的是整行数据。InnoDB表本身就是一个按主键组织的B树表数据就存放在主键索引的叶子节点上。这也是聚簇索引名字的由来——数据行和索引聚在一起。所以InnoDB表必须有主键如果没有显式定义主键InnoDB会找一个非空唯一列作为主键找不到的话会隐式生成一个6字节的rowid作为主键。辅助索引的叶子节点存的是索引列的值加上主键值而不是整行数据。这里有个重要推论通过辅助索引查询时如果只需要索引列和主键列的数据那么直接从辅助索引的叶子节点就能拿到不需要回表。如果需要索引列之外的其他列数据那就要拿主键值去主键索引里再查一次这个过程叫回表。关于主键索引和唯一索引的区别可以这样理解主键索引是聚簇索引决定了数据行的物理存储顺序唯一索引只是普通的辅助索引只是加上了唯一约束。主键索引一张表只能有一个唯一索引可以建多个。主键不允许为NULL唯一索引允许NULL而且多个NULL值是被允许的MySQL中的UNIQUE约束不限制多个NULL。提示InnoDB推荐使用自增整数主键因为数据按主键顺序存储自增主键能让插入操作顺序进行减少页分裂的概率。用UUID这类随机值做主键数据插入时可能要频繁移动位置、分裂页面写入性能会明显下降。4. 回表原理与覆盖索引一次查询的真实旅程4.1 回表是怎么发生的用一个具体例子演示回表过程。假设有一张用户表userCREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, name varchar(50) DEFAULT NULL, age int DEFAULT NULL, city varchar(50) DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name) ) ENGINEInnoDB;执行下面的查询SELECT * FROM user WHERE name 张三;执行过程是先在辅助索引idx_name的B树里查找name 张三的记录。辅助索引的叶子节点存的是name值和对应的主键id值。找到id 123举例后拿着这个主键id去主键索引里再查一次。主键索引的B树里id 123对应的叶子节点存的就是整行数据。返回结果。第2步就是回表。每次回表相当于一次额外的B树查找数据量越大回表带来的IO开销越明显。如果查询条件是WHERE name 张三但我们只查询name和id这两个字段比如SELECT id, name FROM user WHERE name 张三;那就不需要回表了。因为辅助索引idx_name的叶子节点已经包含了name和id查询所需的所有数据都在这个索引里InnoDB不会再去查主键索引。这种索引本身就覆盖了查询所需的全部列的情况就叫覆盖索引。4.2 覆盖索引的正确使用姿势覆盖索引的精髓是尽量让查询的列和条件列落在同一个索引里。这样就不需要回表查询效率会大幅提升。什么情况下适合用覆盖索引常见场景有两种。第一高频查询固定返回某些列。比如列表页只展示id、title、status三列那就建一个包含这三个列的联合索引。第二统计类查询。比如COUNT某个条件下的记录数如果索引叶子节点里正好包含条件列不需要回表就能完成统计。在设计覆盖索引时有一个需要权衡的点索引列越多索引体积越大写入成本越高。所以不是索引里的字段越多越好而是要在覆盖常用查询和控制索引体积之间找平衡。经验是先看慢查询日志里哪些SQL高频出现再针对这些SQL设计索引。4.3 一个覆盖索引优化的真实对比之前处理过一个内容表表结构大概是这样CREATE TABLE article ( id bigint NOT NULL AUTO_INCREMENT, cat_id int DEFAULT NULL, title varchar(200) DEFAULT NULL, status tinyint DEFAULT NULL, create_time datetime DEFAULT NULL, PRIMARY KEY (id), KEY idx_cat_status (cat_id, status) ) ENGINEInnoDB;原业务SQL是查某个分类下的文章列表只取id和titleSELECT id, title FROM article WHERE cat_id 10 AND status 1 ORDER BY id DESC LIMIT 20;这条SQL需要回表取title字段。后来把索引改成cat_id, status, title变成了覆盖索引查询EXPLAIN的Extra字段从Using where变成了Using index查询耗时降了一倍多。这就是覆盖索引的实战价值。但要注意覆盖索引也不是万能的——如果你在查询里SELECT了索引之外的列覆盖索引就失效了。所以用覆盖索引有一个隐含约束你的查询列要尽量稳定不要今天查三列明天查五列。4.4 辅助索引如何避免回表这是搜索引擎上高频出现的一个问题。答案核心就一条让辅助索引覆盖你的查询列。具体做法是把查询中经常出现的列添加到辅助索引的叶子节点上也就是联合索引。查询时只SELECT索引中已有的列。避免在SELECT中随意加索引外的列。实际业务里不可能所有查询都只查索引列回表也并非完全是坏事。如果回表次数不多数据量可控性能影响就不会太大。真正要避免的是大量回表——比如一个查询扫描了10万行索引记录每行都要回表一次那10万次磁盘随机读足以把数据库拖垮。5. 最左匹配原则联合索引的使用边界5.1 最左匹配原则是怎么定义的联合索引也叫复合索引是多个列组成的索引比如(col1, col2, col3)。最左匹配原则说的是MySQL在联合索引中查找数据时会按照索引定义时列的顺序从左到右逐列匹配查询条件。只有在左侧列匹配查询条件时右侧的列才能继续参与索引查找。具体来说对于索引(a, b, c)WHERE a 1能使用索引只用到了a列。WHERE a 1 AND b 2能使用索引用到a和b列。WHERE a 1 AND b 2 AND c 3能使用索引用到三列。WHERE b 2不能使用索引除非全扫描或走其他索引。WHERE c 3不能使用索引。WHERE a 1 AND c 3能使用索引但只用到a列c列无法利用索引。为什么会有这个限制因为B树的联合索引是按第一个列、第二个列、第三个列的顺序排序的。也就是说先按a排序a相同再按b排序b相同再按c排序。在这样一个复合排序规则下如果查询条件不包含最左侧列就无法通过索引快速定位。可以类比一下词典的编排规则中文词典先按拼音首字母排首字母相同再按第二个字母排依次类推。如果你只知道一个词的第二个字母你是没法通过词典的目录快速找到这个词的。这个就是最左匹配原则的本质。5.2 最左匹配在排序和分组中的应用最左匹配原则不只作用于WHERE条件也作用于ORDER BY和GROUP BY。对于联合索引(a, b, c)SELECT * FROM t WHERE a 1 ORDER BY b;这里用到了索引的排序效果。因为在索引中a 1的记录按b有序排列所以ORDER BY b可以直接利用索引顺序不需要额外的排序操作Filesort。但如果是SELECT * FROM t WHERE a 1 ORDER BY c;那c列的顺序就无法利用索引了因为索引里b列是c的上级排序维度跳过b直接按c排序顺序并不保证。此时数据库需要对结果重新排序。GROUP BY同理。GROUP BY中出现的列也必须符合最左匹配原则才能利用索引消除临时表和排序操作。5.3 调整索引顺序的正确姿势理解了最左匹配原则你就知道联合索引的列顺序不是随便定的。常见的索引设计经验是把等值查询的列放在前面范围查询的列放在后面。因为范围查询、、BETWEEN之后的列无法利用索引继续过滤。举个例子如果想优化这样的查询SELECT * FROM t WHERE a 1 AND b 100 AND c 5;如果把索引建成(a, b, c)那a用了等值匹配b能利用索引做范围扫描但c就无法利用索引了。如果把索引建成(a, c, b)那a和c都能用索引过滤b只在最后做范围扫描。后者的过滤效果通常更好。这里涉及一个反直觉的点范围查询的列放在最后是因为它的条件会打断索引的有序性导致后续列无法继续用索引定位。索引匹配的机制是精确定位优先范围匹配是一段区间区间之后没法继续精确匹配所以把等值列放在前面更合理。6. 索引失效场景哪些SQL注定用不上索引6.1 常见索引失效场景速查平时排查SQL性能最常碰到的就是SQL没走索引。下面整理一份高频失效场景清单每条都附上原因和优化建议。失效场景示例原因优化建议LIKE左模糊WHERE name LIKE %张B树按左前缀排序左侧不定的字符串无法定位改成右模糊张%或用全文索引、搜索引擎兜底隐式类型转换WHERE phone 13800138000phone是varchar字符串列和数字比较时MySQL会对列做类型转换参数类型与列类型保持一致或显式CAST对索引列使用函数WHERE DATE(create_time) 2024-01-01函数破坏了索引列的原始顺序改成范围条件create_time 2024-01-01 AND create_time 2024-01-02索引列参与运算WHERE age 1 30表达式改变列值B树中无法按原值匹配WHERE age 29OR连接非索引列WHERE name a OR status 1status无索引OR两侧若有一侧无法走索引整条SQL往往放弃索引拆分SQL或对OR两侧列都建索引NOT IN / NOT EXISTSWHERE id NOT IN (...)范围取反无法利用索引有序性改写为LEFT JOIN等方案IS NOT NULLWHERE name IS NOT NULL非空判断无法通过索引快速定位比例极高时优化器直接全表扫视实际数据分布决定数据稀疏时可不处理前导列缺失WHERE b 2联合索引(a,b)但条件不含a违反最左匹配原则调整索引列顺序或新增适配的索引这张表里LIKE左模糊和隐式类型转换是我实际排查中遇到最多的情况。尤其是隐式类型转换很多人写了一年SQL都没意识到这个问题——索引列是varchar查参数传了数字MySQL底层会把索引列转成数字去比较索引就废了。6.2 优化器放弃索引的隐藏原因有些情况是SQL写法没问题但优化器就是不走索引。最典型的场景是回表成本太高。如果查询用了辅助索引但SELECT涉及的列在索引里找不到需要回表。当这个条件匹配的行很多比如占了全表的30%以上优化器会认为反正要大量回表还不如全表扫描来得快。这时候即使索引可用优化器也会选择全表扫描。另外一个常见原因是区分度太低。像gender列只有男、女两个值用索引查性别男可能查出一半的数据这种情况下索引不划算。遇到这类情况你的处理方向不是逼优化器走索引而是改变查询结构。比如改成覆盖索引查询、加过滤条件缩小范围或者拆成多个查询。6.3 MySQL排序场景的索引优化排序是另一个容易踩坑的点。ORDER BY在索引列上一般能利用索引有序性但要满足两个条件排序列符合最左匹配原则且排序方向一致。如果ORDER BY的方向和索引定义方向不一致比如索引是(a ASC, b ASC)你写ORDER BY a DESC, b DESCMySQL有时候也会走索引文件逆序扫。但如果是ORDER BY a ASC, b DESC混合方向那索引排序就用不上了大概率出现Using filesort。说到FilesortMySQL的排序有两种实现方式如果要排序的数据量小会在内存中做快速排序数据量大会使用磁盘上的归并排序。无论哪种都是额外消耗。所以设计索引时高频排序查询的排序列最好包含在索引中排序列顺序也要符合最左匹配原则。6.4 一个关于JOIN的索引建议JOIN的关联字段是否走索引直接影响关联查询的效率。join时MySQL会从驱动表逐行取数据然后去被驱动表里匹配这个过程类似于查询。如果被驱动表的关联字段没有索引每次匹配都要做全表扫描性能会很差。所以JOIN优化的核心是被驱动表的关联字段必须建索引。这条原则比选哪个表做驱动表更优先。之前处理过一个三表JOIN的慢查询关联字段完全没索引Join Type是ALL三张表都是几十万行一条查询跑了10秒。给被驱动表的关联字段补上索引后查询降到几百毫秒。7. 索引优化实战EXPLAIN怎么读、索引怎么设计7.1 EXPLAIN执行计划快速解读索引优化绕不开EXPLAIN。它的作用是查看SQL的执行计划MySQL会告诉你它打算怎么执行这条SQL——先读哪张表、用哪个索引、扫描多少行。核心关注字段有这几个type访问类型。从好到差依次是system const eq_ref ref range index ALL。达到range及以上基本问题不大ALL意味着全表扫描。key实际使用的索引名。为NULL表示没走索引。rows预估扫描行数。数值越小越好。Extra额外信息。出现Using filesort和Using temporary要警惕说明排序或分组没用上索引。一个比较典型的优化案例SQL执行计划显示type ref, rows 100, Extra Using index condition。这个状态是可以接受的。如果显示type ALL, rows 800000, Extra Using where那就必须处理了。用EXPLAIN做索引优化正确节奏是先看type再看key然后看rows和Extra。如果type是ALL说明SQL压根没走索引如果key指向某个索引但Extra里有Using filesort说明排序是额外做的还能再优化。7.2 慢日志和优化器追踪的辅助定位除了EXPLAIN还有两个工具也很实用。第一个是慢查询日志。MySQL可以把执行时间超过指定阈值的SQL记录到日志里。线上环境建议开启慢日志把long_query_time设置为1秒或更短隔几天看一眼基本能找出性能瓶颈。第二个是Optimizer Trace。MySQL 5.6以上版本支持追踪优化器的决策过程可以看到优化器为什么选择了某个索引或拒绝了某个索引。这个工具适合在EXPLAIN结果让你困惑的时候使用可以帮你进一步了解优化器的真实想法。7.3 从零设计一套索引的完整步骤网上有很多索引设计的银弹建议但实际项目里每个表的情况都不一样。我自己做索引设计一般按这个流程走收集核心查询把业务系统里的SQL全部梳理一遍标出高频查询、慢查询和事务关键操作。分析查询模式每个查询的WHERE条件哪些列是等值的、哪些是范围的ORDER BY和GROUP BY涉及哪些列。确定索引列顺序等值列放前面范围列放中间排序列放最后。排序需求可以一起并入联合索引。评估覆盖索引收益高频查询如果只需要少数几列考虑把这几列加入索引做成覆盖索引。控制索引数量单表索引数量一般控制在5个以内超过这个数量写入性能大概率会受影响。验证执行计划逐个查询跑EXPLAIN看type、key、rows和Extra是否符合预期。线上灰度验证索引变更建议先在测试环境压测确认无性能回退后再到线上执行。这套流程跑过很多次基本能保证在业务正常迭代的前提下把索引建到最优解附近。7.4 索引维护与常见操作注意点索引建完之后还需要维护。随着数据持续写入B树会发生页分裂索引碎片会增加。碎片多了索引的查询效率会下降。处理方式是定期执行OPTIMIZE TABLE重建表和索引回收碎片空间。在线对大表做索引变更时要小心。MySQL 5.7以上版本支持在线DDL多数索引操作不会锁表但如果并发写入非常高还是建议放在业务低峰期做。为了安全起见大表加索引前最好先做备份或先在从库上验证。另外一个容易被跳过的问题是查看索引是否真的被使用。MySQL有个index_statistics分析工具但平时最简单的方式还是查慢日志。如果一个索引建了很久慢日志里完全没有相关SQL用到它那这个索引可能是多余的可以考虑删除——毕竟每个索引都在拖慢写入速度。8. 实操经验总结我踩过的那些索引坑文章写到最后分享几条我做索引优化踩过的坑和经验都是实实在在用线上故障换来的。第一个坑模糊查询的伪优化。有次业务反馈某个模糊查询很慢开发给name字段建了索引但SQL写的是LIKE %关键词%索引压根用不上。这种场景的根本解法是全文索引MySQL FULLTEXT或者引入搜索引擎比如ESMySQL普通索引解决不了前导模糊匹配问题。第二个坑OR条件导致索引失效。一条SQL里有OR连接两个字段一个字段有索引一个字段没有整条SQL直接走全表扫描。当时排查了很久才找到这个原因。解决方式是把SQL拆成两条用UNION ALL合并。这是SQL改写里最实用的一招。第三个坑联合索引顺序搞反。建的索引和查询完全不匹配字段都在索引里但顺序不对——比如条件用b和c索引建的是(a, b, c)因为没包含a整条查询走不了索引。后来调整字段顺序重新建了索引查询效率直接翻倍。这个教训就是设计联合索引前一定要先看SQL的WHERE条件。第四个坑优化器选择了错误的索引。某些情况下表上有多个索引优化器根据统计信息判断走A索引但实际业务数据分布和统计信息不一致走A索引反而更慢。这时候可以用FORCE INDEX强制指定索引或者更推荐的方式是重建统计信息ANALYZE TABLE。索引优化是一个持续的、需要不断验证的过程业务在变数据分布也在变没有一劳永逸的索引方案。每次加新功能、写新SQL之前都带上执行计划跑一遍你就能避免大多数线上性能问题。我觉得做索引优化最有价值的能力不是死记硬背哪些规则而是理解它底层的数据结构——明白了B树的组织方式和回表机制很多规则你根本不需要背顺着结构推导就能得出正确答案。这也是我在这一篇里花了大量篇幅讲原理的原因。