MySQL 慢 SQL：排查流程、执行计划分析与根本原因定位

在 MySQL 数据库的生产运维中，慢 SQL 是导致数据库性能下降、业务请求超时、服务器资源耗尽的核心诱因之一，也是后端开发与 DBA 日常工作中必须攻克的重点问题。慢 SQL 的本质是SQL 语句执行时间超过阈值（默认 1 秒，由long_query_time配置），其背后既可能是索引设计缺陷、索引失效等基础问题，也可能是 MySQL 优化器选错索引、锁竞争、SQL 逻辑复杂等深层原因。

本文将从慢 SQL 的核心定义出发，梳理标准化的线上排查流程，重点讲解EXPLAIN执行计划的全字段解读，针对未用上索引和用上索引但仍慢两种核心场景做精细化根因分析，并给出可落地的优化方案，同时覆盖慢 SQL 的监控、预防体系搭建，形成 “排查 - 分析 - 优化 - 预防” 的完整闭环，内容适配生产环境实操与面试备考。

一、慢 SQL 基础认知

1. 慢 SQL 的定义与判定标准

慢 SQL 是指执行时间超过 MySQL 配置的慢查询阈值的 SQL 语句，MySQL 默认慢查询阈值为1秒，可通过参数long_query_time自定义（支持小数，如 0.5 秒）。

  • 查看当前阈值：show variables like 'long_query_time';
  • 临时修改阈值：set global long_query_time=0.5;（需重新连接会话生效）
  • 永久修改：在my.cnf中配置long_query_time=0.5，重启 MySQL。

注意：MySQL 中慢查询的时间统计是实际执行时间，不包含锁等待时间，但锁等待会间接导致 SQL 执行超时，需与纯慢 SQL 区分分析。

2. 慢 SQL 的核心危害

  • 占用数据库核心资源：慢 SQL 会消耗大量的 CPU、IO、内存资源，导致正常 SQL 的执行被抢占，数据库整体 QPS 下降；
  • 引发连锁反应：单条慢 SQL 可能导致数据库连接数堆积，进而引发应用服务线程阻塞，最终导致整个业务系统不可用；
  • 加剧锁竞争：慢 SQL 的执行时间长，会长期持有锁资源，引发锁等待、死锁等问题，进一步恶化数据库性能；
  • 影响数据一致性：部分慢 SQL（如大表批量更新）可能导致主从复制延迟，影响读写分离架构的数据一致性。

3. 慢 SQL 的常见触发场景

慢 SQL 并非孤立存在，其触发场景具有明显的规律性，主要集中在以下几类：

  • 大表查询 / 更新未使用索引，导致全表扫描；
  • 索引设计不合理（如低区分度索引、冗余索引），或 SQL 写法不当导致索引失效；
  • MySQL 优化器因统计信息过期等原因选错索引，导致执行计划偏离最优；
  • SQL 逻辑复杂（如多表联查、子查询嵌套、聚合函数滥用），执行计划低效；
  • 大表做批量操作（如批量插入、更新、删除），未做分批次处理；
  • 热点数据查询 / 更新引发锁竞争，间接导致 SQL 执行时间变长；
  • 数据库服务器资源不足（如 CPU、IO 瓶颈），导致所有 SQL 执行变慢。

二、MySQL 慢 SQL 标准化线上排查流程

生产环境中遇到慢 SQL 问题，切忌盲目优化，需遵循 “先恢复业务，再定位根因，最后优化解决” 的原则，通过标准化流程快速定位问题，避免因操作不当加剧性能问题。以下是通用的慢 SQL 排查流程，适配所有 MySQL 版本（5.7/8.0 为主）。

第一步：开启慢查询日志，捕获慢 SQL

MySQL 默认关闭慢查询日志，需先开启日志功能，才能捕获到具体的慢 SQL 语句，这是排查的基础。

1. 开启慢查询日志（临时生效，重启后丢失）
-- 开启慢查询日志
set global slow_query_log=ON;
-- 指定慢查询日志存储路径（需保证MySQL有写入权限）
set global slow_query_log_file='/var/log/mysql/slow_query.log';
-- 开启记录未使用索引的SQL（关键：即使未达慢阈值，也记录无索引SQL）
set global log_queries_not_using_indexes=ON;
-- 关闭日志中重复SQL的合并（便于精准分析）
set global log_slow_admin_statements=OFF;
2. 永久开启慢查询日志（my.cnf 配置）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
log_slow_admin_statements = 0
slow_query_log_always_write_time = 0.5

配置完成后重启 MySQL：systemctl restart mysqld。

3. 快速查看慢 SQL（无需打开日志文件）

通过mysqldumpslow工具可快速统计慢查询日志中的核心信息，避免手动分析大日志文件：

