大数据SQL调优专题——调优切入

引入

我们都知道大数据的SQL优化，并非一蹴而就的简单任务，而是一个涉及多个环节的复杂过程。虽然我们的专栏名字叫大数据SQL调优，但是调优并不是简单对SQL优化，而是一个涉及多个环节的复杂过程。实际上从需求接入到最终交付，任何一个环节的都可能影响最终成果。而调优的本质并非对任务进行大规模重构，而是通过各种监控工具，排查梳理出瓶颈点在哪，并深入了解其成因以及对应需求背景，才能以最小的代价进行修改或调整。

正所谓“工欲善其事，必先利其器”。在优化过程中，无论是发现问题的根源，还是寻找最优的解决方案，合适的工具、流程和思路都至关重要。

以下将我们从数据研发日常工作的角度，来简要介绍调优的几个关键切入点。

梳理真实需求

梳理真实需求的前提是理解业务，这是数仓调优的根基。只有深入理解企业的业务流程、业务模式和业务目标，才能明确业务部门的真实需求是什么。

数据最终的目的是赋能业务、驱动业务，以此为前提，我们就需要用数据准确的展示业务现状，为业务和分析提供数据支持。而要做到以上各点，必须了解业务痛点，理解业务流程，分析出业务场景中可能存在的问题，以及判断各业务方的诉求是否合理、其底层的真实需求是否和表述需求一致等。

所谓理解业务，从高层视角而言，指的是了解行业的基本模式、运作的流程、供需之间如何流转，本质就是找到不同主体之间的业务流转关系。从底层视角看的话，则是需要清楚地知道不同表中具体字段的含义，以及相关的表有哪些，他们之间的数据关联关系是什么等。数据是客观存在的，而需求才是基于业务的，很多不必要的资源开销、冗长的沟通、费尽周折的查询优化，其实在源头阶段是可以避免的。只有了解业务，我们才有能力去甄别需求是否合理，其背后隐藏的真实需求是怎样的。

所以对于一个合格的数据研发来说，在开发工作开始之前，就需要明确以下几点：

1.明确需求

不要接到需求就只顾埋头实现，在正常开发流程的需求澄清会上，不要闷头光想着怎么实现需求，要学会多问问需求提供方或业务方究竟要做什么事情，想达到什么目的，并与业务提供需求文档和设计文档核对复验，最后要记得询问期望的结果、反馈和交付时间节点。这样的过程才谈得上是一个闭环，而不是埋头苦干一通，最后发现需要返工，或者被业务人员全盘否定。

2.应对需求的正确思路

比如老板发现一个城市的司机接单指标下跌了，不要急着去查口径，找数据问题，或者直接拿相关数据去做多维交叉分析，拿历史数据波动，看同环比等。因为当数据指标出现异常时，急于从数据本身寻找答案，最后的结果可能也只是“自证清白”，并没能找到问题根因，我们应该深入探究背后的业务逻辑。比如前面提的指标下跌的原因，可能是因为多方面的，比如高峰期刚过，或者对应城市天气问题等，基于业务理解，去找出这些真正影响指标的答案，才是正确的思路。

3.技术栈应用储备

大数据处理的技术栈种类繁多，它们都有各自的适用场景和局限性，也就是所谓的术业有专攻，目前也没有出现一个能解决所有问题的银弹。而需求方不了解也不关心底层的具体实现和局限性，所有数据研发作为其中承上启下的重要节点，就需要有相关大数据处理技术广度和深度的知识储备，才能帮助或引导模糊的需求具象化、合理化。

收集相关信息

执行计划

执行计划（Execution Plan，也叫查询计划或者解释计划）是查询优化的重要依据。我们可以通过EXPLAIN命令查看优化器针对指定SQL生成的逻辑执行计划。如果要分析某条SQL的性能问题，通常需要先查看SQL的执行计划，排查每一步SQL执行是否存在问题。

通过查看执行计划，我们可以得到表的读取顺序、表之间的引用、表连接的顺序、表连接的方式以及实际读取文件的位置等信息。尽管不同的框架实现会导致执行计划有所差异，但目的都是相同的。通过使用EXPLAIN关键字，可以模拟优化器执行SQL查询语句，从而理解引擎是如何处理SQL语句的。这样我们就能够分析提交的查询语句可能存在的性能瓶颈。

例如通过EXPLAIN命令解析HQL执行计划去定位以下可能的瓶颈：

