SQL

上篇提到，一段看起來沒有問題的 Cursor，因為同時「從同一張表讀資料，又寫回同一張表」，最後讓資料量一路膨脹，效能也跟著快速惡化；理解原因之後，接下來要探討的是：如果接手這樣的問題，該怎麼調整或修改？ 可以怎麼調整？ 拆出獨立的「來源表」，把自我參照分開 修改幅度最小的做法；把母體先複製到另一張暫存表，Cursor 從來源表讀、寫入目標表，兩者不會混在一起： 1-- 先把「會員母體」獨立出來 2SELECT DISTINCT member_id, member_name, member_level 3INTO #member_source 4FROM #member_base; 5 6-- 清空目標表（如果需要的話） 7TRUNCATE TABLE #member_base; 8 9DECLARE month_cursor CURSOR FOR SELECT month_id FROM dim_month; 10OPEN month_cursor; 11FETCH NEXT FROM month_cursor INTO @month_id; 12 13WHILE @@FETCH_STATUS = 0 14BEGIN 15 INSERT INTO #member_base (member_id, member_name, member_level, month_id, amount) 16 SELECT member_id, member_name, member_level, @month_id, 0 17 FROM #member_source; -- 來源固定，不會膨脹 18 19 FETCH NEXT FROM month_cursor INTO @month_id; 20END 21 22CLOSE month_cursor; 23DEALLOCATE month_cursor; 24DROP TABLE #member_source; 這個改法只處理了「資料爆炸」的部分，Cursor 本身的效能成本還在；不過如果業務邏輯比較複雜、確實需要逐筆處理，這算是相對安全的最小改動。 ...

最近在看一段比較舊的 T-SQL 程式碼，遇到一個還算滿常見的效能議題：用 Cursor 逐筆寫入時，從同一張暫存表讀資料、又寫回去同一張表；乍看之下邏輯沒問題、結果也正確，但只要資料量稍微一多，整段 SQL 就會跑得非常慢；這篇筆記想整理一下背後的原理，以及為什麼這種寫法在 T-SQL 中通常不太建議。 簡單情境 用一個跟業務無關的例子來說明，假設有一個「會員 × 月份的消費紀錄初始化」的需求： 有一張 #member_base，裡面是會員基本資料（會員編號、姓名、等級）。 有 12 個月份，需要幫每個會員產生 12 筆「初始為 0」的消費紀錄。 預期結果：會員數 × 12 筆資料。 一個比較直覺、但其實會有問題的寫法可能會長這樣： 1DECLARE month_cursor CURSOR FOR 2 SELECT month_id FROM dim_month; -- 1 ~ 12 月 3 4OPEN month_cursor; 5FETCH NEXT FROM month_cursor INTO @month_id; 6 7WHILE @@FETCH_STATUS = 0 8BEGIN 9 INSERT INTO #member_base (member_id, member_name, member_level, month_id, amount) 10 SELECT DISTINCT member_id, member_name, member_level, @month_id, 0 11 FROM #member_base; -- 注意：從 #member_base 讀，又寫回 #member_base 12 13 FETCH NEXT FROM month_cursor INTO @month_id; 14END 直覺上看起來是：「就是跑 12 圈，每圈把所有會員複製一份、補上月份而已。」但實際跑起來，資料量會以指數的速度成長。 ...

前陣子在跟同事討論一段 SP（Stored Procedure）的效能問題，話題漸漸轉到了 Table 建立時的設計策略。 同事的想法是這樣的：「建 Table 的時候就把欄位結構跟 Index 都設定好，之後 Insert 資料就一步到位，感覺比較有效率。」 而我的直覺剛好相反：「不對吧，Index 本身會影響 Insert 的速度，應該是先把資料塞完，最後再建 Index？」 兩個人都有點不確定，但認知方向剛好相反，就決定一起查清楚；結果，我的印象是對的，但要加一些但書。 為什麼 Index 會讓 Insert 變慢？ 要解釋這件事，得先聊聊資料庫最常用的 Index 結構：B-Tree。 B-Tree 是什麼？ B-Tree 是大多數關聯式資料庫（PostgreSQL、MySQL、SQL Server）預設使用的 Index 結構，它長這樣： [50] / \ [25] [75] / \ / \ [10] [40] [60] [90] 重點有三個： 樹的每一層都保持平衡：葉子節點都在同一深度。 資料有排序：從左到右值是遞增的。 查詢速度快：找一筆資料需要 O(log n) 次比較。 因為 B-Tree 要維持排序與平衡的特性，每次 Insert 一筆資料，都必須同時更新 B-Tree 的結構。 Insert 時到底發生什麼事？ 每當 INSERT 一筆新資料，資料庫不只是把那筆資料寫進去而已，它還要作以下這些事： 找到這筆資料在 B-Tree 中對應的位置。 把新資料插入正確的節點。 如果節點滿了，觸發「節點分裂（node split）」，重新調整樹的結構。 必要時，更新上層節點的資訊。 如果你有 3 個 Index，就得重複這個過程 3 次；資料量越大，這個效能開銷就越明顯。 ...

