包含UNION的MySQL View不能很好地进行优化...换句话说，速度很慢！ [英] MySQL View containing UNION does not optimize well...In other words SLOW!

问题描述

我有一个包含UNION ALL的视图.例如:

I have a view containing a UNION ALL. For example:

CRATE VIEW myView as
(SELECT col1, col2, col3
 FROM tab1)
UNION ALL
(SELECT col1, col2, col3
 FROM tab2)

这些是大表，每个表包含数千万行.如果我写:

These are large tables containing 10s of millions of rows each. If I write:

SELECT * 
FROM myView
LIMIT 1;

与其立即执行相反，它基本上不会像针对此视图编写的其他查询一样返回.如果在针对单个基础表的查询中使用LIMIT，则它是立即的.我在基础表上有索引.似乎MySQL会在应用任何过滤条件之前为该视图创建整个聚合数据集(视图中的查询).疯了吧.这是MySQL针对视图优化查询的方式吗?顺便说一句，我什至不能对视图运行一个解释计划，因为它永远不会返回.

instead of being immediate, it basically never returns as do other queries written against this view. If I use the LIMIT in a query against the individual underlying tables, it is immediate. I have indexes on the underlying tables. It seems like MySQL is creating the entire aggregated data set (queries within the view) for the view before applying any filtration criteria. This is insane. Is this the way MySQL optimizes queries against views? By the way, I can't even run an explain plan against the view because it never returns.

推荐答案

您遇到的行为是如何在每个数据库上处理非实例化视图. MySQL不支持实体化视图，它的视图支持甚至无法与竞争对手媲美...

The behavior you're experiencing is how non-materialized views are handled on every database. MySQL doesn't support materialized views, and it's view support isn't even on par with competitors...

非物化视图只是其封装的查询的简写形式/宏/变量-使用之间没有区别

A non-materialized view is just a shorthand/macro/variable for the query it encapsulates - there's no difference between using:

SELECT * 
  FROM myView
 LIMIT 1

...并且:

SELECT x.*
  FROM (SELECT col1, col2, col3
          FROM TAB1
        UNION ALL
        SELECT col1, col2, col3
          FROM TAB2) x
 LIMIT 1

如果没有ORDER BY，最好是 ，您将基于查询的插入获得第一行，那么您可能正在运行:

Without an ORDER BY, at best you're going to get the first row based on insertion for your query, you might as well be running:

SELECT col1, col2, col3
  FROM TAB1
 LIMIT 1

...因为由于UNIONed语句返回的记录顺序，不太可能从TAB2中提取记录.然后就是要处理数以千万计的记录中的十个问题……

...because it's unlikely to pull records from TAB2 due to the order of records returned by a UNIONed statement. Then there's the matter of dealing with 10's of millions of records...

这篇关于包含UNION的MySQL View不能很好地进行优化...换句话说，速度很慢！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！