查询优化器无法做优化的情况

查询优化器无法做优化的情况，又可以分为合理的和不合理的。
整理自《数据库查询优化器的艺术》，每个例子后面都有页码

合理的情况

常量+in+子查询，且子查询与上层的表没有关系，没有做上拉优化
select t1.* from t1 where 10 in (select t2.id from t2)
这种情况没法做上拉优化。查询计划是对t2做一个顺序扫描，结果作为t1表的filter。
where true 则where条件相当于没有
where false 则语句不返回任何值
(P24-25)

exists+子查询，且子查询与上层的表没有关系，没有做上拉优化
select t1.* from t1 where exists (select t2.id from t2 where t2.id=10)
子查询只要执行一次即可推知EXISTS的结果是TRUE或FALSE（exists在这里的含义是找到一个就返回true），所以不会执行多次，也没有比要优化。
(P27-28)

主查询中出现了易失函数，影响了子查询的结果，没有做上拉优化
select t1.* from t1 where t1.id+random() in (select t2.id from t2)
由于出现易失函数random()，子查询结果不确定，查询优化器就不能对子查询做优化。
(P24-25)

聚集函数操作出现在子查询的select中，没有做子查询消除
select * from t1 where t1.age>(select min(t2.age) from t2);
不支持子查询消除。因为没有必要，子查询只需执行一次即可得出结果值。
另一个例子也是这种情况，聚集函数操作出现在in子查询的select部分: select * from t1 where t1.age in (select min(t2.age) from t2);
(P377)

不合理的情况

OR没法转换ANY
sal>10 or sal>base+1 or sal>base*2
应重写为sal > ANY(10,base+1,base*2)
PG和MySQL都不支持
(P31）

谓词条件相似的子查询没有合并
select * from t1 where a1<4 and (exists(select a2 from t2 where t2.a2<5 and t2.b2=1) or exists(select a2 from t2 where t2.a2<5 and t2.b2=2));
执行计划是两个subquery，t2表做了两次独立的扫描（虽然都是用到了a2列索引的范围扫描)，没有把条件合并在一起判断。
应重写为select * from t1 where a1<4 and exists(select a2 from t2 wheer t2.a2<5 and (t2.b2=1 or t2.b2=2));
这样的好处是t2只执行了一次全表扫描，filter是(b2=1) or (b2=2)。这种改写的前提是t2表上面的选择度比较大，否则的话还是原语句效率更高。
(P374-P375)

子查询作为主查询的视图时，展开支持不够好
select from t1, (select from t2 where t2.age>10) v_t2 where t1.age <10 AND v_t2.age <20;
这种情况下，t2上的子查询仍然时单独执行：
select t1.id, t1.age, t2.id, t2.age from t1
join
(select t2.id, t2.age from t2 where t2.age >10) v_t2
where t1.age<10 and v_t2.age<20)
应重写为：
select * from t1 join t2 where t1.age<10 and t2.age>10 and t2.age<20
(P375)

in相关子查询上拉为内连接的策略，只在子查询的select是针对主键列的才生效
select * from t1 where t1.age<100 and age in (select age from t2 where t2.age>10)
当t2.age不是主键时，上述执行计划中，子查询被物化，但是没有上拉到顶层与t1进行连接。
当t2.age是主键时，执行计划显示上述子查询被转换为内连接。
(P376)
为什么这里强调in相关子查询？因为对于in非相关子查询来说，大多数又是支持上拉为内连接的。
(P380-P381)

不支持exists/not exists子查询转化为join
select * from t1 where exists(select 1 from t2 where t1.age=t2.age and t2.age>10)
select * from t1 where not exists(select 1 from t2 where t1.age=t2.age and t2.age>10)
上述两个语句的执行计划中，子查询的select type为dependent subquery。
(P378-P379)

不支持not in子查询转化为join
select * from t1 wher t1.age not in (select age from t2 where t2.age>10)
查询计划中，只是对子查询做了物化的优化方式，但没有与t1做join
(P381-P382)

普通子查询无法转换为连接
普通子查询指除了IN/NOT IN/EXISTS/NOT EXISTS/ALL/ANY/SOME以外的类型。
如select from t1 left join (select from t2 where t2.b2>10) tt2 on t1.b1=tt2.b2;
没有做优化，子查询仍然在。
(P438)

不支持把两个相同子查询合并
虽然可以把IN子查询转换为连接操作，如select * from t1 where t1.b1 in (select b2 from t2) and t1.a1 in (select b2 from t2);
对多次出现的相同子查询做了优化，在t2表上合并了in的条件，执行了一次扫描后对t2表进行了物化，并用两次semi join 完成连接：
select * from t1 semi join t2 semi join t2 where t1.a1=t2.b2 and t2.b2=t1.b1
但是两个semi join其实是可以合并的。
(P441)

能识别子查询，但不能进行子查询的上拉优化
select from (select from t1)T;
MySQL把select from t1和select from T当作两个不同的查询，t1表以derived方式被处理。
MySQL上拉子查询，在逻辑上有时会把子查询和原查询作为两个查询逻辑，所以没有机会把逻辑上重复的表去掉（但MySQL可以把逻辑上重复的表的条件合并）。
(P442)

连接的消除

• 主外键关系的表进行内/外连接，可消除主键表
• 唯一键作为连接条件，三表连接可以去掉中间表（中间表的列只作为连接条件）
• 可消除的表除了作为连接对象外，不出现在任何子句中
  (P40)

但是MySQL对上面的三种情况，都不支持连接消除(P398-P406)
情况一的一个示例：
create table a(id int, name varchar(2)), primary key(id);
create table b(id int, name varchar(2)), foreign key(id) references a(id);
create table c(id int,name varcher(2));

select b.,c. from a,b,c where b.id=a.id and a.id=c.id
被查询优化器处理后的语句：select b.,c. from a join b join c where b.id=c.id and a.id=c.id
执行计划显示，在a关系上做了eq_ref扫描，a没有被去除，c和a先进行连接，然后与b再连接。
(P398-P399)

语义优化

• 检测空回答集(Detecting the empty answer set)
• 谓词引入
• 排序优化(order optimizer)
• 唯一性使用(exxploiting uniqueness)

(P40)

MySQL支持部分语义优化的功能。（通过为SQL查询语句添加DDL语句定义的表上的非空属性，从而限定查询语句的条件判断来实现语义优化的，但其他的完整性约束MySQL尚不能利用）

MySQL支持：检测空回答集，如check约束限定name是not null，而一个查询条件是where name is null and age>18，则立刻推知条件不成立。
(P401)

MySQL不支持：谓词引入，如一个表上有c1<c2的列约束，c2列上存在一个索引，查询语句中的where条件有c1>200,则可以推知c2>200，where条件变更为c1>200 and c2>200 and c1<c2，由此可以利用c2列上的索引，对查询语句进行优化，如果c2列上索引的选择率很低，优化效果会更高。
(P40,P402)

MySQL支持：排序优化，但条件较为苛刻。order by操作如果可以利用索引，则排序操作可省略。
(案例见P403-P404)

MySQL支持：唯一性使用，利用唯一性、索引等特点，检查是否存在不必要的distinct操作，如在主键上执行distinct，可以把distinct消除掉。
(P405)