MySQL中慢SQL的监控与优化技巧

目录

• 一、什么是慢SQL？——数据库的"交通堵塞"
• 慢SQL的定义
• 慢SQL的典型危害
• 二、开启慢SQL监控:安装"交通摄像头"
• 1. 配置慢查询日志
• 2. 性能模式监控
• 3. 慢查询日志格式解析
• 三、分析慢SQL:找出"堵点"原因
• 1. EXPLAIN命令详解
• 2. 关键指标解读表
• 3. 可视化分析工具
• 四、十大慢SQL优化技巧:从入门到精通
• 1. 索引优化:数据库的"高速公路"
• 2. 查询重写:更高效的表达
• 3. 避免SELECT *:精准数据获取
• 4. 深度分页优化
• 5. 批量操作:减少交互次数
• 6. 合理使用JOIN
• 7. 控制事务大小
• 8. 使用覆盖索引
• 9. 分区表优化
• 10. 架构升级
• 五、自动化监控工具:24小时守护
• 工具对比表
• Prometheus监控配置
• Grafana仪表盘示例
• 六、实战案例:电商系统优化之旅
• 问题场景
• 优化过程
• 优化效果
• 七、预防慢SQL:最佳实践
• 开发规范清单
• 持续优化流程
• 八、总结:慢SQL优化黄金法则
• 优化金字塔
• 终极忠告

一、什么是慢SQL？——数据库的"交通堵塞"

慢SQL的定义

慢SQL是指执行时间超过设定阈值（通常0.5-2秒）的SQL语句。就像高速路上android的事故车，它会阻塞整个交通流！

慢SQL的典型危害

二、开启慢SQL监控:安装"交通摄像头"

1. 配置慢查询日志

-- 查看当前设置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 1秒阈值
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久生效（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1 -- 记录无索引查询
log_output = FILE

2. 性能模式监控

-- 开启性能监控
UPDATE setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- 查看慢SQL统计
SELECT * FROM events_statements_summary_by_digest 
WHERE SUM_TIMER_WAIT > 1000000000000; -- 查询超过1秒的SQL

3. 慢查询日志格式解析

# Time: 2023-10-05T08:12:34.123456Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 5.123456  Lock_time: 0.001000 Rows_sent: 10  Rows_examined: 100000
SET timestamp=1696493554;
SELECT * FROM orders WHERE status='pending' AND amount > 100;

关键字段:

• Query_time:SQL执行时间
• Rows_examined:扫描行数
• Rows_sent:返回行数
• Lock_time:锁定时间

三、分析慢SQL:找出"堵点"原因

1. EXPLAIN命令详解

EXPLAIN SELECT * FROM users WHERE age > 30 AND country = 'CN';

2. 关键指标解读表

3. 可视化分析工具

pt-query-digest使用:

# 安装Percona Toolkit
sudo apt install percona-toolkit

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

# 输出报告示例
# 220ms avg, 95% 350ms, 最大耗时2.5s
# 执行次数:120次/天
# 建议:添加索引(status,amount)

四、十大慢SQL优化技巧:从入门到精通

1. 索引优化:数据库的"高速公路"

-- 添加组合索引
CREATE INDEX idx_status_amount ON orders(status, amount);

-- 删除冗余索引
DROP INDEX idx_status ON orders;

索引优化矩阵:

2. 查询重写:更高效的表达

-- 优化前（函数导致索引失效）
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 优化后
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

3. 避免SELECT *:精准数据获取

-- 优化前
Shttp://www.devze.comELECT * FROM products;

-- 优化后
SELECT id, name, price FROM products;

4. 深度分页优化

-- 传统分页（慢）
SELECT * FROM orders LIMIT 1000000, 20;

-- 游标分页（快）
SELECT * FROM orders WHERE id > 1000000 LIMIT 20;

5. 批量操作:减少交互次数

// Java示例:批量插入
PreparedStatement ps = conn.prepareStatement("INSERT INTO logs VALUES (?)");
for (String log : logs) {
    ps.setString(1, log);
    ps.addBATch(); // 添加到批处理
}
ps.executeBatch(); // 批量执行

6. 合理使用JOIN

-- 使用EXISTS代替JOIN
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

7. 控制事务大小

-- 小事务提交
START TRANSACTION;
INSERT INTO ...; -- 少量操作
COMMIT;

8. 使用覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_cover ON orders(user_id, status);

-- 查询只需索引
SELECT user_id, status FROM orders WHERE user_id=100;

9. 分区表优化

-- 按时间分区
CREATE TABLE logs (
    id INT,
    log_time DATETIME
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VAandroidLUES LESS THAN (2022)
);

10. 架构升级

五、自动化监控工具:24小时守护

工具对比表

Prometheus监控配置

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-server:9104']
    metrics_path: /metrics

Grafana仪表盘示例

六、实战案例:电商系统优化之旅

问题场景

• 订单查询超时（>5秒）
• 高峰期数据库CPU 100%
• 每分钟超时错误50+

优化过程

优化效果

七、预防慢SQL:最佳实践

开发规范清单

1. 所有查询使用EXPLAIN分析
2. 避免全表扫描（type编程客栈=ALL）
3. 为WHERE条件列添加索引
4. 禁止超过3表JOIN
5. 事务内操作不超过5条SQL
6. 分页查询使用游标模式
7. 批量操作代替循环操作
8. 定期进行SQL审查

持续优化流程js

八、总结:慢SQL优化黄金法则

优化金字塔

终极忠告

慢SQL优化是"治未病"的艺术——最好的优化是在问题发生前预防！

行动指南:

1. 立即开启慢查询日志
2. 分析TOP 10慢SQL
3. 实施优化方案
4. 建立监控告警
5. 制定开发规范

记住:数据库优化不是一次性的任务，而是持续的过程。现在就开始你的优化之旅吧！

（本文基于MySQL 8.0，部分命令在旧版本中可能不同）

以上就是MySQL中慢SQL的监控与优化技巧的详细内容，更多关于MySQL慢SQL监控与优化的资料请关注编程客栈(www.devze.com)其它相关文章！