主页 / dba / 问题 / 23252
Accepted
AmmarR
Asked: 2012-08-28 23:07:57 +0800 CST

提高事实表的性能

  • 772

我有一个事实表 CardTransactionFact

表结构

TABLE [dbo].[CardTransactionFact]
    [CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionTerminalID] [int] NOT NULL,
    [SourceAccountTypeID] [int] NULL,
    [DestinationAccountTypeID] [int] NULL,
    [RimNo] [varchar](15) NULL,
    [CaptureCodeID] [int] NOT NULL,
    [RoutingCodeID] [int] NOT NULL,
    [ProcessingCodeID] [int] NOT NULL,
    [ActionCodeID] [int] NOT NULL,
    [NetworkCodeID] [int] NOT NULL,
    [ProductCodeID] [int] NOT NULL,
    [AcquiringCountryCodeID] [int] NOT NULL,
    [IssuingCountryCodeID] [int] NOT NULL,
    [TransactionCurrencyCodeID] [int] NOT NULL,
    [AmountBD] [decimal](18, 3) NOT NULL,
    [LocalCurrencyCodeID] [int] NOT NULL,
    [CardIssuerBank] [int] NOT NULL,
    [CardTypeID] [int] NOT NULL,
    [SuspectTransactionFlag] [char](1) NOT NULL,
    [ReversalTransactionFlag] [char](1) NOT NULL,
    [LocalTransactionDateKey] [int] NOT NULL,
    [LocalTransactionHourKey] [int] NOT NULL,
    [BBKRole] [char](1) NOT NULL,
    [AmountRangeKey] [int] NULL,
    [CustomerKey] [int] NULL

大小：11GB 行数：56,959,828

现在访问这个表变得非常困难，一个简单Select count(*) from CardTransactionFact的需要几个小时才能执行。

表中的大多数列只是整数，这就是我没有做任何索引的原因。

你认为我应该做些什么来改进这个表，并提高查询这个表的速度

  1. 如果索引我应该索引哪些列以及为什么
  2. 对表进行分区是个好主意吗
  3. 任何其他建议
sql-server-2008 index

3 个回答

  1. Best Answer

    这里有很多问题，谢天谢地，有很多可以修复的。

    问题：

    • 你有一堆。很有可能这是非常分散的，并且页面分布在整个 82GB 数据文件中。有关检查碎片的指导，请参阅sys.dm_db_index_physical_stats。
    • 您只有 6GB 的内存，如果幸运的话，缓冲池可能有 4GB 可用。
    • 从字里行间看，您正在使用狗慢速 SATA 旋转驱动器。
    • 对表的扫描将需要 11GB 的随机 IO 跨越该狗慢速驱动器，并将缓冲池完全搅动 3 次。

    修复：

    • 在表上创建聚集索引。CardTransactionId 看起来是目前唯一明智的选择。
    • 你迫切需要记忆。对于 82GB 的数据仓库来说，128GB 是合理的。
    • 您的 IO 严重不足。SSD 将是最便宜、最快的解决方案。

    11GB 不适合 6GB，真的就这么简单。一个非常粗略的估计表明该表将占用大约 150 万个 8KB 页面，如果 100 IOPS 从磁盘读取大约需要 4 个小时（假设最坏的情况，100% 随机读取，没有预读等）。

    • 9

  2. 替换您的查询

    SELECT COUNT(*) FROM CardTransactionFact

    下面有

    SELECT Rows FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_NAME('CardTransactionFact')

    你必须Clustered Index在你的表中。执行DBCC CONTIG检查堆表中的碎片

    Create Clustered INDEX IX_Column on TableName(COLUMNNAME)

    桌子上出现的一个问题是碎片化的问题。根据执行的活动（例如删除、插入和更新），您的堆表和聚簇表可能会变得碎片化。这在很大程度上取决于活动以及用于聚集索引的键值。

    1. 如果您的堆表仅发生 INSERTS，则您的表不会碎片化，因为只会写入新数据。
    2. 如果您的聚集索引键是顺序的，例如标识值，并且您只有 INSERTS，那么这也不会成为碎片，因为新数据始终写入聚集索引的末尾。
    3. 但是，如果您的表是堆表或聚集表，并且有很多 INSERTS、UPDATES 和 DELETES，则数据页可能会变得非常碎片化。这导致浪费空间以及需要读取额外的数据页以满足查询。将表创建为堆时，SQL Server 不会强制写入新数据页的位置。每当写入新数据时，此数据始终写入表的末尾或分配给此表的下一个可用页。当数据被删除时，数据页中的空间变得空闲，但它不会被重新使用，因为新数据总是被写入下一个可用页。使用聚集索引，根据索引键，新记录可能会写入存在可用空间的现有页面，或者可能需要将页面拆分为多个页面以插入新数据。

    索引重建前的统计

    在此处输入图像描述

    再次执行DBCC CONTIG以检查堆表中的碎片

    索引重建后的统计

    重建索引查询以删除索引 

    ALTER INDEX ALL 
ON TableName
REBUILD WITH 
(
     FILLFACTOR = 80, 
     SORT_IN_TEMPDB = ON,
     STATISTICS_NORECOMPUTE = ON
);

    在此处输入图像描述

    参考

    • 2

  3. 索引和分区都可以提供很大帮助。但是哪些索引以及如何划分分区在很大程度上取决于您在它们上运行的查询。

    如果没有索引或分区，查询优化器将不得不为每个查询读取完整的表。

    对于分区部分，是否有一个逻辑列可以轻松用于将数据分隔到多个分区中？是否可以将此列添加到大多数查询的 where 子句中？

    • 0

相关问题