慢查询又双叒叕报警了。研发同事甩来一条SQL说“数据量才一百万加了索引还是慢”。我一看执行计划全表扫描索引压根没用上。像这样的场景在过去几年里我至少处理过几十次。其实很多问题都不是“要不要建索引”的问题而是“索引到底是怎么工作的、建了什么索引、为什么没走索引”这三个环节里有一个掉了链子。MySQL索引这个话题网上资料一大把但大部分讲得要么太浅只会教你怎么CREATE INDEX要么太深上来就甩一堆源码。我试着用这篇文章把索引的底层原理、常见类型、为什么失效、如何优化一次性讲透用实际案例和经验踩坑记录来串起来。这篇文章适合刚接触数据库的同学建立完整认知也适合工作两三年的开发拿来校验自己之前的索引理解有没有盲区。1. 索引本质与存储结构为什么偏偏是B树先说一个被问烂了但在面试和实战中都极其关键的问题为什么MySQL的InnoDB引擎选择了B树而不是哈希表、不是红黑树、不是普通的B树。这个问题的答案直接决定了你在建索引时做的每一个决策对不对。1.1 从磁盘IO的角度理解索引设计索引存在的意义只有一个减少磁盘IO次数。数据库的数据最终是存在磁盘上的而磁盘随机读的速度比内存慢几个数量级。机械硬盘一次随机IO大概要10ms左右SSD会快很多但依然和内存有数量级的差距。所以索引设计的第一原则是尽量用更少的磁盘IO找到目标数据。那怎么减少IO次数关键在于每个节点能存储更多的数据、树的高度足够矮。InnoDB默认一个数据页是16KBB树的每个节点对应一个数据页。假设一个索引键是8字节加上指向子节点的指针大概6字节那一个节点大约能存16KB/14Byte ≈ 1170个键值。如果这棵树有三层能存储的数据量大约是1170 × 1170 × 16KB这个数字算下来是几十亿行。换句话说在绝大多数业务场景下我们查一条数据只需要3次磁盘IO第一次读根节点第二次读中间层节点第三次读叶子节点。这个思路很重要。很多人一开始接触索引就钻进“平衡二叉树调整”的细节里出不来但数据库的索引设计首先要服从磁盘物理特性。树矮了层数就少IO次数少查询就快。这也解释了为什么红黑树不行红黑树是二叉树同样存一百万条数据树的高度大约是20层意味着最坏情况下要20次磁盘IO才能定位到叶子节点而B树最多三四层。20次IO和3次IO的差距在高并发场景下就是灾难。1.2 B树和B树的区别到底在哪B树和B树长得很像但有几个关键差别。最核心的一点是B树的每个节点都存储数据而B树只有叶子节点存储数据非叶子节点只存索引键和指针。这带来两个巨大优势第一B树的非叶子节点能存储更多的键同样高度的树B树的容量远大于B树换句话说树更矮、IO更少。第二B树的所有数据都在叶子节点而叶子节点之间通过链表连接。这个链表结构让范围查询变成了一条线性扫描查到下限之后顺着链表往右走就行不需要回溯到父节点再下来。B树要做范围查询就很麻烦得在中序遍历中反复上下跳每次跳都是一次磁盘IO。还有一个容易被忽略的点B树的数据存储顺序是物理有序的。同一层的叶子节点是通过双向链表连接的而且页内的数据也是按索引键排序存放的。这个有序性决定了索引可以同时服务于等值查询、范围查询和排序场景——ORDER BY、GROUP BY、去重都可以直接利用索引的有序性而不需要额外的filesort操作。这块内容在后面讲联合索引的时候还会再展开。1.3 数据页与局部性原理聊到存储结构不得不提数据页和“预读”机制。InnoDB读取数据的最小单位不是行而是页。当你需要某一行数据时InnoDB会把整个页默认16KB从磁盘加载到内存缓冲池里。由于数据在页内的存储是物理连续的一个页里可能有多条目标数据或相邻的索引键加载一次就能覆盖多条需求这就是局部性原理。理解页的概念对索引优化很有帮助。比如为什么建议索引列用自增主键而不是随机UUID因为InnoDB聚簇索引的叶子节点就是整行数据而且按主键顺序物理排列。自增主键导致新插入的行总是追加在末尾只需要在最新的页上做写入几乎不会触发页分裂。而UUID无序插入会让数据随机落在之前的页中间页分裂频繁碎片化严重写入性能和不经过索引的随机IO都会大幅上升。这个我在后面的聚簇索引部分会详细讲。2. 聚簇索引与非聚簇索引决定存储形态的关键不少开发者在建表时对主键的选择很随意觉得“只要有个主键就行”。但主键在InnoDB里远不止唯一约束那么简单它决定了整张表的数据物理存储方式。这一块是理解MySQL索引性能的基石值得掰开揉碎讲清楚。2.1 InnoDB的聚簇索引模型InnoDB的表数据本身就是按主键组织成一棵B树的这棵树叫聚簇索引。聚簇索引的叶子节点直接存储完整的行记录也就是说表数据和索引数据是一体的。换句话说InnoDB表本质上就是一棵以主键为索引键的B树不存在“表数据和索引分开存”这种说法。为什么这么设计因为聚簇索引让主键查询极快——直接通过B树定位到叶子节点就能拿到整行数据不需要额外跳转。同时由于叶子节点按主键有序范围查询比如主键BETWEEN 100 AND 200就是一次顺序扫描效率非常高。但聚簇索引有一个不可避免的副产品非主键索引二级索引的叶子节点不存储整行数据存储的是主键值。这就引出了“回表”的概念。比如你在一张表上针对name字段建了一个普通索引那么执行SELECT * FROM t WHERE name 张三时MySQL会先在name索引的B树上找到对应的主键值再拿着主键值去聚簇索引上再查一次才能拿到完整行数据。这个过程就是回表。回表不是免费的每回一次表都要走一次B树查找。如果命中的行数很多可能还不如直接全表扫描。所以在很多优化场景里我们希望避免回表这就是后面要讲的覆盖索引发挥作用的地方。2.2 MyISAM的非聚簇索引形态有必要把MyISAM也拉出来对比一下因为很多老项目还在用这个引擎理解它才能明白为什么某些表那么慢。MyISAM的索引和数据是分开存储的索引B树的叶子节点不存数据存的是数据行的物理地址行指针。无论主键索引还是普通索引叶子节点存的都是这个行指针。所以MyISAM的“主键索引”和“二级索引”在结构上没有本质区别性能差异主要来自键值长度的不同。这种存储形态的好处是索引结构简单独立单表查询没有回表的概念因为所有索引叶子节点都是行指针但坏处是数据文件与索引文件分开数据的物理插入顺序与索引顺序没有强绑定关系如果写入频繁且主键无序数据文件会产生较多的随机IO与碎片。现在主流使用中MyISAM已经非常少见了但理解两种引擎的区别能帮你在被问到“为什么这个表查得这么慢”时多一个排查方向。我记得有一次排查一个老系统的慢查询表是MyISAM查询条件不是主键而是普通字段但索引明明存在EXPLAIN也显示用了索引——慢就慢在每次查询都根据行指针做了大量随机IO。换到InnoDB相同表结构和数据量后查询时间直接降了一个数量级。当然这只是个案不能直接归因于引擎差异但确实值得排查。2.3 为什么建表时主键要选自增ID这个问题值得单独提出来因为它直接影响聚簇索引的写入性能和数据页的利用率。前面说过InnoDB聚簇索引的叶子节点按主键值有序排列。如果主键是自增整数新纪录总是落在B树最右侧的页上这就是所谓的“顺序写”。反过来如果用UUID或者业务随机字符串作为主键每次插入的主键值是随机的。新纪录可能落在任何一个页的中间位置如果该页已满InnoDB必须执行页分裂把一半数据移到新页重新调整B树指针。页分裂除了本身的开销还会留下物理碎片后续扫描和范围查询的性能都会受影响。更麻烦的是UUID主键让聚簇索引的叶子节点存储顺序与数据实际插入顺序完全脱节频繁的分裂和随机写入会让缓冲池的效率显著下降。注意这不是说业务表不能用UUID作为主键而是说如果用了UUID尽量别让它成为InnoDB聚簇索引的键。可以保留UUID作为业务标识字段另设一个自增ID作为主键这样业务层依然可以用UUID做关联查询但物理存储上保持了顺序写。当然如果你用的是分布式ID方案比如雪花ID它本身是趋势递增的作为主键对聚簇索引的写影响远小于完全随机的UUID很多项目也直接用雪花ID做主键前提是生成的ID单调性足够好。3. 索引类型与使用场景从单列索引到联合索引MySQL官方文档把索引分成几大类主键索引、唯一索引、普通索引、全文索引、空间索引以及从访问方法角度看的哈希索引。很多人在建索引时对着表一顿操作却不清楚每种索引的应用边界。这一节我会按照实际业务里最常见的场景来逐个拆解。3.1 主键、唯一索引与普通索引的取舍主键索引不用多说定义主键时自动创建一张表只有一个。唯一索引和普通索引的差别在于是否允许重复值但它们的物理结构完全一样都是B树二级索引。唯一索引除了约束数据唯一性之外还有一个效果查询后最多返回一条记录MySQL在找到匹配项后可以立即停止搜索而不是继续扫描到边界。普通索引允许重复值查询可能返回多条记录MySQL需要扫描满足条件的区间直到遇到第一个不匹配的键值为止。我的建议是凡是业务上有唯一性要求的字段比如手机号、身份证号、订单号、设备序列号直接建唯一索引不要建普通索引再加应用层判断。因为唯一索引把“查重”的代价前置到了数据库层面在高并发插入场景下能避免应用层的竞态条件。但同时要注意唯一索引在插入时会做唯一性检查如果插入冲突频繁性能会有一定拖累这在批量导入时尤其明显。3.2 联合索引与列顺序的艺术联合索引复合索引是MySQL索引优化的核心武器。它是指在一张表的多个列上建立一个索引索引键按列的顺序排列。比如INDEX idx_a_b_c (a, b, c)这个索引实际上是按a排序a相同再按b排序b相同再按c排序的。联合索引为什么重要因为单列索引在一次查询中最多只能用一个这个观点在MySQL 8.0的Index Merge功能产生后有了变化但大多数场景仍然成立。如果你对table的a和b分别建了两个单列索引查询WHERE a 1 AND b 2MySQL大概率只能选择其中一个索引再对回表结果做过滤而不是同时利用两个索引做交集。联合索引则不同它一上来就按(a, b)两个维度排序查询可以直接定位到最精确的区间。联合索引的列顺序是使用中最容易出错的点。基本原则是把最常用于等值查询的列放在最前面把用于范围查询的列放在后面。原因在于B树的排序逻辑先按第一列排再按第二列排。如果第一列用了范围查询那么第二列的排序条件就派不上用场了——因为第一列范围内第二列的排序并不是全局有序的MySQL只能从满足第一列范围的所有记录中过滤第二列无法利用索引直接跳过不匹配数据。这个现象叫“索引中间断裂”。举个例子INDEX idx_shop_time (shop_id, create_time)查询WHERE shop_id 100 AND create_time 2024-01-01能完美命中索引因为shop_id等值定位之后create_time在shop_id100这个分组内是有序的。但如果你把顺序反过来建INDEX idx_time_shop (create_time, shop_id)同样这条SQLcreate_time先做了范围判断shop_id的索引有序性就发挥不出来了只能逐条过滤。3.3 覆盖索引让查询根本不用回表覆盖索引是实战中最值得刻意追求的优化手段。所谓覆盖索引指的是查询所需要读取的所有列都在同一个索引中MySQL可以直接从索引B树里拿到全部数据完全不用回表。举例SELECT name, age FROM t WHERE name 张三如果t表上有INDEX idx_name_age (name, age)那么MySQL在二级索引中就能直接拿到name和age的值不需要再根据主键回表。如果索引只含name就得回表拿到age字段多一次IO。覆盖索引带来的性能提升非常明显尤其对高并发、高频的查询场景。我的习惯是对于核心业务里的高频查询如果查询字段不多会考虑把查询字段全部塞进索引让查询变成纯粹的索引扫描。简单说就是“以空间换时间”多存几个冗余字段在索引里换取每次查询少一次回表。但覆盖索引不是越宽越好。索引列太多会导致B树的节点变大、单页存放键减少、树变高、写入变慢。覆盖索引还是要克制一般建议控制在三到四个字段以内优先覆盖查询频率最高的组合。3.4 全文索引与哈希索引的适用边界MySQL的全文索引FULLTEXT用于全文检索场景比如长文本字段的模糊匹配但它在中文场景下的表现一般。对于英文分词MySQL默认支持空格和常用分隔符分词中文则需要配置合适的分词器或者依赖第三方方案。实话实说业务上如果对中文搜索有明确需求我还是建议直接用专门的搜索引擎MySQL全文索引适用于少量文本、简单场景的补充。哈希索引在InnoDB中并不是一种“显式创建”的索引类型它是基于内存的适配器运行在自适应哈希索引Adaptive Hash Index机制下。当InnoDB检测到某些等值查询频繁命中时会自动在B树索引之上构建哈希索引来加速。这个过程对DBA和开发者是透明的你无法手动指定只能通过配置参数innodb_adaptive_hash_index开关。为什么InnoDB不直接把哈希索引作为默认索引因为哈希索引不支持范围查询、不支持部分键匹配、不支持排序而且哈希冲突处理会带来额外开销。B树则天然支持等值、范围、排序、前缀匹配等绝大多数SQL操作通用性最强。哈希结构更适合Redis这类内存缓存场景不适合磁盘型数据库做通用存储。4. 最左前缀原则与索引失效实战中踩过最多的坑这一节是全文的精华所在。很多开发同学建索引时想得很美但SQL一写出来索引就是走不上。原因大多出在几个固定的模式上。我逐个讲清楚并且给出能落地的规避思路。4.1 最左前缀原则到底是什么最左前缀原则是MySQL联合索引使用的基本规则查询必须从联合索引的最左列开始匹配才能充分利用索引。比如INDEX idx_a_b_c (a, b, c)能使用该索引的查询条件组合包括a、ab、abc而b单独或bc组合是无法走上索引的。原因还是B树的排序逻辑。索引按(a, b, c)排序后单独看b或c的列它们在全局范围内并不是有序的。既然不是有序的MySQL就无法用二分查找来定位只能做全索引扫描或者全表扫描。这个道理和“电话簿按姓氏笔画排序你只记了名字不知道姓氏就没法快速找到人”是一样的。但这里有一个细节值得注意最左前缀不仅适用于“从左开始的完整列”也适用于“从左开始的单列前缀”。也就是说WHERE a 1、WHERE a 1 AND b 2可以走索引WHERE a LIKE 张%也可以走索引因为LIKE 张%本质上还是一个前缀匹配索引按a排序后“张”开头的记录天然聚在一起。4.2 索引失效的六大高频场景综合这些年的排查经验索引失效的场景可以归纳成六类第一类是查询条件里对索引列做了函数运算或表达式运算。比如WHERE DATE(create_time) 2024-01-01MySQL无法直接对create_time做二分查找因为索引里存的键值不是DATE(create_time)的结果。解决办法是把表达式改写成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。第二类是隐式类型转换。比如字段phone是VARCHAR类型查询写成WHERE phone 13812345678MySQL会把字符串字段转成数字再比较等于在索引列上做了隐式函数索引失效。这个场景在代码里非常隐蔽从ENGINE日志里很难一眼看出来必须对着表结构逐个核对。第三类是LIKE的左模糊匹配。WHERE name LIKE %张%无法走索引原因同样是索引键是按顺序排列的以%开头的匹配无法利用有序性。这种需求理论上可以用全文索引但业务上如果只是百分比的模糊查询分页全表扫描往往是性价比最高的方案。第四类是联合索引中间列使用了范围查询。前面已经说过WHERE a 1 AND b 100 AND c 2c的索引部分无法利用。因为b的范围查询让后面c的有序性断裂了。第五类是查询条件使用了OR连接。当WHERE a 1 OR b 2如果a和b分别有索引理论上有些版本的MySQL可以用Index Merge做交集但大部分情况下优化器会选择全表扫描因为无法确定两边的数据是否有重叠。如果你确信a和b互斥可以拆成两条SQL用UNION ALL拼接但这属于比较“摧残可读性”的优化建议先实测再做。第六类是对索引列做了隐式字符集转换或者使用了NOT IN、NOT EXISTS这类否定判断。NOT IN为什么难走索引因为优化器通常认为否定条件的过滤度难估计且需要访问大量索引节点最终会被优化成全索引扫描或全表扫描。这不是绝对的取决于数据分布和优化器版本但实战中确实很少看到NOT IN走索引。4.3 通过EXPLAIN做一次索引使用体检排查一条SQL是否走了索引、走了哪个索引、有没有回表最直接的办法就是看EXPLAIN的输出。这里我列出几个必须关注的字段和它们的含义。type字段是访问类型从好到坏依次是system、const、eq_ref、ref、range、index、ALL。ALL就是全表扫描——如果你的核心高频查询出现ALL基本可以断定索引没建对或者SQL写法有问题。index稍微好一点表示全索引扫描但如果没有过滤条件依然可能是低效的。key字段显示实际使用的索引名如果为NULL说明当前查询没有命中任何索引。rows字段是估算的扫描行数这个值越小越好。Extra字段最值得细看如果出现Using filesort说明排序没有用上索引可以在合适的位置响索引设计来消除如果出现Using temporary说明需要临时表通常会带来严重性能消耗如果出现Using index condition说明用了索引条件下推ICP把部分过滤条件下推到存储引擎层处理减少了回表次数。举个实际案例。某公司一张订单表有三百万行慢SQL是SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND pay_time 2024-03-01 ORDER BY amount DESC LIMIT 20;EXPLAIN的结果是typeALL、rows3000000、ExtraUsing filesort。这意味着每次执行都全表扫描三百万行还会在内存或磁盘上做排序。后来我建议建了联合索引INDEX idx_user_pay_amount (user_id, pay_time, amount)。三列分别覆盖了等值条件、范围条件、排序和查询字段。再跑EXPLAINtyperange、rows50、Extra已经是纯索引扫描查询从秒级别降到了毫秒级别。这个案例想说明的是指数失效的问题不只是“建个索引”这么简单而是要把SQL里的每个条件拆开看它分别属于等值、范围、排序、还是覆盖再按最左前缀原则组合成一个联合索引。少一列或多一列效果可能天差地别。5. 索引优化策略与实际调优案例我把这一章放在最后因为它的内容建立在前文的基础上。如果你前面的理解到位了这一章主要是方法论和实战手感的补充。5.1 索引设计的基本方法论面对一张业务表的索引设计我一般按以下步骤来梳理第一步列出所有高频查询SQL。不只看单条SQL还要看整体查询模式。区分哪些是核心链路、哪些是后台报表、哪些只是低频查询。索引对写入有负面影响所以优先保证高频查询的读取性能。第二步对每条SQL分类拆解出等值条件、范围条件、排序字段、分组字段、覆盖需求。等值条件排最前范围条件次之排序字段跟着放需要覆盖的字段放在最后。第三步考虑重复索引和冗余索引。INDEX (a)和INDEX (a, b)中第一个就是冗余的因为联合索引天然覆盖了单列索引的场景。删除冗余索引可以大幅减少写入开销和存储占用。第四步评估区分度。索引列的选择性越高索引越有效。比如性别字段只有两个值区分度极低建索引意义不大手机号、订单号这类几乎每个值都不一样的字段索引效果就很好。第五步对核心SQL做EXPLAIN验证并持续观察线上慢查询日志。索引规划从来不是一次性的业务查询模式变化、数据量增长都可能导致原来的索引方案失效。5.2 索引下推、MRR与优化器选项了解MySQL的一些高级优化机制有助于理解为什么某些SQL的执行计划很奇怪。索引条件下推ICP是MySQL 5.6引入的特性。它允许MySQL在使用索引查询时将在存储引擎层就能直接过滤掉的记录提前处理减少回表次数。举个例子INDEX (zipcode, lastname)查询WHERE zipcode 100000 AND lastname LIKE %张%。在没有ICP时MySQL通过zipcode定位到一批主键全部回表后再过滤lastname有了ICP后lastname的过滤直接被推到了存储引擎的索引读取层只有最终匹配的记录才会回表。这个优化对二级索引的过滤很有效EXPLAIN中表现为Using index condition。MRRMulti-Range Read是另一个优化机制它在二级索引扫描后再批量回表时先将主键排序再进行有序主键访问。这样做的好处是把随机回表IO变成顺序IO显著提升回表效率。但在实际调优中我并不建议普通开发者去手动SET优化器开关。MySQL的优化器在绝大多数情况下会自己做出正确的选择。手动干预的前提是你对数据分布和执行计划有充分的把握否则容易适得其反。5.3 一个完整的索引治理复盘最后分享一个我印象很深的实际案例。某系统有个payment_records表数据量将近五千万。最初建表时研发凭着直觉给几个常用列分别建了单列索引。结果发现三个问题慢查询居高不下、写入延迟偏高、磁盘占用明显超过预期。我把线上慢日志拉出来看发现高频查询主要是两类一是按merchant_id加create_time的范围查询做分页二是按order_no做等值查询求出账单。原表上有idx_merchant_id、idx_create_time、idx_order_no三个单列索引其中idx_merchant_id和idx_create_time在组合查询时完全派不上用场因为优化器只能选一个索引用。治理方案是这样的把idx_merchant_id和idx_create_time合并成联合索引idx_merchant_time(merchant_id, create_time)把order_no的索引改成唯一索引uniq_order_no(order_no)同时删掉原有的两个冗余单列索引。改造之后第一条高频查询从单靠一个索引过滤变成联合索引精确命中分页查询不再触发大面积回表第二条高频查询因为order_no的唯一性等值查询直接const访问延迟稳定在1ms以内。写入方面由于索引从三个变成了两个索引维护次数下降写入性能也有改善。注意在删除索引之前务必确认线上没有其他查询依赖被你删除的索引。我一般的做法是先在测试环境把表结构和索引改成目标状态跑一遍全量慢SQL回归观察EXPLAIN没有出现ALL扫描和明显的filesort再在一个业务低峰期灰度切换观察一段时间慢查询曲线后再彻底清理旧索引。6. 索引维护与长期观察索引建完不是终点。随着数据持续写入页分裂、碎片化、索引统计信息过期这些问题都会显现。这里给出几个日常维护的实操建议。6.1 碎片整理与统计信息更新InnoDB表在频繁的写入和删除之后会产生页碎片。碎片会让索引的叶子页存放大量未使用的空洞导致扫描的行数大于实际行数查询变慢。可以用OPTIMIZE TABLE来重建表并整理碎片注意这个操作会锁表必须在业务低峰期执行。MySQL会定期自动更新索引统计信息但有时候统计信息不够精确会导致优化器选错索引。你可以用ANALYZE TABLE强制更新统计信息再查看后续执行计划是否发生了变化。这条命令的代价比OPTIMIZE TABLE小得多可以更频繁地执行。6.2 利用慢查询日志主动发现问题慢查询日志是索引治理中最重要的工具之一。建议开启slow_query_log并把long_query_time设置到0.5秒甚至更低取决于业务对延迟的容忍度定期分析慢查询日志。很多索引问题在初期只是“稍微慢一点”等到数据量涨上去就变成“完全不可用”。现在很多团队使用可视化工具来收集慢日志但不管用什么工具核心的排查思路都是看三样东西SQL文本、执行计划、表索引结构。这三样对齐了绝大多数索引问题都能定位。6.3 冗余索引排查SQL我最后提供一个排查冗余索引的SQL思路帮助你在自己的环境里快速找到重复的索引组合。SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS idx_columns FROM information_schema.statistics GROUP BY table_name, index_name;通过这条语句把每张表的索引列组合列出来再人工比对哪些索引的列集合是另一个索引的前缀子集。比如你有(a, b, c)和(a, b)后者就是冗余的可以安全删除。我个人在实际操作中的体会是索引优化是最典型的“前期一点思考、后期百倍回报”的工作。一个精心设计的联合索引能让核心查询从秒级降到毫秒级一个失误的冗余索引也能让写入越来越慢。多看执行计划、多维护慢日志、定期做索引治理复盘慢慢你就会发现大部分所谓“数据库慢”或“SQL性能问题”根源都在索引设计这一个点上。