VIEW 与 SQL 语句的性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1867896/
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
Performance of VIEW vs. SQL statement
提问by Matt W.
I have a query that goes something like the following:
我有一个类似于以下内容的查询：
select <field list>
from <table list>
where <join conditions>
and <condition list>
and PrimaryKey in (select PrimaryKey from <table list>
where <join list>
and <condition list>)
and PrimaryKey not in (select PrimaryKey from <table list>
where <join list>
and <condition list>)
The sub-select queries both have multiple sub-select queries of their own that I'm not showing so as not to clutter the statement.
子选择查询都有自己的多个子选择查询，我没有显示，以免使语句混乱。
One of the developers on my team thinks a view would be better. I disagree in that the SQL statement uses variables passed in by the program (based on the user's login Id).
我团队中的一位开发人员认为视图会更好。我不同意 SQL 语句使用程序传入的变量（基于用户的登录 ID）。
Are there any hard and fast rules on when a view should be used vs. using a SQL statement? What kind of performance gain issues are there in running SQL statements on their own against regular tables vs. against views. (Note that all the joins / where conditions are against indexed columns, so that shouldn't be an issue.)
关于何时应该使用视图与使用 SQL 语句，是否有任何硬性规定？在针对常规表与针对视图运行 SQL 语句时，存在什么样的性能提升问题。（请注意，所有连接 / where 条件都针对索引列，因此这应该不是问题。）
EDIT for clarification...
编辑以澄清...
Here's the query I'm working with:
这是我正在使用的查询：
select obj_id
from object
where obj_id in(
(select distinct(sec_id)
from security
where sec_type_id = 494
and (
(sec_usergroup_id = 3278
and sec_usergroup_type_id = 230)
or
(sec_usergroup_id in (select ug_gi_id
from user_group
where ug_ui_id = 3278)
and sec_usergroup_type_id = 231)
)
and sec_obj_id in (
select obj_id from object
where obj_ot_id in (select of_ot_id
from obj_form
left outer join obj_type
on ot_id = of_ot_id
where ot_app_id = 87
and of_id in (select sec_obj_id
from security
where sec_type_id = 493
and (
(sec_usergroup_id = 3278
and sec_usergroup_type_id = 230)
or
(sec_usergroup_id in (select ug_gi_id
from user_group
where ug_ui_id = 3278)
and sec_usergroup_type_id = 231)
)
)
and of_usage_type_id = 131
)
)
)
)
or
(obj_ot_id in (select of_ot_id
from obj_form
left outer join obj_type
on ot_id = of_ot_id
where ot_app_id = 87
and of_id in (select sec_obj_id
from security
where sec_type_id = 493
and (
(sec_usergroup_id = 3278
and sec_usergroup_type_id = 230)
or
(sec_usergroup_id in (select ug_gi_id
from user_group
where ug_ui_id = 3278)
and sec_usergroup_type_id = 231)
)
)
and of_usage_type_id = 131
)
and
obj_id not in (select sec_obj_id
from security
where sec_type_id = 494)
)
回答by Charles Bretana
Depending on the database vendor, in general, the execution of a query against a view combines the SQL defined in the View with the Where clause predicates and Order By clause sort expressions appended to the sql that you pass against the View, to come up with a combined complete SQL query to execute. This is then executed as though it had itself been passed to query processsor, so there should be no difference.
根据数据库供应商的不同，通常，对视图执行查询会将视图中定义的 SQL 与附加到针对视图传递的 sql 的 Where 子句谓词和 Order By 子句排序表达式结合起来，以得出要执行的组合完整 SQL 查询。然后执行就好像它本身已传递给查询进程一样，因此应该没有区别。
Views are an organizational tool, not a performance enhancement tool.
视图是一种组织工具，而不是性能增强工具。
From SQL Server View resolution
When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.
当 SQL 语句引用非索引视图时，解析器和查询优化器会分析 SQL 语句和视图的来源，然后将它们解析为单个执行计划。没有针对 SQL 语句的一个计划和针对视图的单独计划。
回答by RickNZ
Regular (non indexes / materialized) Views are just aliases; they don't offer any performance advantages. Selecting from a View generates exactly the same query plan as selecting directly from the table.
常规（非索引/物化）视图只是别名；它们不提供任何性能优势。从视图中选择生成的查询计划与直接从表中选择生成的查询计划完全相同。
回答by simeonwillbanks
Views aside, aren't the PrimaryKey AND clauses redundant? If the PrimaryKey value is IN a list, wouldn't it not be IN the other list? I think condensing those two clauses into one would boost performance.
撇开观点不谈，PrimaryKey AND 子句不是多余的吗？如果 PrimaryKey 值在一个列表中，它不会在另一个列表中吗？我认为将这两个子句浓缩为一个会提高性能。
