遊戲 Lag 白皮書 › L12 資料庫
執行計畫改變造成的查詢延遲 Query plan regression (stats, parameter sniffing)
原因 ID db-plan-flip · 主要負責 基礎設施團隊（DB 基礎設施） · 協同 遊戲開發團隊（伺服器開發）
在含圖解與實驗的完整版中開啟卡片 →
程式碼沒變，DB 卻改變了處理同一個查詢的方式（執行計畫）時，昨天只要 2ms 的查詢，今天會變成數百 ms。
為什麼 統計資訊自動更新、DB 重新啟動或資料分布改變，使 DB 重新建立執行計畫 → 於是 選中了不走索引的計畫，同一個查詢慢了數十～數百倍，連線被占住 → 畫面上 明明沒有部署，特定功能的載入卻突然變慢，連其他請求也要等待
- 症狀
- 輸入延遲, 連不上／無限讀取
- 因素
- 延遲, 停滯
- 誰會遇到
- 只有特定功能, 整個伺服器
- 何時
- 偶爾隨機發生
- 負責單位
- 主要負責 基礎設施團隊（DB 基礎設施） · 協同 遊戲開發團隊（伺服器開發）
- 遊戲開發團隊要做的事
- 結果筆數會因參數值而差異很大的查詢，拆開使用或評估加上計畫提示（hint）；設計確定會走索引的查詢。
- 基礎設施團隊要做的事
- 監看慢查詢與執行計畫的歷史紀錄、固定好的計畫（SQL Server 的查詢存放區等）、管理統計資訊的更新時間。
- 圖表上
- 從某個時間點起階梯式上升 · 各查詢的平均執行時間
- 查看位置
- 定期收集相同形式查詢的平均時間，觀察變化趨勢。MySQL 為 events_statements_summary_by_digest 的 AVG_TIMER_WAIT，PostgreSQL 為 pg_stat_statements 的 mean_exec_time（12 以下為 mean_time）。變慢前後的執行計畫用 EXPLAIN 或 PostgreSQL auto_explain 比較，SQL Server 則用查詢存放區的「迴歸查詢」（Regressed Queries）畫面比較
- 符合的跡象
- 在沒有部署的時間點，某個查詢的平均時間階梯式上升數十倍，該時間點與統計資訊更新或 DB 重新啟動重疊，且執行計畫已經改變
- 不符合的跡象
- 執行計畫沒變卻變慢時，是資料量增加、鎖定等待（db-hot-row）或磁碟的問題
- 確認方式
- 用基礎設施工具確認（不需要遊戲程式碼）
- 深入了解
- SQL Server 會重複使用依第一次傳入的值所建立的計畫（參數探查，parameter sniffing）。依只有幾個道具的新角色建立的計畫，用在擁有數萬個道具的老角色上時會大幅變慢，反過來的情況也很常見。重新啟動清掉計畫後會暫時恢復正常，之後又可能再度變差。
出處
- Query Processing Architecture Guide Microsoft SQL Server
參數探查（parameter sniffing）：依編譯或重新編譯時傳入的參數值建立執行計畫 - Parameter Sensitive Plan Optimization Microsoft SQL Server
資料分布不均時，快取中的單一計畫無法適用所有參數值 - Monitor performance by using the Query Store Microsoft SQL Server
統計資訊、schema、索引的變化會改變計畫，而計畫快取只保留最新的計畫；可用查詢存放區的強制計畫固定好的計畫，並在「迴歸查詢」（Regressed Queries）畫面比較變慢的查詢與其計畫 - Statement Summary Tables MySQL
events_statements_summary_by_digest：依相同形式的查詢彙總 COUNT_STAR、AVG_TIMER_WAIT（平均時間） - pg_stat_statements — track statistics of SQL planning and execution PostgreSQL
每個陳述式的 calls、total_exec_time、mean_exec_time（平均執行時間） - pg_stat_statements (PostgreSQL 12 Documentation) PostgreSQL
12 以前的欄位名稱為 total_time、mean_time - auto_explain — log execution plans of slow queries PostgreSQL
把耗時超過 auto_explain.log_min_duration 的查詢的執行計畫寫入 log
相關原因
同一層：L12 資料庫
同一症狀（輸入延遲）在其他層的原因
查看含圖解與實驗的完整版卡片