it教程網(wǎng)數(shù)據(jù)庫(kù)查詢優(yōu)化技術(shù)3mysql

數(shù)據(jù)庫(kù)查詢優(yōu)化技術(shù)

TheQueryOptimizationTechniqueOfMySQL(5.6.X)@那海藍(lán)藍(lán)1數(shù)據(jù)庫(kù)查詢優(yōu)化技術(shù)

1Adatatechnologylearners,practitionersBook:《數(shù)據(jù)庫(kù)查詢優(yōu)化器的藝術(shù)：原理解析與SQL性能優(yōu)化》23Blog:4Twitter：@那海藍(lán)藍(lán)Email:5Direction:DB(PostgreSQL,MySQL.etc)63

課程大綱：1數(shù)據(jù)庫(kù)與關(guān)系代數(shù)2數(shù)據(jù)庫(kù)查詢優(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í)踐（四）------條件化簡(jiǎn)7查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（五）------外連接消除、嵌套連接消除與連接消除8查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（六）------數(shù)據(jù)庫(kù)的約束規(guī)則與語(yǔ)義優(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ù)庫(kù)的查詢優(yōu)化的指導(dǎo)意義------查詢優(yōu)化技術(shù)總結(jié)

Lesson3:LogicalQueryOptimizationSubquery(1)

3HowtooptimizeSubQuery？4TypeofSubQuery？5

1WhatistheSubQuery？2WhydoesDBneedtooptimizeSubQuery？6

1WhatistheSubQuery？XX百科:當(dāng)一個(gè)查詢是另一個(gè)查詢的條件時(shí)，稱之為子查詢正解：當(dāng)一個(gè)查詢是另一個(gè)查詢的子部分時(shí)，稱之為子查詢（查詢語(yǔ)句中嵌套有查詢語(yǔ)句）7

1WhatistheSubQuery？查詢的子部分，包括那些情況：1目標(biāo)列位置。子查詢?nèi)绻挥谀繕?biāo)列，則只能是標(biāo)量子查詢，否則數(shù)據(jù)庫(kù)可能返回類似“錯(cuò)誤:子查詢必須只能返回一個(gè)字段”的提示。8

1WhatistheSubQuery？1目標(biāo)列位置。--示例CREATETABLEt1(k1INTPRIMARYKEY,c1INT);CREATETABLEt2(k2INTPRIMARYKEY,c2INT);INSERTINTOt2VALUES(1,10),(2,2),(3,30);9

1WhatistheSubQuery？1目標(biāo)列位置。--示例mysql>SELECTt1.c1,(SELECTt2.c2FROMt2)FROMt1,t2;Emptyset(0.00sec)mysql>insertintot1values(1,1),(2,2),(3,3);QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>SELECTt1.c1,(SELECTt2.c2FROMt2)FROMt1,t2;ERROR1242(21000):Subqueryreturnsmorethan1rowmysql>DELETEFROMT2;QueryOK,3rowsaffected(0.01sec)mysql>SELECTt1.c1,(SELECTt2.c2FROMt2)FROMt1,t2;Emptyset(0.00sec)10

1WhatistheSubQuery？1目標(biāo)列位置。--示例mysql>insertintot2values(1,10),(2,2),(3,30);QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>SELECTt1.c1,(SELECTt2.c2FROMt2WHEREK2=1)FROMt1,t2;+------+-----------------------------------+|c1|(SELECTt2.c2FROMt2WHEREK2=1)|+------+-----------------------------------+|1|10||2|10||3|10||1|10||2|10||3|10||1|10||2|10||3|10|+------+-----------------------------------+9rowsinset(0.00sec)11

1WhatistheSubQuery？1目標(biāo)列位置。--示例mysql>SELECTt1.c1,(SELECTt2.c2FROMt2WHEREc2=1)FROMt1,t2;+------+-----------------------------------+|c1|(SELECTt2.c2FROMt2WHEREc2=1)|+------+-----------------------------------+|1|NULL||2|NULL||3|NULL||1|NULL||2|NULL||3|NULL||1|NULL||2|NULL||3|NULL|+------+-----------------------------------+9rowsinset(0.00sec)mysql>SELECTt1.c1,(SELECTt2.c2FROMt2WHEREc2>1)FROMt1,t2;ERROR1242(21000):Subqueryreturnsmorethan1row12

