英文:
Can I merge these two Sql Server index into one:
问题
我可以为您翻译代码部分。以下是合并索引的SQL代码:
创建非聚集索引 [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus_SendTime]
在 [dbo].[SMSInOutBoxDetails] 上
(
[SMSInOutBox_ID] 升序,
[SendStatus] 升序,
[SendTime] 升序
)
包含 ([ID], [Number], [Delivery], [Cost], [RefId], [IsRead], [MobileOperator], [WebServiceOutboxDetailId],
[Order], [sort])
在 [PRIMARY] 上
如果您需要任何其他帮助,请随时告诉我。
英文:
I have a table in Sql server 2022 with more tahn 100 milion records, Can I merge these two index into one:
create nonclustered index [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus] on [dbo].[SMSInOutBoxDetails]
(
[SMSInOutBox_ID] asc,
[SendStatus] asc
)
include ([Cost]) on [PRIMARY]
create nonclustered index [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime] on [dbo].[SMSInOutBoxDetails]
(
[SMSInOutBox_ID] asc,
[SendTime] asc
)
include ([ID], [Number], [Delivery], [Cost], [RefId], [SendStatus], [IsRead], [MobileOperator],
[WebServiceOutboxDetailId], [Order],
[sort]) on [PRIMARY]
Merge Index:
create nonclustered index [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus_SendTime] on [dbo].[SMSInOutBoxDetails]
(
[SMSInOutBox_ID] asc,
[SendStatus] asc,
[SendTime] asc
)
include ([ID], [Number], [Delivery], [Cost], [RefId], [IsRead], [MobileOperator], [WebServiceOutboxDetailId],
[Order], [sort])
on [PRIMARY]
答案1
得分: 2
这不是一个自动快速合并候选项。当您尝试合并它们时,您需要选择是使用 [SMSInOutBox_ID], [SendStatus], [SendTime]
还是 [SMSInOutBox_ID], [SendTime], [SendStatus]
。
您提出的索引将支持 IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus
所支持查询的超集(尽管可能会牺牲一些逻辑读取,因为它不会像现有索引那样紧凑)。
但它不会支持 IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime
所支持的所有查询。
例如,如果您有一个查询:
SELECT SMSInOutBox_ID,
SendStatus,
Cost,
SendTime,
ID,
Number,
Delivery,
RefId,
IsRead,
MobileOperator,
WebServiceOutboxDetailId,
[Order],
sort
FROM dbo.SMSInOutBoxDetails
WHERE [SMSInOutBox_ID] = @SMSInOutBox_ID
AND [SendTime] > @SendTime
ORDER BY [SendTime]
那么 IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime
将支持从单个索引搜索中返回结果,无需任何排序操作。
如果您只有有限数量的发送状态(也许是已交付、已发送、已拒绝和失败?),您的新索引仍然可以支持,但这更加繁琐(还存在新状态可能添加而不反映在这里的风险)。
上面的查询执行了4个独立的索引搜索,将按 SendTime, RowLocator
顺序返回行,并使用合并联合操作将它们组合成全局结果集,无需排序。
英文:
This is not an automatic quick win merge candidate. When you try to combine them you need to select whether to go for
[SMSInOutBox_ID], [SendStatus], [SendTime]
or for [SMSInOutBox_ID], [SendTime], [SendStatus]
.
Your proposed index will support a superset of the queries that IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus
does (though maybe at the expense of some logical reads as it won't be as compact as the existing one).
But it won't support all the queries that IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime
does.
For example if you have a query
SELECT SMSInOutBox_ID,
SendStatus,
Cost,
SendTime,
ID,
Number,
Delivery,
RefId,
IsRead,
MobileOperator,
WebServiceOutboxDetailId,
[Order],
sort
FROM dbo.SMSInOutBoxDetails
WHERE [SMSInOutBox_ID] = @SMSInOutBox_ID
AND [SendTime] > @SendTime
ORDER BY [SendTime]
Then IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime
will support returning this from a single index seek without any sort operations.
If you only have a limited number of send statuses though (maybe Delivered, Sent, Rejected and Failed?) your new index could still support this but it is more cumbersome (and also runs a risk that new statuses might be added without being reflected here)
WITH T AS
(
SELECT SMSInOutBox_ID,
SendStatus,
Cost,
SendTime,
ID,
Number,
Delivery,
RefId,
IsRead,
MobileOperator,
WebServiceOutboxDetailId,
[Order],
sort
FROM dbo.SMSInOutBoxDetails
WHERE [SMSInOutBox_ID] = @SMSInOutBox_ID
AND [SendTime] > @SendTime
)
SELECT *
FROM T
WHERE SendStatus = 1
UNION ALL
SELECT *
FROM T
WHERE SendStatus = 2
UNION ALL
SELECT *
FROM T
WHERE SendStatus = 3
UNION ALL
SELECT *
FROM T
WHERE SendStatus = 4
ORDER BY SendTime, ID /*I have assumed ID is a clustered primary key here*/
The above does 4 independent index seeks that will bring back the rows in order of SendTime, RowLocator
and combines them with a merge union to bring back the global resultset without any sorting (Fiddle).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论