MySQL慢查询日志与查询计划分析

MySQL慢查询日志

慢查询日志概述

在MySQL数据库的运维和优化过程中，慢查询日志是极为重要的工具。它记录了执行时间超过指定阈值的SQL查询语句，这个阈值可以由用户自行设定。通过分析慢查询日志，数据库管理员和开发人员能够快速定位哪些查询语句执行效率低下，进而对这些语句进行针对性的优化，提升整个数据库系统的性能。

MySQL从5.1版本开始支持慢查询日志功能，它默认是关闭的。开启慢查询日志后，MySQL会在每次执行SQL查询时，检查查询的执行时间。如果执行时间超过了设定的阈值（以秒为单位），该查询语句及其执行时间等相关信息就会被记录到慢查询日志文件中。

开启和配置慢查询日志

要开启慢查询日志，需要修改MySQL的配置文件。在Linux系统中，MySQL的配置文件通常是my.cnf或my.ini。以下是具体的配置步骤：

1. 编辑配置文件：
   • 使用文本编辑器打开my.cnf文件，例如在CentOS系统中，可以使用以下命令：

   sudo vi /etc/my.cnf
   

2. 添加或修改配置项：
   • 在[mysqld]部分添加或修改以下配置：

   # 开启慢查询日志
   slow_query_log = 1
   # 设置慢查询日志文件路径
   slow_query_log_file = /var/log/mysql/slow - query.log
   # 设置查询执行时间阈值，单位为秒，这里设置为2秒
   long_query_time = 2
   

   • slow_query_log = 1表示开启慢查询日志，0则表示关闭。slow_query_log_file指定了慢查询日志文件的路径和文件名。long_query_time设置了查询执行时间的阈值，超过这个时间的查询会被记录到慢查询日志中。
3. 重启MySQL服务：
   • 保存并关闭配置文件后，重启MySQL服务使配置生效。在CentOS系统中，可以使用以下命令：

   sudo systemctl restart mysqld
   

慢查询日志的格式与内容

慢查询日志文件中的每一条记录通常包含以下几部分内容：

1. 时间戳：记录查询开始执行的时间，格式通常为YYYY - MM - DD HH:MM:SS。
2. 查询执行时间：以秒为单位，精确到微秒，显示该查询实际执行所花费的时间。
3. 查询语句：完整的SQL查询语句，包括所有的参数和条件。
4. 锁等待时间：如果查询涉及到锁操作，这里会记录等待锁的时间。
5. 扫描的行数：查询过程中扫描的行数，这可以帮助判断查询是否扫描了过多的数据。

例如，以下是一条慢查询日志的示例：

# Time: 2023 - 10 - 05 14:30:25
# User@Host: root[root] @ localhost []  Id:     10
# Query_time: 3.567891  Lock_time: 0.000123 Rows_sent: 100  Rows_examined: 10000
SET timestamp = 1696492225;
SELECT * FROM users WHERE age > 30;

在这个示例中，查询于2023年10月5日14:30:25开始执行，执行时间为3.567891秒，锁等待时间为0.000123秒，返回了100行数据，扫描了10000行数据。

分析慢查询日志

1. 手动分析：
   • 对于少量的慢查询日志，可以通过直接查看日志文件进行分析。从查询语句本身出发，检查是否存在不合理的查询条件、是否使用了不合适的索引等。例如，如果发现某个查询扫描了大量的行，但返回的行很少，可能需要优化查询条件或添加合适的索引。