經過前面四篇文章，能夠知道 Execution Plan 的四大核心：閱讀方向（粗細箭頭）、存取方式（Seek 與 Scan）、關聯策略（三大 Join），以及警告標誌（四大陷阱）。 SQL Server 效能調校之一：不迷路的導航，Execution Plan 的閱讀方向與指標 SQL Server 效能調校之二：看懂 Index Seek、Scan 與 Key Loop SQL Server 效能調校之三：三大 Join 運算子解密 SQL Server 效能調校之四：四個常見陷阱解析 然而，當正式面對一張包含幾十個、甚至上百個節點的巨大執行計畫時，很容易再度感到不知所措；這時候，需要的是一套系統化的除錯 SOP；效能調校的心法「一次只動一個地方，並且永遠以數據驗證差異。」以下是從一團亂麻中找出解方的步驟： 步驟一：鎖定「最貴」的目標 (Find the Most Expensive) 面對龐大的執行計畫，不要試圖由右至左把每個節點都看懂，需要「擒賊先擒王」。 尋找 Cost % 最高的節點：每個節點下方都會標示該步驟佔整句查詢成本的百分比；直接掃視全圖，把目光鎖定在那些標示 40%、60% 甚至 90% 的高成本節點上。 追蹤「最粗的箭頭」：尋找哪兩個節點之間傳遞了異常龐大的資料量，特別是「漏斗效應」；如果一個節點右邊進來很粗的箭頭，左邊出去卻變得很細，這代表它浪費了大量資源在過濾不必要的資料。 限縮範圍，挑出 1 到 2 個效能最差的局部節點作為第一階段的開刀對象，不要急著對整句 SQL 做全域（Global）的改寫。 步驟二：辨識症狀與警告 (Identify Symptoms) 鎖定可疑節點後，把滑鼠懸停（Hover）在該節點上，開始進行「健康檢查」： 有沒有黃色驚嘆號：如果是 CONVERT_IMPLICIT，就去檢查程式端參數型別是不是和資料庫不符（尤其是字串型別）；如果是 Spill to TempDB，先去檢查統計資料是否過期。 它是怎麼存取資料的：如果是 Table Scan 或 Clustered Index Scan，檢查 WHERE 條件欄位是不是漏建了索引，或者條件寫法讓索引失效了（例如在欄位上套用函數）；如果是 Index Seek，點開屬性檢查有沒有發生了「殘餘篩選條件（Residual Predicate）」，導致讀取了一堆資料卻被拋棄。 有沒有跟著 Key Lookup：如果高成本節點是 Key Lookup，去看看 SELECT 了哪些多餘的欄位，或者把它們加入現有索引的 INCLUDE 清單中。 步驟三：戳破 Optimizer 的幻覺 (Check Row Estimates) 很多時候，SQL Server 選了極差的執行計畫（例如：不該用 Hash Match 卻用了），是因為它「猜錯了資料量」： ...

