是否在SQL视图中未选择列? [英] Are Columns Not Selected in SQL Views Executed?

问题描述

我无法提供正确的关键字来寻找答案，因此，如果已经回答，我深表歉意.

I wasn't able to come up with the right keywords to search for the answer for this, so apologies if it was answered already.

请考虑以下SQL视图:

Consider the following SQL view:

CREATE VIEW View1 AS
SELECT Column1
      ,Column2
      ,(SELECT SUM(Column3) FROM Table2 WHERE Table2.ID = Table1.ID)  -- Subquery
FROM Table1

如果我运行以下查询，将执行子查询还是SQL Server优化查询?

If I run the following query, will the subquery be executed or does SQL Server optimise the query?

SELECT Column1 FROM View1

我是从性能的角度来看这个问题的，例如，如果视图中有很多子查询(如果内部选择引用一个大表，则聚合可能会花费很长时间).

I'm looking at this from a performance point of view, say, if the view has quite a few subqueries (aggregations can take a long time if the inner select refers to a large table).

我正在使用SQL Server 2008 R2，但我想知道2012年的答案是否有所不同，也许是MySQL.

I'm using SQL Server 2008 R2, but I'm interested to know if the answer differs for 2012 or maybe MySQL.

谢谢.

推荐答案

如前所述，这取决于您的DBMS(版本和提供程序)，因此要确定要检查执行计划.这显示了对于SQL Server 2008子查询未执行:

As has been said, this varies depending on your DBMS (version and provider), to know for sure check the execution plan. This shows for SQL-Server 2008 the subquery is not executed:

正如您在未选择Column3的顶层计划中看到的那样，该计划只是从table1中选择，而在包含Column3的底层计划中，查询table2.

As you can see in the top plan where Column3 is not selected the plan is simply selecting from table1, in the bottom plan that in includes Column3, table2 is queried.

在SQL-Server 2008 R2中不会执行.

在SQL-Server 2012中未执行；

在MySQL中它被执行，并且两个查询生成相同的计划:

In MySQL it is executed, and both queries generate the same plan:

要进一步详细说明，这还取决于您的确切查询以及您的DBMS.例如:

To elaborate further, it will also depend on your exact query, as well as your DBMS. For example:

CREATE VIEW View2
AS
    SELECT  t.ID, t.Column1, t.Column2, t2.Column3
    FROM    Table1 t
            LEFT JOIN
            (   SELECT  ID, Column3 = SUM(Column3)
                FROM    Table2
                GROUP BY ID
            ) t2
                ON t2.ID = t.ID
GO
SELECT  Column1, Column2
FROM    View2;

SELECT  Column1, Column2, Column3
FROM    View2;

在这种情况下，您得到的结果与相关子查询类似，如果未选择column3，则计划仅显示来自table1的选择，因为优化器知道子查询t2与LEFT JOIN无关，因此它是LEFT JOIN.从表1中选择，并且不使用任何列，因此不会引起任何麻烦.但是，如果您将LEFT JOIN更改为INNER JOIN，例如

In this case you get similar results to the correlated subquery, The plan shows only a select from table1 if column3 is not selected, because it is a LEFT JOIN the optimiser knows that the subquery t2 has no bearing on the select from table1, and no columns are used so it does not bother with it. If you changed the LEFT JOIN to an INNER JOIN though, e.g.

CREATE VIEW View3
AS
    SELECT  t.ID, t.Column1, t.Column2, t2.Column3
    FROM    Table1 t
            INNER JOIN
            (   SELECT  ID, Column3 = SUM(Column3)
                FROM    Table2
                GROUP BY ID
            ) t2
                ON t2.ID = t.ID
GO
SELECT  Column1, Column2
FROM    View3;

SELECT  Column1, Column2, Column3
FROM    View3;

这两个查询的查询计划表明，由于第二个查询中未使用聚合列，因此优化程序实际上将视图更改为此:

The query plan for these two queries shows that because the aggregate column is not used in the second query, the optimiser essentially changes the view to this:

SELECT  t.ID, t.Column1, t.Column2
FROM    Table1 t
        INNER JOIN
        (   SELECT  DISTINCT ID
            FROM    Table2
        ) t2
            ON t2.ID = t.ID;

如表2中Distinct Sort的出现和Stream Aggregate的移除所示.

As seen by the appearance of the Distinct Sort on table2 and the removal of the Stream Aggregate.

总而言之，这取决于.

这篇关于是否在SQL视图中未选择列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！