mysql - 历史/审计表的设计建议

Question

我需要随时跟踪许多项目及其状态。

例子

ItemId Location  DateTime           State
   1   Mall A    2010-02-03 07:00   on_sale
   1   Mall A    2010-02-20 08:22   sold
   2   Warehouse 2010-02-02 09:00   on_sale
   2   Transit   2010-03-02 16:20   transit
   2   Mall B    2010-03-03 10:10   on_sale
   2   Mall B    2010-03-12 12:11   sold

现在，这是一个巨大的表，我使用 MySQL 的伪排名函数来执行查询。

但是，它很慢。查询无法使用索引，因为对于任何特定项目，它必须先找到等级 = 1 的项目，然后才能执行任何过滤。

SELECT 
    item_sorted.*, IF(@prev <> item_sorted.item_id, @rownum := 1, @rownum := @rownum+1) AS rank,
    @prev := item_sorted.item_id
FROM ...

您在实现此类功能方面有何经验？您会推荐什么样的数据库设计？由于支持分区查询，像 Oracle 或支持 T-SQL 的数据库会更好吗？

提前感谢您的任何想法!

Answer 1

让我从一个问题开始:

这个表的主要目标是什么？向其中插入数据或从中检索数据？

两种可能性:

• 对于插入:我建议您将其规范化，您将有很多冗余。
• 对于检索:它是好的方式。我会为每组推荐一个代理键。

如果想比较耗时，可以尝试安装PostgreSQL，看看结果。您不需要使用 Oracle 或 MS-SQL，PostgreSQL 应该足够好。

我知道我的建议与数据分区无关，但在工作中有一个类似的表，这就是我的做法，而且效果很好。

希望能帮到你