什么是慢查询？如何优化？

<think>我们正在讨论如何检测和识别数据库中的慢查询。根据引用[1]和引用[2]，我们可以采用以下步骤： 1. **启用慢查询日志**：这是MySQL内置的功能，可以记录执行时间超过指定阈值的SQL语句。 2. **分析慢查询日志**：通过查看日志文件或使用工具分析，找出需要优化的SQL。 3. **使用`EXPLAIN`命令**：分析查询的执行计划，了解查询的执行细节。 具体步骤如下： ### 一、启用慢查询日志 #### 方式1：修改配置文件（永久生效） 在MySQL配置文件（如`my.cnf`或`my.ini`）中添加以下配置： ```ini [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 # 定义慢查询的阈值，单位：秒 log_queries_not_using_indexes = 1 # 记录未使用索引的查询 ``` 重启MySQL服务使配置生效。 #### 方式2：动态设置（无需重启） ```sql SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; SET GLOBAL long_query_time = 1; # 设置阈值为1秒 SET GLOBAL log_queries_not_using_indexes = 'ON'; ``` > **注意**：`long_query_time`的最小精度为微秒，设置后可能需要重新连接才能生效。 ### 二、分析慢查询日志 #### 方式1：直接查看日志文件 慢查询日志是文本文件，可直接用文本工具查看： ```bash tail -f /var/log/mysql/slow.log ``` 日志中每条记录包含： - 执行时间（`Query_time`） - 锁定时间（`Lock_time`） - 返回行数（`Rows_sent`） - 扫描行数（`Rows_examined`） - SQL语句 #### 方式2：使用`mysqldumpslow`工具分析 MySQL自带工具可汇总慢查询日志： ```bash # 统计最慢的10条查询 mysqldumpslow -t 10 -s t /var/log/mysql/slow.log # 统计出现次数最多的慢查询 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log ``` #### 方式3：使用Percona Toolkit（高级分析） 安装Percona Toolkit后使用`pt-query-digest`： ```bash pt-query-digest /var/log/mysql/slow.log ``` 该工具提供： - 查询执行时间占比 - 锁时间统计 - 索引使用情况 - SQL语句抽象化（指纹） ### 三、使用`EXPLAIN`分析慢查询 针对日志中发现的慢SQL，使用`EXPLAIN`分析执行计划： ```sql EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status='pending'; ``` 关键指标解读： - **type**：访问类型（从优到差） ```sql system > const > eq_ref > ref > range > index > ALL ``` 目标至少达到`range`级别 - **key**：实际使用的索引 - **rows**：预估扫描行数 - **Extra**：额外信息 - `Using filesort`：需要额外排序 - `Using temporary`：使用临时表 - `Using index`：覆盖索引（良好） ### 四、性能监控系统集成 将慢查询监控集成到运维系统： 1. **Prometheus+MySQL Exporter**：实时采集`Slow_queries`指标 2. **ELK Stack**：将慢日志导入Elasticsearch可视化 3. **阿里云RDS/腾讯云CDB**：直接使用云数据库的慢查询分析功能 ### 五、识别高频慢查询模式 通过慢查询日志可识别以下典型问题： 1. **全表扫描**：`Rows_examined`远大于`Rows_sent` 2. **索引缺失**：日志中出现`log_queries_not_using_indexes`记录 3. **复杂排序**：`Extra`列出现`Using filesort` 4. **大表连接**：多表JOIN且扫描行数乘积过大 > **优化优先级**：优先处理执行次数多且耗时长的查询（参考公式） > $$优化价值 = \frac{总耗时}{执行次数} \times 出现频率$$ --- ### 效果验证示例 优化前后对比： | 查询ID | 优化前耗时 | 优化后耗时 | 下降比例 | |--------|------------|------------|----------| | Q001 | 2.8s | 0.12s | 95.7% | | Q034 | 1.5s | 0.05s | 96.7% | > 通过持续监控慢查询，可建立SQL性能基线（如P95响应时间<500ms）[^1][^2] --- §§相关问题§§ 1. 如何分析`EXPLAIN`执行计划中的`type`字段含义？ 2. 使用`pt-query-digest`分析慢日志有哪些高级技巧？ 3. 云数据库（如RDS）的慢查询分析与自建数据库有何不同？ 4. 如何自动化识别需要优化的慢查询？ 5. 慢查询优化后如何验证性能提升效果？</think>### 如何检测和识别数据库中的慢查询 #### 一、启用慢查询日志（核心方法） ```sql -- 动态开启（无需重启） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 定义慢查询阈值（单位：秒） SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录未使用索引的查询 ``` **配置文件永久生效**（`my.cnf`）： ```ini [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1 ``` > **关键参数说明**： > - `long_query_time`：执行时间超过此值的SQL会被记录 > - `log_queries_not_using_indexes`：捕获全表扫描查询 > - `min_examined_row_limit`：设置扫描行数阈值（避免记录小表查询）[^2] #### 二、分析慢查询日志 ##### 1. 原始日志分析 ```bash # 查看TOP 10慢查询 grep "Query_time" /var/log/mysql/slow.log | sort -rn | head -10 # 典型日志条目示例 # Query_time: 3.141592 Lock_time: 0.001000 Rows_sent: 100 Rows_examined: 100000 # SET timestamp=1689157890; # SELECT * FROM orders WHERE create_date BETWEEN '2023-01-01' AND '2023-12-31'; ``` ##### 2. 使用官方工具 `mysqldumpslow` ```bash # 按总耗时排序 mysqldumpslow -s t /var/log/mysql/slow.log # 按出现频率排序 mysqldumpslow -s c /var/log/mysql/slow.log # 输出示例 Count: 50 Time=2.34s (117s) Lock=0.00s (0s) Rows=10.0 (500) SELECT * FROM users WHERE age > N ``` ##### 3. 使用高级工具 `pt-query-digest`（Percona Toolkit） ```bash pt-query-digest /var/log/mysql/slow.log # 输出分析维度： # - 查询响应时间占比 # - 执行频率TOP 10 # - 表扫描统计 # - 锁等待分析 ``` #### 三、实时监控慢查询 ##### 1. 通过 `SHOW PROCESSLIST` 监控 ```sql -- 查看当前运行中的慢查询 SELECT * FROM information_schema.processlist WHERE TIME > 5 -- 执行超过5秒 AND COMMAND NOT IN ('Sleep'); ``` ##### 2. 使用性能模式（Performance Schema） ```sql -- 开启性能监控 UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%statement/%'; -- 查询TOP慢SQL SELECT sql_text, timer_wait/1000000000 AS exec_seconds FROM performance_schema.events_statements_history_long ORDER BY timer_wait DESC LIMIT 10; ``` #### 四、自动报警机制 ```bash # 日志监控脚本示例（每分钟检测） slow_count=$(grep "Query_time" /var/log/mysql/slow.log | wc -l) if [ $slow_count -gt 10 ]; then echo "警告：发现 $slow_count 条慢查询" | mail -s "数据库慢查询警报" [email protected] fi ``` #### 五、慢查询特征识别 通过分析日志可识别以下典型问题： 1. **全表扫描**：`Rows_examined` >> `Rows_sent` 2. **锁争用**：高`Lock_time`值 3. **索引缺失**：出现大量`log_queries_not_using_indexes`记录 4. **复杂排序**：`Extra`列显示`Using filesort` 5. **大结果集**：高`Rows_sent`值（>1000行） > **优化优先级公式**： > $$优化价值 = \frac{执行次数 \times 平均耗时}{实现难度}$$ > 优先优化高频高耗时的查询[^1] --- ### 效果验证示例 优化前后对比： | 查询ID | 优化前耗时 | 优化后耗时 | 扫描行数下降 | |--------|------------|------------|--------------| | QX-12 | 3.2s | 0.15s | 100,000→200 | | QR-87 | 1.8s | 0.08s | 50,000→150 | > 通过持续监控慢查询日志，可建立SQL性能基线（如P95响应时间<500ms）[^2] ---