查询引擎将如何评估此 SQL 查询？
我正在研究 SQL 查询，我想到了这个 - 从表 T 中选择 x,y。
这里x和y可以是算术表达式，如2.0/5.0 OR 1+2，结果是表T的行数，第一列的值为2.0/5.0，即0.444444，第二列的值为3。
我很好奇了解该查询是如何执行的。 我了解到，在 SQL 查询引擎中，存在从查询的 SQL 表示到内部表示（如关系代数）的映射，然后对其进行优化，以产生最佳的查询执行计划。
如果我的理解有误，请纠正我的理解 - 对于我所说的查询，将有一个从我的查询 Q 到内部表示的内部映射，该内部表示相当于 select * from table T。 然后，对于返回的所有行，投影出 n 个列，其中 n 是 Q 中指定的算术表达式的数量。并且 n 列的所有行都将具有其各自的算术表达式的计算值。
干杯
I was playing around with SQL Queries and this one came to my mind - select x,y from table T.
Here x and y can be arithmetic expressions like 2.0/5.0 OR 1+2, the result has the number of rows of table T, with the first column having value 2.0/5.0 i.e. 0.444444 and the second column having value 3.
I am curious to know as to how this query is executed. I have learned that in the SQL query engine there is a mapping from SQL representation of a query to an internal representation (like relational algebra) which is then optimized in order to yield an optimal query execution plan.
Please correct my understanding if its wrong - For the query which I have stated, there would be an internal mapping from my query Q to an internal representation which is equivalent to that of select * from table T. Then for all the rows returned, n number of columns are projected out where n is the number of arithmetic expressions stated in the Q. And all rows for the n columns would have their respective evaluations of the arithmetic expressions.
cheers
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
它很可能取决于数据库。
如果您使用的 x 和 y 表达式是常数，那么一个好的数据库应该能够识别这一点，并且只执行一次计算，然后将值放入结果集中。
如果表达式包含其他一些函数，那么每次都必须执行该表达式，以防函数产生副作用。
一般来说，您是正确的，计算 from 子句中描述的虚拟表，然后我相信应用 where 子句中的过滤器，然后应用 select 子句。
It most likely depends on the database.
If the expressions you use for x and y were constant then a decent database ought to recognize that and perform the calculation only once and just putting the value in the result set.
If the expressions included some other function then it would have to execute the expression each time in case the function had side effects.
In general you are correct, calculate the virtual table described in the from clause, then I believe the filters from the where clause are applied followed by the select clause.
不，表达式仅计算一次（至少在 MS SQL Server 中）。
如果这样做，您可以很容易地看到这一点：
所有行都将具有相同的随机数。
No, the expressions are only evaluated once (at least in MS SQL Server).
You can easily see this if you do:
All rows will have the same random number.
它会扫描，因为没有 where 子句。 这是 sql server 的设置 showplan_all on
it will scan because there is no where clause. here is sql server's set showplan_all on
从 MS SQL 的角度来看...
它的计算结果可能与 SELECT * FROM Table 不同，因为引擎应该意识到它实际上不需要表中的任何列，因此它很可能会选择最薄的索引放在桌子上并扫描它。
实际方程只应计算一次，然后用作常数。
From a MS SQL point of view...
It might not evaluate the same as SELECT * FROM Table because the engine should realize that it doesn't actually need any of the columns from the table, so it will most likely select the thinnest index on the table and scan that.
The actual equations should only be evaluated once and from then on used as a constant.
PostgreSQL 将首先计算表达式，然后很可能对表执行顺序扫描。 此外，正如其他地方提到的，许多 SQL 风格都有一个 EXPLAIN 关键字，它将向您展示如何在给定当前实现的情况下评估特定查询。
PostgreSQL will evaluate the expression first, then most likely perform a sequential scan on the table. Also, as mentioned elsewhere, many SQL flavors have an EXPLAIN keyword, which will show you how, given your current implementation, a particular query will be evaluated.
数据库是否对每行计算一次函数是一个实现决策。 在 Oracle 中，您可以从表中选择 dbms_random.random() 并为每行获取不同的数字。 例如，如果您想以随机顺序返回行，这会很有帮助。
Whether the database evaluates the function once for each row is an implementation decision. In Oracle, you can select dbms_random.random() from a table and get different numbers back for each row. This is helpful if you want to return the rows in random order, for example.