如何優化這個 IN 查詢?
可以說我有這個架構
Resource(resource_mapping_id:Uuid, reaource_id: uuid, node_id: varchar, date: date, available: boolean, resource_start: varchar, resource_end: varchar)
所以我在上面形成了複合鍵
(resource_mapping_id, resource_id, node_id, date, resource_start, resource_end)
注意:節點也是 uuid 儲存為文本。現在我有這兩個查詢:
update resource set available = :value where resource_id=:somevalue and date=:somedate and resource_start=:sometime and resource_end=:sometime
和
select * from resource where resource_id In (:resourceidlist) and date in (:dates) and node_id in (:nodeIds)
這張表包含大量的記錄,你可以說大約 5 億左右..
因此,每當我通過 jpa 通過我的 Java 應用程序訪問這些查詢時,它們都會使數據庫的 CPU 使用率飆升至 100%。
所以在做了分析之後,我創建了一個索引說
Index(resource_id, node_id, date)
這反過來又解決了更新查詢的問題,即使它在並行執行緒中執行,CPU 也從未出現過一點點飆升。
但是現在來到 select 語句,當參數變高時我遇到了問題。所以我對它們進行了批量處理,我的意思是批量 x 沒有。可以處理節點 ID、資源 ID 和日期的數量,即使使用 100(注意,所有參數的大小相同,如果我 tel 100,總共 300)參數,它會加速 CPU 和其他執行緒進入等待狀態!
如何解決這個問題?我應該改變我的查詢還是什麼?我是否應該僅針對這種情況進行任何其他更改或創建進一步的索引?請幫我。
我正在使用 postgres v13。
為了支持該
SELECT
陳述,您首先需要確定三個條件中的哪一個是選擇性的,即過濾掉大量行。對於每個選擇性條件,只在該列上創建一個 B 樹索引,這樣您最終會得到最多三個單列索引。PostgreSQL 可以結合這些索引來加快查詢速度。在這種情況下,多列索引不是很有用。
對於
UPDATE
,子句中出現的所有列的多列索引WHERE
將是完美的。但是 PostgreSQL 也可以使用我們上面創建的單列索引。總有一個權衡:索引可以加快查詢速度,但會減慢數據修改速度。所以你不想擁有太多。
在 where 子句序列中創建表索引並嘗試它。例如。
where resource_id In (:resourceidlist) and date in (:dates) and node_id in (:nodeIds)
表索引如
Table Index(resource_id, date , node_id )