# 查看执行次数最多的10条慢SQL
mysqldumpslow -s c -n 10 /var/log/mysql/slow_query.log
# 查看执行时间最长的10条慢SQL
mysqldumpslow -s t -n 10 /var/log/mysql/slow_query.log
# 查看返回结果集最大的10条慢SQL
mysqldumpslow -s r -n 10 /var/log/mysql/slow_query.log

参数说明：-s指定排序规则（c：执行次数，t：执行时间，r：结果集行数）；-n指定显示条数。

第二步：紧急恢复业务，缓解数据库压力

若慢 SQL 已导致数据库资源耗尽（如 CPU100%、连接数满），需先执行紧急操作恢复业务，再做根因分析，避免业务长时间不可用。

  1. 终止慢 SQL 进程：通过show processlist;找到慢 SQL 的id，执行kill id;终止进程（确认业务可中断，避免数据不一致）；

    • 重点关注State列（如Sending data、Waiting for table lock）、Time列（执行时间）、Info列（具体 SQL）；

  2. 临时限流 / 降级：通知应用端对相关业务做限流或降级，减少对数据库的请求，缓解资源占用；

  3. 临时调整数据库参数：如增大join_buffer_size、sort_buffer_size等缓存参数，临时提升 SQL 执行效率（仅作紧急处理，后续需还原并优化 SQL）。

第三步：使用 EXPLAIN 分析执行计划，定位核心问题

捕获到慢 SQL 后，通过EXPLAIN（或EXPLAIN ANALYZE，MySQL8.0.18 + 支持，会实际执行并返回真实执行计划）命令分析其执行计划，这是慢 SQL 排查的核心步骤，能直接看出 SQL 是否使用索引、使用了哪个索引、数据扫描方式、联查方式等关键信息。

  • 用法：EXPLAIN + 慢SQL语句;
  • 示例：EXPLAIN SELECT * FROM t_order WHERE order_no='20240101001' AND create_time > '2024-01-01';

执行后会返回一张包含id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra共 10 个字段的结果集，每个字段都对应执行计划的关键信息，后续会单独做全字段解读。

第四步：结合数据库状态，做根因补充分析

EXPLAIN执行计划能解决 80% 的慢 SQL 问题，但部分场景（如锁竞争、优化器选错索引、服务器资源瓶颈）需要结合数据库当前状态做补充分析：

  1. 查看数据库资源使用情况：通过top、iostat、free命令查看服务器的 CPU、IO、内存使用率，确认是否存在资源瓶颈；
  2. 查看锁持有与等待情况：通过select * from performance_schema.data_locks;（MySQL8.0）或show engine innodb status;查看是否存在锁竞争、长事务，确认慢 SQL 是否由锁等待导致；
  3. 查看 MySQL 优化器统计信息：通过ANALYZE TABLE 表名;更新表的统计信息，确认是否因统计信息过期导致优化器选错索引；
  4. 查看连接数与会话状态：通过show full processlist;查看当前数据库的连接数，是否存在大量相同的慢 SQL 并发执行，导致资源抢占。

第五步：制定并实施优化方案，验证优化效果

根据执行计划和补充分析的结果，定位慢 SQL 的根因后，制定针对性的优化方案并实施，优化完成后需通过执行时间、扫描行数、资源占用三个维度验证优化效果：

  1. 执行优化后的 SQL，查看实际执行时间是否降至阈值以下；
  2. 再次执行EXPLAIN，确认执行计划已优化（如使用了合适的索引、扫描行数大幅减少）；
  3. 查看数据库资源使用率，确认 CPU、IO 等资源占用是否明显下降。

第六步：复盘问题，完善预防体系

优化完成后，需对慢 SQL 问题做复盘，分析问题产生的原因（如开发规范未落地、SQL 审核缺失、索引设计不合理），并完善慢 SQL 的预防体系，避免同类问题再次发生。

三、EXPLAIN 执行计划全字段深度解读

EXPLAIN执行计划的 10 个字段是慢 SQL 分析的 “密码本”，掌握每个字段的含义和取值范围，才能精准定位问题。以下按重要程度从高到低解读每个字段，重点标注高频问题取值和优化判断标准。

1. type：访问类型（核心字段，直接决定 SQL 性能）

表示 MySQL 在表中找到所需记录的数据扫描方式，取值从优到劣依次为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。关键判断：至少要达到range级别，最优为const/eq_ref/ref级别，若为ALL（全表扫描）或index（全索引扫描），则表示 SQL 未使用合适的索引，是慢 SQL 的核心诱因。

  • const：通过主键 / 唯一索引的等值查询，表中只有一条匹配记录，MySQL 会将其作为常量处理，性能最优；
  • eq_ref：多表联查时，被联查表使用主键 / 唯一索引的等值匹配，每次联查只返回一条记录，性能仅次于 const；
  • ref：通过普通索引的等值查询，匹配多条记录，是单表查询中较优的访问类型；
  • range：索引范围查询，如>、<、BETWEEN、IN等，使用索引但扫描部分索引记录；
  • index：全索引扫描，遍历整个索引树，比 ALL 优（索引文件比数据文件小），但仍属于全量扫描；
  • ALL：全表扫描，遍历整个表的所有数据，性能最差，必须优化。

2. key：实际使用的索引（核心字段，判断是否用上索引 / 是否选错索引）

表示 MySQL实际执行时使用的索引名称，若为NULL，则表示未使用任何索引。

  • 与possible_keys的区别：possible_keys是 MySQL 优化器认为可能适用的索引集合，key是实际使用的索引，若possible_keys非空而key为 NULL，说明索引失效或优化器认为走索引效率更低；
  • 若key的值与预期不符，说明MySQL 优化器选错了索引，是慢 SQL 的常见深层原因。

3. rows：预估扫描行数（核心字段，判断扫描范围是否过大）

表示 MySQL 优化器预估需要扫描的记录行数，该值越接近实际返回行数，执行计划越优，若预估行数远大于实际返回行数，说明表的统计信息过期，需执行ANALYZE TABLE 表名;更新。关键判断：若rows值达到数万甚至数十万，即使使用了索引，也可能因扫描行数过多导致慢 SQL，需进一步优化索引（如建立联合索引）。

4. Extra：额外信息（核心字段，判断索引是否被充分利用 / 是否有额外操作）

表示 MySQL 执行 SQL 时的额外处理逻辑，包含大量关键信息，高频出现的异常取值是慢 SQL 的重要信号，重点关注以下取值：

【性能差的高频取值（必须优化）】
  • Using filesort：MySQL 无法通过索引完成排序，需在内存 / 磁盘中做文件排序，排序数据量大会导致慢 SQL，常见于ORDER BY字段未建立索引；
  • Using temporary：MySQL 需要创建临时表来存储中间结果，常见于GROUP BY字段未建立索引、多表联查的排序 / 分组，临时表会消耗大量内存和 IO，性能极差；
  • Using where：仅通过WHERE条件过滤数据，未使用索引，通常与type=ALL配合出现，属于全表扫描；
  • Using index condition：索引下推（ICP），看似使用了索引，但实际需要回表过滤部分数据，索引未被充分利用，常见于联合索引字段未全部匹配；
  • Range checked for each record (index map: N) ：MySQL 无法确定合适的索引，需要为每条记录做索引范围检查，性能极差，通常是索引设计不合理导致。
【性能优的取值（理想状态）】
  • Using index：覆盖索引，MySQL 仅通过索引就能获取所需数据，无需回表查询数据行，性能最优，是索引优化的终极目标之一；
  • Using where; Using index：既使用了索引过滤数据，又使用了覆盖索引，无需回表，是单表查询的理想执行计划。

5. id：查询序列号

表示 SQL 中查询操作的执行顺序，取值为数字，规则如下：

  • id 相同：查询操作按从上到下的顺序执行；
  • id 不同：id 值越大，执行优先级越高；
  • id 为 NULL：表示该操作是其他操作的辅助，如聚合函数、分组操作。作用：主要用于分析多表联查、子查询的执行顺序，判断是否因执行顺序不合理导致慢 SQL（如子查询被优先执行，导致大量数据扫描）。

6. select_type：查询类型

表示 SQL 的查询类型，用于区分普通查询、子查询、联查、聚合查询等，核心取值如下：

  • SIMPLE：简单查询，无子查询、无联查，最常见；
  • PRIMARY：主查询，包含子查询或联查时的外层查询；
  • SUBQUERY：子查询中的内层查询，结果不依赖外部查询；
  • DERIVED：派生表查询，即FROM后的子查询，MySQL 会先将其执行结果存入临时表；
  • MERGE：合并子查询，优化器将子查询与主查询合并为一个查询，性能较优；
  • DEPENDENT SUBQUERY：依赖外部查询的子查询，外层查询执行一次，内层查询执行一次，性能较差，需优化为联查。作用：判断是否因子查询嵌套过深、派生表未优化导致慢 SQL，通常建议将子查询优化为 JOIN 联查，提升执行效率。

7. table：表名

表示当前执行计划对应的表名，若为派生表或联查，会显示为<derivedN>（N 为 id 值）或<subqueryN>。

8. possible_keys：可能使用的索引

表示 MySQL 优化器认为可能适用的索引集合，基于表的统计信息和 SQL 的 WHERE 条件得出，若为NULL，说明表中无适用的索引，需新建索引。

9. key_len：实际使用的索引长度

