Mysql - “EXPLAIN SELECT"从 VIEW 中查看主表的所有行 [英] Mysql - "EXPLAIN SELECT" from a VIEW is looking in all rows of the main table

问题描述

我有 2 张桌子:

• t_cities (idCity, idCountry, name, population)
• t_countries (idCountry, name)

• t_cities (idCity, idCountry, name, population)
• t_countries (idCountry, name)

t_cities 有 50000 行，所以我用英国的城市(只有 1100)创建了一个视图:

t_cities has 50000 rows so I create a VIEW with the cities from UK (only 1100):

CREATE VIEW v_city_uk AS SELECT * FROM t_cities WHERE idCountry = 140

到目前为止一切都很好，我得到了一个 1100 行的 VIEW.
查询

Everything is fine so far, I get a VIEW with 1100 rows.
The query

SELECT COUNT(*) FROM v_city_uk

返回 1100，但解释:

returns 1100, but the EXPLAIN:

EXPLAIN SELECT COUNT(*) FROM v_city_uk

表示它正在检查 50000 行以执行此查询.为什么?
有什么办法可以避免这种情况发生吗?因为我创建视图来加速查询不一样.

says that it is checking the 50000 rows to execute this query. Why?
Is there any way to make this not happen? Because I create the view to speed up the queries not to be the same.

编辑:感谢 matthewdavidson，我需要聚合表".

EDIT: An "aggregate table" is what I need, Thanks to matthewdavidson.

推荐答案

因为视图不是预编译查询或存储结果，它是一个预定义的 查询.它在视图中执行 SELECT 语句，每次.否则，视图永远不会更新.视图是查询的表示.对于程序员来说，这是一个方便的速记，但对于机器来说却不是.

Because a view is not a precompiled query or a stored result, it is a predefined query. It executes the SELECT statements in the view, every time. Otherwise, a view would never get updated. A view is a representation of a query. It's a convenient shorthand for the programmer, but not for the machine.

换句话说，当你

SELECT something FROM YourView

而 YourView 由

And YourView is defined by

CREATE VIEW YourView AS SELECT stuff FROM sometable 
INNER JOIN othertable ON sometable.column = othertable.column

实际运行的是

 SELECT something FROM 
(SELECT stuff FROM sometable 
INNER JOIN othertable ON sometable.column = othertable.column)

如果要存储结果，应使用聚合表.

If you want to store a result, you should use an aggregate table.

这篇关于Mysql - “EXPLAIN SELECT"从 VIEW 中查看主表的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！