oracle中大表的设计模式

Question

我有一个用户表、事务表和 user_transaction 表。用户数量约为 75,000 个，应用程序中可能的唯一交易数量约为（交易表中的行数在 1 到 3 百万之间）。 user_transaction 是上面两个表的连接，存储用户在什么日期和时间执行了哪些事务。所以这个表对于 1 年的数据来说将是巨大的（我们将从表中清除活动数据并在 1 年后将其存档）年）。我们预计计数约为 50-6000 万行。这将是今年年底的最终数据大小。

我想说平均大小约为 3000 万条记录。 此外，夜间导入作业会更新所有这些表，这是在这些表中完成插入时的唯一部分，我们仅从我们的应用程序访问数据（使用选择查询）。

设计联接表的最佳方法是什么，以便更快地从庞大的事务表中进行检索？我们在表中添加了许多字段来对其进行非规范化并减少联接，并且几乎所有数据都仅在 transaction 和 user_transaction 表中可用。

如果我们想对表进行分区，我们该如何进行分区呢？该应用程序用于最频繁地查询较新的数据。

我们正在考虑按月对事务表进行分区，这样我们每个月就有 1 个表。

我们考虑的其他选项是每周 1 天每个表有 7 个表，但这大大增加了查询的复杂性，考虑到我们正在使用休眠。

我们如何设计大约6000万的巨大表格

按要求提供更多详细信息：
我必须根据模式制作一个图表，同时这里还有一些更多信息：关系并不复杂，大约有 4 个表：用户、事务、users_transaction、资源表。 user_transaction 是包含所有其他三个表 id 的连接表，这将是一个巨大的表，因为它在每个 id 上都有单独的条目，并且基于时间戳也有单独的条目。
目前该应用程序的用户数量非常少，大约小于 20。 （但将来可能会增长）。
表的主要消费者是：
1) 每周自我审核报告以电子邮件形式发送，其中包含这些表格中过去一周的用户活动详细信息。这些将（最终）发送给大约 75,000 个用户，生成报告并为 1 个用户发送电子邮件目前需要大约 1 分钟（试点阶段测试）。我们需要认真提高这方面的性能，例如每封电子邮件的时间少于 5 秒。这是一个在夜间运行的后端作业（最多应该消耗 3-4 小时）
2) 包含图表的仪表板，显示这些表格中交易的汇总视图。这些查询根据日期范围内的各个字段运行和汇总数据。 因此，如果所有其他字段都相同（用户 ID、资源 ID、资源事件 ID、位置），我们计划总结存储每天计数（不包括时间）的 user_transactions 表。
并根据月份对这些汇总表进行分区。 （每月一份）
需要注意的事项：该解决方案应该适用于所有数据库（MySQL、DB2 等），而不仅仅是 Oracle。

问候， 普里扬克·德瓦卡

Answer 1

好的，首先要说的是第一件事。

1. 按照 Oracle 标准，包含 3000 万行的表并不算大。
2. 说您有 75,000 个用户意味着数据库不管理您的用户登录，并且可能有一些角色正在处理数据库。

审计...

Oracle 具有非常强大的审计功能，因此在尝试推出自己的审计功能之前应该先研究一下这些功能。

如果您确实想推出自己的产品，那么用户和交易之间存在一对多关系。现在，我在这里非常宽松地使用术语事务，因为您似乎想要做的是记录用户 X 执行某些操作来修改表或表中的行。

最简单的做法是使用任何前端代码来插入到该表中，例如：

插入审计表（用户ID，操作）值（'fred'，'更新表付款并将某些列旧值更改为新值');

我将创建一个 userID 和时间戳的复合索引，以便可以将这两列作为单个实体进行查询。该表看起来像这样：

create table user_audit as 
(
user_id number,
action_timestamp systimestamp,
db_action clob
)

CREATE INDEX idx_user_audit_ia ON  user_audit (user_id,action_timstamp);

复合索引的作用是创建两个键的几乎散列，并且当您按这两列查询时它们非常快。

这个单一表的删除和插入速度将非常快。您可以通过以下方式使其更快：

• 没有主键约束。
• 关闭该表或其所在表空间的日志记录。

• 没有返回用户表的 FK（这确实毫无意义）。

• 如果数据库计算机上有足够的内存，请将其设置为保留缓存缓冲区，但仅如果您有足够的内存，否则您会将服务器放入容器中。

• 如果您选择分区，请仅在仔细阅读并理解 Oracle 上的分区后选择您的方法。

• 定义表空间时，请确保它是BIG TABLE，因为这将确保您不会超出大小限制（至少在 Linux 上）一个文件。

至于您处理的其余数据库将有各自的调整问题，因此每个都是一组一次性条件，适合一个数据库引擎，但不适合另一个。

始终记住 Unix 座右铭，做一件事，并做好。