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

我在价格/产品表的顶部创建了一个视图，该视图可生成统计数据，例如最近7天，15天和30天的平均值，均值和众数，并计算出当前价格与7、15和30天。

问题是，每当我运行此视图时，将花费近50秒钟来生成数据。 我看到了一些有关切换到计算表的评论，在该表中，当将新数据输入到表中时，计算将被更新，但是我对此表示怀疑，因为我要在一个特定的位置插入大约1000个价格点一天中的时间会影响表格中的所有计算。 计算表是否只更新已更新的行，否则会重新计算所有内容？ 我担心这可能导致的开销（内存不是服务器的问题）。

我已经将产品和价格表以及视图粘贴到了DBFiddle，在这里： https ://dbfiddle.uk/?rdbms = mariadb_10.2 & fiddle = 4cf594a85f950bed34f64d800601baa9

可以看到产品代码22141

为了给出一个想法，这些是视图执行的一些计算（也可以在小提琴中找到）： 

        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

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

    

===============>>#1 票数：2

“计算表”不是MySQL / MariaDB功能。 因此，我想您的意思是从原始数据派生的另一张表，在需要这些统计信息时使用。

您说表格“每天都在填充...”。 您是说要从头开始重新加载，还是要再添加800行？ “每天”指的是一天中的特定时间，还是全天都在进行。

您是否总是必须从视图中选择所有行，还是有时可以SELECT columns FROM view WHERE something = 'constant'; 这很重要，因为优化技术在全行情况和少数行情况之间有所不同。

您如何有效地解决这个问题？

  1. 您可以努力优化用于定义视图的查询，从而使其更快。 那很可能是个好方法。

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

  3. 您可以定义EVENT（计划的SQL作业）以执行以下操作。

    • 创建一个新的，空的，“计算的”表，其名称类似于tbl_new 。
    • 使用（慢速）视图插入所需的行。
    • 将鼠标悬停在桌子上，以便新桌子代替当前桌子，并保留几个旧桌子。 这将为您提供一个简短的窗口，其中不存在tbl 。
      • 如果存在则删除表tbl_old_2;
      • 重命名表tbl_old至tbl_old_2，tbl至tbl_old，tbl_new至tbl；

  ask by Lucas Neto translate from so

===============>>#2 票数：2

这是整个相关子查询的工作量，需要适当的索引。

对于查询返回的合理数量的行，相关子查询可以提供合理的性能。 但是，如果外部查询返回数千行，则将是子查询的数千次执行。

我倾向于避免对同一个表运行多个SELECT，以获取最后7天，过去15天，过去30天，然后重复该操作以获取AVG，重复该操作以获取MAX，再获取MIN。

取而代之的是，我倾向于使用条件聚合，以单次通过表格的方式获取30天，15天和7天所有时间段的所有统计数据AVG，MAX，MIN。

……停下来注意，视图可能会影响性能； 来自外部查询的谓词可能不会被推入视图查询。 我们没有看到整个视图定义在做什么，但是我怀疑我们可能会实现一大套。

考虑这样的查询： 

SELECT ...      , ROUND( ( n.mal / a.avg_07_day - 1)*100 ,2)     AS dif_7_dias      , ROUND( ( n.mal / a.avg_15_day - 1)*100 ,2)     AS dif_15_dias      , ROUND( ( n.mal / a.avg_30_day - 1)*100 ,2)     AS dif_30_dias      , ...   FROM vinhos   LEFT   JOIN ( SELECT h.codigowine               , AVG(IF( h.timestamp >= CURRENT_DATE + INTERVAL -30 DAY, h.preconormal, NULL)) AS avg_30_day               , MAX(IF( h.timestamp >= CURRENT_DATE + INTERVAL -30 DAY, h.preconormal, NULL)) AS max_30_day               , MIN(IF( h.timestamp >= CURRENT_DATE + INTERVAL -30 DAY, h.preconormal, NULL)) AS min_30_day               , AVG(IF( h.timestamp >= CURRENT_DATE + INTERVAL -15 DAY, h.preconormal, NULL)) AS avg_15_day                , MAX(IF( h.timestamp >= CURRENT_DATE + INTERVAL -15 DAY, h.preconormal, NULL)) AS max_15_day                , MIN(IF( h.timestamp >= CURRENT_DATE + INTERVAL -15 DAY, h.preconormal, NULL)) AS min_15_day                , AVG(IF( h.timestamp >= CURRENT_DATE + INTERVAL  -7 DAY, h.preconormal, NULL)) AS avg_07_day               , MAX(IF( h.timestamp >= CURRENT_DATE + INTERVAL  -7 DAY, h.preconormal, NULL)) AS max_07_day               , MIN(IF( h.timestamp >= CURRENT_DATE + INTERVAL  -7 DAY, h.preconormal, NULL)) AS min_07_day            FROM precos h           GROUP              BY h.codigowine          HAVING h.codigowine IS NOT NULL        ) a     ON a.codigowine = vinhos.codigowine    LEFT   JOIN ( SELECT s.codigowine               , MAX(s.precnormal) AS mal               , MIN(s.precnormal) AS mil            FROM precos s           WHERE s.timestamp >= CURRENT_DATE - INTERVAL 9 HOUR           GROUP               BY s.codigowine          HAVING s.codigowine IS NOT NULL        ) n     ON n.codigowine = vinhos.codigowine

考虑内联视图查询a 。

请注意，我们可以单独运行该SELECT，并获得返回的结果集，就像我们从表中返回结果一样。 我们预计这种通过引用的表做单通。 可能会有一些谓词（WHERE子句中的条件）将过滤我们的行，或使我们能够更好地利用索引。 如当前所写，该查询可以使用带有codigowine前导列的codigowine来避免（可能很昂贵）“使用filesort”操作来满足GROUP BY 。

我对查询-间隔9小时感到有些困惑。 在我看来，这些子查询可能会返回多个行。 没有LIMIT子句（也没有ORDER BY）...但是，考虑到除法运算，我们似乎期望一个值（标量）。

在不了解我们要达到的目标而又不了解规范的情况下，我把我的困惑包装了起来，并放入另一个内联视图n 。这不是我们要做的，而只是为了说明（再次）内联视图返回结果集。 无论我们试图从-INTERVAL 9 HOUR子查询中获取什么值，我都认为我们也可以将它们作为集合返回。

综上所述，我们现在可以回答所提出的问题：添加“计算表”。

如果我们不需要第二个结果，但可以使用缓存的统计信息，则需要将内联视图a的结果集具体化为表格，然后重新编写上面的查询以将内联视图a替换为对缓存表的引用。 

