如何优化 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 语句。

・慢日志输出

慢日志该日志输出的是
这些条目是耗时最长的 SQL 查询。

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

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

`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 |

直接使用 `set global` 命令会导致重启后设置恢复原状，因此我们需要将其添加到配置文件中。
*注意：上述方法不适用于 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 查询可以更轻松地找到

・提取日志

虽然可以直接对 slowlog 运行 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
在输出结果中排名靠前但执行次数少、平均耗时长的
这些是瓶颈，可以使用 slowlog 轻松识别。

输出结果中排名靠前的查询（即查询次数多、平均执行时间短、累计执行时间长的查询）很可能是资源密集型且执行频率高的 SQL 查询。
如果不使用 mysqldump，很难找到这些瓶颈。

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

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