【Mysql 底层原理】MySQL 查询优化器的工作原理：如何生成最优执行计划

👉博主介绍：博主从事应用安全和大数据领域，有8年研发经验，5年面试官经验，Java技术专家，WEB架构师，阿里云专家博主，华为云云享专家，51CTO 专家博主

⛪️ 个人社区：个人社区
💞 个人主页：个人主页
🙉 专栏地址： ✅ Java 中级
🙉八股文专题：剑指大厂，手撕 Java 八股文

在这里插入图片描述

文章目录

- - 1. MySQL 查询优化器概述
  - - 1.1 定义与作用
    - 1.2 优化目标
    - 1.3 优化过程
    - 1.4 查询优化器的工作流程
    - 1.5 查询优化器的重要性
  - 2. MySQL 查询优化器的原理
  - - 2.1 词法和语法分析
    - 2.2 语义分析
    - 2.3 生成逻辑执行计划
    - 2.4 基于成本的优化
    - 2.5 生成物理执行计划
    - 2.6 执行计划缓存
    - 2.7 动态调整
  - 3. MySQL 最优执行计划实战
  - - 3.1 使用 `EXPLAIN` 命令
    - 3.2 创建合适的索引
    - 3.3 避免全表扫描
    - 3.4 优化连接操作
    - 3.5 使用覆盖索引
    - 3.6 优化排序和分组
    - 3.7 优化子查询

1. MySQL 查询优化器概述

1.1 定义与作用

定义：MySQL 查询优化器是数据库管理系统中的一个关键组件，负责生成和选择执行 SQL 查询的最佳物理执行计划。
作用：查询优化器的主要任务是在多种可能的执行策略中找到一种最高效的执行方法，以最小化查询的执行时间、I/O 操作和 CPU 使用。

1.2 优化目标

最小化 I/O 操作：减少磁盘读写次数，提高查询速度。
减少 CPU 使用：通过优化计算步骤，减少 CPU 资源消耗。
缩短查询响应时间：提高查询的整体性能，使用户能够更快地获得结果。
利用索引：有效使用索引来加速数据访问。
避免全表扫描：尽可能避免全表扫描，而是使用更高效的数据访问方法。

1.3 优化过程

语法解析：将 SQL 语句解析成内部表示形式（如语法树）。
语义分析：验证语法树的语义正确性，进行类型检查和权限验证。
生成逻辑执行计划：将语法树转换为逻辑执行计划，通常表示为关系代数表达式。
基于规则的优化：应用一系列预定义的优化规则，简化逻辑执行计划。
基于成本的优化：估算不同物理执行计划的成本，并选择成本最低的方案。
生成物理执行计划：将逻辑执行计划转换为具体的物理操作步骤，包括访问路径、连接顺序和算法等。
执行计划缓存：将生成的执行计划缓存起来，以便后续相同或类似的查询可以直接使用。
执行计划调整：在查询执行过程中，根据实际情况动态调整执行计划，以应对数据分布的变化。

1.4 查询优化器的工作流程

接收 SQL 语句：
- 用户提交 SQL 查询。
- 查询优化器开始处理该查询。
词法和语法分析：
- 将 SQL 语句分解成词法单元（tokens）。
- 根据 SQL 语法规则构建语法树（Parse Tree）。
语义分析：
- 验证语法树的语义正确性。
- 进行类型检查和权限验证。
- 解析表名、列名等元数据信息。
生成逻辑执行计划：
- 将语法树转换为关系代数表达式。
- 应用基于规则的优化技术，如谓词下推、常量折叠等，简化逻辑表达式。
基于成本的优化：
- 利用统计信息（如表的行数、索引的选择性等）估算不同执行策略的成本。
- 选择成本最低的逻辑执行计划。
生成物理执行计划：
- 确定具体的物理操作步骤，包括访问路径（如全表扫描、索引扫描）、连接顺序和算法（如嵌套循环、哈希连接、排序合并等）。
- 选择合适的索引以加速数据访问。
执行计划缓存：
- 将生成的物理执行计划缓存起来，以便后续相同的查询可以直接使用，提高性能。
执行计划调整：
- 在查询执行过程中，根据实际的数据分布和运行时环境，动态调整执行计划，以优化性能。

