sql优化之逻辑优化

查询语句可表示成一颗二叉树，其中叶子代表关系，内部结点是运算符，表示左右子树的连接关系，子树是sql片段或子表达式。根节点是最后运算的操作符。根节点运算之后，得到的是sql查询优化后的结果。这样一棵树就是查询路径。多个关系连接，连接顺序不同，可以得出多个类似的二叉树。
查询优化就是找出代价最小的二叉树，即最优的查询路径。
每条路径的生成，包括单表扫描，两表连接，多表连接顺序，多表连接搜索空间等技术。

思路一：逻辑优化，找出SQL语句等价的变换形式，使得sql执行更高效。优化操作依赖于表的一些属性信息（如索引和约束等）

• 子句局部优化：等价谓词重写，where和having条件化简
  1、等价谓词重写
  列举常见的等价谓词重写（like规则，between-and规则，in转换成or规则，NOT规则，or重写Union规则，这些规则的前提是要查询的字段建立索引；or转换any规则就是or谓词的any等价重写，以更好的利用min/max操作进行优化；all/any转换min/max规则,>any等价于min，<all等价于max;）？
  答：like谓词是sql标准支持的一种模式匹配比较操作，like规则是对like谓词的等价重写。如name like 'Abc%'可以改写成name>='Abc' and name < 'Abd'；
  between-and谓词是sql标准支持的一种范围比较操作，between-and规则是指between-and谓词的等价重写。如sno between 10 and 20要重写为sno>10 and sno <= 20；
  此处的in是in操作符，不是in查询。in转换成or规则是将In谓词转换成等价的or谓词，以更好的利用索引优化，如age in(8,12,21)重写为age=8 or age=12 or age=21;
  NOT规则，如not(col_1 != 2)重写为col_1=2;
  or重写union规则，如SELECT * FROM student WHERE(sex='f' AND sno＞15) OR age＞18;改写成SELECT * FROM student WHERE sex='f' and sno＞15 UNION SELECT * FROM student WHERE age＞18;
  all/any转换min/max规则，如sno>any(10,2*5+3,sqrt(9)) 找其中的最小值 重写为sno>min(10,2*5+3,sqrt(9));
  应用like规则和between-and规则，in转换成or规则,not规则的好处是？
  答：转换前对于like谓词只能进行全表扫描，如果name列上存在索引，则转换后可以进行索引范围扫描。转换前对于between-and谓词限定的全表扫描，如果在sno上建立索引，可以用索引范围扫描。in转换or规则后效率能否提高，需要看数据库对in谓词是否只支持全表扫描。如果数据库对In谓词只支持全表扫描且or谓词字段存在索引，则使用转换规则后效率会提高；not规则，如果在字段建立了索引，就可以用索引扫描代替原来的全表扫描。
  2、where,having和on条件化简
  将having条件并入where条件，只有在sql语句中不存在group by条件或聚集函数的情况下，才能having条件与where条件进行合并。去除冗余括号，常量传递，消除死码，表达式计算，等式变换，不等式变换，布尔表达式变换，利用索引。这几项过于简单，不做展开描述

• 子句间关联优化（子句与子句之间关联的语义）：外连接消除，嵌套连接消除，子查询优化，视图重写等都属于子句间的关联优化，因为它们的优化都需要借助其他子句，表定义或列属性等信息
  1、外连接消除
  外连接是什么？
  答：左外连接，右外连接，全外连接。连接过程中，外连接的左右子树不能互换。它的这种性质限制了优化器在选择连接顺序时能够考虑的表与表交换连接位置的优化方式。
  优化时将外连接转换成内连接的意义
  答：查询优化器在处理外连接操作时所需要执行的操作和时间多于内连接。优化器在选择表的连接顺序时，可以有更多更灵活的选择，从而可以选择更好的表连接顺序，加快查询执行的速度。表的一些连接算法（如块嵌套连接和索引循环连接等）将规模小的或筛选条件最严格的表作为“外表”（放在连接顺序的最前面，是多层循环体的外循环层），可以减少不必要的IO开销，极大地加快算法执行速度。
  外连接消除的条件
  答：where子句中与内表相关的条件满足“空值拒绝”
  2、嵌套连接消除
  嵌套连接及嵌套连接消除是什么？
  答：当执行连接操作的次序不是从左到右逐个进行时，就说明这样的连接表达式存在嵌套。如select * from t1 left join(t2 left join t3 on t2.b = t3.b) on t1.a = t2.a where t1.a > 1;先t2与t3连接，得到中间结果{t2t3}后再与t1连接,去掉括号会影响语义。还有形如select * from a join (b join c on b.b1 = c.c1) on a.a1 = b.b1 where a.a1 > 1;去掉括号对语义没有影响，所以可以消除。还有连接表达式只包括内连接，就可以去掉括号。因为内连接表之间的次序可以互换。如果是外连接，至多转化成内连接，再消除。
  3、子查询优化
  为啥要执行子查询优化？
  答：查询优化器对子查询一般采用嵌套执行方式，即对父查询中的每一行，都执行一次子查询，这样子查询会执行很多次。所以会让查询效率降低。因此对于子查询的优化，可能带来几个数量级的查询效率的提高。把子查询变成连接操作之后，子查询不用执行很多次。优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序。子查询中的连接条件，过滤条件分别变成了父查询的连接条件，过滤条件，优化器可以对这些条件进行下推，以提高执行效率。
  什么时候子查询只能单独求解？
  答：如果子查询出现聚集，group by,distinct，子查询只能单独求解，不可以拉到上层。
  最常见的子查询类型优化？
  答：常见子查询格式有in类型，all/any/some类型,exists类型。
  4、视图重写
  建立视图。

• 语义优化：根据完整性约束，sql表达含义等信息对语句进行语义优化
  语义优化有哪些
  groupby 优化
  1、分组操作下移 ：groupby 操作可能较大幅度地减少关系元组的个数，如果能对某个关系先进行分组操作，再进行表的连接，会提高连接效率。这种优化方式是把分组操作提前执行。下移含义，是在查询树上让分组操作尽量靠近叶子节点，使得分组操作的结点低于一些选择操作。
  2、分组操作上移 ：如果连接操作能够过滤掉大部分元组，则先进行连接后进行groupby操作，可能提高分组操作效率。这种优化方式是把分组操作置后执行。
  order by优化
  排序消除，优化器在生成执行计划之前，将语句中没有必要的排序操作消除（利用索引），避免在执行计划中出现排序操作或由排序导致的操作。
  排序下推，把排序操作尽量下推到基表中，有序的基表进行连接后的结果符合排序的语义，这样能避免在最终的大的连接结果集上执行排序操作。
  distinct优化
  distinct消除：如果表中存在主键，唯一约束，索引等，则可以消除查询语句中的distinct。
  distinct推入：生成含distinct的反半连接查询执行计划时，先进行反半连接再进行distinct操作，也许先执行distinct操作再执行反半连接更优，这是利用连接语义上确保唯一功能特性进行distinct的优化。
  distinct迁移：对连接操作的结果执行distinct,可能把distinct移到一个子查询中优先进行。

• 其他优化：根据一些规则对非SPJ做的其他优化，根据硬件环境进行的并行查询优化