深入理解MySQL查询执行计划

MySQL查询执行计划概述

在MySQL数据库中，查询执行计划是理解和优化查询性能的关键工具。当我们在MySQL中执行一条SQL查询语句时，数据库并不是简单地按照语句的书写顺序去执行。相反，MySQL查询优化器会分析查询语句，生成一个执行计划，这个计划描述了MySQL将如何执行查询，包括如何访问表、使用何种索引以及表之间的连接顺序等重要信息。

通过深入理解查询执行计划，开发者和数据库管理员能够发现查询中的性能瓶颈，进而优化查询语句，提高数据库的整体性能。

查看查询执行计划

在MySQL中，我们可以使用EXPLAIN关键字来查看查询的执行计划。例如，对于如下简单的查询：

EXPLAIN SELECT * FROM employees WHERE department = 'HR';

执行上述语句后，MySQL会返回一个包含执行计划详细信息的结果集。结果集中的每一行代表了执行计划中的一个操作步骤，各列提供了关于该操作的具体信息。

EXPLAIN输出列详解

1. id：这一列是查询中每个SELECT子句的唯一标识符。在复杂查询中，可能包含多个SELECT子句，每个子句都会有一个唯一的id。id值越大，优先级越高，执行顺序越靠前。如果id值相同，则按照执行计划中出现的顺序依次执行。

例如，对于如下查询：

EXPLAIN SELECT * FROM (
    SELECT * FROM employees WHERE salary > 50000
) AS subquery
WHERE department = 'Engineering';

子查询的id值通常会比外层查询的id值大，这表明子查询会先执行。

2. select_type：表示查询的类型，常见的类型有：
   • SIMPLE：简单查询，不包含子查询或UNION操作。
   • PRIMARY：最外层的查询。如果查询中包含子查询，那么最外层的查询就是PRIMARY类型。
   • SUBQUERY：子查询，指在SELECT或WHERE子句中嵌套的子查询。
   • DERIVED：衍生表，通常指在FROM子句中使用的子查询。MySQL会先执行该子查询并将结果存储为一个临时表（衍生表），然后再对这个临时表进行后续操作。例如：

EXPLAIN SELECT * FROM (
    SELECT employee_id, salary FROM employees WHERE salary > 40000
) AS derived_table;

这里的子查询就是DERIVED类型。 - UNION：在UNION操作中，除了第一个SELECT之外的其他SELECT语句属于这种类型。 - UNION RESULT：表示UNION操作的结果集。

3. table：表示执行计划中操作的表名。如果是子查询或衍生表，这里会显示对应的别名。

4. partitions：对于分区表，这一列显示查询将访问的分区。如果表不是分区表，则该列为NULL。例如，对于一个按日期分区的销售记录表：

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022)
);

当执行查询EXPLAIN SELECT * FROM sales WHERE sale_date BETWEEN '2020-01-01' AND '2020-12-31';时，partitions列可能会显示p1，表明只访问2020年数据所在的分区。

5. type：这一列非常重要，它表示表的访问类型，从最优到最差依次为：
   • system：表只有一行记录（等于系统表），这是一种特殊的const类型，访问效率极高。例如，在一些配置表中，如果只有一行数据，可能会以这种类型访问。
   • const：通过索引一次就找到记录。通常用于主键或唯一索引的等值查询。例如：

EXPLAIN SELECT * FROM employees WHERE employee_id = 1;

如果employee_id是主键，那么该查询的type就是const。 - eq_ref：对于每个来自前面表的行组合，从该表中读取一行。这通常发生在多表连接中，连接条件是主键或唯一索引的等值连接。例如：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
EXPLAIN SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

在这个查询中，如果orders.customer_id和customers.customer_id都有合适的索引，customers表的访问类型可能是eq_ref。 - ref：对于每个来自前面表的行组合，从该表中读取所有匹配的行。通常用于非唯一索引的等值或不等值查询。例如：

CREATE TABLE products (
    product_id INT,
    category VARCHAR(50),
    price DECIMAL(10, 2)
);
CREATE INDEX idx_category ON products(category);
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

这里使用了category列的索引，访问类型可能是ref。 - fulltext：使用全文索引进行查询。 - ref_or_null：类似ref，但还会额外搜索NULL值。例如，查询一个允许为NULL的列，并且该列有索引时可能出现这种类型。 - index_merge：表示使用了索引合并优化，即使用多个索引来检索数据。例如，一个表有两个索引，一个是index1，另一个是index2，查询条件同时涉及这两个索引列，MySQL可能会使用索引合并技术，此时type为index_merge。 - unique_subquery：用于子查询，并且子查询使用了唯一索引。 - index_subquery：类似于unique_subquery，但子查询使用的是非唯一索引。 - range：只检索给定范围的行，使用一个索引来选择行。常见于使用BETWEEN、IN、>, <等操作符的查询。例如：

EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;

如果salary列有索引，访问类型可能是range。 - index：全索引扫描，MySQL遍历整个索引来查询数据，比全表扫描快，因为索引通常比数据文件小。例如，查询EXPLAIN SELECT employee_id FROM employees;，如果employee_id列有索引，MySQL可能会选择全索引扫描，type为index。 - ALL：全表扫描，MySQL会扫描表中的每一行数据，这是最慢的访问类型。例如，在没有索引可用且查询条件无法有效过滤数据时，可能会出现这种类型。

6. possible_keys：显示可能用于查找行的索引。这些索引是MySQL基于查询条件和表结构分析出来的，但并不一定实际使用。例如，对于如下查询：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_username (username),
    INDEX idx_email (email)
);
EXPLAIN SELECT * FROM users WHERE username = 'John' OR email = 'john@example.com';

possible_keys列可能会显示idx_username和idx_email，表示这两个索引都有可能被用于该查询。

7. key：实际使用的索引。如果为NULL，则表示没有使用索引。例如，在上述查询中，如果MySQL选择使用idx_username索引，那么key列就会显示idx_username。

8. key_len：表示使用的索引中，实际使用部分的长度。通过key_len可以判断MySQL是否使用了完整的索引。例如，对于一个多列索引(col1, col2, col3)，如果key_len的值只对应col1的长度，说明只使用了该索引的第一列。

9. ref：显示哪些列或常量被用于查找索引列上的值。例如，在eq_ref类型的查询中，ref列会显示连接的另一张表的列名。

10. rows：MySQL估计为了找到所需的行而要读取的行数。这是一个估计值，不一定完全准确，但对于评估查询性能有重要参考价值。行数越少，通常表示查询性能越好。

11. filtered：表示存储引擎返回的数据在server层过滤后，剩下满足查询条件的数据的比例。例如，filtered为50，表示存储引擎返回的数据，经过server层过滤后，有50%的数据满足查询条件。

12. Extra：这一列包含了一些额外的信息，常见的有：

    • Using index：表示使用了覆盖索引，即查询所需的数据都可以从索引中获取，不需要回表操作。例如，查询EXPLAIN SELECT employee_id FROM employees WHERE employee_id > 10;，如果employee_id列有索引，并且MySQL能够通过索引获取到所有所需数据，Extra列会显示Using index。
    • Using where：表示MySQL在存储引擎返回数据后，需要在server层使用WHERE子句进行过滤。这通常意味着查询不能仅通过索引完成，可能需要回表操作。
    • Using temporary：表示MySQL需要创建一个临时表来存储中间结果。常见于GROUP BY或ORDER BY操作中，如果不能使用索引优化，就可能会出现这种情况。例如，查询EXPLAIN SELECT department, AVG(salary) FROM employees GROUP BY department;，如果没有合适的索引支持，Extra列可能会显示Using temporary。
    • Using filesort：表示MySQL需要对结果集进行文件排序，而不能使用索引直接完成排序。同样，在ORDER BY操作中，如果不能利用索引，就会出现这种情况。例如，查询EXPLAIN SELECT * FROM employees ORDER BY salary;，如果salary列没有合适的索引，Extra列会显示Using filesort。
    • Range checked for each record (index map: N)：表示MySQL会对每个从前面表中获取的记录，检查possible_keys中列出的索引，以确定是否使用该索引进行范围查询。N是一个索引掩码，用于表示可能使用的索引。

基于查询执行计划的优化策略

索引优化

从查询执行计划中，我们可以通过type、possible_keys、key等列来判断索引的使用情况。如果type为ALL，且key为NULL，说明没有使用索引，这时候需要考虑添加合适的索引。

例如，对于如下查询：

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

假设执行计划显示type为ALL，possible_keys为空，我们可以考虑在order_date列上添加索引：

CREATE INDEX idx_order_date ON orders(order_date);

添加索引后，再次执行EXPLAIN，type可能会变为range，key会显示idx_order_date，查询性能会得到显著提升。

但需要注意的是，索引并非越多越好。过多的索引会增加数据插入、更新和删除操作的开销，因为每次数据变动都需要更新相应的索引。

优化表连接

在多表连接查询中，连接顺序和连接类型对性能有很大影响。从执行计划的id和type等列可以分析连接情况。

连接顺序优化

MySQL查询优化器会尝试选择最优的表连接顺序，但有时也需要我们手动调整。例如，对于三个表A、B、C的连接查询：

SELECT * FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.id = C.b_id;

如果执行计划显示连接顺序不是最优，我们可以尝试调整连接顺序，例如：

SELECT * FROM B
JOIN A ON B.a_id = A.id
JOIN C ON B.id = C.b_id;

通过多次测试不同的连接顺序，结合执行计划中的rows等指标，选择性能最优的方案。

连接类型优化

