MySQL 视图性能

问题描述

我有一张可容纳大约 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?

推荐答案

关于 查看算法 已使用.

merge 算法适用于大多数表索引等 - 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 视图性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！