我可以将这两个 SQL Server 索引合并成一个吗?

huangapple go评论76阅读模式
英文:

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).

我可以将这两个 SQL Server 索引合并成一个吗?

huangapple
  • 本文由 发表于 2023年6月12日 23:36:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76458197.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定