​【香菇带你学Mysql】Mysql超长执行sql定位和优化【建议收藏】

2024-08-07 00:35:51 浏览数 (1)

本文为MySQL数据库管理员和开发人员提供了一套全面的超时SQL定位和优化解决方案。通过合理运用这些方法和技巧，可以显著提升MySQL数据库的性能和稳定性，减少超时SQL语句的发生，确保数据库的高效运行。

0. 引言

最近某个Mysql数据库频繁告警，监控系统提醒有执行时间超长sql（基本上是执行时间超过1800秒）的语句。

之前查的问题时存在某个Mysql8.0的Bug，Kill掉某个sql语句后，该sql语句实际还在运行，只能重启解决。但是由于是生产数据库。重启十分流程麻烦。进行两次维护重启后还是频繁告警，我在怀疑系统开发的sql语句到底写的是什么东西(只管查，不管耗时)。必须要和他们battle一番。要不最后还是我来背锅~

最近一次执行时间超过30分钟的还是有70 条

超长执行sql占用大量的数据库资源，如 CPU、内存和 I/O 带宽，导致其他并发的查询和操作响应变慢，甚至可能导致数据库死机。

今天就来和大家一起分析一下查询超长执行时间sql语句的方法和解决预防防控措施

1. 超长执行sql出现原因

数据量过大：

  • 当处理大规模的数据表时，例如涉及数百万甚至数十亿条记录的查询，即使查询逻辑相对简单，也可能需要较长时间来处理和返回结果。

复杂的查询逻辑：

  • 包含多层嵌套的子查询、多个表的连接操作、复杂的条件判断和聚合函数的组合使用。

比如，一个查询同时涉及 5 个表的连接，每个表又有复杂的筛选条件，并且还包含多层子查询来获取相关的关联数据。

缺少合适的索引：

  • 没有为经常用于查询、连接和排序的字段创建索引，导致数据库需要进行全表扫描来获取数据。

例如，在一个经常根据用户 ID 进行查询的用户表中，如果没有为用户 ID 字段创建索引，每次查询都要遍历整个表。

服务器资源不足：

  • 数据库服务器的硬件资源（如 CPU、内存、磁盘 I/O 性能）有限，无法快速处理复杂的查询请求。

当服务器的内存不足时，可能会频繁进行磁盘交换，从而大大降低查询的执行速度。

网络延迟：

  • 如果数据库服务器与应用程序服务器之间的网络连接不稳定或存在较高的延迟，数据传输时间会增加，导致 SQL 执行时间变长。

数据库配置不合理：

  • 例如，缓存设置过小、连接池参数配置不当等。

比如，缓冲池大小不足以容纳常用的数据，导致频繁的磁盘读取。

并发操作过多：

  • 同时有大量的并发查询或更新操作，导致资源竞争和排队等待，从而延长单个 SQL 语句的执行时间。

下图为网络段子

你问我外包算什么东西? 我现在告诉你： 1：你们正编不敢连表查询，我们外包10张表都敢连 2：你们正编不敢在循环里查询数据库，我们外包敢 3：你们正编写代码考虑扩展性，我们外包直接写死 总之一句话正编做得了的，我们外包要做，正编做不了的我们外包更要做，够不够清楚

3. sql 查询优化

正确的sql习惯可以在保证正确查询结果的前提下降低系统内存，IO的消耗，避免系统出现异常问题。

下面我将从可能导致数据库执行时间超长查询sql的情况来逐一分析并给出解决建议。

3.1 大表查询sql优化

对于数据量较大的sql语句(百万行及以上)，万万不可不可直接select *,执行时间很长不说，也很难找到我们需要的关键信息

以下是一个可能对千万级数据量的订单表进行全表扫描查询的示例 SQL 语句：

代码语言：sql复制
SELECT * FROM orders;

在这个示例中，* 表示选择所有列，orders 是订单表的表名。由于没有指定任何条件，这将导致对整个订单表进行全表扫描。

