查詢性能中的聯接與虛擬表

假設我有兩個由外鍵連結的表

範例 DDL

CREATE TABLE raw_collection (
   info_datetime DATETIME NOT NULL, 
   asset_id INTEGER NOT NULL, 
   price FLOAT, 
   PRIMARY KEY (info_datetime, asset_id), 
   FOREIGN KEY(asset_id) REFERENCES assets (id)
);

CREATE TABLE assets (
   id INTEGER NOT NULL, 
   asset_symbol VARCHAR, 
   PRIMARY KEY (id)
);

asset_symbol在表格上過濾時raw_collection，什麼（以及為什麼）將是一個更快的解決方案。使用連接或從表中創建記憶體表並根據它進行asset_symbol過濾？raw_collection

---

查詢

-- query 1
with needed_assets as (
 select id 
 from assets
 where asset_symbol in ('a', 'b'))
select *
from raw_collection
where asset_id in (select id from needed_assets) ;
-- where asset_id in (table needed_assets) ;  -- Postgres only syntax
-- where asset_id in needed_assets ;          -- SQLite only syntax

-- query 2
select r.*
from raw_collection r join assets a on r.asset_id = a.id 
where a.asset_symbol in  ('a', 'b') ;

---

我正在使用 PostgreSQL 11 或 SQLite 3.28.0

這取決於很多事情。您在其中一個標籤中列出了 postgres，因此應注意 CTE 將完全實現，因為 CTE 是阻止謂詞下推的優化柵欄。我不確定 sqlite，您在標籤中列出的另一個數據庫，但並非所有數據庫都是這種情況，SQL Server 可以跨 CTE 進行優化。

因此，對於目前的 postgres † ，所有其他條件都相同並且存在適當的索引，如果有足夠的assets匹配數據asset_symbol in ('a', 'b'))以使差異變得明顯，我希望查詢 2 會更快。

無論哪種情況，您定義的索引也會產生影響。特別要注意的兩件事是，標記raw_collection.asset_id為外鍵不會自動 為該列創建索引，這可能會限制查詢計劃者的選擇，如果您還沒有索引，那麼索引覆蓋assets.asset_symbol可能很有用，除非表的行數很少。

†注意：這是由於版本 12 的變化，該版本即將發布，儘管您聲明您使用的是 v11，這與您無關

引用自：https://dba.stackexchange.com/questions/249168