不同的连接类型（如INNER JOIN、LEFT JOIN、RIGHT JOIN）在性能上可能存在差异。INNER JOIN通常性能较好，因为它只返回满足连接条件的行。而LEFT JOIN或RIGHT JOIN可能会返回更多的数据，特别是当连接条件不满足时。

例如，在某些情况下，如果可以将LEFT JOIN优化为INNER JOIN，且不影响业务逻辑，那么查询性能可能会提升。假设我们有如下查询：

SELECT * FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

如果业务需求允许，且可以确定customers表中的所有customer_id在orders表中都有对应记录，那么可以将其改为INNER JOIN：

SELECT * FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

这样在执行计划中，可能会减少不必要的数据扫描，提升查询性能。

子查询优化

子查询在MySQL中可能会带来性能问题，尤其是嵌套较深的子查询。通过执行计划分析子查询的select_type等信息，可以进行优化。

子查询改写为连接

很多情况下，子查询可以改写为连接查询，通常连接查询的性能更好。例如，有如下子查询：

SELECT * FROM employees WHERE department IN (
    SELECT department FROM departments WHERE location = 'New York'
);

可以改写为连接查询：

SELECT employees.* FROM employees
JOIN departments ON employees.department = departments.department
WHERE departments.location = 'New York';

改写后，执行计划可能会更优，因为连接操作通常比子查询更容易被MySQL优化器优化。

利用临时表优化子查询

对于一些复杂的子查询，MySQL可能会使用临时表来存储中间结果。如果执行计划中显示Extra列有Using temporary，可以考虑手动创建临时表并优化其结构和索引。

例如，对于如下子查询：

SELECT * FROM (
    SELECT employee_id, salary FROM employees WHERE salary > 50000
) AS subquery
WHERE department = 'Engineering';

如果执行计划显示性能不佳，可以手动创建临时表：

CREATE TEMPORARY TABLE temp_employees AS
SELECT employee_id, salary FROM employees WHERE salary > 50000;
CREATE INDEX idx_department ON temp_employees(department);
SELECT * FROM temp_employees WHERE department = 'Engineering';

这样通过优化临时表的索引，可以提升查询性能。

处理复杂查询中的性能问题

在实际应用中，经常会遇到复杂的查询，例如包含多个JOIN、GROUP BY、ORDER BY等操作的查询。通过分析执行计划，可以逐步优化这类复杂查询。

分解复杂查询

将一个复杂的查询分解为多个简单的查询，有时可以提高性能。例如，对于如下复杂查询：

SELECT customers.customer_name, SUM(orders.amount) AS total_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN products ON orders.product_id = products.product_id
WHERE products.category = 'Electronics'
GROUP BY customers.customer_name
ORDER BY total_amount DESC;

可以分解为两个查询：

-- 第一步：筛选出电子产品的订单
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders
JOIN products ON orders.product_id = products.product_id
WHERE products.category = 'Electronics';

-- 第二步：计算每个客户的总金额并排序
SELECT customers.customer_name, SUM(temp_orders.amount) AS total_amount
FROM customers
JOIN temp_orders ON customers.customer_id = temp_orders.customer_id
GROUP BY customers.customer_name
ORDER BY total_amount DESC;

通过分解查询，每个步骤的执行计划更简单，更容易优化。

优化GROUP BY和ORDER BY

在执行计划中，如果Extra列显示Using temporary和Using filesort，说明GROUP BY或ORDER BY操作可能没有使用索引优化。

对于GROUP BY，可以通过添加合适的索引来避免临时表的创建。例如，对于查询SELECT department, AVG(salary) FROM employees GROUP BY department;，如果在department列上添加索引：

CREATE INDEX idx_department ON employees(department);

再次执行EXPLAIN，Extra列可能不再显示Using temporary。

对于ORDER BY，同样可以通过索引优化。如果查询SELECT * FROM employees ORDER BY salary;，在salary列上添加索引：

CREATE INDEX idx_salary ON employees(salary);

执行计划中的Extra列可能不再显示Using filesort。

总结查询执行计划优化要点

1. 始终使用EXPLAIN来分析查询的执行计划，了解MySQL如何执行查询。
2. 关注type列，尽量将访问类型优化为更高效的类型，如const、eq_ref等。
3. 确保key列显示有实际使用的索引，避免ALL类型的全表扫描。
4. 对于多表连接，优化连接顺序和连接类型，减少数据扫描量。
5. 尽量避免复杂的子查询，将其改写为连接查询或使用临时表优化。
6. 优化GROUP BY和ORDER BY操作，通过索引避免临时表创建和文件排序。

通过深入理解和应用这些要点，我们能够更好地优化MySQL查询性能，提升数据库应用的整体效率。在实际工作中，要不断实践和总结经验，针对不同的业务场景和数据特点，制定最优的查询优化策略。