另一个示例是：

代码语言：sql复制
SELECT order_id, customer_name, total_amount FROM orders;

这个语句选择了 order_id、customer_name 和 total_amount 这几列，但同样没有添加任何筛选条件，仍然会进行全表扫描。

需要注意的是，在实际应用中，对如此大规模的数据量进行全表扫描通常是不推荐的，因为这可能会导致性能严重下降。应该尽量根据具体的业务需求添加合适的条件来缩小查询范围，例如：

代码语言：sql复制
SELECT * FROM orders WHERE order_date > '2024-08-01';

这个语句只会查询订单日期在 2024 年 8 月 1 日之后的订单，从而减少需要扫描的数据量，提高查询性能。

同样也可以限制查询结果数量

下面sql语句是从 orders 表中选取前 10 行的数据

代码语言：sql复制
SELECT * FROM orders BY order_id LIMIT limit 10；

3.2 复杂查询逻辑sql优化

对于复杂查询逻辑sql，我们尽量分开查询，避免一次执行，这样虽然可能看似方便，实际执行时间太长，导致查询效率很低

部分优化方法如下

使用合适的索引：

  • 分析查询中经常用于条件判断、连接和排序的字段，为这些字段创建合适的索引。

例如，如果经常根据 order_date 字段进行查询，可以创建索引：CREATE INDEX idx_order_date ON orders (order_date);

分解复杂查询：

  • 将一个复杂的大查询分解为多个较小的、更简单的查询，然后在应用程序层进行组合和处理。

比如，原本一个包含多个子查询和连接的复杂查询，可以拆分为先获取一部分数据，然后基于这部分数据再进行后续的查询。

避免不必要的计算和函数：

在WHERE子句中尽量避免使用复杂的计算和函数，这可能会导致索引无法使用。

例如，不要写成 WHERE YEAR(order_date) = 2024 ，而是直接写成 WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' 。

优化连接操作：

  • 确保连接条件准确且高效，尽量使用主键或有索引的字段进行连接。

比如，在连接两个表时，使用具有索引的 id 字段进行关联。

减少数据量：

  • 在查询的早期阶段通过条件过滤掉尽可能多的数据，减少后续操作的数据量。

先使用 WHERE 子句筛选出符合条件的记录，再进行其他操作。

使用临时表：

  • 对于一些中间结果集，可以将其存储在临时表中，以提高查询的可读性和性能。

调整数据库配置参数：

  • 根据服务器的硬件资源和业务需求，调整一些数据库的配置参数，如缓冲池大小、并发连接数等。

例如，有一个复杂的查询语句：

代码语言：sql复制
SELECT o.order_id, c.customer_name, SUM(o.order_amount) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-08-06'
GROUP BY o.order_id, c.customer_name;

优化后的可能如下：

代码语言：sql复制
-- 先创建一个临时表存储筛选后的订单数据
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-08-06';

-- 基于临时表进行连接和聚合操作
SELECT o.order_id, c.customer_name, SUM(o.order_amount) AS total_amount
FROM temp_orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.order_id, c.customer_name;

通过这样的优化，可以提高查询的性能和效率。

3.3 数据库配置优化

Mysql数据库常见配置文件名称如下

/etc/mysql/my.cnf /etc/my.cnf /etc/mysql/mysqld.cnf /etc/mysql/conf.d/ （这里可能包含多个配置文件） /usr/local/mysql/etc/my.cnf（如果 MySQL 是通过源码编译安装的话）

在配置文件中修改下面的信息，附内核配置文件优化修改脚本

调整缓冲池大小（InnoDB Buffer Pool）：

代码语言：ini复制
innodb_buffer_pool_size = 总内存的 50% - 80%

较大的缓冲池可以减少磁盘 I/O，加快数据访问速度。

增加并发线程数（Thread Concurrency）：

代码语言：ini复制
thread_concurrency = CPU 核心数 * 2

这有助于提高并发处理能力。

