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

數(shù)據(jù)庫查詢優(yōu)化技術(shù)/li_hx/TheQueryOptimizationTechniqueOfMySQL(5.6.X)@那海藍(lán)藍(lán)1數(shù)據(jù)庫查詢優(yōu)化技術(shù)/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)63/li_hx/課程大綱：1數(shù)據(jù)庫與關(guān)系代數(shù)2數(shù)據(jù)庫查詢優(yōu)化技術(shù)總攬3查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（一）------子查詢的優(yōu)化（一）4查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（二）------子查詢的優(yōu)化（二）5查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（三）------視圖重寫與等價(jià)謂詞重寫6查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（四）------條件化簡7查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（五）------外連接消除、嵌套連接消除與連接消除8查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（六）------數(shù)據(jù)庫的約束規(guī)則與語義優(yōu)化9查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（七）------非SPJ的優(yōu)化10MySQL物理查詢優(yōu)化技術(shù)概述11MySQL索引的利用、優(yōu)化12表掃描與連接算法與MySQL多表連接優(yōu)化實(shí)踐13查詢優(yōu)化的綜合實(shí)例------TPCH實(shí)踐（一）14查詢優(yōu)化的綜合實(shí)例------TPCH實(shí)踐（二）15關(guān)系代數(shù)對(duì)于數(shù)據(jù)庫的查詢優(yōu)化的指導(dǎo)意義------查詢優(yōu)化技術(shù)總結(jié)/li_hx/Lesson4:LogicalQueryOptimizationSubquery(2)

3MySQL支持哪些子查詢的優(yōu)化技術(shù)？5/li_hx/1MySQL可以優(yōu)化什么格式的子查詢？2MySQL支持對(duì)哪些類型的子查詢進(jìn)行優(yōu)化?4MySQL子查詢總結(jié)6/li_hx/1MySQL可以優(yōu)化什么格式的子查詢？MySQL支持對(duì)簡單SELECT查詢中的子查詢優(yōu)化，包括：簡單SELECT查詢中的子查詢。帶有DISTINCT、ORDERBY、LIMIT操作的簡單SELECT查詢中的子查詢。7/li_hx/1MySQL可以優(yōu)化什么格式的子查詢？CREATETABLEt1(a1INT,b1INT,PRIMARYKEY(a1));CREATETABLEt2(a2INT,b2INT,PRIMARYKEY(a2));CREATETABLEt3(a3INT,b3INT,PRIMARYKEY(a3));插入10000行與上例同樣的數(shù)據(jù)。查詢執(zhí)行計(jì)劃如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1<100ANDa1IN(SELECTa2FROMt2WHEREt2.a2>10);+----+-------------+-------+--------+---------+--------------------------+|id|select_type|table|type|key|Extra|+----+-------------+-------+--------+---------+--------------------------+|1|SIMPLE|t2|range|PRIMARY|Usingwhere;Usingindex||1|SIMPLE|t1|eq_ref|PRIMARY|NULL|+----+-------------+-------+--------+---------+--------------------------+2rowsinset,1warning(0.00sec)8/li_hx/1MySQL可以優(yōu)化什么格式的子查詢？MySQL不支持對(duì)如下情況的子查詢進(jìn)行優(yōu)化：帶有UNION操作。帶有GROUPBY、HAVING、聚集函數(shù)。使用ORDERBY中帶有LIMIT。內(nèi)表、外表的個(gè)數(shù)超過MySQL支持的最大表的連接數(shù)。9/li_hx/1MySQL可以優(yōu)化什么格式的子查詢？聚集函數(shù)操作在子查詢中，查詢執(zhí)行計(jì)劃如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1>(SELECTMIN(t2.a2)FROMt2);+----+-------------+-------+------+---------------+------------------------------+|id|select_type|table|type|possible_keys|Extra|+----+-------------+-------+------+---------------+------------------------------+|1|PRIMARY|t1|ALL|t_idx_t1|Usingwhere||2|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|+----+-------------+-------+------+---------------+------------------------------+10/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？1子查詢合并技術(shù),不支持：--例1mysql>explainEXTENDEDSELECT*FROMt1WHEREa1<4AND(EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=1)OREXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=2));+----+-------------+-------+-------+---------------+------------------------------------+|id|select_type|table|type|possible_keys|Extra|+----+-------------+-------+-------+---------------+------------------------------------+|1|PRIMARY|t1|range|t_idx_t1|Usingindexcondition||3|SUBQUERY|t2|range|t_idx_t2|Usingindexcondition;Usingwhere||2|SUBQUERY|t2|range|t_idx_t2|Usingindexcondition;Usingwhere|+----+-------------+-------+-------+---------------+------------------------------------+t2表上執(zhí)行了2次子查詢?nèi)绻С肿硬樵兒喜⒓夹g(shù),則t2表上只執(zhí)行一次子查詢11/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？1子查詢合并技術(shù),不支持：--例1被查詢優(yōu)化器處理后的語句為：/*select#1*/select`t1`.`id1`AS`id1`,`t1`.`a1`AS`a1`,

