查询表，同时排除其他表中引用的值

Question

我有一个数据库，里面装满了来自各种银行账户的交易。每笔交易都带有user_id、bank_id、account_id和transaction_id。如果用户选择忽略银行、帐户或单个交易，我想在查询时排除交易。

换句话说，如果用户：

• 忽略银行，跳过该bank_id的所有交易，
• 忽略一个帐户，跳过该account_id的所有交易，
• 忽略单个事务，将跳过具有该transaction_id的事务。

我当前的数据库如下所示：

-- Simplified for brevity.
CREATE TABLE IF NOT EXISTS transactions
(
user_id        TEXT NOT NULL,
transaction_id TEXT NOT NULL,
account_id     TEXT NOT NULL,
bank_id        TEXT NOT NULL,
PRIMARY KEY (user_id, transaction_id)
);
-- Exclusion tables for banks and accounts are similar.
CREATE TABLE IF NOT EXISTS excluded_transactions
(
id             INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
user_id        TEXT NOT NULL,
transaction_id TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS exc_trn_idx ON excluded_transactions (user_id, transaction_id);

每当用户abc排除银行、帐户或交易时，都会将其添加到相应的排除表中。然后，查询如下所示：

WITH b AS (
SELECT bank_id FROM excluded_banks WHERE user_id = 'abc'
), a AS (
SELECT account_id FROM excluded_accounts WHERE user_id = 'abc'
), t AS (
SELECT transaction_id FROM excluded_transactions WHERE user_id = 'abc'
)
SELECT * FROM transactions 
WHERE user_id = 'abc'
AND bank_id NOT IN (SELECT * FROM b) 
AND account_id NOT IN (SELECT * FROM a)
AND transaction_id NOT IN (SELECT * FROM t)

这在 ~1M 事务的测试集上提供了良好的性能(平均 ~100ms 计划时间，~1s 执行时间(。但是，我担心它会随着数据库的增长而显着降低。

我的问题是：如何改进表/查询以有效地检索具有上述约束的事务？如果较慢的写入速度加快，则可以接受。另外，如果我采取的一般方法不是最佳的，请告诉我和/或建议改进的方法。

Answer 1

我建议把它写成：

SELECT t.*
FROM transactions t
WHERE t.user_id = 'abc' AND
NOT EXISTS (SELECT 1
FROM excluded_banks eb
WHERE eb.bank_id = t.bank_id AND
eb.user_id = t.user_id
) AND
NOT EXISTS (SELECT 1
FROM excluded_accounts ea
WHERE ea.account_id = t.account_id AND
ea.user_id = t.user_id
) AND
NOT EXISTS (SELECT 1
FROM excluded_transaction et
WHERE et.transaction_id = t.transaction_id AND
et.user_id = t.user_id
);

然后，请确保您具有以下索引：

• excluded_banks(user_id, bank_id)
• excluded_accounts(user_id, account_id)
• excluded_transaction(user_id, transaction_id)