表示 MySQL实际使用的索引字段的长度，单位为字节，该值能反映出联合索引的使用情况（是否使用了联合索引的所有字段）。作用：判断联合索引是否被充分利用，例如联合索引idx_order_no_create_time (order_no, create_time)的key_len若仅等于order_no的字段长度，说明仅使用了联合索引的第一个字段，create_time未被利用。计算规则：需结合字段类型（如 varchar (32) 为 32*3+2=98 字节，int 为 4 字节）、是否允许 NULL（允许则加 1 字节）、字符集（utf8mb4 为 4 字节 / 字符，utf8 为 3 字节 / 字符）计算。

10. ref：与索引比较的列或常量

表示与索引字段进行等值匹配的列名或常量，若为const，表示是常量匹配（如order_no='20240101001'）；若为列名，表是多表联查的等值匹配（如t1.order_no = t2.order_no）。作用：判断索引的匹配方式是否为等值匹配，等值匹配的索引效率远高于范围匹配。

四、慢 SQL 核心场景分析：未用上索引的 3 类根因与解决方案

通过EXPLAIN执行计划发现key=NULL（未用上索引），是慢 SQL 最常见的场景，其根因可分为压根没建索引、索引失效、MySQL 认为走索引效率不高三类，三类场景的表现、根因和解决方案差异显著，需精准区分，避免盲目建索引。

场景 1：压根没建索引（最基础，占比约 30%）

核心表现

EXPLAIN中possible_keys=NULL、key=NULL、type=ALL，WHERE/ORDER BY/GROUP BY的字段在表中未建立任何索引，MySQL 只能做全表扫描。

典型示例
-- t_order表仅主键id有索引，order_no和create_time无索引
SELECT * FROM t_order WHERE order_no='20240101001' AND create_time > '2024-01-01';
-- EXPLAIN结果：possible_keys=NULL，key=NULL，type=ALL
根本原因

开发人员在创建表时，未根据业务查询场景建立合适的索引；或新增业务查询后，未及时补充索引。

解决方案：按需建立合适的索引

根据 SQL 的查询条件（WHERE）、排序条件（ORDER BY）、分组条件（GROUP BY）建立单列索引或联合索引，遵循最左匹配原则，优先建立联合索引（减少索引数量，提升索引利用率）。

  • 示例优化：为t_order表建立联合索引idx_order_no_create_time (order_no, create_time)；
  • 建索引原则：高频查询的字段优先建，低区分度的字段不单独建（如性别、状态，区分度低于 10% 的字段单独建索引无意义）。

场景 2：索引失效（最常见，占比约 50%）

核心表现

EXPLAIN中possible_keys非空（表中有适用的索引）、key=NULL（实际未使用），type=ALL或range，表中有索引但因 SQL 写法不当导致索引失效，这是未用上索引的最主要原因。

索引失效的 10 类典型场景（开发必记，面试高频）

索引失效的核心原则：MySQL 优化器认为使用索引的成本高于全表扫描，或无法通过索引快速定位数据，以下是生产环境中最常见的 10 类索引失效场景，均为开发规范的重点禁止项：

  1. WHERE 条件中对索引字段做函数操作：如DATE(create_time) = '2024-01-01'、SUBSTRING(order_no,1,8) = '20240101'；
  2. WHERE 条件中对索引字段做隐式类型转换：如索引字段为 int 类型，查询时传入字符串（id='123'）；索引字段为 datetime 类型，查询时传入字符串（create_time='2024-01-01'，未加时分秒）；
  3. WHERE 条件中使用OR连接非索引字段：如id=1 OR name='test'，name无索引，导致整个条件的索引失效；
  4. 模糊查询使用左模糊（LIKE '%xxx'） ：如order_no LIKE '%001'，左模糊会导致索引无法匹配，右模糊（LIKE '2024%'）可正常使用索引；
  5. WHERE 条件中使用NOT IN/!=/<> ：这类操作会导致 MySQL 放弃索引，选择全表扫描；
  6. WHERE 条件中使用IS NULL/IS NOT NULL：对于普通索引，IS NULL可使用索引，IS NOT NULL会失效；主键索引无此问题（主键非空）；
  7. 联合索引未遵循最左匹配原则：如联合索引idx_a_b_c (a,b,c)，查询条件为b=1 AND c=2，跳过了第一个字段a，导致联合索引失效；
  8. ORDER BY/GROUP BY 的字段与 WHERE 条件的索引字段不一致：如 WHERE 条件使用a字段的索引，ORDER BY 使用b字段，导致索引无法用于排序，触发Using filesort；
  9. 查询结果包含大量数据（如SELECT *） ：MySQL 认为回表的成本高于全表扫描，放弃使用索引；
  10. 索引字段的区分度过低：如性别字段（男 / 女）、状态字段（0/1），MySQL 认为使用索引的效率不如全表扫描，主动放弃索引。
