这些年排查线上MySQL慢查询十个里有八个问题出在索引上。索引这东西背概念容易真正遇到实际问题却经常抓瞎明明建了索引SQL还是慢明明用了主键却走了全表扫描明明是同一个SQL换个环境效果天差地别。所以我想把InnoDB索引这件事从头到尾捋一遍从底层数据结构到性能实战讲清楚原理也分享一些实际排查时的经验。这篇内容适合刚接触MySQL的开发者入门也适合被慢查询折磨过的同学对照排查全文不扯虚的尽量把每一步为什么这么做说明白。1. 从一条慢查询说起InnoDB索引到底是个什么结构先看一个真实场景订单表order_info里有500万行数据查询语句很简单SELECT * FROM order_info WHERE order_no NO202412010001;这条SQL跑了1.8秒。业务方不能接受因为接口要求300毫秒以内。我第一反应就是看index字段果不其然order_no连索引都没有全表扫了500万行。这种问题看似简单但很多人只记得“加索引”却说不清InnoDB里的索引究竟是什么、为什么能快、什么时候会失效。所以先放下SQL把底层结构搞明白。1.1 B树长什么样为什么MySQL选了它InnoDB索引的物理结构是B树。注意不是二叉树不是B树是B树。B树的特点是数据只存在叶子节点非叶子节点只存索引键值叶子节点之间通过双向链表连接根节点到每个叶子节点的路径高度相同。为什么MySQL选择B树而不是B树或红黑树核心原因有两个磁盘IO和范围查询。先讲磁盘IO。InnoDB以页为最小存储单位默认16KB。一次IO会把整个页读入内存。B树的非叶子节点可以容纳大量索引键比如一个8字节的bigint加上指针一个16KB的页大概能容纳上千个键。假设一条记录占1KB一个叶子页存16条三层的B树能存约1600万条记录。也就是说查询一张千万级表最多三次磁盘IO就能定位到数据。换成红黑树树高可以达到二十多层磁盘IO次数会多出很多完全没法用于海量存储。再说范围查询。B树的叶子节点用双向链表串起来一旦命中某个区间起点沿着链表顺序扫描就能拿到所有符合条件的记录。如果是B树叶子节点和非叶子节点都存数据范围查询需要多次回溯父节点效率差很多。简单类比B树像一本新华字典非叶子节点是目录页叶子节点是正文。查一个字先翻目录定位到页再翻到具体页码。而B树相当于每个目录项后面都贴了一段正文不仅字典更厚而且想看连续内容得反复在目录和正文之间跳来跳去。1.2 聚簇索引和二级索引一张表其实有两套地图InnoDB的索引分两类聚簇索引和二级索引。很多人以为主键索引就是“给主键建了个索引”其实在InnoDB里聚簇索引就是表本身。聚簇索引的叶子节点存放整行记录。它就是按照主键建立的B树主键顺序就是表数据的物理存储顺序。我们常说MySQL的InnoDB表是索引组织表就是这个意思。所有数据都挂在这棵树上。二级索引则不同无论是普通索引、唯一索引、联合索引都算二级索引。它的叶子节点不存整行记录只存索引列的值和主键值。比如在order_no列上建一个普通索引那这棵B树的叶子节点就是“order_no 主键id”。这意味着通过二级索引查数据至少要经历两次查找第一步先从二级索引B树中根据索引键找到对应的主键第二步再拿着主键去聚簇索引的B树中找到整行记录。第二步就是传说中的“回表”。举个例子SELECT * FROM order_info WHERE order_no NO202412010001;如果order_no有索引第一步在二级索引里找到主键id 10086第二步再通过id去聚簇索引中取整行。如果id也是按顺序密集排列的回表其实很快。但如果SQL只查询order_no和id两个字段呢直接走二级索引就能拿到所有要返回的数据连回表都省了这就是覆盖索引。这个概念后面再说。聚簇索引和二级索引的差异用一个表格总结对比项聚簇索引二级索引叶子节点内容整行记录索引列值 主键值唯一性每张表有且仅有一个聚簇索引一张表可以有多个二级索引索引组织方式按主键顺序存储数据按索引列顺序存储是否导致回表不需要可能触发回表所以一张表默认一定有一个聚簇索引。如果没有显式定义主键InnoDB会选择一个非空唯一索引作为聚簇索引如果连唯一索引都没有InnoDB会生成一个隐藏的rowid作为主键。这也是为什么我强烈建议每个业务表都显式定义主键否则你连“通过主键快速定位”的优势都用不上还会面临隐藏主键带来的不可控问题。2. 索引设计的第一课主键、复合索引和那些容易踩的坑了解B树结构之后索引设计就有据可依了。因为聚簇索引的叶子节点存的是整行数据主键的大小和顺序会直接影响所有二级索引的大小也影响插入性能。这一节是我在数据库设计评审时反复强调的三个点。2.1 主键选不选自增这是个问题很多同学建表时随手就写id bigint auto_increment primary key这很合理。但面试或实际工作中总会遇到用UUID、雪花ID、业务订单号等作为主键的情况。到底怎么选从聚簇索引的结构来看主键排序决定了数据页的物理写入顺序。自增主键是严格递增的新插入的行总是追加在B树的末尾。这样有两个好处一是数据页不需要频繁分裂写性能稳定二是二级索引叶子节点里的主键更紧凑占用的空间最小。如果主键是UUID由于UUID随机无序新插入的主键值会落在B树中间或前面某个位置InnoDB需要不断移动已有记录、分裂页面才能让新记录放到正确位置。数据量小的时候感觉不出来数据量过千万后会明显影响写入性能还会产生大量索引碎片。但自增主键也有缺点暴露业务数据量和增长趋势在分布式场景下多个库并发生成自增id容易冲突。所以现在很多系统用雪花ID这类有序的分布式ID作为主键。注意雪花ID虽然比UUID长一些但它是趋势递增的插入顺序基本有序对InnoDB的友好程度接近自增主键。我的建议是单库单表系统优先自增主键简单可靠。分库分表或分布式系统使用雪花ID、号段模式等趋势递增ID避免UUID。实在要用UUID建议存储为二进制格式16字节而非36字节字符串能省不少索引空间。2.2 复合索引的最左前缀和排序优化复合索引联合索引是日常开发中频率很高的索引类型。很多人以为“给多个列都建了索引就等于有了复合索引”这是错的多个单列索引和复合索引执行计划完全不同。复合索引遵守最左前缀原则。什么叫最左前缀比如在(user_id, status, create_time)上建一个复合索引那么以下查询能用到这个索引WHERE user_id ?WHERE user_id ? AND status ?WHERE user_id ? AND status ? AND create_time ?WHERE user_id ? AND create_time ?注意这里只用到了user_idcreate_time上的过滤是索引内部先按user_id匹配后从返回的记录再用or? 实际上因为跳过了statuscreate_time不能利用索引的有序性做精确或范围过滤但仍能通过索引下推提高性能。以下查询用不到最左列就完全无法使用该复合索引WHERE status ?WHERE create_time ?另外复合索引还有一个隐藏能力用于排序。ORDER BY status, create_time如果和复合索引的列顺序完全一致并且都是升序MySQL可以直接利用索引顺序返回数据避免filesort。如果排序方向不一致比如一个升序一个降序在MySQL 8.0之前也可能导致无法利用索引顺序但8.0支持降序索引后会好很多。所以设计复合索引时要结合查询条件。高频查询的等值条件放最左边范围条件放后面把排序字段也考虑进去。一个常见的反例是查询条件WHERE user_id ? ORDER BY create_time DESC却只建了(create_time, user_id)索引。这种索引在user_id过滤后create_time可能不是全局有序MySQL只能额外sort。如果换成(user_id, create_time)既能过滤又能排序性能高一个量级。3. 性能实战前先学会看执行计划Explain到底在说什么刚开始接触索引优化时我也犯过“盲猜”的毛病。后来发现所有索引优化问题第一步都是EXPLAIN。MySQL的执行计划就像体检报告每一项指标都有具体含义能告诉你SQL到底是怎么执行的。3.1 Explain字段逐个看一条最简单的命令EXPLAIN SELECT * FROM order_info WHERE order_no NO202412010001;关键字段有这几个id查询中每个SELECT子句的标识如果id相同说明是从上到下顺序执行的如果不同数值大的先执行。select_type查询类型常见SIMPLE、PRIMARY、SUBQUERY、DERIVED等表示是简单查询还是子查询。table正在访问哪张表。type访问类型这是最重要的字段之一。从好到坏依次是system const eq_ref ref range index ALL。possible_keys优化器认为可能用到的索引。key实际使用的索引。key_len索引键的长度字节数。rows预估扫描多少行。Extra额外信息包含Using index、Using where、Using filesort等对判断优化效果非常关键。其中type字段可以单独写一篇文章。简单说type含义典型场景const按主键或唯一索引等值查询最多返回一行主键id查询eq_ref多表join中被驱动表使用唯一索引等值关联join连接ref使用普通索引等值查询普通索引where条件range使用索引做范围查询大于、小于、between、inindex扫描了整棵索引树覆盖索引但条件无法过滤或索引列出现在select中ALL全表扫描没有可用索引如果看到ALL基本就说明这条SQL大概率有问题需要重点优化。3.2 哪些写法会让索引静悄悄失效即使你已经建了索引也不代表优化器一定用得上。下面几个场景是我在实践中踩得最多的第一对索引列使用函数。比如WHERE DATE(create_time) 2024-01-01如果create_time有索引这个写法会失效因为MySQL必须先对每一行的create_time进行函数计算才能和常量比较索引无法直接定位。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。第二隐式类型转换。比如手机号字段是varchar类型你的SQL是WHERE phone 13800138000数字常量会被转换成字符串再进行匹配索引同样可能失效。或者反过来WHERE字符串字段 数字也可能出问题。所以字段类型和查询常量要保持一致。第三前导通配符。LIKE %abc%一定用不到索引因为字符串无法从中间开始匹配排序位置。但LIKE abc%是可以走索引的因为B树是根据字符串前缀排序的。第四OR条件连接。假设a列有索引b列没有索引WHERE a 1 OR b 2优化器可能选择全表扫描因为OR需要同时满足两个条件只走a索引无法得到完整结果。应该把OR拆成两个查询union或者给b也加上索引。第五对索引列做运算。WHERE id 1 100本来主键等值很高效加上运算后索引失效。建议把运算移到等号的右侧变成WHERE id 99。遇到这些情况不要凭经验猜直接跑一次EXPLAIN看key字段就能确认索引是否真的生效。4. 回表、覆盖索引和索引下推性能优化三板斧明白二级索引要回表之后很多新手会产生一个误解既然二级索引总要多查一次干脆把所有查询都直接走主键。这当然不对。回表确实有成本但可以通过覆盖索引和索引下推这两个机制显著降低开销。4.1 什么是回表怎么避免回表回表是指通过二级索引得到主键后再到聚簇索引取整行数据。一次回表等于额外一次主键查询通常情况下性能开销不大。但如果查询需要回表的行很多比如SELECT * FROM order_info WHERE status 1返回10万行那么每行都回表就是10万次随机IO慢得吓人。避免回表最常见的方案是覆盖索引。所谓覆盖索引就是查询所需的字段全部包含在索引中查询计划直接通过索引即可返回不再需要聚簇索引。举个例子SELECT id, order_no FROM order_info WHERE order_no NO202412010001;如果order_no有普通索引那么该索引的叶子节点就是(order_no, id)而查询只需要这两个字段直接扫描索引树就能得到结果。通过EXPLAIN会看到Extra显示Using index这就是覆盖索引生效的标志。所以只select需要的字段是有实际性能意义的。很多同学习惯写SELECT *把所有列都捞回来覆盖索引就没戏了。4.2 索引下推把过滤压到索引层索引下推Index Condition PushdownICP是MySQL 5.6引入的优化。它适用于联合索引核心思想是在索引遍历过程中先把索引列上的过滤条件从服务层“下推”到存储引擎层提前过滤掉不满足条件的记录减少回表次数。场景是这样的假设有联合索引(user_id, status)查询SELECT * FROM order_info WHERE user_id 10086 AND status 1;没有ICP时存储引擎只用user_id去索引树上定位找到所有user_id10086的主键然后挨个回表拿数据到服务层再判断status1。如果有ICP存储引擎在扫描索引时直接把status1也作为条件过滤只对通过主键记录回表。在EXPLAIN里Extra输出Using index condition就代表启用了ICP。这个优化对二级索引查询非常友好能大幅减少随机IO。它不需要我们做任何配置MySQL默认开启。这里有个容易被误解的点ICP并不是覆盖索引它仍然可能回表只是回表次数减少了。理解这一点很关键优化方向完全不同。5. 实战一次订单表慢查询的优化全过程理论讲再多不如来一次完整的排查。下面这个例子是我在一个真实项目中遇到的做了脱敏处理但流程完全一致。5.1 现场还原SQL、表结构和执行计划表结构如下CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, pay_amount DECIMAL(10,2) DEFAULT 0.00, create_time DATETIME NOT NULL, KEY idx_status_create_time (status, create_time) ) ENGINEInnoDB;业务反馈说一个分页查询很慢SELECT id, order_no, user_id, create_time FROM order_info WHERE status 1 ORDER BY create_time DESC LIMIT 20;这个查询走了idx_status_create_time按照status1在索引树中定位因为索引叶子节点已经按create_time排序理论上找到后逆序取20条就行了。但实际执行计划显示Using filesortrows扫描量是几十万。为什么因为DESC排序MySQL 8.0之前的版本在复合索引(status ASC, create_time ASC)中无法直接利用索引做降序所以额外做了一次排序操作。再加上status1的记录非常多几十万条全部要进入排序缓冲区。还有一个隐藏问题查询返回了user_id字段而idx_status_create_time里并没有包含user_id所以每条满足条件的主键还需要回表拿user_id。即使只需要20条MySQL也无法提前知道哪些是要的必须先收集所有status1记录排序后再取20条。5.2 优化步骤加索引、改SQL、前后对比第一步调整索引结构让覆盖索引和排序方向都满足ALTER TABLE order_info ADD INDEX idx_status_ct_user(status, create_time, user_id);这里把查询涉及的三列都加进索引同时保持status 1等值过滤、create_time排序、user_id覆盖。第二步由于是DESC排序MySQL 8.0支持降序索引可以直接再把排序列提升到合理位置ALTER TABLE order_info ADD INDEX idx_status_desccreate(status, create_time DESC, user_id);但是在8.0之前我们也可以改变SQL写法比如把排序改成ORDER BY create_time ASC再在应用层倒序展示这样就能完全利用索引顺序。第三步查看优化后的执行计划EXPLAIN SELECT id, order_no, user_id, create_time FROM order_info WHERE status 1 ORDER BY create_time DESC LIMIT 20;此时type refkey idx_status_ct_userExtra Backward index scan如果用了降序索引或者Extra Using index如果彻底覆盖。扫描rows从几十万降到了几百甚至几十查询时间从原来的450ms降到了2ms。这个案例说明优化不是靠一蹴而就而是三步走先看表结构确定索引是否满足查询再做EXPLAIN找出瓶颈最后调整索引或SQL。其中ORDER BY和索引顺序的匹配比单纯加索引更需要细心。6. 索引维护重复、冗余与碎片整理索引不是越多越好也不是建完就一劳永逸。我见过一些表上挂了七八个联合索引实际使用频率很低写入时却都要更新反而拖累性能。索引维护是数据库长期稳定运行不可忽视的一项工作。6.1 重复和冗余索引的识别与清理重复索引指完全相同的索引。比如KEY idx_user_id (user_id), KEY idx_user_id_status (user_id, status)第一个索引和第二个索引的前缀完全重复第一个索引其实已经被第二个覆盖完全多余。MySQL不会自动识别这种重复建的时候也不会报错但每次插入、更新都要额外维护一棵索引树。冗余索引是指索引功能可以被另一个索引完全或部分覆盖。例(user_id)和(user_id, status)前者是冗余。(status, create_time, user_id)和(status, create_time)后者也是冗余因为前者最左前缀已经覆盖后者。如何排查可以查询系统表查看索引信息SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name;结合业务SQL把使用率低、重复前缀的索引梳理出来在低峰期DROP INDEX。注意删除索引前一定要先在测试库中验证确认没有查询依赖。6.2 索引碎片与重建建议随着频繁插入、更新、删除索引页会产生碎片。碎片多了索引树的叶子节点并不是连续存放的范围扫描和查询效率会下降索引占用的磁盘空间也会增大。InnoDB没有提供单独重建索引的命令但可以通过ALTER TABLE ... ENGINEInnoDB来重建整张表顺带整理索引和数据。也可以用OPTIMIZE TABLE它实际上是先重建表再分析表相当于把碎片整理一遍。不过OPTIMIZE TABLE在表数据量大时会锁表或复制全表数据耗时很长并且需要足够的磁盘空间。生产环境务必评估风险最好放在业务低峰期执行。如果表特别大有些团队会选择在线DDL工具比如pt-online-schema-change但操作前也要充分了解机制。判断索引是否需要整理可以看两个指标information_schema.tables里的data_free字段表示已分配但未使用的空间如果很大说明表中有不少碎片。另外查看information_schema.innodb_metrics中相关的page读指标也能辅助判断。7. 最后分享几点个人经验做了这么多年的MySQL性能排查我越来越觉得索引优化本质上是一个“理解数据访问路径”的工作。每一类查询、每一个索引设计都藏着对数据结构、磁盘IO和优化器行为的理解。一点小建议建索引前先用一个文档把所有高频查询列出来按执行频率排序再根据这些查询设计联合索引的顺序。最容易犯的错就是看到慢SQL就加索引结果索引越来越多写入越来越慢。宁可少而精不要多而杂。还有一个容易被忽略的细节MySQL优化器并不是完全可靠的。有时候你明明建了一个索引优化器却选择了全表扫描可能是因为数据量太少、统计信息过期或者某个表关联了太多的索引。遇到这种情况不要急着强改先ANALYZE TABLE更新统计信息再查看执行计划通常能解决。最后提醒一句任何索引优化都要在真实的压测环境验证。开发库数据量一万行时全表扫描和索引查询几乎没有差别但到了生产环境一千万行差距可能就是毫秒和秒的区别。动手改造之前先确认数据量级再决定要不要加索引。这是我个人在实际排查中反复验证过的最重要原则。