MySQL的查询优化技术-07

MySQL数据库查询优化技术第7周法律声明【声明】本视频和幻灯片为炼数成金网络课程的教学资料，所有资料只能在课程内使用，不得在课程以外范围散播，违者将可能被追究法律和经济责任。课程详情访问炼数成金培训网站TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1Adatatechnologylearners,practitionersBook:《数据库查询优化器的艺术：原理解析与SQL性能优化》23Blog:/li_hx4Twitter：@那海蓝蓝Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)6课程大纲：1数据库与关系代数2数据库查询优化技术总揽3查询优化技术理论与MySQL实践（一）------子查询的优化（一）4查询优化技术理论与MySQL实践（二）------子查询的优化（二）5查询优化技术理论与MySQL实践（三）------视图重写与等价谓词重写6查询优化技术理论与MySQL实践（四）------条件化简7查询优化技术理论与MySQL实践（五）------外连接消除、嵌套连接消除与连接消除8查询优化技术理论与MySQL实践（六）------数据库的约束规则与语义优化9查询优化技术理论与MySQL实践（七）------非SPJ的优化10MySQL物理查询优化技术概述、表扫描与连接算法与MySQL多表连接优化实践11MySQL索引的利用、优化12查询优化的综合实例------TPCH实践（一）13查询优化的综合实例------TPCH实践（二）14关系代数对于数据库的查询优化的指导意义------查询优化技术总结15MySQL的其它的一些优化技术TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/Lesson7:LogicalQueryOptimization------OuterJoinElimination、JoinElimination、NestJoinEliminationTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1OuterJoinElimination2JoinElimination3NestJoinEliminationTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1OuterJoinElimination1.1WhatisOuterJoin?1.2ThetypeofOuterJoin1.3WhydoesOuterJoinElimination?1.4HowtodoOuterJoinEliminationforMySQL?TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.1WhatisOuterJoin?内连接外连接A与B都满足连接条件A满足连接条件B不满足连接条件TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.1WhatisOuterJoin?Table:bookTable:studentSELECT*FROMbookASb,studentASsWHEREb.studentid=s.studentidSELECT*FROMbookASbLEFTJOINstudentASsONb.studentid=s.studentidTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.1WhatisOuterJoin?SELECT*FROMbookASb,studentASsWHEREb.studentid=s.studentidSELECT*FROMbookASbLEFTJOINstudentASsONb.studentid=s.studentid左外连接内连接TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.2ThetypeofOuterJoin1）LEFTJOIN/LEFTOUTERJOIN：左外连接

左向外连接的结果集包括：LEFTOUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。2）RIGHTJOIN/RIGHTOUTERJOIN：右外连接

右向外连接是左向外联接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。3）FULLJOIN/FULLOUTERJOIN：全外连接全外连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.2ThetypeofOuterJoin右外连接SELECT*FROMbookASbRIGHTJOIN

