完了，一條 SQL 把數據庫服務器干爆了！

Java精選面試題（微信小程序）：5000+道面試題和選擇題，包含Java基礎、并發、JVM、線程、MQ系列、Redis、Spring系列、Elasticsearch、Docker、K8s、Flink、Spark、架構設計、大廠真題等，在線隨時刷題！

前言

5月16號，一條SQL把數據庫服務器干爆了

這個問題出現過好多次了,但是這次這個SQL是我寫的，加上最近有重要的業務在開展，產生了很多臟數據。

當時氣氛一下就緊張起來了，快！快！快！緊急修復啊!

看了一下SQL，確實有問題 :

selectcount(*)fromAleftjoinBonA.b_id = B.id

本來A表中的b_id 是bigint，但是確實設置的是varchar類型, 完了不會是我的問題吧！

當時就先緊急發版本了，把這個SQL注釋掉了！ 當時數據庫的CPU已經被打滿了，我肯定不敢再調試，但是作為一個老油條這個問題我也不能背鍋呀。

??那就開始我們的問題定位，和甩鍋之旅途吧??

問題定位

前言中已經透露了兩個關鍵關鍵信息

1. 第一就是 A.b_id = B.id A表中b_id類型存在問題

2. 執行計劃走的是nested loop 循環，執行計劃如下

Finalize Aggregate (cost=97553.09..97553.10rows=1 width=8)

-> Gather (cost=97552.88..97553.09rows=2 width=8)

Workers Planned: 2

->PartialAggregate (cost=96552.88..96552.89rows=1 width=8)

-> Nested Loop (cost=0.29..96550.62rows=903 width=0)

-> Parallel Seq ScanonA_bak20250318 a (cost=0.00..92882.53rows=903 width=6)

Filter: ((NOTdel)ANDp_groupAND(m_status = 1)AND((c_status)::integer= 3))

->IndexScan using B_pkey4onB b (cost=0.29..4.06rows=1 width=8)

IndexCond: (id = (a.b_id)::bigint)

Filter: (((c_path)::text ~~'9987.%'::text)OR((_path)::text ='9987'::text))

當然，如果去掉join肯定是能解決這個問題的，但是join在我們這種數據體量正常情況是肯定沒有問題的(前提是join 兩三個表)

猜想一：索引失效

很容易就想到了是不是字段類型的問題，導致索引失效走了nested loop，從而導致CPU飆高呢？

排除是索引失效導致CPU飆高的問題

仔細看上面的執行計劃的話，會發現 索引是沒有失效的，但是確實也進行了類型轉換。

修正數據庫字段，再看執行計劃，和上面的唯一區別就是少了一個類型轉換

對比（修改字段類型前后）執行時間都是3s左右

根據修改字段類型前后的執行計劃、執行時間至少能夠確認這并不是索引失效影響了SQL的執行計劃推理，以及導致CPU飆高的問題

猜想二，改成子查詢是否能解決呢

selectcount(*)fromAwhereb_idin(selectidfromB..)...

執行計劃和join查詢一樣，執行時間也差不多. 也排除了改成只查詢就能解決這個問題

猜想三，數據庫執行計劃又出毛病兒呢，不能走nested loop

關閉當前會話的nested loop 算法(SET enable_nestloop = off;

)，再看執行時間和執行計劃

Finalize Aggregate (cost=98340.72..98340.73rows=1 width=8)

-> Gather (cost=98340.50..98340.71rows=2 width=8)

Workers Planned: 2

->PartialAggregate (cost=97340.50..97340.51rows=1 width=8)

-> Parallel HashJoin(cost=4453.23..97338.24rows=903 width=0)

Hash Cond: ((a.b_id)::bigint= b.id)

-> Parallel Seq ScanonA_bak20250318 a (cost=0.00..92882.53rows=903 width=6)

Filter: ((NOTdel)ANDp_groupAND(p_status = 1)AND((k_status)::integer= 3))

-> Parallel Hash (cost=4275.41..4275.41rows=14226 width=8)

