在MySQL拿到一条慢SQL语句要如何优化？

在工作的过程中，很多时候会发现执行业务逻辑的时候，某一条SQL语句执行得非常慢。这时候，要如何处理这条语句，如何判断语句慢的地方在哪里？

一、初级排查 EXPLAIN+慢SQL分析

MySQL官网用法：
https://dev.mysql.com/doc/refman/8.4/en/explain.html
{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] [schema_spec]
select_statement = [SELECT,UPDATE,DELETE,INSERT,REPLACE,TABLE]
schema_spec = {SCHEMA/DATABASE}
FORMAT = {JSON/TREE}

基础用法为：EXPLAIN SELECT * FROM XXX 执行语句的默认返回结果是tabular format

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

字段解释：

1. id
定义： SELECT 查询的唯一标识符，每个 SELECT 子句分配一个独立 ID。
规则：
相同 ID：执行顺序从上到下（如简单 JOIN）。
不同 ID：数值越大优先级越高，越先执行（常见于嵌套子查询）。

2. select_type
定义： 查询类型，反映 SQL 的复杂度。
常见值：
SIMPLE：不含子查询或 UNION 的简单查询。
PRIMARY：最外层 SELECT（如含子查询的主查询）。
SUBQUERY：WHERE 或 HAVING 中的子查询。
DERIVED：FROM 子句中的子查询（临时表）。
UNION：UNION 操作的第二个或后续 SELECT。

3. table
定义： 当前查询涉及的表名或别名。
特殊情况：
<derivedN>：表示第 N 个子查询生成的临时表。
<unionM,N>：UNION 合并结果的临时表。

4. partitions
定义： 匹配的分区名称（仅当表使用分区时显示）。
示例： p0,p1 表示查询命中了分区 p0 和 p1。

5. type
定义： 表的访问方式，性能关键指标，从优到劣排序：
system > const > eq_ref > ref > range > index > ALL。
优化目标： 至少达到 range 级别，推荐 ref 或更高。

6. possible_keys
**定义：**理论上可能使用的索引列表（基于查询条件和表结构）。
注意： 可能为空（如全表扫描），但实际使用索引由 key 字段决定。

7. key
定义： 实际使用的索引名称。
特殊情况： NULL 表示未使用索引；PRIMARY 表示使用主键。

8. key_len
定义： 索引使用的字节数，反映索引字段的实际利用率。
规则：
数值越大，使用的索引字段越多。
根据字段类型和长度计算（如 INT 为 4 字节，可为 NULL 时 +1 字节）。

9. ref
定义： 与索引比较的列或常量。
常见值：
const：常量值（如 WHERE id=1）。
列名：关联查询中另一表的列（如 t1.col ）。

10. rows
定义： MySQL 预估需要扫描的行数（基于统计信息）。
优化参考： 数值越小越好，若过大需检查索引或条件过滤。

11. filtered
定义： 经 WHERE 条件过滤后剩余行数的百分比。
示例： filtered=10% 表示预估 90% 数据被过滤。

12. Extra
定义： 额外执行信息，常见值及优化建议：
- Using filesort：需外部排序（建议添加索引优化 ORDER BY）。
- Using temporary：使用临时表（常见于GROUP BY，需优化查询或索引）。
- Using index：覆盖索引（无需回表，性能最佳）。
- Using where：WHERE条件过滤数据。

在以上的各个字段中，可以有限考虑分析Type与Extra这两个字段，如果无法分析出结果，再考虑其他字段。

---

除了使用基本的语法，还可以使用 EXPLAIN ANALYZE SELECT * xxx 这句语句会直接帮你分析语句的内容。

参数	含义	示例值说明
Table scan on user_login_logs	执行计划类型：全表扫描（未使用索引，逐行遍历表数据）	当前查询未命中索引，导致必须扫描整张表
cost=479916	预估总成本：基于统计信息计算的资源消耗（包含CPU、IO等加权值	成本单位是虚拟值，数值越大表示资源消耗越高
rows=4.68e+6	预估扫描行数：优化器预测需要扫描的行数（基于数据分布统计）	预计扫描468万行，与实际值（470万）误差约0.4%，说明统计信息较准确
actual time=0.0512…1585	实际耗时：单位毫秒，0.0512为获取第一行的时间，1585为扫描全表总耗时	首次数据返回极快，但全量扫描耗时1.585秒，可能引发性能瓶颈
rows=4.7e+6	实际扫描行数：执行时真实处理的数据量	实际扫描470万行，与预估基本一致，说明表体积较大
loops=1	循环次数：该操作执行的总轮次（通常为1）	单次全表扫描，无嵌套循环操作

全表扫描代价高
问题：未使用索引导致强制扫描全表，消耗大量I/O和CPU资源6。
验证：检查查询条件（如WHERE子句）是否涉及未索引字段，或索引选择性不足。

高成本与实际耗时匹配
现象：cost=479916与actual time=1585ms均表明操作代价极高。
根因：表数据量大（470万行），且无有效过滤条件或索引支持。

首次返回延迟低但总耗时长
特征：0.0512ms首行返回快，但全量处理需1.5秒，适合分批读取场景（如分页）。

---

二、慢查询日志分析

1、开启慢查询日志

show VARIABLES like '%slow_query_log%'
set GLOBAL slow_query_log = 1+-------------+-----+
|Variable_name      |value|
|slow_query_log     | ON  |
|slow_query_log_file| XXX |
+-------------+-----+show GLOBAL STATUS like '%Slow_queries%'
+-------------+-----+
|Slow_queries |  0  |
+-------------+-----+show GLOBAL VARIABLES like '%long_query_time%'
set GLOBAL long_query_time = 2
+-------------+-----+
|long_query_time |  2.0000  |
+-------------+-----+

2、捕获慢查询SQL

使用 mysqldumpslow 工具统计高频慢 SQL

root > mysqldumpslow -s t -t 10 /path/to/slow.log   

三、SHOW PROFILE 深度分析

1、开启性能分析：

show variables profiling;
set profiling = 1

2、执行目标SQL语句，查看耗时分布：

SHOW PROFILES;  
SHOW PROFILE CPU, BLOCK IO FOR QUERY [QUERY_ID];