2. 使用工具分析：
   • mysqldumpslow：这是MySQL自带的慢查询日志分析工具。它可以对慢查询日志进行统计分析，提供一些有用的统计信息。例如，要统计慢查询日志中执行时间最长的前10条查询，可以使用以下命令：

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

   • 其中，-s t表示按照查询执行时间排序，-t 10表示只显示前10条记录。
   • pt - query - digest：这是Percona Toolkit中的一个工具，功能更为强大。它可以生成详细的报告，包括查询的执行频率、平均执行时间、最大执行时间等信息。首先需要安装Percona Toolkit，在Ubuntu系统中可以使用以下命令安装：

   sudo apt - get install percona - toolkit
   

   • 安装完成后，使用以下命令分析慢查询日志：

   pt - query - digest /var/log/mysql/slow - query.log
   

   • 该工具会输出一份详细的报告，帮助用户更全面地了解慢查询的情况，从而有针对性地进行优化。

查询计划分析

查询计划简介

查询计划是MySQL在执行SQL查询语句之前生成的一份执行方案。它描述了MySQL将如何执行查询，包括选择哪些表、使用什么连接类型、是否使用索引以及如何对数据进行排序等信息。通过分析查询计划，开发人员和数据库管理员可以了解查询的执行过程，发现潜在的性能问题，并对查询进行优化。

在MySQL中，可以使用EXPLAIN关键字来获取查询计划。EXPLAIN会在不实际执行查询的情况下，返回关于查询如何执行的详细信息。

使用EXPLAIN获取查询计划

要获取一个查询的查询计划，只需在查询语句前加上EXPLAIN关键字即可。例如，对于以下查询：

SELECT * FROM orders WHERE order_date > '2023 - 01 - 01';

使用EXPLAIN获取查询计划的语句为：

EXPLAIN SELECT * FROM orders WHERE order_date > '2023 - 01 - 01';

执行上述EXPLAIN语句后，MySQL会返回一个包含查询计划信息的结果集。结果集通常包含以下几列：

1. id：表示查询中每个SELECT语句的标识符。如果查询中包含子查询或联合查询，可能会有多个id值。
2. select_type：表示SELECT语句的类型，常见的类型有SIMPLE（简单查询，不包含子查询和联合查询）、PRIMARY（主查询，当查询包含子查询时，最外层的SELECT语句）、SUBQUERY（子查询）等。
3. table：表示当前行涉及的表名。
4. partitions：如果表是分区表，这里会显示查询涉及的分区。
5. type：表示连接类型，常见的连接类型有ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（使用非唯一索引进行连接）、eq_ref（使用唯一索引进行连接）、const（常量连接，通常用于主键或唯一索引的等值查询）等。连接类型从优到劣的顺序大致为：system > const > eq_ref > ref > range > index > ALL。
6. possible_keys：显示可能用于查询的索引。
7. key：显示实际使用的索引。如果为NULL，表示没有使用索引。
8. key_len：表示使用的索引长度。
9. ref：显示与索引进行比较的列或常量。
10. rows：表示MySQL估计要扫描的行数。
11. filtered：表示估计返回的行占扫描行的百分比。
12. Extra：包含一些额外的信息，例如Using index表示使用了覆盖索引，Using temporary表示使用了临时表，Using filesort表示需要文件排序等。

分析查询计划示例

假设有以下两个表：

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    category_id INT,
    INDEX (category_id)
);

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

现在执行以下查询并分析其查询计划：

EXPLAIN SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > 100;

执行结果可能如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	range	price,category_id	price	5	NULL	100	100.00	Using index condition
1	SIMPLE	c	NULL	eq_ref	PRIMARY	PRIMARY	4	test.p.category_id	1	100.00	NULL

1. id为1的行（对应products表）：
   • select_type为SIMPLE，表示这是一个简单查询。
   • type为range，说明使用了范围扫描，因为查询条件中有p.price > 100。
   • possible_keys显示了price和category_id两个索引，实际使用的key是price索引。
   • rows估计要扫描100行，filtered为100%，表示扫描的行都会被返回。Extra中的Using index condition表示使用了索引条件下推，即MySQL在索引扫描时就可以过滤数据，而不需要回表后再过滤。
2. id为1的行（对应categories表）：
   • type为eq_ref，这是一种比较高效的连接类型，因为通过p.category_id = c.id这种唯一索引（c.id是主键）进行连接。
   • rows估计只需要扫描1行，因为通过唯一索引可以快速定位到对应的行。

