【实战篇】执行计划解析

执行计划解析

获取执行计划

数据库优化器可能会根据连接方式、连接条件等因素选择不同的执行计划。你可以通过 EXPLAIN 关键字查看两个查询的执行计划，以便更好地理解优化器的选择。

EXPLAIN SELECT ...
-- 替换 ... 部分为你的查询内容

观察执行计划并查看其中的关键信息，这可以帮助你理解查询优化器是如何选择执行计划的，从而找到可能导致性能差异的原因。

内容含义

执行计划是数据库优化器生成的一种表示查询执行方式的输出。它提供了关于查询如何执行的详细信息，包括表的访问顺序、使用的索引、连接方式等。

执行计划的输出可能会因数据库管理系统而异，下面是一些通用的解释：

1. ID： 序号，每个操作的唯一标识符，通常从 1 开始递增。

2. Select Type： 表示查询的类型，包括 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。

3. Table： 表名，操作涉及的表。

4. Type： 访问表的方式，包括 ALL（全表扫描）、INDEX（索引扫描）、range（范围扫描）等。

5. Possible Keys： 可能用于执行查询的索引。

6. Key： 实际用于执行查询的索引。

7. Key Length： 索引的长度。

8. Ref： 显示连接的列。

9. Rows： 预计需要检查的行数。

10. filtered： 表示经过表扫描或索引扫描后，通过过滤条件的行的百分比。具体来说，它表示在执行计划的某个步骤中，有多少行满足查询的 WHERE 子句或其他过滤条件。filtered 列的值范围是 0 到 100，表示过滤条件满足的行的百分比。以下是一些可能的情况：

    • 如果 filtered 为 100%，表示所有经过扫描的行都满足过滤条件。
    • 如果 filtered 为 0%，表示没有一行满足过滤条件。
    • 如果 filtered 介于 0% 和 100% 之间，表示部分行满足过滤条件。

    这个值的大小可以帮助开发人员分析查询性能。如果 filtered 较低，可能意味着过滤条件不够严格，导致了更多的行需要被检查。反之，如果 filtered 较高，则说明过滤条件较为有效，减少了不必要的行扫描，提高了查询性能。

11. Extra： 其他信息，可能包括文件排序、临时表等。

通过观察执行计划的输出，你可以了解查询是如何执行的，哪些步骤可能导致性能问题。

以下是一些常见的优化提示：

• 使用索引： 确保查询中的条件列上有索引。
• 避免全表扫描： 尽量避免 ALL 类型的访问，特别是对大表的情况。
• 合理使用连接： 确保连接条件足够明确，选择合适的连接类型。
• 考虑分区表： 对于大表，使用分区表可以提高查询性能。
• 注意临时表和文件排序： 如果看到 Using temporary 或 Using filesort，可能需要考虑索引或调整查询。

访问表的方式

以下是几种常见的访问表的方式：

1. ALL:

   • 全表扫描，效率最低。所有行都被读取来找到匹配的行。
   • 通常出现是因为缺少合适的索引。

2. index:

   • 全索引扫描，类似于全表扫描，但扫描的是索引树而不是数据行。
   • 比全表扫描更快，因为索引树的大小通常比数据行小。

3. range:

   • 范围扫描，通过索引查找位于指定范围内的行。
   • 适用于范围条件查询，比如 BETWEEN, <, >, <=, >= 等。

4. ref:

   • 非唯一索引扫描，返回所有匹配某一单个值的行。
   • 常见于非唯一索引和前缀索引的查询。

5. eq_ref:

   • 唯一索引扫描，返回最多一条匹配的行。

   • 通常用于主键或唯一索引查询。

   • 在执行计划中，eq_ref 是一种连接方式，表示等值连接。具体来说，eq_ref 是指使用索引查找关联表的唯一行。

     

     一般情况下，eq_ref 出现在连接条件中使用了【唯一或主键索引】，并且查询优化器能够确定被引用的表中的每个值只与另一表中的一个唯一值匹配。

     举例说明，假设有两张表 A 和 B，它们通过 A 表的主键或唯一键与 B 表关联。执行计划中的 eq_ref 表示对于 A 表的每一行，在 B 表中都只有一行与之匹配。

     -- 示例表结构
     CREATE TABLE A (
         id INT PRIMARY KEY,
         data VARCHAR(255)
     );
     
     CREATE TABLE B (
         id INT PRIMARY KEY,
         a_id INT,
         other_data VARCHAR(255),
         FOREIGN KEY (a_id) REFERENCES A(id)
     );
     
     -- 查询
     EXPLAIN SELECT *
     FROM A
     JOIN B ON A.id = B.a_id;
     

     在上述查询中，如果使用了 A 表的主键或唯一键索引，那么连接操作的类型可能会显示为 eq_ref。

     总的来说，eq_ref 是一种高效的连接方式，因为它表示连接的列是唯一的，每行都只匹配一次。这通常是通过主键或唯一键来实现的。

6. const:

   • 常量查询，当查询结果最多有一条匹配的行，并且优化器能够将其视为常量。
   • 通常用于主键或唯一索引的等值查询。

7. system:

   • 表只有一行（系统表），是 const 类型的特例。
   • 查询效率最高。

8. NULL:

   • MySQL 无需访问表或索引直接就能得出结果。
   • 例如 SELECT 1 + 1。

覆盖索引（Covering Index）

覆盖索引是指一个索引包含了查询所需的所有列，不需要再回表（访问数据行）来获取数据。这样能显著提高查询性能，因为索引通常比数据行小且紧凑。

示例：

假设有一张表 users，有索引 idx_name_email (name, email)：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    age INT,
    INDEX idx_name_email (name, email)
);

如果执行以下查询：

SELECT name, email FROM users WHERE name = 'John';

MySQL 可以直接从 idx_name_email 索引中获取 name 和 email，而不需要回表查询，从而提高查询性能。

索引下推（Index Condition Pushdown, ICP）

索引下推是在 MySQL 5.6 及之后引入的优化技术。在使用索引扫描时，MySQL 优化器会将查询条件 “推” 到索引扫描过程中，而不是在索引扫描后再进行过滤。

示例：

假设有一张表 employees，有索引 idx_last_name_first_name (last_name, first_name)：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    last_name VARCHAR(255),
    first_name VARCHAR(255),
    hire_date DATE,
    INDEX idx_last_name_first_name (last_name, first_name)
);

如果执行以下查询：

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name LIKE 'J%';

使用索引下推，MySQL 优化器会在扫描 idx_last_name_first_name 索引时，同时应用 first_name LIKE 'J%' 过滤条件，而不是扫描完索引后再应用过滤条件。这减少了不必要的行访问，提高了查询效率。