`t1`.`b1`AS`b1`from`t1`where((`t1`.`a1`<4)and

(exists(/*select#2*/select`t2`.`a2`//沒有和條件“b2=2”合并在一起判斷，而是一個(gè)獨(dú)立結(jié)點(diǎn)

from`t2`where((`t2`.`a2`<5)and(`t2`.`b2`=1)))

or

exists(/*select#3*/select`t2`.`a2`//沒有和條件“b2=1”合并在一起判斷，而是一個(gè)獨(dú)立結(jié)點(diǎn)

from`t2`where((`t2`.`a2`<5)and(`t2`.`b2`=2)))

))12/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？1子查詢合并技術(shù),不支持：--例1SQL語句的語義等價(jià)于上一條SQL語句，查詢執(zhí)行計(jì)劃如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREa1<10ANDEXISTS(SELECTa2FROMt2WHEREt2.a2<5AND(t2.b2=1ORt2.b2=2));+----+-------------+-------+------+------+-------------+|id|select_type|table|type|key|Extra|+----+-------------+-------+------+------+-------------+|1|PRIMARY|t1|ALL|NULL|Usingwhere||2|SUBQUERY|t2|ALL|NULL|Usingwhere|+----+-------------+-------+------+------+-------------+2rowsinset,1warning(0.02sec)人為的合并查詢條件為“(t2.b2=1ORt2.b2=2)”t2表上的子查詢，只執(zhí)行一次13/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？1子查詢合并技術(shù),不支持：--例1被查詢優(yōu)化器處理后的語句為：/*select#1*/select`t1`.`id1`AS`id1`,`t1`.`a1`AS`a1`,`t1`.`b1`AS`b1`from`t1`where((`t1`.`a1`<10)and

exists(/*select#2*/select`t2`.`a2`

from`t2`

where((`t2`.`a2`<5)and((`t2`.`b2`=1)or(`t2`.`b2`=2)))))人為的合并查詢條件為“(t2.b2=1ORt2.b2=2)”t2表上的子查詢，只執(zhí)行一次14/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例2mysql>EXPLAINEXTENDEDSELECT*FROMt1,(SELECT*FROMt2WHEREt2.a2>10)v_t2WHEREt1.a1<10ANDv_t2.a2<20;+----+-------------+------------+------+------+----------------------------------------------------+|id|select_type|table|type|key|Extra|+----+-------------+------------+------+------+----------------------------------------------------+|1|PRIMARY|<derived2>|ALL|NULL|Usingwhere||1|PRIMARY|t1|ALL|NULL|Usingwhere;Usingjoinbuffer(BlockNestedLoop)||2|DERIVED|t2|ALL|NULL|Usingwhere|+----+-------------+------------+------+------+----------------------------------------------------+3rowsinset,1warning(0.00sec)從查詢執(zhí)行計(jì)劃看，在表t2上的子查詢被單獨(dú)執(zhí)行，沒和表t1進(jìn)行了嵌套循環(huán)連接，子查詢沒有被消除，所以MySQL支持子查詢反嵌套技術(shù)有限（需要對(duì)比例3和例4）15/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例2被查詢優(yōu)化器處理后的語句為：/*select#1*/select`test`.`t1`.`id1`AS`id1`,`test`.`t1`.`a1`AS`a1`,

`test`.`t1`.`b1`AS`b1`,`v_t2`.`id2`AS`id2`,`v_t2`.`a2`AS`a2`,

