使用EXPLAIN分析MySQL查询性能

MySQL查询性能概述

在数据库应用开发中，MySQL作为一款广泛使用的关系型数据库管理系统，查询性能至关重要。高效的查询能确保系统快速响应，提升用户体验，而性能低下的查询则可能导致系统瓶颈，影响业务正常运转。影响MySQL查询性能的因素众多，包括数据库设计、查询语句编写、索引使用以及服务器配置等。

查询性能主要体现在查询执行时间和资源消耗上。执行时间短意味着用户能更快获取所需数据，资源消耗低则可以在有限的服务器资源下处理更多查询请求。例如，在一个电商网站中，商品查询功能如果响应时间过长，用户可能会放弃操作，从而影响销售业绩。因此，优化MySQL查询性能是数据库开发和运维人员的重要任务。

EXPLAIN简介

EXPLAIN是什么

EXPLAIN是MySQL提供的一个强大工具，用于分析查询语句的执行计划。通过EXPLAIN，我们可以了解MySQL如何执行一个查询，包括如何选择表的连接顺序、使用哪些索引以及预估的查询执行成本等信息。这些信息对于优化查询语句、提升查询性能至关重要。

当我们在查询语句前加上EXPLAIN关键字并执行时，MySQL会返回一个包含查询执行计划详细信息的结果集。例如，对于以下简单的查询语句：

EXPLAIN SELECT * FROM users WHERE age > 30;

执行后，我们将得到关于此查询执行计划的详细信息。

EXPLAIN的作用

1. 分析查询语句：帮助我们理解MySQL如何解析和执行查询语句。例如，它可以告诉我们MySQL是否选择了最优的索引，以及表之间的连接方式是否合理。
2. 优化查询性能：通过分析EXPLAIN的输出结果，我们能够发现查询中存在的性能问题，如全表扫描、索引使用不当等，并针对性地进行优化。比如，如果发现某个查询使用了全表扫描，我们可以考虑添加合适的索引来减少扫描的数据量，从而提高查询性能。
3. 评估索引有效性：可以判断当前使用的索引是否真正有效，以及是否需要创建新的索引。例如，如果EXPLAIN结果显示某个索引并未被使用，我们就需要分析原因，看是否是索引设计不合理或者查询条件无法利用该索引。

EXPLAIN输出结果详解

id

id是SELECT标识符，在一个查询语句中，每个SELECT关键字都有一个唯一的id。如果查询语句中包含子查询或者联合查询，id值会根据查询的层次结构进行分配。

• 单表查询：对于简单的单表查询，id通常为1。例如：

EXPLAIN SELECT * FROM products;

这里的id值为1，表示这是一个简单的单表查询。

• 子查询：在子查询中，id值会根据子查询的嵌套层次递增。例如：

EXPLAIN SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 1);

在这个例子中，外层查询的id为1，内层子查询的id为2。id值越大，查询的执行优先级越高，MySQL会先执行id值大的查询。

• 联合查询：在联合查询（UNION）中，每个SELECT语句也会有不同的id值。例如：

EXPLAIN SELECT * FROM users WHERE age > 30 UNION SELECT * FROM users WHERE gender = 'Male';

这里两个SELECT语句的id分别为1和2，MySQL会分别执行这两个查询，然后将结果合并。

select_type

select_type表示查询的类型，常见的类型有以下几种：

1. SIMPLE：简单查询，不包含子查询和联合查询。例如：

EXPLAIN SELECT * FROM customers WHERE country = 'USA';

此查询的select_type为SIMPLE。

2. PRIMARY：如果查询中包含子查询，最外层的查询为PRIMARY。例如：

EXPLAIN SELECT * FROM products WHERE product_id IN (SELECT product_id FROM product_reviews WHERE rating > 4);

这里外层查询的select_type为PRIMARY。

3. SUBQUERY：子查询，通常指在SELECT子句中的子查询。例如：

EXPLAIN SELECT product_name, (SELECT COUNT(*) FROM product_reviews WHERE product_id = products.product_id) AS review_count FROM products;

