sql-server - Azure SQL 数据库 - 对 10 多百万行建立索引

Question

我的数据库托管在 Azure SQL 数据库上，下面是单个表的架构:

CREATE TABLE [dbo].[Article](
    [ArticleHash] [bigint] NOT NULL,
    [FeedHash] [bigint] NOT NULL,
    [PublishedOn] [datetime] NOT NULL,
    [ExpiresOn] [datetime] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [Url] [nvarchar](max) NULL,
    [Title] [nvarchar](max) NULL,
    [Summary] [nvarchar](max) NULL
 CONSTRAINT [PK_dbo.Article] PRIMARY KEY CLUSTERED 
(
    [ArticleHash] ASC,
    [FeedHash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

我正在执行的一些查询非常慢，因为该表包含超过 1000 万条记录:

SELECT * 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY PublishedOn DESC) page_rn, *
      FROM Article
      WHERE (FeedHash = -8498408432858355421 AND ExpiresOn > '2016-01-18 14:18:04.970')
     ) paged 
WHERE page_rn>0 AND page_rn<=21 

还有一个:

SELECT ArticleHash
FROM Article
WHERE (FeedHash = -8498408432858355421 
       AND ArticleHash IN (-1776401574438488264,996871668263687248,-5186412434178204433,6410875610077852481,-5428137965544411137,-5326808411357670185,2738089298373692963,9180394103094543689,8120572317154347382,-369910952783360989,1071631911959711259,1187953785740614613,6665010324256449533,3720795027036815325,-5458296665864077096,-5832860214011872788,-2941009192514997875,334202794706549486,-5579819992060984166,-696086851747657853,-7466754676679718482,-1461835507954240474,9021713212273098604,-6337379666850984216,5502287921912059432) 
       AND ExpiresOn >= '2016-01-18 14:28:25.883')

<小时/>

为此表建立索引以使查询执行时间低于 300 毫秒的最佳方法是什么？这么大的 table 上还可以吗？ Azure SQL 数据库版本是 S3。

此外，在此表上执行了许多 DELETE/INSERT 操作，因此任何索引都不应影响这些操作的性能...

Answer 1

第一个查询将受益于 native pagination使用 OFFSET 和 FETCH:

SELECT * 
FROM Article
WHERE FeedHash = -8498408432858355421 AND ExpiresOn > '2016-01-18 14:18:04.970'
ORDER BY PublishedOn DESC
OFFSET 0 FETCH NEXT 20 ROWS ONLY

第二个查询可能会受益于用表的 INNER JOIN 替换 IN 列表:

DECLARE @ArticleHashList AS TABLE (ArticleHashWanted bigint PRIMARY KEY);
INSERT INTO @ArticleHashList (ArticleHashWanted) VALUES
    (-1776401574438488264),
    (  996871668263687248),
    (-5186412434178204433),
    ( 6410875610077852481),
    (-5428137965544411137),
    (-5326808411357670185),
    ( 2738089298373692963),
    ( 9180394103094543689),
    ( 8120572317154347382),
    ( -369910952783360989),
    ( 1071631911959711259),
    ( 1187953785740614613),
    ( 6665010324256449533),
    ( 3720795027036815325),
    (-5458296665864077096),
    (-5832860214011872788),
    (-2941009192514997875),
    (  334202794706549486),
    (-5579819992060984166),
    ( -696086851747657853),
    (-7466754676679718482),
    (-1461835507954240474),
    ( 9021713212273098604),
    (-6337379666850984216),
    ( 5502287921912059432);

SELECT ArticleHash
FROM Article
INNER JOIN @ArticleHashList On ArticleHash = ArticleHashWanted
WHERE FeedHash = -8498408432858355421 AND ExpiresOn >= '2016-01-18 14:28:25.883';

在日期上创建索引应该会有很大帮助:

CREATE INDEX idx_Article_PublishedOn ON Article (PublishedOn);
CREATE INDEX idx_Article_ExpiresOn ON Article (ExpiresOn);