基于事件分析的数据库模式设计

Question

我正在试图找到最好的方法来为我正在编写的基于事件的分析系统建模模式。我主要关心的是以一种使查询变得简单和快速的方式来编写它。我也将使用MySQL。我将研究一些需求，并给出一个可能的(但我认为是糟糕的)模式的大纲。

要求

• 径赛项目(例如，跟踪"APP_LAUNCH“事件的发生)
• 定义自定义事件
• 能够在>1个自定义属性上分割事件(例如，在"APP_LAUNCH“属性上分割"APP_VERSION”)
• 轨道会议
• 根据时间戳范围执行查询

可能建模

我面临的主要问题是如何建模分段和要执行的查询以获得事件的总体计数。

我最初的想法是定义一个事件表，其中包含id、int计数、时间戳、属性(?)和EVENTTYPE的外键。EVENTTYPE具有属于一般事件类型的id、名称和其他信息。

例如，"APP_LAUNCH“事件将在EVENTS表中有一个带有唯一id的条目，计数表示事件发生的次数，时间戳(不确定标记在什么上)，以及属性或属性列表(例如，"APP_VERSION“、”国家“等)和一个名为"APP_LAUNCH“的EVENTTYPE的外键。

评论和问题

我很确定这不是一个很好的方式来建模，因为以下原因。这使得执行时间戳范围查询(“时间x和y之间的APP_LAUNCHES数”)变得困难。EVENTTYPE表没有真正的用途。最后，我不确定如何对不同的分段执行查询。最后一个是我最担心的。

我希望有任何帮助，帮助正确建模，或指出我的资源，将有助于。

最后一个问题(可能是愚蠢的)：为每个事件插入一行会不会很糟糕？例如，假设我的客户端库对我的API进行了以下调用：

track("APP_LAUNCH", {count: 4, segmentation: {"APP_VERSION": 1.0}})

我将如何将其存储在表中(这显然与模式设计密切相关)？简单地为这些调用中的每一个插入一行是否不好，其中可能有大量的调用？我的直觉反应是，我真正感兴趣的主要是总体的汇总计数。我没有足够的SQL经验来了解这些查询是如何在这些条目中执行的。当我希望客户端实际获得分析时，聚合表或内存缓存会帮助缓解问题吗？

我知道这里有很多问题，但我真的很感激你的帮助。谢谢!

Answer 1

我认为你的大部分担忧都是不必要的。接二连三地回答你的问题：

1)最大的问题是定制属性，每个事件都不同。为此，您必须使用实体-属性值设计。重要的问题是-这些属性可以有哪些类型？如果不止一个，例如字符串和整数，那么它就更复杂了。这类设计一般有两种：

• 对所有类型的值使用一个表和一个列，并将所有内容转换为字符串(非可伸缩解决方案)。
• 对于每种数据类型都有单独的表(非常可伸缩，我会这样做)

所以，这些桌子看起来应该是：

Events             EventId int,  EventTypeId varchar,   TS timestamp
EventAttrValueInt  EventId int,  AttrName varchar,  Value int
EventAttrValueChar EventId int,  AttrName varchar,  Value varchar

2)，你所说的分段是什么意思？查询事件的各种参数？在上面提到的EAV设计中，您可以这样做：

select * 
from Events 
  join EventAttrValueInt  on Id = EventId and AttrName = 'APPVERSION' and Value > 4
  join EventAttrValueChar on Id = EventId and AttrName = 'APP_NAME' 
                                          and Value like "%Office%"
where EventTypeId = "APP_LAUNCH"

这将选择APPVERSION >4且APP_LAUNCH包含"Office“的所有APP_NAME类型的事件。

3) EVENTTYPE表可以达到一致性的目的，即可以：

table EVENTS (.... EVENTTYPE_ID varchar - foreign key to EVENTTYPE ...)
table EVENTTYPE (EVENTTYPE_ID varchar)

或者，您可以使用ID作为number，并在EVENTTYPE表中使用事件名称--这样可以节省空间，并且可以轻松地重新命名事件，但是您需要在每个查询中加入这个表(导致查询速度稍微慢一些)。取决于节省存储空间的优先级与较低的查询时间/简单性。

4)在您的设计中，时间戳范围查询实际上非常简单：

select * 
from EVENTS
where EVENTTYPE_ID = "APP_LAUNCH" and TIMESTAMP > '2013-11-1'

5)“为每个事件插入一行不好吗？”

这完全取决于你！如果您需要每个这样的事件的时间戳和/或不同的参数，那么很可能每个事件都有一行。如果有大量相同类型和参数的事件，您可能可以执行大多数loging系统所做的工作:将发生在一行中的事件聚合起来。如果你有这样的直觉，那么这可能是一条路。

6)“我没有足够的经验来了解这些查询是如何在这些条目中执行的”

将无问题地处理数百或数千个这样的条目。当你到达1毫升时，你将不得不对效率考虑得更多。

7)“当我希望客户端真正获得分析时，聚合表或内存缓存会帮助缓解问题吗？”

当然，这也是一个解决方案，如果查询变得缓慢，您需要快速响应。但是，您必须引入某种机制来定期刷新缓存。它过于复杂；也许最好考虑将事件聚合到输入中，参见5)。