1.5 查询优化器的重要性

提高查询性能：通过选择最优的执行计划，显著提高查询的执行效率。
减少资源消耗：优化 I/O 和 CPU 使用，降低系统负载。
提升用户体验：加快查询响应时间，提高用户的满意度。
支持复杂查询：能够处理复杂的 SQL 查询，确保数据库系统的稳定性和可扩展性。

2. MySQL 查询优化器的原理

MySQL 查询优化器的工作原理涉及多个步骤，从解析 SQL 语句到生成最优的物理执行计划。以下是查询优化器的主要工作原理和每个步骤的详细说明：

2.1 词法和语法分析

词法分析：将 SQL 语句分解成基本的词法单元（tokens），例如关键字、标识符、运算符等。
语法分析：根据 SQL 语法规则，将词法单元构建成语法树（Parse Tree）。语法树表示了 SQL 语句的结构。

2.2 语义分析

验证语义正确性：检查语法树是否符合语义规则，例如表和列是否存在、数据类型是否匹配等。
权限验证：确保用户具有执行该查询所需的权限。
元数据解析：解析表名、列名等元数据信息，并将其与系统目录中的信息进行匹配。

2.3 生成逻辑执行计划

关系代数表达式：将语法树转换为关系代数表达式。关系代数是一种描述数据库操作的数学语言，包括选择、投影、连接等操作。
基于规则的优化：应用一系列预定义的优化规则来简化逻辑执行计划。这些规则包括：
- 谓词下推：将过滤条件尽可能地推到数据访问层，减少需要处理的数据量。
- 常量折叠：计算并替换常量表达式的结果。
- 子查询展开：将子查询展开为更简单的形式，以便更好地优化。
- 冗余消除：移除不必要的操作，如重复的投影或连接。

2.4 基于成本的优化

统计信息：利用表和索引的统计信息（如行数、列值分布、索引选择性等）来估算不同执行策略的成本。
成本模型：定义一个成本模型，用于评估不同执行计划的成本。成本通常以 I/O 操作次数、CPU 使用时间等指标来衡量。
代价估算：对每种可能的执行策略进行代价估算，包括不同的访问路径（全表扫描、索引扫描）、连接顺序和算法（嵌套循环、哈希连接、排序合并等）。
选择最优计划：根据成本估算结果，选择总成本最低的执行计划。

2.5 生成物理执行计划

访问路径选择：确定最有效的表访问方法，例如：
- 全表扫描：读取整个表的数据。
- 索引扫描：使用索引来加速数据访问。
- 覆盖索引：如果索引包含了查询所需的所有列，则可以直接从索引中获取数据，而不需要访问表数据。
连接顺序与算法：确定表的连接顺序及使用的连接算法，例如：
- 嵌套循环连接：适用于小表之间的连接。
- 哈希连接：适用于大表之间的连接，特别是当连接条件是等值连接时。
- 排序合并连接：适用于有序数据的连接。
索引使用：决定是否使用索引以及使用哪些索引来加速查询。索引的选择基于统计信息和代价估算。

2.6 执行计划缓存

缓存机制：将生成的物理执行计划缓存起来，以便后续相同或类似的查询可以直接使用，从而提高性能。
缓存管理：定期清理缓存中的过期或无效的执行计划，以保持缓存的有效性和性能。

2.7 动态调整

运行时监控：在查询执行过程中，监控实际的执行情况和性能。
动态调整：根据实际情况动态调整执行计划，例如：
- 如果发现某个索引的性能不如预期，可以切换到其他索引。
- 如果数据分布发生变化，重新评估执行计划的成本并进行调整。

3. MySQL 最优执行计划实战

在实际应用中，生成和优化查询的执行计划是提高数据库性能的关键步骤。以下是一些实战技巧和示例，帮助你理解和优化 MySQL 查询的执行计划。

3.1 使用 `EXPLAIN` 命令

