mysql 数据库分区对极端不平衡数据？

Question

200万客户2年产生20亿个订单，但根据历史数据，50%的订单属于前100名客户，所以客户id上的数据极不平衡。客户希望通过支付账户查询自己的历史订单(一个客户有多个支付账户，有些客户有数千个支付账户)。

困难在于头号客户总是在变化，您不知道哪个客户在未来一个月会有大量订单。

我需要存储 3 或 4 年的订单数据，并为客户提供订单搜索服务。我应该如何分区数据库和表？我的意思是，如果在客户的支付账户上使用哈希，一些数据库将拥有巨大的数据。我现在有35台服务器，每台服务器上有600G storge。

Answer 1

解决的关键:将 customer_id 作为 PRIMARY KEY 的 first 列全部( ？)具有该列的表。当然，在查询中包含 AND customer_id = 123。

我不明白“pay accounts”，但是如果有 acct_id，那么您可能需要

PRIMARY KEY(customer_id, acct_id, ...)

因为您可能已经有了 id .. AUTO_INCREMENT PRIMARY KEY，所以更改为

PRIMARY KEY(customer_id, acct_id, id)
INDEX(id)     -- sufficient to keep AUTO_INCREMENT happy

修改后的 PK 集群在大多数查询中可能被使用/搜索/等的行，从而加快了它们的速度。

“顶级”客户的行将大部分保留在 buffer_pool 中，从而减少对 I/O 的需求。当一位顾客越来越忙时，他的队伍就会挤掉一位不那么忙的顾客。这就是 LRU 缓存 的特性。也就是说，“谁在上面”的转移大部分是自动覆盖的。

“散列”不太可能有帮助。事实上它可能会造成伤害，因为它非常随机并且可能导致在缓存中跳来跳去。 (稍后会详细介绍。)

您会清除“旧”数据(4 年后)吗？如果是这样，这会带来另一个问题:DELETEing 从一个巨大的表中删除大量行的成本很高。与此相关的是通常获取哪些行的问题——也许只是“最近”的行？

如果您需要清除，那么PARTITION BY RANGE(TO_DAYS(...)) 将大大加快DELETE(通过将其变成DROP分区)。它可能对通常只查看“最近”行的问题有一些影响。

参见 this用于讨论时间序列数据。我建议安排 TO_DAYS() 落在月份边界上，从而有大约 50 个分区。

有了分区，我仍然会像上面讨论的那样设计 PK。但是，在大多数 WHERE 子句中包含 AND date > ... 会有所帮助，否则将搜索所有 50 个分区，这将是一个性能负担。 (很难说它是否是一个足够大的负担来对抗拥有 50 个分区。)

您现在有 35 台服务器，每台服务器上有 600G 存储:您是在谈论分片还是复制？如果是Replication，是指一个Master和34个readonly Slave吗？

如果按 Customer_id 分片，

• 构建一个强大的脚本来将客户从一个分片转移到另一个分片。这将是您完成许多管理任务的关键:卸载重载的分片；升级硬件/软件；添加另一个分片；等
• 这不仅需要一个“散列”，还需要一个字典来查找给定的 customer_id 所在的位置。我喜欢这样的组合:例如，哈希到 12 位 (0..4095)，然后查找给定客户所在的 35 个(截至今天)分片中的哪一个。