1WhatistheSubQuery？1目標(biāo)列位置。--示例mysql>SELECTt1.c1,(SELECTt2.c2FROMt2WHEREc2=10)FROMt1,t2;+------+------------------------------------+|c1|(SELECTt2.c2FROMt2WHEREc2=10)|+------+------------------------------------+|1|10|......|2|10||3|10|+------+------------------------------------+9rowsinset(0.00sec)mysql>INSERTINTOt2VALUES(4,10);QueryOK,1rowaffected(0.00sec)mysql>SELECTt1.c1,(SELECTt2.c2FROMt2WHEREc2=10)FROMt1,t2;ERROR1242(21000):Subqueryreturnsmorethan1row13

1WhatistheSubQuery？查詢的子部分，包括那些情況：2FROM子句位置。相關(guān)子查詢出現(xiàn)在FROM子句中，數(shù)據(jù)庫(kù)可能返回類似“在FROM子句中的子查詢無(wú)法參考相同查詢級(jí)別中的關(guān)系”的提示，所以相關(guān)子查詢不能出現(xiàn)在FROM子句中；非相關(guān)子查詢出現(xiàn)在FROM子句中，可上拉子查詢到父層，在多表連接時(shí)統(tǒng)一考慮連接代價(jià)然后擇優(yōu)。14

1WhatistheSubQuery？2FROM子句位置。---示例mysql>SELECT*FROMt1,(SELECT*FROMt2WHEREt1.k1=t2.k2);ERROR1248(42000):Everyderivedtablemusthaveitsownaliasmysql>SELECT*FROMt1,(SELECT*FROMt2WHEREt1.k1=t2.k2)ASA_t12;ERROR1054(42S22):Unknowncolumn't1.k1'in'whereclause'15

1WhatistheSubQuery？2FROM子句位置。---示例mysql>SELECT*FROMt1,(SELECT*FROMt2);ERROR1248(42000):Everyderivedtablemusthaveitsownaliasmysql>SELECT*FROMt1,(SELECT*FROMt2)asA_t2;+----+------+----+------+|k1|c1|k2|c2|+----+------+----+------+|1|1|1|10||2|2|1|10|…...|2|2|4|10||3|3|4|10|+----+------+----+------+12rowsinset(0.00sec)16

1WhatistheSubQuery？查詢的子部分，包括那些情況：3WHERE子句位置。出現(xiàn)在WHERE子句中的子查詢，是一個(gè)條件表達(dá)式的一部分，而表達(dá)式可以分解為操作符和操作數(shù)；根據(jù)參與運(yùn)算的不同的數(shù)據(jù)類型，操作符也不盡相同，如INT型有“>、<、=、<>”等操作，這對(duì)子查詢均有一定的要求（如INT型的等值操作，要求子查詢必須是標(biāo)量子查詢）。另外，子查詢出現(xiàn)在WHERE子句中的格式，也有用謂詞指定的一些操作，如IN、BETWEEN、EXISTS等。17

1WhatistheSubQuery？3WHERE子句位置。---示例mysql>SELECT*FROMt1WHEREk1IN(SELECTk2FROMt2);+----+------+|k1|c1|+----+------+|1|1||2|2||3|3|+----+------+3rowsinset(0.00sec)18

1WhatistheSubQuery？3WHERE子句位置。---示例mysql>SELECT*FROMt1WHEREk1>=ANY(SELECTk2FROMt2);+----+------+|k1|c1|+----+------+|1|1|…...+----+------+3rowsinset(0.00sec)mysql>SELECT*FROMt1WHEREk1<=SOME(SELECTk2FROMt2);+----+------+|k1|c1|+----+------+|1|1|…...+----+------+3rowsinset(0.00sec)19

1WhatistheSubQuery？3WHERE子句位置。---示例mysql>SELECT*FROMt1WHEREk1<=ANY(SELECTk2FROMt2);+----+------+|k1|c1|+----+------+|1|1||2|2||3|3|+----+------+3rowsinset(0.00sec)20

