如何优化 MySQL 系统中的 SQL：查找瓶颈

我是来自 Beyond 的 Haraoka。
今天我将谈谈 MySQL SQL 调优。

 

• 系统运行缓慢！
• 服务器突然变慢了！

 

在类似情况下，SQL 调优可以产生显著效果。
这次，我们将向您展示如何找到性能瓶颈。

・为什么要进行 SQL 调优？

系统突然变慢的原因中，大约有一半（50%）是数据库引起的。
如果数据库出现问题，我认为其中70%的问题都源于SQL。

100% × 0.5 × 0.7 = 35%

所以，如果你学习了 SQL 调优，
你应该能够解决导致系统突然变慢的问题的大约三分之一！

*基于过往经验

・SQL调优流程

让我们开始吧。SQL
调优的一般流程如下：

① 识别冗余 SQL
↓
② 分析 SQL
↓
③ 修改 SQL 或添加索引

识别冗长的 SQL

首先，找出耗时的 SQL 语句。

・慢日志输出

slowlog 的，并记录耗时较长的 SQL 语句。
这样，您可以将执行时间超过一定时长的 SQL 语句记录到日志中。

要输出 Slowlog，请在 my.cnf 中写入以下内容。

[mysqld] slow_query_log = 1 --- 启用慢查询日志 long_query_time = 1 --- 设置 SQL 输出时间

最简单的方法是从 1 秒开始设置
long_query_time 这是因为 long_query_time 可以以 0.1 秒为增量进行设置，但将其设置为 0.1 秒可能会导致大量输出，因此
最好从 1 秒或更长的 SQL 语句开始。

从 5.1 系列开始，该设置已成为一个全局变量，无需重启服务器即可更改，因此可以在服务器运行时进行设置。

mysql> set global slow_query_log = 1; mysql> set global long_query_time = 1; mysql> show variables like "%query%"; +--------------------------------+----------------+ | Variable_name | Value | +--------------------------------+----------------+ | long_query_time | 1.000000 |

如果仅使用“设置全局”选项，重启电脑后设置将被重置，因此需要将其添加到配置文件中。
*以上方法不适用于 5.0 系列系统，因此设置完成后需要重启电脑。

$ vi /etc/my.cnf --------------------- [mysqld] slow_query_log = 1 long_query_time = 1 --------------------- $ service mysqld 重新启动

如果您不指定文件路径，数据将输出到数据目录。

$ ll /var/lib/mysql/mysql-slow.log

・如何阅读慢博客

查看日志输出，你会看到以下日志内容：

$ tail -100 mysql-slow.log

(1)# 时间：150926 4:30:04 (2)# 用户@主机：hogeuser[hogedb] @ [172.16.0.1] (3)# 查询时间：1.644283 锁定时间：0.542217 发送行数：3 检查行数：9971 使用 hogedb; (4)SET timestamp=1424145644; SELECT 1 FROM aaa_history WHERE id = 26 AND code = 'bbbb';

这里重要的是Rows_examined （已处理的行数）。
它表示“数据库已读取的数据量”。
换句话说，该值越高，读取的数据就越多。

此外，如果将其除以 Rows_sent，就可以找出读取了多少行才能输出一行。

已检查行数：9971 ÷ 已发送行数：3 = 3323

所以需要 3323 行数据才能输出一行。

所以，快速查看一下日志，找出 Rows_examined 值较大的 SQL 语句。Rows_examined
值较大的 SQL 语句通常是性能瓶颈。

运行 mysqldumpslow

如果您想从输出的慢日志中收集统计信息并识别耗时较长的 SQL 语句，
可以使用
`mysqldumpslow`使用此命令可以轻松找到执行频率较高但略微耗时的 SQL 语句

・提取日志

您可以直接对慢日志运行 mysqldumpslow，但
如果日志是在长时间运行后积累的，则通过将日志提取到适当数量的行来更容易识别最近的繁重 SQL。

$ tail -10000 /var/lib/mysql/mysql-slow.log > /home/hogeuser/160721_mysql-slow.log

・运行 mysqldumpslow

使用 mysqldumpslow 分析上面提取的日志，并将其写入单独的文件。

$ mysqldumpslow -st /home/hogeuser/160721_mysql-slow.log > /home/hogeuser/160721_mysqldumpslow.log

-s
sort_type 参数决定输出结果的排序方式。
排序选项如下：

上述命令将输出慢日志统计信息。

- 检查输出结果

如果您检查刚刚输出的文件，您会发现查询是按执行时间降序排列的。

$ 更多 160721_mysqldumpslow.log

①计数：762 时间=14.12秒（10761秒）锁定=0.00秒（0秒）行数=45.9（34974），5个用户@13个主机使用aaa_db；设置时间戳=N；②从aaa_history中选择所有id=N且mid=N且kid在(N,N,N,N,N,N,N,N,N,N,N,N,N)且status=N

①

②
在 SQL 输出结果中排名靠前但 Count 值低、平均执行时间长的 SQL 语句属于负载较高的 SQL 语句。
这很容易从慢日志中找到瓶颈。

输出结果顶部出现的那些 SQL 语句，其计数较大、平均执行时间较短、累计执行时间较长，通常执行量较大且出现频率较高。
这些是性能瓶颈，如果不使用 mysqldump 工具，很难发现它们。

接下来，我们将解释如何解析 SQL。

如何在运行 MySQL 的系统中优化 SQL（第二部分：SQL 分析）