php - 使用 MySQL 的物化 View (汇总表)的首选方法

Question

我正在开发一个我需要创建和维护的项目 汇总表 出于性能原因。我相信正确的术语是 物化 View .

我有两个主要原因这样做:

非规范化

我尽可能地对表格进行了标准化。所以在某些情况下，我必须加入许多表来提取数据。我们使用 MySQL Cluster，它在 JOIN 方面的性能非常差。

所以我需要创建可以运行更快 SELECT 的非规范化表。

汇总数据

例如，我有一个包含几百万条记录的 Transactions 表。交易来自不同的网站。应用程序需要生成一份报告，将显示每日或每月的交易计数，以及每个网站的总收入金额。我不希望报告脚本每次都计算这个，所以我需要生成一个汇总表，该表将按 [site,date] 进行分割。

这只是一个简单的例子。我需要生成和维护许多不同类型的汇总表。

过去，我通过编写几个 cron 脚本来更新每个汇总表来完成这些工作。但是在这个新项目中，我希望实现一个更优雅、更合适的解决方案。

我更喜欢基于 PHP 的解决方案，因为我不是服务器管理员，当我可以通过我的应用程序代码控制一切时，我感觉最舒服。

我考虑过的解决方案:

复制VIEW的

如果结果表可以表示为单个 SELECT 查询，我可以生成一个 VIEW。由于它们很慢，因此可以有一个 cronjob 将这个 VIEW 复制到一个真实的表中。

但是，其中一些 SELECT 查询可能非常慢，以至于即使对于 cronjobs 也是 Not Acceptable 。如果较旧的行甚至没有进行太多更新，那么重新创建整个汇总数据的效率就不是很高。

每个汇总表的自定义 Cronjobs

这是我以前使用过的解决方案，但现在我尽可能避免使用它。如果汇总表很多，维护起来会很麻烦。

MySQL 触发器

可以向主表添加触发器，以便每次有 INSERT、UPDATE 或 DELETE 时，汇总表都会相应地更新。

将没有 cronjobs，摘要将是实时的。但是，如果需要从头开始重建汇总表，则必须使用另一种解决方案(可能是上面的 #1)来完成。

使用 ORM 钩子(Hook)/触发器

我使用 Doctrine 作为我的 ORM。有一种方法可以添加事件监听器，这些事件监听器将触发 INSERT/UPDATE/DELETE 上的内容，进而可以更新汇总表。从某种意义上说，这个解决方案类似于上面的#3，但我将更好地控制这些触发器，因为它们将在 PHP 中实现。

实现注意事项:

完全重建

我想避免重建汇总表，以提高效率，并且只更新新数据。但是，万一出现问题，我需要能够使用主表上的现有数据从头开始重建汇总表。

忽略旧数据的更新/删除

一些摘要可以假设旧记录永远不会被更新或删除，但只会插入新记录。通过假设不需要检查旧数据的更新，汇总过程可以节省大量工作。

但这当然不适用于所有表。

记录日志

假设我无法访问或不想使用二进制 MySQL 日志。

为了汇总新数据，汇总过程只需要记住它汇总的最后一条记录的最后一个主键 ID。下次运行时，它可以总结该 id 之后的所有内容。但是，为了跟踪已更新/删除的旧记录，它需要另一个日志，以便它可以返回并重新汇总该数据。

我将不胜感激任何可以提供帮助的策略、建议或链接。谢谢!

Answer 1

Flexviews (http://flexvie.ws) 是一个基于 PHP/MySQL 的开源项目。 Flexviews 向 MySQL、使用 PHP 和存储过程添加了可增量刷新的物化 View (如 Oracle 中的物化 View )。

它包括 FlexCDC，一个基于 PHP 的更改数据捕获实用程序，它读取二进制日志，以及用于定义和维护 View 的 Flexviews MySQL 存储过程。

Flexviews 支持连接(仅限内部连接)和聚合，因此可用于创建汇总表。此外，您可以将 Flexviews 与 Mondrian(一个 ROLAP 服务器)聚合设计器结合使用来创建 ROLAP 工具可以自动使用的汇总表。

如果您无权访问日志(它可以远程读取它们，顺便说一句，因此您不需要服务器访问权限，但您确实需要 super 权限)，那么您可以使用 Flexviews 的“完整”刷新。这会自动在新表名下创建一个带有“CREATE TABLE ... AS SELECT”的新表。然后它使用 RENAME TABLE 将新表替换为新表，用 _old 后缀重命名旧表。最后它丢弃了旧表。这里的优点是创建 View 的 SQL 存储在数据库 (flexviews.mview) 中，并且可以通过一个简单的 API 调用来刷新，该 API 调用会自动执行交换过程。