主页 / dba / 问题 / 6883
Accepted
codecool
Asked: 2011-10-18 05:25:24 +0800 CST

从头开始索引还是出现性能问题时？

  • 772

我的问题是关于索引的使用。

  1. 我应该从一开始就开始编制索引还是在出现性能问题时开始编制索引？

  2. 我们还可以在执行查询时创建临时索引。这些技术的优缺点是什么？

performance index

5 个回答

  1. Best Answer

    我应该从一开始就开始编制索引还是在出现性能问题时开始编制索引？

    索引策略往往会随着使用模式的出现而发展。也就是说，还有可以预先应用的策略和设计指南。

    • 选择一个好的集群键。您通常可以在设计时根据表的预期插入模式确定合适的聚簇索引。如果未来出现令人信服的变革案例，那就顺其自然吧。

    • 创建您的主要约束和其他唯一约束。这些将由唯一索引强制执行。

    • 创建您的外键和关联的非聚集索引。外键是您最常引用的连接列，因此从一开始就为它们建立索引。

    • 为任何明显高度选择性的查询创建索引。对于您已经知道的查询模式将具有高度选择性，并且可能使用查找而不是扫描。

    除此之外，采取循序渐进的整体方法来实施新指标。整体而言，我的意思是在评估添加时评估对所有查询和现有索引的潜在好处和影响。

    由于缺少索引 DMV 和 SSMS 提示的指导，SQL Server 圈子中的一个常见问题是过度索引。这些工具都不会评估现有索引，并且会愉快地建议您创建一个新的 6 列索引，而不是将单个列添加到现有的 5 列索引中。

    -- If you have this
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
)

-- But your query would benefit from the addition of a column
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

-- SSMS will suggest you create this instead
CREATE NONCLUSTERED INDEX [IX_MyTable_AnotherIndexWithTheSameColumnsAsTheExistingIndexPlusCol6] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

    Kimberly Tripp有一些关于索引策略的优秀材料，虽然专注于 SQL，但也适用于其他平台。对于 SQL Server 用户，有一些方便的工具可以识别重复项，如上面的示例。

    我们还可以在执行查询时创建临时索引。这些技术的优缺点是什么？

    这通常只适用于很少运行的查询，通常是 ETL。您需要评估：

    1. 创建索引所花费的时间是否减少了查询的执行时间。
    2. 保留索引的维护开销是否超过在需要时创建/删除所花费的时间。
    • 17

  2. 这两种方法都存在风险：

    选项 a)从一开始就建立索引，但没有意识到您已经创建了许多从未使用过的索引。这些增加了一些开销（最明显的是修改数据的查询，还有试图识别最佳索引的 SELECT 语句的优化）。

    您需要约束自己来识别不再使用的索引并尝试删除它们（PostgreSQL 可以做到这一点；不幸的是，相比之下 MySQL 在这方面非常弱。）

    选项 b)在人们开始抱怨之前不要添加索引，或者您的诊断工具触发某些查询很慢并且可以改进。

    您引入的风险是，在您注意到需要索引和必须添加索引之间没有足够大的时间窗口。

    PostgreSQL 确实支持构建索引CONCURRENTLY，这确实减轻了这种突然添加索引的要求带来的一些压力，但手册中有一些注意事项。

    选项 (b) 往往是我的偏好，但我认为两种选项的混合可能是最好的解决方案。它与您对是否实际使用索引的信心水平有关。

    使这个讨论特别复杂的原因是更改索引通常很容易，但更改架构却比较困难。我不想将 b 的延迟反应作为鲁莽的借口。

    • 8

  3. 除了马克的回答

    您可以通过获得预期数量的真实测试数据来感受一下。我见过很多很多（太多）情况，其中查询在 1000 行的情况下运行正常，但在生产中却运行不正常。

    如果可以的话，稍后再制作一份产品副本，

    当然，我只在生产中看到过奇怪的问题，因为当其他一切都相同时使用模式

    临时索引？在 ETL 加载模式之外，如果您需要它们一次，您将再次需要它们。不要忘记：索引创建/删除是写入并记录=更多负载

    • 4

  4. 只是为了添加一些东西。

    • 临时索引是一个糟糕的主意..除非索引在临时表上。
    • 索引占用的数据空间（以及其他开销）比人们意识到的要多得多。因此，保守地创建它们。

    这是我的方法。

    1. 与 Mark 类似，在有意义的地方创建索引，但不要逾期。
    2. 您不必等到性能变慢再创建新索引。每当您编写新的 SQL 时，运行一个查询计划（最好针对您的产品数据库）。您应该能够看到是否需要新索引。

    3. 不要害怕在未使用的列的 where 子句中放置> 0或。> ""

      1. 也就是说，假设您有一个关于 A、B、C 和 D 的索引。但是，您只有信息 A、B、D。你没有理由做不到-
      select * from blah 
where A="one" 
and B="two" 
and C>=""     --to match index
and D="four"

--This will use your existing index. No need to create a redundant one.
    • 3

  5. 我将尝试只回答第一个问题。如果您甚至可以粗略地从一开始就估计在一定时间后您的表中将有多少记录，那么我会说最好从头开始设计一些索引。尝试使用一些测试工具或测试脚本，这些工具或测试脚本将尽可能多地自动调用您认为最常使用的应用程序调用，您将看到从一开始就可以避免哪些表扫描。

    一开始这将是猜测工作，但随着时间的推移，当您拥有正确的使用统计信息时，您将拥有更清晰的图像。

    • 2

相关问题