gpt4 book ai didi

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

转载 作者:可可西里 更新时间:2023-11-01 06:37:40 25 4
gpt4 key购买 nike

我正在开发一个我需要创建和维护的项目 汇总表 出于性能原因。我相信正确的术语是 物化 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 之后的所有内容。但是,为了跟踪已更新/删除的旧记录,它需要另一个日志,以便它可以返回并重新汇总该数据。


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

    最佳答案

    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 调用会自动执行交换过程。

    关于php - 使用 MySQL 的物化 View (汇总表)的首选方法,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4150288/

    25 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com