我有一个连接字段 PRODID 上的两个表的简单查询。出于某种原因,SQL Server 选择使用哈希匹配来连接它,但我认为它应该选择合并连接,因为该字段是索引的一部分,并且两个索引的前面字段已经用于过滤掉大部分数据。所以两个索引中的下一个字段是 PRODID,应该对其进行排序。
查询:
select JOURNAL.PRODID, JOURNAL.JOURNALID
from PRODJOURNALTABLE JOURNAL
inner join PRODROUTEJOB JOB on JOB.PRODID = JOURNAL.PRODID and JOB.DATAAREAID = JOURNAL.DATAAREAID and JOB.PARTITION = JOURNAL.PARTITION
where JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
and JOURNAL.POSTED = 1
and JOURNAL.JOURNALTYPE = 1
and JOURNAL.DATAAREAID = N'LAN'
and JOURNAL.PARTITION = 5637144576
and JOB.WRKCTRID = N'TF1'
查询计划 https://www.brentozar.com/pastetheplan/?id=B1oyc8qBh
使用的索引
CREATE NONCLUSTERED INDEX [I_243ROLLERRORVIEWIDX] ON [dbo].[PRODJOURNALTABLE]
(
[PARTITION] ASC,
[DATAAREAID] ASC,
[JOURNALTYPE] ASC,
[POSTED] ASC,
[POSTEDDATETIME] ASC,
[PRODID] ASC
)
INCLUDE([JOURNALID])
CREATE NONCLUSTERED INDEX [I_258ROLLERRORVIEWIDX] ON [dbo].[PRODROUTEJOB]
(
[PARTITION] ASC,
[DATAAREAID] ASC,
[WRKCTRID] ASC,
[PRODID] ASC,
[OPRNUM] ASC
)
INCLUDE([OPRPRIORITY])
编辑:
将 JOIN HINT 合并连接添加到查询显示它尝试对字段 PRODID 上的 PRODJOURNALTABLE 中的数据进行排序
select JOURNAL.PRODID, JOURNAL.JOURNALID
from PRODJOURNALTABLE JOURNAL
inner merge join PRODROUTEJOB JOB on JOB.PRODID = JOURNAL.PRODID and JOB.DATAAREAID = JOURNAL.DATAAREAID and JOB.PARTITION = JOURNAL.PARTITION
where JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
and JOURNAL.POSTED = 1
and JOURNAL.JOURNALTYPE = 1
and JOURNAL.DATAAREAID = N'LAN'
and JOURNAL.PARTITION = 5637144576
and JOB.WRKCTRID = N'TF1'
查询计划:https://www.brentozar.com/pastetheplan/? id=rJh1GPqBn
但是应该是按照索引排序的吧?
有点有点
为等式谓词维护索引顺序,但您有一个不等式(范围)谓词:
JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
这导致排序运算符需要
PRODID
为合并连接设置正确的顺序:为了获得没有排序的合并连接计划,您需要将索引更改为:
您将不再获得 的搜索谓词
POSTEDDATETIME
,但这可能不会有太大影响,因为您还有许多其他谓词在起作用。我在这里谈论这个: