mysql 视图 union 慢,包含UNION的MySQL View不能很好地进行优化...换句话说，速度很慢！...

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;

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.

解决方案

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

...and:

SELECT x.*

FROM (SELECT col1, col2, col3

FROM TAB1

UNION ALL

SELECT col1, col2, col3

FROM TAB2) x

LIMIT 1

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

...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...