在調校 SQL Server 查詢效能時，有些問題容易被忽略，卻也容易造成嚴重的效能退化：Missing Index、Implicit Conversion、Spill to TempDB、以及 Residual Predicate；本篇整理這四種問題的成因、症狀與修正方式，並說明彼此之間容易混淆的關鍵差異。 Missing Index（缺少索引） 成因：查詢的篩選欄位（WHERE、JOIN 條件）未建立對應索引，導致 SQL Server 必須掃描整張資料表（Table Scan 或 Clustered Index Scan）才能找到符合的資料列。 症狀： 執行計畫出現 Table Scan 或 Clustered Index Scan 節點。 Logical reads 數量遠超預期。 CPU 與磁碟 IO 長期偏高。 偵測方式：執行計畫中會出現綠色的「遺漏索引」提示文字；亦可查詢 sys.dm_db_missing_index_details 取得建議清單。 修正建議：針對高頻查詢的篩選欄位建立索引，並善用 INCLUDE 子句將查詢所需的非索引鍵欄位一併納入，形成 Covering Index，避免額外的 Key Lookup。 1CREATE INDEX IX_Orders_CustDate 2 ON Orders (CustomerID, OrderDate) 3 INCLUDE (TotalAmount); Implicit Conversion（隱含型別轉換） 成因：當查詢參數或比較值的資料型別與欄位型別不符時，SQL Server 會自動進行隱含型別轉換；轉換作業發生在欄位端，使得索引的 Seek 能力喪失，退化為 Scan。 症狀： 明明已建立索引，執行計畫仍顯示 Index Scan 而非 Index Seek。 執行計畫節點出現黃色警告，標示 CONVERT_IMPLICIT。 偵測方式：檢查執行計畫節點上的警告圖示，或透過 SET STATISTICS IO ON 觀察 Logical reads 是否異常偏高。 修正建議：確保應用程式傳入的參數型別與資料表欄位型別完全一致；使用 ORM 框架時須特別注意 nvarchar 與 varchar 的混用，以及資料庫 Collation 設定是否統一。 1-- 錯誤：欄位為 int，傳入字串導致隱含轉換 2WHERE CustomerID = '12345' 3 4-- 正確：型別一致，索引可正常 Seek 5WHERE CustomerID = 12345 Spill to TempDB（記憶體溢出至暫存資料庫） 成因：執行 Sort、Hash Match、或 Window Function 等需要工作記憶體的運算時，若 SQL Server 低估了資料量（通常源自過期的統計資料），分配的 Memory Grant 不足，多餘的中間資料便會溢出（Spill）至 TempDB 磁碟。 症狀： 執行計畫的 Sort 或 Hash Match 節點出現警告圖示。 TempDB 磁碟 IO 明顯飆升。 相同查詢的執行時間不穩定、變異大。 偵測方式：查看執行計畫節點屬性中的 Warning > SpillLevel；或查詢 sys.dm_exec_query_stats 的 total_spills 欄位找出高溢出查詢。 修正建議：這個問題通常是統計資料過舊，建議定期執行 UPDATE STATISTICS；此外，可以透過建立索引讓資料預先排序，減少 Sort 運算的需求，或將大量資料的操作拆分成批次處理。 1-- 更新統計資料 2UPDATE STATISTICS Orders WITH FULLSCAN; 3 4-- 建立索引預先排序，減少 Sort 需求 5CREATE INDEX IX_Orders_Date 6 ON Orders (OrderDate); Residual Predicate（殘餘篩選條件） 成因：Index Seek 只能用索引的前導鍵欄位（Seek Predicate）快速定位資料範圍，查詢條件中不屬於前導鍵的欄位，會在 Storage Engine 層逐列再次過濾（Predicate），稱為殘餘謂詞（Residual Predicate）。 症狀： 執行計畫中有 Index Seek，表面上看起來正常。 Seek 節點的 Rows Read 遠大於 Actual Rows，顯示大量資料列被讀取後又被篩掉。 Logical reads 偏高但不易察覺。 偵測方式：點開執行計畫中 Index Seek 節點的屬性，區分 Seek Predicates（有效利用索引）與 Predicates（殘餘過濾）兩個欄位的內容。 修正建議：將殘餘條件欄位加入複合索引的鍵欄位，或納入 INCLUDE 清單，調整索引欄位順序以符合查詢的過濾邏輯。 1-- 原索引只有 OrderDate, Status 成為殘餘條件 2-- 調整為複合索引以消除殘餘條件 3CREATE INDEX IX_Orders_DateStatus 4 ON Orders (OrderDate, Status); 容易混淆的差異 Missing Index vs Residual Predicate：前者是「完全沒有索引」，後者是「有索引但 Seek 只用了部分欄位」；看到執行計畫有 Index Seek 不代表沒有問題，需要進一步比對 Rows Read 與 Actual Rows 的差距。 Implicit Conversion：它會靜默破壞現有索引，讓原本正常的 Seek 退化成 Scan，執行計畫只會出現一個小黃警告，極易被忽略；ORM 框架是最常見的來源，尤其是字串型別混用或 Collation 不一致的情境。 Spill to TempDB：與其他三者性質不同，它不是索引問題，而是「查詢工作記憶體不足」的問題；根本原因通常是統計資料過舊，光是建立索引並無法解決，必須從統計資料更新與查詢設計兩方面著手。

延續上篇，當我們確保了資料存取的效率（消滅了不必要的 Scan 與 Key Lookup）後，接下來要面對的就是關聯式資料庫最核心的動作：將多張資料表結合在一起（Join）。 當你在 SQL 語法中寫下 INNER JOIN 或 LEFT JOIN 時，SQL Server 的 Query Optimizer（查詢最佳化程式）會根據資料表的大小、有沒有索引、以及資料是否已經排序，自動從武器庫中挑選最適合的實體運算子；在執行計畫中，一定會遇到以下這三位主角： 精緻的雙層迴圈：Nested Loops (巢狀迴圈) 運作原理：概念上就像是寫程式時的兩層 for 迴圈；SQL Server 會將資料量較小的表作為外部表（Outer Table），針對外部表的「每一列」，逐一去掃描內部表（Inner Table）尋找匹配的資料。 適合情境：小表 Join 大表，且大表的 Join 欄位上有索引；當內部表有索引時，SQL Server 可以直接使用高效的 Index Seek 來尋找目標，效能極好。 效能警訊：如果兩張表都很大，且內部表沒有索引（被迫變成 Table Scan），那麼時間複雜度 O(N X M) 會讓效能急速惡化。 暴力卻有效的碰撞：Hash Match (雜湊比對) 運作原理： 處理過程分為兩個階段。 Build（建立）階段：掃描較小的表，在記憶體中建立一個 Hash Table（雜湊表）。 Probe（探測）階段：逐一掃描較大的表，將每一列資料套用雜湊函數，去 Hash Table 中快速查詢並配對。 適合情境：兩張表都很大，且沒有合適索引的場合，這時 Query Optimizer 通常會自動選擇 Hash Match 作為最後防線；它的時間複雜度為 O(N + M)，在處理無索引大數據時效能相對穩定。 效能警訊：最大的致命傷是「記憶體消耗」，如果記憶體不足以容納整個 Hash Table，就會發生 Spill to TempDB（溢出到硬碟）的現象（圖示上會出現黃色驚嘆號Warning），此時讀寫速度會大幅下降，是必須優先解決的瓶頸。 完美排序的雙劍合璧：Merge Join (合併連接) 運作原理：就像拉鍊一樣的結合方式，前提是兩個輸入資料集都必須已經依照 Join Key 排序完成；執行時，SQL Server 會同時推進兩個指標，依序往下比對，兩邊的資料都只需要掃描一遍即可完成配對。 適合情境：兩表已有排序順序（如有索引，或前置步驟已排序），且資料量大的場合；這是記憶體需求極低、效能非常優異的演算法。 效能警訊：若資料本身未經排序，SQL Server 為了硬湊出 Merge Join，必須先在前方加入一個昂貴的 Sort（排序）運算子；這個額外付出的排序成本，往往會抵銷掉 Merge Join 帶來的所有優勢，得不償失。 優化建議 面對這三種 Join，我們該如何除錯與優化呢？ ...

在上一篇中，建立了閱讀執行計畫的「方向感」，學會透過箭頭粗細找出塞車路段；順著箭頭一路往右追溯到最源頭，就會看到 SQL Server 是如何進入資料庫「拿資料」，這一步至關重要，因為資料庫系統最大的效能瓶頸往往在於磁碟 I/O（資料讀寫），是在「精準尋找」還是在「盲目翻找」，決定了這個查詢是只要 0.1 秒，還是要跑 10 分鐘。 在執行計畫的最右側，最常看見的資料存取運算子有這幾種： 理想的境界：Index Seek 白話比喻：就像查字典時，利用部首或注音索引，直接翻到你要的那一頁。 背後意義：這是效能最好的存取方式，代表 SQL Server 完美利用了 B-Tree 索引結構，精確定位到符合 WHERE 條件的資料列；看到這個圖示通常代表你的索引設計順利發揮了作用。 尷尬的中間地帶：Index Scan 白話比喻：不用翻整本字典，但把字典的「整個附錄/整個目錄」從頭到尾看了一遍。 背後意義：看到 “Index” 這個字或許會覺得令人安心，但請注意後面的 “Scan”；這代表查詢條件無法讓 SQL Server 縮小搜尋範圍，只好把整個索引從頭到尾掃了一遍。 優化建議： 改善查詢條件（避免索引失效）：這是最常見的「索引殺手」，例如在欄位上套函數（如 WHERE YEAR(created_at) = 2026），或發生型別隱含轉換（字串比對數字欄位），可以改成對索引友善的寫法，例如：WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'，讓 SQL Server 能切換回 Index Seek。 建立或調整 Composite Index（複合索引）：把常常一起出現在 WHERE、ORDER BY 的欄位組合進同一個索引，且要注意欄位順序。 減少回傳欄位：盡量避免 SELECT *，只取真正需要的欄位，有助於讓 Optimizer 選擇更精準的索引。 效能的紅燈：Table Scan / Clustered Index Scan 白話比喻：為了找書裡的一句話，從第一頁逐字逐句讀到最後一頁。 背後意義：這是最需要優先處理的狀況，代表完全沒有可用的索引，或查詢條件根本繞過了索引；SQL Server 必須把整張表掃描一遍，一筆一筆核對。 優化建議： 建立索引：最直接的解法，可以先參考 Execution Plan 上方黃色字體的 Missing Index 提示來建立 Non-Clustered Index，這通常能立竿見影。 檢查資料選擇性（Selectivity）：如果你的 WHERE 條件篩出來的資料佔了整張表的 20%–30% 以上，SQL Server 會認為「既然都要抓這麼多資料，不如直接掃整張表比較快」；此時加索引沒用，需要檢討的是查詢邏輯本身。 更新統計資料：執行 UPDATE STATISTICS 或 sp_updatestats，有時候是資料庫的統計資訊太舊，導致 Optimizer 誤判，放著好好的索引不用跑去掃表。 檢查 NOLOCK 或 Hint 干擾：確認程式碼中是否有不必要的查詢提示（Hint）強制改變了 SQL Server 的預設行為。 隱藏的效能殺手：Key Lookup 白話比喻：用目錄找到了頁碼，但發現那頁只有大綱，還得跑去另一本詳細版的手冊裡翻出完整內容。 ...

