慢SQL治理总结

概述

慢SQL是数据库性能优化的核心问题之一。本文档总结了慢SQL的识别、诊断、优化和治理的完整流程。

什么是慢SQL？

慢SQL是指执行时间超过预期阈值的SQL语句，通常会导致：

• 数据库响应时间延长
• 用户体验下降
• 系统资源占用增加
• 可能导致数据库连接池耗尽

慢SQL识别

1. 设置慢查询阈值

MySQL：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;

-- 设置慢查询日志文件位置
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

2. 慢查询日志分析

使用 mysqldumpslow 工具：

# 查看执行次数最多的10条慢SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# 查看平均执行时间最长的10条慢SQL
mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# 查看总执行时间最长的10条慢SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

使用 pt-query-digest 工具：

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow-query.log

# 输出到文件
pt-query-digest /var/log/mysql/slow-query.log > slow-report.txt

3. 实时监控

查看当前正在执行的慢查询：

-- 查看正在执行的慢查询
SELECT * FROM information_schema.processlist 
WHERE command != 'Sleep' 
AND time > 5
ORDER BY time DESC;

-- 查看慢查询统计
SHOW STATUS LIKE 'Slow_queries';

慢SQL诊断

1. 使用 EXPLAIN 分析执行计划

基本用法：

EXPLAIN SELECT * FROM users WHERE age > 25;

关键字段解读：

• type：连接类型

  • ALL：全表扫描（最差）
  • index：全索引扫描
  • range：范围扫描
  • ref：非唯一索引扫描
  • eq_ref：唯一索引扫描
  • const：常量查询（最好）

• key：使用的索引

• rows：扫描的行数

• Extra：额外信息

  • Using filesort：需要排序
  • Using temporary：使用临时表
  • Using index：使用覆盖索引（好）

2. 使用 EXPLAIN ANALYZE（MySQL 8.0+）

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

3. 使用 SHOW PROFILE

-- 开启 profiling
SET profiling = 1;

-- 执行SQL
SELECT * FROM users WHERE age > 25;

-- 查看执行时间
SHOW PROFILES;

-- 查看详细执行信息
SHOW PROFILE FOR QUERY 1;

慢SQL优化策略

1. 索引优化

创建合适的索引：

-- 单列索引
CREATE INDEX idx_age ON users(age);

-- 复合索引（注意最左前缀原则）
CREATE INDEX idx_age_name ON users(age, name);

-- 覆盖索引（包含查询所需的所有列）
CREATE INDEX idx_covering ON users(age, name, email);

索引优化原则：

• 为 WHERE 子句中的列创建索引
• 为 JOIN 条件中的列创建索引
• 为 ORDER BY 和 GROUP BY 中的列创建索引
• 避免在索引列上使用函数
• 注意最左前缀原则

索引失效场景：

-- ❌ 使用函数导致索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;

-- ✅ 使用范围查询
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- ❌ LIKE 以 % 开头导致索引失效
SELECT * FROM users WHERE name LIKE '%张%';

-- ✅ LIKE 以具体值开头可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

2. SQL语句优化

**避免 SELECT ***：

-- ❌ 查询所有列
SELECT * FROM users WHERE age > 25;

-- ✅ 只查询需要的列
SELECT id, name, email FROM users WHERE age > 25;

使用 LIMIT 限制结果集：

-- ❌ 查询所有数据
SELECT * FROM users WHERE age > 25;

-- ✅ 使用 LIMIT
SELECT * FROM users WHERE age > 25 LIMIT 100;

避免子查询，使用 JOIN：

-- ❌ 使用子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- ✅ 使用 JOIN
SELECT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

使用 EXISTS 替代 IN（大数据集）：

-- ❌ 使用 IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- ✅ 使用 EXISTS
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

避免使用 OR，使用 UNION：

-- ❌ 使用 OR
SELECT * FROM users WHERE age < 18 OR age > 65;

-- ✅ 使用 UNION
SELECT * FROM users WHERE age < 18
UNION
SELECT * FROM users WHERE age > 65;

3. 分页优化

深度分页优化：

-- ❌ 深度分页（慢）
SELECT * FROM users ORDER BY id LIMIT 10000, 20;

-- ✅ 使用子查询优化
SELECT * FROM users 
WHERE id > (SELECT id FROM users ORDER BY id LIMIT 10000, 1)
ORDER BY id LIMIT 20;

-- ✅ 使用游标分页
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;

4. 批量操作优化

