主页 / dba / 问题 / 82604
Accepted
Horaciux
Asked: 2014-11-14 10:47:55 +0800 CST

特定查询的聚簇索引改进

  • 772

我找到了一张看起来像这样的表：

 CREATE TABLE [dbo].Table1 (  
  id INT primary key IDENTITY (1, 1),  
  [idUser] INT  NOT NULL ,  
  [Amount] INT NOT NULL ,  
  [Attempts] INT NOT NULL ,  
  [date] [datetime] NOT NULL ,  
  [SUM_Amount] INT NOT NULL   
 ) ON [PRIMARY]

此表由作业创建并填充特定时期的聚合数据。

特点：

  • 该表最多可容纳一百万行
  • idUser 是唯一的
  • sum_Amount 是前几行金额的总和。

该表将保持原样，没有更新或删除或插入操作。只是这种类型的查询：

 select top (@n) * from table1
 order by [SUM_Amount] desc, [Attempts] desc

 select top (@n) * from table1
 where [SUM_Amount] >=@m order by [SUM_Amount] asc

我认为更改为这样的聚集索引会提高性能：

 CREATE TABLE [dbo].Table2 (  
  id INT IDENTITY (1, 1),  
  [idUser] INT  NOT NULL ,  
  [Amount] INT NOT NULL ,  
  [Attempts] INT NOT NULL ,  
  [date] [datetime] NOT NULL ,  
  [SUM_Amount] INT NOT NULL  

 CONSTRAINT [PK_Nueva]
   PRIMARY KEY CLUSTERED ([SUM_Amount] desc, [Attempts] desc, id asc)   

 ) ON [PRIMARY]

我读到使用非唯一聚集索引会添加一个 4 字节的隐藏列（http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx），所以我决定添加集群索引的身份（不确定这是否是正确的方法）

我想问（冒着听起来荒谬的风险，但需要确定）：

  • 如何改进？
  • 我会影响磁盘大小吗？
  • 插入所有数据后是否应该重建索引？

编辑：

关于id，我认为是不是有个坏习惯。我会保留它，不确定以前的工作如何计算运行总计（我无法访问它）

有很多这样的表，每天有数百个（不要问我为什么）。这就是为什么 DBA 团队因为大小问题而要求我不要创建新索引。这就是为什么我考虑通过聚集索引重新排列表结构。还更改超出正常范围的数据类型。

sql-server-2005

3 个回答

  1. Best Answer

    是的，像您建议的那样放置聚簇索引将为您提供这两种特定类型查询的出色查询性能，但它可能会给该表上的大多数其他 SELECT 查询带来灾难。我会提供一个替代解决方案：

    我建议在单个唯一列上添加基本聚集索引，例如idUser：

    ALTER TABLE dbo.Table1 ADD
    PRIMARY KEY CLUSTERED (idUser)
        WITH (FILLFACTOR=100);

    ...然后专门为您的查询构建一个单独的非聚集索引：

    CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    WITH (FILLFACTOR=100);

    聚簇索引（主键）不会为您的表添加任何空间——它只是另一种通过对二叉树中的行进行预排序来组织存储的方式，这将使访问表的速度更快。

    非聚集索引会占用一些空间，但它相对较小：SUM_Amount和Attempts都是整数值，所以它们每个都有 4 个字节大，这意味着您的非聚集索引最终应该有大约 12 MB 的一百万行（包括“聚类键”，它也是一个整数）。

    您的两个示例查询将使用非聚集索引来查找最大/最小值，这将Key lookup在执行计划中生成一个运算符，但是因为您使用的是TOP (@n)，我认为您甚至不会注意到它的性能成本. 如果您绝对需要更好的性能并且想要消除Key lookup，您可以INCLUDE ()将非聚集索引中的所有相关列形成一个所谓的覆盖索引，有效地使索引成为原始表的排序副本：

    CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    INCLUDE (id, idUser, Amount, date)
    WITH (FILLFACTOR=100);

    如果数据未更改，则无需重建索引，尤其是当整个表在单个批次中填充时。但是，如果该表已随时间填充，您可能会看到一些碎片。

    • 4

  2. 一百万行和一张窄表并不是真正的大表。我希望大多数查询都能在亚秒级返回，除非您将整个表拉回到客户端，在这种情况下，在 SSMS 网格中呈现行确实很花时间。

    话虽如此，这可能是索引视图的一个很好的用途（而且数量不多！）。我不倾向于经常使用它们，因为它们会破坏基础表上的 INSERT / UPDATE / DELETE 性能。但是，由于您的表是静态的并且您有自定义报告需求，因此您可以在索引视图上创建多个非聚集索引来为它们提供服务。他们还可以帮助聚合。这是我的 100 万行简单装备，它显示了带有支持索引的索引视图如何为您工作：

    USE tempdb
GO

SET NOCOUNT ON
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.vw_table1_reports') IS NOT NULL DROP VIEW dbo.vw_table1_reports
IF OBJECT_ID('dbo.Table1') IS NOT NULL DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1 (
    id                  NUMERIC PRIMARY KEY IDENTITY (1, 1),  
    [idUser]            INT  NOT NULL,  
    [Amount]            INT NOT NULL,  
    [Attempts]          INT NOT NULL,  
    [date]              DATETIME NOT NULL,  
    [SUM_Amount]        INT NOT NULL
) ON [PRIMARY]
GO


-- Add one million rows
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.Table1 ( idUser, Amount, Attempts, date, SUM_Amount )
SELECT rn % 33 AS idUser, rn % 44 AS Amount, rn % 7 AS Attempts, DATEADD( day, rn % 300, '1 Jan 2014' ) AS [date], rn % 77 AS SUM_Amount
FROM cte
GO

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Original Queries START
------------------------------------------------------------------------------------------------

-- Original queries
DECLARE @n INT = 10, @m INT = 33

SELECT TOP (@N) * 
FROM dbo.table1
ORDER BY [SUM_Amount] DESC, [Attempts] DESC

SELECT TOP (@n) * 
FROM dbo.table1
WHERE [SUM_Amount] >= @m 
ORDER BY [SUM_Amount] ASC
GO

-- Original Queries END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Indexed Views START
-- with supporting non-clustered indexes
------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.vw_table1_reports') IS NOT NULL DROP VIEW dbo.vw_table1_reports
GO
CREATE VIEW dbo.vw_table1_reports
WITH SCHEMABINDING
AS
SELECT id, idUser, Amount, Attempts, [date], SUM_Amount
FROM dbo.table1
GO

CREATE UNIQUE CLUSTERED INDEX cdx_vw_table1_reports ON dbo.vw_table1_reports ( id )
GO

CREATE INDEX idx_vw_table1_reports1 ON dbo.vw_table1_reports ( [SUM_Amount] DESC, [Attempts] DESC )
INCLUDE ( idUser, Amount, [date] )
GO

CREATE INDEX idx_vw_table1_report2 ON dbo.vw_table1_reports ( [SUM_Amount] ASC )
INCLUDE ( idUser, Amount, Attempts, [date] )
GO

-- Indexed Views END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Revised Indexes START
------------------------------------------------------------------------------------------------

DECLARE @n INT = 10, @m INT = 33

-- Revised query 1
SELECT TOP (@n) * 
FROM dbo.vw_table1_reports
ORDER BY [SUM_Amount] DESC, [Attempts] DESC

-- Revised query 2
SELECT TOP (@n) * 
FROM dbo.vw_table1_reports
WHERE [SUM_Amount] >= @m 
ORDER BY [SUM_Amount] ASC
GO

-- Revised Indexes END
------------------------------------------------------------------------------------------------

    我的任何结果：

    在此处输入图像描述

    请注意，原始查询的执行时间不到一秒，但修改后的查询“更快”。

    如果必须重新加载表，请删除并重新创建这些索引。

    总而言之，如果您的查询在亚秒级返回，您不必太担心索引。如果不是，请考虑索引视图，考虑它们的使用限制和可能的开销。

    • 4
    1. 为什么要创建列名“id”（如果没有原因，我建议删除它）。
    2. 对于Clustered Index的问题，我觉得他的KEY应该只有，“[SUM_Amount]”。原因是您的查询将执行“INDEX SCAN”。对于这种类型的操作，聚集索引为我们提供了最佳性能（聚集索引页面在数据之外进行了排序）。
    • 0

相关问题