MySQL定位并优化慢查询SQL的详细实例

背景

当我们的MySQL应用慢的时候，我们通常会使用慢查询日志来找出哪些查询语句是最慢的，以便优化它们并提高应用性能。下面将介绍如何使用慢查询日志找到慢查询SQL并进行优化。

步骤

1. 开启慢查询日志

在MySQL配置文件my.cnf中，将slow_query_log设置为1，并将log_slow_queries设置为日志文件的存储路径。

slow_query_log=1
log_slow_queries=/var/log/mysql/slow.log

需要重启MySQL生效。

2. 分析慢查询日志

使用工具如mysqldumpslow来将slow.log文件中的慢查询语句按照执行时间排序，并将结果输出到屏幕。

mysqldumpslow -s t /var/log/mysql/slow.log

输出的结果将包含慢查询语句的执行时间、次数、平均执行时间等信息，根据这些信息，我们可以找出哪些查询语句执行最慢。

3. 优化查询语句

示例1

慢查询日志显示的语句为：

# Time: 2021-09-10T10:10:12.123456Z
# User@Host: root[root] @ localhost []  Query_time: 3.112345  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 2000123
SELECT COUNT(*) FROM orders WHERE status = 'paid'

该查询语句是一个简单的统计订单表中状态为"paid"的记录数。但是，在订单表中有数百万条记录，每次执行该查询语句都需要扫描整个表，导致查询非常慢。

为了优化该查询语句，我们可以创建一个包含状态为"paid"的记录数的索引。首先，我们可以使用EXPLAIN命令来查看查询语句的执行计划。

EXPLAIN SELECT COUNT(*) FROM orders WHERE status = 'paid';

输出结果中的Extra字段将显示Using where，这表示MySQL将在执行查询之前扫描整个表。我们可以为status列创建一个索引来避免这种情况。

CREATE INDEX idx_status ON orders(status);

使用EXPLAIN命令再次查看查询语句的执行计划，我们可以看到MySQL使用了我们新创建的索引。

示例2

慢查询日志显示的语句为:

# Time: 2021-09-10T11:11:11.111111Z
# User@Host: root[root] @ localhost []  Query_time: 10.012345  Lock_time: 1.234567 Rows_sent: 10  Rows_examined: 100001
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC LIMIT 10;

该查询语句获取客户ID为123的最近10个订单。一般来说，如果customer_id列上已经存在一个索引，这个查询应该很快。但是，如果客户ID为123的订单非常多，那么MySQL可能需要扫描整个表或者很大的一个范围，导致查询变慢。

为了解决这个问题，我们可以将索引修改为包含customer_id和order_date两列。

CREATE INDEX idx_customer_order ON orders(customer_id, order_date);

这种情况下，MySQL可以使用索引来找到与客户ID匹配的记录，并按照order_date列排序，而不是需要扫描整个表。

结论

当然，这两个示例只是MySQL慢查询SQL优化的一些技巧。实际上，与MySQL相关的性能问题可能会非常复杂，处理这些问题可能需要更进一步的诊断和调整。但是，在你遇到性能问题的时候，使用慢查询日志是一个不错的开始，这将帮助你找到最慢最耗时的查询语句，并开始优化的工作。