优化查询缓存（Query Cache）：

代码语言：ini复制
query_cache_type = 0  # 除非您的应用有大量相同的查询，否则建议关闭

调整连接参数：

代码语言：ini复制
max_connections = 根据预期的并发连接数设置

优化 InnoDB 日志文件：

代码语言：ini复制
innodb_log_file_size = 256M  # 根据写入量适当调整
innodb_log_buffer_size = 16M  # 缓冲日志数据

调整排序缓冲区大小（Sort Buffer Size）：

代码语言：ini复制
sort_buffer_size = 2M  # 根据需要适当增大

调整读缓冲区大小（Read Buffer Size）：

代码语言：ini复制
read_buffer_size = 2M  # 适当调整

启用并行查询（Parallel Query）：

代码语言：ini复制
innodb_parallel_read_threads = 4  # 根据硬件配置调整

Mysql配置参数优化脚本

下面脚本可以保存后执行，但是请确认你的配置文件路径，仅供参考

代码语言：sql复制
#!/bin/bash

# 备份原配置文件
# 这一步很重要，万一修改配置文件后出现问题，可以回滚到原始状态
cp /etc/my.cnf /etc/my.cnf.bak

# 打开配置文件进行编辑
vi /etc/my.cnf

# 添加或修改以下参数

# [mysqld] 部分表示以下的参数是针对 mysqld 服务的
echo "[mysqld]" >> /etc/my.cnf

# innodb_buffer_pool_size：InnoDB 缓冲池的大小，用于缓存表和索引数据。较大的值可以减少磁盘 I/O，提高性能。
# 这里设置为 512M，您可以根据服务器内存大小进行调整。
echo "innodb_buffer_pool_size = 512M" >> /etc/my.cnf

# thread_concurrency：设置并发线程数，根据服务器的 CPU 核心数进行适当调整，以提高并发处理能力。
echo "thread_concurrency = 8" >> /etc/my.cnf

# query_cache_type：查询缓存类型，设置为 0 表示关闭查询缓存。除非您的应用有大量重复的查询，否则关闭可能更好。
echo "query_cache_type = 0" >> /etc/my.cnf

# max_connections：设置最大并发连接数，根据预期的并发连接需求进行设置。
echo "max_connections = 200" >> /etc/my.cnf

# innodb_log_file_size：InnoDB 日志文件的大小，适当调整可以优化性能和恢复时间。
echo "innodb_log_file_size = 256M" >> /etc/my.cnf

# innodb_log_buffer_size：InnoDB 日志缓冲区的大小，缓冲日志数据以减少磁盘写入次数。
echo "innodb_log_buffer_size = 16M" >> /etc/my.cnf

# sort_buffer_size：排序缓冲区大小，用于排序操作，根据需要适当调整。
echo "sort_buffer_size = 2M" >> /etc/my.cnf

# read_buffer_size：读缓冲区大小，用于顺序读取数据，适当调整。
echo "read_buffer_size = 2M" >> /etc/my.cnf

# innodb_parallel_read_threads：InnoDB 并行读线程数，根据硬件配置调整，提高读取性能。
echo "innodb_parallel_read_threads = 4" >> /etc/my.cnf

4. 超长执行sql语句定位

下面我将介绍集中查询到底哪些sql语句在超时执行的语句，记录下来做针对性优化。

4.1 使用 SHOW PROCESSLIST 命令

代码语言：sql复制
SHOW PROCESSLIST;

这个命令会显示当前正在执行的所有连接和它们正在执行的 SQL 语句，以及执行的状态、时间等信息。您可以通过观察 Time 列来判断哪些语句执行时间较长。

4.2.开启慢查询日志

首先，需要在 MySQL 的配置文件（通常是 my.cnf 或 my.ini）中进行相关设置：

代码语言：ini复制
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 单位为秒，设置超过多长时间的查询被认为是慢查询

