引言
在處理資料庫支援的應用程式時，管理效能是一項持續的任務。執行緩慢的查詢可能導致超時，降低使用者體驗，消耗更多資源，甚至可能根據你支付資料庫費用的方式影響你的預算。這些問題使得理解資料庫的效能特性變得很重要，以便你能識別並修復有問題的查詢。
在本指南中，我們將討論在 PostgreSQL 資料庫中識別低效查詢的不同方法。隨後，我們將討論可用於修復慢查詢以維護 PostgreSQL 效能的不同技術。
Prisma ORM 中的 PostgreSQL 聯結器允許你直接從 JavaScript 和 TypeScript 應用程式管理你的 PostgreSQL 資料庫。
你可以透過 CLI 快速設定帶有生產就緒的 Prisma Postgres 資料庫的 Prisma ORM
然後按照此指南構建一個完整的 TypeScript 應用程式。
檢查活動查詢和程序
在嘗試追蹤低效能查詢時，首先要檢查的是當前活動查詢和程序的列表。PostgreSQL 透過 pg_stat_activity 檢視提供此資料。
The pg_stat_activity 檢視是 PostgreSQL 累積統計系統中的一個可用檢視。它包含每個伺服器程序的一行，幫助你檢視每個程序當前正在處理什麼。
要顯示檢視中的所有資訊，請鍵入
注意： 使用 \gx 行終止符序列而不是傳統的 `;` 分號告訴 PostgreSQL 為當前查詢使用擴充套件輸出模式。這會將每個記錄的列和相關值垂直而不是水平顯示，這在某些情況下可以提高可讀性。
輸出中有許多欄位在查詢較慢的查詢時會很有幫助。其中一些最相關的包括
state：程序的當前狀態。列為active的行正在執行。其他狀態包括idle（等待新客戶端命令的程序）、idle in transaction（在事務上下文中等待命令的程序）和idle in transaction (aborted)（語句導致錯誤的事務）。query：最近執行的查詢。對於活動程序，這將是當前正在執行的查詢。usename：與程序關聯的使用者名稱。application_name：連線到程序的應用程式名稱。datname：使用者連線到的資料庫名稱。wait_event：程序正在等待的事件名稱（如果有）。如果程序處於active狀態並且存在wait_event，則表示查詢當前被系統中的其他部分阻塞。wait_event_type：程序正在等待的事件類別。pid：程序的程序 ID。query_start：對於活動查詢，表示當前查詢開始的時間戳。xact_start：如果程序正在執行事務，表示當前事務開始的時間戳。
我們可以根據當前上下文的相關列來過濾查詢。一個有用的模式是使用 age() 函式來計算查詢已執行的時間。例如
這將顯示非空閒查詢的執行時間、使用者名稱、資料庫和查詢文字。我們將結果按查詢執行時間從長到短排序，並從結果中排除此特定查詢。
同樣，你可以看到所有非空閒但有等待事件的程序
這可以幫助你檢視因系統其他部分（例如，鎖競爭）而未當前進展的查詢。
檢查其他系統統計資訊
儘管 pg_stat_activity 檢視可能會提供你識別慢查詢所需的大部分資訊，但檢視其他系統統計資訊也很有用，以幫助識別額外的最佳化目標。
檢視資料庫統計資訊
The pg_stat_database 表包含每個資料庫的統計資訊
對於我們的目的，一些有趣的列包括
blks_read：資料庫中讀取的磁碟塊數。blks_hit：磁碟塊在緩衝區快取中被找到的次數（避免了從磁碟進行慢速讀取）。xact_commit：已提交的事務數。xact_rollback：已回滾的事務數。
正如 Data Egret 團隊在其部落格上所示，你可以使用這些原始值來計算有趣的統計資料，例如你的快取命中率
這可能是很有價值的資訊，可以幫助你評估是否可以透過增加資料庫叢集的 RAM 來獲益，以便最常見的查詢可以有效地快取。
查看錶統計資訊
另一組有用的檢視是 pg_stat_all_tables、pg_stat_user_tables 和 pg_stat_sys_tables。pg_stat_all_tables 檢視顯示所有資料庫的訪問統計資訊，而另外兩個檢視則根據表是使用者表還是系統表進行過濾。
這些檢視中一些有趣的列包括
seq_scan：在表上執行的順序掃描次數。seq_tup_read：順序掃描返回的行數。idx_scan：針對表執行的索引掃描次數。idx_tup_fetch：透過索引檢索的行數。
這些列中的數字可以幫助你評估索引的效能以及你的查詢是否有效地使用了它們。如果你發現你的表有許多順序掃描，你可能會受益於建立可供你最常見查詢使用的額外索引。
檢視索引命中情況
如果你需要更多關於當前索引的資訊，可以檢視 pg_stat_all_indexes、pg_stat_user_indexes 和 pg_stat_sys_indexes 檢視
這些檢視提供了關於你的每個索引被使用頻率的資訊。idx_scan 列顯示索引被掃描的次數。idx_tup_read 列顯示掃描返回的條目數，而 idx_tup_fetch 顯示索引掃描返回的總行數。
這些資訊有助於你瞭解何時存在未被查詢使用的索引。一旦識別出這些索引，你可以重寫查詢以利用它們，或者刪除未使用的索引以提高寫入效能。
檢視鎖資訊
你收集的一些關於慢查詢的資訊可能指向了鎖定問題。你可以透過查詢 pg_locks 檢視來獲取當前所有持有鎖的更多資訊
輸出將提供 PostgreSQL 中所有鎖的資訊。這有助於你診斷當不同程序請求控制相同物件時可能發生的爭用問題。
一些可能幫助你調查問題鎖的列包括
啟用慢查詢日誌記錄
更輕鬆地查詢長時間執行查詢資訊的一種方法是啟用慢查詢日誌記錄。啟用慢查詢日誌記錄允許 PostgreSQL 自動記錄任何執行時間超過給定時間的查詢。這使你能夠收集有關在調查時未執行的慢查詢的資訊。
檢查 PostgreSQL 是否已啟用慢查詢日誌記錄
你首先應該做的是驗證慢查詢日誌記錄的當前狀態。如果慢查詢日誌記錄已啟用，你無需進行任何操作。
你可以透過鍵入以下內容來檢查是否啟用了慢查詢日誌記錄
如果你檢查 short_desc 和 extra_desc 列的值，你將找到允許我們評估日誌記錄是否當前啟用的資訊。我們可以看到慢查詢日誌記錄當前*未*啟用，因為 setting 列當前設定為 -1。
現在你瞭解了當前狀態，可以根據需要進行更改。
配置 PostgreSQL 以記錄慢查詢
在繼續之前，需要注意的是，雖然慢查詢日誌記錄非常有用，但它可能會產生額外的效能影響。PostgreSQL 必須執行額外的操作來計時每個查詢並將其結果記錄到日誌中。這可能會影響效能並意外地佔用硬碟空間。
始終記錄慢查詢可能不是一個好主意。相反，在你積極調查問題時啟用該功能，並在完成後停用它。
全域性記錄慢查詢
考慮到這一點，你可以透過修改 PostgreSQL 伺服器的配置檔案來全域性配置慢查詢日誌記錄。你也可以互動式地修改這些值，但在配置檔案中設定良好的預設值將使以後互動式調整變得更容易。
開啟 PostgreSQL 的配置檔案。你可以透過鍵入以下內容找到當前配置檔案的位置
在檔案中，搜尋 log_min_duration_statement 設定。如果我們的示例輸出值是從配置檔案中讀取的，它將被設定為 -1 以指示該功能當前已停用。還有許多其他相關設定可以根據你的需要進行調整
目前，log_min_duration_statement 設定被註釋掉，其當前值設定為 -1 以表示預設值。檔案中的其他設定都有很好的註釋，允許你對超過最小值的語句進行取樣，而不是記錄所有語句。最後一個設定還允許你對事務中發生的語句進行取樣。
你可以透過取消註釋 log_min_duration_statement 並將其設定為另一個值來開啟長查詢日誌記錄。例如，我們可以將其設定為 5 秒，以記錄任何執行時間超過此值的語句
儲存檔案後，你可以透過在 PostgreSQL 中鍵入以下內容來重新載入 PostgreSQL 伺服器
你可以透過再次檢查當前值來驗證伺服器是否正在使用你的新設定
現在，setting 欄位設定為 5000，unit 欄位設定為 ms，表明我們設定的 5 秒已轉換為 5000 毫秒並已應用。sourcefile 行也確認此值是從我們修改的配置檔案中讀取的。
按資料庫記錄慢查詢
在嘗試檢測慢查詢時，另一個選項是將慢查詢日誌記錄限制到特定資料庫。雖然 log_min_duration_statement 可以全域性設定，如上一節所示，但它也可以在資料庫級別進行配置。
要為單個數據庫開啟慢查詢日誌記錄，請使用 ALTER DATABASE 命令
請注意，與全域性設定不同，當使用 ALTER DATABASE 命令時，該值必須是一個不帶單位的整數，表示毫秒時間。
我們可以透過查詢每個資料庫的角色設定來驗證該設定是否已應用
我們可以驗證這沒有干擾我們之前設定為 5 秒閾值的全域性設定
測試慢查詢日誌記錄
透過發出一個超過最小日誌記錄持續時間的語句來測試該設定
檢查日誌，你應該會找到指示發生了長時間執行查詢的語句
由於我們為全侷限制和特定表設定了不同的閾值，因此我們可以透過使用應觸發其中一個而非另一個的查詢時間來測試每個閾值是否正確應用。
例如，我們可以連線到具有較低閾值的資料庫並休眠 4 秒，這應該會觸發一條日誌行
我們的日誌顯示
現在，我們可以切換到只受全域性設定影響的另一個數據庫。相同的休眠語句不應觸發日誌行
不應記錄新的日誌行。
總結
在本文中，我們介紹瞭如何檢視和理解 PostgreSQL 提供的一些效能資訊。檢視這些資訊可以讓你深入瞭解系統資源、查詢模式和配置設定中的不同瓶頸。當你遇到效能問題時，可以檢查 PostgreSQL 提供的資訊，開始調查問題行為。
我們還討論瞭如何使用慢查詢日誌記錄來精確找出哪些查詢正在佔用系統資源並執行時間超出預期。記錄這些資料並評估生成的日誌可以幫助你確定可能需要額外索引、不同查詢結構或更高效查詢設計的地方。瞭解如何識別這些耗費資源的操作是執行更具功能性的資料庫支援應用程式的第一步。
The PostgreSQL 資料庫聯結器可以幫助你從 JavaScript 和 TypeScript 應用程式管理 PostgreSQL 資料庫。瞭解如何將 Prisma 新增到現有專案或如何從頭開始使用 Prisma。
