MySQL 视图性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2760475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL view performance
提问by vamsivanka
I have a table for about 100,000 users in it.
我有一个大约 100,000 个用户的表。
First Case:
第一种情况：
explain select state, count(*) as cnt from users where state = 'ca'
When I do an explain plan for the above query I get the cost as 5200
当我为上述查询做一个解释计划时，我得到的成本为 5200
Second Case:
第二种情况：
Create or replace view vw_users as select state, count(*) as cnt from users
Explain select cnt from vw_users where state = 'ca'
When I do an explain plan on the second query I get the cost as 100,000.
当我对第二个查询执行解释计划时，我得到的成本为 100,000。
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?
视图中的 where 子句如何工作？视图检索所有行后是否应用 where 子句？我该如何解决这个问题？
回答by Peter Bailey
It's about the view algorithmthat's been used.
这是关于已使用的视图算法。
The mergealgorithm works well most table indexes and whatnot - the temptablealgorithm doesn't - in many cases your indexes will just be flat-out not used at all.
该合并算法行之有效最表的索引和诸如此类的东西-的不是Temptable算法不-在很多情况下，你的索引将只是平了都没有用。
And there's lots of crap that merge doesn't support
合并不支持很多废话
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)
如果视图包含以下任何结构，则不能使用 MERGE：
* 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)
