wordpress站内301,wordpress去除更新提示,公司标志logo,wordpress阅读全文文章目录 MySQL 基于规则的优化 (RBO)#xff1a;RBO 的核心思想#xff1a;模式匹配与规则应用RBO 的主要优化规则查询重写 (Query Rewrite) / 查询转换 (Query Transformation)子查询优化 (Subquery Optimization) - RBO 的重中之重非相关子查询 (Non-Correlated Subquery)… 文章目录 MySQL 基于规则的优化 (RBO)RBO 的核心思想模式匹配与规则应用RBO 的主要优化规则查询重写 (Query Rewrite) / 查询转换 (Query Transformation)子查询优化 (Subquery Optimization) - RBO 的重中之重非相关子查询 (Non-Correlated Subquery) 优化相关子查询 (Correlated Subquery) 的优化 (有限的 RBO 优化) 视图合并 (View Merging)条件化简 (Predicate Simplification)外连接消除 (Outer Join Elimination)其他查询重写规则 访问路径选择 (Access Path Selection) - RBO 的早期角色 (现在更多由 CBO 负责)JOIN 顺序优化 (Join Order Optimization) - RBO 的早期角色 (现在更多由 CBO 负责) RBO vs. CBO各有千秋协同工作RBO 的局限性与 CBO 的优势RBO 优化指导与实践建议子查询优化子查询语法按返回结果集区分按与外层查询关系区分 子查询在布尔表达式中的使用子查询在 MySQL 中的执行方式 实战优化技巧IN vs EXISTS选择派生表优化 优化验证工具实际使用建议总结 MySQL 基于规则的优化 (RBO)
MySQL 查询优化器除了成本优化 (CBO) 外还包含一套基于规则的优化 (Rule-Based Optimization, RBO) 策略。RBO 就像 SQL 查询的 “整形医生”依据预定义的规则对查询进行快速的语法和语义转换提升查询效率。
RBO 的核心思想模式匹配与规则应用
RBO 的核心是 模式匹配 (Pattern Matching) 与规则应用 (Rule Application)。优化器预定义了一系列优化规则, 描述特定 SQL 模式的优化转换方式。优化器解析 SQL 查询时, 会尝试将查询与 RBO 规则进行匹配。如果匹配成功则应用规则对查询进行改写, 生成一个语义等价但可能更高效的新查询。
RBO 的主要优化规则
查询重写 (Query Rewrite) / 查询转换 (Query Transformation)
这是 RBO 最核心的功能通过改写 SQL 语句本身来优化。
子查询优化 (Subquery Optimization) - RBO 的重中之重
子查询是常见的性能瓶颈。RBO 针对不同类型的子查询应用不同的优化规则。
非相关子查询 (Non-Correlated Subquery) 优化
子查询的执行不依赖于外部查询的表。RBO 倾向于将非相关子查询 物化 (Materialization) 或 转换为连接 (Unnesting)。
IN** 子查询转换为 **JOIN** (Subquery Unnesting - IN to JOIN)* 将 WHERE column IN (SELECT ...) 形式的非相关 IN 子查询转换为等价的 INNER JOIN 或 LEFT SEMI JOIN。 -- 原始 SQL (IN 子查询)SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region North);-- RBO 转换后的 SQL (JOIN)SELECT o.* FROM orders oINNER JOIN customers c ON o.customer_id c.customer_idWHERE c.region North;
机制详解: RBO 识别出 IN 子查询是非相关的并且子查询的目的是过滤 orders 表的 customer_id。 因此它将子查询提取出来与外部查询的 orders 表进行 INNER JOIN 连接连接条件是 o.customer_id c.customer_id。 WHERE c.region ‘North’ 条件被保留。
SELECT * FROM orders o WHERE o.customer_id IN (SELECT c.customer_id FROM customers c WHERE c.coutryo.contry);注当子查询引用了外部查询的列时(相关子查询)其结果依赖于外部查询的每一行外部查询每一行都需要执行一次子查询非相关子查询 EXISTS** 子查询转换为 **JOIN** (Subquery Unnesting - EXISTS to JOIN)* 将 WHERE EXISTS (SELECT ...) 形式的非相关 EXISTS 子查询转换为 LEFT SEMI JOIN。 -- 原始 SQL (EXISTS 子查询)SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE dept_id departments.dept_id AND salary 100000);-- RBO 转换后的 SQL (LEFT SEMI JOIN)SELECT d.* FROM departments dLEFT SEMI JOIN employees e ON d.dept_id e.dept_id AND e.salary 100000;
机制详解: EXISTS 子查询用于判断是否存在满足条件的记录。 RBO 将其转换为 LEFT SEMI JOINLEFT SEMI JOIN 只返回左表 (departments) 中在右表 (employees) 中找到匹配行的记录且对于左表的每一行右表最多返回一行。 ON 子句中包含了连接条件 d.dept_id e.dept_id 和子查询的过滤条件 e.salary 100000。
物化 (Materialization) 非相关子查询: 对于某些非相关子查询RBO 可能会将子查询的结果 物化 为一个临时表。 -- 原始 SQL (非相关子查询多次引用)SELECT (SELECT COUNT(*) FROM orders WHERE status pending) AS pending_orders,(SELECT AVG(total_amount) FROM orders WHERE status completed) AS avg_completed_amount;-- RBO 可能物化子查询结果为临时表 (伪代码)CREATE TEMPORARY TABLE temp_subquery_result ASSELECT pending_orders AS result_name, COUNT(*) AS result_value FROM orders WHERE status pendingUNION ALLSELECT avg_completed_amount AS result_name, AVG(total_amount) AS result_value FROM orders WHERE status completed;SELECT result_value FROM temp_subquery_result WHERE result_name pending_orders;SELECT result_value FROM temp_subquery_result WHERE result_name avg_completed_amount;
机制详解: RBO 检测到两个相同的非相关子查询 (虽然 WHERE 条件不同但表和基本结构相同)。 为了避免重复计算RBO 可以将子查询结果预先计算出来并存储在一个临时表中。 外部查询直接从临时表中获取结果。 注意: MySQL 实际的物化策略比这个伪代码更复杂会考虑更多因素例如子查询结果集大小、查询复杂度等
相关子查询 (Correlated Subquery) 的优化 (有限的 RBO 优化)
子查询的执行依赖于外部查询的表。RBO 主要尝试将某些简单的相关子查询 转换为连接。
EXISTS** 相关子查询转换为 **JOIN** (有限的 Unnesting)* 某些简单的 EXISTS 相关子查询RBO 可以尝试转换为 JOIN例如 LEFT SEMI JOIN。 -- 原始 SQL (简单的 EXISTS 相关子查询)SELECT * FROM customers c WHERE EXISTS (SELECT * FROM orders o WHERE o.customer_id c.customer_id AND o.order_date 2023-01-01);-- RBO 可能转换为 (LEFT SEMI JOIN)SELECT c.* FROM customers cLEFT SEMI JOIN orders o ON o.customer_id c.customer_id AND o.order_date 2023-01-01;
视图合并 (View Merging)
如果查询中使用了视图 (View)RBO 尝试将视图的定义 合并 (Merge) 到主查询中。
-- 假设定义了视图 v_customer_orders
CREATE VIEW v_customer_orders AS
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id o.customer_id
GROUP BY c.customer_id, c.customer_name;-- 查询视图
SELECT * FROM v_customer_orders WHERE order_count 5;-- RBO 视图合并后的 SQL (伪代码)
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING order_count 5; -- 注意这里是 HAVING, 因为原视图有 GROUP BY条件化简 (Predicate Simplification)
RBO 会尝试化简 WHERE 子句中的条件表达式。 常量传递 (Constant Propagation): 将已知常量值代入表达式。 死代码消除 (Dead Code Elimination): 移除永远为真或永远为假的条件。 布尔代数化简 (Boolean Algebra Simplification): 应用布尔代数规则化简。 移除不必要的括号 等值传递equality_propagation HAVING 子句和 WHERE 子句的合并 若查询语句中无聚集函数及 GROUP BY 子句 常量表检测
外连接消除 (Outer Join Elimination)
在某些情况下LEFT JOIN 或 RIGHT JOIN 可以被转换为更高效的 INNER JOIN。
-- 原始 SQL (LEFT JOIN)
SELECT o.*, c.* FROM orders o
LEFT JOIN customers c ON o.customer_id c.customer_id
WHERE c.customer_id IS NOT NULL; -- 对 LEFT JOIN 右表列的非 NULL 条件-- RBO 转换为 (INNER JOIN)
SELECT o.*, c.* FROM orders o
INNER JOIN customers c ON o.customer_id c.customer_id
WHERE c.customer_id IS NOT NULL;其他查询重写规则
例如DISTINCT 优化、GROUP BY 优化、ORDER BY 优化等。
访问路径选择 (Access Path Selection) - RBO 的早期角色 (现在更多由 CBO 负责)
JOIN 顺序优化 (Join Order Optimization) - RBO 的早期角色 (现在更多由 CBO 负责)
RBO vs. CBO各有千秋协同工作
特性基于规则的优化 (RBO)基于成本的优化 (CBO)优化依据预定义的规则 (启发式规则)成本模型 (基于统计信息)优化策略查询重写、简单访问路径和 JOIN 顺序选择访问路径选择、JOIN 类型和 JOIN 顺序的精细化选择 (基于成本)优化速度快相对较慢 (需要成本估算)优化精度相对较低 (依赖规则的有效性)较高 (更准确地评估执行计划成本)统计信息依赖低 (或不依赖)高 (依赖于准确的统计信息)适用场景简单查询、快速优化、初步优化复杂查询、精细化优化、对性能要求高的场景在 MySQL 中的角色初步优化、查询重写、为 CBO 优化打基础主要优化器、负责大部分优化决策
RBO 的局限性与 CBO 的优势
RBO 虽然速度快但其优化能力受限于预定义的规则。CBO 基于成本估算能够更全面地考虑各种因素做出更明智的优化选择。现代 MySQL 主要依赖 CBO 进行查询优化RBO 更多地作为辅助手段。
RBO 优化指导与实践建议 编写规范的 SQL 语句: 编写符合 RBO 规则的 SQL。 理解 MySQL 的 RBO 规则: 了解 MySQL RBO 主要的优化规则。 关注 EXPLAIN** 执行计划:** 使用 EXPLAIN 命令分析 SQL 查询的执行计划。 结合 CBO 进行优化: RBO 只是优化过程的第一步, 最终性能还是取决于CBO。
子查询优化
子查询语法
按返回结果集区分 标量子查询 只返回一个单一值的子查询。 行子查询 返回一条记录的子查询包含多个列。 列子查询 返回一个列的数据包含多条记录。 表子查询 子查询结果既包含多条记录又包含多个列。
按与外层查询关系区分 不相关子查询 子查询可单独运行出结果不依赖于外层查询的值。 相关子查询 子查询的执行依赖于外层查询的值。
子查询在布尔表达式中的使用 使用 、、 等操作符。 [NOT] IN/ANY/SOME/ALL 子查询。 EXISTS 子查询。
子查询在 MySQL 中的执行方式 标量子查询、行子查询的执行方式 不相关的标量子查询或行子查询先单独执行子查询再将结果作为外层查询的参数。相关的标量子查询或行子查询按外层查询逐条执行。 IN 子查询优化 物化表的提出 对于不相关的 IN 子查询若子查询结果集较大优化器会将子查询结果写入临时表物化表。 物化表转连接 将子查询物化后可将外层查询与物化表进行内连接。 将子查询转换为 semi-join 对于符合一定条件的 IN 子查询优化器会将其转换为 semi-join。 semi-join 的适用条件 子查询必须是和 IN 语句组成的布尔表达式且在外层查询的 WHERE 或 ON 子句中出现外层查询可有其他搜索条件但必须与 IN 子查询的搜索条件使用 AND 连接子查询必须是单一查询不能由 UNION 连接子查询不能包含 GROUP BY、HAVING 或聚集函数等。 不适用于 semi-join 的情况 外层查询的 WHERE 条件中有其他搜索条件与 IN 子查询组成的布尔表达式使用 OR 连接使用 NOT IN子查询在 SELECT 子句中子查询包含 GROUP BY、HAVING 或聚集函数子查询包含 UNION 等。 ANY/ALL 子查询优化 不相关的 ANY/ALL 子查询在很多场合可转换为其他形式执行, 如 ANY (SELECT inner_expr ...) 可转换为 (SELECT MAX(inner_expr) ...)。 [NOT] EXISTS 子查询的执行 不相关的 [NOT] EXISTS 子查询先执行子查询得出结果后再重写外层查询。相关的 [NOT] EXISTS 子查询按逐条执行的方式进行。 对于派生表的优化 将子查询放在外层查询的 FROM 子句中子查询的结果相当于一个派生表。优化器会尝试将派生表与外层查询合并若无法合并则将派生表物化为临时表。
实战优化技巧
IN vs EXISTS选择
场景推荐写法原因外层结果集大EXISTS可快速短路判断内层结果集小IN物化成本低需要结果去重IN DISTINCT利用物化表的自动去重特性
派生表优化
-- 原始查询
SELECT * FROM (SELECT dept_id, AVG(salary) avg_sal FROM employees GROUP BY dept_id
) AS dept_sal
WHERE avg_sal 10000;-- 优化手段
SET optimizer_switch derived_mergeon; -- 启用派生表合并优化验证工具
-- 查看优化器决策过程
SET optimizer_traceenabledon;
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_traceenabledoff;实际使用建议 对于关联子查询确保被驱动表的连接列有索引。 大数据集IN查询优先测试物化表性能。 使用EXPLAIN FORMATJSON分析执行计划细节。 定期更新统计信息保证优化器决策准确。
总结
MySQL 基于规则的优化 (RBO) 是查询优化器中不可或缺的一部分。它通过快速的模式匹配和规则应用对 SQL 查询进行初步的 “整形美容”提升查询的可读性和执行效率。虽然 RBO 的优化能力相对有限但它仍然是现代 MySQL 优化器的重要组成部分与 CBO 协同工作, 共同打造高效的数据库查询引擎。 参考https://relph1119.github.io/mysql-learning-notes/#/mysql ,推荐理解本文之后去看原书原书有一定深度需前后贯穿仔细理解
