MySQL索引与SQL优化：从B+树到慢查询排查实战

很多同学学 MySQL 都是靠视频入门索引、B树、SQL优化这些词听起来都很熟但一到线上问题就露馅明明建了索引查询还是慢明明 SQL 看着没问题explain 一执行却走了全表扫描面试背了一堆“最左前缀”“索引失效”被追问底层原因时直接卡住。这套知识之所以难不是因为它有多高深而是因为大多数教程把“原理”和“实践”拆开了。讲 B树的时候不讲 SQL 怎么写才走索引讲 SQL 优化的时候又不解释为什么 MySQL 要这样执行。结果是背了一堆结论遇到新问题完全不会迁移。这篇文章不打算复述某个视频的目录而是把索引、B树、联合索引、SQL 优化、慢查询定位、高频面试题串成一条完整的知识链路。读完后你至少能做到三件事第一给一张表设计合理的索引不再“凭感觉加索引”第二遇到慢 SQL 能按固定思路排查知道先看哪里、再看哪里第三面试被问到底层原理时能用手画 B树的方式把过程讲清楚。1. 这篇文章真正要解决的问题先说说我观察到的普遍现象。很多开发者的 MySQL 知识是碎片化的知道索引能加速查询但不知道索引为什么能加速知道联合索引有最左前缀原则但不知道这个原则是怎么推导出来的知道 explain 能看执行计划但看到 type 为 ref 还是 range 时判断不了要不要优化。这些碎片化知识最直接的后果是线上 SQL 变慢时只能靠猜。网上说“不要在索引列上做函数操作”你就把所有函数调用都去掉网上说“select 不要用 *”你就把所有 select * 改成显式字段。这些建议本身没大问题但没有底层逻辑支撑你永远不知道边界在哪里。这篇文章要帮你解决的核心问题是建立一条“数据结构 → 索引设计 → SQL 写法 → 执行计划验证 → 慢查询治理 → 面试表达”的完整链路。你会先理解 B树为什么是 MySQL 索引的默认数据结构再理解聚簇索引和二级索引的区别然后掌握联合索引的字段顺序设计方法最后能熟练用 explain 和慢查询日志定位线上问题。一句话总结这不是一篇“记住 20 条优化技巧”的文章而是一篇帮你把 MySQL 索引和 SQL 优化体系化梳理的文章。适合正在准备 MySQL 面试的开发者也适合被线上慢查询折磨得焦头烂额的日常开发。2. 核心概念B树、索引与聚簇索引2.1 B树为什么是 MySQL 索引的默认选择要理解索引先理解存储。MySQL 的数据最终是存在磁盘上的而磁盘随机读写的速度比内存慢好几个数量级。所以存储引擎设计索引的第一目标是尽量减少磁盘随机 IO 的次数。B树就是一种专门为磁盘存储设计的多路平衡查找树。它有两个典型特征第一所有数据都存放在叶子节点非叶子节点只存放索引键值。这意味着每次查找都必须走到叶子节点才能拿到数据所以任何一个查询的 IO 次数是相对稳定的基本等于树的层数。第二叶子节点之间用指针串联成了有序链表。这个设计让范围查询变得非常高效。比如查 id 大于 100 且小于 200 的数据只要找到 100然后顺着链表往后读就行了不需要回根节点重新遍历。从数据量角度算一笔账。假设一个非叶子节点能存 1000 个键值三层 B树可以存储大约 10 亿条记录。也就是说从 10 亿条数据里查一条记录大概只需要 3 到 4 次磁盘 IO。这正是 B树能支撑海量数据在线查询的根本原因。2.2 聚簇索引、非聚簇索引与回表InnoDB 存储引擎的索引结构需要区分两种聚簇索引和二级索引。聚簇索引的特点是索引键值决定数据的物理存储顺序。InnoDB 的聚簇索引就是主键索引。当你建表时没有显式指定主键InnoDB 会选择一个没有 NULL 的唯一键作为主键如果也没有它会生成一个隐藏的 ROWID 作为聚簇索引。所以从实践角度看每张 InnoDB 表都应该有主键主键最好是自增整型这样新数据的物理插入位置永远在末尾不会频繁触发页分裂。二级索引也叫非聚簇索引它的叶子节点存放的是索引键值和主键值。这里有一个非常关键的设计通过二级索引查数据时先用索引找到主键再用主键回聚簇索引查完整行。这个过程叫回表。用一个例子说明。表 user 有主键 id还有二级索引 idx_name(name)。执行 SQLSELECT * FROM user WHERE name 张三;MySQL 会先走 idx_name 索引找到 name 为“张三”的叶子节点得到主键 id再回到聚簇索引中根据 id 找到完整行。如果查询命中的数据量很大回表成本就很高。这也引出后面要讲的覆盖索引它是一种避免回表的优化手段。2.3 为什么 Hash 索引没有成为默认选择有同学会问Hash 索引做等值查询不是更快吗确实对等值查询来说Hash 索引的定位速度通常比 B树更快。但 Hash 索引有两个致命短板第一它不支持范围查询因为 Hash 值是无序的区间查找只能全表扫描第二Hash 冲突时需要逐个比较在极端情况下性能反而下降。MySQL 的 Memory 引擎和 InnoDB 的 adaptive hash index 都提供 Hash 索引能力但 InnoDB 默认还是使用 B树核心原因正是为了同时兼顾等值查询和范围查询。理解了这个对比你在面试里说“B树适合磁盘存储、支持范围查询、树高稳定”时就有了依据。3. 环境准备搭建一个可持续实验的 MySQL 环境3.1 安装与版本选择网上关于 MySQL 安装的教程非常多还有安装配置教程、Windows 安装教程、Docker 安装 MySQL 教程方法五花八门。这里不重复铺开只强调几个关键选择。学习索引和 SQL 优化建议优先使用 MySQL 8.0 及以上版本。8.0 的优化器比 5.7 更强支持窗口函数、CTE对索引失效场景的判定也更精细。生产环境选版本要看具体业务和云厂商支持情况。如果公司还在用 5.7学习时至少要清楚 5.7 和 8.0 在索引行为上的差异。本地实验建议用 Docker 快速启动一个实例避免在系统里安装一堆依赖也方便随时重置。Docker 启动 MySQL 的参考命令如下docker run -d \ --name mysql-study \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot \ mysql:8.0启动后连接mysql -h127.0.0.1 -P3306 -uroot -proot3.2 准备一张有足够数据量的测试表索引优化实验最怕数据量太少。几千条数据时全表扫描和走索引的差别几乎看不出来很容易得出错误结论。建议准备一张百万级数据量的测试表。创建一个简单的订单表CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;用存储过程灌入测试数据。这里用递归 CTE 或存储过程都行但存储过程的写法更容易控制循环次数DELIMITER $$ CREATE PROCEDURE init_order_data(IN total INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i total DO INSERT INTO t_order(order_no, user_id, amount, status, create_time) VALUES ( CONCAT(NO_, LPAD(i, 8, 0)), FLOOR(RAND() * 100000), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), NOW() - INTERVAL FLOOR(RAND() * 365) DAY ); SET i i 1; END WHILE; END$$ DELIMITER ;然后执行CALL init_order_data(1000000);数据量到 100 万条后后续 explain 的结果会更有参考价值。这步做完了才算有一个能支撑实验的环境。3.3 开启慢查询日志慢查询日志是定位线上 SQL 问题的第一工具。在 MySQL 8.0 中可以直接用 SET GLOBAL 命令动态开启避免改配置文件后重启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_output TABLE;log_output 设为 TABLE慢查询记录会写入 mysql.slow_log 表查询起来非常方便SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;如果要查看当前慢查询日志相关的所有配置SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;4. explain 执行计划判断 SQL 是否走索引的标尺4.1 explain 的核心字段给任意一条查询前面加上 explainMySQL 就会告诉你这条 SQL 的执行计划。重点看四个字段type、key、rows、Extra。type 表示访问类型性能从好到差大致是type含义常见场景system系统表通常不会出现系统表const主键或唯一索引等值查询WHERE id 1eq_ref联表查询时被驱动表用主键或唯一索引JOIN 查询ref非唯一索引等值查询WHERE user_id 100range索引范围扫描WHERE id 100index遍历索引树覆盖索引扫描ALL全表扫描无索引或索引失效key 表示实际使用的索引名如果为 NULL说明没走索引。rows 表示预估扫描行数行数越大通常性能越差。Extra 是优化器给出的补充信息常见的有 Using index、Using where、Using filesort、Using temporary这几项在后面优化时都会遇到。4.2 用 explain 验证一个基本查询回到刚才的 t_order 表先看一个简单查询EXPLAIN SELECT * FROM t_order WHERE user_id 12345;预期结果中type 应该是 refkey 应该是 idx_user_idExtra 一般会显示空或 Using index condition。这说明这条 SQL 使用了二级索引查找。再执行EXPLAIN SELECT * FROM t_order WHERE amount 500;amount 列没有索引结果大概率是 type 为 ALLkey 为 NULL。这就是一个典型的全表扫描。这里要养成的习惯是任何一条你担心性能的 SQL先 explain 再下结论。不要只看 SQL 表面因为 MySQL 优化器有自己的规则是否走索引并不是由 SQL 写法单方面决定的。5. 联合索引设计、最左前缀与索引下推5.1 联合索引的物理结构联合索引是指由多个字段组成的索引。很多人以为联合索引是给多个字段分别建了索引这是错误的。联合索引本质上是一棵单独的 B树它先按第一个字段排序第一个字段相同时再按第二个字段排序依次类推。这也是最左前缀原则的由来。因为索引树的排序是从左到右依赖的如果你没有使用联合索引的最左边字段作为查询条件优化器就无法利用这个有序性。比如联合索引 (a, b, c)查询条件只有 b 和 c 时用不上这个索引的最左前缀很难有效利用。5.2 字段顺序怎么选联合索引的字段顺序设计有两个核心优先级第一优先级区分度高的字段放前面。区分度越高索引树在第一个层级就能筛掉越多的数据。比如性别字段区分度很低只有两三个值放在联合索引最前面通常不划算。第二优先级考虑查询频率和排序需求。如果有 order by 字段尽量让排序字段包含在联合索引中这样可以利用索引有序性避免 filesort。举个例子订单表常见查询是SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC;比较合理的联合索引是 (user_id, status, create_time)。user_id 区分度高放最前面status 用来进一步过滤create_time 用来排序。这样查询既能利用索引过滤又能避免额外的排序操作。5.3 索引下推是什么索引下推是 MySQL 5.6 引入的优化英文叫 Index Condition Pushdown简称 ICP。它的作用是减少回表次数。在没有索引下推之前二级索引查到数据后要先回表再把整行数据交给 Server 层去判断其他条件。有了索引下推后部分条件可以在存储引擎层直接过滤掉只有满足条件的记录才回表。还是用 (user_id, status, create_time) 这个联合索引举例。执行SELECT * FROM t_order WHERE user_id BETWEEN 100 AND 200 AND status 1;MySQL 可以在索引树的扫描过程中直接用 status 1 过滤掉不符合条件的记录减少回表数量。理解了这一点你就能明白为什么说联合索引设计得好不只是“能走索引”的问题还能降低回表压力。5.4 一个典型的联合索引示例建立联合索引ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);然后执行EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC;预期的结果是type 为 refkey 为 idx_user_status_timeExtra 里不会出现 Using filesort。这说明索引设计覆盖了查询的过滤和排序需求。如果查询条件少了 user_idEXPLAIN SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC;这时候联合索引大概率无法被有效利用因为最左前缀缺失。这就是最左前缀原则的实际表现。6. 索引失效的常见场景与规避方法6.1 隐式类型转换导致索引失效这是最容易被忽视的场景。比如 order_no 是 varchar 类型但查询时用数字匹配SELECT * FROM t_order WHERE order_no 12345678;MySQL 会把 varchar 列隐式转换为数字再比较导致索引失效走全表扫描。正确写法是使用字符串SELECT * FROM t_order WHERE order_no 12345678;判断一个查询是否发生隐式类型转换可以用 explain 看 type 是不是从 ref 变成了 ALL。这类的坑非常隐蔽尤其是长字符串类型的编号字段。6.2 在索引列上做计算或函数操作如果在索引列上应用函数或表达式优化器通常无法使用索引。常见写法包括SELECT * FROM t_order WHERE DATE(create_time) 2026-01-01;原因是 create_time 这个列的值被 DATE() 函数包装后索引的有序性被打乱了。更推荐改成范围查询SELECT * FROM t_order WHERE create_time 2026-01-01 AND create_time 2026-01-02;这里要说明一点MySQL 8.0 对部分函数有优化但作为通用规则仍然建议不要在索引列上做函数操作。6.3 前导模糊查询以通配符开头的 LIKE 查询无法有效利用索引SELECT * FROM t_order WHERE order_no LIKE %abc%;因为索引树是按前缀排序的%abc%无法从有序树中定位起点。如果业务确实需要这种模糊匹配可以考虑全文索引或搜索引擎。仅以固定前缀查询时LIKE abc%是可以走索引的。6.4 OR 条件与 IN 的注意事项OR 条件中只要有一个字段没有索引整个查询就可能走全表扫描。比如SELECT * FROM t_order WHERE user_id 100 OR status 1;如果 status 上没有索引这条 SQL 很可能退化为全表扫描。更稳妥的做法是拆分为两个查询或者用 UNION ALL 合并结果。关于 INMySQL 优化器对 IN 列表的处理相对智能在索引列上使用 IN 通常可以走 range 访问。但 IN 列表如果过长优化器会重新评估成本甚至选择全表扫描。这里不能一概而论应该以 explain 结果为准。6.5 对索引字段做 IS NULL 判断在 MySQL 中IS NULL 不一定会让索引失效。实际上如果索引列允许 NULLIS NULL 走索引的情况是存在的。但有一个容易忽略的问题不要在可空列上建立过多索引因为 NULL 值会占用额外空间也可能影响查询计划的稳定性。设计表时能用 NOT NULL 就用 NOT NULL并为字段设置 DEFAULT 值。7. SQL 优化实战从慢 SQL 到高效 SQL7.1 找到慢 SQL前面已经配置了慢查询日志。线上排查时第一步先看慢日志把耗时超过阈值的 SQL 捞出来。MySQL 8.0 里还可以查 performance_schema 的 events_statements_summary_by_digest 表按累计耗时排序快速找到 Top N 慢 SQLSELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms, MAX_TIMER_WAIT/1000000000 AS max_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;7.2 经典分页深翻页优化LIMIT 深翻页是很多业务都会遇到的问题。比如SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;这条 SQL 会扫描前 1000020 行再丢弃前 1000000 行成本非常高。优化思路是先用子查询拿到目标主键范围再回表查完整数据SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20 ) tmp ON t.id tmp.id;子查询里只扫描主键速度会明显更快。如果有序字段不是主键也可以用覆盖索引先定位主键来做。7.3 order by 排序优化出现 Using filesort 时通常说明排序字段没有利用索引有序性。优化方式有两个层面。第一如果排序字段和过滤条件能组成联合索引建立合适的索引消除 filesort。比如前面提到的 (user_id, status, create_time) 联合索引。第二如果数据量确实很大让排序在内存中完成。可以通过调整 sort_buffer_size 来缓解但这是治标不治本。真正的思路是让排序走索引而不是依赖缓冲区。7.4 group by 优化group by 的优化核心是消灭临时表。常见思路是尽量让分组字段走索引减少 MySQL 创建内部临时表的概率。如果无法避免可以通过 SQL 改写减少分组行数比如先缩小 WHERE 范围再分组统计。7.5 避免 select * 的深层原因很多教程都会说“不要 select *”但没说清楚原因。从索引优化角度看select * 会强制回表读取所有字段导致覆盖索引失效。如果你只需要 user_id 和 status而索引 (user_id, status) 已经覆盖了这两个字段查询就变成覆盖索引查询Extra 会显示 Using index完全不需要回表。SELECT user_id, status FROM t_order WHERE user_id 100;只要这条查询只查索引列它就在索引树上完成不回表。这是覆盖索引的价值也是 select * 最直接的性能损失点。7.6 用一条实际慢查询走完排查流程假设有一条线上慢 SQLSELECT * FROM t_order WHERE user_id 100 AND DATE(create_time) 2026-01-01 ORDER BY amount DESC;排查步骤第一步explain 看执行计划大概率会发现 type 不是理想状态或者 Extra 里有 Using filesort。第二步检查 WHERE 条件。DATE(create_time) 在索引列上用了函数直接导致该条件无法使用索引。改成 create_time 2026-01-01 AND create_time 2026-01-02。第三步检查排序字段。amount 不在联合索引中导致 filesort。如果查询频率很高可以考虑调整索引设计让它覆盖排序需求。经过改写后的 SQLSELECT * FROM t_order WHERE user_id 100 AND create_time 2026-01-01 AND create_time 2026-01-02 ORDER BY amount DESC;再 explain 一次对比 type、key、rows、Extra 的变化。这个流程不依赖任何经验玄学完全靠执行计划驱动。8. 高频面试题与底层原理串讲8.1 为什么 InnoDB 表必须有主键InnoDB 的数据存储在聚簇索引中聚簇索引的叶子节点就是整行数据。没有主键InnoDB 就要找唯一键找不到就生成隐藏 ROWID。这个隐藏 ROWID 没有业务意义且会导致数据存储位置不受控。更重要的是二级索引叶子节点存的是主键值如果没有主键二级索引就无法高效回表。所以从设计和原理两个层面都建议为每张 InnoDB 表显式设计主键。8.2 主键为什么推荐自增整型自增整型主键有两个优点一是整型占用空间小二级索引叶子节点存的都是主键值主键小索引自然小二是自增顺序插入数据大概率追加到 B树末尾减少页分裂。换成 UUID 作为主键随机字符串不仅占用空间大插入时还会导致页分裂和索引碎片化。8.3 什么情况下适合用前缀索引对于 text 或超长 varchar 列为了减小索引空间可以只对字段前 N 个字符建索引。比如用户手机号或邮箱前缀区分度足够高时用前缀索引是合理的。但要注意前缀索引无法用覆盖索引优化也不能用于 order by 和 group by。实际项目中可以用如下 SQL 验证不同前缀长度的区分度SELECT COUNT(DISTINCT LEFT(order_no, 6)) AS prefix6, COUNT(DISTINCT LEFT(order_no, 8)) AS prefix8, COUNT(DISTINCT LEFT(order_no, 10)) AS prefix10, COUNT(DISTINCT order_no) AS full_col FROM t_order;然后选择一个与全列区分度接近的前缀长度。8.4 联合索引为什么要考虑区分度区分度等于某个字段不同值的数量除以总行数。区分度越高该字段在索引树中的过滤效果越强。比如共同构造联合索引 (gender, user_id)gender 只有 0 和 1区分度极低几乎不会缩小数据范围。把区分度高的 user_id 放前面过滤效果会更好。8.5 什么是回表覆盖索引怎么避免回表二级索引叶子节点只存主键值和索引列值查询完整行时必须回聚簇索引这个过程叫回表。如果查询需要的所有字段都在二级索引中那就不必回表直接遍历索引树即可这就是覆盖索引。覆盖索引是优化高频查询最有效的手段之一。8.6 为什么 explain 里 rows 不准explain 的 rows 是优化器基于统计信息和索引基数估算出来的不是精确值。它用于判断执行计划的相对好坏但不要迷信这个数字。如果统计信息过期可以用 ANALYZE TABLE 更新ANALYZE TABLE t_order;9. 常见问题与排查方法问题现象可能原因排查方式解决方案查询变慢explain 显示全表扫描索引列上使用函数或隐式转换检查 WHERE 条件是否对索引列做计算改写 SQL避免函数操作联合索引没有生效查询条件不满足最左前缀explain 查看 key 字段调整查询条件顺序或重新设计联合索引排序字段导致 Using filesort排序字段不在索引中查看 Extra 字段优化联合索引让排序字段入索引select * 导致回表过多查询字段超出索引覆盖范围查看 Extra 是否显示 Using index改成只查询必要的字段分页越深越慢LIMIT 深偏移扫描大量行观察耗时随页数变化用覆盖索引先取主键再回表慢查询日志没有记录long_query_time 设置过大或日志未开启查询慢日志相关变量调整 long_query_time打开日志数据量小但查询不稳定统计信息过期对比多次执行计划执行 ANALYZE TABLE 更新统计信息唯一键冲突导致性能抖动不合理的唯一索引设计查看错误日志重新评估业务约束字段10. 最佳实践与工程建议10.1 索引设计的分阶段策略新系统上线时建议不要一次性加满所有索引。先建主键索引和明显高频查询需要的联合索引其他索引等压测或上线后根据慢 SQL 和应用日志逐步补充。每个索引都会带来写入开销索引数量越多insert、update、delete 的代价就越大。10.2 不要盲目删除重复或冗余索引线上经常出现 idx_user_id 和 idx_user_status 同时存在的情况而 idx_user_status 其实可以覆盖 user_id 的查询场景。删除索引前先用 sys.schema_unused_indexes 查看未使用索引SELECT * FROM sys.schema_unused_indexes;但要注意这张视图只能反映统计信息收集期内的使用情况生产变更前一定要备份和验证。10.3 SQL 审查应该进入 Code Review 流程很多团队只 review Java 代码SQL 写完直接上线。实际上SQL 质量问题最好在开发阶段就通过 explain 发现。团队可以在 MR 模板里加入「本次涉及的 SQL 是否附上了 explain 结果」这一项从流程上倒逼执行计划检查。10.4 用索引监控视图帮助决策MySQL 8.0 的 sys 库提供了不少索引使用统计视图。除了 schema_unused_indexes还可以查 schema_index_statistics查看索引的扫描次数、使用次数等信息。这是做一个索引增减决策的重要依据。10.5 运维变更安全提醒任何线上索引变更都要在低峰期进行。MySQL 8.0 支持在线 DDL但大表加索引依然会产生额外负载需要监控主从延迟和磁盘 IO。执行前确认备份执行后立即用 explain 验证查询计划。11. 总结与后续学习方向这篇文章把 MySQL 索引相关的核心知识做了一个体系化梳理从 B树的结构原理到聚簇索引、二级索引、回表、覆盖索引这些基本概念再到联合索引设计、最左前缀、索引下推、索引失效场景最后落到慢查询定位和 SQL 改写实战。比较重要的是这些知识点不是孤立的。遇到任何线上慢 SQL都应该走同一条排查路径先捞慢日志再 explain 看执行计划然后根据 type、key、rows、Extra 判断问题层面最后通过改写 SQL 或调整索引解决。如果你正在准备面试建议不要停留在背诵概念。可以找一个自己项目里的表把联合索引设计、最左前缀失效、覆盖索引优化各写一个 explain 示例亲眼看一遍 type 和 Extra 的变化。这个过程比背十套面试题都有效。后续值得继续深入的方向还有MySQL 优化器成本模型、事务隔离级别对索引读取的影响、redo log 与 undo log 的底层机制、主从复制延迟排查。这些内容都建立在索引和存储结构的基础上把今天这篇吃透再往下学就不会觉得吃力了。