如何将 SQL 调整到运行在 MySQL 上的系统 - 发现瓶颈 -

这是《Beyond》里的原冈。
今天我们来聊聊MySQL的SQL调优。

 

• 系统很慢！
• 服务器突然变重了！

 

在这种情况下，SQL 调优可以产生显着的效果。
这次，我们将解释如何找到瓶颈。

・为什么要进行 SQL 调优？

大约一半 (50%) 的系统突然变慢的原因是由数据库引起的。
当DB出现问题时，我认为70%是由SQL引起的。

100% × 0.5 × 0.7 = 35%

所以，如果你学会了SQL调优，
你应该能解决大约1/3的系统突然变重的问题！ ！

*基于过去的经验

・SQL调优的流程

我们马上就做吧。
一般来说，SQL 调优是这样的：

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

识别重型 SQL

首先，识别大量 SQL 语句。

・慢日志输出

slowlog，输出重SQL。
这允许您将花费一定时间的 SQL 输出到日志中。

Slowlog的输出在my.cnf中写入如下。

[mysqld] Slow_query_log = 1 ---启用slowlog long_query_time = 1 ---设置SQL输出时间

long_query_time开始很容易
原因是long_query_time可以以0.1秒为单位设置，但设置为0.1秒可能会导致大量输出，所以
最好先剔除长度超过1秒的SQL语句。

从5.1版本开始，它已经成为一个全局变量，允许您在不重新启动的情况下更改设置，因此您可以在服务器运行时设置它。

mysql> 设置全局 Slow_query_log = 1; mysql> 设置全局 long_query_time = 1; mysql> 显示“%query%”等变量； --------+----------------+ | 变量名 | +-------------------- - ------------+----------------+ | 1.000000 | 关闭 | ---------------------------------+----------------+

如果只设置全局，则重启后设置会恢复，所以将其添加到配置文件中。
*以上不适用于5.0系列，因此设置后需重新启动。

$ vi /etc/my.cnf --------------------- [mysqld] Slow_query_log = 1 long_query_time = 1 ------------ --------- $ service mysqld restrat

如果不指定文件路径，则输出将位于数据目录中。

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

・如何阅读慢日志

如果往里面看，会输出如下日志。

$ tail -100 mysql-slow.log

(1)# 时间: 150926 4:30:04 (2)# User@Host: hogeuser[hogedb] @ [172.16.0.1] (3)# Query_time: 1.644283 Lock_time: 0.542217 Rows_sent: 3 Rows_examined: 9971 use hogedb; 4)设置时间戳=1424145644;从aaa_history中选择1，其中id=26并且代码='bbbb'；

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

此外，通过除以 Rows_sent，您可以了解读取了多少行来输出一行。
在这种情况下

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

这意味着来输出一行

因此，快速查看日志并选择具有较大 Rows_examined 值的 SQL。
Rows_examined 值较大的 SQL 通常是瓶颈。

运行 mysqldumpslow

如果您想收集slowlog统计信息并识别较重的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

①Count: 762 Time=14.12s (10761s) Lock=0.00s (0s) Rows=45.9 (34974), 5users@13hosts use aaa_db; SET timestamp=N; ②select * from aaa_history where id = N and mid = N and kids in (N,N,N,N,N,N,N,N,N,N,N,N,N) 且状态 = N

①

SQL
中位于最前面
这是一个即使从 Slowlog 中也很容易找到的瓶颈。

输出结果中排名靠前、计数高、平均时间短、累计执行时间长的SQL，数量较多，出现次数较多。
这成为不使用 mysqldump 很难发现的瓶颈。

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

如何调整 SQL 以适应使用 MySQL 运行的系统第 2 部分 ~SQL 分析 ~