mysql - 统计表的最佳分区

问题描述

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

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

约200M行+

我们有两种查询这个日期的方法——它总是在 WHERE 中有 productid（或多个 productID），然后也可以选择指定一个日期。大多数查询都会有两者。

我们允许用户查询通常的日期“桶”，例如今天/昨天/过去 7 天/上个月/过去 3 个月/今年/去年。他们还可以选择任意日期。

所以我在日期分区和productid分区之间折腾。我的想法是，如果我们一次查询多个 productID（使用 IN()），那么这可能意味着跨越多个分区。但是，由于大多数查询将在一年内进行，因此按日期分区是最好的吗？

由于大多数查询都是短期的（例如几天/几周），也许我们有类似的东西：

最近 3 个月最近 3-6 个月最近 6-12 个月然后是每年的水桶。

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

谢谢你的建议。

标签： mysqldatabase-performancedatabase-partitioning

首先确保数据类型尽可能小。会有十亿个productids吗？ 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。

对于 AND 子句 (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)

并在需要之前每晚尝试创建一个新的“月”分区。（不要预先构建大量分区。）

（注意：年+月分区是一次性任务。所有未来的分区都是“月”，我避免将月合并为年。在计算机或项目的生命周期内，您不会遇到任何分区限制.)

他们从不测试channel吗？他们从不这样做SUM(hits) .. GROUP BY吗？这些将引发更多讨论。

“参考地点”...

数据可能占用10GB？你有多少内存？的设置是innodb_buffer_pool_size什么？用户是否经常阅读“最近的”数据？答案交互以预测需要多少 I/O，从而预测查询的运行速度。

我上面推荐的索引和分区是针对

如果所有查询都是针对“最近”数据的，则 I/O 非常低。
即使偶尔有“旧”查询，I/O 也相当低。（听起来这就是你的情况。）
即使日期分散在所有时间，也会“工作”（以某种速度）。

您可能已经注意到我主要在productid. 这是影响：

2D 索引很棘手；PK需要一个维度先走；另一个可以通过分区。
如果有，比如说，10K 个产品，那么“插入”将有 10K 个“热点”。也就是说，buffer_pool 中基本上一直有 10K 个 16KB 块，准备接收下一次读取。只有 160MB，这无疑是 buffer_pool_size 的一小部分。因此没有用于插入的 I/O。

11个月的数据大概不到1GB？同样，对于所有“最近”查询，它可能会一直存在于 buffer_pool 中。（参考位置；“分区修剪”）。

mysql - 统计表的最佳分区

问题描述

解决方案

推荐阅读