如何定位慢sql

在日常工作中定位 慢 SQL（Slow SQL） 是性能优化的重要一环，通常结合 MySQL 的日志机制 和 APM 工具（如 Skywalking） 来全面定位问题。

下面从 日志分析 和 Skywalking 工具监控 两方面讲解：

---

一、通过 MySQL 日志定位慢 SQL

1. 开启慢查询日志

在 my.cnf 配置文件中添加以下内容：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1  # 设置超过1秒就认为是慢SQL
log_queries_not_using_indexes = 1  # 记录未使用索引的查询

然后重启 MySQL：

sudo systemctl restart mysql

2. 查看慢查询日志内容

日志文件示例：

# Time: 2025-06-22T10:10:11.000000Z
# User@Host: root[root] @ localhost []
# Query_time: 2.001164  Lock_time: 0.000123 Rows_sent: 1  Rows_examined: 10000
use mydb;
SELECT * FROM users WHERE name LIKE '%John%';

重点关注：

• Query_time：执行耗时

• Rows_examined：扫描的行数

• 是否使用索引

3. 使用 mysqldumpslow 工具聚合分析

mysqldumpslow -s t /var/log/mysql/mysql-slow.log

可按时间（t）、行数（r）等维度查看最慢的SQL模板。

4. 使用 pt-query-digest 工具分析（推荐）

pt-query-digest /var/log/mysql/mysql-slow.log > report.txt

它能生成详细报告，展示：

• 哪些 SQL 最慢

• 平均耗时、最大耗时

• 执行频率

• 具体 SQL 模板

---

二、通过 Skywalking 定位慢 SQL

Skywalking 是一款分布式链路追踪、性能分析和监控平台，适合微服务场景。

1. Skywalking 可以定位哪些内容？

• 每个接口的响应时间

• 慢 SQL 明细

• SQL 属于哪个服务、接口调用

• 数据库响应耗时

• 是否出现异常

2. 使用步骤

（1）在 Java 后端中集成 Skywalking Agent

启动参数加入：

-javaagent:/path/to/skywalking-agent/skywalking-agent.jar
-Dskywalking.agent.service_name=my-service
-Dskywalking.collector.backend_service=127.0.0.1:11800

（2）配合数据库插件

Skywalking 支持 JDBC、MyBatis、Spring Data 等插件，可以采集 SQL 信息。

（3）在 UI 页面中查看链路追踪

你可以在 Skywalking UI 中：

• 查看每个请求的 Trace

• 每个 Trace 中包含的 SQL 调用明细

• SQL 执行耗时、调用堆栈

• 哪个服务、哪个接口触发的 SQL

示意图像这样：

接口调用 /api/user/login
 ├── 调用 MySQL：SELECT * FROM user WHERE username=?
     └── 耗时：1.8s

使用EXPLAIN命令排查慢sql的原因

使用 EXPLAIN 是排查慢 SQL 的最重要方法之一，它可以帮你 分析 SQL 的执行计划，从而判断 SQL 是否走索引、是否存在全表扫描、关联顺序是否合理等问题。

---

🧠 一、什么是 EXPLAIN？

EXPLAIN 是 MySQL 提供的一个语句，用于显示 SQL 的执行计划，也叫查询计划。通过它，你可以知道 SQL 是怎么被 MySQL 执行的。

用法：

EXPLAIN SELECT * FROM user WHERE id = 1;

EXPLAIN SELECT ...;
或
EXPLAIN FORMAT=JSON SELECT ...;

---

🧾 二、EXPLAIN 输出字段解释

下面是 EXPLAIN 的典型输出字段及含义：

字段名	含义	如何判断
id	查询序列号（越大优先级越高）	多表 JOIN 时可看执行顺序
select_type	查询类型（SIMPLE、PRIMARY、SUBQUERY等）	关注是否是子查询、UNION 等
table	当前操作的表	慢 SQL 通常与该表有关
type	访问类型（最重要）	越靠近 ALL 越差
possible_keys	可能用到的索引	应该有值
key	实际使用的索引	应该命中索引
key_len	使用的索引长度	越短越好
rows	扫描行数	越小越好（1000以内最好）
filtered	过滤比例	结合 rows 判断
Extra	额外信息	是否有 Using filesort、Using temporary 等不佳提示

---

🔍 三、重点关注 type（访问类型）

这是判断 SQL 优化程度最关键的字段：

type 值	含义	性能
system	表只有一行（常见于 system 表）	最优
const	通过主键/唯一索引获取单行	极优
eq_ref	多表连接中，主键等值连接	优
ref	非唯一索引等值查询	中等偏上
range	范围查询，使用索引	可接受
index	全索引扫描（非数据行）	较差
ALL	全表扫描	最差 ⚠️

出现 type = ALL 基本说明没有命中索引，是慢 SQL 的常见根因。

---

🧪 四、排查慢 SQL 步骤（实战指南）

1️⃣ 拿到慢 SQL，比如：

SELECT * FROM orders WHERE user_id = 123 AND create_time > '2024-01-01';

2️⃣ 用 EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND create_time > '2024-01-01';

3️⃣ 分析结果（假设如下）：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ALL	idx_user, idx_time	NULL	100000	Using where; Using filesort

🔴 问题诊断：

• type = ALL：全表扫描 ❌

• key = NULL：未使用任何索引 ❌

• Using filesort：可能排序慢 ❌

