MySQL中怎么实现慢查询

发布时间:2021-08-02 11:33:47 作者:Leah
来源:亿速云 阅读:207
# MySQL中怎么实现慢查询

## 一、慢查询概述

### 1.1 什么是慢查询
慢查询是指执行时间超过指定阈值的SQL语句。在MySQL中,当一个查询的执行时间超过了`long_query_time`参数设置的阈值(默认10秒),该查询就会被记录到慢查询日志中。

### 1.2 慢查询的危害
- 消耗大量数据库资源(CPU、内存、I/O)
- 可能导致连接池耗尽,引发连锁反应
- 影响用户体验,降低系统整体性能
- 在高并发场景下可能成为系统瓶颈

### 1.3 慢查询分析的价值
- 识别性能瓶颈
- 优化数据库设计
- 改进SQL编写质量
- 提升系统整体响应速度

## 二、MySQL慢查询配置

### 2.1 慢查询相关参数

```sql
-- 查看慢查询相关参数
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE '%log_output%';

主要参数说明:

参数名称 默认值 说明
slow_query_log OFF 是否开启慢查询日志
slow_query_log_file host_name-slow.log 慢查询日志文件路径
long_query_time 10.000000 慢查询阈值(秒)
log_queries_not_using_indexes OFF 是否记录未使用索引的查询
log_output FILE 日志输出方式(FILE/TABLE/NONE)
log_slow_admin_statements OFF 是否记录管理语句
log_slow_slave_statements OFF 从库是否记录慢查询

2.2 开启慢查询日志

临时开启(重启失效)

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_output = 'FILE';

永久开启(修改配置文件)

# 编辑my.cnf或my.ini文件
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

2.3 慢查询日志格式示例

# Time: 2023-05-20T08:15:23.123456Z
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 3.141592  Lock_time: 0.000100 Rows_sent: 100  Rows_examined: 10000
SET timestamp=1684563323;
SELECT * FROM large_table WHERE non_indexed_column = 'value';

三、慢查询分析方法

3.1 使用mysqldumpslow工具

MySQL自带的慢查询日志分析工具:

# 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 常用选项
-s 排序方式(t:按时间,c:按次数,l:按锁定时间)
-t 显示前N条记录
-g 使用正则过滤

3.2 使用pt-query-digest工具

Percona Toolkit中的高级分析工具:

# 安装
sudo apt-get install percona-toolkit

# 使用
pt-query-digest /var/log/mysql/mysql-slow.log

# 输出示例
# 170ms user time, 40ms system time, 24.00M rss, 203.11M vsz
# Current date: Mon May 20 08:30:00 2023
# Hostname: db-server
# Files: /var/log/mysql/mysql-slow.log
# Overall: 1.02k total, 21 unique, 0.00 QPS, 0.00x concurrency
# Time range: 2023-05-20 00:00:00 to 08:30:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           104s    100ms      3s    101ms   500ms   200ms    80ms
# Lock time            50s      0s      1s     50ms   100ms    30ms    20ms
# Rows sent          1.02M       0  10.00k    1.02k   5.00k   1.02k     500
# Rows examine       2.04G       0   1.00M    2.04M   5.00M   1.02M   1.00M
# Query size        10.20M      10  10.00k   10.24k  10.00k   5.12k   5.12k

3.3 将慢查询记录到表中

-- 设置日志输出到表
SET GLOBAL log_output = 'TABLE';

-- 查询慢查询记录
SELECT * FROM mysql.slow_log;

四、慢查询优化方法

4.1 索引优化

常见索引问题

优化示例

-- 低效查询
SELECT * FROM users WHERE DATE(create_time) = '2023-05-20';

-- 优化后
SELECT * FROM users WHERE create_time >= '2023-05-20 00:00:00' 
                      AND create_time < '2023-05-21 00:00:00';

4.2 SQL语句优化

常见问题SQL

优化示例

-- 低效分页
SELECT * FROM large_table LIMIT 1000000, 10;

-- 优化分页(使用索引覆盖)
SELECT * FROM large_table WHERE id > 1000000 LIMIT 10;

4.3 数据库设计优化

优化策略

示例

-- 原始设计(需要频繁JOIN)
SELECT u.name, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 优化设计(适当冗余)
ALTER TABLE orders ADD COLUMN user_name VARCHAR(100);

五、高级慢查询监控方案

5.1 使用Performance Schema

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

-- 查询慢SQL
SELECT * FROM performance_schema.events_statements_summary_by_digest 
WHERE AVG_TIMER_WT > 1000000000
ORDER BY SUM_TIMER_WT DESC LIMIT 10;

5.2 使用sys Schema

MySQL 5.7+提供的系统视图:

-- 查看最耗时的SQL
SELECT * FROM sys.statement_analysis 
ORDER BY avg_latency DESC LIMIT 10;

-- 查看全表扫描的SQL
SELECT * FROM sys.statements_with_full_table_scans;

5.3 使用第三方监控工具

推荐工具: 1. Percona PMM 2. VividCortex 3. Datadog 4. Prometheus + Grafana

六、慢查询优化案例

6.1 案例一:索引缺失问题

问题SQL

SELECT * FROM orders WHERE status = 'pending' AND create_time > '2023-01-01';

分析: - 执行时间:8.2秒 - 扫描行数:1,200,000 - 返回行数:1,500

优化方案

ALTER TABLE orders ADD INDEX idx_status_createtime (status, create_time);

优化效果: - 执行时间降至0.05秒 - 扫描行数降至1,500

6.2 案例二:分页查询优化

问题SQL

SELECT * FROM user_activities ORDER BY create_time DESC LIMIT 100000, 20;

分析: - 执行时间:12.5秒 - 需要先排序100020条记录

优化方案

SELECT * FROM user_activities 
WHERE create_time < '2023-05-01'  -- 添加时间范围条件
ORDER BY create_time DESC 
LIMIT 20;

6.3 案例三:JOIN优化

问题SQL

SELECT p.*, u.username 
FROM products p 
LEFT JOIN users u ON p.seller_id = u.id 
WHERE p.category = 'electronics';

分析: - 执行计划显示全表扫描 - 没有使用到合适的索引

优化方案

ALTER TABLE products ADD INDEX idx_category_seller (category, seller_id);
ALTER TABLE users ADD INDEX idx_id_username (id, username);

七、慢查询预防措施

7.1 开发规范

7.2 上线前检查

7.3 监控告警

八、总结

MySQL慢查询优化是数据库性能调优的重要环节。通过合理配置慢查询日志、使用专业分析工具、结合索引优化和SQL改写等手段,可以显著提升数据库性能。建议:

  1. 生产环境必须开启慢查询日志
  2. 定期分析并优化慢SQL
  3. 建立SQL审核机制
  4. 使用专业监控工具持续跟踪

通过系统化的慢查询管理,可以有效预防和解决数据库性能问题,保障业务系统的稳定运行。


附录:常用诊断命令

-- 查看当前运行中的慢查询
SHOW PROCESSLIST;

-- 查看表索引
SHOW INDEX FROM table_name;

-- 分析SQL执行计划
EXPLN SELECT * FROM table_name WHERE condition;

-- 深入分析(MySQL 8.0+)
EXPLN ANALYZE SELECT * FROM table_name WHERE condition;

参考资料: 1. MySQL官方文档 - Slow Query Log 2. 《高性能MySQL》第3版 3. Percona博客 - Slow Query Optimization “`

推荐阅读:
  1. MySQL慢查询 ------ 开启慢查询
  2. MySQL慢查询日志

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:js如何删除对象/数组中null、undefined、空对象及空数组

下一篇:jQuery插件扩展操作的示例分析

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》