然后，重启 MySQL 服务使配置生效。之后，执行时间超过设定阈值的 SQL 语句会被记录到指定的慢查询日志文件中。

4.3.利用性能分析工具

例如 pt-query-digest 工具，它可以对 MySQL 的查询日志（包括慢查询日志）进行分析，帮助您找出执行效率低下的 SQL 语句。

例如，如果您的慢查询日志文件名为 mysql-slow.log，可以使用以下命令进行分析：

代码语言：plaintext复制
pt-query-digest mysql-slow.log

4.4 查询近期长时间执行sql

下图可以查询24h内执行时间超过AVG_TIMER_WAIT的语句和相关执行用户

代码语言：sql复制
-- 从 performance_schema.events_statements_summary_by_digest 表中选择数据
SELECT * 
FROM performance_schema.events_statements_summary_by_digest 
-- 筛选条件：摘要文本不包含'performance_schema'
WHERE DIGEST_TEXT NOT LIKE '%performance_schema%' 
  -- 平均等待时间大于1小时
  AND AVG_TIMER_WAIT > 3600000000000 
  -- 最后出现时间在过去 24 小时内
  AND LAST_SEEN >= DATE_SUB(NOW(), INTERVAL 24 HOUR) 
-- 按照平均等待时间降序排序
ORDER BY AVG_TIMER_WAIT DESCG

5. 超长sql监控脚本

作为DBA，善于利用shell脚本和定时任务可以极大的减轻工作量，提高工作效率。

我们可以通过编写定时任务的方法每天执行时间较长的sql查询语句，获取其相关信息到txt文本中。

  1. 首先，创建一个包含上述查询语句的 SQL 脚本文件，例如 slow_query_check.sql ，内容如下：
代码语言：sql复制
SELECT * 
FROM performance_schema.events_statements_summary_by_digest 
WHERE DIGEST_TEXT NOT LIKE '%performance_schema%' 
  AND AVG_TIMER_WAIT >3600000000000 
  AND LAST_SEEN >= DATE_SUB(NOW(), INTERVAL 24 HOUR) 
ORDER BY AVG_TIMER_WAIT DESCG

然后，使用 crontab -e 命令编辑定时任务。

在打开的文件中添加以下内容：

代码语言：sql复制
0 8 * * * 登录数据的命令，需要包含密码 < /home/mysql/zhangfakai/slow_query_check.sql > /home/mysql/zhangfakai/$(date  %Y%m%d_slow_query_result.txt)

每天早上 8 点就会自动执行查询，并将结果输出到指定的 txt 文本中。

我们每天上班后可以先查询txt查看有无异常。

6. 总结

本文详细探讨了MySQL数据库中出现超时SQL语句的原因、定位方法、以及相应的优化和预防措施。通过深入分析，我们了解到MySQL数据库超时执行SQL的问题主要源于数据量过大、复杂的查询逻辑、缺少合适的索引、服务器资源不足、网络延迟、数据库配置不合理以及并发操作过多等多方面因素。

针对这些问题，本文提出了多项优化策略。在SQL查询优化方面，强调了避免大表全表扫描、优化复杂查询逻辑、使用合适的索引、分解复杂查询、避免不必要的计算和函数、优化连接操作、减少数据量以及使用临时表等技巧。同时，也介绍了如何通过调整数据库配置参数（如缓冲池大小、并发线程数、查询缓存、连接参数等）来优化数据库性能。

在超时SQL语句的定位方面，本文介绍了使用SHOW PROCESSLIST命令、开启慢查询日志、利用性能分析工具（如pt-query-digest）以及查询近期长时间执行的SQL语句等多种方法。这些方法有助于快速定位问题SQL语句，从而进行针对性的优化。

最后，本文还提出了编写超长SQL监控脚本的建议，通过定时任务自动检测长时间执行的SQL语句，并生成报告，帮助DBA及时发现问题并进行处理。

腾讯技术创作特训营S8Mysql调优 香菇带你学Mysql

0 人点赞