内部的子查询的select_type为SUBQUERY。

4. DERIVED：衍生表，指在FROM子句中使用的子查询。MySQL会将该子查询结果作为一个临时表（衍生表）来处理。例如：

EXPLAIN SELECT * FROM (SELECT product_id, AVG(rating) AS avg_rating FROM product_reviews GROUP BY product_id) AS derived_table WHERE avg_rating > 4;

这里FROM子句中的子查询的select_type为DERIVED。

5. UNION：联合查询中的第二个及后续的SELECT语句。例如：

EXPLAIN SELECT * FROM users WHERE age > 30 UNION SELECT * FROM users WHERE gender = 'Female';

第二个SELECT语句的select_type为UNION。

6. UNION RESULT：联合查询的结果集。例如，上述联合查询中，MySQL会将两个SELECT语句的结果合并，这个合并结果的select_type为UNION RESULT。

table

table表示当前查询涉及的表名。在复杂查询中，可能会涉及多个表，这里会依次列出每个表的名称。例如：

EXPLAIN SELECT * FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
JOIN products ON order_items.product_id = products.product_id;

在这个查询的EXPLAIN结果中，会依次列出orders、order_items和products这三个表。

partitions

partitions显示查询所涉及的分区。如果表是分区表，这里会显示具体的分区信息。例如，如果我们有一个按日期分区的销售记录表sales，查询某个时间段的销售数据：

EXPLAIN SELECT * FROM sales WHERE sale_date BETWEEN '2023 - 01 - 01' AND '2023 - 02 - 01';

如果该表按月份分区，EXPLAIN结果中的partitions字段可能会显示涉及的具体分区，如p202301、p202302等。

type

type表示表的连接类型，它反映了MySQL找到所需数据的方式，是评估查询性能的重要指标之一。常见的连接类型从优到劣依次为：

1. system：表只有一行记录（等于系统表），这是const类型的一种特例，查询效率极高。例如，当我们查询一个只有一条记录的配置表时：

EXPLAIN SELECT * FROM system_config;

如果system_config表只有一条记录，其type可能为system。

2. const：通过索引一次就找到记录，常用于主键或唯一索引的等值查询。例如：

EXPLAIN SELECT * FROM users WHERE user_id = 1;

如果user_id是主键，此查询的type很可能是const，因为MySQL可以通过主键索引快速定位到id为1的用户记录。

3. eq_ref：对于每个来自前面表的行组合，从该表中读取一行。这通常发生在多表连接中，并且连接条件是主键或唯一索引的等值连接。例如：

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

如果customer_id是customers表的主键，并且orders表中的customer_id字段与customers表的customer_id建立了索引关联，那么customers表的连接类型可能为eq_ref。

4. ref：对于每个来自前面表的行组合，从该表中读取所有匹配的行。它通常用于非唯一索引的等值查询或范围查询。例如：

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

如果category字段上有索引，此查询的type可能为ref，因为MySQL会通过category索引找到所有category为'Electronics'的产品记录。

5. ref_or_null：类似于ref，但会额外搜索包含NULL值的行。例如，在一个用户表中，可能有部分用户的邮箱字段为空，当我们查询特定邮箱或者邮箱为空的用户时：

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com' OR email IS NULL;

如果email字段有索引，此查询的type可能为ref_or_null。

6. index_merge：表示使用了索引合并优化方法，MySQL会同时使用多个索引来查找数据。例如，在一个产品表中，我们有price和rating两个索引，当我们查询价格在某个范围且评分高于一定值的产品时：

EXPLAIN SELECT * FROM products WHERE price BETWEEN 100 AND 200 AND rating > 4;

如果MySQL同时使用了price和rating索引来执行此查询，type可能为index_merge。

7. unique_subquery：用于子查询，子查询的结果是唯一的，并且通过唯一索引查找。例如：

EXPLAIN SELECT * FROM products WHERE product_id = (SELECT product_id FROM product_reviews WHERE rating = 5 ORDER BY review_date LIMIT 1);

