您好,登录后才能下订单哦!
# 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 | 从库是否记录慢查询 |
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
# 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';
MySQL自带的慢查询日志分析工具:
# 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# 常用选项
-s 排序方式(t:按时间,c:按次数,l:按锁定时间)
-t 显示前N条记录
-g 使用正则过滤
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
-- 设置日志输出到表
SET GLOBAL log_output = 'TABLE';
-- 查询慢查询记录
SELECT * FROM mysql.slow_log;
-- 低效查询
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';
-- 低效分页
SELECT * FROM large_table LIMIT 1000000, 10;
-- 优化分页(使用索引覆盖)
SELECT * FROM large_table WHERE id > 1000000 LIMIT 10;
-- 原始设计(需要频繁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);
-- 开启性能监控
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;
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;
推荐工具: 1. Percona PMM 2. VividCortex 3. Datadog 4. Prometheus + Grafana
问题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
问题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;
问题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);
MySQL慢查询优化是数据库性能调优的重要环节。通过合理配置慢查询日志、使用专业分析工具、结合索引优化和SQL改写等手段,可以显著提升数据库性能。建议:
通过系统化的慢查询管理,可以有效预防和解决数据库性能问题,保障业务系统的稳定运行。
附录:常用诊断命令
-- 查看当前运行中的慢查询
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 “`
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。