mysql - 使用生成大量计算统计信息的 MariaDB View - 如何移动到计算表？

Question

我目前有一个 MariaDB 数据库，每天都会填充不同的产品(大约 800 种)，并且还会获取这些产品的价格更新。

我在价格/产品表的顶部创建了一个 View ，它生成过去 7 天、15 天和 30 天的平均值、均值和众数等统计数据，并计算今天的价格与 7 天的平均值之间的差异、15 天和 30 天。

问题是，每当我运行此 View 时，生成数据都需要将近 50 秒。我看到一些关于切换到计算表的评论，当新数据输入到表中时计算将更新，但是我对此持怀疑态度，因为我在一个特定的位置插入大约 1000 个价格点一天中的时间将影响表格中的所有计算。计算表是只更新已更新的行，还是会重新计算所有内容？我担心这可能导致的开销(内存不是服务器的问题)。

我已将产品和价格表以及 View 粘贴到 DBFiddle，此处:https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=4cf594a85f950bed34f64d800601baa9

产品代码22141可以看到计算结果

只是为了给出一个想法，这些是 View 完成的一些计算(也可以在 fiddle 上使用):

        ROUND((((SELECT preconormal
        FROM precos
        WHERE codigowine = vinhos.codigowine
            AND timestamp >= CURRENT_DATE - INTERVAL 9 HOUR) / (SELECT AVG(preconormal)
        FROM precos
        WHERE codigowine = vinhos.codigowine
            AND timestamp >= CURRENT_DATE - INTERVAL 7 DAY) - 1) * 100), 2) as dif_7_dias,
        ROUND((((SELECT preconormal
        FROM precos
        WHERE codigowine = vinhos.codigowine
            AND timestamp >= CURRENT_DATE - INTERVAL 9 HOUR) / (SELECT AVG(preconormal)
        FROM precos
        WHERE codigowine = vinhos.codigowine
            AND timestamp >= CURRENT_DATE - INTERVAL 15 DAY) - 1) * 100), 2) as dif_15_dias,
        ROUND((((SELECT preconormal
        FROM precos
        WHERE codigowine = vinhos.codigowine
            AND timestamp >= CURRENT_DATE - INTERVAL 9 HOUR) / (SELECT AVG(preconormal)
        FROM precos
        WHERE codigowine = vinhos.codigowine
            AND timestamp >= CURRENT_DATE - INTERVAL 30 DAY) - 1) * 100), 2) as dif_30_dias

如果切换到计算表，是否有最佳方法？

Answer 1

“计算表”不是 MySQL/MariaDB 的功能。所以我猜你的意思是从你的原始数据派生的另一个表，你在需要这些统计数据时使用。

您说该表“每天都有人……”。你是说它是从头开始重新加载的，还是说又添加了 800 行？ “每天”是指一天中的特定时间，还是一整天都在进行。

您是否总是必须从 View 中选择所有行，或者您有时是否可以SELECT columns FROM view WHERE something = 'constant';' 这很重要，因为优化技术在所有行之间是不同的案例和少行案例。

如何有效地处理这个问题？

1. 您可以优化用于定义 View 的查询，使其更快。这很可能是一个好方法。

2. MariaDB 有一种称为持久计算列的列。这些是在插入或更新行时计算的。然后它们可供快速引用。但是它们有局限性；它们不能用子查询定义。

3. 您可以定义一个 EVENT(计划的 SQL 作业)来执行以下操作。

   • 创建一个名为 tbl_new 的新的空“计算”表。
   • 使用您的(慢速) View 插入所需的行。
   • 翻转你的 table ，让新的 table 取代当前的 table ，而你保留几张旧的。这将为您提供一个简短的窗口，其中 tbl 不存在。
     • 如果存在 tbl_old_2 则删除表；
     • 将表 tbl_old 重命名为 tbl_old_2，将 tbl 重命名为 tbl_old，将 tbl_new 重命名为 tbl；