我有这张桌子:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
amount NUMERIC(18, 2) DEFAULT NULL NULL,
-- Some 100 columns
customer_msisdn VARCHAR(255) DEFAULT NULL NULL,
customer_email VARCHAR(255) DEFAULT NULL NULL,
payment_date DATETIME2 NOT NULL
);
CREATE NONCLUSTERED INDEX msisdn_idx ON transactions (customer_msisdn, payment_date, id);
CREATE NONCLUSTERED INDEX email_idx ON transactions (customer_email, payment_date, id);
我每月索引大约 100 万行。非常频繁地,我需要选择过去 3 个月的每笔交易customer_msisdn
或每笔交易customer_email
,即 99% 的时间 50 - 1000 条记录。
这是我的查询以获得更多见解:
SELECT t.*
FROM transactions t
JOIN (SELECT t.id
FROM transactions t
WITH (FORCESEEK)
WHERE t.customer_email = :customerEmail
AND t.payment_date >= :startDate
AND t.payment_date < :endDate
UNION
SELECT t.id
FROM transactions t
WITH (FORCESEEK)
WHERE t.customer_msisdn = :customerMsisdn
AND t.payment_date >= :startDate
AND t.payment_date < :endDate) AS filtered_transactions
ON t.id= filtered_transactions.id
ORDER BY t.payment_date;
而且我觉得既然:endDate
总是现在(如果不是,可以容忍错误)并且:startDate
总是三个月前,我还有一些改进的空间。这就是我的想法:
创建一个带有过滤器的索引视图payment_date
:
CREATE VIEW [dbo].transactions_iv
WITH SCHEMABINDING AS
SELECT [t].id,
-- All the rows
[t].customer_msisdn,
[t].customer_phone,
[t].payment_date
FROM [dbo].[transactions] [t]
WHERE [t].payment_date >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP);
和我的索引:
CREATE NONCLUSTERED INDEX msisdn_iv_idx ON transactions_iv (customer_msisdn, id);
CREATE NONCLUSTERED INDEX phone_iv_idx ON transactions_iv (customer_phone, id);
并从查询中完全删除AND t.payment_date >= :startDate AND t.payment_date < :endDate
子句。查询变为:
SELECT t.*
FROM transactions_iv t
JOIN (SELECT t.id
FROM transactions_iv t
WITH (FORCESEEK)
WHERE t.customer_email = :customerEmail
UNION
SELECT t.id
FROM transactions_iv t
WITH (FORCESEEK)
WHERE t.customer_msisdn = :customerMsisdn) AS filtered_transactions
ON t.id= filtered_transactions.id
ORDER BY t.payment_date;
由于该视图仅包含最近 3 个月的交易,我假设索引也是如此。这个假设正确吗?是否会仅更新索引以涵盖最近 3 个月的记录,并且我的性能会得到提升吗?
另一种选择是:
- 创建另一个相同的表,
- 用主表上的触发器填充它
- 使用 cron 作业,每晚删除 3 个月以上的记录。
此选项与前一个选项相比如何?
您的索引视图无法按书面形式创建,因为它是不确定的。随着时间的推移,行会从视图中消失。
您当前的查询可能会生成一个执行计划,例如:
暂时搁置单独的表和索引视图的问题,试一下下面的小重写(使用现有索引):
请注意 中的额外列
t.payment_date
,UNION
并且ORDER BY
已更改为filtered_transactions.payment_date
。这在语义上与您的查询没有什么不同,但它会帮助优化器找到更好的计划。你应该得到一个相当有效的执行计划,比如:
优化器确实应该选择该计划形状(或者可能是并行版本),但如果不是,则可能需要一个或多个提示。一个极端的例子: