database - 在 SQL Server 中优化索引以进行排名

Question

我们有一张宽表，目前正在尝试优化。该表有 50 列(统计数据)，我们最终希望按降序排列。目前有超过 500 万行。

我们正在寻找在降低复杂性和提高读取速度方面优化此表的方法。写速度对我们来说也很重要，但读更关键。这些统计数据的排名应该尽可能接近实时，最佳解决方案是在每个请求的基础上快速排名(新行一直在添加，我们希望尽快显示这些行的排名.)

我们目前正在评估垂直表格布局是否 a.) 性能更高，b.) 更易于使用。

因为插入的统计数据不一定定义明确，如果它们不被硬编码到表中(因此首选垂直表结构)，对我们来说会更容易。

下面是我们当前的表结构和查询:

CREATE TABLE Stats 
(
    Id BIGINT PRIMARY KEY NOT NULL,
    UserId INT,
    Name VARCHAR(32) NOT NULL,
    Value DECIMAL(10,4) DEFAULT ((0)) NOT NULL,
    UpdatedAt DATETIME
);

CREATE INDEX Leaderboard__index ON Stats (Name, Value DESC);

SELECT
    Id,
    Name,
    Value,
    RANK() OVER (PARTITION BY Name ORDER BY Value DESC) AS Rank
FROM 
    Stats
ORDER BY 
    Value DESC

通常我们会搜索任何给定统计数据的前 N ​​行(例如排行榜)，或者我们会选择一个 UserId 并获取与该 UserId 关联的所有统计数据的排名。

数据量很大(我上面说了，因为有很多行和很多列，一个垂直表结构可能在2.5亿行范围内，而且还会继续增长。)

我们希望在任何需要的硬件上尽快获取这些数据，秒是我们的目标，因为我们目前处于分钟范围内。

在垂直表结构的测试中，我们插入了超过 400,000 行数据，上面的查询只用了不到 3 分钟(尽管对 10,000 行进行排名也只用了大约 18 秒。)

我很想听听任何建议。感谢您的宝贵时间!

Answer 1

您拥有的索引对您的窗口函数没有用，因为

1.To get ID column value, SQL may end up doing key lookups or even end up scanning whole other index if it crosses Tipping point.So your index may not be used at all.

2.You are ordering by val desc which requires a sort with no suitable index and may even endup spilling to TEMPDB

3.For one more interesting fragmenation aspect ,see below

通常要使窗口函数运行良好，您将需要一个POC索引，这意味着

P,O--Partition and order by columns should be in key clause
C--covering --columns you are including in select should be included

因此，下面的查询才能以最佳方式工作。

SELECT
    Id,
    Name,
    Value,
    RANK() OVER (PARTITION BY Name ORDER BY Value DESC) AS Rank
FROM 
    Stats
ORDER BY 
    Value DESC

您将需要以下索引

create index nci_test on dbo.table(name,value desc)
include(id)

您使用“value desc”创建的索引还有一个问题。

通常在一个索引中，所有的值都默认按升序存储，但是对于这个索引，你要求以相反的方式存储，这会导致逻辑碎片，这可以从answer中看出。的 Martin Smith此处 ..从此处粘贴答案中的相关术语 ...

If the index is created with keys descending but new rows are appended with ascending key values then you can end up with every page out of logical order. This can severely impact the size of the IO reads when scanning the table and it is not in cache

选项太少了..

1.根据您的频率运行索引重建，看看是否有帮助

2.将查询更改为按分区子句排序将消除使用“val desc”选项创建索引的需要

SELECT
        Id,
        Name,
        Value,
        RANK() OVER (PARTITION BY Name ORDER BY Value DESC) AS Rank
    FROM 
        Stats
    ORDER BY 
        name DESC

上面的查询不需要像您创建的那样创建索引。您可以像下面这样更改它..它也处理上面提到的碎片方面

CREATE INDEX Leaderboard__index ON Stats (Name, Value)
include(id);

引用资料:
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions