引入

我們都知道大數據的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的索引選擇與執行計劃穩定性	分布式數據傾斜與網絡傳輸成本控制
共性技術	謂詞下推、常量折疊、連接順序優化	分區剪枝、動態資源分配、小文件合并