4️⃣ 优化建议：

• 为 (user_id, create_time) 添加联合索引：

CREATE INDEX idx_user_time ON orders(user_id, create_time);

5️⃣ 再次执行 EXPLAIN 验证：

| type = range | key = idx_user_time | rows = 100 |

✅ 成功优化！

---

💡 五、Extra 字段常见警告项

Extra 内容	含义	是否需要优化
Using filesort	使用了文件排序（非索引排序）	是
Using temporary	使用了临时表（通常 group by/order by）	是
Using where	有where条件过滤	正常
Using index	覆盖索引（仅访问索引）	优

---

🎯 总结：排查慢 SQL 的思维流程

1. 发现慢 SQL（日志、APM 工具）
2. 使用 EXPLAIN 查看执行计划
3. 判断 type（访问类型）是否高效
4. 检查 key（是否用了索引）
5. 分析 Extra（是否排序、临时表）
6. 添加索引 or 重写 SQL
7. 再用 EXPLAIN 验证是否改善

优化慢sql

排查出慢 SQL 后，接下来最重要的就是 定位瓶颈点并制定针对性的优化方案。下面我从常见问题出发，系统地总结慢 SQL 的优化思路与实战策略。

---

🧠 一、常见导致慢 SQL 的原因

问题类型	描述
未使用索引	最常见，走了全表扫描（type = ALL）
索引失效	使用了函数、隐式转换、范围查询导致无法走索引
查询字段太多	使用 SELECT * 读了大量无关字段
表关联复杂	多表 JOIN 无优化，索引缺失
排序分页无索引	ORDER BY、LIMIT 扫描大量数据
数据量太大	表数据量大，缺分库分表或归档策略
数据倾斜	某些值过于集中，导致查询慢或热点

---

🛠 二、慢 SQL 优化方案大全

1️⃣ 添加或优化索引

✅ 最核心手段！

• 针对 WHERE 条件、JOIN 条件、ORDER BY 字段创建合适的索引

• 复合索引顺序非常关键：最左前缀原则

• 避免创建冗余、重复的索引

示例：

CREATE INDEX idx_user_time ON orders(user_id, create_time);

2️⃣ 重写 SQL 结构

• 拆分复杂查询为多个小查询（尤其是子查询、嵌套查询）

• 把 OR 拆成 UNION 多次走索引

• 优化 JOIN 顺序，把小表放前面

示例优化前：

SELECT * FROM user WHERE name LIKE '%abc%';

优化后（改为全文索引或外部搜索引擎如 Elasticsearch）：

-- 使用 MATCH AGAINST
SELECT * FROM user WHERE MATCH(name) AGAINST('abc');

3️⃣ 避免索引失效

• WHERE 中 不要对列使用函数、运算、类型转换

• 不要隐式类型转换（如 int vs varchar）

• 使用范围查询时避免放在联合索引的第一位（影响索引利用）

示例（索引失效）：

SELECT * FROM order WHERE DATE(create_time) = '2024-01-01';  -- 索引失效

优化：

SELECT * FROM order WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

4️⃣ 减少查询字段（避免 SELECT *）

• 只查需要的字段，能使用 覆盖索引 更佳

• 覆盖索引 = 查询字段全部来自于索引，避免回表查询

示例：

SELECT id, username FROM user WHERE id = 1;

5️⃣ 优化分页查询（大页码分页）

当 LIMIT 10000, 20 这种大偏移分页会特别慢，因为 MySQL 会从头跳过 10000 行。

优化方式：

• 记录上一次的 id，使用 基于游标的分页（keyset pagination）

示例：

-- 慢
SELECT * FROM orders ORDER BY create_time LIMIT 10000, 20;

-- 快
SELECT * FROM orders WHERE id > 123456 ORDER BY id LIMIT 20;

6️⃣ 加缓存

• 对热点查询使用 Redis 缓存，避免频繁访问数据库

• 可使用缓存工具框架如 Spring Cache、Caffeine 等

7️⃣ 垂直或水平拆分大表

• 垂直拆分：将大字段（如长文本、图片）拆出去

• 水平拆分：分库分表，减小每张表的数据量

---

📋 三、优化示例汇总

示例 1：模糊查询优化

-- 慢
SELECT * FROM user WHERE name LIKE '%张%';

-- 优化：使用全文索引或外部搜索引擎
ALTER TABLE user ADD FULLTEXT(name);
SELECT * FROM user WHERE MATCH(name) AGAINST('张');

示例 2：子查询优化为 JOIN

-- 慢
SELECT * FROM orders WHERE user_id IN (SELECT id FROM user WHERE status = 1);

-- 优化
SELECT o.* FROM orders o JOIN user u ON o.user_id = u.id WHERE u.status = 1;

示例 3：避免函数导致索引失效

-- 慢
SELECT * FROM order WHERE DATE(create_time) = '2024-01-01';

-- 优化
SELECT * FROM order WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

---

✅ 四、总结：慢 SQL 优化 checklist

检查点	是否 OK
是否走索引（EXPLAIN 分析）	✅
是否有适当的联合索引	✅
是否写了 SELECT *	❌
是否避免函数/运算/类型转换	✅
是否避免大分页 OFFSET	✅
是否考虑缓存热点数据	✅
是否考虑数据量太大需分表	✅
是否考虑使用全文索引/搜索引擎	✅