1WhatistheSubQuery？3WHERE子句位置。---示例mysql>SELECT*FROMt1WHERENOTEXISTS(SELECTk2FROMt2WHEREk2=100);+----+------+|k1|c1|+----+------+|1|1||2|2||3|3|+----+------+3rowsinset(0.00sec)21

1WhatistheSubQuery？查詢的子部分，包括那些情況：4JOIN/ON子句位置。JOIN/ON子句可以拆分為兩部分，一是JOIN塊類似于FROM子句，二是ON子句塊類似于WHERE子句，這兩部分都可以出現(xiàn)子查詢。子查詢的處理方式同F(xiàn)ROM子句和WHERE子句。22

1WhatistheSubQuery？查詢的子部分，包括那些情況：5GROUPBY子句位置。目標(biāo)列必須和GROUPBY關(guān)聯(lián)1?？蓪⒆硬樵儗懺贕ROUPBY位置處，但子查詢用在GROUPBY處沒(méi)有實(shí)用意義。23

1WhatistheSubQuery？查詢的子部分，包括那些情況：6ORDERBY子句位置?？蓪⒆硬樵儗懺贠RDERBY位置處。但ORDERBY操作是作用在整條SQL語(yǔ)句上的，子查詢用在ORDERBY處沒(méi)有實(shí)用意義。24

2TypeofSubQuery子查詢的類型---從對(duì)象間的關(guān)系看：1相關(guān)子查詢。子查詢的執(zhí)行依賴于外層父查詢的一些屬性值。子查詢因依賴于父查詢的參數(shù)，當(dāng)父查詢的參數(shù)改變時(shí)，子查詢需要根據(jù)新參數(shù)值重新執(zhí)行（查詢優(yōu)化器對(duì)相關(guān)子查詢進(jìn)行優(yōu)化有一定意義），如：SELECT*FROMt1WHEREcol_1=ANY(SELECTcol_1FROMt2WHEREt2.col_2=t1.col_2);/*子查詢語(yǔ)句中存在父查詢的t1表的col_2列*/25

2TypeofSubQuery子查詢的類型---從對(duì)象間的關(guān)系看：2非相關(guān)子查詢。子查詢的執(zhí)行，不依賴于外層父查詢的任何屬性值。這樣子查詢具有獨(dú)立性，可獨(dú)自求解，形成一個(gè)子查詢計(jì)劃先于外層的查詢求解，如：SELECT*FROMt1WHEREcol_1=ANY(SELECTcol_1FROMt2WHEREt2.col_2=10);//子查詢語(yǔ)句中（t2）不存在父查詢（t1）的屬性26

2TypeofSubQuery子查詢的類型---從對(duì)象間的關(guān)系看：3相關(guān)子查詢與非相關(guān)子查詢的優(yōu)化對(duì)比。擴(kuò)展閱讀:相關(guān)子查詢與不相關(guān)子查詢的優(yōu)化（一）

相關(guān)子查詢與不相關(guān)子查詢的優(yōu)化（二）

相關(guān)子查詢與不相關(guān)子查詢的優(yōu)化（三）

27

2TypeofSubQuery子查詢的類型---從特定謂詞看：1[NOT]IN/ALL/ANY/SOME子查詢。語(yǔ)義相近，表示“[取反]存在/所有/任何/任何”，左面是操作數(shù)，右面是子查詢，是最常見(jiàn)的子查詢類型之一。2[NOT]EXISTS子查詢。半連接語(yǔ)義，表示“[取反]存在”，沒(méi)有左操作數(shù)，右面是子查詢，也是最常見(jiàn)的子查詢類型之一。3其他子查詢。除了上述兩種外的所有子查詢。28

2TypeofSubQuery子查詢的類型---從語(yǔ)句的構(gòu)成復(fù)雜程度看：1SPJ子查詢。由選擇、連接、投影操作組成的查詢。2GROUPBY子查詢。SPJ子查詢加上分組、聚集操作組成的查詢。3其他子查詢。GROUPBY子查詢中加上其他子句如Top-N、LIMIT/OFFSET、集合、排序等操作。后兩種子查詢有時(shí)合稱非SPJ子查詢。。29