-> Parallel Seq ScanonB b (cost=0.00..4275.41rows=14226 width=8)

Filter: (((c_path)::text ~~'9987.%'::text)OR((c_path)::text ='9987'::text))

執行時間：700ms ??沒錯這才是我SQL的真正執行時間，都是數據庫和服務器的問題，至少能甩給數據庫。

?在執行計劃探測測段，估算的A的行數為900行左右，實際上達到了應該返回幾十萬行，所以數據庫底層就把兩個表當作了數據量很少的情況，這就引發了數據庫走Nested loop 循環。這才是問題問題的根本所在。導致 Nested loop 成本 小于了 Hash join 成本。

鍋就甩到這兒呢，下面就分享點硬核知識了

解決方案

這條SQL本該執行Hash join但是卻走了Nested loop，走Nested loop的原因我們也找到了，由于在執行計劃階段，預估行數的時候出了問題，導致算出得成本用Nested loop 更優，從而執行的時候選擇了錯誤的方式。

知道了問題所在，那么就好解決了，先解決SQL中類型的轉換的問題，然后更新統計信息ANALYZE VERBOSE your_table再次查看執行計劃就回歸正常了，走hash join,并且主表的探測行數快20W了

需要更準確的行數預估，就增加取樣的行數吧（不知道怎么設置的看下文）

一勞永逸的方案：直接禁用nested loop循環（謹慎考慮，結合公司業務場景）；開啟hint語法配置，指定執行計劃（建議）

知識擴展（硬核知識）

如何分析執行計劃(KingBase)

執行計劃中的cost 和 rows 都很好理解，一個是執行成本(并不是耗時)，一個返回行數，關于hash join 和 nested loop這兩個聯表算法，我就不再解釋了。

示例一：nested loop

Finalize Aggregate (cost=97553.09..97553.10rows=1 width=8)

-> Gather (cost=97552.88..97553.09rows=2 width=8)

Workers Planned: 2

->PartialAggregate (cost=96552.88..96552.89rows=1 width=8)

-> Nested Loop (cost=0.29..96550.62rows=903 width=0)

-> Parallel Seq ScanonA_bak20250318 a (cost=0.00..92882.53rows=903 width=6)

Filter: ((NOTdel)ANDp_groupAND(m_status = 1)AND((c_status)::integer= 3))

->IndexScan using B_pkey4onB b (cost=0.29..4.06rows=1 width=8)

IndexCond: (id = (a.b_id)::bigint)

Filter: (((c_path)::text ~~'9987.%'::text)OR((_path)::text ='9987'::text))

執行計劃詳解

1. 頂層操作 - Finalize Aggregate

• 這是查詢的最終操作，負責匯總各個并行工作進程的部分聚合結果。

• 預估會返回 1 行數據，處理成本在 97553.09 到 97553.10 之間。

2. Gather 操作

• 該操作會協調 2 個并行工作進程，把它們的結果收集起來。

• 它的子操作是 Partial Aggregate，也就是部分聚合。

3. 并行處理 - Partial Aggregate

• 每個工作進程都會進行部分聚合計算。

• 成本估計在 96552.88 到 96552.89 之間。

4. 數據關聯 - Nested Loop

• 采用嵌套循環的方式將表 A 和表 B 進行連接。

• 外層循環是對表 A 的掃描，內層循環則是對表 B 的索引掃描。

5. 表 A 數據掃描 - Parallel Seq Scan on A_bak20250318

• 對表 A 進行并行順序掃描，這是因為表數據沒有合適的索引，所以選擇并行處理來提高效率。

• 掃描條件為：NOT del AND p_group AND m_status = 1 AND c_status::integer = 3。

• 預估會返回 903 行數據，成本為 92882.53，在整個查詢成本中占比最大。

6. 表 B 數據檢索 - Index Scan using B_pkey4 on B

• 依據表 B 的主鍵索引（B_pkey4）來查找匹配的行。

• 索引條件是：id = a.b_id，這表明是通過外鍵進行關聯的。

• 過濾條件為：c_path LIKE '9987.%' OR _path = '9987'。