studentASsONb.studentid=s.studentid全外连接SELECT*FROMbookASbFULLOUTERJOINstudentASsONb.studentid=s.studentidTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.3WhydoesOuterJoinElimination?外连接消除：把外连接变为内连接AOUTERJOINB变形为AJOINBTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.3WhydoesOuterJoinElimination?外连接消除的意义：1查询优化器在处理外连接操作时所需执行的操作和时间多于内连接2外连接消除后，优化器在选择多表连接顺序时，可以有更多更灵活的选择，从而可以选择更好的表连接顺序，加快查询执行的速度3表的一些连接算法（如块嵌套连接和索引循环连接等）在将规模小的或筛选条件最严格的表作为“外表”（放在连接顺序的最前面，是多层循环体的外循环层），可以减少不必要的I/O开销，能加快算法执行的速度TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.3WhydoesOuterJoinElimination?SELECT*FROMT1INNERJOINT2ONP1(T1,T2)INNERJOINT3ONP2(T2,T3)WHEREP(T1,T2,T3)FOReachrowt1inT1{FOReachrowt2inT2suchthatP1(t1,t2){FOReachrowt3inT3suchthatP2(t2,t3){IFP(t1,t2,t3){t:=t1||t2||t3;OUTPUTt;}}}}FOReachrowt1inT1{BOOLf1:=FALSE;FOReachrowt2inT2suchthatP1(t1,t2){BOOLf2:=FALSE;FOReachrowt3inT3suchthatP2(t2,t3){IFP(t1,t2,t3){t:=t1||t2||t3;OUTPUTt;}f2=TRUE;f1=TRUE;}IF(!f2){IFP(t1,t2,NULL){t:=t1||t2||NULL;OUTPUTt;}f1=TRUE;}}IF(!f1){IFP(t1,NULL,NULL){t:=t1||NULL||NULL;OUTPUTt;}}}SELECT*FROMT1LEFTJOIN(T2LEFTJOINT3ONP2(T2,T3))ONP1(T1,T2)WHEREP(T1,T2,T3)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除的条件：WHERE子句中的条件满足“空值拒绝”（又称为“reject-NULL”条件）。

WHERE条件可以保证从结果中排除外连接右侧（右表）生成的值为NULL的行（即条件确保应用在右表带有空值的列对象上时，条件不满足，条件的结果值为FLASE或UNKONOWEN，这样右表就不会有值为NULL的行生成），所以能使该查询在语义上等效于内连接。explainSELECT*FROMXLEFTJOINYON(X.X_num=Y.Y_num)WHEREY.Y_numISNOTNULL;TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：创建表，命令如下：CREATETABLEt_1(t_1_idINTUNIQUE,t_1_col_1INT,t_1_col_2VARCHAR(10));CREATETABLEt_2(t_2_idINTUNIQUE,t_2_col_1INT,t_2_col_2VARCHAR(10));插入数据，命令如下：INSERTINTOt_1VALUES(1,11,'t_1_1');INSERTINTOt_1VALUES(2,12,NULL);INSERTINTOt_1VALUES(3,NULL,'t_1_3');INSERTINTOt_1VALUES(4,14,'t_1_4');INSERTINTOt_1VALUES(5,15,NULL);INSERTINTOt_1VALUES(7,NULL,NULL);INSERTINTOt_2VALUES(1,11,'t_2_1');INSERTINTOt_2VALUES(2,NULL,'t_2_2');INSERTINTOt_2VALUES(3,13,NULL);INSERTINTOt_2VALUES(4,14,'t_2_4');INSERTINTOt_2VALUES(6,16,'t_2_6');INSERTINTOt_2VALUES(7,NULL,NULL);TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：语句一，使用TRUE作为ON的子句，WHERE子句包括连接条件：SELECT*FROMt_1LEFTJOINt_2ONtrue

WHEREt_1_id=t_2_id;语句二，使用ON子句包括连接条件：SELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_id;语句三，使用ON和WHERE子句包括连接条件：SELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_id

