stats表的最佳分区

Question

我们有一个大型的统计表，每天记录每件产品的点击量。

   date      | datetime
   productid | int
   hits      | int
   channel   | enum

约200米rows+

我们有两种查询此日期的方法--它总是在WHERE (或多个productid )中有productid，然后也可以选择指定日期。大多数查询都有。

我们允许用户查询通常的日期‘桶’，如今天/昨天/最后7天/上个月/最后3个月/今年/去年。他们也可以选择任意的日期。

因此，我在日期分区与productid之间进行切换。我的想法是，如果我们同时查询多个产品if (使用IN())，那么这可能意味着跨越多个分区。但是，由于大多数查询将在一年内进行，按日期进行分区将是最好的吗？

由于大多数查询的时间都很短(如天/周)，也许我们有如下内容：

最后三个月，最后3-6个月，6-12个月，然后每年的桶。

我们的数据可以追溯到2005年。

谢谢你的建议。

Answer 1

首先要确保数据类型尽可能小。会有10亿个产品吗？INT是4个字节。MEDIUMINT UNSIGNED是3个字节，允许值在0到1600万之间。等。

你有这些的组合吗？

AND productid = 123  (or, equivalently, productid IN (123))  -- (1)

AND productid IN (234,345,456)  -- (2)

AND date >= '2020...'
AND date <  '2020...' + INTERVAL 7 DAY    -- (3)

您的“上个月”速记可以很容易地转换成一个日期范围与开始日期和那个+ INTERVAL 1 MONTH。

For和子句(1)或(1和3)或(2)：即使没有分区，PRIMARY KEY(productid, date)也是最优的。如果那对是唯一的，那就去做。(听起来是这样。)

( 3 )和(2和3)比较棘手。

(3)需要INDEX(date)

你需要删除“旧”的统计数据吗？如果是这样，那么强烈建议使用PARTITION BY RANGE(date)。它允许您DROP PARTITION (非常快)而不是DELETE。请参阅http://mysql.rjweb.org/doc.php/partitionmaint

通过分区，您可以获得(2和3)的一些二维帮助。或者优化器可能足够聪明，可以在表中跳过我推荐的PK。

我会在2020年之前每年建一次桶，然后从2020年1月开始每月一次。将来我也不会把每月合并成每年一次，因为这样做“太多了”。分区：

1  pre-2005 (empty) (see link for reason)
15 2005..2019  (Thanks for providing the '2005')
11(so far)  2020, jan..nov
1  "future" (see link)
(28 currently, but growing)

并在需要之前，每晚尝试创建一个新的“月”分区。(不要预先构建许多分区。)

(注意: years+months分区是一个一次性任务。所有未来的分区都是“月份”，我避免将几个月合并成几年。在计算机或项目的生命周期内，您将不会对分区设置任何限制。)

他们从来不测试channel吗？他们从来不做SUM(hits) .. GROUP BY吗？这将导致更多的讨论。

“参考地点”..。

数据可能占用10 The？你有多少内存？innodb_buffer_pool_size的设置是什么？用户通常读“最近的”数据吗?答案交互预测需要多少I/O，因此查询运行得有多快。

上面我推荐的索引和分区的目标是

如果所有查询都用于“最近”data.

• Reasonably低I/O，即使偶尔会有“旧”查询，则
• 非常低I/O。(听起来这就是你的情况。)
• 将“工作”(以某种速度)，即使日期在所有时间都是分散的。

您可能已经注意到，我主要集中在productid上。影响如下：

partitioning.

• If的
• 2D索引很棘手；一个维度首先需要在PK中进行；另一个维度可以是通过
• (例如，10K产品)，然后“插入”将有10K“热点”。也就是说，buffer_pool中基本上一直都有10K 16 of块，随时准备接收下一次读取。这仅仅是160 of，无疑是buffer_pool_size的一小部分。因此，插入没有I/O。

。

11个月的数据可能还不到1GB？同样，对于所有“最近”的查询，这很可能会留在buffer_pool中。(参考地点；“分区剪枝”)。