MySQL优化器与执行计划之：`MySQL`的`执行计划`：`explain`命令中`select_type`和`type`的详细解读。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，直接进入正题。

MySQL优化器与执行计划：`explain`命令中`select_type`和`type`的详细解读

大家好，今天我们来深入探讨MySQL执行计划中的两个关键属性：select_type和type。理解这两个属性对于优化SQL查询至关重要，它们能帮助我们了解MySQL如何执行我们的查询，并识别潜在的性能瓶颈。

1. 什么是执行计划？

在深入select_type和type之前，我们首先要明确什么是执行计划。执行计划是MySQL优化器为SQL查询生成的“蓝图”，它描述了MySQL将如何执行查询，包括使用的索引、表的访问顺序、连接类型等。通过分析执行计划，我们可以了解查询的执行方式，并根据需要进行优化。

我们可以使用EXPLAIN命令来获取SQL查询的执行计划。例如：

EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN命令会返回一个结果集，其中包含了关于查询执行的各种信息。select_type和type就是其中的两个重要列。

2. `select_type`：查询类型

select_type列指示了查询中每个SELECT语句的查询类型。它可以帮助我们理解查询的复杂性，以及MySQL是如何处理子查询和UNION等操作的。以下是select_type的常见取值及其含义：

`select_type`	描述	示例
`SIMPLE`	简单查询，不包含子查询或UNION。	`SELECT * FROM users WHERE age > 20;`
`PRIMARY`	最外层的SELECT查询，通常在包含UNION、子查询等复杂查询中出现。	`SELECT * FROM (SELECT id FROM users WHERE age > 20) AS t1;`
`SUBQUERY`	在SELECT或WHERE列表中包含的子查询。	`SELECT * FROM products WHERE category_id IN (SELECT id FROM categories);`
`DERIVED`	在FROM子句中包含的子查询，MySQL会将结果放在一个临时表中，也称为派生表。	`SELECT * FROM (SELECT id, name FROM users WHERE city = 'Beijing') AS t1;`
`UNION`	UNION语句中的第二个或之后的SELECT查询。	`SELECT id FROM users WHERE age > 20 UNION SELECT id FROM users WHERE city = 'Shanghai';`
`UNION RESULT`	UNION查询的结果集。	(参见`UNION`示例)
`DEPENDENT SUBQUERY`	子查询的结果依赖于外部查询的每一行。这种类型的子查询效率通常较低。	`SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE product_id = products.id);`
`DEPENDENT UNION`	UNION中的第二个或后面的SELECT查询，依赖于外部查询。	(通常不直接出现，而是与其他`select_type`组合使用)
`MATERIALIZED`	子查询的结果被物化为一个临时表。这通常发生在优化器认为物化子查询比重复执行子查询更有效时。	`SELECT * FROM order_details WHERE product_id IN (SELECT id FROM products WHERE price > 100);` (如果product子查询被物化)
`UNCACHEABLE SUBQUERY`	无法缓存的子查询，每次执行查询时都需要重新执行。这通常是由于使用了不确定的函数（如RAND()）或变量。	`SELECT * FROM users WHERE signup_date > (SELECT DATE_SUB(CURDATE(), INTERVAL RAND() * 30 DAY));`
`UNCACHEABLE UNION`	无法缓存的UNION查询，每次执行查询时都需要重新执行。这通常是由于使用了不确定的函数或变量。	(通常不直接出现，而是与其他`select_type`组合使用)

示例分析：

简单查询 (SIMPLE):

EXPLAIN SELECT id, name FROM users WHERE age > 25;

执行计划中的 select_type 列会显示 SIMPLE。这意味着这是一个简单的单表查询，没有子查询或UNION。

子查询 (SUBQUERY):

EXPLAIN SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York');

在这个例子中，外层查询的 select_type 通常是 PRIMARY，而子查询的 select_type 是 SUBQUERY。

派生表 (DERIVED):

EXPLAIN SELECT AVG(order_total) FROM (SELECT order_total FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31') AS monthly_orders;

这里，FROM子句中的子查询被视为派生表，其 select_type 为 DERIVED。最外层查询的 select_type 为 PRIMARY。

UNION:

EXPLAIN SELECT id, name FROM users WHERE city = 'London' UNION SELECT id, name FROM users WHERE country = 'UK';

第一个SELECT语句的 select_type 是 PRIMARY，第二个SELECT语句的 select_type 是 UNION，而用于合并结果的临时表的 select_type 是 UNION RESULT。

DEPENDENT SUBQUERY:

EXPLAIN SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE department_id = (SELECT id FROM departments WHERE name = products.department));

