下面是详细讲解如何开启慢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 的具体原因。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:Mysql开启慢SQL并分析原因 - Python技术站