解决方案：规范 SQL 写法，规避索引失效

针对上述 10 类场景，逐一制定对应的 SQL 写法规范，核心原则：让索引字段以 “原始形式” 出现在 WHERE 条件中，遵循联合索引的最左匹配原则，以下是关键解决方案：

  1. 避免对索引字段做函数操作：将函数操作转移到查询值上，如create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'替代DATE(create_time) = '2024-01-01'；
  2. 避免隐式类型转换：保证查询参数的类型与索引字段类型完全一致，如id=123替代id='123'，create_time='2024-01-01 00:00:00'替代create_time='2024-01-01'；
  3. OR连接的字段需全部建立索引：若无法为所有字段建索引，将OR改写为UNION ALL；
  4. 模糊查询使用右模糊：若业务需要左模糊，可考虑使用全文索引（FULLTEXT）或 Elasticsearch 替代；
  5. 替换NOT IN/!=：用NOT EXISTS替代NOT IN，用RANGE范围查询替代!=；
  6. 联合索引严格遵循最左匹配原则：查询条件必须包含联合索引的第一个字段，若业务需要跳过第一个字段，可单独为后续字段建立索引；
  7. SELECT指定具体字段，避免SELECT * ：优先使用覆盖索引，让 MySQL 仅通过索引就能获取所需数据，无需回表；
  8. 低区分度字段不单独建索引：可将低区分度字段与高区分度字段组合建立联合索引，提升区分度。

场景 3：MySQL 认为走索引效率不高（较隐蔽，占比约 20%）

核心表现

EXPLAIN中possible_keys非空、key=NULL，表中有适用的索引，SQL 写法也无问题，但MySQL 优化器主动放弃使用索引，选择全表扫描，这类场景较隐蔽，容易被误认为是索引失效。

典型示例
-- t_order表有索引idx_status (status)，status字段为0（未支付）/1（已支付），已支付记录占95%
SELECT * FROM t_order WHERE status=1;
-- EXPLAIN结果：possible_keys=idx_status，key=NULL，type=ALL
根本原因

MySQL 优化器会根据表的统计信息计算走索引和全表扫描的成本，当查询结果集占表总数据量的 30% 以上时，MySQL 认为回表的成本（从索引找到数据行的成本）高于全表扫描的成本，会主动放弃使用索引。

解决方案：针对性优化，让 MySQL 选择走索引

  1. 优化索引为覆盖索引：通过SELECT 具体字段替代SELECT *，让 MySQL 仅通过索引就能获取数据，无需回表，降低走索引的成本；

    • 示例优化：若查询仅需要order_no和create_time字段，建立联合索引idx_status_order_no_create_time (status, order_no, create_time)，实现覆盖索引；

  2. 强制使用索引：通过FORCE INDEX命令强制 MySQL 使用指定索引，适用于优化器判断失误的场景（谨慎使用，仅作为临时解决方案，避免优化器后续无法自适应表的统计信息变化）；

    • 示例：SELECT * FROM t_order FORCE INDEX (idx_status) WHERE status=1;

  3. 拆分表：对于数据量极大、且存在明显冷热数据划分的表，将高频查询的冷数据拆分到单独的表中，降低单表的数据量，让查询结果集的占比降低；

  4. 优化查询条件：增加额外的高区分度查询条件，缩小查询结果集的范围，让 MySQL 认为走索引更高效；

    • 示例优化：SELECT * FROM t_order WHERE status=1 AND create_time > '2024-01-01';，增加create_time条件后，查询结果集占比大幅降低，MySQL 会选择走索引。

五、慢 SQL 核心场景分析：用上索引但仍慢的 4 类根因与解决方案

通过EXPLAIN执行计划发现key非空（已用上索引），但 SQL 执行时间仍超过阈值，这类场景比 “未用上索引” 更复杂，占慢 SQL 问题的约 20%，根因主要集中在索引不恰当、MySQL 选错了索引、锁竞争、SQL 逻辑复杂四类，需逐一分析并优化。

场景 1：索引不恰当（最常见，占比约 50%）

核心表现

SQL 使用了索引，但索引设计不合理，导致扫描行数过多、需要回表、无法覆盖排序 / 分组，最终仍表现为慢 SQL，EXPLAIN中常见rows值过大、Extra为Using filesort/Using temporary/Using index condition。

索引不恰当的 3 类典型场景
  1. 使用单列索引而非联合索引：多条件查询时，使用多个单列索引，MySQL 可能使用索引合并，但效率远低于联合索引，且无法覆盖排序 / 分组；
  2. 联合索引的字段顺序不合理：联合索引的字段顺序未按区分度从高到低排列，导致索引的匹配效率低，扫描行数过多；
  3. 未建立覆盖索引：查询结果需要回表获取数据，回表操作会消耗大量的 IO 资源，尤其是大表查询时，回表成本极高。
