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

前言

5月16號，一條SQL把數(shù)據(jù)庫服務(wù)器干爆了

這個問題出現(xiàn)過好多次了,但是這次這個SQL是我寫的，加上最近有重要的業(yè)務(wù)在開展，產(chǎn)生了很多臟數(shù)據(jù)。

當(dāng)時氣氛一下就緊張起來了，快！快！快！緊急修復(fù)啊!

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

selectcount(*)fromAleftjoinBonA.b_id = B.id

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

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

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

問題定位

前言中已經(jīng)透露了兩個關(guān)鍵關(guān)鍵信息

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

2. 執(zhí)行計劃走的是nested loop 循環(huán)，執(zhí)行計劃如下

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))

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

猜想一：索引失效

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

排除是索引失效導(dǎo)致CPU飆高的問題

仔細(xì)看上面的執(zhí)行計劃的話，會發(fā)現(xiàn) 索引是沒有失效的，但是確實也進(jìn)行了類型轉(zhuǎn)換。

修正數(shù)據(jù)庫字段，再看執(zhí)行計劃，和上面的唯一區(qū)別就是少了一個類型轉(zhuǎn)換

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

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

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

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

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

猜想三，數(shù)據(jù)庫執(zhí)行計劃又出毛病兒呢，不能走nested loop

關(guān)閉當(dāng)前會話的nested loop 算法(SET enable_nestloop = off;

)，再看執(zhí)行時間和執(zhí)行計劃

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))

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

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

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

解決方案

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

知道了問題所在，那么就好解決了，先解決SQL中類型的轉(zhuǎn)換的問題，然后更新統(tǒng)計信息ANALYZE VERBOSE your_table再次查看執(zhí)行計劃就回歸正常了，走h(yuǎn)ash join,并且主表的探測行數(shù)快20W了

需要更準(zhǔn)確的行數(shù)預(yù)估，就增加取樣的行數(shù)吧（不知道怎么設(shè)置的看下文）

一勞永逸的方案：直接禁用nested loop循環(huán)（謹(jǐn)慎考慮，結(jié)合公司業(yè)務(wù)場景）；開啟hint語法配置，指定執(zhí)行計劃（建議）

知識擴(kuò)展（硬核知識）

如何分析執(zhí)行計劃(KingBase)

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

示例一：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))

執(zhí)行計劃詳解

1. 頂層操作 - Finalize Aggregate

• 這是查詢的最終操作，負(fù)責(zé)匯總各個并行工作進(jìn)程的部分聚合結(jié)果。

• 預(yù)估會返回 1 行數(shù)據(jù)，處理成本在 97553.09 到 97553.10 之間。

2. Gather 操作

• 該操作會協(xié)調(diào) 2 個并行工作進(jìn)程，把它們的結(jié)果收集起來。

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

3. 并行處理 - Partial Aggregate

• 每個工作進(jìn)程都會進(jìn)行部分聚合計算。

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

4. 數(shù)據(jù)關(guān)聯(lián) - Nested Loop

• 采用嵌套循環(huán)的方式將表 A 和表 B 進(jìn)行連接。

• 外層循環(huán)是對表 A 的掃描，內(nèi)層循環(huán)則是對表 B 的索引掃描。

5. 表 A 數(shù)據(jù)掃描 - Parallel Seq Scan on A_bak20250318

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

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

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

6. 表 B 數(shù)據(jù)檢索 - Index Scan using B_pkey4 on B

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

• 索引條件是：id = a.b_id，這表明是通過外鍵進(jìn)行關(guān)聯(lián)的。

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

• 每匹配到表 A 的一行數(shù)據(jù)，就會執(zhí)行一次這個索引掃描，每次掃描成本約為 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 的計劃沒那就沒啥了?？偝杀緩募s 97,553 增加到約 98,340。

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

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

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

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

3. 資源允許：服務(wù)器有足夠的 CPU 核心和內(nèi)存來支持額外的工作進(jìn)程

影響執(zhí)行計劃的因素

1.SQL 本身問題

• 鏈表方式、數(shù)量

• 索引失效

• 類型轉(zhuǎn)換

• 數(shù)據(jù)量

2.統(tǒng)計信息準(zhǔn)確性

優(yōu)化器依賴表和索引的統(tǒng)計信息估算成本，若統(tǒng)計信息過時或不完整，會導(dǎo)致執(zhí)行計劃偏差，優(yōu)化方式：

• 更新統(tǒng)計信息

-- KingBase（PostgreSQL）：更新統(tǒng)計信息

ANALYZE your_table;

ANALYZE VERBOSE your_table;--強(qiáng)制

-- MySQL：更新統(tǒng)計信息

ANALYZETABLEyour_table;

• 增加取樣數(shù)量

-- PostgreSQL：增加統(tǒng)計目標(biāo)

ALTERTABLEyour_tableALTERCOLUMNyour_columnSETSTATISTICS1000;

3.服務(wù)器硬件配置

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

• 磁盤類型：SSD 的隨機(jī)讀寫性能遠(yuǎn)高于 HDD，可降低random_page_cost參數(shù)。

• CPU 核數(shù)：多核 CPU 可提升并行查詢性能，需調(diào)整max_parallel_workers_per_gather。

4.數(shù)據(jù)分布

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

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

總結(jié)

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

作者：提前退休的java猿

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

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

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

文章有幫助的話，點在看，轉(zhuǎn)發(fā)吧！