当前位置：首页 > ai >正文

Mysql慢查询分析

ai 2025/8/11 19:16:38

分析慢 SQL 查询需要系统化的方法，结合数据库的执行计划（EXPLAIN）和性能关键指标。以下是详细的流程和关键点：

一、分析慢 SQL 的通用流程

确认问题来源
- 使用 SHOW PROCESSLIST 或监控工具（如 Prometheus）检查当前 SQL 执行状态。
- 通过慢查询日志（slow_query_log）定位具体 SQL（需提前开启）。
分析执行计划
- 使用 EXPLAIN 或 EXPLAIN ANALYZE（MySQL 8.0+）查看 SQL 执行路径。
- 重点关注访问类型（type）、索引使用（key）、扫描行数（rows）等字段。
检查索引有效性
- 确认 WHERE、JOIN、ORDER BY 等子句是否用到了合适的索引。
- 检查索引选择性（重复值比例）和覆盖索引（Using index）。
评估数据量和资源
- 检查表的数据量（SELECT COUNT(*)），过大的表可能需要分页或分区。
- 观察 CPU、内存、磁盘 I/O 使用率，排除资源瓶颈。
排查锁和并发问题
- 使用 SHOW ENGINE INNODB STATUS 检查锁竞争。
- 检查事务隔离级别和长事务（information_schema.INNODB_TRX）。
优化 SQL 和架构
- 重写复杂查询（如子查询转 JOIN）。
- 考虑缓存、读写分离、分库分表等架构优化。

二、分析方法

1）通过 EXPLAIN 分析 SQL 执行计划

通常，我们在执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，我们可以通过 EXPLAIN 命令来查看这些执行信息。这些执行信息被统称为执行计划。

假设现在我们使用 EXPLAIN 命令查看当前 SQL 是否使用了索引，先通过 SQL EXPLAIN 导出相应的执行计划如下：

字段说明：

id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
partitions：访问的分区表信息。
type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。

eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。

ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。

range：索引范围扫描，比如，<，>，between 等操作。

index：索引全表扫描，此时遍历整个索引树。

ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：可能使用到的索引。
key：实际使用到的索引。
key_len：当前使用的索引的长度。
ref：关联 id 等信息。
rows：查找到记录所扫描的行数。
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息。

2）通过 Show Profile 分析 SQL 执行性能

上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。

Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。以下是相关命令的注释：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type 参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示 CPU 的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置 (行数) 
| SWAPS：显示 swap 交换次数的相关开销信息

值得注意的是，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确定的话，可以通过 select @@have_profiling 查询是否支持该功能，如下图所示：