解决方案：优化索引设计，打造最优索引

索引设计的核心原则：按 “查询频率从高到低、区分度从高到低” 设计联合索引，优先打造覆盖索引，针对上述 3 类场景的解决方案如下：

  1. 多条件查询优先建立联合索引：替代多个单列索引，遵循最左匹配原则，让 WHERE 条件、ORDER BY、GROUP BY 的字段尽可能包含在联合索引中；

    • 示例：查询条件为order_no='xxx' AND create_time>'xxx'，排序条件为update_time DESC，建立联合索引idx_order_no_create_time_update_time (order_no, create_time, update_time)，既能用于查询条件匹配，又能用于排序，避免Using filesort；

  2. 调整联合索引的字段顺序：将区分度高的字段放在联合索引的前面，区分度计算公式为：SELECT COUNT(DISTINCT 字段名)/COUNT(*) FROM 表名;，值越接近 1，区分度越高；

    • 反例：联合索引idx_status_order_no (status, order_no)，status区分度低，order_no区分度高，查询时先匹配status会导致扫描行数过多；
    • 正例：调整为idx_order_no_status (order_no, status)，先匹配高区分度的order_no，大幅减少扫描行数；

  3. 建立覆盖索引：让SELECT的查询字段全部包含在索引中，实现索引覆盖，EXPLAIN中Extra为Using index，无需回表，这是索引优化的终极目标；

    • 建覆盖索引原则：只包含业务需要的字段，不冗余，避免索引文件过大，影响索引的查询效率。

场景 2：MySQL 选错了索引（较隐蔽，占比约 20%）

核心表现

EXPLAIN中key的值与预期不符（如预期使用联合索引idx_a_b，实际使用了单列索引idx_a），rows值远大于实际扫描行数，SQL 执行时间长，表中有多个适用的索引，但 MySQL 优化器选择了效率较低的索引。

根本原因

MySQL 优化器选择索引的依据是表的统计信息和执行成本计算，选错索引的核心原因主要有两类：

  1. 统计信息过期：MySQL 的统计信息是基于表的采样数据生成的，若表中的数据发生了大量的插入、更新、删除，统计信息未及时更新，优化器会基于错误的统计信息计算执行成本，导致选错索引；
  2. 执行成本计算失误：对于复杂的多表联查、范围查询，MySQL 优化器的成本模型可能存在偏差，导致选择了效率较低的索引。
解决方案：让 MySQL 选择正确的索引

针对选错索引的场景，解决方案从临时解决到根本解决分为三级，按优先级依次使用：

一级方案：更新统计信息（首选，无副作用）

通过ANALYZE TABLE 表名;命令更新表的统计信息，让 MySQL 优化器基于最新的统计信息计算执行成本，重新选择索引，这是解决选错索引的最常用方法，无任何副作用，建议优先执行。

  • 示例：ANALYZE TABLE t_order;
二级方案：强制使用索引（临时解决，谨慎使用）

通过FORCE INDEX命令强制 MySQL 使用预期的索引，适用于统计信息更新后仍选错索引的场景，仅作为临时解决方案，因为表的统计信息后续会变化，强制索引可能导致后续 SQL 执行效率下降。

  • 示例：SELECT * FROM t_order FORCE INDEX (idx_order_no_create_time) WHERE order_no='20240101001' AND create_time > '2024-01-01';
三级方案：删除冗余索引，优化索引设计（根本解决）

若表中有大量的冗余索引（如多个单列索引与联合索引重叠），会导致 MySQL 优化器在选择索引时出现混乱，删除冗余索引是解决选错索引的根本方法。

  • 示例：表中有联合索引idx_a_b (a,b)，又有单列索引idx_a (a)，则idx_a为冗余索引，可删除；
  • 冗余索引判断原则：若索引 A 的所有字段都包含在索引 B 中，且索引 B 的第一个字段与索引 A 一致，则索引 A 为冗余索引。

场景 3：锁竞争导致的慢 SQL（间接原因，占比约 15%）

核心表现

SQL 本身的执行计划无问题（使用了合适的索引，扫描行数少），但实际执行时间远大于EXPLAIN的预估时间，show processlist;中该 SQL 的State为Waiting for row lock/Waiting for table lock，说明慢 SQL 是由锁竞争 / 锁等待导致的，而非 SQL 本身的执行效率问题。

根本原因

该 SQL 的查询 / 更新对象是热点数据，有大量的并发事务在争抢该数据的锁资源，导致当前 SQL 需要等待其他事务释放锁后才能执行，锁等待时间被计入 SQL 的执行时间，最终被判定为慢 SQL。

解决方案：解决锁竞争，减少锁等待时间