根据查询计划优化查询

1. 索引优化：
   • 如果type为ALL，表示全表扫描，通常性能较差。可以通过添加合适的索引来优化。例如，如果查询经常使用某个列作为条件，可以为该列添加索引。如上述示例中，如果经常根据category_id进行查询，并且没有索引，type可能为ALL，添加INDEX (category_id)后，type变为ref或range，性能会得到提升。
2. 避免文件排序：
   • 如果Extra中出现Using filesort，说明MySQL需要额外的排序操作，这可能会导致性能下降。可以通过在查询条件或ORDER BY子句中使用索引来避免文件排序。例如，对于SELECT * FROM users ORDER BY age;，如果age列没有索引，可能会出现Using filesort。为age列添加索引CREATE INDEX idx_age ON users(age);，可以优化查询。
3. 优化连接类型：
   • 尽量使用高效的连接类型，如eq_ref、ref等。这通常需要合理设计表结构和索引。例如，确保连接条件使用的列上有合适的索引，并且尽量避免使用复杂的子查询，因为子查询可能会导致连接类型变差。

复杂查询计划分析

当查询涉及多个表的连接、子查询、联合查询等复杂操作时，查询计划会更加复杂。例如，以下是一个包含子查询的查询：

EXPLAIN SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

执行EXPLAIN后，查询计划可能如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	products	NULL	ALL	price	NULL	NULL	NULL	1000	100.00	Using where
2	SUBQUERY	products	NULL	ALL	price	NULL	NULL	NULL	1000	100.00	Select tables optimized away

1. id为1的行（主查询）：
   • select_type为PRIMARY，表示这是主查询。
   • type为ALL，进行全表扫描，因为子查询的结果不能直接用于索引查找。
2. id为2的行（子查询）：
   • select_type为SUBQUERY。
   • type为ALL，同样是全表扫描。Extra中的Select tables optimized away表示MySQL对这个子查询进行了优化，通过优化器的计算，直接得出了子查询的结果，而不是实际执行全表扫描，但整体查询仍然因为主查询的全表扫描而性能不佳。

对于这种情况，可以考虑使用连接来替代子查询，优化后的查询如下：

EXPLAIN SELECT p1.*
FROM products p1
JOIN (SELECT AVG(price) AS avg_price FROM products) p2
ON p1.price > p2.avg_price;

优化后的查询计划可能如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		NULL	system	NULL	NULL	NULL	NULL	1	100.00	NULL
1	PRIMARY	p1	NULL	ALL	price	NULL	NULL	NULL	1000	100.00	Using where
2	DERIVED	products	NULL	ALL	price	NULL	NULL	NULL	1000	100.00	NULL

虽然这里主查询仍然是全表扫描，但通过将子查询转换为连接，MySQL的优化器可能会有更多的优化机会，例如在某些情况下可以利用索引进行连接操作，从而提升查询性能。

索引覆盖与查询优化

索引覆盖是一种重要的查询优化技术。当一个查询的所有列都包含在索引中时，MySQL可以直接从索引中获取所需的数据，而不需要回表操作，这大大提高了查询性能。例如，假设有以下表和查询：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    INDEX idx_name_salary (name, salary)
);

EXPLAIN SELECT name, salary FROM employees WHERE name LIKE 'John%';

查询计划可能如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	range	idx_name_salary	idx_name_salary	103	NULL	10	100.00	Using index

在这个例子中，Extra显示Using index，说明使用了索引覆盖。因为查询的name和salary列都包含在idx_name_salary索引中，MySQL可以直接从索引中获取数据，而不需要根据索引找到对应的行再回表获取数据，提高了查询效率。

如果查询改为SELECT id, name, salary FROM employees WHERE name LIKE 'John%';，由于id列不在idx_name_salary索引中，就无法使用索引覆盖，可能会出现回表操作，查询性能会下降。

