主页 / dba / 问题 / 23465
Accepted
Nate
Asked: 2012-08-31 11:24:52 +0800 CST

是否可以在具有数百万行的窄表上提高查询性能？

  • 772

我有一个查询，目前平均需要 2500 毫秒才能完成。我的表很窄，但有 4400 万行。我有哪些选择来提高性能，或者这是否已经达到了最好的水平？

查询

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

桌子

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

指数

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

添加其他索引会有所帮助吗？如果是这样，它们会是什么样子？当前的性能是可以接受的，因为查询只是偶尔运行，但我想知道作为一个学习练习，我能做些什么来让它更快吗？

更新

当我更改查询以使用强制索引提示时，查询在 50 毫秒内执行：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'

添加正确选择性的 DeviceID 子句也会达到 50 毫秒的范围：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

如果我添加ORDER BY [DateEntered], [DeviceID]到原始查询，我在 50 毫秒范围内：

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' 
ORDER BY [DateEntered], [DeviceID];

这些都使用我期望的索引（CommonQueryIndex）所以，我想我现在的问题是，有没有办法强制这个索引用于这样的查询？或者我的表的大小是否过多地抛弃了优化器，我必须只使用一个ORDER BY或一个提示？

sql-server optimization

4 个回答

  1. Best Answer

    为什么优化器不适合您的第一个索引：

    CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    是 [DateEntered] 列的选择性问题。

    您告诉我们您的表有 4400 万行。行大小为：

    4 个字节用于 ID，4 个字节用于设备 ID，8 个字节用于日期，1 个字节用于 4 位列。即 17 字节 + 7 字节开销（标签、空位图、var col 偏移量、col 计数）总计每行 24 字节。

    这将转化为 140k 页。存储这 4400 万行。

    现在优化器可以做两件事：

    1. 它可以扫描表（聚集索引扫描）
    2. 或者它可以使用您的索引。对于索引中的每一行，它需要在聚集索引中进行书签查找。

    现在在某个时刻，为非聚集索引中找到的每个索引条目在聚集索引中进行所有这些单一查找变得更加昂贵。其阈值通常是查找的总计数应超过总表页计数的 25% 到 33%。

    所以在这种情况下：140k/25%=35000 行 140k/33%=46666 行。

    （@RBarryYoung，35k 是总行数的 0.08%，46666 是 0.10%，所以我认为这就是混乱所在）

    因此，如果您的 where 子句将导致 35000 到 46666 行之间的某个位置。（这是在顶部子句的下方！）很可能不会使用您的非聚集索引扫描并且将使用聚集索引扫描。

    改变这种情况的唯一两种方法是：

    1. 使您的 where 子句更具选择性。（如果可能的话）
    2. 删除 * 并仅选择几列，以便您可以使用覆盖索引。

    现在确定即使您使用 select * 也可以创建覆盖索引。但是，这只会为您的插入/更新/删除带来巨大的开销。我们必须更多地了解您的工作负载（读取与写入），以确保这是否是最佳解决方案。

    从 datetime 更改为 smalldatetime 会使聚集索引的大小减少 16%，非聚集索引的大小减少 24%。

    • 15

  2. 您的 PK 集群是否有特殊原因？许多人这样做是因为它默认为这种方式，或者他们认为 PK 必须是集群的。不这样。聚集索引通常最适合范围查询（例如这个）或子表的外键。

    聚簇索引的一个作用是将所有数据聚集在一起，因为数据存储在聚簇 b 树的叶节点上。因此，假设您没有要求“太宽”的范围，优化器将确切知道 b 树的哪个部分包含数据，并且它不必找到行标识符然后跳到数据所在的位置是（就像处理 NC 索引时一样）。什么是范围“太宽”？一个荒谬的例子是从只有一年记录的表中要求 11 个月的数据。假设您的统计数据是最新的，那么提取一天的数据应该不是问题。（不过，如果您正在查找昨天的数据并且三天没有更新统计信息，优化器可能会遇到麻烦。）

    由于您正在运行“SELECT *”查询，因此引擎将需要返回表中的所有列（即使有人添加了您的应用当时不需要的新列），因此覆盖索引或索引如果有的话，包含列将无济于事。（如果您将表中的每一列都包含在索引中，那么您做错了。）优化器可能会忽略那些 NC 索引。

    那么该怎么办？

    我的建议是删除 NC 索引，将聚簇 PK 更改为非聚簇并在 [DateEntered] 上创建一个聚簇索引。越简单越好，直到证明不是这样。

    • 8

  3. 只要你有那个“*”，那么我能想象的唯一会产生很大不同的事情就是将你的索引定义更改为：

    CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats] 
(
    [DateEntered] ASC,
    [DeviceID] ASC
)INCLUDE (ID, IsWebUp, IsPingUp, IsPUp)
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    正如我在评论中指出的那样，它应该使用该索引，但如果不使用，您可以使用 ORDER BY 或索引提示来说服它。

    • 4

  4. 我会以不同的方式看待这一点。

    • 是的，我知道这是一个旧线程，但我很感兴趣。

    我会转储日期时间列 - 将其更改为 int。有一个查找表或为您的日期进行转换。

    转储聚集索引 - 将其保留为堆，并在表示日期的新 INT 列上创建非聚集索引。即今天将是 20121015。该顺序很重要。根据加载表的频率，查看按 DESC 顺序创建该索引。维护成本会更高，您将需要引入填充因子或分区。分区还有助于减少运行时间。

    最后，如果您可以使用 SQL 2012，请尝试使用 SEQUENCE - 它的插入性能将优于 identity()。

    • 3

相关问题