这类慢 SQL 的优化核心不是优化 SQL 本身，而是解决锁竞争问题，具体解决方案可参考《MySQL 锁机制核心问题分析》中的锁竞争优化方案，核心要点如下：

  1. 热点数据打散：通过分库分表、逻辑分桶的方式，将热点数据拆分为多个子数据，让不同的并发事务争抢不同的锁资源；
  2. 替换悲观锁为乐观锁：无强一致性要求的场景，用version版本号替代SELECT ... FOR UPDATE悲观锁，彻底避免锁竞争；
  3. 缩短事务生命周期：事务中仅包含核心 SQL，避免在事务中执行非数据库操作，减少锁的持有时间；
  4. 限流削峰：对热点数据的并发请求做限流，控制每秒的请求数，避免数据库被瞬间高并发压垮。

场景 4：SQL 逻辑复杂导致的慢 SQL（最易忽视，占比约 15%）

核心表现

SQL 使用了合适的索引，但逻辑过于复杂（如多表联查、子查询嵌套、聚合函数滥用、大数据量的 JOIN/UNION），导致 MySQL 的执行计划虽然最优，但实际执行时仍需要大量的计算和数据传输，最终表现为慢 SQL。

典型示例
  1. 多表联查：4 张及以上大表做 JOIN 联查，且联查条件未建立索引；
  2. 子查询嵌套：子查询中嵌套子查询，形成 “嵌套查询地狱”；
  3. 聚合函数滥用：对大表执行COUNT(*)、SUM()、GROUP BY等聚合操作，且未建立对应的聚合索引；
  4. 大数据量 UNION：使用UNION合并多个大结果集，且未做去重优化（UNION会去重，比UNION ALL慢）。
解决方案：简化 SQL 逻辑，优化执行方式

复杂 SQL 的优化核心原则： “能拆则拆、能简则简、能离线则离线” ，将复杂的 SQL 逻辑拆分为简单的 SQL，或转移到应用端、离线数仓处理，具体解决方案如下：

  1. 子查询优化为 JOIN 联查：MySQL 对 JOIN 联查的优化远优于子查询，尤其是依赖型子查询，将其改写为 JOIN 联查能大幅提升执行效率；

    • 反例：SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE status=1);
    • 正例：SELECT t1.* FROM t1 JOIN t2 ON t1.id=t2.t1_id WHERE t2.status=1;

  2. 拆分多表联查：将 4 张及以上的多表联查拆分为多个简单的双表联查，在应用端做数据聚合，减少数据库的计算压力；

  3. 替换聚合函数的使用方式：

    • 对大表的COUNT(*)查询，避免直接执行，可通过新增计数表的方式，在插入 / 删除数据时更新计数表，查询时直接从计数表获取结果；
    • 对大表的SUM()、AVG()等聚合操作，转移到离线数仓（如 Hive、ClickHouse）处理，生产库仅保留核心业务数据；

  4. 用UNION ALL替代UNION：若业务无需去重，使用UNION ALL（不排序、不去重）替代UNION（排序、去重），大幅提升执行效率；

  5. 避免大数据量 JOIN：JOIN 的两张表中，将小表作为驱动表（MySQL 会自动选择小表为驱动表），并为联查条件建立索引，减少数据扫描量。

六、MySQL 慢 SQL 的监控与预防体系搭建

慢 SQL 的排查和优化是 “事后补救”，而建立完善的监控与预防体系，才能从源头避免慢 SQL 的产生，实现 “事前预防、事中告警、事后快速排查” 的闭环管理，以下是生产环境中可落地的慢 SQL 监控与预防体系，适配中小团队和大厂的不同场景。

1. 慢 SQL 监控：实时监控，及时告警

搭建慢 SQL 的实时监控体系，核心是监控慢 SQL 的数量、执行时间、扫描行数、资源占用，并设置阈值告警，当慢 SQL 的指标超过阈值时，通过邮件、短信、企业微信等方式及时通知开发和 DBA。

（1）开源监控方案（中小团队首选）
  • Prometheus + Grafana + mysqld_exporter：mysqld_exporter 采集 MySQL 的慢 SQL 指标（如mysql_global_status_slow_queries），Prometheus 存储指标数据，Grafana 做可视化展示，并设置告警规则；
  • ELK：将慢查询日志导入 Elasticsearch，通过 Kibana 做慢 SQL 的检索、统计和可视化，支持按表名、SQL 类型、执行时间等维度筛选；
  • Percona Monitoring and Management (PMM) ：Percona 官方推出的 MySQL 监控工具，专门针对 MySQL 的性能监控，包含慢 SQL 的详细分析，开箱即用。
