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

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

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

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

課程大綱：1數(shù)據(jù)庫與關系代數(shù)2數(shù)據(jù)庫查詢優(yōu)化技術總攬3查詢優(yōu)化技術理論與MySQL實踐（一）------子查詢的優(yōu)化（一）4查詢優(yōu)化技術理論與MySQL實踐（二）------子查詢的優(yōu)化（二）5查詢優(yōu)化技術理論與MySQL實踐（三）------視圖重寫與等價謂詞重寫6查詢優(yōu)化技術理論與MySQL實踐（四）------條件化簡7查詢優(yōu)化技術理論與MySQL實踐（五）------外連接消除、嵌套連接消除與連接消除8查詢優(yōu)化技術理論與MySQL實踐（六）------數(shù)據(jù)庫的約束規(guī)則與語義優(yōu)化9查詢優(yōu)化技術理論與MySQL實踐（七）------非SPJ的優(yōu)化10MySQL物理查詢優(yōu)化技術概述11MySQL索引的利用、優(yōu)化12表掃描與連接算法與MySQL多表連接優(yōu)化實踐13查詢優(yōu)化的綜合實例------TPCH實踐（一）14查詢優(yōu)化的綜合實例------TPCH實踐（二）15關系代數(shù)對于數(shù)據(jù)庫的查詢優(yōu)化的指導意義------查詢優(yōu)化技術總結

Lesson3:LogicalQueryOptimizationSubquery(1)

3HowtooptimizeSubQuery？4TypeofSubQuery？5

1WhatistheSubQuery？2WhydoesDBneedtooptimizeSubQuery？6

1WhatistheSubQuery？XX百科:當一個查詢是另一個查詢的條件時，稱之為子查詢正解：當一個查詢是另一個查詢的子部分時，稱之為子查詢（查詢語句中嵌套有查詢語句）7

1WhatistheSubQuery？查詢的子部分，包括那些情況：1目標列位置。子查詢如果位于目標列，則只能是標量子查詢，否則數(shù)據(jù)庫可能返回類似“錯誤:子查詢必須只能返回一個字段”的提示。8

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

1WhatistheSubQuery？1目標列位置。--示例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目標列位置。--示例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目標列位置。--示例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目標列位置。--示例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子句位置。相關子查詢出現(xiàn)在FROM子句中，數(shù)據(jù)庫可能返回類似“在FROM子句中的子查詢無法參考相同查詢級別中的關系”的提示，所以相關子查詢不能出現(xiàn)在FROM子句中；非相關子查詢出現(xiàn)在FROM子句中，可上拉子查詢到父層，在多表連接時統(tǒng)一考慮連接代價然后擇優(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子句中的子查詢，是一個條件表達式的一部分，而表達式可以分解為操作符和操作數(shù)；根據(jù)參與運算的不同的數(shù)據(jù)類型，操作符也不盡相同，如INT型有“>、<、=、<>”等操作，這對子查詢均有一定的要求（如INT型的等值操作，要求子查詢必須是標量子查詢）。另外，子查詢出現(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子句位置。目標列必須和GROUPBY關聯(lián)1?？蓪⒆硬樵儗懺贕ROUPBY位置處，但子查詢用在GROUPBY處沒有實用意義。23

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

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

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

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

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

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

27

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

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

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

30

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

4HowtooptimizeSubQuery？1子查詢合并（SubqueryCoalescing）。在某些條件下（語義等價：兩個查詢塊產(chǎn)生同樣的結果集），多個子查詢能夠合并成一個子查詢（合并后還是子查詢，以后可以通過其他技術消除掉子查詢）。這樣可以把多次表掃描、多次連接減少為單次表掃描和單次連接，如：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)

/*兩個ESISTS子句合并為一個，條件也進行了合并*/);32

4HowtooptimizeSubQuery？2子查詢展開（SubqueryUnnesting）。又稱子查詢反嵌套，又稱為子查詢上拉。把一些子查詢置于外層的父查詢中，作為連接關系與外層父查詢并列，其實質是把某些子查詢重寫為等價的多表連接操作（展開后，子查詢不存在了，外部查詢變成了多表連接）。帶來的好處是，有關的訪問路徑、連接方法和連接順序可能被有效使用，使得查詢語句的層次盡可能的減少。常見的IN/ANY/SOME/ALL/EXISTS依據(jù)情況轉換為半連接（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表的連接操作，相當于把t2表從子查詢中上拉了一層*/33

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