大数据SQL优化原理与实践系列之原理篇（三）——以SQL案例看优化器

PowerData

编者荐语：

来自PowerData-阿丞同学的文章

以下文章来源于阿丞的数据漫谈 ，作者阿阿丞

[

阿丞的数据漫谈 .

聚焦数据及人工智能领域，不定期分享能源行业知识、数据科学、学习笔记等。尽可能All in 原创。

](#)

                    HELLO     更多趣文请关注阿丞的数据漫谈                

前言

在上文大数据SQL优化原理与实践系列之认知篇（一）和 大数据SQL优化原理与实践系列之原理篇（二）——Hive源码级运行原理剖析中，具体介绍了SQL作为一个声明式语言，如何一步步由字符形式的语句转换为最终执行的物理计划，其中优化器作为承上启下的关键一环，主要作用就是将初步解析和分析后的逻辑执行计划进行各种优化和合并，最终转换为高性能、可执行的物理执行计划。优化器的强大与否将直接影响任务的执行性能。本篇作者尽可能讲清楚优化器的类型、原理。

根据工作原理，优化器的两种分类：

基于规则（RBO）：依赖预定义规则进行优化的优化器。这些规则通常基于关系代数的等价变换，如列裁剪、谓词下推等。具有以下特点：

基于成本（CBO）：通过估算不同执行计划的成本来选择最优的执行路径。成本包括I/O操作、CPU使用和网络资源等。具有以下特点：

基于规则的优化器——RBO

在基于规则的优化器中，主要有以下优化规则：谓词下推、常量堆叠、常量传递、等式传递、布尔表达式简化、BETWEEN-AND重写、NOT取反重写、简化IF/CASE WHEN条件表达式、优化LIKE正则表达式、简化CAST表达式、简化UPPER/LOWER表达式、优化二元表达式、简化复杂类型数据结构的操作符、合并投影、列裁剪、优化冗余别名、替换NULL表达式、CONCAT合并、等式变换、不等式变换等，其中不少是面试八股文慢SQL优化问题中常见的名词。

-- 原始SQL SELECT t1.user_id, t1.name, t2.age FROM user_info t1          INNER JOIN user_info_ext t2 on t1.user_id = t2.user_id WHERE t1.user_id > 2;

谓词下推： 谓词下推是将查询中的过滤条件尽可能地靠近数据源。这可以减少数据量，以提高查询性能。

-- UNION下推 SELECT user_id FROM (SELECT user_id FROM user_info UNION ALL SELECT user_id FROM user_info_ext) t WHERE user_id > 2;

常量堆叠： 常量堆叠是将多个常量表达式合并为一个单一的常量值。例如，5 + 3 可以被优化为 8。

-- 常量堆叠 SELECT t1.user_id, t1.name, t2.age FROM user_info t1          INNER JOIN user_info_ext t2 on t1.user_id = t2.user_id WHERE t1.user_id > (1 + 1);

常量传递： 当一个变量在所有可能的执行路径中都被赋予了相同的常量值时，可以用这个常量来替换该变量的所有使用。

-- 常量传递 SELECT t1.user_id, t1.name, t2.age FROM user_info t1          INNER JOIN user_info_ext t2 on t1.user_id = t2.user_id WHERE t2.age = 20 AND t1.user_id < age; -- 优化后 SELECT t1.user_id, t1.name, t2.age FROM user_info t1          INNER JOIN user_info_ext t2 on t1.user_id = t2.user_id WHERE t2.age = 20 AND t1.user_id < 20;

等式传递： 如果两个表达式通过等式相等，则可以互相替换。比如，如果 A = B 和 B = C，那么 A 可以被 C 替换。

-- 等式传递 SELECT c.customer_name FROM customers c          JOIN orders o ON c.customer_id = o.customer_id          JOIN products p ON o.product_id = p.product_id WHERE p.product_name = 'Widget'; -- 优化后 SELECT c.customer_name FROM customers c          JOIN orders o ON c.customer_id = o.customer_id WHERE o.product_id = 100; -- 使用已知的 product_id

布尔表达式简化： 涉及使用逻辑规则（如德摩根定律）简化复杂的布尔表达式，以提高效率和可读性，主要有代数规则、逻辑等价变换、常量传播和折叠、短路规则、提取公因式等。

-- 原始布尔表达式： -- 1.年龄大于30岁 或 是部门经理  -- 2.不是实习生 且 当前在职 SELECT * FROM employees WHERE (age > 30OR is_manager = TRUE) AND is_intern = FALSE AND is_active = TRUE; -- 常量传播和折叠 -- is_manager = TRUE 可以简化为 is_manager is_intern = FALSE 可以简化为 NOT is_intern is_active = TRUE 可以简化为 is_active SELECT * FROM employees WHERE (age > 30OR is_manager) ANDNOT is_intern AND is_active; -- 提取公因式：假设我们知道 is_manager 的员工一定是 is_active 的，那么可以提取 is_active 作为公因式 SELECT * FROM employees WHERE is_active AND ((age > 30OR is_manager) ANDNOT is_intern); -- 逻辑等价变换：(age > 30 OR is_manager) 可以看作是一个整体条件，不需要进一步简化。 -- 短路规则：在SQL查询中，短路规则通常由数据库优化器自动处理。例如，如果 is_active 为 FALSE，则整个条件会立即返回 FALSE，不再评估后续条件。 SELECT * FROM employees WHERE is_active AND (age > 30OR is_manager) ANDNOT is_intern;

BETWEEN-AND重写： 将 BETWEEN AND 表达式转换为两个独立的比较操作。

-- 原始语句：查询年龄在特定范围内（例如22岁到30岁）的员工信息 SELECT * FROM employees WHERE age BETWEEN 22 AND 30; -- 优化后 SELECT * FROM employees WHERE age >= 22   AND age <= 30;

NOT取反重写： 对包含 NOT 的表达式进行重写，例如，NOT (A OR B) 可以重写为 NOT A AND NOT B。

-- 原始SQL：查找不是经理（is_manager = FALSE）的员工 SELECT * FROM employees WHERE NOT is_manager; -- 优化后： is_manager = FALSE 替代 NOT is_manager SELECT * FROM employees WHERE is_manager = FALSE;

简化IF/CASE WHEN条件表达式： 简化复杂的条件逻辑，使它们更易于理解并潜在地更快执行。

-- IF/CASE WHEN：需要根据员工的年龄和职位状态来计算他们的“员工等级” SELECT     id,     name,     department,     is_manager,     is_intern,     is_active,     age,     CASE         WHEN is_manager = TRUETHEN'Manager'         WHEN is_intern = TRUETHEN'Intern'         WHEN age < 25THEN'Junior'         WHEN age BETWEEN25AND35THEN'Mid-level'         ELSE'Senior'     ENDAS employee_level FROM employees; -- 优化后：将复杂的CASE WHEN语句分解为多个简单的条件判断 SELECT     id,     name,     department,     is_manager,     is_intern,     is_active,     age,     CASE         WHEN is_manager THEN'Manager'         WHEN is_intern THEN'Intern'         WHEN age < 25THEN'Junior'         WHEN age <= 35THEN'Mid-level'         ELSE'Senior'     ENDAS employee_level FROM employees;

优化LIKE正则表达式： 改进 LIKE 模式匹配语句，以提高性能，特别是避免不必要的全表扫描。

正则表达式

实际执行

LIKE 'abc%'

StartsWith

LIKE '%abc'

EndsWith

LIKE '%abc%'

Contains

LIKE 'abc'

EqualTo

简化CAST表达式： 减少不必要的类型转换，或者确保转换是最有效的方式完成的。

-- 原始查询（使用 CAST 将布尔值转换为整数）： SELECT id, name, CAST(is_manager AS Varchar) AS manager_flag FROM employees; -- 优化后:使用 CASE 表达式 SELECT id, name, CASE WHEN is_manager THEN 1 ELSE 0 END AS manager_flag FROM employees;

简化UPPER/LOWER表达式： 避免对字符串应用 UPPER 或 LOWER 函数，可能会阻止索引的有效使用。

优化二元表达式： 简化或重新排列二元运算符（如加法、减法、乘法、除法），以提高计算效率。

-- 原始SQL：判断(1 + 2) 和 (4 - 1)是否相等 SELECT (1 + 2) <=> (4 - 1); -- 优化后 SELECT TRUE;

简化复杂类型数据结构的操作符： 优化对数组、JSON、XML 等复杂数据类型的查询，确保只处理必要的部分。

合并投影： 合并来自多个查询的相同列投影，以减少不必要的数据传输和计算过程。

-- 原始SQL：计算员工的年龄组 SELECT e.id, e.name, e.department, e.age,        (SELECT'Junior'FROM dual WHERE e.age < 30) AS age_group FROM employees e; -- 优化后：在投影中直接计算 SELECTid, name, department, age,        CASE            WHEN age < 30THEN'Junior'        ENDAS age_group FROM employees;

列裁剪： 只选择实际需要的列，而不是使用 SELECT *，从而减少I/O和处理时间。

-- 原始SQL SELECT * FROM employees WHERE department = '研发部'; -- 优化后：仅选择必要的列 SELECT id, name, department, age FROM employees WHERE department = '研发部';

优化冗余别名： 移除不必要的别名，以减少混淆。

替换NULL表达式： 处理 NULL 值以确保不会在执行阶段产生不必要的资源开销。

CONCAT合并： 将多个 CONCAT 操作合并为一个，以减少函数调用次数。

等式变换： 使用等价但更有效的表达式替换原始表达式。

-- 原始SQL SELECT t1.user_id, t1.name, t2.age FROM user_info t1          INNER JOIN user_info_ext t2 on t1.user_id = t2.user_id WHERE t1.user_id > 2; -- 优化后 SELECT user_id FROM (SELECT user_id FROM user_info WHERE user_id > 2 UNION ALL SELECT user_id FROM user_info_ext WHERE user_id > 2) t;

不等式变换： 优化不等式表达式以更好地利用索引或减少计算成本。

基于成本的优化器——CBO

CBO通过估算不同执行计划的成本来选择最优的查询执行路径。CBO考虑的因素包括表的统计信息、索引的存在与否、数据分布等，通过对比RBO和CBO，会发现CBO很难沉淀出类似RBO一样的固定规则，因为随着场景、表、数据等因素的不同，执行SQL带来的代价也不同，很难固化下来具体的优化规则。

CBO的核心问题：

成本估算的维度和度量：成本估算是CBO的关键部分，它决定了哪些执行计划是最优的。成本估算涉及多个维度和度量指标，主要包括以下几点：

    1.I/O成本：

        磁盘I/O: 数据从磁盘读取或写入的成本。

        缓存命中率: 如果数据已经在内存中（例如缓冲区池），则I/O成本较低。

    2.CPU 成本：

        计算成本: 执行算术运算、逻辑判断等CPU密集型操作的成本。

        排序和哈希成本: 排序和哈希操作通常需要较多的CPU资源。

    3.网络成本: 在分布式环境中，数据在网络中的传输成本也是一个重要的因素。

    4.内存使用：

        临时表空间: 创建临时表和索引所需的空间。

        缓冲区池使用: 缓冲区池的大小和可用性会影响性能。

    5.行数统计：

        基数估计: 预测查询结果集中行的数量。

        选择性估计: 评估谓词的选择性，即满足某个条件的行的比例。

    6.等

成本估算模型：用于量化上述各个维度的成本，并综合这些成本来评估整个执行计划的总成本。不同的数据库系统可能有不同的成本估算模型实现，例如，Hive支持多种计算引擎，每种引擎可能有不同的成本模型实现，如hivedefaultcostmodel和hiveontezcostmodel。常见的成本估算模型主要有以下几种：

    1.基于代价的模型：

        基本公式: 总成本 = I/O 成本 + CPU 成本 + 网络成本 + 内存成本。

    2.统计信息：

        直方图: 描述数据分布情况的统计工具。

        密度向量: 描述数据密度的信息。

        NDV (Number of Distinct Values): 表示某一列中不同值的数量。

    3.访问路径成本：

        全表扫描 (Full Table Scan): 对表的所有行进行扫描。

        索引扫描 (Index Scan): 使用索引来定位行。

        范围扫描 (Range Scan): 在索引上进行范围查找。

        连接方法: 如嵌套循环连接、哈希连接、合并连接等。

    4.谓词评估成本：

        简单谓词: 如column = value。

        复合谓词: 如column > value AND column < value。

        多表谓词: 涉及多个表的连接条件。

    5.并行执行成本：

        并行度: 同时执行任务的数量。

        协调开销: 并行任务之间的通信和协调成本。

    6.等

小结

基于规则的优化器（RBO）依赖预定义规则进行优化，如谓词下推、常量堆叠等。RBO的特点包括：

1. 规则驱动：根据内置规则集选择执行计划。
2. 不依赖统计信息：不使用表的统计信息。
3. 适用场景：适用于规则稳定、数据量小的情况。

基于成本的优化器（CBO）通过估算不同执行计划的成本来选择最优路径。CBO的核心问题是：

1. 成本估算的维度和度量：

• I/O 成本：磁盘I/O和缓存命中率。
• CPU 成本：计算成本和排序/哈希成本。
• 网络成本：分布式环境下的传输成本。
• 内存使用：临时表空间和缓冲区池使用。
• 行数统计：基数估计和选择性估计。

3. 成本估算模型：

• 基于代价的模型：总成本 = I/O 成本 + CPU 成本 + 网络成本 + 内存成本。
• 统计信息：直方图、密度向量、NDV。
• 访问路径成本：全表扫描、索引扫描、范围扫描、连接方法。
• 谓词评估成本：简单谓词、复合谓词、多表谓词。
• 并行执行成本：并行度和协调开销。

RBO忽略了SQL中表本身的统计信息，对场景和数据不敏感，因此CBO在复杂查询和大数据环境下表现优异，能够动态调整执行计划以确保最佳性能。

关于作者

曾从事于世界500强企业，多年能源电力及企业数字化转型项目经验，深度参与和设计多个国网新型电力系统及数字化转型项目。

公众号聚焦数据及人工智能领域，不定期分享能源电力行业知识、数据科学、学习笔记等。尽可能All in原创，All in 干货。

关于社区

PowerData社区是由一群数据从业人员，因为热爱凝聚在一起，以开源精神为基础，组成的数据开源社区。

社区群内会定期组织模拟面试、线上分享、行业研讨（涉及金融、医疗、能源、工业、互联网等）、线下Meet UP、城市聚会、求职内推等。同时，在社区群内您可以进行技术讨论、问题请教，解释更多志同道合的数据朋友。

社区整理了一份每日一题汇总及社区分享PPT，内容涵盖大数据组件、编程语言、数据结构与算法、企业真实面试题等各个领域，帮助您自我提升，成功上岸。可以添加作者微信（Lzc543621），进入PowerData官方社区群。 

往期推荐

规划包含大数据技术分享、面试题分享、行业业务、个人随笔、资料分享、读书笔记等。

大数据SQL系列

大数据SQL优化原理与实践系列之认知篇（一）

大数据SQL优化原理与实践系列之原理篇（一）

大数据SQL优化原理与实践系列之原理篇（二）——Hive源码级运行原理剖析

能源电力行业系列

【科普文】电力调度控制中心

人工智能系列

大模型如何在垂直且封闭的行业（如电力行业）扎稳脚跟？

垂直且封闭的行业如何训练自己的大模型？

大数据Doris系列

使用 Doris Manager 全方位可视化运维 Apache Doris（上）

使用 Doris Manager 全方位可视化运维 Apache Doris（下）