这个示例包含一个嵌套的子查询，且内部的子查询依赖于外部查询的 products.department 列。这会导致MySQL为 products 表的每一行都执行子查询，效率非常低。优化这类查询的关键是避免使用这种依赖关系，例如可以使用JOIN操作。

MATERIALIZED:

EXPLAIN SELECT * FROM orders WHERE product_id IN (SELECT id FROM products WHERE price > 100);

如果MySQL优化器决定将SELECT id FROM products WHERE price > 100的结果物化为一个临时表，那么这个子查询的select_type将会是MATERIALIZED。这意味着MySQL会先执行子查询一次，并将结果存储在一个临时表中，然后使用这个临时表来执行外层查询，而不是为外层查询的每一行都重新执行子查询。

理解 select_type 有助于我们识别查询中可能存在的性能问题。例如，DEPENDENT SUBQUERY 通常是一个性能瓶颈，应该尽量避免。 UNCACHEABLE SUBQUERY 也需要小心，因为它会导致查询每次执行时都需要重新计算子查询结果。

3. `type`：访问类型

type 列表示MySQL在表中找到所需行的方式，也就是访问类型。它是评估查询性能的重要指标，可以帮助我们确定查询是否使用了索引，以及使用了哪种类型的索引。一般来说，type 的取值越靠前（越接近 system），查询效率越高。以下是 type 的常见取值及其含义，按性能从好到坏排序：

`type`	描述	可能的优化方法
`system`	表中只有一行数据，或者使用了系统表。这是理想情况，但很少出现。	N/A
`const`	使用主键或唯一索引进行等值查询时，MySQL能够快速找到匹配的行。因为只匹配一行数据，所以很快。	确保查询条件使用了主键或唯一索引。
`eq_ref`	在连接查询中，使用主键或唯一索引作为连接条件，对于前表中的每一行，后表都只有一行匹配。这通常发生在两个表使用主键或唯一索引进行JOIN操作时。	确保连接条件使用了主键或唯一索引。
`ref`	使用非唯一索引进行等值查询。这意味着可能会找到多行数据。	考虑是否可以使用更具选择性的索引，或者优化查询条件以减少匹配的行数。
`ref_or_null`	类似于 `ref`，但MySQL需要额外搜索包含NULL值的行。	尽量避免在索引列上使用NULL值。可以考虑使用其他值来代替NULL，或者使用单独的索引来处理NULL值。
`index_merge`	使用了索引合并优化。这意味着MySQL使用了多个索引来查找行。这通常发生在对同一张表的不同列使用多个AND或OR条件，并且这些列都有独立的索引。	检查是否可以通过创建一个包含所有相关列的复合索引来避免索引合并。
`unique_subquery`	在 `IN` 子查询中使用唯一索引来查找匹配的行。	确保子查询使用了唯一索引。
`index_subquery`	在 `IN` 子查询中使用非唯一索引来查找匹配的行。	考虑是否可以使用唯一索引或JOIN操作来替代子查询。
`range`	使用索引进行范围查询，例如使用 `BETWEEN`、`>`、`<` 或 `IN` 等操作符。	确保范围查询的条件能够充分利用索引，并尽量避免范围过大的查询。
`index`	全索引扫描。MySQL会扫描整个索引树，而不是扫描表中的数据行。这通常发生在查询只需要索引中的列，而不需要访问实际的数据行时。例如，`SELECT COUNT(*) FROM table_name` 如果可以使用索引覆盖，则会显示 `index`。	确保查询只需要索引中的列，或者考虑创建一个包含所有需要列的覆盖索引。如果查询需要访问实际的数据行，则应该尝试使用更有效的索引访问类型，例如 `ref` 或 `range`。
`ALL`	全表扫描。MySQL会扫描整个表来找到匹配的行。这是最慢的访问类型，应该尽量避免。	检查是否可以添加索引来优化查询，或者优化查询条件以减少需要扫描的行数。

示例分析：

const:

EXPLAIN SELECT * FROM users WHERE id = 5;

如果 id 列是主键或唯一索引，那么 type 将会是 const。

eq_ref:

EXPLAIN SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.id;

如果 c.id 是 customers 表的主键或唯一索引，并且o.customer_id上有索引，那么customers表的 type 将是 eq_ref。