• ​JOIN顺序优化：优先过滤小表以减少Shuffle数据量。
• ​数据倾斜处理：识别Hash Join中的倾斜Key。
• ​资源分配：根据Stage耗时调整Map/Reduce并发度。

统计信息

统计信息对于查询优化也是至关重要。前面我们有提到CBO，其本质就是依赖统计信息来选择最优的执行计划。

统计数据分为表级别的统计数据（如行数、大小等）和列级别的统计数据（如最大、最小值等）两类。表或字段的统计信息有助于我们更深入地了解表中数据的分布情况，从而规避潜在的问题。例如，单表数据量过大，就可以考虑将表转换为分区或分桶表；如果字段唯一值数量较少，在去重时就需要注意潜在的数据倾斜问题。

在Hive中，除了保存元数据库表之外，也会保存这两类统计信息，包括表或分区的大小、行数等信息。Hive会尽可能地在每次变更数据时更新统计信息，以确保其准确性。统计信息的主要用途在于辅助CBO制定更高效的查询计划，从而选择成本最低的执行路径。此外，当启用配置hive.compute.query.using.stats（默认关闭）时，对于COUNT(1)、MIN、MAX等聚合查询，引擎会直接利用统计信息快速返回结果，而无须进行实际的文件扫描操作。

在Spark中，尽管我们也使用Hive Metastore来管理库表的元数据，并且Spark能够兼容Hive表的读写操作，但Spark对于Hive统计信息的利用与Hive本身存在差异。Spark定义了一套自己的统计指标和规则体系，这也意味着由Spark引擎写入的统计信息不会被其他引擎所利用和共享。此外，Spark在处理统计信息的写入时比Hive更为谨慎。在默认配置下，Spark并不会在数据写入时自动更新统计信息，这导致Hive Metastore中的统计数据可能无法准确地反映表的当前状态。Spark主要通过org.apache.spark.sql.execution.command.CommandUtils类中的update-TableStats方法来更新表级别的统计信息，感兴趣小伙伴可以深入看看其实现原理。其核心就是，如果处理的是非分区表，它会递归遍历表存储路径下的所有文件，并对每个文件的大小进行求和。如果处理的是分区表，它会默认使用listPartitions方法获取所有分区的路径，然后并行计算每个分区的大小。最终，该方法只会更新表级别的spark.sql.statistics.totalSize属性。对于字段级别的统计信息，只能通过执行ANALYZE TABLE...COMPUTE STATISTICS FOR COLUMNS...命令来进行计算和写入。这个语句会记录某个字段在整张表中的最小值、最大值等统计信息，但并不会在分区维度上进行统计。字段级别的统计信息包括平均长度(avgLen)、唯一值的数量(distinctCount)、最小值(min)、最大值(max)、最大长度(maxLen)、空值数量(nullCount)以及直方图(histogram)。前几个统计值的含义相对直观，而直方图(histogram)则较为特殊，需要在开启spark.sql.statistics.histogram.enabled后，再运行ANALYZE命令才能生成。直方图会将字段值的分布切分成n个百分比位（n=spark.sql.statistics.histogram.numBins，默认值为254）​，每一段内部会统计最小值、最大值和唯一值的数量。直方图可以对高频查询字段建立统计快照，加速范围查询响应。

执行日志

执行日志记录了数据库的各种操作和事件，包括查询执行时间、错误信息等。通过分析执行日志，可以发现频繁执行的查询、耗时较长的查询以及出现错误的查询。针对这些问题查询，可以进行针对性的优化，提高数据仓库的整体性能。

而且日志有助于保护开发人员和用户免受应用程序和系统中大规模故障和问题的影响。由于我们无法24h实时监控查询任务，因此当任务出现错误并失败时，我们需要确定原因，此时日志的重要性就显现出来了。日志对于程序执行的监控和问题定位至关重要。在系统设计、开发和实现的过程中，必须关注输出的日志，这对于查询任务的异常分析至关重要。

通常我们会分析日志中的关键指标：

• ​Shuffle溢出：监控磁盘溢出次数。
• ​GC耗时：通过日志中的GC暂停时间判断内存配置合理性，优化堆内存分配。

分析工具

Dr.Elephant

