MySQL数据库中历史数据的最佳做法 [英] Best practices with historical data in MySQL database

问题描述

最近，我考虑了将历史数据存储在MySQL数据库中的最佳做法.现在，每个可版本控制的表都有两列-valid_from和valid_to，均为DATETIME类型.具有当前数据的记录用其创建日期填充了valid_from.当我更新此行时，我在valid_to中填入更新日期，并用与上一行中的valid_to相同的valid_from添加新记录-简单的东西.但是我知道表很快就会很大，因此获取数据可能会很慢.
我想知道您是否有存储历史数据的任何做法?

Recently I think about the best practices with storing historical data in MySQL database. For now, each versionable table has two columns - valid_from and valid_to, both DATETIME type. Records with current data has valid_from filled with its creation day. When I update this row, I fill valid_to with update date and add new record with valid_from the same as valid_to in previous row - easy stuff. But I know that table will be enormous very quick so fetching data can be very slow.
I'd like to know if you have any practices with storing historical data?

推荐答案

担心大"表和性能是一个常见的错误.如果您可以使用索引来访问数据，那么拥有1000000条记录中的1000条就没有关系-至少可以测量.您提到的设计是常用的.这是一个很棒的设计，其中时间是业务逻辑的关键部分.

It's a common mistake to worry about "large" tables and performance. If you can use indexes to access your data, it doesn't really matter if you have 1000 of 1000000 records - at least not so as you'd be able to measure. The design you mention is commonly used; it's a great design where time is a key part of the business logic.

例如，如果您想知道客户下订单时某件商品的价格，可以在valid_from< order_date和valid_until为null或> order_date是迄今为止最简单的解决方案.

For instance, if you want to know what the price of an item was at the point when the client placed the order, being able to search product records where valid_from < order_date and valid_until is either null or > order_date is by far the easiest solution.

并非总是如此-如果出于存档目的保留数据，则创建存档表可能更有意义.但是，您必须确保时间不是业务逻辑的一部分，否则搜索多个表的痛苦将是巨大的-想象每次必须搜索产品表或product_archive表您想了解在下订单时产品的价格.

This isn't always the case - if you're keeping the data around just for archive purposes, it may make more sense to create archive tables. However, you have to be sure that time is really not part of the business logic, otherwise the pain of searching multiple tables will be significant - imagine having to search either the product table OR the product_archive table every time you want to find out about the price of a product at the point the order was placed.

这篇关于MySQL数据库中历史数据的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！