在MySQL这条进阶路上索引就是那个一懂全懂、一卡全卡的知识节点。前期写SQL可能没太大感觉等数据量一上来、线上查询变慢你回头看执行计划时才发现当初建表时随手写的几个索引到底有多重要。这篇文章想系统性地把索引这条线拉通从数据结构出发讲到复合索引、最左前缀、索引失效、覆盖索引再到实际设计索引的具体套路和排查思路。不管你是刚能熟练写增删改查的开发还是已经在负责表结构设计的同学这篇都值得边看边在自己库里试一遍。我平时做性能和调优相关的工作索引相关的坑踩了不少。有一类问题反复出现线上一个查询跑几十秒DBA过来一看要么是索引压根没建要么是建了但因为写法不对导致索引失效。说到底索引不是建了就完事理解它工作的底层逻辑你才知道每个索引该怎么建、SQL该怎么写。这篇我就按自己的理解和实操经验来说清楚尽量不说废话。1. 为什么索引是MySQL性能的核心先聊点为什么。MySQL本质上就是一个存储和检索数据的系统而检索速度的快慢直接决定了业务接口的响应时间。在没有索引的情况下InnoDB要找到一行数据只能做全表扫描——也就是把整张表的数据页从磁盘搬出来逐行比对。假设一张表有500万行平均每行200字节那就是约1GB的数据量。哪怕InnoDB有缓冲池帮忙缓存热点页第一次查询的磁盘IO也够喝一壶了。索引的本质是拿额外的存储空间和维护开销换取查询时的磁盘IO次数大幅下降。这个交易在很多场景下是划算的。比如有个简单的等值查询SELECT * FROM user WHERE phone 13800138000;如果phone列上没有索引MySQL会扫描主键索引的叶子节点逐行比对phone字段值。这里的叶子节点存储的是整行数据每页能放的行数是有限的。假设每页16KB、每行约200字节那么一页大概能放80行500万行就需要6万多页。就算一次IO能读一页你也要做6万多次逻辑读。如果加了phone的普通索引情况就变成了先通过辅助索引定位到主键值再回表查一次。辅助索引的叶子节点只存索引列和主键值假设phone字段占11字节、主键占8字节加上其他开销一页能放的行数多得多树的高度可能只有3层。也就是说你只需要3次左右的磁盘IO就能定位到目标记录。这个差距就是索引带来的核心收益。MySQL的索引结构是B树不是二叉树也不是哈希表。这个选择背后有几个非常实际的考量。二叉树的树高和数据量成对数关系看起来还行但实际存储时每个节点只有一个键值当数据量到千万级别时树高会达到20多层而InnoDB每次从磁盘读数据是按页读的一次IO对应一个节点20多层意味着最坏情况下要20多次磁盘IO这在机械硬盘时代是不可接受的。哈希表做等值查询确实快O(1)复杂度但它天生无法支持范围查询和排序。你执行一个WHERE age BETWEEN 20 AND 30哈希表只能逐个枚举没有任何加速手段。B树的兄弟叶子节点之间用双向链表串联范围查询和排序就变得非常顺手。数据量千万级时B树通常也就3到4层根节点和中间层节点因为常被访问几乎都能被缓冲池缓存真正每次都走磁盘IO的只有最后一层叶子节点。这个设计使得绝大多数查询在IO层面都极其可控。MySQL最终选择B树作为索引结构本质上是从磁盘IO的特性出发做的工程决策——尽量减少随机IO让顺序IO和缓存命中率发挥最大作用。2. 索引的底层结构InnoDB到底在玩什么2.1 B树为什么长这样B树和B树的区别很多人背过但没有真正理解。B树在每个节点上都存数据而B树只在叶子节点存数据非叶子节点只存索引键值。这个差异直接影响了两件事。第一非叶子节点能容纳更多的键值树更矮IO次数更少第二叶子节点通过链表相连方便范围扫描。用个生活化的类比B树像一本每页都有完整目录的书B树像图书馆的索引卡柜目录卡只告诉你在哪一排书架具体书的位置要到最后一层卡片才写清楚所有的卡片又用一根绳子串起来从第一张顺到最后一张就能按顺序走完。InnoDB的主键索引即聚簇索引叶子节点直接存储整行数据。辅助索引的叶子节点存储的是索引列值和主键值。这是InnoDB最核心的设计也是后边很多优化技巧的源头。比如覆盖索引就是让查询所需的列全部在辅助索引里能找到省掉回表那一次IO又比如主键为什么建议用自增整数而不是UUID就是因为辅助索引叶子节点存的是主键值主键越长辅助索引越大IO开销越高UUID还是无序的插入时会导致页分裂产生大量碎片。2.2 聚簇索引和辅助索引的区别这张表最好记清楚对比项聚簇索引主键索引辅助索引二级索引叶子节点存储内容整行数据索引列值 主键值每张表数量只有1个可以有多个回表需求不需要需要除非覆盖索引数据物理排序按主键排序按索引列排序主键值做辅助关键点在于所有辅助索引的叶子节点都带主键值。也就是说你给一张表建了5个索引等于多存了5份主键值的冗余数据。这也是索引数量不宜过多的原因之一——写放大很严重。插入一条数据不仅要更新主键索引的B树还要同步更新所有辅助索引的B树。所以MySQL里有一个常见建议单表索引数控制在5个以内不是没有道理的。你加了索引查询变快了但在高并发写入场景下每个索引的维护都是额外开销。2.3 页分裂和索引碎片数据插入B树时如果某个叶子页已经满了就必须申请新页并将一半数据搬过去这个过程叫页分裂。页分裂本身是正常的但如果分裂频繁会造成数据页的物理存储不连续产生碎片。碎片率高了以后即使逻辑上相邻的数据物理上也隔得很远顺序扫描的性能会显著下降。典型场景就是主键用UUID或业务随机字符串。UUID是无序的每次插入都可能落在B树中间的某个位置触发页分裂。而自增主键的插入永远追加在末尾极少触发分裂。所以在设计表结构时我通常会建议尽量用自增整型做主键除非有分库分表的全局唯一ID需求再用雪花算法这类有序的分布式ID方案。3. 复合索引与最左前缀面试必问实战也必用3.1 复合索引的底层逻辑复合索引是指在一个索引中包含多个列比如idx_user_age_name (age, name)。它的排序规则是先按第一个列排序第一个列相同的再按第二个列排序依此类推。这个先按谁排的顺序决定了索引的适用范围。在实际建索引之前先理解一个核心原则复合索引的设计要尽量让查询条件里的列能用上索引的顺序且不要跳跃。MySQL的优化器在做索引选择时会从复合索引的最左列开始匹配一直匹配到范围查询或等值查询结束。如果查询条件里没有包含最左列那这个复合索引基本发挥不了作用优化器大概率会放弃它选择全表扫描或者其它索引。3.2 最左前缀原则的具体表现到底什么写法能命中索引什么写法不能我用一张表说清楚。假设有一张订单表CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, order_time datetime NOT NULL, amount decimal(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_user_time (user_id, order_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;现在有这些查询-- 能命中user_id是最左列 SELECT * FROM order_info WHERE user_id 1001; -- 能命中user_id等值匹配order_time范围匹配 SELECT * FROM order_info WHERE user_id 1001 AND order_time 2024-01-01; -- 不能命中缺少user_id SELECT * FROM order_info WHERE order_time 2024-01-01;最后一条为什么不能命中因为复合索引的最左列是user_id如果查询条件里没有user_id那么索引B树中order_time列的排序是建立在user_id相同的前提下的直接按order_time范围查找时优化器没法利用索引的有序性只能全表扫描。这是最左前缀原则最容易踩的坑。3.3 覆盖索引让查询连回表都省掉覆盖索引值得单独拿出来说因为它的收益太直观了。一条SQL如果查询的列都包含在辅助索引的叶子节点中那么查询就不需要回表直接遍历辅助索引就能拿到全部结果。还是拿上面的订单表举例-- user_id、status、order_time这三列都在idx_user_status_time里 -- 不需要回表Extra会显示Using index SELECT user_id, status, order_time FROM order_info WHERE user_id 1001 AND status 1;为了达到覆盖索引的效果我在设计索引时会有意识地把查询中高频出现的列塞进索引里。但这里有一个代价索引列越多占用空间越大写入维护成本越高。所以覆盖索引不是无脑堆列而是针对高频查询做精细化设计。有一个常见的做法是把一个查询里反复出现的列做成联合索引即使它们原本不是经常一起出现在WHERE条件里只要能让这条高频查询省掉回表就值得。3.4 索引下推MySQL 5.6之后的隐形优化索引下推ICPIndex Condition Pushdown是一个容易被忽略但实际影响很大的优化。在没有ICP的情况下InnoDB通过辅助索引找到记录后需要回表然后在服务层对WHERE条件里的其它列做过滤。有了ICP之后存储引擎会在使用索引遍历时直接对索引中包含的列做条件过滤减少回表次数。举个例子SELECT * FROM user WHERE name 张三 AND age 20;假设有复合索引(name, age)在MySQL 5.6之前存储引擎通过name定位到所有张三的记录先回表再过滤age 20的记录。在5.6及之后存储引擎在索引遍历时就会判断age是否符合条件不符合的直接跳过不需要回表。数据量大的时候这个优化能把IO减少一大截。理解ICP的好处在于你会更倾向于设计让过滤条件尽可能落在索引列上的复合索引让下推机制发挥最大效果。4. 索引失效的典型场景这些坑我基本都踩过4.1 索引失效清单索引失效是个高频面试题但很多答案只列了现象没解释原因。我根据实际排查经验把常见的失效场景整理成下表并说明为什么失效场景示例失效原因对索引列使用函数WHERE YEAR(create_time) 2024函数改变了列值B树的有序性失效对索引列做隐式类型转换WHERE phone 13800138000phone是varchar数字会被转成字符串索引失效模糊查询前置通配符WHERE name LIKE %张三%通配符在前无法利用B树的按前缀匹配特性联合索引不满足最左前缀WHERE order_time 2024-01-01缺user_idB树先按最左列排序使用OR连接非索引列WHERE user_id 1 OR status 2OR会拆成多个条件需要多个索引做合并可能退化查询条件里的列做了运算WHERE age 1 30运算改变了列的原始值NOT IN、NOT LIKE、!WHERE status ! 1不等于无法匹配B树的等值/范围查找范围查询后的列无法继续走索引WHERE user_id 1 AND create_time 2024-01-01 AND status 1status在范围后范围查询后索引有序性被破坏这里面有几点需要单独解释。隐式类型转换是特别隐蔽的坑因为MySQL有时能自动转换有时不能。比如phone 13800138000MySQL会把字符串列和数字比较时将字符串转换成数字再做比较。比较值全部发生转换后索引列本身无法直接匹配优化器只能放弃索引。解决方式很简单应用层传参时保持类型一致或者SQL里写成字符串形式。OR有一个特殊情况如果OR连接的多个条件列都各自有索引MySQL可以用索引合并Index Merge来优化不一定全表扫描。但索引合并本身成本不低还需要额外的排序去重开销性能通常不如直接设计一个复合索引来得干净。所以遇到OR优先考虑改写SQL或合并索引。4.2 为什么范围查询后的列会失效这是理解索引失效最容易混淆的地方我想拆开说透。复合索引a, b, c的B树排序规则是先按a排序a相同的按b排序b相同的按c排序。当你执行WHERE a 1 AND b 2 AND c 3时MySQL能利用索引找到a1的所有记录然后在其中找到b2的记录。但这些b2的记录里c列并不一定是有序的。原因在于c的排序是在b相等的前提下才成立的而b 2是一个范围在这个范围内b值不等c的有序性就无从谈起了。所以c条件只能在索引范围内做过滤无法继续用B树跳跃查找优化器通常就不再把这个条件作为索引访问的定位条件了。这给设计索引提供了一个非常重要的思路在复合索引中把等值条件的列放在前面范围条件的列放在后面。等值条件可以帮助索引精确定位范围条件只需要一个就够。如果所有条件都是等值顺序主要看区分度和查询频率如果有范围条件范围条件后的其它列设计索引时基本不用考虑了它们只能作为普通过滤条件存在。4.3 一个实际的失效排查我之前接手过一个项目线上有个慢查询总是超时。当时的SQL简化后类似SELECT * FROM pay_record WHERE pay_time 2024-03-01 AND merchant_id 888 AND amount 100;表结构里已经有一个索引idx_pay_time按pay_time建的。结果执行计划显示全表扫描。为什么因为数据表里有上亿条记录pay_time范围内命中的记录数可能占到全表的30%以上优化器一算回表成本觉得还不如全表扫描来得快。这就是一个容易被误判为索引失效的典型案例——索引其实可以用但选择性太低优化器主动放弃。后面我把索引改成idx_merchant_pay_time (merchant_id, pay_time)查询条件里merchant_id本来就是高区分度的列就这么一个小改动查询从6秒降到了0.1秒。这里我想强调的是判断索引有效性不能只看能不能走索引还得看优化器算出来的成本。高区分度列放前面等于帮优化器缩小了检索范围。5. 索引设计实战从where条件反推索引5.1 where a and b到底怎么建索引这个热搜词出现的频率极高算是MySQL索引设计中最经典的问题。WHERE a ? AND b ?这种情况到底建单列索引还是复合索引我的结论是优先建复合索引。原因有三点。第一复合索引可以直接通过最左前缀同时利用a和b两个列单列索引只能利用一个另一个需要回表过滤或索引合并。第二复合索引天然支持覆盖索引可以把SELECT需要的列塞进索引里。第三单列索引两个都要维护写入时索引更新的开销翻倍。具体建索引时有一个微调原则区分度高的列放前面。如果a的区分度极低比如只有0和1两个值b的区分度很高那么(a, b)和(b, a)效果差距会很大。因为如果a区分度低即使先按a定位命中数据量依然很大B树在第二层过滤的效果会打折扣而先按b定位很快就能收敛到少量记录a再作为过滤条件就很轻松。假设一张表有100万行a字段有2个不同值b字段有10万个不同值(b, a)先按b定位每个b值大约对应10行再按a过滤成本极低(a, b)先按a定位命中50万行再按b继续查找虽然B树也能处理但第一层就暴露了50万行的范围IO和CPU成本明显高。所以实操时我会先跑几条SQL看区分度SELECT COUNT(DISTINCT a) / COUNT(*) AS a_cardinality, COUNT(DISTINCT b) / COUNT(*) AS b_cardinality FROM my_table;区分度接近1的放前面。这里的逻辑其实和前面提到的B树排序规则完全一致越能快速收敛的列越应该排在索引的前面。5.2 排序查询与索引避免filesortMySQL中排序如果可以用索引就直接按B树的顺序读取数据Extra显示Using index。如果不能就需要在内存或磁盘上做排序操作也就是filesort。filesort在小数据量时无所谓但数据量一大就会产生临时文件和额外IO。比如有一张订单表有个高频查询SELECT id, user_id, amount FROM order_info WHERE user_id 1001 ORDER BY order_time DESC LIMIT 20;如果索引是(user_id, order_time)那么user_id等值定位后order_time天然有序排序操作直接省掉MySQL只需要从索引里倒序取20条记录。这就是索引本身就帮你排好序的好处。如果把ORDER BY的列换成amount索引必须回表后重新排序。所以设计索引时不能只看WHERE条件ORDER BY也是很重要的索引驱动因素。我的设计顺序是等值条件列放前面ORDER BY列紧跟其后范围条件列再往后。这样可以最大化利用索引的有序性。5.3 主键索引和唯一索引的区别热搜词里有一个问得很细的问题主键索引和唯一索引有什么区别日常很多人混着用但两者的语义和实现并不相同。对比项主键索引唯一索引每张表数量只能有一个可以有多个是否允许NULL不允许允许且允许多个NULL是否作为聚簇索引InnoDB中默认是只能是辅助索引用途行唯一标识业务唯一约束这里有一个值得注意的点在InnoDB中如果表没有显式主键第一个非空唯一索引会被当作聚簇索引使用。这在有些场景下会带来隐藏问题。如果你的唯一索引列是无序字符串比如身份证号它被当成聚簇索引后插入时会产生大量的页分裂和碎片。所以即便业务上有唯一约束的列我还是建议单独建自增主键唯一约束用辅助唯一索引实现这样物理存储的连续性有保障。另外唯一索引在查询执行计划上和普通索引没有本质区别但写入时多一步唯一性检查。高并发写入场景下如果业务上能接受短暂的不一致可以考虑用普通索引替代唯一索引来提升写入性能。不过这个取舍需要结合具体业务不能拍脑袋。6. 索引选择的代价与常见问题排查6.1 索引不是越多越好索引数量这个话题在生产环境里特别容易出问题。很多开发同学的做法是来了一个新查询就加一个索引半年后一张表上挂了十几个索引。查询确实快了但写入变慢、磁盘占用变大、缓冲池命中率下降最终引发更大的性能问题。索引的代价要从三个维度看。第一是写入代价每插入一条记录所有索引都要更新。假设一张表有5个索引插入一条记录等于维护5棵B树的插入操作如果赶上页分裂这个开销会进一步放大。第二是存储代价辅助索引的叶子节点存索引列和主键5个索引就是5份冗余数据。第三是优化器代价索引多了以后MySQL优化器在选择执行计划时要评估更多候选路径评估成本也会上升。更关键的是优化器的选择有时候并不总是最优的。如果统计信息不准确或者数据分布出现倾斜优化器可能会选到一条很差的路径。我们就遇到过一张表明明有索引但执行计划里选了全表扫描的情况排查到最后发现是优化器基于过时的统计信息做了错误判断。所以索引设计要遵循一个原则用最少的索引覆盖最多的查询模式。一张表里高频的查询如果有三四种尽量让这些查询都能共用一到两个复合索引而不是每种查询各建一个。这是索引设计中最容易被忽视的取舍。6.2 常见问题速查与排查套路我把平时排查索引问题时最常用的几个动作整理成清单直接照着做就行。第一先看执行计划。执行EXPLAIN SELECT ...关注type、key、rows、Extra四列。type从好到坏依次是system、const、eq_ref、ref、range、index、ALL。如果type是ALL说明没走索引如果是index说明走了索引但扫的是全索引Extra中出现Using filesort或Using temporary说明排序或分组没有利用到索引。第二确认索引是否真的被用上。有时候key列显示用了索引但rows依然很大说明索引选择性差。这时应该检查索引列是否有区分度或者是不是查询条件本身写得不合理。第三复核索引列有没有被函数、运算、类型转换污染。这是最常见的失效原因。排查方法很直接把WHERE条件里的函数去掉或者改成对常量做运算比如WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY)而不是WHERE DATE(create_time) CURDATE()。第四分析是慢在回表还是慢在扫描。如果explain的Extra显示Using index condition说明走了索引下推但还有回表如果Extra显示Using index说明覆盖索引。线上查询追求的理想状态是Using index。第五统计信息不准的问题。MySQL 8.0里可以手动执行ANALYZE TABLE来更新统计信息或者考虑调大innodb_stats_persistent_sample_pages参数。这一招在处理优化器选错索引时非常有效。6.3 一张SQL的索引优化前后对比最后放一个完整的优化案例把上面的思路串起来。假设有一张商品订单表数据量约2000万行原SQLSELECT order_id, buyer_name, amount, create_time FROM trade_order WHERE status 1 AND buyer_id 9527 AND create_time 2024-06-01 ORDER BY create_time DESC LIMIT 50;原索引是idx_status_create_time (status, create_time)。执行计划的type是refrows约80万行查询耗时3.2秒。问题很明显status区分度低它的选择性只有几个值索引第一列几乎起不到过滤作用。我把索引改成idx_buyer_create (buyer_id, status, create_time)buyer_id区分度很高等值匹配后直接定位到几百行create_time再用来排序。改完以后执行计划type为refrows显示496查询耗时降到0.08秒。这个案例在之前的线上排查中也遇到过类似的场景区别只在于表名不同。这里的经验是高区分度列做索引的前导列永远优先于看起来业务上更重要的列。等你把Explain用熟练了你会发现大部分慢查询的根源就是索引列顺序设计得有问题。7. 我的一些实操体会索引优化的水很深但核心逻辑并不复杂理解B树的排序规则顺着它的性子设计索引SQL写法上不去破坏索引的有序性。我建议你在自己的测试库里面多练几次Explain把一个复合索引拆成不同顺序观察rows的变化这种感觉会比看多少篇文章都来得实在。另外线上环境加索引前最好在低峰期操作用pt-online-schema-change这类工具做在线变更避免大表锁表时间过长。这一步在真正处理生产环境问题时能帮你避开很多不必要的麻烦。