时间戳

首页 > 解决方案 > MySQL视图中的分组扫描所有分区

mysql - MySQL视图中的分组扫描所有分区

问题描述

我想在 MySQL 中创建一个视图，以允许数据分析用户轻松过滤大量数据，但是当我创建具有任何分组的视图时，会扫描整个视图，从而使视图在性能方面毫无用处。

一个简单的例子

值表 - 约 35 亿行，每月分区

SELECT
    Timestamp,
    DeviceId,
    SUM(Entry)
    FROM Value v
    WHERE DeviceId = 123456 AND Timestamp >= '2020-08-01'AND Timestamp <= '2020-08-30'
    GROUP BY Timestamp, DeviceId;

使用 EXPLAIN 我可以看到查询扫描 August 分区并使用 (DeviceId,Timestamp) 的主键在 63 毫秒内返回其值，选择类型为“简单”

当我创建此视图时，省略 WHERE 子句，EXPLAIN 命令显示使用时

SELECT * FROM vTest WHERE deviceid = 123456 AND Timestamp >= '2020-08-01'AND Timestamp <= '2020-08-30'

扫描所有分区，选择类型为 DERIVED，主键被标识为可能的键，但未使用。这使得查询“永远”。

如果我创建没有分组的视图，则不会发生此问题，并且视图使用正确的索引/键来扫描基础表。

是否可以在视图中使用分组并“将 where 子句传递给基础表”，或者视图的用户是否总是需要自己执行分组。

GCP 管理 MySQL 5.7.25

标签： mysqlview

解决方案

Mysql 可以使用两种算法来处理一个视图：

对于 MERGE，引用视图和视图定义的语句文本被合并，以便视图定义的部分替换语句的相应部分。

对于 TEMPTABLE，视图的结果被检索到临时表中，然后用于执行语句。

对于 UNDEFINED，MySQL 选择使用哪种算法。如果可能，它更喜欢 MERGE 而不是 TEMPTABLE，因为 MERGE 通常更有效，并且如果使用临时表，则视图无法更新。

根据mysql手册的视图部分限制：

索引可用于使用合并算法处理的视图。但是，使用 temptable 算法处理的视图无法利用其基础表上的索引（尽管可以在生成临时表期间使用索引）。

用于创建视图的 select 语句包含一个group by子句。根据 mysql 手册的第8.2.2.4 节优化派生表、视图引用和公用表表达式与合并或物化：

对于派生表、公用表表达式和视图引用，阻止合并的构造是相同的：

聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT() 等）

清楚的

通过...分组

拥有

限制

UNION 或 UNION ALL

选择列表中的子查询

分配给用户变量

仅引用文字值（在这种情况下，没有基础表）

由于该group by子句，该temptable算法用于视图。这会导致 mysql 首先将视图具体化为临时表，而不会从外部查询中下推过滤条件，从而导致您在说明中看到更广泛的扫描。过滤发生在临时表上，不能利用基础表上的索引。

如果 mysql 对视图使用合并或临时方法，您确实需要注意，因为视图的行为将在很大程度上取决于此选择。

推荐阅读