将以下时态数据存储在数据库中的最佳方式

问题描述：

想象一下，我们有一组实体，每个实体都有其状态：空闲，忙或破碎。状态被指定为一天，例如，今天在2011-05-17，一个实体E1是免费的，明天在2011年5月18日它很忙。将以下时态数据存储在数据库中的最佳方式

需要在1000天内存储〜10^5个实体。哪种方法是最好的方法？

我想2个选择：

代表每一天都当作一个字符“0”，“1”或“2”，并存储为每一个实体的每一天1000个字
店字符串与实体的状态连续，即对于实体的1000行

对于这样的数据最重要的查询是：给定的开始日期和结束日期标识哪些实体是空闲的。

性能的优先级高于存储。

欢迎提出任何建议和意见。

如果实体是在天10和12免费的，但不是在第11天 - 它应该被认为是“第10和12之间的*”？ – tucuxi 2011-05-17 13:18:47

它必须明确定义，即如果它在第10天和第12天是空闲的，那么我们对第11天一无所知。然而，如果存储时间片段，那么它可以如此定义：[10,12]是免费的。但我觉得这种方式更复杂。 – Tim 2011-05-17 13:22:59

答

创建一个表来保存您的数据。使用ID，日期，实体名称和八个布尔字段创建表格。 SQL Server 2008给了我下表的代码：

CREATE TABLE [dbo].[EntityAvailability](
[EA_Id] [int] IDENTITY(1,1) NOT NULL, 
[EA_Date] [date] NOT NULL, 
[EA_Entity] [nchar](10) NOT NULL, 
[EA_IsAvailable] [bit] NOT NULL, 
[EA_IsUnAvailable] [bit] NOT NULL, 
[EA_IsBroken] [bit] NOT NULL, 
[EA_IsLost] [bit] NOT NULL, 
[EA_IsSpare1] [bit] NOT NULL, 
[EA_IsSpare2] [bit] NOT NULL, 
[EA_IsSpare3] [bit] NOT NULL, 
[EA_IsActive] [bit] NOT NULL, 
CONSTRAINT [IX_EntityAvailability_Id] UNIQUE NONCLUSTERED 
(
    [EA_Id] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 
END 
GO 

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EntityAvailability]') AND name = N'IXC_EntityAvailability_Date') 
CREATE CLUSTERED INDEX [IXC_EntityAvailability_Date] ON [dbo].[EntityAvailability] 
(
    [EA_Date] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
GO

日期聚集索引将执行最适合您的范围搜索。永远不允许没有日期范围的搜索，并且除了聚集索引之外不需要任何索引。布尔字段允许只使用一个字节的八种情况。该表的行大小为35个字节。 230行将适合页面。您表示您需要存储1000个日期的10^5个实体，即1亿个。一亿行将占用434,782 8K页或约3演出。

SSD上安装表，你可以走了。

答

取决于实体是否更经常是免费的，只是存储实体免费或不实际的日期。

假设你存储的日期时，该实体是不*，则搜索是从哪里开始日期< =日期日期和结束日期> =日期和任何行匹配，这意味着该实体是不*的那一段

好吧，实体可能会在未来有超过2个的状态，例如，免费的，忙坏 – Tim 2011-05-17 13:03:54

如果只有3然后为每个忙碌的一个表，并打破 - 否则添加标记为忙碌，破碎等日期表 – Mark 2011-05-17 13:19:03

答

它听起来你可能会走在正确的轨道上，我会建议，因为记录的数量和性能的重点，你保持架构尽可能非规范化。为了确定空闲或繁忙实体，您需要做的联接越少越好。

在这种情况下，连接可能与使用替代ID号码有关，而不是归一化。尽管如此，不要使用代理ID号作为州使用CHAR（1）和'F'，'B'和'X'“将是很好的建议。 – 2011-05-17 21:48:39

答

最好的方法是首先尝试更简单和更灵活的选项（即将每天存储在自己的行中），并且只在性能不理想时才设计出复杂的替代方法。避免过早优化。

10^8行对于目前商品服务器上的普通数据库并不是什么大不了的事情。把一个索引放在日期上，我敢打赌，范围查询（“给定开始日期和结束日期...”）将工作得很好。

的原因，我声称，这既是简单，不是存储1000个字符的字符串的想法更加灵活的有：

你必须在代码来处理这一点，该代码不会这很容易理解为查询包含日期和状态的DB记录的代码。
根据数据库引擎的不同，1000个字符串可能是存储在记录外部的斑点。这使得他们效率较低。
如果您突然需要2,000天而不是1,000天会发生什么？开始更新所有行和处理它们的代码？这不仅仅是改变你的查询。
当您接下来要求每日记录存储一些附加信息或需要更改粒度时（例如从几天移动到几小时）会发生什么？

同意 - 对索引列执行范围查询要比对计算数组逐一屏蔽10万个实体要快得多。不太紧凑的存储方式，但速度更快。我假设这个EntityStatus表只包含一个entity-id，一个日期和一个状态（free，broken，whatever）。 – tucuxi 2011-05-17 13:32:58

答

我会广泛去寻找一个Kimball Star Schema（http：//en.wikipedia。组织/维基/ Star_schema）配有三个表（初始）

FactEntity（FK kStatus，kDate）
DimStatus（PK kStatus）
DimDate（PK kDate）

这种类型的结构可以非常简单地加载（Dims first，然后是Fact（s）），并且查询也很简单。性能可以通过适当的索引进行优化。

这种设计的一大优势是，它是非常可扩展;如果你想增加日期范围，或者增加有效状态的数量，那么扩展是微不足道的。

其他尺寸可以合理地加入，例如， DimEntity可以有更丰富的信息，可以给出分类信息，迁移有趣的切片/切块你的实体。

DimDate通常通过添加DayNo，MonthNo，YearNo，DayOfWeek，WeekendFlag，WeekdayFlag，PublicHolidayFlag来丰富。这些允许进行一些非常有趣的分析。

由于@Elad问，如果添加基于时间的信息会有什么ahppen，那么这也可以通过在每个小时或分钟一个记录一个DimTime尺寸inforporated。

道歉我命名，因为我没有你的数据有很好的理解。考虑到更多的时间，我可以拿出一些更好的！

答

enter image description here

要获得一个约会免费的实体，您可以尝试：

select 
     e.EntityName 
    , s.StateName 
    , x.ValidFrom 
from EntityState as x 
join Entity  as e on e.EntityId = x.EntityId 
join State  as s on s.StateID = x.StateID 
where StateName = 'free' 
    and x.ValidFrom = (select max(z.ValidFrom) 
         from EntityState as z 
         where z.EntityID = x.EntityID 
         and z.ValidFrom <= your_date_here) 
;

注意：请确保您存储在EntityState表只是状态的变化。

将以下时态数据存储在数据库中的最佳方式

相关推荐