ref:

EXPLAIN SELECT * FROM users WHERE city = 'London';

如果 city 列上有一个非唯一索引，那么 type 将会是 ref。

range:

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

如果 order_date 列上有一个索引，那么 type 将会是 range。

index:

EXPLAIN SELECT COUNT(*) FROM users;

如果MySQL可以使用索引来满足 COUNT(*) 查询，而不需要访问实际的数据行，那么 type 将会是 index。例如 id 是主键。

ALL:

EXPLAIN SELECT * FROM users WHERE email LIKE '%@example.com';

如果 email 列上没有索引，或者查询条件无法使用索引（例如，使用了前导模糊匹配），那么 type 将会是 ALL。这意味着MySQL需要扫描整个 users 表来找到匹配的行。

优化建议：

避免 ALL 和 index： 这两种访问类型通常表示查询性能较差，应该尽量避免。
使用索引： 确保查询条件中使用的列都有合适的索引。
优化查询条件： 尽量使用精确匹配，避免使用范围过大的查询或模糊匹配。
覆盖索引： 如果查询只需要索引中的列，可以考虑创建一个覆盖索引。
重写查询： 有时候，可以通过重写查询来使用更有效的访问类型。例如，可以使用JOIN操作来替代子查询。

4. 结合`select_type`和`type`进行分析

select_type 和 type 应该结合起来分析，才能更全面地了解查询的执行情况。例如：

如果 select_type 是 SUBQUERY，而 type 是 ALL，那么说明子查询的性能可能存在问题，需要优化。
如果 select_type 是 DERIVED，而 type 是 ALL，那么说明派生表的创建过程可能比较耗时，需要优化。
如果 select_type 是 UNION，而 type 是 ALL，那么说明UNION中的某个查询可能性能较差，需要优化。

5. 案例分析

假设我们有两张表：users 和 orders。

users 表结构：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    city VARCHAR(255),
    age INT
);

INSERT INTO users (name, city, age) VALUES
('Alice', 'New York', 30),
('Bob', 'London', 25),
('Charlie', 'Paris', 35),
('David', 'New York', 28),
('Eve', 'London', 32);

CREATE INDEX idx_city ON users(city);

orders 表结构：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO orders (user_id, order_date, total_amount) VALUES
(1, '2023-01-01', 100.00),
(2, '2023-01-05', 200.00),
(1, '2023-01-10', 150.00),
(3, '2023-01-15', 300.00),
(2, '2023-01-20', 250.00);

现在我们执行以下查询：

EXPLAIN SELECT u.name, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'London' AND o.order_date BETWEEN '2023-01-01' AND '2023-01-15';

可能的执行计划如下：

id	`select_type`	table	partitions	`type`	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	u	NULL	ref	idx_city	idx_city	767	const	2	100.00	Using index condition
1	SIMPLE	o	NULL	ref	user_id	user_id	5	u.id	1	50.00	Using where; Using index condition

分析：

select_type 是 SIMPLE，表示这是一个简单的连接查询，没有子查询或UNION。
users 表的 type 是 ref，表示使用了 idx_city 索引进行查询。
orders 表的 type 是 ref，表示使用了 user_id 索引进行查询。
Extra 列显示 Using index condition，表示使用了索引下推优化。

这个执行计划看起来不错，但仍然可以优化。例如，可以考虑创建一个包含 user_id 和 order_date 的复合索引，以避免对 orders 表进行额外的过滤。

6. 优化工具

除了 EXPLAIN 命令，还有一些其他的工具可以帮助我们分析和优化MySQL查询：

MySQL Profiler： 可以用来分析查询的执行时间，找出耗时最多的步骤。
Performance Schema： 提供更详细的性能数据，例如锁的等待时间、I/O操作等。
pt-query-digest： 可以用来分析慢查询日志，找出最慢的查询。

掌握这些工具可以帮助我们更深入地了解MySQL的性能，并进行更有效的优化。

简而言之，select_type 告诉你查询的类型，type 告诉你MySQL如何访问表。结合两者，可以更清晰地了解查询的执行方式，并针对性地进行优化，提升数据库性能。希望今天的讲解对大家有所帮助。

MySQL优化器与执行计划：explain命令中select_type和type的详细解读

1. 什么是执行计划？

2. select_type：查询类型

3. type：访问类型

4. 结合select_type和type进行分析