大型跟踪表的数据库设计

Question

我正在为祈祷者建立一个数据库。每天有5种祈祷方式。我有一张叫"Types"的桌子

id, name
1, Type 1
2, Type 2
3, Type 3
4, Type 4
5, Type 5

我希望每天保存each user与each prayer类型对应的记录。下面是我的tracking表的结构。

id, type_id (FK of Types), user_id (FK of user table), date (Y-m-d)

我有500K+用户，对于每个用户，每天最多可以有5条记录。这将以百万计。基本上，我想要创建一个优化的db结构，所以在执行查询时应该更快。

• 用户是登录者，他应该每天查看他/她的所有记录。
• 一年后，如果每天有5条记录，那么for 500 K用户将有912,500,000条记录。桌子将更大much。
• 不是的。用户数量也在日益增加。

那么，为了优化数据库，对于上面的跟踪表结构，最好的做法是什么呢？

Answer 1

老实说，9.12亿条记录似乎令人望而生畏，但这不是常规索引所不能处理的，特别是因为您的主要查询是只返回当前用户的prayer types，这是一个非常小的数据量。我已经管理了包含数百亿行的表，通过适当的索引，我们可以在运行时毫秒内在非常小的硬件上返回一小组数据。我敢肯定，数以千万计的桌子也会很好。注意，这使用的是Server而不是MySQL，但是索引在存储和获取数据方面的工作原理是相同的。

例如，以您当前的速度(没有用户增长)，您在大约100年内不会达到1000亿行。如果您能够实现Akina的建议，那么您也可以将数据量减少80%。所以1000亿行现在在100年内只变成200亿行。(换句话说，它经得起时间的考验。)如果您能够在X年之后存档数据，您还可以减轻表的数据负载。

最后，关于索引问题，我认为要支持只在给定日期为给定用户选择所有prayer type记录的查询，您应该在user_id、date上创建索引(如果不是默认情况下，则根据使用的MySQL引擎包括type_id )来实现最佳索引。

Answer 2

CREATE TABLE blah(
    user_id MEDIUMINT UNSIGNED NOT NULL,  -- 3 bytes
    date DATE NOT NULL,  -- 3 bytes
    prayers SET('1','2','3','4','5') NOT NULL,  -- 1 byte
    PRIMARY KEY(user_id, date)
) ENGINE=InnoDB;

将占用约30(包括开销)。

您可能需要其他索引，这取决于查询的内容。