Dr.Elephant 是一个用于分析大数据作业性能的工具。它可以帮助用户识别性能瓶颈、优化资源使用和提高作业效率。通过 Dr.Elephant，可以收集和分析作业的执行时间、资源消耗等信息，为性能调优提供数据支持。将作业的执行日志上传到Dr.Elephant，Dr.Elephant会生成详细的分析报告，指出作业的性能问题，并提供优化建议。

Dr.Elephant于2016年4月由LinkedIn开源，是一个支持Hadoop和Spark的性能监控和调优工具。Dr.Elephant能自动收集所有指标，进行数据分析，并以简单易用的方式进行呈现。与此同时，Dr.Elephant也支持对Hadoop和Spark任务进行可插拔式、配置化以及基于规则的启发式Job性能分析，并且能根据分析结果给出合适的建议，来指导开发人员进行调优以使任务有更高效率。

火焰图

火焰图是一种可视化工具，可以帮助用户直观地了解程序的执行流程和性能瓶颈。在大数据数仓调优中，火焰图可以用于分析查询执行过程中的 CPU 使用率、内存分配等情况，帮助找到性能瓶颈所在。

通常的做法是，使用性能分析工具（如perf）收集程序的执行数据，生成火焰图，通过分析火焰图找出执行时间较长的函数或代码段，进行优化。

火焰图(Flame Graph)是由Linux性能优化大师Brendan Gregg发明的。和其他的性能分析方法不同，火焰图以一个全局视野来看待时间分布，它由下向上地列出所有可能导致性能瓶颈的调用栈。

Prometheus

Prometheus 是一个开源的监控系统，广泛应用于云计算和容器化环境中。在大数据数仓中，Prometheus 可以用于收集和监控各种指标，如 CPU 使用率、内存使用率、网络流量等。通过监控这些指标，可以及时发现性能问题，并采取相应的措施进行优化。

通过部署Prometheus服务器和相关的Exporter，收集系统的监控数据，使用Prometheus的查询语言（PromQL）查询和分析数据，设置报警规则，来及时发现和处理性能问题。

Prometheus是由SoundCloud开源的监控告警解决方案，2012年开始编写代码，2015年开源，发展速度迅猛，社区活跃，并且被广泛应用于各大公司中，Prometheus通过领先的开源监控解决方案为用户的指标和告警提供了强大的技术支持。

等价重写思想

在数据库领域，等价重写思想乃是一种极具价值的优化技术。其旨在对查询表达式进行优化改写，使得改写后的表达式与原始查询等价，同时在执行效率、性能或其他方面展现出更为显著的优势。等价重写的核心目标在于通过巧妙地重新组织查询结构，充分利用数据库系统的优化策略，从而大幅降低查询的计算成本。

尽管大数据引擎在物理实现方面与关系型数据库存在着较大差异，然而，在设计思想以及实际应用过程中，这些差异并非十分突出。一方面，我们能够借助引擎自身所支持的优化功能，例如基于关系代数以及利用等价变换的规则。另一方面，我们可以通过对查询语句进行改写，进而实现优化查询语句的目的。

等价重写思想是一种基于关系代数的查询优化技术，其核心在于通过语义等价性转换生成执行效率更高的新查询表达式。

该技术遵循两大原则：

1. 数学等价性：利用关系代数的结合律、交换律、分配律等规则（如投影下推、选择分解、外连接转半连接），保证重写前后的查询结果完全一致；
2. 执行高效性：通过重构查询结构降低计算复杂度（如减少全表扫描）、提升索引利用率（如将LIKE 'abc%'改写为范围查询）、优化分布式数据分片策略（如减少Shuffle数据量）。

典型技术场景：

• 子查询提升：将IN子查询转化为SEMI JOIN，避免嵌套循环执行（如SELECT * FROM film WHERE id IN (SELECT film_id FROM film_actor) → SELECT f.* FROM film f JOIN film_actor fa ON f.id=fa.film_id）；
• 谓词下推：提前过滤无效数据（如将WHERE a>10 AND b<5分解为两阶段过滤）；
• 常量折叠：预计算静态表达式（如WHERE a=1+1重写为WHERE a=2）。

大数据的演化区别：

维度	传统关系型数据库	大数据引擎
优化模式	自动优化为主（如CBO优化器）	手动干预为主（需人工规避Shuffle瓶颈）
核心挑战	复杂SQL的索引选择与执行计划稳定性	分布式数据倾斜与网络传输成本控制
共性技术	谓词下推、常量折叠、连接顺序优化	分区剪枝、动态资源分配、小文件合并