这实际上是一个更大的复杂查询的一部分。
根据查询计划,此语句的排序主导较大查询的成本。
通过具体化查询的这一部分,我验证了它在成本中占主导地位。
select [sID], ROW_NUMBER() over (partition by [sID] order by [wordPos]) [rn], [wordPos], [wordID]
from [FTSindex]
where [wordID] in (428,2112)
order by [sID], [rn]
从右到左:
- 索引查找 5% (IX_FTSindex_wordID_sID)
- 排序 76%
- 并行度 19%
CREATE TABLE [dbo].[FTSindex](
[sID] [int] NOT NULL,
[wordPos] [int] NOT NULL,
[wordID] [int] NOT NULL,
[charPos] [int] NOT NULL,
CONSTRAINT [PK_FTSindex] PRIMARY KEY CLUSTERED
(
[sID] ASC,
[wordPos] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_FTSindex_wordID_sID] ON [dbo].[FTSindex]
(
[wordID] ASC,
[sID] ASC,
[wordPos] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
鉴于 IX_FTSindex_wordID_sID 包括 [sID] 和 [wordPos],我认为排序会非常快。
单独尝试了 [wordID] 和 [wordID]、[sID],排序仍然是 76% 的成本。
即使这个查询
select [sID], [wordPos] -- , wordID
from [FTSindex]
where [wordID] in (428,2112)
order by [sID], [wordPos]
排序是排序是 76% 或成本。
如何才能降低排序成本?
PK必须保持原样。
我可以添加或修改其他索引。