代码之家 › 专栏 › 技术社区 › derivation

从大表中获取不同值的最佳方法

performance sql-server

derivation · 技术社区 · 14 年前

我有一个DB表,大约有10列,其中两列是月和年。该表现在大约有25000行,我们预计它每月将增长大约100-150k条记录。很多查询都涉及到月份和年份列(例如,所有记录都来自2010年3月),因此我们经常需要获得可用的月份和年份组合(即我们有2010年4月的记录吗?).

一位同事认为,我们应该有一个单独的表格,与我们的主要表格,只包含我们有数据的月份和年份。我们每个月只向主表添加一次记录,所以在脚本的末尾进行一次小的更新,将新条目添加到第二个表中。每当我们需要在第一个表中找到可用的月/年条目时,都会查询第二个表。这个解决方案对我来说很模糊,违反了dry。

你认为解决这个问题的正确方法是什么?有比两张桌子更好的方法吗?

5 回复 | 直到 6 年前

Gabriel Guimarães 14 年前

确保在这些列上有一个聚集索引。在这些日期列上对表进行分区,并将数据文件放在不同的磁盘驱动器上我相信保持低指数是你最好的选择。

我也相信,用理想的选择来观察物体并不是一个好主意, 因为它增加了插入/更新开销。平均每分钟插入3,5次。或者每次插入之间大约17秒(如果我错了,请平均纠正我)

问题是你是不是每17秒就要选择一次? 这是关键的想法。希望有帮助。

Adriaan Stander 14 年前

在所需的列(年和月)上使用简单索引应该 大大提高 要么是 独特的 或 小组通过 查询。

我不会使用辅助表,因为这会增加维护辅助表的额外开销(插入/更新删除需要验证辅助表)

编辑:

你甚至可以考虑使用 Improving Performance with SQL Server 2005 Indexed Views

David Sopko 6 年前

使用“物化视图”(也称为“带架构绑定的索引视图”),然后索引此视图。执行此操作时,SQL Server将在后台创建和维护辅助表中的数据,并在适当时选择使用此表上的索引。

这与同事的建议类似,优点是不需要向查询中添加逻辑就可以利用它,SQL Server将在创建查询计划时执行此操作,SQL Server也将自动在索引视图中维护数据。

以下是实现此目标的方法:创建一个返回不同[月][年]值的视图,然后在该视图上索引[年][月]。同样,sql server将在视图上使用小索引,避免在大表上扫描表。

由于SQL Server不允许您使用DISTINCT关键字为视图编制索引,因此请使用GROUP BY[年]、[月],并在“选择”中使用大计数(*)。它看起来像这样:

CREATE VIEW dbo.vwMonthYear WITH SCHEMABINDING
AS

   SELECT
     [year],
     [month],
     COUNT_BIG(*) [MonthCount]
   FROM [dbo].[YourBigTable]
   GROUP BY [year],[month]
GO

CREATE UNIQUE CLUSTERED INDEX ICU_vwMonthYear_Year_Month 
   ON [dbo].[vwMonthYear](Year,Month)

现在,当您在大表上选择distinct[year]、[month]时,查询优化器将扫描视图上的小索引,而不是扫描大表上的数百万条记录。

SELECT DISTINCT
   [year],
   [month]
FROM YourBigTable

这项技术使我从500万次读取(估计I/O为10.9)到36次读取(估计I/O为0.003)。这方面的开销是维护一个额外的索引,因此每次更新大表时,视图上的索引也将更新。

如果你发现这个指数大大减缓了你的加载时间。删除索引,执行数据加载,然后重新创建它。

完整工作示例:

    CREATE TABLE YourBigTable(
        YourBigTableID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_YourBigTable_YourBigTableID PRIMARY KEY,
        [Year] INT,
        [Month] INT)
    GO  


    CREATE VIEW dbo.vwMonthYear WITH SCHEMABINDING
    AS

       SELECT
          [year],
          [month],
          COUNT_BIG(*) [MonthCount]
       FROM [dbo].[YourBigTable]
       GROUP BY [year],[month]
    GO

    CREATE UNIQUE CLUSTERED INDEX ICU_vwMonthYear_Year_Month ON [dbo].[vwMonthYear](Year,Month)


    SELECT DISTINCT
       [year],
       [month]
    FROM YourBigTable

-- Actual execution plan shows SQL server scaning ICU_vwMonthYear_Year_Month

KM. 14 年前

创建的物化索引视图:

SELECT DISTINCT
    MonthCol, YearCol
    FROM YourTable

现在,您可以访问预先计算的不同值,而不必每次都完成工作。

Remus Rusanu 14 年前

将日期设为表的聚集索引键中的第一列。这对于历史数据来说是非常典型的,因为大多数查询(如果不是全部的话)都对特定的范围感兴趣,而一个及时的聚集索引可以解决这个问题。“五月”等所有查询都需要作为范围处理,例如: WHERE DATECOLKEY BETWEEN '05/01/2010' AND '06/01/2001' . 回答“5月份有没有记录”这样的问题需要对聚集索引进行简单的搜索。

虽然这对程序员来说似乎很复杂,但这是处理数据库设计问题的最佳方法。