`v_t2`.`b2`AS`b2`from`test`.`t1`join(

/*select#2*/select`test`.`t2`.`id2`AS`id2`,`test`.`t2`.`a2`AS`a2`,

`test`.`t2`.`b2`AS`b2`

from`test`.`t2`

where(`test`.`t2`.`a2`>10))`v_t2`where((`test`.`t1`.`a1`<10)and(`v_t2`.`a2`<20))從查詢執(zhí)行計(jì)劃看，在表t2上的子查詢被單獨(dú)執(zhí)行，參看/*select#2*/16/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例3再看一個(gè)IN子查詢的例子，查詢執(zhí)行計(jì)劃如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1<100ANDa1IN(SELECTa2FROMt2WHEREt2.a2>10);+----+--------------+-------------+------+------+----------------------------------------------------+|id|select_type|table|type|key|Extra|+----+--------------+-------------+------+------+----------------------------------------------------+|1|SIMPLE|<subquery2>|ALL|NULL|Usingwhere||1|SIMPLE|t1|ALL|NULL|Usingwhere;Usingjoinbuffer(BlockNestedLoop)||2|MATERIALIZED|t2|ALL|NULL|Usingwhere|+----+--------------+-------------+------+------+----------------------------------------------------+3rowsinset,1warning(0.00sec)從查詢執(zhí)行計(jì)劃看，在表t2上的子查詢被物化17/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例3被查詢優(yōu)化器處理后的語句為：/*select#1*/select`t1`.`id1`AS`id1`,`t1`.`a1`AS`a1`,

`t1`.`b1`AS`b1`from`t1`where((`t1`.`a1`<100)and

`t1`.`a1`in(/*select#2*/

select`t2`.`a2`

from`t2`

where(`t2`.`a2`>10)))從查詢執(zhí)行計(jì)劃看，子查詢被物化，但沒有被上拉到頂層與t1進(jìn)行連接。從查詢執(zhí)行計(jì)劃看，在表t2上的子查詢被物化18/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例4重新構(gòu)造表如下，與上面示例不同之處在于這里所用列是主鍵列。CREATETABLEt1(a1INT,b1INT,PRIMARYKEY(a1));CREATETABLEt2(a2INT,b2INT,PRIMARYKEY(a2));CREATETABLEt3(a3INT,b3INT,PRIMARYKEY(a3));插入10000行與上例同樣的數(shù)據(jù)19/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例4執(zhí)行與例3同樣的查詢語句，查詢執(zhí)行計(jì)劃如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1<100ANDa1IN(SELECTa2FROMt2WHEREt2.a2>10);+----+-------------+-------+--------+---------+--------------------------+|id|select_type|table|type|key|Extra|+----+-------------+-------+--------+---------+--------------------------+|1|SIMPLE|t2|range|PRIMARY|Usingwhere;Usingindex||1|SIMPLE|t1|eq_ref|PRIMARY|NULL|+----+-------------+-------+--------+---------+--------------------------+2rowsinset,1warning(0.00sec)20/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例4執(zhí)行與例3同樣的查詢語句，查詢執(zhí)行計(jì)劃如下：被查詢優(yōu)化器處理后的語句為：/*select#1*/select`tt`.`t1`.`a1`AS`a1`,`tt`.`t1`.`b1`AS`b1`,

`tt`.`t1`.`c1`AS`c1`from`tt`.`t2`join`tt`.`t1`where((`tt`.`t1`.`a1`=`tt`.`t2`.`a2`)and(`tt`.`t2`.`a2`<100)and(`tt`.`t2`.`a2`>10)從查詢執(zhí)行計(jì)劃看，子查詢不存在，SQL語句被轉(zhuǎn)換為內(nèi)連接操作，這表明MySQL只有在針對(duì)主鍵列進(jìn)行類似的子查詢時(shí)，才把子查詢上拉為內(nèi)連接。所以，MySQL還是支持子查詢展開技術(shù)的。21/li_hx/2MySQL支持哪些子查詢的優(yōu)化技術(shù)？2子查詢展開（子查詢反嵌套）技術(shù),支持得不夠好：--例4執(zhí)行與例3同樣的查詢語句，查詢執(zhí)行計(jì)劃如下：被查詢優(yōu)化器處理后的語句為：/*select#1*/select`tt`.`t1`.`a1`AS`a1`,`tt`.`t1`.`b1`AS`b1`,

`tt`.`t1`.`c1`AS`c1`from`tt`.`t2`join`tt`.`t1`where((`tt`.`t1`.`a1`=`tt`.`t2`.`a2`)and(`tt`.`t2`.`a2`<100)and(`tt`.`t2