EXPLAIN 命令可以帮助你查看 MySQL 如何执行 SQL 语句，从而识别潜在的性能瓶颈。通过 EXPLAIN，你可以看到查询的执行计划，包括使用的表、访问类型、索引使用情况等信息。

示例：

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

输出解释：

id：查询的标识符。
select_type：查询的类型（如 SIMPLE, PRIMARY, SUBQUERY 等）。
table：涉及的表。
partitions：涉及的分区（如果有分区的话）。
type：访问类型（如 ALL, index, range, ref, eq_ref, const, system, NULL 等）。
possible_keys：可能使用的索引。
key：实际使用的索引。
key_len：使用的索引长度。
ref：与索引比较的列或常量。
rows：估计需要检查的行数。
filtered：按表条件过滤的百分比。
Extra：额外的信息，如 Using where, Using index, Using temporary, Using filesort 等。

3.2 创建合适的索引

索引是提高查询性能的重要手段。合理创建索引可以显著减少 I/O 操作和 CPU 使用。

示例：
假设有一个 orders 表，包含以下字段：

id (主键)
customer_id
order_date
amount

为了加速按 customer_id 和 order_date 的查询，可以创建复合索引：

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

3.3 避免全表扫描

全表扫描是最慢的访问方法之一。确保查询能够利用索引进行数据访问。

示例：

-- 不好的查询，可能导致全表扫描
SELECT * FROM orders WHERE amount > 100;-- 改进后的查询，利用索引
SELECT * FROM orders WHERE customer_id = 1 AND amount > 100;

3.4 优化连接操作

连接操作是查询中的常见操作，合理的连接顺序和算法选择可以显著提高性能。

示例：
假设有一个 customers 表和一个 orders 表，我们希望获取每个客户的订单总数。

不好的查询：

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

改进后的查询：

-- 使用子查询来减少连接的数据量
SELECT c.id, c.name, COALESCE(sub.order_count, 0) AS order_count
FROM customers c
LEFT JOIN (SELECT customer_id, COUNT(id) AS order_countFROM ordersGROUP BY customer_id
) sub ON c.id = sub.customer_id;

3.5 使用覆盖索引

覆盖索引是指查询的所有列都在索引中，这样查询可以直接从索引中获取数据，而不需要回表查找完整的数据行。

示例：
假设有一个 products 表，包含以下字段：

id (主键)
name
price
category_id

为了加速按 category_id 查询产品名称和价格，可以创建覆盖索引：

CREATE INDEX idx_category_name_price ON products (category_id, name, price);

然后使用该索引进行查询：

SELECT name, price FROM products WHERE category_id = 1;

3.6 优化排序和分组

排序和分组操作可能会导致大量的 I/O 操作和 CPU 使用。尽量使用索引来避免文件排序（filesort）。

示例：
假设有一个 employees 表，包含以下字段：

id (主键)
name
salary
department_id

为了按 department_id 分组并计算每个部门的平均工资，可以创建适当的索引：

CREATE INDEX idx_department_salary ON employees (department_id, salary);

然后使用该索引进行查询：

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

3.7 优化子查询

子查询有时会导致性能问题，特别是当子查询的结果集较大时。可以考虑将子查询转换为连接操作或其他更高效的查询形式。

示例：
假设有一个 orders 表和一个 order_items 表，我们希望获取每个订单的总金额。

不好的查询：

SELECT o.id, o.customer_id, (SELECT SUM(i.amount) FROM order_items i WHERE i.order_id = o.id) AS total_amount
FROM orders o;

改进后的查询：

SELECT o.id, o.customer_id, SUM(i.amount) AS total_amount
FROM orders o
JOIN order_items i ON o.id = i.order_id
GROUP BY o.id, o.customer_id;

精彩专栏推荐订阅：在下方专栏👇🏻
✅ 2023年华为OD机试真题（A卷&B卷）+ 面试指导
✅ 精选100套 Java 项目案例
✅ 面试需要避开的坑（活动）
✅ 你找不到的核心代码
✅ 带你手撕 Spring
✅ Java 初阶

在这里插入图片描述