历史/审计表的设计建议
我需要随时跟踪许多项目及其状态。
示例
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 这样的数据库会因为支持分区查询而更好吗？
预先感谢您的任何想法！
I need to keep track of many items and their states throughout time.
Example
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
Right now, this is a huge table and I use MySQL's pseudo rank function to perform the queries.
However, it is very slow. The queries are unable to make use of the indexes since for any particular item it will have to find the item with rank = 1 before it can perform any filtering.
SELECT
item_sorted.*, IF(@prev <> item_sorted.item_id, @rownum := 1, @rownum := @rownum+1) AS rank,
@prev := item_sorted.item_id
FROM ...
What are your experiences in implementing such feature?
What is the database design that you would recommend?
Would a database like Oracle or T-SQL capable DBs be a lot better due to their support for partitioned queries?
Thanks in advance for any ideas!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
让我从一个问题开始：
这张表的主要目标是什么？向其中插入数据或从中检索数据？
两种可能性：
如果你想比较时间消耗，可以尝试安装PostgreSQL并查看结果。
您不需要使用 Oracle 或 MS-SQL，PostgreSQL 应该足够好。
我知道我的建议与数据分区无关，但是在工作中有一个类似的表，这就是我的做法，而且效果很好。
希望我能帮忙，
Let me start with a question :
What is the main goal of this table ? To insert data in it or to retrieve data from it?
Two possibilities :
If you want to compare time consumtion, try to install PostgreSQL and see the result.
You should not need to use Oracle or MS-SQL, PostgreSQL should be good enough.
I know that my recommendation has nothing to do with data-partitioning, but having a similar table at work this is how I do it and it works fine.
Hope I could help,