WHEREt_1_id=t_2_id;TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---从执行结果看对于语句二，查询执行结果如下：mysql>SELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_id;+--------+-----------+-----------+--------+-----------+-----------+|t_1_id|t_1_col_1|t_1_col_2|t_2_id|t_2_col_1|t_2_col_2|+--------+-----------+-----------+--------+-----------+-----------+|1|11|t_1_1|1|11|t_2_1||2|12|NULL|2|NULL|t_2_2||3|NULL|t_1_3|3|13|NULL||4|14|t_1_4|4|14|t_2_4||5|15|NULL|NULL|NULL|NULL||7|NULL|NULL|7|NULL|NULL|+--------+-----------+-----------+--------+-----------+-----------+6rowsinset(0.00sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---从执行结果看对于语句一、语句三，查询执行结果如下：mysql>SELECT*FROMt_1LEFTJOINt_2ONtrueWHEREt_1_id=t_2_id;+--------+-----------+-----------+--------+-----------+-----------+|t_1_id|t_1_col_1|t_1_col_2|t_2_id|t_2_col_1|t_2_col_2|+--------+-----------+-----------+--------+-----------+-----------+|1|11|t_1_1|1|11|t_2_1||2|12|NULL|2|NULL|t_2_2||3|NULL|t_1_3|3|13|NULL||4|14|t_1_4|4|14|t_2_4||7|NULL|NULL|7|NULL|NULL|+--------+-----------+-----------+--------+-----------+-----------+5rowsinset(0.01sec)WHERE条件可以保证从结果中排除外连接右侧（右表）生成的值为NULL的行|5|15|NULL|NULL|NULL|NULL|TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---从查询执行计划看对于语句二，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_id;+----+-------------+-------+------+---------------+--------+---------+----------------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+-------+------+---------------+--------+---------+----------------+------+-------+|1|SIMPLE|t_1|ALL|NULL|NULL|NULL|NULL|6|NULL||1|SIMPLE|t_2|ref|t_2_id|t_2_id|5|tt2.t_1.t_1_id|1|NULL|+----+-------------+-------+------+---------------+--------+---------+----------------+------+-------+2rowsinset(0.00sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---从查询执行计划看被查询优化器处理后的语句为：/*select#1*/select`tt2`.`t_1`.`t_1_id`AS`t_1_id`,`tt2`.`t_1`.`t_1_col_1`AS`t_1_col_1`,`tt2`.`t_1`.`t_1_col_2`AS`t_1_col_2`,`tt2`.`t_2`.`t_2_id`AS`t_2_id`,`tt2`.`t_2`.`t_2_col_1`AS`t_2_col_1`,`tt2`.`t_2`.`t_2_col_2`AS`t_2_col_2`from`tt2`.`t_1`leftjoin`tt2`.`t_2`on((`tt2`.`t_1`.`t_1_id`=`tt2`.`t_2`.`t_2_id`))where1TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---从查询执行计划看对于语句一、语句三，它们的查询执行计划相同，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONtrueWHEREt_1_id=t_2_id;+----+-------------+-------+------+---------------+--------+---------+----------------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+-------+------+---------------+--------+---------+----------------+------+-------+|1|SIMPLE|t_1|ALL|t_1_id|NULL|NULL|NULL|6|NULL||1|SIMPLE|t_2|ref|t_2_id|t_2_id|5|tt2.t_1.t_1_id|1|NULL|+----+-------------+-------+------+---------------+--------+---------+----------------+------+-------+2rowsinset(0.06sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---从查询执行计划看被查询优化器处理后的语句为：/*select#1*/select`tt2`.`t_1`.`t_1_id`AS`t_1_id`,`tt2`.`t_1`.`t_1_col_1`AS`t_1_col_1`,`tt2`.`t_1`.`t_1_col_2`AS`t_1_col_2`,`tt2`.`t_2`.`t_2_id`AS`t_2_id`,`tt2`.`t_2`.`t_2_col_1`AS`t_2_col_1`,`tt2`.`t_2`.`t_2_col_2`AS`t_2_col_2`from`tt2`.`t_1`join`tt2`.`t_2`where(`tt2`.`t_2`.`t_2_id`=`tt2`.`t_1`.`t_1_id`)joinleftjoinTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例1：---辨析ON和WHERE的差异ONt_1_id=t_2_id：t_1_id和t_2_id进行连接WHEREt_1_id=t_2_id：

当t_1_id和t_2_id的值相等TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例2：---深入理解WHERE条件对外连接优化的影响语句四，外表的索引列出现在WHERE子句中：EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_idWHEREt_1_id>0;语句五，内表的索引列出现在WHERE子句中：EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_idWHEREt_2_id>0;TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例2：---深入理解WHERE条件对外连接优化的影响对于语句四，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_idWHEREt_1_id>0;+----+-------------+-------+------+--------+----------------+------+-------------+|id|select_type|table|type|key|ref|rows|Extra|+----+-------------+-------+------+--------+----------------+------+-------------+|1|SIMPLE|t_1|ALL|NULL|NULL|6|Usingwhere||1|SIMPLE|t_2|ref|t_2_id|tt2.t_1.t_1_id|1|NULL|+----+-------------+-------+------+--------+----------------+------+-------------+2rowsinset,1warning(0.00sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例2：---深入理解WHERE条件对外连接优化的影响对于语句四，被查询优化器处理后的语句为：/*select#1*/select`tt2`.`t_1`.`t_1_id`AS`t_1_id`,`tt2`.`t_1`.`t_1_col_1`AS`t_1_col_1`,`tt2`.`t_1`.`t_1_col_2`AS`t_1_col_2`,`tt2`.`t_2`.`t_2_id`AS`t_2_id`,`tt2`.`t_2`.`t_2_col_1`AS`t_2_col_1`,`tt2`.`t_2`.`t_2_col_2`AS`t_2_col_2`from`tt2`.`t_1`leftjoin`tt2`.`t_2`on((`tt2`.`t_2`.`t_2_id`=`tt2`.`t_1`.`t_1_id`))where(`tt2`.`t_1`.`t_1_id`>0)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例2：---深入理解WHERE条件对外连接优化的影响对于语句五，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_idWHEREt_2_id>0;+----+-------------+-------+------+--------+----------------+------+-------------+|id|select_type|table|type|key|ref|rows|Extra|+----+-------------+-------+------+--------+----------------+------+-------------+|1|SIMPLE|t_1|ALL|NULL|NULL|6|Usingwhere||1|SIMPLE|t_2|ref|t_2_id|tt2.t_1.t_1_id|1|NULL|+----+-------------+-------+------+--------+----------------+------+-------------+2rowsinset,1warning(0.00sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外连接消除示例2：---深入理解WHERE条件对外连接优化的影响对于语句五，被查询优化器处理后的语句为：/*select#1*/select`tt2`.`t_1`.`t_1_id`AS`t_1_id`,`tt2`.`t_1`.`t_1_col_1`AS`t_1_col_1`,`tt2`.`t_1`.`t_1_col_2`AS`t_1_col_2`,`tt2`.`t_2`.`t_2_id`AS`t_2_id`,`tt2`.`t_2`.`t_2_col_1`AS`t_2_col_1`,`tt2`.`t_2`.`t_2_col_2`AS`t_2_col_2`from`tt2`.`t_1`join`tt2`.`t_2`where((`tt2`.`t_2`.`t_2_id`=`tt2`.`t_1`.`t_1_id`)and

(`tt2`.`t_1`.`t_1_id`>0))TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1OuterJoinElimination外连接消除总结:1注意外连接与内连接的语义差别2外连接优化的条件：空值拒绝3外连接优化的本质：语义上是外连接，但WHER条件使得外连接可以蜕化为内连接

TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除：

去掉不必要的连接对象，则减少了连接操作连接消除的条件：

无固定模式，具体问题具体处理TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除情况一：

唯一键/主键作为连接条件，三表内连接可以去掉中间表（中间表的列只作为连接条件）CREATETABLEA(a1INTUNIQUE,a2VARCHAR(9),a3INT);CREATETABLEB(b1INTUNIQUE,b2VARCHAR(9),c2INT);CREATETABLEC(c1INTUNIQUE,c2VARCHAR(9),c3INT);B的列在WHERE条件子句中只作为等值连接条件存在，则查询可以去掉对B的连接操作：SELECTA.*,C.*FROMAJOINBON(a1=b1)JOINCON(b1=c1);相当于：SELECTA.*,C.*FROMAJOINCON(a1=c1);TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除情况二：

一些特殊形式，可以消除连接操作（可消除的表除了作为连接对象外，不出现在任何子句中）。示例：SELECTMAX(a1)FROMA,B;/*在这样格式中的MIN、MAX函数操作可以消除连接，去掉B表不影响结果；其他聚集函数不可以*/SELECTDISTINCTa3FROMA,B;/*对连接结果中的a3列执行去重操作*/SELECTa1FROMA,BGROUPBYa1;/*对连接结果中的a1列执行分组操作*/TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除情况三：

主外键关系的表进行的连接，可消除主键表，这不会影响对外键表的查询。TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例：---创建对象创建表，命令如下：CREATETABLEB(b1INT,b2VARCHAR(2),PRIMARYKEY(b1));CREATETABLEA(a1INT,a2VARCHAR(2),FOREIGNKEY(a1)REFERENCESB(b1));/*A作为外键表参照主键表B*/CREATETABLEC(c1INT,c2VARCHAR(2));插入数据，命令如下：INSERTINTOBVALUES(1,'B1');INSERTINTOBVALUES(2,'B2');INSERTINTOBVALUES(3,'B3');INSERTINTOAVALUES(1,'A1');INSERTINTOAVALUES(null,'A2');INSERTINTOAVALUES(3,'A3');INSERTINTOCVALUES(1,'C1');INSERTINTOCVALUES(2,'C2');INSERTINTOCVALUES(NULL,'C3');TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第一条SQL：三个表做内连接，但目标列不包括主键表B的对象，主键表B只作为连接对象和连接条件存在；mysql>SELECTA.*,C.*FROMA,B,CWHEREA.a1=B.b1ANDB.b1=C.c1;//主键表B作为连接对象和连接条件存在+------+------+------+------+|a1|a2|c1|c2|+------+------+------+------+|1|A1|1|C1|+------+------+------+------+1rowinset(0.05sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第一条SQL：三个表做内连接，但目标列不包括主键表B的对象，主键表B只作为连接对象和连接条件存在；

mysql>EXPLAINEXTENDEDSELECTA.*,C.*FROMA,B,CWHEREA.a1=B.b1ANDB.b1=C.c1;+----+-------------+-------+--------+---------------+---------+---------+------+|id|select_type|table|type|possible_keys|key|key_len|ref|+----+-------------+-------+--------+---------------+---------+---------+------+|1|SIMPLE|C|ALL|NULL|NULL|NULL|NULL||1|SIMPLE|B|eq_ref|PRIMARY|PRIMARY|4|test.C.c1||1|SIMPLE|A|ALL|a1|NULL|NULL|NULL|+----+-------------+-------+--------+---------------+---------+---------+------+TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第一条SQL：三个表做内连接，但目标列不包括主键表B的对象，主键表B只作为连接对象和连接条件存在；

被查询优化器处理后的语句为：/*select#1*/select`test`.`a`.`a1`AS`a1`,`test`.`a`.`a2`AS`a2`,`test`.`c`.`c1`AS`c1`,`test`.`c`.`c2`AS`c2`from`test`.`a`join`test`.`b`join`test`.`c`

where((`test`.`b`.`b1`=`test`.`c`.`c1`)and(`test`.`a`.`a1`=`test`.`c`.`c1`))TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第二条SQL：只有表A和表C进行连接，但WHERE子句多一条判定条件“A.a1ISNOTNULL”；

mysql>SELECTA.*,C.*FROMA,CWHEREA.a1=C.c1ANDA.a1ISNOTNULL;//只有表A和C进行连接+------+------+------+------+|a1|a2|c1|c2|+------+------+------+------+|1|A1|1|C1|+------+------+------+------+1rowinset(0.01sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第二条SQL：只有表A和表C进行连接，但WHERE子句多一条判定条件“A.a1ISNOTNULL”；

查询执行计划如下：mysql>EXPLAINEXTENDEDSELECTA.*,C.*FROMA,CWHEREA.a1=C.c1ANDA.a1ISNOTNULL;+----+-------------+-------+------+---------------+------+---------+-----------+|id|select_type|table|type|possible_keys|key|key_len|ref|+----+-------------+-------+------+---------------+------+---------+-----------+|1|SIMPLE|C|ALL|NULL|NULL|NULL|NULL||1|SIMPLE|A|ref|a1|a1|5|test.C.c1|+----+-------------+-------+------+---------------+------+---------+-----------+TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第二条SQL：只有表A和表C进行连接，但WHERE子句多一条判定条件“A.a1ISNOTNULL”；

被查询优化器处理后的语句为：/*select#1*/select`test`.`a`.`a1`AS`a1`,`test`.`a`.`a2`AS`a2`,`test`.`c`.`c1`AS`c1`,`test`.`c`.`c2`AS`c2`from`test`.`a`join`test`.`c`where((`test`.`a`.`a1`=`test`.`c`.`c1`)and(`test`.`c`.`c1`isnotnull))TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第三条SQL：只有表A和表C进行连接,但WHERE子句比第二条SQL的WHERE子句内容更为简单

mysql>SELECTA.*,C.*FROMA,CWHEREA.a1=C.c1;+------+------+------+------+|a1|a2|c1|c2|+------+------+------+------+|1|A1|1|C1|+------+------+------+------+1rowinset(0.00sec)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第三条SQL：只有表A和表C进行连接,但WHERE子句比第二条SQL的WHERE子句内容更为简单

mysql>EXPLAINEXTENDEDSELECTA.*,C.*FROMA,CWHEREA.a1=C.c1;+----+-------------+-------+------+---------------+------+---------+------+|id|select_type|table|type|possible_keys|key|key_len|ref|+----+-------------+-------+------+---------------+------+---------+------+|1|SIMPLE|C|ALL|NULL|NULL|NULL|NULL||1|SIMPLE|A|ALL|a1|NULL|NULL|NULL|+----+-------------+-------+------+---------------+------+---------+------+TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例1：---对主外键参照的表进行内连接，可以消除主键表

MySQL不支持第一条SQL：EXPLAINEXTENDEDSELECTA.*,C.*FROMA,B,CWHEREA.a1=B.b1ANDB.b1=C.c1;第二条SQL：EXPLAINEXTENDEDSELECTA.*,C.*FROMA,CWHEREA.a1=C.c1ANDA.a1ISNOTNULL;第三条SQL：EXPLAINEXTENDEDSELECTA.*,C.*FROMA,CWHEREA.a1=C.c1;TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例2：---对主外键参照的表进行外连接，可以消除主键表

MySQL不支持表A外连接表B，然后连接表C，查询目标列没有表B的列，表B没有被消除，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECTA.*,C.*FROMALEFTJOINBON(a1=b1)JOINCON(a1=c1);+----+-------------+-------+--------+---------------+-----------------------------------------------|id|select_type|table|type|possible_keys|Extra|+----+-------------+-------+--------+---------------+-----------------------------------------------|1|SIMPLE|C|ALL|NULL|NULL|1|SIMPLE|A|ALL|a1|Usingwhere;Usingjoinbuffer(BlockNestedL|1|SIMPLE|B|eq_ref|PRIMARY|Usingindex+----+-------------+-------+--------+---------------+-----------------------------------------------TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除示例2：---对主外键参照的表进行外连接，可以消除主键表

MySQL不支持被查询优化器处理后的语句为：/*select#1*/select`test`.`a`.`a1`AS`a1`,`test`.`a`.`a2`AS`a2`,`test`.`c`.`c1`AS`c1`,`test`.`c`.`c2`AS`c2`from`test`.`a`leftjoin`test`.`b`on((`test`.`b`.`b1`=`test`.`c`.`c1`))join`test`.`c`where(`test`.`a`.`a1`=`test`.`c`.`c1`)TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination连接消除总结：1注意连接消除与外连接消除的技术差别

连接消除去掉的是被连接的某个对象

外连接消除去掉的是外连接的语义，变形为内连接TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/3NestJoinElimination嵌套连接消除：