多表连接查询计划优化

在多表连接的情况下，优化查询计划尤为重要。例如，有三个表orders、order_items和products：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

现在要查询每个订单及其包含的产品信息：

EXPLAIN SELECT o.order_id, o.order_date, p.product_name, p.price, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

查询计划可能如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	NULL	ALL	PRIMARY	NULL	NULL	NULL	100	100.00	NULL
1	SIMPLE	oi	NULL	ref	order_id	order_id	4	test.o.order_id	10	100.00	NULL
1	SIMPLE	p	NULL	eq_ref	PRIMARY	PRIMARY	4	test.oi.product_id	1	100.00	NULL

1. 对于orders表：
   • type为ALL，全表扫描。可以考虑在查询条件中添加合适的索引，或者如果经常按日期查询，可以为order_date列添加索引。
2. 对于order_items表：
   • type为ref，通过order_id索引与orders表连接，这是比较高效的连接类型。
3. 对于products表：
   • type为eq_ref，通过主键product_id与order_items表连接，也是高效的连接类型。

为了进一步优化，可以为orders表的order_date列添加索引：

CREATE INDEX idx_order_date ON orders(order_date);

然后重新执行EXPLAIN，如果查询条件中有关于order_date的过滤条件，查询计划中orders表的type可能会变为range或其他更高效的类型，从而提升查询性能。

子查询与联合查询的计划优化

1. 子查询优化：
   • 如前文所述，尽量将子查询转换为连接查询，因为连接查询通常更容易被MySQL优化器优化。例如，对于子查询SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);，转换为连接查询SELECT u1.* FROM users u1 JOIN (SELECT AVG(age) AS avg_age FROM users) u2 ON u1.age > u2.avg_age;。
   • 如果子查询无法避免，可以确保子查询中的条件使用合适的索引，并且尽量减少子查询的嵌套层数，因为嵌套层数过多会使查询计划变得复杂，降低查询性能。
2. 联合查询优化：
   • 联合查询（UNION）会将多个SELECT语句的结果合并。在使用UNION时，确保每个SELECT语句的查询计划本身是优化的。例如，每个SELECT语句都使用合适的索引，避免全表扫描。
   • 如果UNION的结果集较大，可以考虑使用UNION ALL代替UNION。UNION会自动去除重复的行，而UNION ALL不会，因此UNION ALL的性能通常更好，除非确实需要去除重复行。

执行计划缓存与性能影响

MySQL有执行计划缓存机制，它会缓存查询的执行计划。当相同的查询再次执行时，MySQL可以直接使用缓存中的执行计划，而不需要重新生成，这提高了查询的执行效率。

然而，执行计划缓存也可能带来一些问题。例如，如果表结构或数据发生了较大变化，缓存的执行计划可能不再是最优的，但MySQL仍然会使用缓存的计划，导致查询性能下降。为了解决这个问题，当表结构或数据发生重大变化时，可以通过FLUSH QUERY CACHE语句清除查询缓存，或者重启MySQL服务，使MySQL重新生成执行计划。

同时，对于一些动态查询，如使用了不同参数的预处理语句，执行计划缓存可能无法充分发挥作用。在这种情况下，需要确保查询的设计能够尽量利用执行计划缓存，例如尽量使用固定的查询结构，减少动态部分。

总结

MySQL慢查询日志和查询计划分析是数据库性能优化的重要手段。通过开启和分析慢查询日志，可以快速定位执行效率低下的查询语句。而通过EXPLAIN分析查询计划，可以深入了解查询的执行过程，发现潜在的性能问题，并通过索引优化、连接类型优化、避免文件排序等方法对查询进行针对性的优化。在实际的数据库开发和运维中，熟练掌握这些技术，能够有效提升MySQL数据库系统的性能，满足业务需求。同时，需要注意执行计划缓存等相关机制对查询性能的影响，确保查询始终以最优的方式执行。