在时间戳上创建索引以优化查询

我有以下表格的疑问:

SELECT * FROM MyTable WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime]

我想优化这个查询，我正在考虑在时间戳上添加索引，但不确定这是否有帮助。理想情况下，我希望将时间戳设置为聚集索引，但是除了主键之外，MySQL 不支持聚集索引。

  • MyTable有400多万行。
  • Timestamp实际上是 INT类型。
  • 插入行后，将永远不更改该行。
  • 具有任意给定 Timestamp的行数平均约为20，但可能高达200。
  • 新插入的行的 Timestamp大于大多数现有行，但可能小于一些较新的行。

Timestamp上的索引能帮助我优化这个查询吗？

73994 次浏览
小开
最佳答案

毫无疑问。如果没有索引，则查询必须查看表中的每一行。使用索引，只要查找正确的行，查询就几乎是即时的。您将支付的代价是在插入中 轻微的性能下降; 但这实际上将是轻微的。

小开

绝对应该使用索引。MySQL 不知道这些时间戳的顺序，为了找到给定时间戳(或时间戳范围)的记录，它需要查看每一条记录。加上四百万人，那可是相当长的一段时间！索引是告诉 MySQL 关于数据的方式——“我会经常查看这个字段，所以请保留一个列表，列出我在哪里可以找到每个值的记录。”

对于常规查询字段，索引通常是一个好主意。定义索引的唯一缺点是它们使用额外的存储空间，所以除非空间非常紧张，否则应该尝试使用它们。如果它们不适用，MySQL 无论如何都会忽略它们。

小开

如果您的查询主要使用这个时间戳，您可以测试这个设计(将时间戳作为第一部分放大主键) :

CREATE TABLE perf (
, ts INT NOT NULL
, oldPK
, ... other columns
, PRIMARY KEY(ts, oldPK)
, UNIQUE (oldPK)
) ENGINE=InnoDB ;

这将确保您发布的查询将使用集群(主)键。

缺点是您的插入会有点慢。另外，如果表上有其他索引，它们将使用更多的空间(因为它们将包含4字节宽的主键)。

这种聚集索引的最大优点是具有大范围扫描的查询，例如，必须读取表的大部分或整个表的查询将按照需要的顺序(BY timestamp)找到相关行，如果你想按日、周、月或年进行分组，这也很有用。

旧的 PK 仍然可以通过保留 UNIQUE约束来标识行。

您可能还想了解一下 TokuDB，它是一个支持 多重聚类指数多重聚类指数的 MySQL (和开源)变体。

小开

我不否认索引对于提高选择查询时间的重要性，但是如果您可以对其他键进行索引(并使用这些索引构成查询) ，那么可能就不需要对时间戳进行索引。

例如，如果您有一个包含 timestamp、 category和 userId的表，那么最好在 userId上创建一个索引。在包含许多不同用户的表中，这将大大减少用于搜索时间戳的剩余集合。

如果我没记错的话，这样做的好处是可以避免在每次插入时创建时间戳索引的开销——在一个具有高插入率和高度唯一时间戳的表中，这可能是一个重要的考虑因素。

我正在努力解决基于时间戳和其他键的索引的同样问题。我还有测试要做，这样我才能证明我在这里说的话。我将根据我的结果尽量回复。

更好的解释是:

  1. 时间戳99% 唯一
  2. UserId 80% 惟一

  3. 类别25% 独一无二

    • 对时间戳进行索引可以快速地将查询结果减少到表大小的1%
    • 对 userId 进行索引可以快速地将查询结果减少到表大小的20%
    • 对类别进行索引可以快速地将查询结果减少到表大小的75%
    • 使用时间戳上的索引进行插入将具有很高的开销 * *
    • 尽管我们知道我们的插入将尊重时间戳递增的事实，但是我没有看到任何关于基于增量键的 MySQL 优化的讨论。
    • 使用 userId 上的索引进行插入会带来相当高的开销。
    • 使用类别上的索引插入将具有相当低的开销。

* * 对不起，我不知道计算开销或插入索引。


提交答案