mysql - 在 MySQL 中使用时间戳存储大量数据的最佳方法

Question

我该怎么办？

想象一下网球比赛。

运算符(operator)按下按钮( Action )“Ace”、“Fault”、“Winner”、“Unforced error”等我们有很多运算符(operator)，同时匹配。我们有很多来自用户的数据库请求(每分钟约 1000 个)。

存储 match_id、player、action、time_of_action 的最佳方式是什么？

1) 每个匹配项有 1 行的表:match_id、actions。 Action 、玩家、时间戳编码成 1 个字符串 #of player TINYINT id of action CHAR timestamp TIMESTAMP

示例:actions = "1A2014-11-28 09:01:21 2W2014-11-28 09:01:33 1F2014-11-28 09:01:49"

2) 一场比赛有多行的表格:id, match_id, player, action_id, current timestamp (id PRIMARY KEY)一天后将有大约 250K 行(每场比赛 300 场比赛 * 1 场比赛 40 场比赛 * 每天 20 场比赛)

什么更好:很多行和很多请求 SELECT player, action_id, timestamp FROM scores WHERE match_id = N要么相同数量的请求，更少的行 (/300 ) 但行中的数据更大？

对不起我的丑陋语言，我希望你能理解我，如果不明白，请告诉我

添加:我打算将它用于现场或赛后的比赛统计。用户打开费德勒-纳达尔比赛统计页面，每10-30秒刷新一次页面示例:http://www.wimbledon.com/en_GB/slamtracker/slamtracker.html?ts=1419259452680&ref=www.wimbledon.com/en_GB/slamtracker/index.html&syn=none&

Answer 1

我建议您创建名为

的引用表

 match    match_id, name, venue          A row for each distinct match
 player   player_id, name                A row for each distinct player 
 action   action_id, name                This is a codelist  1=Ace  2=Fault, etc.

这些表将是相对静态的。

然后，我建议您创建一个事件表，其中按以下顺序包含以下项目。

match_id
ts            (TIMESTAMP)
action_id
player_id

您应该按照我显示的顺序将所有这四个列包含在一个复合主键中。

每次您的记分员记录一个 Action 时，您都会在此表中插入一个新行。

当你想显示特定匹配的 Action 时，你可以这样做:

SELECT event.ts,
       action.name AS action,
       player.name AS player
  FROM event
  JOIN player ON event.player_id = player.player_id
  JOIN action ON event.action_id = action.action_id
 WHERE event.match_id = <<whatever match ID>> 
 ORDER BY event.match_id, event.ts

由于 event 表上复合主键的列顺序，即使您向该表插入大量新行，这种查询也会非常有效。

MySQL 就是为这种应用而生的。尽管如此，当您的站点开始接收大量用户流量时，您可能应该安排每隔几秒运行一次这些查询，缓存结果，并使用缓存的结果将信息发送给您的用户。

如果您想检索所有当前事件的比赛(即过去十分钟内发生的事件)的比赛 ID，您可以这样做。

SELECT DISTINCT match.id, match.name, match.venue
  FROM event
  JOIN match on event.match_id = match.match_id
 WHERE event.ts >= NOW() - INTERVAL 10 MINUTE

如果您经常需要进行此类查询，我建议您在(ts, match_id) 上创建一个额外的索引。