MySQL的查詢優(yōu)化技術(shù)-07

MySQL數(shù)據(jù)庫查詢優(yōu)化技術(shù)第7周法律聲明【聲明】本視頻和幻燈片為煉數(shù)成金網(wǎng)絡(luò)課程的教學(xué)資料，所有資料只能在課程內(nèi)使用，不得在課程以外范圍散播，違者將可能被追究法律和經(jīng)濟(jì)責(zé)任。課程詳情訪問煉數(shù)成金培訓(xùn)網(wǎng)站TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1Adatatechnologylearners,practitionersBook:《數(shù)據(jù)庫查詢優(yōu)化器的藝術(shù)：原理解析與SQL性能優(yōu)化》23Blog:/li_hx4Twitter：@那海藍(lán)藍(lán)Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)6課程大綱：1數(shù)據(jù)庫與關(guān)系代數(shù)2數(shù)據(jù)庫查詢優(yōu)化技術(shù)總攬3查詢優(yōu)化技術(shù)理論與MySQL實踐（一）------子查詢的優(yōu)化（一）4查詢優(yōu)化技術(shù)理論與MySQL實踐（二）------子查詢的優(yōu)化（二）5查詢優(yōu)化技術(shù)理論與MySQL實踐（三）------視圖重寫與等價謂詞重寫6查詢優(yōu)化技術(shù)理論與MySQL實踐（四）------條件化簡7查詢優(yōu)化技術(shù)理論與MySQL實踐（五）------外連接消除、嵌套連接消除與連接消除8查詢優(yōu)化技術(shù)理論與MySQL實踐（六）------數(shù)據(jù)庫的約束規(guī)則與語義優(yōu)化9查詢優(yōu)化技術(shù)理論與MySQL實踐（七）------非SPJ的優(yōu)化10MySQL物理查詢優(yōu)化技術(shù)概述、表掃描與連接算法與MySQL多表連接優(yōu)化實踐11MySQL索引的利用、優(yōu)化12查詢優(yōu)化的綜合實例------TPCH實踐（一）13查詢優(yōu)化的綜合實例------TPCH實踐（二）14關(guān)系代數(shù)對于數(shù)據(jù)庫的查詢優(yōu)化的指導(dǎo)意義------查詢優(yōu)化技術(shù)總結(jié)15MySQL的其它的一些優(yōu)化技術(shù)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?內(nèi)連接外連接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左外連接內(nèi)連接TheQueryOptimizationTechniqueOfMySQL(5.6.X)

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

左向外連接的結(jié)果集包括：LEFTOUTER子句中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。2）RIGHTJOIN/RIGHTOUTERJOIN：右外連接

右向外連接是左向外聯(lián)接的反向連接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。3）FULLJOIN/FULLOUTERJOIN：全外連接全外連接返回左表和右表中的所有行。當(dāng)某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結(jié)果集行包含基表的數(shù)據(jù)值。

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?外連接消除：把外連接變?yōu)閮?nèi)連接AOUTERJOINB變形為AJOINBTheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.3WhydoesOuterJoinElimination?外連接消除的意義：1查詢優(yōu)化器在處理外連接操作時所需執(zhí)行的操作和時間多于內(nèi)連接2外連接消除后，優(yōu)化器在選擇多表連接順序時，可以有更多更靈活的選擇，從而可以選擇更好的表連接順序，加快查詢執(zhí)行的速度3表的一些連接算法（如塊嵌套連接和索引循環(huán)連接等）在將規(guī)模小的或篩選條件最嚴(yán)格的表作為“外表”（放在連接順序的最前面，是多層循環(huán)體的外循環(huán)層），可以減少不必要的I/O開銷，能加快算法執(zhí)行的速度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條件可以保證從結(jié)果中排除外連接右側(cè)（右表）生成的值為NULL的行（即條件確保應(yīng)用在右表帶有空值的列對象上時，條件不滿足，條件的結(jié)果值為FLASE或UNKONOWEN，這樣右表就不會有值為NULL的行生成），所以能使該查詢在語義上等效于內(nèi)連接。explainSELECT*FROMXLEFTJOINYON(X.X_num=Y.Y_num)WHEREY.Y_numISNOTNULL;TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外連接消除示例1：創(chuàng)建表，命令如下：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));插入數(shù)據(jù)，命令如下：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：---從執(zhí)行結(jié)果看對于語句二，查詢執(zhí)行結(jié)果如下：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：---從執(zhí)行結(jié)果看對于語句一、語句三，查詢執(zhí)行結(jié)果如下：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條件可以保證從結(jié)果中排除外連接右側(cè)（右表）生成的值為NULL的行|5|15|NULL|NULL|NULL|NULL|TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外連接消除示例1：---從查詢執(zhí)行計劃看對于語句二，查詢執(zhí)行計劃如下：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：---從查詢執(zhí)行計劃看被查詢優(yōu)化器處理后的語句為：/*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：---從查詢執(zhí)行計劃看對于語句一、語句三，它們的查詢執(zhí)行計劃相同，查詢執(zhí)行計劃如下：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：---從查詢執(zhí)行計劃看被查詢優(yōu)化器處理后的語句為：/*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進(jìn)行連接WHEREt_1_id=t_2_id：

當(dāng)t_1_id和t_2_id的值相等TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外連接消除示例2：---深入理解WHERE條件對外連接優(yōu)化的影響語句四，外表的索引列出現(xiàn)在WHERE子句中：EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_idWHEREt_1_id>0;語句五，內(nèi)表的索引列出現(xiàn)在WHERE子句中：EXPLAINEXTENDEDSELECT*FROMt_1LEFTJOINt_2ONt_1_id=t_2_idWHEREt_2_id>0;TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/1.4HowtodoOuterJoinEliminationforMySQL?外連接消除示例2：---深入理解WHERE條件對外連接優(yōu)化的影響對于語句四，查詢執(zhí)行計劃如下：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條件對外連接優(yōu)化的影響對于語句四，被查詢優(yōu)化器處理后的語句為：/*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條件對外連接優(yōu)化的影響對于語句五，查詢執(zhí)行計劃如下：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條件對外連接優(yōu)化的影響對于語句五，被查詢優(yōu)化器處理后的語句為：/*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外連接消除總結(jié):1注意外連接與內(nèi)連接的語義差別2外連接優(yōu)化的條件：空值拒絕3外連接優(yōu)化的本質(zhì)：語義上是外連接，但WHER條件使得外連接可以蛻化為內(nèi)連接

TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination連接消除：

去掉不必要的連接對象，則減少了連接操作連接消除的條件：

無固定模式，具體問題具體處理TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination連接消除情況一：

唯一鍵/主鍵作為連接條件，三表內(nèi)連接可以去掉中間表（中間表的列只作為連接條件）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);相當(dāng)于：SELECTA.*,C.*FROMAJOINCON(a1=c1);TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination連接消除情況二：

一些特殊形式，可以消除連接操作（可消除的表除了作為連接對象外，不出現(xiàn)在任何子句中）。示例：SELECTMAX(a1)FROMA,B;/*在這樣格式中的MIN、MAX函數(shù)操作可以消除連接，去掉B表不影響結(jié)果；其他聚集函數(shù)不可以*/SELECTDISTINCTa3FROMA,B;/*對連接結(jié)果中的a3列執(zhí)行去重操作*/SELECTa1FROMA,BGROUPBYa1;/*對連接結(jié)果中的a1列執(zhí)行分組操作*/TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination連接消除情況三：