如果product_id是product_reviews表的主键，子查询的type可能为unique_subquery。

8. index_subquery：与unique_subquery类似，但子查询的结果不是唯一的，通过索引查找。例如：

EXPLAIN SELECT * FROM products WHERE product_id IN (SELECT product_id FROM product_reviews WHERE rating > 4);

子查询的type可能为index_subquery。

9. range：只检索给定范围的行，使用一个索引来选择行。常见于使用BETWEEN、IN、>、<等操作符的查询。例如：

EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

如果age字段有索引，此查询的type可能为range。

10. index：全索引扫描，MySQL遍历整个索引树来获取数据，通常比全表扫描快，因为索引通常比表数据小。例如：

EXPLAIN SELECT age FROM users;

如果age字段有索引，且查询只涉及age字段，MySQL可能会选择全索引扫描，type为index。

11. ALL：全表扫描，MySQL会扫描整个表来查找所需数据，这是性能最差的连接类型。例如：

EXPLAIN SELECT * FROM large_table WHERE some_column = 'value';

如果some_column字段没有索引，此查询很可能是全表扫描，type为ALL。

possible_keys

possible_keys列出了在查询中可能使用到的索引。这些索引是MySQL根据查询条件和表结构分析出来的潜在可用索引。例如：

EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND order_date > '2023 - 01 - 01';

如果orders表在customer_id和order_date字段上都有索引，EXPLAIN结果中的possible_keys字段可能会列出这两个索引。然而，possible_keys只是潜在可用的索引，并不意味着MySQL实际会使用这些索引。

key

key显示MySQL实际使用的索引。如果为NULL，表示没有使用索引。例如，在上述orders表的查询中，如果MySQL实际使用了customer_id索引，key字段会显示该索引的名称。如果查询条件没有合适的索引可用，key字段为NULL，此时可能需要考虑优化索引或者调整查询语句。

key_len

key_len表示MySQL使用的索引长度。它反映了索引使用的字节数，通过key_len可以判断MySQL是否使用了完整的索引。例如，对于一个VARCHAR(255)类型的字段，如果该字段上有索引，且查询中使用了该索引，key_len的值会根据字符集和实际使用的索引前缀长度计算得出。在UTF - 8字符集下，VARCHAR(255)类型字段的完整索引长度可能为767字节（255 * 3 + 2，2为变长字段的额外字节数），如果key_len小于这个值，说明可能只使用了索引的前缀部分。

ref

ref显示哪些列或常量被用于与索引进行比较。例如，在以下查询中：

EXPLAIN SELECT * FROM products WHERE category = 'Clothing';

如果category字段有索引，ref字段会显示const，表示与常量'Clothing'进行比较。如果是多表连接查询，ref字段可能会显示连接的另一个表的字段名。

rows

rows表示MySQL预估要扫描的行数。这是一个预估值，并非实际扫描的行数，但可以帮助我们大致了解查询的成本。例如，如果一个查询预估要扫描10000行数据，而另一个查询预估只需要扫描100行数据，显然前者的成本更高。通过优化查询和索引，我们可以尽量减少rows的预估值，从而提高查询性能。

filtered

filtered表示按表条件过滤后剩余记录的百分比估计值。例如，如果一个表有1000条记录，filtered值为50%，意味着MySQL估计经过条件过滤后，还剩下500条记录。这个值结合rows可以更准确地评估查询的成本和性能。

Extra

Extra包含一些额外的信息，这些信息对于分析查询性能非常有帮助。常见的Extra信息如下：

1. Using index：表示使用了覆盖索引，即查询所需的数据都可以从索引中获取，不需要回表操作。例如：

EXPLAIN SELECT product_id, product_name FROM products WHERE category = 'Books';

如果product_id、product_name和category字段都包含在一个索引中，Extra字段可能会显示Using index，这通常会提高查询性能。

2. Using where：表示MySQL在存储引擎检索数据后使用了WHERE条件进行过滤。例如：

EXPLAIN SELECT * FROM users WHERE age > 30;