（2）商业监控方案
  • 阿里云 RDS 慢 SQL 监控、腾讯云 CDB 慢 SQL 分析：云数据库自带的慢 SQL 监控功能，支持实时查看慢 SQL、执行计划分析、索引建议；
  • Datadog、New Relic：全链路监控工具，可将慢 SQL 与应用端的请求关联，实现全链路的性能分析。

2. 慢 SQL 预防：从源头规避，强制落地规范

慢 SQL 的预防比排查更重要，核心是建立并强制落地开发、测试、上线的全流程规范，让慢 SQL 无法进入生产环境，以下是核心的预防规范：

（1）开发端规范：从代码层面规避慢 SQL
  1. 索引设计规范：表创建时，必须根据业务查询场景建立合适的索引，新增查询场景后，及时补充索引；禁止为低区分度字段单独建索引，优先建立联合索引和覆盖索引；
  2. SQL 写法规范：严格规避索引失效的 10 类场景，禁止使用SELECT *，必须指定具体字段；禁止大表做无索引的批量操作，批量操作必须分批次处理；
  3. 事务开发规范：缩短事务生命周期，禁止在事务中执行非数据库操作；规范加锁顺序，避免锁竞争；
  4. 代码审核规范：开发人员提交代码时，必须对新增的 SQL 做EXPLAIN分析，确认执行计划最优，由资深开发或 DBA 审核通过后才能合并。
（2）测试端规范：上线前做性能测试
  1. SQL 性能测试：对所有上线的 SQL 做性能测试，模拟生产环境的并发量，确认 SQL 的执行时间、扫描行数、资源占用均在合理范围内；
  2. 索引有效性测试：测试过程中，检查 SQL 是否使用了合适的索引，是否存在索引失效、选错索引的情况；
  3. 极限场景测试：对大表的 SQL 做极限场景测试（如大结果集查询、高并发更新），确认 SQL 在极限场景下的性能表现。
（3）上线端规范：做 SQL 审核，灰度发布
  1. 线上 SQL 审核：所有上线到生产环境的 SQL，必须通过 SQL 审核工具（如Archery、SQLAdvisor）做自动化审核，审核不通过的 SQL 禁止上线；
  2. 灰度发布：对新增的慢 SQL 风险较高的业务（如大表查询、批量更新），做灰度发布，先在小流量环境验证性能，无问题后再全量发布；
  3. 索引变更规范：线上表的索引新增、删除、修改，必须在低峰期执行，且执行前做备份，避免索引变更导致的数据库性能波动。
（4）运维端规范：定期优化，持续维护
  1. 定期分析慢查询日志：DBA 每周 / 每月分析慢查询日志，统计慢 SQL 的类型、根因，形成慢 SQL 分析报告，反馈给开发团队，优化业务代码；
  2. 定期更新统计信息：对数据量变化大的表，定期执行ANALYZE TABLE更新统计信息，让 MySQL 优化器始终基于最新的统计信息选择索引；
  3. 定期清理冗余索引和无效索引：使用sys.schema_unused_indexes（MySQL8.0）查看未使用的索引，定期清理，减少索引维护的成本，提升数据库性能；
  4. 大表分区：对数据量超过 1000 万的大表，做分区表（如按时间分区、按范围分区），减少单表的数据量，提升 SQL 的执行效率。

七、总结

MySQL 慢 SQL 的排查和优化是一项综合性的工作，既需要掌握EXPLAIN执行计划的解读技巧，能精准定位未用索引、索引失效、选错索引等基础问题，也需要结合数据库的锁机制、优化器原理、服务器资源状态，分析锁竞争、SQL 逻辑复杂等深层问题。

慢 SQL 的核心优化思路可总结为 “三大核心”：

  1. 索引优化：建立合适的索引，规避索引失效，打造覆盖索引，让 SQL 尽可能使用索引且仅通过索引就能完成查询；
  2. SQL 优化：规范 SQL 写法，简化 SQL 逻辑，将复杂查询拆分为简单查询，避免全表扫描、文件排序、临时表等低效操作；
  3. 架构优化：热点数据打散，读写分离，分库分表，将离线聚合操作转移到数仓，从架构层面降低数据库的压力。

同时，慢 SQL 的治理不能只依赖 “事后排查”，更需要建立 “开发 - 测试 - 上线 - 运维” 的全流程预防体系 ，通过规范落地、实时监控、定期优化，让慢 SQL 无法进入生产环境，最终实现 MySQL 数据库的高性能、高可用运行。

# mysql # sql # 数据库 # 经验分享 # 后端
Logo
2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

cover

【Azure AI Search】如何通过Entra ID RBAC认证连接中国区 Azure AI Search

avatar 2048 AI社区
cover

宏智树 AI 问卷设计：告别 “无效提问”，让你的数据支撑更硬核

avatar 2048 AI社区
cover

宏智树 AI：让数据 “开口说话”，实证论文数据分析零门槛通关

avatar 2048 AI社区