mysql - 每天数百万新行的数据库架构

Question

我需要为大量网站实现定制开发的网络分析服务。这里的关键实体是:

• 网站
• 访客

每个唯一身份访问者在数据库中都有一行，其中包含着陆页、一天中的时间、操作系统、浏览器、引荐来源网址、IP 等信息。

我需要对该数据库进行聚合查询，例如“计算所有使用 Windows 作为操作系统并来自 Bing.com 的访问者”

我有数百个网站需要跟踪，这些网站的访问者数量从每天几百到几百万不等。总的来说，我预计这个数据库每天会增长大约一百万行。

我的问题是:

1) MySQL 是适合此目的的数据库吗？

2) 什么是好的架构？我正在考虑为每个网站创建一个新表。或者，如果现有表中的行数超过 100 万(我的假设是否正确)，也许从单个表开始，然后(每天)生成一个新表。我唯一担心的是，如果表变得太大，SQL 查询会变得非常慢。那么，我应该在每个表中存储的最大行数是多少？此外，MySQL 可以处理的表数是否有限制。

3) 对数百万行进行聚合查询是否可取？我准备等待几秒钟以获取此类查询的结果。这是一个好的做法还是有任何其他方法来进行聚合查询？

简而言之，我正在尝试设计一种大型数据仓库类型的设置，这种设置会很繁重。如果您知道任何已发表的案例研究或报告，那就太好了!

Answer 1

如果您谈论的是大量数据，请查看 MySQL partitioning .对于这些表，按数据/时间进行分区肯定会提高性能。有一篇关于分区的不错的文章 here .

看看创建两个独立的数据库:一个用于写入的所有原始数据，索引最少；第二个用于使用汇总值进行报告；使用批处理从原始数据数据库更新报告数据库，或使用复制为您完成。

编辑

如果您想真正巧妙地处理汇总报告，请创建一组汇总表(“今天”、“本周至今”、“本月至今”、“按年”)。每天或“实时”从原始数据汇总到“今天”；每晚从“按天”汇总到“迄今为止的一周”；每周从“周至今”到“月至今”等。执行查询时，为您感兴趣的日期范围加入(UNION)适当的表。

编辑#2

我们不是每个客户端一个表，而是每个客户端使用一个数据库架构。根据客户端的大小，我们可能在单个数据库实例中有多个模式，或者每个客户端有一个专用的数据库实例。我们为原始数据收集和每个客户的聚合/报告使用单独的模式。我们运行多个数据库服务器，将每个服务器限制为单个数据库实例。为实现弹性，数据库在多台服务器之间复制并进行负载平衡以提高性能。