Extra字段会显示Using where，说明MySQL先从表中检索出数据，然后再应用age > 30这个条件进行过滤。

3. Using temporary：表示MySQL在执行查询时需要使用临时表来存储中间结果。这通常发生在排序、分组或者联合查询等操作中。例如：

EXPLAIN SELECT category, COUNT(*) AS product_count FROM products GROUP BY category;

在这个查询中，MySQL可能需要创建一个临时表来存储分组和计数的结果，Extra字段会显示Using temporary。使用临时表会增加查询的资源消耗和执行时间，应尽量避免。

4. Using filesort：表示MySQL需要进行文件排序操作。当查询结果需要按照某个字段排序，而该字段没有合适的索引支持时，就会出现这种情况。例如：

EXPLAIN SELECT * FROM orders ORDER BY order_date;

如果order_date字段没有索引，Extra字段会显示Using filesort，说明MySQL需要将查询结果读取到内存中进行排序。文件排序操作性能较低，应通过添加合适的索引来优化。

5. Impossible WHERE：表示WHERE条件永远为假，查询不会返回任何结果。例如：

EXPLAIN SELECT * FROM products WHERE 1 = 2;

Extra字段会显示Impossible WHERE，MySQL会优化掉这个查询，不会实际执行表扫描。

使用EXPLAIN优化查询示例

单表查询优化

假设有一个员工表employees，结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

我们要查询年龄大于30岁且在销售部门的员工信息：

EXPLAIN SELECT * FROM employees WHERE age > 30 AND department = 'Sales';

假设EXPLAIN结果显示type为ALL，possible_keys为NULL，这说明当前查询使用了全表扫描，没有合适的索引可用。我们可以考虑在age和department字段上创建联合索引：

CREATE INDEX idx_age_department ON employees(age, department);

再次执行EXPLAIN：

EXPLAIN SELECT * FROM employees WHERE age > 30 AND department = 'Sales';

此时，可能会发现type变为range，key显示为idx_age_department，说明索引已被使用，查询性能得到了提升。

多表连接查询优化

假设有订单表orders和客户表customers，结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    email VARCHAR(50),
    phone VARCHAR(20)
);

我们要查询所有客户及其订单信息：

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

假设EXPLAIN结果显示customers表的type为ALL，这可能是因为连接条件的索引使用不合理。我们可以确保customer_id字段在orders表上有合适的外键索引。如果没有，可以添加：

CREATE INDEX idx_customer_id ON orders(customer_id);

再次执行EXPLAIN，可能会发现customers表的连接类型变为eq_ref，查询性能得到改善。

子查询优化

假设有产品表products和产品评论表product_reviews，结构如下：

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

CREATE TABLE product_reviews (
    review_id INT PRIMARY KEY,
    product_id INT,
    rating INT,
    review_text TEXT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

我们要查询平均评分大于4的产品信息：

EXPLAIN SELECT * FROM products WHERE product_id IN (SELECT product_id FROM product_reviews GROUP BY product_id HAVING AVG(rating) > 4);

假设EXPLAIN结果显示子查询的type为ALL，性能较差。我们可以考虑将子查询改写为连接查询：

EXPLAIN SELECT products.* FROM products
JOIN (SELECT product_id FROM product_reviews GROUP BY product_id HAVING AVG(rating) > 4) AS subquery ON products.product_id = subquery.product_id;

通过这种方式，可能会使查询性能得到提升，因为连接查询通常比子查询更高效，并且更容易利用索引。

总结

通过深入了解EXPLAIN的各个输出字段，我们能够全面分析MySQL查询语句的执行计划，发现潜在的性能问题，并采取针对性的优化措施。无论是单表查询、多表连接查询还是子查询，都可以借助EXPLAIN工具来提升查询性能。在实际应用中，应养成在编写复杂查询语句后使用EXPLAIN进行分析的习惯，确保数据库系统的高效运行。同时，要结合具体的业务需求和数据特点，合理设计索引和优化查询语句，以达到最佳的查询性能。