主外鍵關(guān)系的表進(jìn)行的連接，可消除主鍵表，這不會影響對外鍵表的查詢。TheQueryOptimizationTechniqueOfMySQL(5.6.X)

/li_hx/2JoinElimination連接消除示例：---創(chuàng)建對象創(chuàng)建表，命令如下：CREATETABLEB(b1INT,b2VARCHAR(2),PRIMARYKEY(b1));CREATETABLEA(a1INT,a2VARCHAR(2),FOREIGNKEY(a1)REFERENCESB(b1));/*A作為外鍵表參照主鍵表B*/CREATETABLEC(c1INT,c2VARCHAR(2));插入數(shù)據(jù)，命令如下：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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第一條SQL：三個表做內(nèi)連接，但目標(biāo)列不包括主鍵表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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第一條SQL：三個表做內(nèi)連接，但目標(biāo)列不包括主鍵表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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第一條SQL：三個表做內(nèi)連接，但目標(biāo)列不包括主鍵表B的對象，主鍵表B只作為連接對象和連接條件存在；

被查詢優(yōu)化器處理后的語句為：/*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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第二條SQL：只有表A和表C進(jìn)行連接，但WHERE子句多一條判定條件“A.a1ISNOTNULL”；

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

/li_hx/2JoinElimination連接消除示例1：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第二條SQL：只有表A和表C進(jìn)行連接，但WHERE子句多一條判定條件“A.a1ISNOTNULL”；

查詢執(zhí)行計劃如下：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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第二條SQL：只有表A和表C進(jìn)行連接，但WHERE子句多一條判定條件“A.a1ISNOTNULL”；

被查詢優(yōu)化器處理后的語句為：/*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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第三條SQL：只有表A和表C進(jìn)行連接,但WHERE子句比第二條SQL的WHERE子句內(nèi)容更為簡單

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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

MySQL不支持第三條SQL：只有表A和表C進(jìn)行連接,但WHERE子句比第二條SQL的WHERE子句內(nèi)容更為簡單

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：---對主外鍵參照的表進(jìn)行內(nèi)連接，可以消除主鍵表

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：---對主外鍵參照的表進(jìn)行外連接，可以消除主鍵表

MySQL不支持表A外連接表B，然后連接表C，查詢目標(biāo)列沒有表B的列，表B沒有被消除，查詢執(zhí)行計劃如下：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：---對主外鍵參照的表進(jìn)行外連接，可以消除主鍵表

MySQL不支持被查詢優(yōu)化器處理后的語句為：/*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連接消除總結(jié)：1注意連接消除與外連接消除的技術(shù)差別

連接消除去掉的是被連接的某個對象

外連接消除去掉的是外連接的語義，變形為內(nèi)連接TheQueryOptimizationTechniqueOfMySQL(5.6.X)

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