2TypeofSubQuery子查詢的類型---從結(jié)果的角度看：1標(biāo)量子查詢。子查詢返回的結(jié)果集類型是一個(gè)簡(jiǎn)單值（returnascalar，asinglevalue）。2單行單列子查詢。子查詢返回的結(jié)果集類型是零條或一條單元組（returnazeroorsinglerow，butonlyacolumn）。相似于標(biāo)量子查詢,但可能返回零條元組。3多行單列子查詢。子查詢返回的結(jié)果集類型是多條元組但只有一個(gè)簡(jiǎn)單列（returnmultiplerows，butonlyacolumn）。4表子查詢。子查詢返回的結(jié)果集類型是一個(gè)表（多行多列）（returnatable，oneormorerowsofoneormorecolumns）。

30

3WhydoesDBneedtooptimizeSubQuery？為什么要作子查詢優(yōu)化呢？在數(shù)據(jù)庫(kù)實(shí)現(xiàn)早期，查詢優(yōu)化器對(duì)子查詢一般采用嵌套執(zhí)行的方式，即對(duì)父查詢中的每一行，都執(zhí)行一次子查詢，這樣子查詢會(huì)執(zhí)行很多次。這種執(zhí)行方式效率很低。而對(duì)子查詢進(jìn)行優(yōu)化，可能帶來(lái)幾個(gè)數(shù)量級(jí)的查詢效率的提高。子查詢轉(zhuǎn)變成為連接操作之后，會(huì)得到如下好處：1子查詢不用執(zhí)行很多次。2優(yōu)化器可以根據(jù)統(tǒng)計(jì)信息來(lái)選擇不同的連接方法和不同的連接順序。子查詢中的連接條件、過(guò)濾條件分別變成了父查詢的連接條件、過(guò)濾條件，優(yōu)化器可以對(duì)這些條件進(jìn)行下推，以提高執(zhí)行效率。31

4HowtooptimizeSubQuery？1子查詢合并（SubqueryCoalescing）。在某些條件下（語(yǔ)義等價(jià)：兩個(gè)查詢塊產(chǎn)生同樣的結(jié)果集），多個(gè)子查詢能夠合并成一個(gè)子查詢（合并后還是子查詢，以后可以通過(guò)其他技術(shù)消除掉子查詢）。這樣可以把多次表掃描、多次連接減少為單次表掃描和單次連接，如：SELECT*FROMt1WHEREa1<10AND(

EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=1)OR

EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=2));可優(yōu)化為：SELECT*FROMt1WHEREa1<10AND(

EXISTS(SELECTa2FROMt2WHEREt2.a2<5AND(t2.b2=1ORt2.b2=2)

/*兩個(gè)ESISTS子句合并為一個(gè)，條件也進(jìn)行了合并*/);32

4HowtooptimizeSubQuery？2子查詢展開（SubqueryUnnesting）。又稱子查詢反嵌套，又稱為子查詢上拉。把一些子查詢置于外層的父查詢中，作為連接關(guān)系與外層父查詢并列，其實(shí)質(zhì)是把某些子查詢重寫為等價(jià)的多表連接操作（展開后，子查詢不存在了，外部查詢變成了多表連接）。帶來(lái)的好處是，有關(guān)的訪問(wèn)路徑、連接方法和連接順序可能被有效使用，使得查詢語(yǔ)句的層次盡可能的減少。常見(jiàn)的IN/ANY/SOME/ALL/EXISTS依據(jù)情況轉(zhuǎn)換為半連接（SEMIJOIN）、普通類型的子查詢消除等情況屬于此類，如：SELECT*FROMt1,(SELECT*FROMt2WHEREt2.a2>10)v_t2WHEREt1.a1<10ANDv_t2.a2<20;可優(yōu)化為：SELECT*FROM

t1,t2WHEREt1.a1<10ANDt2.a2<20ANDt2.a2>10;/*子查詢變?yōu)榱藅1、t2表的連接操作，相當(dāng)于把t2表從子查詢中上拉了一層*/33

4HowtooptimizeSubQuery？3聚集子查詢消除（AggregateSubqueryElimination）。通常，一些系統(tǒng)支持的是標(biāo)量聚集子查詢消除。如：SELECT*FROMt1WHEREt1.a1>(SELECTavg(