批量插入优化：

-- ❌ 逐条插入
INSERT INTO users (name, age) VALUES ('张三', 25);
INSERT INTO users (name, age) VALUES ('李四', 30);

-- ✅ 批量插入
INSERT INTO users (name, age) VALUES 
('张三', 25),
('李四', 30),
('王五', 28);

批量更新优化：

-- ❌ 逐条更新
UPDATE users SET status = 1 WHERE id = 1;
UPDATE users SET status = 1 WHERE id = 2;

-- ✅ 批量更新
UPDATE users SET status = 1 WHERE id IN (1, 2, 3);

5. 表结构优化

选择合适的数据类型：

• 使用最小的数据类型
• 避免使用 TEXT/BLOB（除非必要）
• 使用 INT 而非 VARCHAR 存储数字
• 使用 DATETIME 而非 VARCHAR 存储时间

表分区：

-- 按时间分区
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

慢SQL治理流程

1. 问题发现

• 监控告警：设置慢查询监控告警
• 日志分析：定期分析慢查询日志
• 用户反馈：关注用户反馈的性能问题

2. 问题诊断

• 执行计划分析：使用 EXPLAIN 分析执行计划
• 索引检查：检查是否缺少索引或索引使用不当
• SQL语句分析：检查SQL语句是否可优化

3. 优化实施

• 索引优化：创建或优化索引
• SQL重写：优化SQL语句逻辑
• 表结构优化：优化表结构设计

4. 效果验证

• 性能测试：对比优化前后的性能
• 监控观察：持续监控优化效果
• 回滚准备：准备回滚方案

5. 持续改进

• 定期审查：定期审查慢查询日志
• 最佳实践：总结优化经验
• 团队分享：分享优化案例

常见慢SQL场景及解决方案

场景1：全表扫描

问题：

SELECT * FROM users WHERE email = 'test@example.com';
-- 如果 email 列没有索引，会全表扫描

解决方案：

-- 创建索引
CREATE INDEX idx_email ON users(email);

场景2：索引未使用

问题：

SELECT * FROM users WHERE age + 1 > 25;
-- 索引列使用函数，索引失效

解决方案：

-- 改写SQL
SELECT * FROM users WHERE age > 24;

场景3：JOIN 性能问题

问题：

SELECT * FROM users u, orders o WHERE u.id = o.user_id;
-- 没有使用合适的JOIN方式

解决方案：

-- 使用 INNER JOIN 并确保有索引
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 确保 user_id 有索引

场景4：排序性能问题

问题：

SELECT * FROM users ORDER BY create_time DESC LIMIT 10;
-- 如果 create_time 没有索引，需要文件排序

解决方案：

-- 创建索引
CREATE INDEX idx_create_time ON users(create_time DESC);

性能监控指标

关键指标

1. 慢查询数量：Slow_queries
2. 平均查询时间：Avg_query_time
3. 索引使用率：Key_buffer_usage
4. 连接数：Threads_connected
5. 查询缓存命中率：Qcache_hits / (Qcache_hits + Qcache_inserts)

监控SQL

-- 查看慢查询统计
SHOW STATUS LIKE 'Slow_queries';

-- 查看索引使用情况
SHOW STATUS LIKE 'Key%';

-- 查看连接数
SHOW STATUS LIKE 'Threads%';

-- 查看查询缓存
SHOW STATUS LIKE 'Qcache%';

最佳实践

1. 开发阶段

• 代码审查：审查SQL语句质量
• 索引设计：合理设计索引
• 性能测试：进行性能测试

2. 测试阶段

• 压力测试：进行压力测试
• 慢查询检查：检查慢查询日志
• 性能基准：建立性能基准

3. 生产阶段

• 监控告警：设置监控告警
• 定期分析：定期分析慢查询日志
• 持续优化：持续优化慢SQL

工具推荐

1. MySQL 自带工具

• mysqldumpslow：慢查询日志分析
• EXPLAIN：执行计划分析
• SHOW PROFILE：性能分析

2. 第三方工具

• pt-query-digest：Percona Toolkit 慢查询分析工具
• MySQL Workbench：可视化工具
• Navicat：数据库管理工具

3. 监控工具

• Prometheus + Grafana：监控和可视化
• Zabbix：企业级监控
• ELK Stack：日志分析

参考资源

• 慢SQL治理总结 - 微信文章
• MySQL 官方文档
• Percona Toolkit 文档

---

最后更新：2026年1月12日