mysql 优化器链接消除笔记

1. 嵌套链接消除

连接存在多个层次，用括号标识连接的优先次序。嵌套连接消除，就是消除嵌套的连接层次，把多个层次的连接减少为较少层次的连接，尽量“扁平化”。

2. 链接消除

create table b(b1 int, b2 varchar(2), primary key(b1));

create table a(a1 int, a2 varchar(2), foreign key(a1) references b(b1));

create table c(c1 int, c2 varchar(2));

insert into b values(1, 'b1');

insert into b values(2, 'b2');

insert into b values(3, 'b3');

insert into a values(1, 'a1');

insert into a values(null, 'a2');

insert into a values(3, 'a3');

insert into c values(1, 'c1');

insert into c values(2, 'c2');

insert into c values(null, 'c3');

①情况一（MySQL不支持）

唯一键/主键作为连接条件，三表内连接可以去掉中间表（中间表的列只作为连接条件）。

如：

t1(t1_c1 int unique, t1_c2 int);

t2(t2_c1 int unique, t2_c2 int);

t3(t3_c1 int unique, t3_c2 int);

select t1.*, t3.* from t1,t2,t3 where t1.t1_c1 = t2.t2_c1 and t2.t2_c1 = t3.t3_c1;

可以消除t2表转为

select t1.*, t3.* from t1,t3 where t1.t1_c1 = t3.t3_c1;

②情况二（MySQL不支持）

一些特殊形式，可以消除连接操作（可消除的表除了作为连接对象外，不出现在任何子句中）。

select max(a1) from a,b;

③情况三（MySQL不支持）

主外键关系的表进行的连接，可消除主键表，这不会影响对外键表的查询。目标主键只作为连接对象和连接条件存在。

对主外键参照的表进行内连接，可以消除主键表（b)

1）

explain extended select a.*, c.* from a,b,c where a.a1=b.b1 and b.b1 = c.c1;

2）

explain extended select a.*, c.* from a,c where a.a1= c.c1 and a.a1 is not null;

3）

explain extended select a.*, c.* from a,c where a.a1= c.c1;

A->B : A表依赖B表，这里如果ABC内连接，结果无B表的列出现，仅仅作为连接条件。

b.b1最为“桥“,且b.b1肯定不为空（null）。

sql1 中的连接条件a.a1=b.b1 and b.b1 = c.c1; a.a1可能为null，但是不满足a.a1=b.b1，因为b.b1不为空，所以对已这类记录可以过滤。

a.a1 is not null 可保证1）和2）等价。

对主外键参照的表进行外连接，可以消除主键表

select a.*, c.* from a left join b on(a1=b1) join c on (a1 = c1);

同样b.b1作为桥，可以消除。

select a.*,c.* from a left join c on(a1 =c1);

注： 如果a1列不为null，则肯定等价：

        如果a1列为null，a1=b1 -> false

                                  a1=c1 -> false

所以也等价。