MySQL优化教程之慢查询日志实践

简介

MySQL 慢查询日志是用来识别在运行中响应较慢（超过阈值）的查询语句，目的是使开发人员和 DBA 能了解出哪些查询较慢，以及如何优化他们。在高访问量的应用中，慢查询会给数据库造成极大的负担，从而影响整个系统的性能，所以非常有必要及时发现并优化这些查询语句。

开启慢查询日志

在 MySQL 中，开启慢查询日志非常简单：只需要设置 slow_query_log 和 slow_query_log_file 两个参数即可。

# 设置慢查询日志的开启
set global slow_query_log = on;
# 设置慢查询日志的位置
set global slow_query_log_file = '/var/log/mysql/slow_query.log';

分析慢查询日志

我们可以使用 mysqldumpslow 工具来分析慢查询日志。

mysqldumpslow 工具会根据慢查询日志中的查询 SQL 语句提取出各种统计信息，并按照指定的排序字段顺序打印出来。通过分析这些信息，我们就能了解到有哪些 SQL 语句运行缓慢，并晋升一些优化的方案。

以查询时间排序的慢查询日志分析

下面以查询时间（-s t）排序的慢查询日志分析为例：

# 提取出慢查询日志中 SELECT 语句的统计信息，并按照查询时间排序
mysqldumpslow -s t /var/log/mysql/slow_query.log

输出结果类似于：

Count: 1  Time=68.93s (68s)  Lock=0.00s (0s)  Rows=0.0 (0), 
SELECT * FROM products WHERE price > 1000 AND category_id = 1;
Count: 13 Time=1.47s (19s)  Lock=0.00s (0s)  Rows=0.0 (0), 
SELECT * FROM orders WHERE user_id = 1001;
Count: 2  Time=0.30s (0s)   Lock=0.00s (0s)  Rows=0.0 (0), 
INSERT INTO products (name, price, description) VALUES ('iPhone', 899, 'The latest iPhone');
...

第一行表示慢查询日志中找到了一条 SELECT * FROM products WHERE price > 1000 AND category_id = 1 查询语句，该语句的查询时间为 68.93 秒，没有锁定时间，并且返回 0 行结果。

以查询次数排序的慢查询日志分析

我们还可以按照查询次数（-s c）排序来分析慢查询日志：

# 提取出慢查询日志中 SELECT 语句的统计信息，并按照查询次数排序
mysqldumpslow -s c /var/log/mysql/slow_query.log

输出的结果类似于：

Count: 1  Time=68.93s (68s)  Lock=0.00s (0s)  Rows=0.0 (0), 
SELECT * FROM products WHERE price > 1000 AND category_id = 1;
Count: 2  Time=0.30s (0s)   Lock=0.00s (0s)  Rows=0.0 (0), 
INSERT INTO products (name, price, description) VALUES ('iPhone', 899, 'The latest iPhone');
Count: 13 Time=1.47s (19s)  Lock=0.00s (0s)  Rows=0.0 (0), 
SELECT * FROM orders WHERE user_id = 1001;
...

优化慢查询语句

一旦我们发现了慢查询语句，我们需要对其进行优化。常见的方法包括如下几种：

• 添加索引
• 优化查询语句的逻辑
• 减少查询返回数据量
• 使用缓存等

下面以添加索引和减少查询返回数据量为两个例子进行说明。

添加索引

我们可以通过为关键字段添加索引来加快查询速度。

比如，如果查询 products 表格中价格大于 1000 并且类别为 1 的商品，可以为 price 和 category_id 字段添加联合索引：

# 为 price 和 category_id 两个字段添加联合索引
ALTER TABLE products ADD INDEX idx_price_category(price, category_id);

减少查询返回数据量

如果查询返回的数据过多，会导致查询速度变慢，甚至卡死数据库。我们可以通过更加细致的查询语句来减少查询返回的数据量。

比如，如果只需要查询 products 表格中价格大于 1000 并且类别为 1 的商品的数量，可以这样写查询语句：

# 查询条件为 WHERE price > 1000 AND category_id = 1 的商品数量
SELECT COUNT(*) FROM products WHERE price > 1000 AND category_id = 1;

而不是这样写：

# 查询条件为 WHERE price > 1000 AND category_id = 1 的所有商品
SELECT * FROM products WHERE price > 1000 AND category_id = 1;

结语

本文讲解了慢查询日志在 MySQL 优化中的作用和使用方法，同时提供了两个优化慢查询语句的例子。相信通过学习本文，你对如何根据慢查询日志来进行MySQL的优化有了更多的认识和实践。