在開發與維護資料庫的日常中，會遇到這樣的場景，一段 SQL 查詢平常跑得順順的，卻突然卡住，或者明明加了 WHERE 條件，資料庫卻慢到讓人想砸電腦。 面對效能瓶頸，可以打開 SQL Server 提供的導航地圖「執行計畫」（Execution Plan），執行計畫是 SQL Server 查詢最佳化工具（Query Optimizer）的詳細報告；然而，初次見到這張充滿各種圖示與線條的圖表時，往往是讓人眼花撩亂，不知所措。 閱讀方向：從右到左、從上到下 在解讀 SQL Server 的圖形化執行計畫時，閱讀順序是：從右到左、從上到下。 最右邊的節點（起點）：代表「資料的來源」；這裡通常是實體資料表（Tables）或是索引（Indexes）。這是 SQL Server 第一步要去硬碟或記憶體裡把基礎資料撈出來的地方。 中間的節點（過程）：每個圖示都代表一個「處理步驟」（Operator），例如：關聯（Join）、排序（Sort）、過濾（Filter）或群組化（Aggregate）。 最左邊的節點（終點）：代表「最終的輸出」；通常會是一個 SELECT、INSERT、UPDATE 或 DELETE 的圖示，表示這段查詢最後呈現給應用程式的結果。 從上到下：當一個步驟需要結合多個資料來源（例如 JOIN 兩張表）時，通常在圖形上方的分支會先被執行或作為外部輸入（Outer Input），下方的分支則作為內部輸入（Inner Input）。 下次打開 Plan 時，將目光移到畫面的最右端，看看 SQL Server 是從哪些表開始動手的而非一開始就盯著最左邊的 SELECT 看。 解讀箭頭的「粗細」 在各個節點之間，會看到許多連接的箭頭；這些箭頭不僅僅是指出資料流動的方向，它們還隱藏著極其重要的效能指標，箭頭的粗細，代表著傳遞的資料列數（Rows）多寡。 極細的箭頭：代表經過這個步驟後，只傳遞了非常少量的資料（甚至只有 1 筆）。 非常粗的箭頭：代表這裡有龐大的資料量正在兩個節點之間搬運。 掌握了箭頭粗細的含義，就能在幾秒鐘內靠「視覺」找出潛在的問題點： 漏斗效應（粗進細出）：如果看到一個節點，右邊進來的箭頭非常粗，但左邊出去的箭頭突然變得很細；這代表 SQL Server 在這個步驟（可能是一個 Filter 或 Hash Match）花費了巨大的力氣，過濾掉成千上萬筆不符合條件的資料，最後只留下幾筆，這通常是強烈的優化訊號，告訴我們「資料需要從源頭就開始進行篩選，而非把一堆資料搬進記憶體裡慢慢濾」。 一路粗到底：如果從最右邊到最左邊，箭頭始終粗得像水管一樣，這意味著你的查詢確實要回傳大量資料；這時你可以問應用程式端：「我們真的需要一次 SELECT 幾百萬筆資料出來嗎？能不能做分頁處理？」 閱讀 Execution Plan 最重要，就是建立「方向感」，只要掌握了正確的閱讀方向，就不會在複雜的 Plan 裡迷失；從「由右至左找源頭」以及「看箭頭粗細找瓶頸」這兩個導航法則，就能在面對那張複雜的網路圖時，擁有清晰的解讀脈絡，而當我們知道資料是從哪裡來，又在哪裡塞車之後，下一步就是要檢視 SQL Server 到實體表中「拿資料」的手法是否夠聰明了。