子查询表的查询是否得到优化？

Question

关于查询优化，我想知道下面这样的语句是否得到优化：

select *
from (
    select *
    from table1 t1
    join table2 t2 using (entity_id)
    order by t2.sort_order, t1.name
)   as foo -- main query of object
where foo.name = ?; -- inserted

考虑到查询是由依赖对象处理的，但正确吗？允许一个人在WHERE条件下进行定位。我认为至少不会有太多的数据被输入到您最喜欢的语言中，但是如果这是一个足够的优化，并且数据库仍然需要一些时间来完成查询，我会重新考虑。

或者，最好将该查询提取出来，并编写一个单独的查询方法，该方法具有where，也许还有LIMIT 1子句？

Answer 1

我不能代表MySQL说话-更不用说它可能因存储引擎和MySQL版本而不同，但对于PostgreSQL：

PostgreSQL会将其扁平化为一个查询。内部ORDER BY不是问题，因为添加或删除谓词不会影响其余行的排序。

它会被夷为平地：

select *
from table1 t1
join table2 t2 using (entity_id)
where foo.name = ?
order by t2.sort_order, t1.name;

然后，join谓词将在内部进行转换，生成与SQL对应的计划：

select t1.col1, t1.col2, ..., t2.col1, t2.col2, ...
from table1 t1, table2 t2 
where 
   t1.entity_id = t2.entity_id
   and foo.name = ?
order by t2.sort_order, t1.name;

使用简化模式的示例：

regress=> CREATE TABLE demo1 (id integer primary key, whatever integer not null);
CREATE TABLE
regress=> INSERT INTO demo1 (id, whatever) SELECT x, x FROM generate_series(1,100) x;
INSERT 0 100
regress=> EXPLAIN SELECT *
FROM (
    SELECT *
    FROM demo1
    ORDER BY id
) derived
WHERE whatever % 10 = 0;
                        QUERY PLAN                         
-----------------------------------------------------------
 Sort  (cost=2.51..2.51 rows=1 width=8)
   Sort Key: demo1.id
   ->  Seq Scan on demo1  (cost=0.00..2.50 rows=1 width=8)
         Filter: ((whatever % 10) = 0)
 Planning time: 0.173 ms
(5 rows)

..。与以下计划相同的计划：

EXPLAIN SELECT *
FROM demo1
WHERE whatever % 10 = 0
ORDER BY id;
                        QUERY PLAN                         
-----------------------------------------------------------
 Sort  (cost=2.51..2.51 rows=1 width=8)
   Sort Key: id
   ->  Seq Scan on demo1  (cost=0.00..2.50 rows=1 width=8)
         Filter: ((whatever % 10) = 0)
 Planning time: 0.159 ms
(5 rows)

如果有一个LIMIT、OFFSET、一个窗口函数，或者在内部查询中阻止限定符向下/向上/平放的某些其他事情，那么PostgreSQL就会认识到它不能安全地使其平坦。它将通过实现内部查询或迭代其输出并将其提供给外部查询来评估内部查询。

视图也是如此。在安全的情况下，PostgreSQL会将视图放入包含的查询中。

Answer 2

在MySQL，没有。

外部查询中的谓词不会被“推”到内联视图查询中。

内联视图中的查询首先处理，独立于外部查询。(MySQL将优化该视图查询，就像您单独提交该查询时会优化该查询一样。)

MySQL处理此查询的方式:首先运行内联视图查询，结果被具体化为“派生表”。也就是说，查询的结果集作为临时表存储在内存中(如果足够小，并且不包含内存引擎不支持的任何列)。否则，就会使用MyISAM存储引擎将其作为一个MyISAM表转到磁盘上。

填充派生表后，外部查询将运行。

(请注意，派生表上没有任何索引。在5.6之前的MySQL版本中是这样的；我认为5.6中有一些改进，其中MySQL实际上将创建一个索引。

澄清:对派生表的索引： of MySQL 5.6.3“在查询执行期间，优化器可以向派生表中添加索引，以加快从派生表中进行行检索。参考资料：http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

而且，我不认为MySQL“优化”了内联视图中任何不需要的列。如果内联视图查询是SELECT *，那么所有列都将在派生表中表示，无论这些列是否在外部查询中引用。

这可能会导致一些重要的性能问题，特别是当我们不理解MySQL如何处理语句时。( MySQL处理语句的方式与其他关系数据库(如Oracle和Server)有很大的不同。

您可能听说过“避免在MySQL中使用视图”的建议。这种一般性建议(适用于“存储”视图和“内联”视图)背后的推理是可能不必要地引入的重要性能问题。

例如，对于此查询：

SELECT q.name
  FROM ( SELECT h.*
           FROM huge_table h
       ) q
 WHERE q.id = 42

MySQL不会将谓词id=42“推”到视图定义中。MySQL首先运行内联视图查询，实质上创建一个huge_table副本，作为一个未索引的MyISAM表。一旦完成，外部查询将扫描表的副本，以找到满足谓词的行。

如果我们重写查询以“将”谓词“推入”视图定义，如下所示：

SELECT q.name
  FROM ( SELECT h.*
           FROM huge_table h
          WHERE h.id = 42
       ) q

我们期望从视图查询返回一个更小的结果集，而派生的表应该更小。MySQL还将能够有效地使用索引ON huge_table (id)。但是，仍然存在一些与实现派生表相关的开销。

如果我们从视图定义中删除不必要的列，那么效率会更高(特别是如果有很多列，就会有任何大列，或者内存引擎不支持数据类型的任何列)：

SELECT q.name
  FROM ( SELECT h.name
           FROM huge_table h
          WHERE h.id = 42
       ) q

更有效的方法是完全消除内联视图：

SELECT q.name
  FROM huge_table q
 WHERE q.id