子查询优化

概述和分类
  • 子查询在外层查询中可能出现的位置：
    • 在 select 中：SELECT (SELECT MAX(COL1) FROM TABLE1);
    • 在 from 中：SELECT M1 FROM (SELECT M+1 FROM TABLE1 WHERE M>2) AS TMP;
    • 在 WHERE 中（最常见）：SELECT * FROM TABLE1 WHERE M1 IN (SELECT M2 FROM TABLE2)；
  • 按照子查询返回的数据的规模，将子查询分类如下：
    • 标量子查询；行子查询；列子查询；表子查询；
  • 按照与外层查询的关系进行分类：
    • 不相关子查询：子查询可以独立运行得到结果；
    • 相关子查询：依赖外部查询的结果；SELECT * FROM T1 WHERE T1.M1 IN (SELECT T2.M2 IN T2 WHERE T1.N1 = T2.N2)
限制条件

由于子查询一般只是用在 where 中，以构成操作数 comparison_operator (子查询)，所以这里列举一下这种情况下的一些限制条件：

  • comparison_operator 为=, !=, <, >之类：子查询只能是标量子查询或者行子查询；
  • 对于列子查询和表子查询而言，因为涉及到集合，所以只能使用 IN/NOT IN/ ANY/SOME/ALL 来构成布尔表达式：SELECT * FROM T1 WHERE T1.N1 > ANY(SELECT T2.N2 FROM T2)等价于SELECT * FROM T1 WHERE T1.N1 > (SELECT MIN(T2.N2) FROM T2)
  • 当使用了 IN/ANY/SOME/ALL 这些关键字后，子查询内不允许有 LIMIT 关键字
  • EXISTS 子查询：只关心是否有值；
  • 在子查询中，ORDER BY, DISTINCT, GROUP BY, HAVING 这些关键字都是多余的，因为子查询本质上返回的是一个集合，而集合的特点就是不关心是否有序和重复；
优化

  1. 不相关的标量子查询或行子查询
    直接执行子查询即可，然后利用执行结果改写外层查询中的 WHERE 条件；

  2. 不相关的 IN 子查询

  • 和 1 最大的不同在于：IN 子查询中提取出的数据可能非常多，所以直接使用 1 的方案是不合理的（可能内存容量不够，效率也不高）；
  • 高效的做法是对子查询的结果建立临时表，并对*所有的列建立联合唯一索引以去重；
  • 由于临时表中已经没有重复数据，所以此时子查询完全等价于 INNER JOIN；
  • 系统将子查询转换为内连接，然后自己决定要将哪一个表作为驱动表；
  1. SEMI-JOIN
  • 2 中的方案已经极大地提升了查询效率；但如果能够跳过建立临时表的阶段，直接进行 join 的话，就能够进一步进行优化；
  • 但是，如果直接进行 join 的话，只要外部 SELECT 的记录能够匹配多条内部 SELECT 的记录，那么 join 之后，该条外部的记录就会被提取多次；
  • 如何避免重复，就是 SEMI JOIN 的思路；具体几种思路如下：
    • 子查询表上拉（Table pullout）：如果子查询的查询列表本身就可以确定是不重复的（比如说查询的是唯一索引的索引列），那么就可以直接转换为 INNER JOIN；
    • 重复值消除（Duplicate Weed Out）：我们的目的是让外部 select 的记录不重复，那么就记录其已经插入的记录的主键 id 即可；当即将插入一个之前已经插入的 id，就放弃这条记录；