• 每匹配到表 A 的一行數據，就會執行一次這個索引掃描，每次掃描成本約為 4.06。

示例二：hash join

Finalize Aggregate (cost=98340.72..98340.73rows=1 width=8)

-> Gather (cost=98340.50..98340.71rows=2 width=8)

Workers Planned: 2

->PartialAggregate (cost=97340.50..97340.51rows=1 width=8)

-> Parallel HashJoin(cost=4453.23..97338.24rows=903 width=0)

Hash Cond: ((a.b_id)::bigint= b.id)

-> Parallel Seq ScanonA_bak20250318 a (cost=0.00..92882.53rows=903 width=6)

Filter: ((NOTdel)ANDp_groupAND(p_status = 1)AND((k_status)::integer= 3))

-> Parallel Hash (cost=4275.41..4275.41rows=14226 width=8)

-> Parallel Seq ScanonB b (cost=0.00..4275.41rows=14226 width=8)

Filter: (((c_path)::text ~~'9987.%'::text)OR((c_path)::text ='9987'::text))

看了[示例一]得分析之后，再看這個Hash join 的計劃沒那就沒啥了。總成本從約 97,553 增加到約 98,340。

A表的預估rows都是903 偏離實際兩個數量級了，這也是導致成本計算出了問題。

?還有一個重要的關鍵詞workers Planned :是一個與并行查詢執行相關的重要參數。它表示優化器計劃為當前查詢分配的并行工作進程（Worker Processes）數量，這些進程將同時執行查詢的某些操作，以提高處理速度。當一個查詢滿足以下條件時，優化器可能會選擇并行執行：

1. 數據量足夠大：小表通常不值得并行處理。

2. 操作支持并行：如順序掃描、哈希連接、聚合等操作可以并行化。

3. 資源允許：服務器有足夠的 CPU 核心和內存來支持額外的工作進程

影響執行計劃的因素

1.SQL 本身問題

• 鏈表方式、數量

• 索引失效

• 類型轉換

• 數據量

2.統計信息準確性

優化器依賴表和索引的統計信息估算成本，若統計信息過時或不完整，會導致執行計劃偏差，優化方式：

• 更新統計信息

-- KingBase（PostgreSQL）：更新統計信息

ANALYZE your_table;

ANALYZE VERBOSE your_table;--強制

-- MySQL：更新統計信息

ANALYZETABLEyour_table;

• 增加取樣數量

-- PostgreSQL：增加統計目標

ALTERTABLEyour_tableALTERCOLUMNyour_columnSETSTATISTICS1000;

3.服務器硬件配置

• 內存不足：若innodb_buffer_pool_size過小，頻繁磁盤 I/O 會使隨機讀成本顯著增加。

• 磁盤類型：SSD 的隨機讀寫性能遠高于 HDD，可降低random_page_cost參數。

• CPU 核數：多核 CPU 可提升并行查詢性能，需調整max_parallel_workers_per_gather。

4.數據分布

• 數據傾斜：如某字段的大部分值集中在少數幾個值上。

• 冷熱數據分布：頻繁訪問的 “熱數據” 若未緩存，會增加隨機讀成本。

總結

本次CPU打滿，查看數據庫這條SQL阻塞了10多條，只能說這條SQL確實消耗CPU資源，但是咱不背鍋。通過對執行計劃的分析，我們定位到是統計信息不準確，導致探測返回行數的與實際情況相差50倍，數據庫做出了錯誤的執行計劃。

作者：提前退休的java猿

來源：https://juejin.cn/post/7504943335841497107

公眾號“Java精選”所發表內容注明來源的，版權歸原出處所有（無法查證版權的或者未注明出處的均來自網絡，系轉載，轉載的目的在于傳遞更多信息，版權屬于原作者。如有侵權，請聯系，筆者會第一時間刪除處理！

最近有很多人問，有沒有讀者交流群！加入方式很簡單，公眾號Java精選，回復“加群”，即可入群！

文章有幫助的話，點在看，轉發吧！