代码之家 › 专栏 › 技术社区 › CCSab

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

analytics database-design database sql mysql

CCSab · 技术社区 · 12 年前

我正在努力找出为我正在编写的这个基于事件的分析系统建模模式的最佳方法。我主要关心的是以一种使查询简单快速的方式编写这篇文章。我也将使用MySQL。我将介绍一些需求,并提出一个可能的(但我认为很糟糕的)模式的大纲。

要求

跟踪事件(例如跟踪“APP_LAUNCH”事件的发生)
定义自定义事件
能够在>1个自定义财产(例如,获取“APP_VERSION”属性上分段的“APP_LAUNCH”的出现次数)
跟踪会话
根据时间戳范围执行查询

可能的建模

我遇到的主要问题是如何对分段和要执行的查询进行建模,以获得事件的总计数。

我最初的想法是定义一个EVENTS表,该表具有id、int count、timestamp、property(?)和EVENTTYPE的外键。EVENTTYPE具有属于通用事件类型的id、名称和附加信息。

例如,“APP_LAUNCH”事件在EVENTS表中有一个条目,该条目具有唯一的id、表示事件发生次数的计数、时间戳(不确定时间戳的内容)、属性或财产列表(例如“APP_VERSION”、“COUNTRY”等)以及名为“APP_LAUNCH”的EVENTTYPE的外键。

评论和问题

我确信这不是一个很好的建模方法,原因如下。这使得很难进行时间戳范围的查询(“时间x和y之间的APP_LAUNCHES数量”)。EVENTTYPE表并没有真正起到作用。最后,我甚至不确定如何为不同的分段执行查询。最后一个是我最担心的一个。

我将感谢任何帮助,帮助我正确建模,或为我提供有帮助的资源。

最后一个问题(可能是愚蠢的):为每个事件插入一行不好吗?例如,假设我的客户端库对我的API进行以下调用:

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

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

我意识到这里有很多问题,但我真的很感激任何帮助。谢谢

1 回复 | 直到 12 年前

Tomas 12 年前

我认为你的大多数担忧都是不必要的。回答一个又一个问题:

1) 最大的问题是自定义属性,每个事件都不同。为此,您必须使用 EAV (entity-attribute-value) 设计重要的问题是,这些属性可以有哪些类型?如果不止一个,例如字符串和整数,那么它就更复杂了。这种设计通常有两种类型:

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

因此,这些表格看起来像:

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"

这将选择APP_LAUNCH类型的所有事件,其中APPVERSION为>4和APP_NAME包含“Office”。

3) EVENTTYPE表可以达到一致性的目的,即您可以:

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

或者,您可以使用ID作为数字,并在EVENTTYPE表中具有事件名称-这节省了空间,并允许轻松重命名事件,但您需要在每个查询中加入该表(导致查询速度较慢)。取决于节省存储空间的优先级与较低的查询时间/简单性。

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

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

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

这完全取决于你!如果您需要每个这样的事件的时间戳和/或不同的参数,那么您可能应该为每个事件设置一行。如果有大量相同类型和参数的事件,您可能可以像大多数登录系统所做的那样:聚合发生在一行中的事件。如果你有这样的直觉,那么这可能是一条路要走。

6) “我没有足够的SQL经验,不知道这些查询在可能数十万个这样的条目上是如何执行的”

成百上千个这样的条目将被毫无问题地处理。当你达到百万时,你将不得不更多地考虑效率。

7) “当我希望客户端真正获得分析时,聚合表或内存缓存是否有助于缓解问题?”

当然,如果查询速度变慢并且需要快速响应,这也是一种解决方案。但是,您必须引入一些机制来定期刷新缓存。它过于复杂;也许最好考虑聚合输入上的事件,请参见5)。