MySQL查看性能 [英] MySQL view performance

问题描述

我有一个约有100,000个用户的表.

I have a table for about 100,000 users in it.

第一种情况:

explain select state, count(*) as cnt from users where state = 'ca'

当我为上述查询做一个解释计划时，我得到的费用为5200

When I do an explain plan for the above query I get the cost as 5200

第二种情况:

Create or replace view vw_users as select state, count(*) as cnt from users

Explain select cnt from vw_users where state = 'ca'

当我对第二个查询做一个解释计划时，我得到的费用为100,000.

When I do an explain plan on the second query I get the cost as 100,000.

视图中的where子句如何工作?在视图检索所有行之后，将应用where子句吗?如何解决此问题?

How does the where clause in the view work? Is the where clause applied after the view retrieves all the rows? How do I fix this issue?

推荐答案

这与查看算法.

合并算法在大多数表索引上都能正常运行，而 temptable 算法则不能，在很多情况下-您的索引将完全不使用

The merge algorithm works well most table indexes and whatnot - the temptable algorithm doesn't - in many cases your indexes will just be flat-out not used at all.

有很多废话不支持合并

如果视图不能使用MERGE 包含以下任何一项 构造:

MERGE cannot be used if the view contains any of the following constructs:

* Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
* DISTINCT
* GROUP BY
* HAVING
* LIMIT
* UNION or UNION ALL
* Subquery in the select list
* Refers only to literal values (in this case, there is no underlying table)

这篇关于MySQL查看性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！