CREATE TABLE calc_stats_n_days ( codigowine <datatype> PRIMARY KEY , avg_30_day  DOUBLE , max_30_day  DOUBLE       , min_30_day  DOUBLE , avg_15_day  DOUBLE , ...

对于最初的人群... 

INSERT INTO calc_stats_n_days  ( codigowine, avg_30_day, maxg_30_day, min_30_day, avg_15_day, ... )          SELECT h.codigowine               , AVG(IF( h.timestamp >= CURRENT_DATE + INTERVAL -30 DAY, h.preconormal, NULL)) AS avg_30_day               , MAX(IF( h.timestamp >= CURRENT_DATE + INTERVAL -30 DAY, h.preconormal, NULL)) AS max_30_day               , MIN(IF( h.timestamp >= CURRENT_DATE + INTERVAL -30 DAY, h.preconormal, NULL)) AS min_30_day               , AVG(IF( h.timestamp >= CURRENT_DATE + INTERVAL -15 DAY, h.preconormal, NULL)) AS avg_15_day                , ...

对于正在进行的同步，我可能会创建一个临时表，用相同的查询填充它，然后在临时表和目标表之间进行同步。 也许是INSERT ... ON DUPLICATE KEY和DELETE反INSERT ... ON DUPLICATE KEY （以删除旧行）。

  ask by Lucas Neto translate from so

===============>>#3 票数：2

在考虑其他选项之前，请尝试使查询更有效。 从长远来看，这是有益的：即使最终移至计算表，您仍将利用更高效的刷新查询。

您的查询具有15-20个内联子查询，它们全部针对同一个依赖表（据我所读），并对同一列precos(preconormal) （最小值，最大值，平均值，最常出现的值）进行汇总计算。 每个度量标准在日期范围从9个小时到1个月不等的范围内多次计算。 因此： 

SELECT      codigowine,      nomevinho,      DATE(timestamp) AS data_adc,     -- ...      /* Medidas estatísticas para 7 dias - min, max, media e moda  */     ROUND(         (             SELECT MIN(preconormal)             FROM precos             WHERE                  codigowine = vinhos.codigowine                 AND timestamp >= CURRENT_DATE - INTERVAL 7 DAY         ),          2     ) AS min_7_dias,     ROUND(         (             SELECT MAX(preconormal)             FROM precos             WHERE                  codigowine = vinhos.codigowine                 AND timestamp >= CURRENT_DATE - INTERVAL 7 DAY         ),          2     ) AS max_7_dias,      -- ... and so on ...  FROM vinhos

似乎使用条件聚合一次完成所有计算可能会更有效： 

select      codigowine,     min(preconormal) min_30d      max(preconormal) max_30d,     avg(preconormal) avg_30d,     min(case when timestamp >= current_date - interval 15 day) min_15d,     max(case when timestamp >= current_date - interval 15 day) max_15d,     avg(case when timestamp >= current_date - interval 15 day) avg_15d,     min(case when timestamp >= current_date - interval 7  day) min_07d,     max(case when timestamp >= current_date - interval 7  day) max_07d,     avg(case when timestamp >= current_date - interval 7  day) avg_07d from precos where timestamp >= current_date - interval 30 day group by codigowine

为了提高性能，您需要在(codigowine, timestamp, preconormal)上建立索引。

然后，您可以将其与原始表连接： 

select     v.nomevinho,      date(v.timestamp) data_adc,     p.* from vinhos v inner join (     select          codigowine,         min(preconormal) min_30d          max(preconormal) max_30d,         avg(preconormal) avg_30d,         min(case when timestamp >= current_date - interval 15 day then preconormal end) min_15d,         max(case when timestamp >= current_date - interval 15 day then preconormal end) max_15d,         avg(case when timestamp >= current_date - interval 15 day then preconormal end) avg_15d,         min(case when timestamp >= current_date - interval 7  day then preconormal end) min_07d,         max(case when timestamp >= current_date - interval 7  day then preconormal end) max_07d,         avg(case when timestamp >= current_date - interval 7  day then preconormal end) avg_07d     from precos     where timestamp >= current_date - interval 30 day     group by codigowine          ) p on p.codigowine = v.codigowine

这应该是一个明智的基础查询。 要获取其他计算值（每个周期中出现最多的值，最新值），可以添加其他联接或使用内联查询。

完成：这是基本查询的另一个版本，在连接后聚合。 根据您的数据在两个表之间的分布方式，效率可能会提高，也可能不会更高（并且如果表vinhos存在重复的codigowine ，则效率将不相等）： 

select     v.nomevinho,      date(v.timestamp) data_adc,     p.codigowine,     date(v.timestamp) data_adc,     min(p.preconormal) min_30d      max(p.preconormal) max_30d,     avg(p.preconormal) avg_30d,     min(case when p.timestamp >= current_date - interval 15 day then p.preconormal end) min_15d,     max(case when p.timestamp >= current_date - interval 15 day then p.preconormal end) max_15d,     avg(case when p.timestamp >= current_date - interval 15 day then p.preconormal end) avg_15d,     min(case when p.timestamp >= current_date - interval 7  day then p.preconormal end) min_07d,     max(case when p.timestamp >= current_date - interval 7  day then p.preconormal end) max_07d,     avg(case when p.timestamp >= current_date - interval 7  day then p.preconormal end) avg_07d from vinhos v inner join precos p     on  p.codigowine = v.codigowine     and p.timestamp >= current_date - interval 30 day group by v.codigowine, v.nomevinho

  ask by Lucas Neto translate from so

===============>>#4 票数：1 已采纳

查看您的查询：尝试对其进行重构，以消除尽可能多的依赖子查询，而是联接到子查询。 消除那些依赖的子查询将带来巨大的性能差异。

确定模式是在数据集中查找详细记录以获取极值的应用。 如果您将此用作子查询 

    WITH freq AS (             SELECT COUNT(*) freq,                    ROUND(preconormal, 2) preconormal,                    codigowine               FROM precos               WHERE timestamp >= CURRENT_DATE - INTERVAL 7 DAY               GROUP BY  ROUND(preconormal, 2), codigowine         ),         most AS (            SELECT MAX(freq) freq,                   codigowine              FROM freq             GROUP BY codigowine        ),        mode AS (          SELECT GROUP_CONCAT(preconormal ORDER BY preconormal DESC) modeps,                 freq.codigowine            FROM freq            JOIN most ON freq.freq = most.freq           GROUP BY freq.codigowine        )        SELECT * FROM mode

您可以找到每个项目的最常价格。 第一个CTE freq获取价格及其频率。

第二个CTE， most ，查找最频繁的价格（或多个价​​格）的频率。

第三CTE mode ，使用JOIN从freq提取最频繁的价格。 它也使用GROUP_CONCAT（），因为可能有多种模式-价格最高。

对于您的统计信息，您可以执行以下操作： 

WITH s7 AS (   SELECT ROUND(MIN(preconormal), 2) minp,          ROUND(AVG(preconormal), 2) meanp,          ROUND(MAX(preconormal), 2) maxp,          codigowine     FROM precos    WHERE timestamp >= CURRENT_DATE - INTERVAL 7 DAY    GROUP BY codigowine ), s15 AS (   SELECT ROUND(MIN(preconormal), 2) minp,          ROUND(AVG(preconormal), 2) meanp,          ROUND(MAX(preconormal), 2) maxp,          codigowine     FROM precos    WHERE timestamp >= CURRENT_DATE - INTERVAL 15 DAY    GROUP BY codigowine ), s30 AS (   SELECT ROUND(MIN(preconormal), 2) minp,          ROUND(AVG(preconormal), 2) meanp,          ROUND(MAX(preconormal), 2) maxp,          codigowine     FROM precos    WHERE timestamp >= CURRENT_DATE - INTERVAL 30 DAY    GROUP BY codigowine ), m7 AS (    WITH freq AS (          SELECT COUNT(*) freq,                 ROUND(preconormal, 2) preconormal,                 codigowine            FROM precos            WHERE timestamp >= CURRENT_DATE - INTERVAL 7 DAY            GROUP BY  ROUND(preconormal, 2), codigowine      ),      most AS (         SELECT MAX(freq) freq,                codigowine           FROM freq          GROUP BY codigowine     ),     mode AS (       SELECT GROUP_CONCAT(preconormal ORDER BY preconormal DESC) modeps,              freq.codigowine         FROM freq         JOIN most ON freq.freq = most.freq        GROUP BY freq.codigowine     )     SELECT * FROM mode ) SELECT v.codigowine, v.nomevinho, DATE(timestamp) AS data_adc,        s7.minp min_7_dias, s7.maxp max_7_dias,  s7.meanp media_7_dias, m7.modeps moda_7_dias,        s15.minp min_15_dias, s15.maxp max_15_dias,  s15.meanp media_15_dias,         s30.minp min_30_dias, s30.maxp max_30_dias,  s30.meanp media_30_dias   FROM vinhos v   LEFT JOIN s7 ON v.codigowine = s7.codigowine   LEFT JOIN m7 ON v.codigowine = m7.codigowine   LEFT JOIN s15 ON v.codigowine = s15.codigowine   LEFT JOIN s30 ON v.codigowine = s30.codigowine

我将让您将模式设置为15天和30天。

这是相当查询。 您最好希望下一个从事此工作的人不要诅咒您的名字。 :-)

  ask by Lucas Neto translate from so

本文未有回复，本站智能推荐：