在 MySQL 数据库的日常使用中索引和 SQL 优化往往是决定系统性能的关键分水岭。很多开发者在项目初期只关注功能能否跑通等数据量增长到百万、千万级别后才发现一条慢 SQL 就能拖垮整个服务。索引、B 树、联合索引、索引优化、SQL 优化、MySQL 调优这些概念并不是孤立的知识点而是构成一条完整性能排查链路的必备技能。这篇文章会从 InnoDB 存储引擎的真实数据结构出发逐步讲清楚 B 树为什么能成为索引的默认选择联合索引在底层如何排列索引下推解决了什么问题再结合慢 SQL 定位、执行计划分析和真实调优案例给出可以直接用到生产环境的优化方法最后整理高频面试题和一套可复用的排查清单。这套内容的定位是给已经会写基础 SQL、做过简单项目的开发者目标是让读者看完后能自己分析一条慢 SQL 是索引失效还是查询设计不合理能设计出适合业务场景的联合索引能解释清楚为什么某条语句在数据量变大后会突然变慢也能在面对 MySQL 性能问题时有一条清晰的排查路径。1. 先搞清楚索引到底是什么以及 InnoDB 为什么要用 B 树很多资料一上来就讲 B 树的定义但如果不先理解索引在存储引擎中的真实角色后面看执行计划、看索引失效场景都会觉得抽象。要理解索引先得理解 InnoDB 的表数据在磁盘上是怎么组织的。1.1 从 InnoDB 表空间说起数据页和行记录InnoDB 存储引擎将数据按页Page存储默认每页大小为 16KB。页是 InnoDB 读写磁盘的最小单位也就是说即使你只需要查询一行记录InnoDB 也会把这行记录所在的一整页数据加载到内存缓冲池中。表空间中的页通过页号进行管理页之间通过双向链表连接页内的行记录通过单向链表连接。这个设计带来一个直接后果在没有索引的情况下如果要查询某个非主键字段InnoDB 只能从第一个数据页开始逐页读取逐行比对。这个操作被称为全表扫描Full Table Scan。当表只有几千行时全表扫描没有问题当表有千万行、数据占用几个 GB 时全表扫描会产生大量磁盘 I/O性能急剧下降。索引的本质就是额外维护一套更小的、排好序的数据结构通过这套结构可以快速定位到目标记录在哪些数据页中从而避免全表扫描。1.2 为什么是 B 树而不是二叉树、红黑树或哈希表常见的索引数据结构有哈希表、二叉树、红黑树、B 树和 B 树。要理解 InnoDB 为什么选择 B 树需要从磁盘 I/O 和范围查询两个角度来分析。首先是磁盘 I/O 模型。访问磁盘一次的时间大约是毫秒级而内存访问是纳秒级。索引结构每往下一层就可能产生一次磁盘 I/O。因此索引树的高度直接决定了查询的磁盘 I/O 次数。二叉树在最坏情况下高度等于节点数红黑树虽然平衡但高度仍然接近 O(log2N)当数据量达到百万级时树高在 20 层左右也就是说一次查询可能触发 20 次磁盘 I/O。B 树和 B 树通过让每个节点存储多个键值和多个子节点指针显著降低了树高。InnoDB 中一个数据页 16KB如果每个节点是一个页那么一个节点大概能存储数百个键值三到四层的 B 树就能支撑千万级数据量。其次是范围查询能力。哈希索引在等值查询上可以达到 O(1) 复杂度但无法支持范围查询、排序和前缀匹配。数据库中最常见的查询是范围查询比如 WHERE create_time BETWEEN 和 ORDER BY哈希索引对此无能为力。B 树虽然在等值查询和范围查询上都不差但 B 树的每个节点都存储完整的数据相同数据量下树更高而且 B 树的中序遍历需要跨层跨节点访问相比 B 树不够紧凑。最后是 B 树的独特设计。B 树的所有数据记录都存储在叶子节点非叶子节点只存储键值和子节点指针因此非叶子节点能容纳更多键值树更矮。同时叶子节点之间通过双向链表连接非常适合范围查询和排序扫描。InnoDB 的主键索引和二级索引底层都是 B 树结构。1.3 聚簇索引和二级索引为什么回表是性能瓶颈InnoDB 中索引可以分为两大类聚簇索引Clustered Index和二级索引Secondary Index。聚簇索引的叶子节点直接存储整行数据。InnoDB 表本身就是一个以主键为 B 树键值的聚簇索引。如果没有显式定义主键InnoDB 会选择一个非空唯一索引作为聚簇索引如果也没有InnoDB 会生成一个隐藏的 ROW_ID 作为聚簇索引键。二级索引的叶子节点存储的是索引字段值和主键值而不是整行数据。因此通过二级索引查询时会先在二级索引 B 树中找到对应的主键值再根据主键值到聚簇索引中查找完整行记录。这个根据主键值到聚簇索引中再查一次的过程称为回表Table Lookup。回表操作不是免费的。如果查询需要访问大量二级索引记录每条记录都触发一次回表会产生大量随机 I/O。理解这一点后后面讲覆盖索引和索引下推时就能明白它们为什么能提升性能。注意在 InnoDB 中二级索引叶子节点存储的是主键值而不是行指针。这意味着主键越短二级索引占用的空间越小查询时的 I/O 成本也越低。使用过长的随机主键比如 UUID 字符串会让每个二级索引都变得臃肿这是很多表性能差的隐藏原因之一。2. 联合索引的底层排列方式决定了你的查询能不能走索引联合索引也叫复合索引是指在一个索引中包含多个字段比如 INDEX idx_user_status (user_id, status)。很多人只知道创建联合索引时要把区分度高的字段放前面但要真正理解联合索引的优化和失效场景必须先搞懂联合索引在 B 树中是怎么排列的。2.1 联合索引的键值排序规则先左后右逐列比较联合索引的每个节点存储的不是单个字段值而是多个字段的复合键值。B 树对复合键值的比较规则是先按第一个字段排序如果第一个字段相同再按第二个字段排序依次类推。举个例子。假设有表 user_orderCREATE TABLE user_order ( id INT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2), KEY idx_user_status (user_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;联合索引 idx_user_status 的叶子节点中所有记录先按 user_id 升序排列在 user_id 相同的情况下再按 status 升序排列。这个排序规则是理解联合索引最核心的点。如果查询条件是WHERE user_id 100 AND status 1MySQL 可以在联合索引中精确定位到目标键值如果查询条件是WHERE status 1由于索引中第一列是 user_idstatus 的排序是建立在 user_id 相同的前提下的MySQL 无法直接跳过 user_id 去按 status 查找因此该查询无法使用这个联合索引。2.2 最左前缀原则为什么跳过了第一个字段就会失效最左前缀原则是联合索引使用中的核心约束。它包含两种情况。第一种是等值匹配的最左前缀。索引中的字段从左到右依次匹配查询条件中的字段。如果查询条件使用了索引的第一个字段则索引可用如果只使用第二个字段则索引失效。比如 idx_user_status 支持WHERE user_id 100但不支持WHERE status 1。第二种是范围匹配的截断效应。当查询条件中出现范围比较、、BETWEEN、LIKE 前缀匹配时范围条件所在的字段之后的其他索引字段都无法用于精确定位。比如查询条件是WHERE user_id 100 AND status 1 AND create_time 2024-01-01索引 idx_user_status 中 user_id 用于等值定位status 用于范围定位而 create_time 不在索引中无法继续参与索引匹配。这里要特别说明一个容易误解的场景WHERE user_id 100 AND status 1中status 使用了范围条件但是 user_id 仍然是等值条件所以这个查询可以走 idx_user_status。范围条件导致失效的是范围条件之后的其他索引字段而不是范围条件本身。2.3 索引下推减少了回表次数但改变不了索引排列规则索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化很多面试题和实际优化场景都会涉及。在没有索引下推之前InnoDB 通过二级索引定位到记录后需要先回表取出完整行再在 Server 层判断 WHERE 条件中非索引字段是否满足。有了索引下推后如果 WHERE 条件中的部分字段也存在于索引中MySQL 会在存储引擎层直接对这些索引字段进行过滤过滤通过后才回表过滤不通过就直接跳过。举个例子。假设表 user_order 上有联合索引 idx_user_status (user_id, status)查询SELECT * FROM user_order WHERE user_id 100 AND status 1;在 MySQL 5.6 之前InnoDB 根据 user_id 100 找到所有二级索引记录然后逐一回表取出完整行后再判断 status 是否等于 1。在启用索引下推后InnoDB 在遍历二级索引时直接在索引层面判断 status 是否等于 1只有等于 1 的记录才回表。如果 user_id 100 的记录有 1000 条其中 status 1 的只有 10 条那么索引下推可以把回表次数从 1000 次降到 10 次。索引下推有一个关键前提下推的字段必须存在于索引中。它不能改变联合索引的排列规则也不能让跳过了第一个字段的查询重新走索引。可以用 EXPLAIN 的 Extra 列观察索引下推是否生效如果出现 Using index condition说明索引下推被使用。在生产环境调优时可以通过对比开关优化器选项来验证效果SET optimizer_switch index_condition_pushdownoff; -- 执行查询并记录耗时 SET optimizer_switch index_condition_pushdownon; -- 再次执行查询并记录耗时3. 索引优化的落地方法先看执行计划再谈优化手段在实际项目中索引优化不是靠猜的。拿到一条慢 SQL 后第一步是看执行计划第二步是确认索引是否被使用第三步才是调整索引或改写 SQL。3.1 用 EXPLAIN 分析慢 SQL重点关注哪些列EXPLAIN 是分析查询执行计划的核心命令。EXPLAIN SELECT user_id, status, amount FROM user_order WHERE user_id 100 AND create_time 2024-01-01;执行结果中需要重点关注以下列列名关注内容常见问题type访问类型从好到差依次是 system、const、eq_ref、ref、range、index、ALLALL 说明全表扫描index 说明扫描完整索引树key实际使用的索引名称NULL 表示没有使用索引key_len使用的索引字节长度可以判断联合索引中实际用到了几个字段rowsMySQL 预估扫描的行数与真实扫描行数偏差过大时说明统计信息不准Extra附加信息Using filesort、Using temporary、Using index condition 等都有不同含义key_len 是很容易被忽略但非常有用的列。假设 idx_user_status (user_id, status)user_id 是 INT NOT NULL占用 4 字节status 是 TINYINT NOT NULL占用 1 字节。如果执行计划中 key_len 显示为 4说明只用了 user_id 一个字段如果显示为 5说明两个字段都被用到了。Extra 列中常见的问题值Using filesort文件排序通常意味着 ORDER BY 的字段没有走索引需要考虑是否可以通过调整索引顺序来消除排序。Using temporary使用了临时表常见于 GROUP BY 和 DISTINCT 操作。Using index condition索引下推生效。Using index覆盖索引查询所需字段都在索引中不需要回表。还有一个常见的误区认为 EXPLAIN 显示索引被使用就说明查询已经优化到位。实际上索引可能只被用于定位小部分数据也可能因为 select 了额外字段而导致回表数量巨大。EXPLAIN 只是优化的起点不是终点。3.2 索引失效的常见场景每一条都要能解释原理索引失效是面试和实际排查中的高频问题。以下几个场景最为常见。第一条对索引字段使用函数或表达式。比如WHERE DATE(create_time) 2024-01-01MySQL 无法直接使用 create_time 上的索引因为索引中的键值是原始时间值不是格式化后的日期值。把 SQL 改写为WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00索引就能生效。类似的还有WHERE amount 1 100、WHERE id 1 5等情况。第二条隐式类型转换。如果字段是 VARCHAR 类型查询条件写成WHERE phone 13812345678MySQL 会把字段转换为数字进行比较导致索引失效。应该写成WHERE phone 13812345678。反过来如果字段是 INT 类型条件写成WHERE id 5MySQL 会尝试把字符串转换为数字这种通常不影响索引使用但不推荐依赖隐式转换。第三条LIKE 以通配符开头。WHERE name LIKE %张无法使用索引因为 B 树的排序规则是从左到右的以通配符开头意味着无法确定匹配的起始键值。但WHERE name LIKE 张%可以走索引这属于范围匹配的一种。第四条联合索引未遵守最左前缀原则。这一点在前面已经详细解释过跳过联合索引左侧字段直接查询右侧字段索引不生效。第五条NULL 值判断和 NOT IN。WHERE name IS NULL在部分 MySQL 版本和索引配置下可能不走索引WHERE id NOT IN (1,2,3)则很可能退化为全表扫描。相比之下WHERE id IN (1,2,3)通常可以走索引。实际项目中如果业务允许尽量使用 IS NOT NULL 配合默认值来避免 NULL 判断或者统计区分度后决定是否加索引。错误写法正确写法原因DATE(create_time) 2024-01-01create_time ... AND create_time ...函数破坏索引键值的有序性phone 13812345678phone 13812345678隐式类型转换导致索引失效name LIKE %张name LIKE 张%前缀通配符无法定位起始键值WHERE status 1需要联合索引包含 status 且使用最左前缀跳过左侧字段无法索引匹配3.3 覆盖索引把查询字段放入索引直接省掉回表覆盖索引是指查询所需的所有字段都包含在同一个二级索引中这样 InnoDB 可以从二级索引直接返回数据不需要回表。在 EXPLAIN 中表现为 Extra 列为 Using index。实际业务中常见的优化方式是把高频查询的字段组合成联合索引。比如一个订单列表页经常执行SELECT user_id, status, create_time FROM user_order WHERE user_id 100 ORDER BY create_time DESC LIMIT 20;如果现有联合索引是 idx_user_status (user_id, status)这个查询虽然能通过 user_id 定位但 ORDER BY create_time 需要进行文件排序而且 select 的字段不在索引中需要回表。可以设计一个更贴合查询的联合索引ALTER TABLE user_order ADD INDEX idx_user_createtime (user_id, create_time);此时这个查询可以完全通过 idx_user_createtime 索引完成user_id 用于等值定位create_time 用于排序叶子节点上的 user_id、create_time 字段直接覆盖了 select 的字段不需要回表。Extra 列会显示 Using index查询性能显著提升。覆盖索引不是越宽越好。索引字段越多写入开销越大占用空间越大。设计覆盖索引时要结合业务中的高频查询优先覆盖 select 字段少但频率极高的场景。3.4 前缀索引长字符串字段的索引设计方案对于 VARCHAR 长字段比如用户昵称、文章标题、URL 等如果直接建立完整索引索引占用空间很大且索引树会比较臃肿。常见做法是只对字段的前 N 个字符建立索引称为前缀索引。ALTER TABLE user_profile ADD INDEX idx_nickname (nickname(10));选择前缀长度时需要平衡区分度和空间占用。区分度太低会导致大量重复键值降低索引效率区分度太高又浪费空间。可以通过统计不同前缀长度的区分度来决定SELECT COUNT(DISTINCT LEFT(nickname, 5)) AS prefix_5, COUNT(DISTINCT LEFT(nickname, 8)) AS prefix_8, COUNT(DISTINCT LEFT(nickname, 10)) AS prefix_10, COUNT(DISTINCT nickname) AS full_count FROM user_profile;用前缀区分度除以全字段区分度可以得到一个比例。常见的经验是前缀区分度达到全字段区分度的 90% 以上就基本够用。比如全字段区分度 100 万前缀 10 的区分度是 95 万那么比例是 95%可以接受。前缀索引有一个限制无法使用覆盖索引优化因为索引中只存储了字段的前 N 个字符select 该字段完整值时必须回表。这一点在面试和设计时需要说明。4. SQL 优化实战从定位慢 SQL 到改写一条查询SQL 优化不只是建索引。很多时候索引已经建好但 SQL 本身的写法导致优化器选择了错误的执行计划。这一章从慢 SQL 日志开始走一遍完整的 SQL 优化流程。4.1 如何定位慢 SQL慢查询日志和 performance_schemaMySQL 提供了慢查询日志用于记录执行时间超过阈值的 SQL。生产环境通常建议开启慢查询日志并设置阈值。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 的单位是秒设置为 2 表示执行时间超过 2 秒的 SQL 会被记录。log_queries_not_using_indexes 开启后没有使用索引的 SQL 也会被记录即使执行时间很短。查看慢日志文件的路径SHOW VARIABLES LIKE slow_query_log_file;也可以直接查询慢日志表SELECT query_time, rows_sent, rows_examined, db, sql_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;这里有一个重要的分析思路不要只看执行时间还要看 rows_examined 和 rows_sent 的比例。如果一个查询扫描了 100 万行只返回 10 行说明 SQL 写法和索引设计有优化空间。反之如果扫描行数很少但执行时间仍然很长可能是锁等待、CPU 计算复杂、函数处理大量数据等原因。除了慢查询日志performance_schema 也记录了更细粒度的 SQL 执行统计。可以查询 events_statements_summary_by_digest 表按照执行总耗时排序找到 TOP N 的 SQL 模板SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_seconds, AVG_TIMER_WAIT / 1000000000 AS avg_seconds FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;定位到具体的慢 SQL 后再通过 EXPLAIN 分析执行计划。4.2 一个真实调优案例分页查询为什么越翻越慢很多分页查询在页码小的时候很快页码变大的时候越来越慢。典型 SQL 如下SELECT id, user_id, status, amount FROM user_order ORDER BY create_time DESC LIMIT 100000, 20;这条 SQL 的问题是MySQL 需要先找到前 100020 条记录然后丢弃前 100000 条只返回最后 20 条。如果 ORDER BY create_time 没有索引还需要进行文件排序。即使 create_time 上有索引LIMIT 偏移量很大时InnoDB 仍然需要扫描并跳过大量记录。优化方式有两种。第一种是延迟关联。先通过覆盖索引获取目标 id再与原表关联获取完整数据SELECT o.id, o.user_id, o.status, o.amount FROM user_order o INNER JOIN ( SELECT id FROM user_order ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;子查询 select 只包含 id 和排序列可以在覆盖索引上完成排序和分页避免回表大量数据。第二种是基于游标的分页适合 App 端和 API 场景。客户端传入上一页最后一条记录的 create_time 或 idSQL 直接定位到该位置之后的数据SELECT id, user_id, status, amount FROM user_order WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;这种方式的扫描行数不会随页码增加而增长性能稳定。4.3 深挖执行计划filesort 和临时表到底怎么消除ORDER BY 出现 Using filesort 时说明 MySQL 需要额外做一次排序操作。优化思路通常有两个方向。第一个方向是让排序列走索引。B 树天然有序如果查询中的 WHERE 等值条件和 ORDER BY 字段构成联合索引的最左前缀那么数据读取时已经是排好序的不需要额外排序。举个例子查询SELECT * FROM user_order WHERE user_id 100 ORDER BY create_time DESC;如果索引是 idx_user_createtime (user_id, create_time)user_id 等值匹配后create_time 在索引中已经按升序排列DESC 时倒序读取即可不需要 filesort。但如果索引是 idx_user_status (user_id, status)ORDER BY create_time 无法利用索引顺序MySQL 需要将结果集加载到内存或磁盘排序这就是 Using filesort。第二个方向是减少排序数据量。如果无法通过索引消除排序可以尽量减少参与排序的字段数量和行数。比如先通过索引过滤掉大部分数据再对剩余小结果集排序比全表排序快得多。GROUP BY 出现 Using temporary 时思路类似。常见做法是确认 GROUP BY 的字段是否与索引匹配如果匹配MySQL 可以利用索引的有序性直接分组不需要临时表。4.4 优化器选择偏差用 FORCE INDEX 还是改写 SQL有时候明明有合适的索引MySQL 优化器却选择了全表扫描或其他索引。这种情况通常发生在统计信息不准确、数据分布倾斜或者查询条件选择性较低时。通过 ANALYZE TABLE 可以更新表的统计信息ANALYZE TABLE user_order;如果更新统计信息后优化器仍然做出错误选择可以尝试 FORCE INDEX 强制使用指定索引SELECT * FROM user_order FORCE INDEX (idx_user_createtime) WHERE user_id 100 ORDER BY create_time LIMIT 20;但 FORCE INDEX 是治标不治本的方法。随着数据分布变化这个索引可能不再适合而强制指定反而会让查询更慢。推荐先分析 SQL 中哪些条件的区分度最高重新设计联合索引让优化器自然选择正确路径。在生产环境如果短期必须用 FORCE INDEX要加上注释说明原因并且定期验证效果。4.5 生产环境 SQL 优化的完整检查顺序在生产环境遇到一条慢 SQL 时可以按以下顺序排查通过慢查询日志确认 SQL 文本、执行时间、扫描行数。使用 EXPLAIN 查看访问类型、使用索引、key_len、Extra。检查是否因为函数、隐式转换、前缀通配符导致索引失效。检查联合索引是否满足最左前缀原则。确认查询字段是否可以用覆盖索引避免回表。确认 ORDER BY、GROUP BY 是否能利用索引顺序消除 filesort 和 temporary。确认 LIMIT 偏移量是否过大考虑延迟关联或游标分页。检查表统计信息是否需要 ANALYZE TABLE。综合考虑索引成本和查询数量决定是否调整索引。5. MySQL 高频面试题从原理到场景一次性讲清楚MySQL 索引和优化相关的面试题考察的重点往往不是死记硬背的定义而是能否结合原理解释现象。以下几个问题在面试中出现频率最高。5.1 为什么主键通常建议用自增整数而不是 UUID这个问题要从聚簇索引的插入和维护成本来分析。InnoDB 聚簇索引的叶子节点按主键顺序排列。自增主键在插入时新记录总是追加到 B 树最右侧的叶子节点已有的叶子节点不需要移动。UUID 主键是随机字符串新记录的主键值可能落在 B 树中间的某个位置为了保持顺序InnoDB 可能需要在中间位置分裂和调整叶子节点产生额外的写入开销和页碎片。从二级索引的角度看前面说过二级索引叶子节点存储主键值。整数主键占 4 到 8 字节UUID 字符串占 36 字节同样一个二级索引使用 UUID 主键会让索引体积更大查询时的 I/O 成本更高。但也不是所有场景都适合自增主键。在分库分表场景中全局唯一 ID 通常由分布式 ID 生成器产生此时需要结合业务选择雪花算法等有序 ID 方案。对于日志表、流水表这类只有插入和归档需求的数据也可以考虑使用类似雪花 ID 的有序整数方案。5.2 什么情况下可能会慢但 EXPLAIN 显示索引被使用这是容易被忽略的面试点。EXPLAIN 显示走了索引只能说明 MySQL 能使用该索引定位数据但性能还取决于回表次数、扫描行数、扫描的索引页数量以及排序和分组操作。典型场景是二级索引的区分度太低。比如一个性别字段只有 0 和 1 两个值在其上建索引后查询WHERE gender 1可能会回表大量记录性能甚至比全表扫描还差。优化器在这种情况下可能选择全表扫描但如果没有及时更新统计信息也可能错误地选择索引。另一种情况是索引范围查询扫到了大量重复键值。比如联合索引 (user_id, status) 中 user_id 100 的记录有 10 万条即使 status 条件能过滤掉大部分但在索引下推没有启用或过滤条件不在索引中时仍然会发生大量回表。5.3 联合索引字段顺序的选择依据是什么核心原则是优先考虑等值条件字段再考虑范围条件字段最后考虑排序字段。等值条件字段放在最左侧可以最大化利用最左前缀范围条件字段放在等值字段之后因为范围条件会截断后续字段的索引匹配排序字段尽量与索引顺序一致以消除 filesort。更进一步应该按字段区分度排序。区分度高的字段放在前面可以更快地缩小扫描范围。但区分度也要结合实际查询频率如果一个区分度较低的字段是查询中的高频等值条件仍然应该放在范围字段之前。比如订单表中的高频查询是SELECT * FROM user_order WHERE status 1 AND user_id 100 ORDER BY create_time DESC;此时联合索引建议设计为 (user_id, status, create_time)而不是 (status, user_id, create_time)。因为 user_id 区分度高放在最左侧能先过滤掉大量数据status 是等值条件放在第二位create_time 用于排序放在最后。5.4 索引下推、覆盖索引和回表之间的关系怎么表达这三者经常被放在一起考察。简单表述是没有覆盖索引时二级索引查到主键后需要回表获取完整行覆盖索引让二级索引直接包含查询所需字段从而避免回表索引下推则是在回表之前先在存储引擎层利用索引字段过滤二级索引记录减少回表次数。举例说明最直观。索引 idx_status_amount (status, amount)查询SELECT amount FROM user_order WHERE status 1 AND amount 100;这个查询中status 和 amount 都在索引中select 字段 amount 也在索引中不需要回表是覆盖索引场景。由于没有 select 未索引字段索引下推在这种场景下作用不明显。如果改成 select *则需要回表此时索引下推可以先在索引中过滤 amount 100减少回表次数。5.5 如果一张表有多个单列索引MySQL 会怎么选择MySQL 优化器在多个单列索引之间通常只会选择一个扫描代价最低的索引并不会自动将多个单列索引组合成一个联合索引来使用。虽然在部分版本中会出现 Index Merge 优化但这类场景复杂且受数据分布影响大不应该依赖。设计索引时建议尽量用联合索引替代多单列索引。两个单列索引占用的空间和写入开销并不比一个联合索引小多少但联合索引能同时满足等值、范围和排序场景。如果表上已经有多个单列索引而且这些索引经常被单独使用可以考虑把其中高频组合的索引合并为联合索引。5.6 索引能提升查询速度为什么不能无限添加每增加一个索引意味着每次 INSERT、UPDATE、DELETE 都要额外维护对应的 B 树结构。写入性能会随着索引数量增加而下降。索引本身也占用磁盘空间和内存缓冲池空间索引过多会导致缓冲池命中率下降反而拖慢查询。维护索引还有一个隐性成本数据变更时需要更新所有相关索引如果索引设计不合理写入操作可能触发大量页分裂和随机写。因此生产中索引数量的经验法则是单表索引控制在 5 个以内核心高频查询的联合索引优先低频查询不轻易加索引。6. MySQL 调优实战中的常见坑以及一套可复用的排查清单这一章把实际项目中容易出现的问题集中梳理一下每一类问题都有具体现象、产生原因和解决方式。6.1 常见坑一统计信息不准导致的错误执行计划现象是一条 SQL 之前执行很快某天数据量增长后突然变慢或者 EXPLAIN 显示的预估行数与实际严重不符。原因可能是表经过大量增删改后InnoDB 的统计信息没有及时更新。解决方式是执行 ANALYZE TABLE 重新收集统计信息然后再次 EXPLAIN 观察执行计划是否变化。如果变化后恢复正常说明是统计信息问题。如果在生产环境反复出现可以调整 innodb_stats_auto_recalc 相关参数并考虑在业务低峰期定期 ANALYZE 大表。6.2 常见坑二隐式字符集不一致导致的关联查询索引失效现象是多表 JOIN 时关联字段明明有索引执行计划却显示全表扫描。常见原因是两张表的关联字段字符集或排序规则不一致比如一张表是 utf8mb4另一张表是 utf8mb4_general_ci或者字段类型不一致导致 MySQL 需要做隐式转换索引随即失效。检查方式是执行SHOW CREATE TABLE table_a; SHOW CREATE TABLE table_b;确认关联字段的类型、字符集和排序规则是否一致。解决方式是统一字符集和字段类型推荐都使用 utf8mb4。如果字段一个是 VARCHAR(64) 一个是 VARCHAR(32)虽然类型兼容但长度不同也可能触发转换建议保持一致。6.3 常见坑三只关注 SQL 本身忽略了锁等待和事务问题现象是一条 SQL 执行计划没有问题索引也走对了但执行时间仍然很长。此时要检查锁等待。通过 performance_schema 可以查看是否存在行锁等待SELECT THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.data_locks;如果查询长时间等待其他事务释放行锁需要考虑业务逻辑中是否事务过大、是否有长事务未提交、是否存在同一行的高频更新。慢 SQL 优化不只是索引问题事务粒度、隔离级别和锁竞争同样需要排查。6.4 常见坑四生产环境直接执行大表 DDL导致主从延迟生产环境的大表加字段或者加索引如果直接执行 ALTER TABLE整个过程会长时间持有元数据锁可能阻塞线上 DML同时主从复制会延迟。可以使用在线 DDL 工具MySQL 8.0 中使用 INPLACE 算法加索引通常不需要复制全表数据但 5.7 版本中部分操作仍然有风险。实际生产中建议使用 gh-ost 或 pt-online-schema-change 这类工具在业务低峰期执行变更。6.5 学习环境与生产环境的调优差异学习环境中数据量几千行或几万行无论如何优化都不明显因为全表扫描在这个数据量下本来就很快。很多人因此产生“索引没用”的错觉。生产环境数据量达到百万行以后索引的设计好坏会直接反映在接口响应时间上。学习环境可以这样快速验证索引效果创建一张 100 万行的测试表插入随机数据。先执行无索引的等值查询记录耗时。创建索引后再次执行记录耗时。使用 EXPLAIN 对比访问类型和扫描行数。生产环境的验证方式则完全不同。不能直接在生产库随意加索引测试需要先在测试环境模拟数据分布、执行 EXPLAIN 观察执行计划、对比主从延迟、评估索引占用空间最后在业务低峰期通过在线 DDL 工具执行变更并持续监控慢查询数量和锁等待情况。6.6 MySQL 调优排查清单这套清单可以直接用于日常慢 SQL 排查和索引优化检查项操作预期结果慢 SQL 定位查看 slow_log 或 performance_schema找到具体 SQL 和执行耗时字段类型检查SHOW CREATE TABLE确认关联字段类型、字符集、排序规则一致EXPLAIN 分析EXPLAIN SELECT ...type 不为 ALLkey 不为 NULLkey_len 分析对照索引字段长度确认联合索引用到了几个字段索引失效检查检查函数、隐式转换、前缀通配符无失效场景覆盖索引检查对比 select 字段与索引字段尽量使用覆盖索引排序分组检查观察 Extra 列无 Using filesort、Using temporary统计信息检查ANALYZE TABLE执行计划预估行数与实际匹配锁等待检查查询 data_locks无长时间锁等待大表 DDL 检查使用在线 DDL 工具低峰期执行无主从延迟和无锁阻塞建议在项目开发阶段就建立索引设计评审机制。每张表的索引不只是建出来就行要在新查询上线前通过 EXPLAIN 确认执行计划在代码 review 时同步检查 SQL 是否可能造成全表扫描同时建立慢查询监控让问题在用户反馈之前暴露。7. 继续深入的方向和最有价值的练习建议MySQL 索引和 SQL 优化是一个覆盖面很广的领域这条主线学完之后可以继续向以下几个方向延伸数据库事务与锁机制、MySQL 主从复制与高可用架构、分库分表与中间件选型、执行计划深层优化、InnoDB 缓冲池原理、以及如何用 sys schema 和 performance_schema 做系统级诊断。对于刚学完这部分内容的开发者最有价值的练习是找一张真实业务表导出百万行测试数据然后模拟线上高频查询逐一分析执行计划尝试用不同索引设计提升性能并将结果记录成文档。这个练习能帮助建立从 SQL 到索引再到执行计划的整体直觉。另一个值得练习的场景是索引失效排查。主动写一批会触发索引失效的 SQL比如函数包裹索引列、隐式类型转换、违反最左前缀、LIKE 前缀通配符等然后通过 EXPLAIN 观察 type 列从 ref 变成 ALL 的过程。亲手验证过这些现象比背十遍面试题更有效。生产环境中最重要的一条经验是不要等到慢查询出现才开始设计索引。应该在表结构设计阶段就依据业务查询模式设计索引在测试环境用接近生产的数据量验证执行计划在发布流程中加入 SQL 检查和慢查询监控。索引优化不是一个一次性动作而是伴随整个系统生命周期持续进行的性能管理。
MySQL索引与SQL优化：从B+树到慢SQL调优实践
在 MySQL 数据库的日常使用中索引和 SQL 优化往往是决定系统性能的关键分水岭。很多开发者在项目初期只关注功能能否跑通等数据量增长到百万、千万级别后才发现一条慢 SQL 就能拖垮整个服务。索引、B 树、联合索引、索引优化、SQL 优化、MySQL 调优这些概念并不是孤立的知识点而是构成一条完整性能排查链路的必备技能。这篇文章会从 InnoDB 存储引擎的真实数据结构出发逐步讲清楚 B 树为什么能成为索引的默认选择联合索引在底层如何排列索引下推解决了什么问题再结合慢 SQL 定位、执行计划分析和真实调优案例给出可以直接用到生产环境的优化方法最后整理高频面试题和一套可复用的排查清单。这套内容的定位是给已经会写基础 SQL、做过简单项目的开发者目标是让读者看完后能自己分析一条慢 SQL 是索引失效还是查询设计不合理能设计出适合业务场景的联合索引能解释清楚为什么某条语句在数据量变大后会突然变慢也能在面对 MySQL 性能问题时有一条清晰的排查路径。1. 先搞清楚索引到底是什么以及 InnoDB 为什么要用 B 树很多资料一上来就讲 B 树的定义但如果不先理解索引在存储引擎中的真实角色后面看执行计划、看索引失效场景都会觉得抽象。要理解索引先得理解 InnoDB 的表数据在磁盘上是怎么组织的。1.1 从 InnoDB 表空间说起数据页和行记录InnoDB 存储引擎将数据按页Page存储默认每页大小为 16KB。页是 InnoDB 读写磁盘的最小单位也就是说即使你只需要查询一行记录InnoDB 也会把这行记录所在的一整页数据加载到内存缓冲池中。表空间中的页通过页号进行管理页之间通过双向链表连接页内的行记录通过单向链表连接。这个设计带来一个直接后果在没有索引的情况下如果要查询某个非主键字段InnoDB 只能从第一个数据页开始逐页读取逐行比对。这个操作被称为全表扫描Full Table Scan。当表只有几千行时全表扫描没有问题当表有千万行、数据占用几个 GB 时全表扫描会产生大量磁盘 I/O性能急剧下降。索引的本质就是额外维护一套更小的、排好序的数据结构通过这套结构可以快速定位到目标记录在哪些数据页中从而避免全表扫描。1.2 为什么是 B 树而不是二叉树、红黑树或哈希表常见的索引数据结构有哈希表、二叉树、红黑树、B 树和 B 树。要理解 InnoDB 为什么选择 B 树需要从磁盘 I/O 和范围查询两个角度来分析。首先是磁盘 I/O 模型。访问磁盘一次的时间大约是毫秒级而内存访问是纳秒级。索引结构每往下一层就可能产生一次磁盘 I/O。因此索引树的高度直接决定了查询的磁盘 I/O 次数。二叉树在最坏情况下高度等于节点数红黑树虽然平衡但高度仍然接近 O(log2N)当数据量达到百万级时树高在 20 层左右也就是说一次查询可能触发 20 次磁盘 I/O。B 树和 B 树通过让每个节点存储多个键值和多个子节点指针显著降低了树高。InnoDB 中一个数据页 16KB如果每个节点是一个页那么一个节点大概能存储数百个键值三到四层的 B 树就能支撑千万级数据量。其次是范围查询能力。哈希索引在等值查询上可以达到 O(1) 复杂度但无法支持范围查询、排序和前缀匹配。数据库中最常见的查询是范围查询比如 WHERE create_time BETWEEN 和 ORDER BY哈希索引对此无能为力。B 树虽然在等值查询和范围查询上都不差但 B 树的每个节点都存储完整的数据相同数据量下树更高而且 B 树的中序遍历需要跨层跨节点访问相比 B 树不够紧凑。最后是 B 树的独特设计。B 树的所有数据记录都存储在叶子节点非叶子节点只存储键值和子节点指针因此非叶子节点能容纳更多键值树更矮。同时叶子节点之间通过双向链表连接非常适合范围查询和排序扫描。InnoDB 的主键索引和二级索引底层都是 B 树结构。1.3 聚簇索引和二级索引为什么回表是性能瓶颈InnoDB 中索引可以分为两大类聚簇索引Clustered Index和二级索引Secondary Index。聚簇索引的叶子节点直接存储整行数据。InnoDB 表本身就是一个以主键为 B 树键值的聚簇索引。如果没有显式定义主键InnoDB 会选择一个非空唯一索引作为聚簇索引如果也没有InnoDB 会生成一个隐藏的 ROW_ID 作为聚簇索引键。二级索引的叶子节点存储的是索引字段值和主键值而不是整行数据。因此通过二级索引查询时会先在二级索引 B 树中找到对应的主键值再根据主键值到聚簇索引中查找完整行记录。这个根据主键值到聚簇索引中再查一次的过程称为回表Table Lookup。回表操作不是免费的。如果查询需要访问大量二级索引记录每条记录都触发一次回表会产生大量随机 I/O。理解这一点后后面讲覆盖索引和索引下推时就能明白它们为什么能提升性能。注意在 InnoDB 中二级索引叶子节点存储的是主键值而不是行指针。这意味着主键越短二级索引占用的空间越小查询时的 I/O 成本也越低。使用过长的随机主键比如 UUID 字符串会让每个二级索引都变得臃肿这是很多表性能差的隐藏原因之一。2. 联合索引的底层排列方式决定了你的查询能不能走索引联合索引也叫复合索引是指在一个索引中包含多个字段比如 INDEX idx_user_status (user_id, status)。很多人只知道创建联合索引时要把区分度高的字段放前面但要真正理解联合索引的优化和失效场景必须先搞懂联合索引在 B 树中是怎么排列的。2.1 联合索引的键值排序规则先左后右逐列比较联合索引的每个节点存储的不是单个字段值而是多个字段的复合键值。B 树对复合键值的比较规则是先按第一个字段排序如果第一个字段相同再按第二个字段排序依次类推。举个例子。假设有表 user_orderCREATE TABLE user_order ( id INT PRIMARY KEY, user_id INT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2), KEY idx_user_status (user_id, status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;联合索引 idx_user_status 的叶子节点中所有记录先按 user_id 升序排列在 user_id 相同的情况下再按 status 升序排列。这个排序规则是理解联合索引最核心的点。如果查询条件是WHERE user_id 100 AND status 1MySQL 可以在联合索引中精确定位到目标键值如果查询条件是WHERE status 1由于索引中第一列是 user_idstatus 的排序是建立在 user_id 相同的前提下的MySQL 无法直接跳过 user_id 去按 status 查找因此该查询无法使用这个联合索引。2.2 最左前缀原则为什么跳过了第一个字段就会失效最左前缀原则是联合索引使用中的核心约束。它包含两种情况。第一种是等值匹配的最左前缀。索引中的字段从左到右依次匹配查询条件中的字段。如果查询条件使用了索引的第一个字段则索引可用如果只使用第二个字段则索引失效。比如 idx_user_status 支持WHERE user_id 100但不支持WHERE status 1。第二种是范围匹配的截断效应。当查询条件中出现范围比较、、BETWEEN、LIKE 前缀匹配时范围条件所在的字段之后的其他索引字段都无法用于精确定位。比如查询条件是WHERE user_id 100 AND status 1 AND create_time 2024-01-01索引 idx_user_status 中 user_id 用于等值定位status 用于范围定位而 create_time 不在索引中无法继续参与索引匹配。这里要特别说明一个容易误解的场景WHERE user_id 100 AND status 1中status 使用了范围条件但是 user_id 仍然是等值条件所以这个查询可以走 idx_user_status。范围条件导致失效的是范围条件之后的其他索引字段而不是范围条件本身。2.3 索引下推减少了回表次数但改变不了索引排列规则索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化很多面试题和实际优化场景都会涉及。在没有索引下推之前InnoDB 通过二级索引定位到记录后需要先回表取出完整行再在 Server 层判断 WHERE 条件中非索引字段是否满足。有了索引下推后如果 WHERE 条件中的部分字段也存在于索引中MySQL 会在存储引擎层直接对这些索引字段进行过滤过滤通过后才回表过滤不通过就直接跳过。举个例子。假设表 user_order 上有联合索引 idx_user_status (user_id, status)查询SELECT * FROM user_order WHERE user_id 100 AND status 1;在 MySQL 5.6 之前InnoDB 根据 user_id 100 找到所有二级索引记录然后逐一回表取出完整行后再判断 status 是否等于 1。在启用索引下推后InnoDB 在遍历二级索引时直接在索引层面判断 status 是否等于 1只有等于 1 的记录才回表。如果 user_id 100 的记录有 1000 条其中 status 1 的只有 10 条那么索引下推可以把回表次数从 1000 次降到 10 次。索引下推有一个关键前提下推的字段必须存在于索引中。它不能改变联合索引的排列规则也不能让跳过了第一个字段的查询重新走索引。可以用 EXPLAIN 的 Extra 列观察索引下推是否生效如果出现 Using index condition说明索引下推被使用。在生产环境调优时可以通过对比开关优化器选项来验证效果SET optimizer_switch index_condition_pushdownoff; -- 执行查询并记录耗时 SET optimizer_switch index_condition_pushdownon; -- 再次执行查询并记录耗时3. 索引优化的落地方法先看执行计划再谈优化手段在实际项目中索引优化不是靠猜的。拿到一条慢 SQL 后第一步是看执行计划第二步是确认索引是否被使用第三步才是调整索引或改写 SQL。3.1 用 EXPLAIN 分析慢 SQL重点关注哪些列EXPLAIN 是分析查询执行计划的核心命令。EXPLAIN SELECT user_id, status, amount FROM user_order WHERE user_id 100 AND create_time 2024-01-01;执行结果中需要重点关注以下列列名关注内容常见问题type访问类型从好到差依次是 system、const、eq_ref、ref、range、index、ALLALL 说明全表扫描index 说明扫描完整索引树key实际使用的索引名称NULL 表示没有使用索引key_len使用的索引字节长度可以判断联合索引中实际用到了几个字段rowsMySQL 预估扫描的行数与真实扫描行数偏差过大时说明统计信息不准Extra附加信息Using filesort、Using temporary、Using index condition 等都有不同含义key_len 是很容易被忽略但非常有用的列。假设 idx_user_status (user_id, status)user_id 是 INT NOT NULL占用 4 字节status 是 TINYINT NOT NULL占用 1 字节。如果执行计划中 key_len 显示为 4说明只用了 user_id 一个字段如果显示为 5说明两个字段都被用到了。Extra 列中常见的问题值Using filesort文件排序通常意味着 ORDER BY 的字段没有走索引需要考虑是否可以通过调整索引顺序来消除排序。Using temporary使用了临时表常见于 GROUP BY 和 DISTINCT 操作。Using index condition索引下推生效。Using index覆盖索引查询所需字段都在索引中不需要回表。还有一个常见的误区认为 EXPLAIN 显示索引被使用就说明查询已经优化到位。实际上索引可能只被用于定位小部分数据也可能因为 select 了额外字段而导致回表数量巨大。EXPLAIN 只是优化的起点不是终点。3.2 索引失效的常见场景每一条都要能解释原理索引失效是面试和实际排查中的高频问题。以下几个场景最为常见。第一条对索引字段使用函数或表达式。比如WHERE DATE(create_time) 2024-01-01MySQL 无法直接使用 create_time 上的索引因为索引中的键值是原始时间值不是格式化后的日期值。把 SQL 改写为WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00索引就能生效。类似的还有WHERE amount 1 100、WHERE id 1 5等情况。第二条隐式类型转换。如果字段是 VARCHAR 类型查询条件写成WHERE phone 13812345678MySQL 会把字段转换为数字进行比较导致索引失效。应该写成WHERE phone 13812345678。反过来如果字段是 INT 类型条件写成WHERE id 5MySQL 会尝试把字符串转换为数字这种通常不影响索引使用但不推荐依赖隐式转换。第三条LIKE 以通配符开头。WHERE name LIKE %张无法使用索引因为 B 树的排序规则是从左到右的以通配符开头意味着无法确定匹配的起始键值。但WHERE name LIKE 张%可以走索引这属于范围匹配的一种。第四条联合索引未遵守最左前缀原则。这一点在前面已经详细解释过跳过联合索引左侧字段直接查询右侧字段索引不生效。第五条NULL 值判断和 NOT IN。WHERE name IS NULL在部分 MySQL 版本和索引配置下可能不走索引WHERE id NOT IN (1,2,3)则很可能退化为全表扫描。相比之下WHERE id IN (1,2,3)通常可以走索引。实际项目中如果业务允许尽量使用 IS NOT NULL 配合默认值来避免 NULL 判断或者统计区分度后决定是否加索引。错误写法正确写法原因DATE(create_time) 2024-01-01create_time ... AND create_time ...函数破坏索引键值的有序性phone 13812345678phone 13812345678隐式类型转换导致索引失效name LIKE %张name LIKE 张%前缀通配符无法定位起始键值WHERE status 1需要联合索引包含 status 且使用最左前缀跳过左侧字段无法索引匹配3.3 覆盖索引把查询字段放入索引直接省掉回表覆盖索引是指查询所需的所有字段都包含在同一个二级索引中这样 InnoDB 可以从二级索引直接返回数据不需要回表。在 EXPLAIN 中表现为 Extra 列为 Using index。实际业务中常见的优化方式是把高频查询的字段组合成联合索引。比如一个订单列表页经常执行SELECT user_id, status, create_time FROM user_order WHERE user_id 100 ORDER BY create_time DESC LIMIT 20;如果现有联合索引是 idx_user_status (user_id, status)这个查询虽然能通过 user_id 定位但 ORDER BY create_time 需要进行文件排序而且 select 的字段不在索引中需要回表。可以设计一个更贴合查询的联合索引ALTER TABLE user_order ADD INDEX idx_user_createtime (user_id, create_time);此时这个查询可以完全通过 idx_user_createtime 索引完成user_id 用于等值定位create_time 用于排序叶子节点上的 user_id、create_time 字段直接覆盖了 select 的字段不需要回表。Extra 列会显示 Using index查询性能显著提升。覆盖索引不是越宽越好。索引字段越多写入开销越大占用空间越大。设计覆盖索引时要结合业务中的高频查询优先覆盖 select 字段少但频率极高的场景。3.4 前缀索引长字符串字段的索引设计方案对于 VARCHAR 长字段比如用户昵称、文章标题、URL 等如果直接建立完整索引索引占用空间很大且索引树会比较臃肿。常见做法是只对字段的前 N 个字符建立索引称为前缀索引。ALTER TABLE user_profile ADD INDEX idx_nickname (nickname(10));选择前缀长度时需要平衡区分度和空间占用。区分度太低会导致大量重复键值降低索引效率区分度太高又浪费空间。可以通过统计不同前缀长度的区分度来决定SELECT COUNT(DISTINCT LEFT(nickname, 5)) AS prefix_5, COUNT(DISTINCT LEFT(nickname, 8)) AS prefix_8, COUNT(DISTINCT LEFT(nickname, 10)) AS prefix_10, COUNT(DISTINCT nickname) AS full_count FROM user_profile;用前缀区分度除以全字段区分度可以得到一个比例。常见的经验是前缀区分度达到全字段区分度的 90% 以上就基本够用。比如全字段区分度 100 万前缀 10 的区分度是 95 万那么比例是 95%可以接受。前缀索引有一个限制无法使用覆盖索引优化因为索引中只存储了字段的前 N 个字符select 该字段完整值时必须回表。这一点在面试和设计时需要说明。4. SQL 优化实战从定位慢 SQL 到改写一条查询SQL 优化不只是建索引。很多时候索引已经建好但 SQL 本身的写法导致优化器选择了错误的执行计划。这一章从慢 SQL 日志开始走一遍完整的 SQL 优化流程。4.1 如何定位慢 SQL慢查询日志和 performance_schemaMySQL 提供了慢查询日志用于记录执行时间超过阈值的 SQL。生产环境通常建议开启慢查询日志并设置阈值。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 的单位是秒设置为 2 表示执行时间超过 2 秒的 SQL 会被记录。log_queries_not_using_indexes 开启后没有使用索引的 SQL 也会被记录即使执行时间很短。查看慢日志文件的路径SHOW VARIABLES LIKE slow_query_log_file;也可以直接查询慢日志表SELECT query_time, rows_sent, rows_examined, db, sql_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;这里有一个重要的分析思路不要只看执行时间还要看 rows_examined 和 rows_sent 的比例。如果一个查询扫描了 100 万行只返回 10 行说明 SQL 写法和索引设计有优化空间。反之如果扫描行数很少但执行时间仍然很长可能是锁等待、CPU 计算复杂、函数处理大量数据等原因。除了慢查询日志performance_schema 也记录了更细粒度的 SQL 执行统计。可以查询 events_statements_summary_by_digest 表按照执行总耗时排序找到 TOP N 的 SQL 模板SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_seconds, AVG_TIMER_WAIT / 1000000000 AS avg_seconds FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;定位到具体的慢 SQL 后再通过 EXPLAIN 分析执行计划。4.2 一个真实调优案例分页查询为什么越翻越慢很多分页查询在页码小的时候很快页码变大的时候越来越慢。典型 SQL 如下SELECT id, user_id, status, amount FROM user_order ORDER BY create_time DESC LIMIT 100000, 20;这条 SQL 的问题是MySQL 需要先找到前 100020 条记录然后丢弃前 100000 条只返回最后 20 条。如果 ORDER BY create_time 没有索引还需要进行文件排序。即使 create_time 上有索引LIMIT 偏移量很大时InnoDB 仍然需要扫描并跳过大量记录。优化方式有两种。第一种是延迟关联。先通过覆盖索引获取目标 id再与原表关联获取完整数据SELECT o.id, o.user_id, o.status, o.amount FROM user_order o INNER JOIN ( SELECT id FROM user_order ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;子查询 select 只包含 id 和排序列可以在覆盖索引上完成排序和分页避免回表大量数据。第二种是基于游标的分页适合 App 端和 API 场景。客户端传入上一页最后一条记录的 create_time 或 idSQL 直接定位到该位置之后的数据SELECT id, user_id, status, amount FROM user_order WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;这种方式的扫描行数不会随页码增加而增长性能稳定。4.3 深挖执行计划filesort 和临时表到底怎么消除ORDER BY 出现 Using filesort 时说明 MySQL 需要额外做一次排序操作。优化思路通常有两个方向。第一个方向是让排序列走索引。B 树天然有序如果查询中的 WHERE 等值条件和 ORDER BY 字段构成联合索引的最左前缀那么数据读取时已经是排好序的不需要额外排序。举个例子查询SELECT * FROM user_order WHERE user_id 100 ORDER BY create_time DESC;如果索引是 idx_user_createtime (user_id, create_time)user_id 等值匹配后create_time 在索引中已经按升序排列DESC 时倒序读取即可不需要 filesort。但如果索引是 idx_user_status (user_id, status)ORDER BY create_time 无法利用索引顺序MySQL 需要将结果集加载到内存或磁盘排序这就是 Using filesort。第二个方向是减少排序数据量。如果无法通过索引消除排序可以尽量减少参与排序的字段数量和行数。比如先通过索引过滤掉大部分数据再对剩余小结果集排序比全表排序快得多。GROUP BY 出现 Using temporary 时思路类似。常见做法是确认 GROUP BY 的字段是否与索引匹配如果匹配MySQL 可以利用索引的有序性直接分组不需要临时表。4.4 优化器选择偏差用 FORCE INDEX 还是改写 SQL有时候明明有合适的索引MySQL 优化器却选择了全表扫描或其他索引。这种情况通常发生在统计信息不准确、数据分布倾斜或者查询条件选择性较低时。通过 ANALYZE TABLE 可以更新表的统计信息ANALYZE TABLE user_order;如果更新统计信息后优化器仍然做出错误选择可以尝试 FORCE INDEX 强制使用指定索引SELECT * FROM user_order FORCE INDEX (idx_user_createtime) WHERE user_id 100 ORDER BY create_time LIMIT 20;但 FORCE INDEX 是治标不治本的方法。随着数据分布变化这个索引可能不再适合而强制指定反而会让查询更慢。推荐先分析 SQL 中哪些条件的区分度最高重新设计联合索引让优化器自然选择正确路径。在生产环境如果短期必须用 FORCE INDEX要加上注释说明原因并且定期验证效果。4.5 生产环境 SQL 优化的完整检查顺序在生产环境遇到一条慢 SQL 时可以按以下顺序排查通过慢查询日志确认 SQL 文本、执行时间、扫描行数。使用 EXPLAIN 查看访问类型、使用索引、key_len、Extra。检查是否因为函数、隐式转换、前缀通配符导致索引失效。检查联合索引是否满足最左前缀原则。确认查询字段是否可以用覆盖索引避免回表。确认 ORDER BY、GROUP BY 是否能利用索引顺序消除 filesort 和 temporary。确认 LIMIT 偏移量是否过大考虑延迟关联或游标分页。检查表统计信息是否需要 ANALYZE TABLE。综合考虑索引成本和查询数量决定是否调整索引。5. MySQL 高频面试题从原理到场景一次性讲清楚MySQL 索引和优化相关的面试题考察的重点往往不是死记硬背的定义而是能否结合原理解释现象。以下几个问题在面试中出现频率最高。5.1 为什么主键通常建议用自增整数而不是 UUID这个问题要从聚簇索引的插入和维护成本来分析。InnoDB 聚簇索引的叶子节点按主键顺序排列。自增主键在插入时新记录总是追加到 B 树最右侧的叶子节点已有的叶子节点不需要移动。UUID 主键是随机字符串新记录的主键值可能落在 B 树中间的某个位置为了保持顺序InnoDB 可能需要在中间位置分裂和调整叶子节点产生额外的写入开销和页碎片。从二级索引的角度看前面说过二级索引叶子节点存储主键值。整数主键占 4 到 8 字节UUID 字符串占 36 字节同样一个二级索引使用 UUID 主键会让索引体积更大查询时的 I/O 成本更高。但也不是所有场景都适合自增主键。在分库分表场景中全局唯一 ID 通常由分布式 ID 生成器产生此时需要结合业务选择雪花算法等有序 ID 方案。对于日志表、流水表这类只有插入和归档需求的数据也可以考虑使用类似雪花 ID 的有序整数方案。5.2 什么情况下可能会慢但 EXPLAIN 显示索引被使用这是容易被忽略的面试点。EXPLAIN 显示走了索引只能说明 MySQL 能使用该索引定位数据但性能还取决于回表次数、扫描行数、扫描的索引页数量以及排序和分组操作。典型场景是二级索引的区分度太低。比如一个性别字段只有 0 和 1 两个值在其上建索引后查询WHERE gender 1可能会回表大量记录性能甚至比全表扫描还差。优化器在这种情况下可能选择全表扫描但如果没有及时更新统计信息也可能错误地选择索引。另一种情况是索引范围查询扫到了大量重复键值。比如联合索引 (user_id, status) 中 user_id 100 的记录有 10 万条即使 status 条件能过滤掉大部分但在索引下推没有启用或过滤条件不在索引中时仍然会发生大量回表。5.3 联合索引字段顺序的选择依据是什么核心原则是优先考虑等值条件字段再考虑范围条件字段最后考虑排序字段。等值条件字段放在最左侧可以最大化利用最左前缀范围条件字段放在等值字段之后因为范围条件会截断后续字段的索引匹配排序字段尽量与索引顺序一致以消除 filesort。更进一步应该按字段区分度排序。区分度高的字段放在前面可以更快地缩小扫描范围。但区分度也要结合实际查询频率如果一个区分度较低的字段是查询中的高频等值条件仍然应该放在范围字段之前。比如订单表中的高频查询是SELECT * FROM user_order WHERE status 1 AND user_id 100 ORDER BY create_time DESC;此时联合索引建议设计为 (user_id, status, create_time)而不是 (status, user_id, create_time)。因为 user_id 区分度高放在最左侧能先过滤掉大量数据status 是等值条件放在第二位create_time 用于排序放在最后。5.4 索引下推、覆盖索引和回表之间的关系怎么表达这三者经常被放在一起考察。简单表述是没有覆盖索引时二级索引查到主键后需要回表获取完整行覆盖索引让二级索引直接包含查询所需字段从而避免回表索引下推则是在回表之前先在存储引擎层利用索引字段过滤二级索引记录减少回表次数。举例说明最直观。索引 idx_status_amount (status, amount)查询SELECT amount FROM user_order WHERE status 1 AND amount 100;这个查询中status 和 amount 都在索引中select 字段 amount 也在索引中不需要回表是覆盖索引场景。由于没有 select 未索引字段索引下推在这种场景下作用不明显。如果改成 select *则需要回表此时索引下推可以先在索引中过滤 amount 100减少回表次数。5.5 如果一张表有多个单列索引MySQL 会怎么选择MySQL 优化器在多个单列索引之间通常只会选择一个扫描代价最低的索引并不会自动将多个单列索引组合成一个联合索引来使用。虽然在部分版本中会出现 Index Merge 优化但这类场景复杂且受数据分布影响大不应该依赖。设计索引时建议尽量用联合索引替代多单列索引。两个单列索引占用的空间和写入开销并不比一个联合索引小多少但联合索引能同时满足等值、范围和排序场景。如果表上已经有多个单列索引而且这些索引经常被单独使用可以考虑把其中高频组合的索引合并为联合索引。5.6 索引能提升查询速度为什么不能无限添加每增加一个索引意味着每次 INSERT、UPDATE、DELETE 都要额外维护对应的 B 树结构。写入性能会随着索引数量增加而下降。索引本身也占用磁盘空间和内存缓冲池空间索引过多会导致缓冲池命中率下降反而拖慢查询。维护索引还有一个隐性成本数据变更时需要更新所有相关索引如果索引设计不合理写入操作可能触发大量页分裂和随机写。因此生产中索引数量的经验法则是单表索引控制在 5 个以内核心高频查询的联合索引优先低频查询不轻易加索引。6. MySQL 调优实战中的常见坑以及一套可复用的排查清单这一章把实际项目中容易出现的问题集中梳理一下每一类问题都有具体现象、产生原因和解决方式。6.1 常见坑一统计信息不准导致的错误执行计划现象是一条 SQL 之前执行很快某天数据量增长后突然变慢或者 EXPLAIN 显示的预估行数与实际严重不符。原因可能是表经过大量增删改后InnoDB 的统计信息没有及时更新。解决方式是执行 ANALYZE TABLE 重新收集统计信息然后再次 EXPLAIN 观察执行计划是否变化。如果变化后恢复正常说明是统计信息问题。如果在生产环境反复出现可以调整 innodb_stats_auto_recalc 相关参数并考虑在业务低峰期定期 ANALYZE 大表。6.2 常见坑二隐式字符集不一致导致的关联查询索引失效现象是多表 JOIN 时关联字段明明有索引执行计划却显示全表扫描。常见原因是两张表的关联字段字符集或排序规则不一致比如一张表是 utf8mb4另一张表是 utf8mb4_general_ci或者字段类型不一致导致 MySQL 需要做隐式转换索引随即失效。检查方式是执行SHOW CREATE TABLE table_a; SHOW CREATE TABLE table_b;确认关联字段的类型、字符集和排序规则是否一致。解决方式是统一字符集和字段类型推荐都使用 utf8mb4。如果字段一个是 VARCHAR(64) 一个是 VARCHAR(32)虽然类型兼容但长度不同也可能触发转换建议保持一致。6.3 常见坑三只关注 SQL 本身忽略了锁等待和事务问题现象是一条 SQL 执行计划没有问题索引也走对了但执行时间仍然很长。此时要检查锁等待。通过 performance_schema 可以查看是否存在行锁等待SELECT THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS FROM performance_schema.data_locks;如果查询长时间等待其他事务释放行锁需要考虑业务逻辑中是否事务过大、是否有长事务未提交、是否存在同一行的高频更新。慢 SQL 优化不只是索引问题事务粒度、隔离级别和锁竞争同样需要排查。6.4 常见坑四生产环境直接执行大表 DDL导致主从延迟生产环境的大表加字段或者加索引如果直接执行 ALTER TABLE整个过程会长时间持有元数据锁可能阻塞线上 DML同时主从复制会延迟。可以使用在线 DDL 工具MySQL 8.0 中使用 INPLACE 算法加索引通常不需要复制全表数据但 5.7 版本中部分操作仍然有风险。实际生产中建议使用 gh-ost 或 pt-online-schema-change 这类工具在业务低峰期执行变更。6.5 学习环境与生产环境的调优差异学习环境中数据量几千行或几万行无论如何优化都不明显因为全表扫描在这个数据量下本来就很快。很多人因此产生“索引没用”的错觉。生产环境数据量达到百万行以后索引的设计好坏会直接反映在接口响应时间上。学习环境可以这样快速验证索引效果创建一张 100 万行的测试表插入随机数据。先执行无索引的等值查询记录耗时。创建索引后再次执行记录耗时。使用 EXPLAIN 对比访问类型和扫描行数。生产环境的验证方式则完全不同。不能直接在生产库随意加索引测试需要先在测试环境模拟数据分布、执行 EXPLAIN 观察执行计划、对比主从延迟、评估索引占用空间最后在业务低峰期通过在线 DDL 工具执行变更并持续监控慢查询数量和锁等待情况。6.6 MySQL 调优排查清单这套清单可以直接用于日常慢 SQL 排查和索引优化检查项操作预期结果慢 SQL 定位查看 slow_log 或 performance_schema找到具体 SQL 和执行耗时字段类型检查SHOW CREATE TABLE确认关联字段类型、字符集、排序规则一致EXPLAIN 分析EXPLAIN SELECT ...type 不为 ALLkey 不为 NULLkey_len 分析对照索引字段长度确认联合索引用到了几个字段索引失效检查检查函数、隐式转换、前缀通配符无失效场景覆盖索引检查对比 select 字段与索引字段尽量使用覆盖索引排序分组检查观察 Extra 列无 Using filesort、Using temporary统计信息检查ANALYZE TABLE执行计划预估行数与实际匹配锁等待检查查询 data_locks无长时间锁等待大表 DDL 检查使用在线 DDL 工具低峰期执行无主从延迟和无锁阻塞建议在项目开发阶段就建立索引设计评审机制。每张表的索引不只是建出来就行要在新查询上线前通过 EXPLAIN 确认执行计划在代码 review 时同步检查 SQL 是否可能造成全表扫描同时建立慢查询监控让问题在用户反馈之前暴露。7. 继续深入的方向和最有价值的练习建议MySQL 索引和 SQL 优化是一个覆盖面很广的领域这条主线学完之后可以继续向以下几个方向延伸数据库事务与锁机制、MySQL 主从复制与高可用架构、分库分表与中间件选型、执行计划深层优化、InnoDB 缓冲池原理、以及如何用 sys schema 和 performance_schema 做系统级诊断。对于刚学完这部分内容的开发者最有价值的练习是找一张真实业务表导出百万行测试数据然后模拟线上高频查询逐一分析执行计划尝试用不同索引设计提升性能并将结果记录成文档。这个练习能帮助建立从 SQL 到索引再到执行计划的整体直觉。另一个值得练习的场景是索引失效排查。主动写一批会触发索引失效的 SQL比如函数包裹索引列、隐式类型转换、违反最左前缀、LIKE 前缀通配符等然后通过 EXPLAIN 观察 type 列从 ref 变成 ALL 的过程。亲手验证过这些现象比背十遍面试题更有效。生产环境中最重要的一条经验是不要等到慢查询出现才开始设计索引。应该在表结构设计阶段就依据业务查询模式设计索引在测试环境用接近生产的数据量验证执行计划在发布流程中加入 SQL 检查和慢查询监控。索引优化不是一个一次性动作而是伴随整个系统生命周期持续进行的性能管理。