优化SELECT-query - index-use等

Question

有一个非常大的表，由8个字段组成(我知道，不精确)。

在我的应用程序中，我重复运行此SELECT：

SELECT d1, time, s1, s2 from Collection WHERE (d1 = 1) and (s1 = 1) and (time BETWEEN 5666300000 AND 566630700);

我使用不同的时间范围运行此SELECT。d1是一个包含200-300个不同值的字段，与s1相同。主键为d1、time、s1。

我正在寻找关于优化表结构和查询的技巧。问题是时间字段不是按升序/降序排列的。因此，这可能是一个需要一些时间的查询。我想知道如何索引我的时间域。那么，我是否必须更改我的查询？如果是那样的话，怎么做呢？

我的查询中有没有看不到的obv错误？这在我的应用程序中运行很慢。谢谢!

Answer 1

我建议按照time + d1 + s1的顺序构建聚集索引(主键)。这将确保按时间顺序物理存储数据，然后按d1和s1顺序存储

Answer 2

我不同意其他答案中提出的主键的顺序。

您的理想场景(对于精确的示例查询)是将所有相关记录放在一起。这将在您的数据上启用单次搜索。例如，使用(d1, s1, time)作为集群主键，您可以按如下方式存储数据...

 d1 | s1 | time 
----+----+------
  1 |  1 | 1234
  1 |  1 | 1235    \
  1 |  1 | 1236     SELECT * FROM table WHERE d1 = 1 AND s1 = 1 AND time BETWEEN 1235 AND 1237
  1 |  1 | 1237    /
  1 |  1 | 1238
  1 |  2 | 1234
  1 |  2 | 1235
  1 |  2 | 1236
  1 |  2 | 1237
  1 |  2 | 1238

如果像其他人建议的那样，将time作为聚集索引中的第一个字段，则无法在一个连续的块中获取所有数据。取而代之的是，对于每个单独的时间值，您都会得到一个单独的查找...

 time | d1 | s1
------+----+----
 1234 |  1 |  1     *Desired Row 1
 1234 |  1 |  2
 1235 |  1 |  1     *Desired Row 2
 1235 |  1 |  2
 1236 |  1 |  1     *Desired Row 3
 1236 |  1 |  2
 1237 |  1 |  1     *Desired Row 4
 1237 |  1 |  2
 1238 |  1 |  1     *Desired Row 5
 1238 |  1 |  2

这种结构实际上非常适合不同的查询...

SELECT * FROM yourTable WHERE time = 1234 AND d1 = 1 AND s2 BETWEEN 2 AND 3

这表明，没有一个通用的完美聚集索引。那么，既然只能有一个聚集索引，那么如何选择要进行聚集的内容呢？

这取决于您的数据和查询。对于每个查询，您需要查看将提取多少个不同的连续数据块。尝试最小化这些块的数量是一个非常好的想法。但是，维护数据的顺序也是如此，以便它适合您的GROUP BY或order by子句。进一步加入这个复合体。

对于您的示例查询，我建议的第一个索引确实是最好的。但不是针对您的所有问题。

此外，您还需要考虑碎片。数据存储在页中，您需要考虑插入数据的方式(考虑这一点时，请将更新视为delete和insert )。因为任何insert的时间值通常都比现有数据的时间值新，所以在聚集索引中使用time优先将减少碎片。

例如，假设每个页面只能容纳三行数据。上面建议的两个索引如下所示...

 d1 | s1 | time            time | d1 | s1 
----+----+------          ------+----+----
  1 |  1 | 1234 \          1234 |  1 |  1   \
  1 |  1 | 1235  Page 1    1234 |  1 |  2    Page 1
  1 |  1 | 1236 /          1235 |  1 |  1   /
----+----+------          ------+----+----
  1 |  1 | 1237 \          1235 |  1 |  2   \
  1 |  1 | 1238  Page 2    1236 |  1 |  1    Page 2
  1 |  2 | 1234 /          1236 |  1 |  2   /
----+----+------          ------+----+----
  1 |  2 | 1235 \          1237 |  1 |  1   \
  1 |  2 | 1236  Page 3    1237 |  1 |  2    Page 3
  1 |  2 | 1237 /          1238 |  1 |  1   /
----+----+------          ------+----+----
  1 |  2 | 1238 -Page 4    1238 |  1 |  2   -Page 4

现在，尝试插入d1 = 1, s1 = 1, time = 1239。

 d1 | s1 | time            time | d1 | s1 
----+----+------          ------+----+----
  1 |  1 | 1234 \          1234 |  1 |  1   \
  1 |  1 | 1235  Page 1    1234 |  1 |  2    Page 1
  1 |  1 | 1236 /          1235 |  1 |  1   /
----+----+------          ------+----+----
  1 |  1 | 1237 \          1235 |  1 |  2   \
  1 |  1 | 1238  Page 2    1236 |  1 |  1    Page 2
 *1 |  1 | 1239*/          1236 |  1 |  2   /
----+----+------          ------+----+----
  1 |  2 | 1234 -Page 3    1237 |  1 |  1   \
----+----+------           1237 |  1 |  2    Page 3
  1 |  2 | 1235 \          1238 |  1 |  1   /
  1 |  2 | 1236  Page 4   ------+----+---- 
  1 |  2 | 1237 /          1238 |  1 |  2   -Page 4
----+----+------           1239 |  1 |  1   /
  1 |  2 | 1238 -Page 5

左边的版本必须创建一个新页面。右侧的版本刚刚继续填充现有页面。

当出现碎片化时，通常会有一些维护计划可以修复碎片化。这通常是一个通宵的过程。

这一切都有点复杂，不是吗？嗯，有很多关于这个主题的书。

我通常不会太担心碎片化，直到它成为一个问题。但这真的是值得记住的事情。

Answer 3

首先，正如npe所说，你不应该使用时间作为主要。我认为在time - d1 - s1上添加主要索引。这样，你就有了时间作为主要的主索引，因此所有的中间结果都会非常快。只有到那时，d1和s1才会出现。此外，将d1和s1放在尽可能小的数据类型中。如果仅为1和0，则将其放入bool中，依此类推。这将加快检查速度。