我有一个数据库,里面充斥着来自各种银行帐户的交易。每个交易配有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的计划时间,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)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句