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

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

慢sql的定义

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

慢sql的典型危害

危害类型	影响	经济损失
用户体验	页面卡顿、超时	用户流失增加30%
系统资源	cpu/内存耗尽	服务器成本增加50%
业务影响	订单丢失、支付失败	每分钟损失$1000+
团队压力	紧急故障处理	开发效率下降40%

二、开启慢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. 关键指标解读表

指标	理想值	问题值	优化建议
type	const, ref	all	添加索引
key	索引名	null	优化查询条件
rows	<1000	>10000	减少扫描范围
extra	using index	using temporary	避免临时表

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;

索引优化矩阵:

场景	索引策略	效果
where条件	组合索引	⭐⭐⭐⭐
order by	排序字段索引	⭐⭐⭐
join字段	外键索引	⭐⭐⭐⭐
高基数字段	b-tree索引	⭐⭐⭐⭐

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 *:精准数据获取

-- 优化前
select * 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 values less than (2022)
);

10. 架构升级

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

工具对比表

工具	类型	功能	适用场景
prometheus+grafana	开源	可视化监控	云原生环境
mysql enterprise monitor	商业	实时监控	企业用户
pt-query-digest	命令行	慢日志分析	深度分析
percona monitoring and management	开源	全套监控	dba首选

prometheus监控配置

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

grafana仪表盘示例

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

问题场景

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

优化过程

优化效果

指标	优化前	优化后	提升
平均响应时间	4200ms	35ms	120倍
cpu使用率	100%	20%	5倍资源释放
错误率	15%	0.1%	99%下降
用户满意度	差评率30%	好评率95%	体验升级

七、预防慢sql:最佳实践

开发规范清单

所有查询使用explain分析
避免全表扫描（type=all）
为where条件列添加索引
禁止超过3表join
事务内操作不超过5条sql
分页查询使用游标模式
批量操作代替循环操作
定期进行sql审查

持续优化流程

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

优化金字塔

终极忠告

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

行动指南:

立即开启慢查询日志
分析top 10慢sql
实施优化方案
建立监控告警
制定开发规范

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

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

以上就是mysql中慢sql的监控与优化技巧的详细内容，更多关于mysql慢sql监控与优化的资料请关注代码网其它相关文章！

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

2025年08月04日 • Mysql •我要评论