Mysql开启慢SQL并分析原因

下面是详细讲解如何开启慢SQL并分析原因的完整攻略：

下面是详细讲解如何开启慢SQL并分析原因的完整攻略：

1. 开启慢SQL日志

MySQL 提供了慢查询日志功能，可以记录执行时间超过规定阈值的 SQL 语句。通过开启慢SQL日志，可以了解到哪些SQL查询语句执行缓慢，以便优化性能，提高查询效率。

1.1 修改my.cnf配置文件

首先，我们需要修改my.cnf配置文件，以开启慢SQL日志。在使用过程中，可能需要先查看my.cnf文件的位置，可以通过如下命令查看：

mysql --help | grep -A 1 "Default options"

一般情况下，my.cnf文件的路径为 /etc/mysql/my.cnf 或 /etc/my.cnf 。我们在配置文件中添加如下内容：

[mysqld]
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

• slow_query_log：表示开启慢SQL日志功能
• long_query_time：表示执行时间超过1秒的 SQL 语句可以被记录到日志文件中（设置的单位是秒）
• log-queries-not-using-indexes：表示记录没有使用索引的查询语句
• slow_query_log_file：表示日志文件的路径

修改完成后，保存配置文件并重启 MySQL 服务。

1.2 查看慢SQL日志

慢SQL日志文件的默认位置是 /var/log/mysql/mysql-slow.log ，我们可以通过查看该日志文件，了解到执行缓慢的 SQL 语句。例如：

# Time: 2021-12-01T10:57:22.038517Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 11.035533  Lock_time: 0.000092 Rows_sent: 10  Rows_examined: 110636
SET timestamp=1638365842;
SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01';

上面的日志记录了一个执行耗时超过11秒的 SQL 查询语句。其中 Query_time 表示 SQL 语句的执行时间，Rows_sent 和 Rows_examined 分别表示返回结果行数和查询行数。

2. 分析慢SQL的原因

通过查看慢SQL日志，我们可以找到哪些 SQL 查询语句执行缓慢，但是日志并没有提供具体的优化方法。下面介绍几种常用的分析慢 SQL 查询语句的方法。

2.1 EXPLAIN

通过使用 MySQL 的 EXPLAIN 命令，可以分析 SQL 语句的执行计划，以便找到执行缓慢的原因。例如：

EXPLAIN SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01';

执行上述命令后，可以看到类似于下面的输出：

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1     SIMPLE         users    NULL          ALL     NULL             NULL   NULL      NULL   18178   10            Using where

其中，每一列的含义如下：

• id：查询的唯一标识符，同一条查询语句的标识符相同
• select_type：查询类型
• table：要查询的表名
• partitions：匹配的分区
• type：关联类型，使用的索引类型
• possible_keys：可能使用的索引列表
• key：实际使用的索引名称
• key_len：使用的索引长度
• ref：索引所用字段
• rows：根据条件查询的行数
• filtered：返回所有结果的百分比
• Extra：额外的信息（如使用了索引等）

其中，关注以下几点：

• type：查询的关联类型，一般优先使用 eq_ref、ref、range 等索引访问方式，尽量避免使用 ALL。ALL 表示全表扫描，执行效率非常低下。
• key：实际使用的索引名称，如果该列是 NULL，则表示查询没有使用索引。需要针对该 SQL 语句添加索引。
• Extra：如果有 Using filesort 或者 Using temporary 字段，一般会导致较差的效率，需要进行优化。

2.2 SHOW PROFILE

使用 SHOW PROFILE 命令可以查看 MySQL 执行 SQL 语句的详细执行情况，例如：

SET profiling = 1;
SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01';
SHOW PROFILES;

执行上述命令，可以看到类似于下面的输出：

Query_ID   Duration   Query
1          0.000512   SELECT * FROM `users` WHERE age>20 AND create_time>'2021-11-01'

其中，Query_ID 表示命令的唯一标识符，Duration 表示 SQL 查询的执行时间。

SHOW PROFILE 还可以显示更详细的信息，例如：

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

该命令可以显示命令 1 的 CPU 和 IO 信息。

示例说明

下面举两个示例来说明该攻略如何实际使用。

示例一

假设有一个名为 user 的表，其中包含如下字段：

id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
create_time DATE

现在，我们需要查询年龄大于20岁并且创建时间大于2021年11月1日的用户信息。

首先，我们需要修改 my.cnf 配置文件，开启慢 SQL 日志：

[mysqld]
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

然后，在 mysql 命令行界面中执行如下 SQL 查询语句：

SELECT * FROM `user` WHERE age>20 AND create_time>'2021-11-01';

执行完毕后，我们需要查看慢 SQL 日志文件中是否记录了该查询语句，可以使用如下命令查看：

cat /var/log/mysql/mysql-slow.log

如果该查询语句记录在日志文件中，我们可以使用 EXPLAIN 命令分析执行计划，例如：

EXPLAIN SELECT * FROM `user` WHERE age>20 AND create_time>'2021-11-01';

通过该命令的输出结果，可以看到查询没有使用索引，需要给 age 和 create_time 字段添加索引来优化查询效率。

示例二

假设有一个名为 order_item 的表，其中包含如下字段：

id INT PRIMARY KEY,
order_no VARCHAR(50),
product_id INT,
product_name VARCHAR(50),
quantity INT,
price DECIMAL(10, 2),
create_time DATE

现在，我们需要查询11月份所有订单的商品数量和总价。

首先，我们需要修改 my.cnf 配置文件，开启慢 SQL 日志：

[mysqld]
slow_query_log = 1
long_query_time = 1
log-queries-not-using-indexes = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

然后，在 mysql 命令行界面中执行如下 SQL 查询语句：

SELECT SUM(quantity), SUM(price) FROM `order_item` WHERE create_time>='2021-11-01' AND create_time<'2021-12-01';

执行完毕后，我们需要查看慢 SQL 日志文件中是否记录了该查询语句，可以使用如下命令查看：

cat /var/log/mysql/mysql-slow.log

如果该查询语句记录在日志文件中，我们可以使用 SHOW PROFILE 命令查看执行情况，例如：

SET profiling = 1;
SELECT SUM(quantity), SUM(price) FROM `order_item` WHERE create_time>='2021-11-01' AND create_time<'2021-12-01';
SHOW PROFILES;

通过 show profiles 命令的输出结果，我们可以看到该 SQL 查询语句的具体执行情况，例如执行时间、CPU 时间、I/O 时间等。可以通过这些信息，找到慢 SQL 的具体原因。