SQL-SQL优化之列裁剪和投影消除

「爱情、让人受尽委屈。」 2023-03-01 11:40 5阅读 0赞

# SQL-SQL优化之列裁剪和投影消除 #

# 转载声明 #

***本文大量内容系转载自以下文章，有删改，并参考其他文档资料加入了一些内容：***

*  [SQL优化之列裁剪和投影消除][SQL]  
    作者：叁金  
    出处：imooc

# 1 列裁剪 #

对于没用到的列，则没有必要读取它们的数据去浪费无谓的IO

比如我们有一张表`table1`，它含有四列数据（a,b,c,d）。

当我们执行查询`select a from table1 where c >10`时，我们可以清晰的看到，table1中只有a,c两列被用到了:

*  Selection算子用到c列
 *  Projection算子用到a列。

那么DataSource读取数据时，`b，d`两列则不需要读取，可以裁剪掉。

那么都有哪些算子与列有关系呢？综合我们多年来使用SQL的经验来看，Selection(Where 条件)、Projection（`select`搜索投影的列）、Sort（排序列）、Join（等值连接）、Aggregation（Group By及相关聚合操作）等。

**列裁剪的算法就是自顶向下的把算子过一遍，某个节点需要用到的列就等于它自己需要用到的列加上它的父节点所需要用到的列。这样得到整个SQL语句所涉及到的列，从而在读取数据时只读取真正需要的列即可。**

也就是说，列裁剪通过只读取需要的数据减少IO操作来达到优化的目的

# 2 投影 #

投影运算也是一个单目运算，它是从一个关系R中选取所需要的列组成一个新关系，比如`select a,b,c`这样的语句，让结果集仅包含指定列`a, b, c`，这种操作称为投影查询。

# 3 投影消除 #

投影消除是把不必要的`Projection`给消除掉

那么问题来了，什么情况下，投影算子是可以被消除的呢？

*  如何Projection算子需要投影的列跟子节点的输出列一样，那么这个投影就是一个废操作，可以被消除掉  
    比如说：`select a,b from table1` ，如果表table1中刚好只有`a，b`两列，也就是DataSource的输出和Projection需要投影的列一样，那么这时候就没必要在TableScan之后再做一次Projection操作了。
 *  如果Projection的子节点还是Projection的话，那么子节点的Projection就没有意义了，可以干掉。  
    如：`select a from (select a,b,c from table2)`这条语句里面有两个Projection，分别是最上层的`Projection(a)`和它的子节点`Projection(a,b,c)`。那么`Projection(a,b,c)`这个节点就是废操作，可以被消除掉。
 *  Aggregation在某种意义上也属于投影操作，因为从这个节点出来的都是列的概念  
    比如`Max(a)`、`Min(b)`等。因此在Aggregation->Projection的过程中，这个Projection也是可以被消除掉的。

所以说，一个节点是否可以被消除:

*  一方面是由它的父节点告诉它，它是否是一个冗余的Projection操作。
 *  另一方面是它自己和孩子节点做比较，看自身是否可以被消除。

投影消除伪代码：

public void eliminate(Plan plan, boolean canEliminate) { 
        //如果plan为Projection则判断是否需要被消除
        if (plan is Projection) { 
            //如果父节点调用时指定canEliminate为true，则进行消除操作
            if (canEliminate) { 
                doEliminate(plan);
            }
            //如果plan的输出和子节点的输出一样则消除plan
            if (checkPlanOutEqualsNext(plan)) { 
                doEliminate(plan);
            }
        }
        //递归调用，遍历子节点
        eliminate(plan.next(), checkPlanNextCanEliminate(plan));
    }

# 4 最大最小消除 #

**最大最小消除严格上说不是标准逻辑优化里面需要做的事情**

举个栗子：

语句1

select min(a) from table1

语句1生成的逻辑执行计划是一个 TableScan 上面接一个 Aggregation，也就是说这是一个全表扫描的操作。

那么可以转换为语句2

select a from table1 order by a desc limit 1

语句2生成的逻辑执行计划是`TableScan + Sort + Limit`，在某些情况，比如a是主键或者是存在索引，数据本身是有序的， Sort 就可以消除，最终变成 TableScan 或者 IndexLookUp 加 Limit，这样子就不需要全表扫了，读到第一条数据就得到结果！

全表扫跟只查一条数据，查询速度可是天壤之别。也许这一点点写法上的区别，就是几分钟甚至更长，跟毫秒级响应的差距。

最大最小消除就是SQL优化器自动把上面的操作实现了。比如说：

*  最大消除  
    select max(id) from table1 生成的查询计划会变成下面这种（最大消除）：
    
    select max(id) from (select id from table1 order by id desc limit 1 where id is not null) t
 *  最小消除  
    select min(id) from table1生成的查询计划会变成下面这种（最小消除）：
    
    select min(id) from (select id from table1 order by id limit 1 where id is not null) t

然后转换后的语句再经过其他的转换规则最终得到最后的查询计划。

[SQL]: https://www.imooc.com/article/278661