mysql查詢優(yōu)化的技術(shù)

1、數(shù)據(jù)庫查詢優(yōu)化技術(shù) The Query Optimization Technique Of MySQL(5.6.X)那海藍(lán)藍(lán)1數(shù)據(jù)庫查詢優(yōu)化技術(shù) 1A data technology learners, practitioners Book : 數(shù)據(jù)庫查詢優(yōu)化器的藝術(shù)：原理解析與SQL性能優(yōu)化23Blog : 4Twitter：那海藍(lán)藍(lán)Email : 5Direction : DB(PostgreSQL,MySQL.etc)63 課程大綱：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í)踐（二）-子查

2、詢的優(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ù)庫的約束規(guī)則與語義優(yōu)化9 查詢優(yōu)化技術(shù)理論與MySQL實(shí)踐（七）-非SPJ的優(yōu)化10 MySQL物理查詢優(yōu)化技術(shù)概述11 MySQL索引的利用、優(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)化技

3、術(shù)總結(jié) Lesson 2:Database Query Optimization Technology 3Query Execution Plan of MySQL4What is the Logical Query Optimization ？5 1What is the Query Optimization Technology ？2What is the Physical Query Optimization ？6 1 What is the Query Optimization Technology？1.3Parallel Query Optimization1.4Query Rewr

4、ite Rule1.1Query Reuse1.2Query Optimization AlgorithmDistributed Query Optimization1.57 1.1 Query Reuse查詢重用是指盡可能利用先前的執(zhí)行結(jié)果，以達(dá)到節(jié)約查詢計(jì)算全過程的時(shí)間并減少資源消耗的目的。目前查詢重用技術(shù)主要集中在兩個(gè)方面：1查詢結(jié)果的重用。在緩存區(qū)中分配一塊緩沖塊，存放該SQL 語句文本和最后的結(jié)果集，當(dāng)同樣的SQL輸入時(shí)，直接把結(jié)果返回。查詢結(jié)果的重用技術(shù)節(jié)約了查詢計(jì)劃生成時(shí)間，減少了查詢執(zhí)行全過程的資源消耗。2查詢計(jì)劃的重用。緩存一條查詢語句的執(zhí)行計(jì)劃及其相應(yīng)語法樹結(jié)構(gòu)。查詢計(jì)劃的

5、重用技術(shù)減少了查詢計(jì)劃生成的時(shí)間和資源消耗。8 1.1 Query Reuse查詢重用技術(shù)利弊：1 弊端，結(jié)果集很大會(huì)消耗很大的內(nèi)存資源，同樣的SQL不同用戶應(yīng)該獲取的結(jié)果集可能不同2 利端，節(jié)約了CPU和IO消耗。在實(shí)際使用的過程中，趨利避害，根據(jù)實(shí)際情況選用9 1.2 Query Rewrite查詢重寫： 是查詢語句的一種等價(jià)轉(zhuǎn)換，即對(duì)于任何相關(guān)模式的任意狀態(tài)都會(huì)產(chǎn)生相同的結(jié)果（相同的關(guān)系替代兩個(gè)表達(dá)式中相應(yīng)的關(guān)系，所得到的結(jié)果是相同的）。10 1.2 Query Rewrite查詢重寫的兩個(gè)目標(biāo)：1 將查詢轉(zhuǎn)換為等價(jià)的效率更高的形式，例如將效率低的謂詞轉(zhuǎn)換為效率高的謂詞、消除重復(fù)條件等。

6、2 盡量將查詢重寫為等價(jià)、簡(jiǎn)單且不受表順序限制的形式，為物理查詢優(yōu)化階段提供更多的選擇，如視圖的重寫、子查詢的合并轉(zhuǎn)換等。11 1.2 Query Rewrite查詢重寫的依據(jù)： 查詢重寫的依據(jù)，是關(guān)系代數(shù)(參看第1課)。1 關(guān)系代數(shù)的等價(jià)變換規(guī)則對(duì)查詢重寫提供了理論上的支持。2 查詢重寫后，查詢優(yōu)化器可能生成多個(gè)連接路徑，可以從候選者中擇優(yōu)。12 1.2 Query Rewrite查詢優(yōu)化技術(shù)類型：1 語法級(jí)。查詢語言層的優(yōu)化，基于語法進(jìn)行優(yōu)化。2 代數(shù)級(jí)。查詢使用形式邏輯進(jìn)行優(yōu)化，運(yùn)用關(guān)系代數(shù)的原理進(jìn)行優(yōu)化。3 語義級(jí)。根據(jù)完整性約束，對(duì)查詢語句進(jìn)行語義理解，推知一些可優(yōu)化的操作。4 物理

7、級(jí)。物理優(yōu)化技術(shù)，基于代價(jià)估算模型，比較得出各種執(zhí)行方式中代價(jià)最小的。 查詢重寫是基于語法級(jí)、代數(shù)級(jí)、語義級(jí)的優(yōu)化，可以統(tǒng)一歸屬到邏輯優(yōu)化的范疇：基于代價(jià)估算模型是物理層面的優(yōu)化，是從連接路徑中選擇代價(jià)最小的路徑的過程。13 1.2 Query Rewrite查詢優(yōu)化重寫思路：1 將過程性查詢轉(zhuǎn)換為描述性的查詢，如視圖重寫。2 將復(fù)雜的查詢（如嵌套子查詢、外連接消除、嵌套連接消除）盡可能轉(zhuǎn)換為多表連接查詢。3 將效率低的謂詞轉(zhuǎn)換為等價(jià)的效率高的謂詞（如等價(jià)謂詞重寫）。14 1.2 Query Rewrite查詢優(yōu)化重寫思路：利用等式和不等式的性質(zhì)，簡(jiǎn)化WHERE、HAVING和ON條件。如何改

8、進(jìn)現(xiàn)有查詢重寫規(guī)則的效率，如何發(fā)現(xiàn)更多更有效的重寫規(guī)則，是查詢優(yōu)化的研究?jī)?nèi)容之一。常見的查詢重寫技術(shù)類型，每一類都有自己的規(guī)則，這些規(guī)則沒有確定的、統(tǒng)一的規(guī)律，但重寫的核心一定是“等價(jià)轉(zhuǎn)換”，只有等價(jià)才能轉(zhuǎn)換，這是需要特別強(qiáng)調(diào)的。15 1.3 Query Optimization Algorithm什么是查詢優(yōu)化算法？查詢優(yōu)化，求解給定查詢語句的高效執(zhí)行計(jì)劃的過程。這樣的過程，包括了多種子問題求解。不同的子問題，對(duì)應(yīng)了不同的解決方法，即算法。16 1.3 Query Optimization Algorithm什么是查詢計(jì)劃？查詢計(jì)劃，也稱為查詢樹，它由一系列內(nèi)部的操作符組成，這些操作符按一定

9、的運(yùn)算關(guān)系構(gòu)成查詢的一個(gè)執(zhí)行方案。簡(jiǎn)單說，就是表A和表B先連接得到中間結(jié)果，然后再和另外的表C連接得到新的中間方式，直至所有表都被連接完畢（連接操作就是操作符，這個(gè)示例有兩個(gè)連接操作符。A連接B連接C、C連接B連接A就是兩種不同的執(zhí)行方案，即是兩個(gè)不同的執(zhí)行計(jì)劃，查詢優(yōu)化要選出最高效的一個(gè)執(zhí)行方案）。17 1.3 Query Optimization Algorithm查詢計(jì)劃的長(zhǎng)像：查詢計(jì)劃，從形式上看，是一顆二叉樹，樹葉是每個(gè)單表對(duì)象；兩個(gè)樹葉的父結(jié)點(diǎn)是一個(gè)連接操作符（如左外連接操作符，A left-out join B）連接后的中間結(jié)果（另外還有一些其他結(jié)點(diǎn)如排序操作等也可以作為中間結(jié)果

10、）這個(gè)結(jié)果是一個(gè)臨時(shí)“關(guān)系”，這樣直至根結(jié)點(diǎn)。18 1.3 Query Optimization Algorithm查詢計(jì)劃，二叉樹上的不同結(jié)點(diǎn)：?jiǎn)伪斫Y(jié)點(diǎn)?？紤]單表的數(shù)據(jù)獲取方式，是直接通過IO獲得數(shù)據(jù)，還是通過索引獲取數(shù)據(jù)，或者是通過索引定位數(shù)據(jù)的位置后再經(jīng)過IO到數(shù)據(jù)塊中獲取數(shù)據(jù)。這是一個(gè)從物理存儲(chǔ)到內(nèi)存解析成邏輯字段的過程，即符合馮諾依曼體系結(jié)構(gòu)的的要求（外存數(shù)據(jù)讀入內(nèi)存才能被處理）。19 1.3 Query Optimization Algorithm查詢計(jì)劃，二叉樹上的不同結(jié)點(diǎn)：兩表結(jié)點(diǎn)。考慮兩表以何種方式連接、代價(jià)有多大、連接路徑有哪些等。表示的是內(nèi)存中的元組，怎么進(jìn)行元組間的連接

11、。此時(shí)，元組通常已經(jīng)存在于內(nèi)存，直接使用即可。這是一個(gè)完成用戶語義的邏輯操作，但是只是局部操作，只涉及兩個(gè)具體的關(guān)系。完成用戶全部語義（用戶連接的語義），需要配合多表的連接順序的操作。不同的連接算法導(dǎo)致的連接效率不同，如數(shù)據(jù)少時(shí)可使用Hash連接，數(shù)據(jù)量大可使用嵌套連接，數(shù)據(jù)如果有序可使用歸并連接或先排序后使用歸并連接等。20 1.3 Query Optimization Algorithm查詢計(jì)劃，二叉樹上的不同結(jié)點(diǎn)：多表結(jié)點(diǎn)。考慮多表連接順序如何構(gòu)成代價(jià)最少的“執(zhí)行計(jì)劃”。決定是AB先連接還是BC先連接，這是一個(gè)比較花費(fèi)大小的運(yùn)算。如果太多的連接方式被判斷，也會(huì)導(dǎo)致效率問題。多個(gè)關(guān)系采用不

12、同次序進(jìn)行連接，花費(fèi)的CPU資源、內(nèi)存資源差異可能較大。許多數(shù)據(jù)庫采用左深樹、右深樹、緊密樹三種方式或其中一部分對(duì)多表進(jìn)行連接得到多種連接路徑。21 1.3 Query Optimization Algorithm生成最有查詢計(jì)劃的策略：1 基于規(guī)則優(yōu)化。根據(jù)經(jīng)驗(yàn)或一些已經(jīng)探知或被證明有效的方式，定義為“規(guī)則”（如根據(jù)關(guān)系代數(shù)得知的規(guī)則、根據(jù)經(jīng)驗(yàn)得知的規(guī)則等），用這些規(guī)則化簡(jiǎn)查詢計(jì)劃生成過程中符合可被化簡(jiǎn)的操作，使用啟發(fā)式規(guī)則排除一些明顯不好的存取路徑，這就是基于規(guī)則的優(yōu)化。22 1.3 Query Optimization Algorithm生成最有查詢計(jì)劃的策略：2 基于代價(jià)優(yōu)化。根據(jù)一個(gè)

13、代價(jià)評(píng)估模型，在生成查詢計(jì)劃的過程中，計(jì)算每條存取路徑（存取路徑主要包括上述三個(gè)“關(guān)系結(jié)點(diǎn)”）的花費(fèi)，然后選擇代價(jià)最小的作為子路徑，這樣直至所有表連接完畢得到一個(gè)完整的路徑。主流數(shù)據(jù)庫都采用了基于代價(jià)策略進(jìn)行優(yōu)化的技術(shù)。主流數(shù)據(jù)庫對(duì)于基于規(guī)則和基于代價(jià)的技術(shù)，都在使用！23 1.3 Query Optimization Algorithm生成最有查詢計(jì)劃的策略：主流數(shù)據(jù)庫對(duì)于基于規(guī)則和基于代價(jià)的技術(shù)，都在使用！基于規(guī)則優(yōu)化具有操作簡(jiǎn)單且能快速確定連接方式的優(yōu)點(diǎn)，但這種方法只是排除了一部分不好的可能，所以得到的結(jié)果未必是最好的；基于代價(jià)優(yōu)化，是對(duì)各種可能的情況進(jìn)行量化比較，從而可以得到花費(fèi)最小的

14、情況，但如果組合情況比較多則花費(fèi)的判斷時(shí)間就會(huì)很多；查詢優(yōu)化器的實(shí)現(xiàn)，多是兩種優(yōu)化策略組合使用，如MySQL和PostgreSQL就采取了基于規(guī)則和代價(jià)估算的查詢優(yōu)化策略。24 1.4 Parallel Query Optimization查詢優(yōu)化為什么要并行？傳統(tǒng)單機(jī)數(shù)據(jù)庫系統(tǒng)中，給定一個(gè)查詢（Query），查詢優(yōu)化算法只需找到查詢的一個(gè)具有最小執(zhí)行花費(fèi)的執(zhí)行計(jì)劃，這樣的計(jì)劃必定具有最快的響應(yīng)時(shí)間。在并行數(shù)據(jù)庫系統(tǒng)中，查詢優(yōu)化的目標(biāo)是尋找具有最小響應(yīng)時(shí)間的查詢執(zhí)行計(jì)劃，這需要把查詢工作分解為一些可以并行運(yùn)行的子工作。一些商業(yè)數(shù)據(jù)庫提供了并行查詢的功能，用以優(yōu)化查詢執(zhí)行操作。25 1.4 Pa

15、rallel Query Optimization查詢優(yōu)化并行的條件：一個(gè)查詢能否并行執(zhí)行，取決于多種因素：1 系統(tǒng)中的可用資源（如內(nèi)存、高速緩存中的數(shù)據(jù)量等）。2 CPU的數(shù)目。3 運(yùn)算中的特定代數(shù)運(yùn)算符。如A、B、C、D四個(gè)表進(jìn)行連接，每個(gè)表的單表掃描可以并行進(jìn)行；在生成四個(gè)表連接的查詢計(jì)劃過程中，可選擇A和B連接的同時(shí)C和D進(jìn)行連接，這樣連接操作能并行運(yùn)行。不同商業(yè)數(shù)據(jù)庫，對(duì)查詢并行的實(shí)現(xiàn)也不盡相同。26 1.4 Parallel Query Optimization查詢優(yōu)化并行的條件：在同一個(gè)SQL內(nèi)，查詢并行可以分為：1 操作內(nèi)并行。將同一操作如單表掃描操作、兩表連接操作、排序操作等

16、分解成多個(gè)獨(dú)立的子操作，由不同的CPU同時(shí)執(zhí)行。2 操作間并行。一條SQL查詢語句可以分解成多個(gè)子操作，由多個(gè)CPU執(zhí)行。27 1.5 Distributrd Query Optimization在分布式數(shù)據(jù)庫系統(tǒng)中, 查詢策略優(yōu)化是查詢優(yōu)化的重點(diǎn)。主要是數(shù)據(jù)傳輸策略，A、B兩結(jié)點(diǎn)的數(shù)據(jù)進(jìn)行連接，是A結(jié)點(diǎn)數(shù)據(jù)傳輸?shù)紹結(jié)點(diǎn)或是從B到A或是先各自進(jìn)行過濾然后再傳輸?shù)龋┖途植刻幚韮?yōu)化（傳統(tǒng)的單結(jié)點(diǎn)數(shù)據(jù)庫的查詢優(yōu)化技術(shù)）28 1.5 Distributrd Query Optimization在查詢優(yōu)化策略中，數(shù)據(jù)的通信開銷是優(yōu)化算法考慮的主要因素。分布式查詢優(yōu)化以減少傳輸?shù)拇螖?shù)和數(shù)據(jù)量作為查詢優(yōu)化的

17、目標(biāo)。分布式數(shù)據(jù)庫系統(tǒng)中的代價(jià)估算模型，除了考慮CPU代價(jià)和I/O代價(jià)外，還要考慮通過網(wǎng)絡(luò)在結(jié)點(diǎn)間傳輸數(shù)據(jù)的代價(jià)。這是分布式并行查詢優(yōu)化技術(shù)與傳統(tǒng)單結(jié)點(diǎn)數(shù)據(jù)庫系統(tǒng)最大不同之處。在分布式數(shù)據(jù)庫系統(tǒng)中，代價(jià)估算模型為：總代價(jià) = I/O代價(jià) + CPU代價(jià) + 通信代價(jià)29 2 What is the Logical Query Optimization ？ 關(guān)系理論+查詢重寫+啟發(fā)式規(guī)則=邏輯查詢優(yōu)化第3課-第9課重點(diǎn)講述30 2 What is the Logical Query Optimization ？ 查詢的基本操作1 選擇操作。對(duì)應(yīng)的是限制條件（格式類似“field consant”

18、，field表示列對(duì)象，op是操作符如“=”、“”等）。優(yōu)化方式是選擇操作下推，目的是盡量減少連接操作前的元組數(shù)，使得中間臨時(shí)關(guān)系盡量少（元組數(shù)少，連接得到的元組數(shù)就少），這樣可能減少IO和CPU的消耗、節(jié)約內(nèi)存空間。31 2 What is the Logical Query Optimization ？ 查詢的基本操作2 投影操作。對(duì)應(yīng)的SELECT查詢的目的列對(duì)象。優(yōu)化方式是投影操作下推。目的是盡量減少連接操作前的列數(shù)，使得中間臨時(shí)關(guān)系盡量?。ㄌ貏e注意差別：選擇操作是使元組的個(gè)數(shù)“盡量少”，投影操作是使一條元組“盡量小”），這樣雖然不能減少IO（多數(shù)數(shù)據(jù)庫存儲(chǔ)方式是行存儲(chǔ)，元組是讀取的最

19、基本單位，所以要想操作列則必須讀取一行數(shù)據(jù)），但可以各減少連接后的中間關(guān)系的元組大小，節(jié)約內(nèi)存空間。32 2 What is the Logical Query Optimization ？ 查詢的基本操作3 連接操作。對(duì)應(yīng)的是連接條件（格式類似“field_1 field_2”，field_1和field_2表示不同表上的列對(duì)象，op是操作符如“=”、“”等），表示兩個(gè)表連接的條件。33 2 What is the Logical Query Optimization ？ 查詢的基本操作3 連接操作涉及兩個(gè)子問題：3.1 多表連接中每個(gè)表被連接的順序決定著效率。如果一個(gè)查詢語句只有一個(gè)表，則

20、這樣的語句很簡(jiǎn)單；但如果有多個(gè)表，則會(huì)涉及表之間以什么樣的順序連接最高效（如A、B、C三表連接，如果ABC、ACB、BCA等連接后的結(jié)果集一樣，則哪種連接次序的效率最高，是需要考慮的問題）。3.2 多表連接每個(gè)表被連接的順序被用戶語義決定。查詢語句多表連接有著不同的語義（如是笛卡兒集、內(nèi)連接、還是外連接中的左外連接等），這決定著表之間的前后連接次序是不能隨意更換的，否則，結(jié)果集中數(shù)據(jù)是不同的。因此，表的前后連接次序是不能隨意交換的。34 2 What is the Logical Query Optimization ？ 查詢的2種類型：根據(jù)SQL語句的形式特點(diǎn)，還可以做如下區(qū)分：1 針對(duì)SP

21、J的查詢優(yōu)化?；谶x擇、投影、連接三種基本操作相結(jié)合的查詢。2針對(duì)非SPJ的查詢優(yōu)化。在SPJ的基礎(chǔ)上存在GROUPBY操作的查詢，這是一種較為復(fù)雜的查詢。所以，針對(duì)SPJ和非SPJ的查詢優(yōu)化，其實(shí)是對(duì)以上多種操作的優(yōu)化?！斑x擇”和“投影”操作，可以在關(guān)系代數(shù)規(guī)則的指導(dǎo)下進(jìn)行優(yōu)化。表連接，需要多表連接的相關(guān)算法完成優(yōu)化。其他操作的優(yōu)化多是基于索引和代價(jià)估算完成的。35 2 What is the Logical Query Optimization ？ 邏輯查詢優(yōu)化包括的技術(shù)：1 子查詢優(yōu)化2 視圖重寫3 等價(jià)謂詞重寫4 條件化簡(jiǎn)5 外連接消除6 嵌套連接消除7 連接消除8 語義優(yōu)化9 非SP

22、J的優(yōu)化36 3 What is the Physical Query Optimization ？ 代價(jià)模型+索引/索引的利用+單表掃描算法+兩表連接算法+多表連接算法=物理查詢優(yōu)化第10課-第12課重點(diǎn)講述37 4 Query Execution Plan of MySQL語法格式：EXPLAIN explain_type explainable_stmt可選項(xiàng)包括：EXTENDED | PARTITIONS | FORMAT = format_nameformat_name: TRADITIONAL | JSON說明：1 EXPLAIN命令，顯示SQL語句的查詢執(zhí)行計(jì)劃。2 EXPLAI

23、N EXTENDED命令，顯示SQL語句的詳細(xì)的查詢執(zhí)行計(jì)劃；之后可以通過“SHOW WARNINGS”命令查看詳細(xì)的信息。3 EXPLAIN PARTITIONS命令，顯示SQL語句的帶有分區(qū)表信息的查詢執(zhí)行計(jì)劃。4 EXPLAIN命令的輸出格式有兩種。 4.1 TRADITIONAL；傳統(tǒng)類型；按行隔離，每行標(biāo)識(shí)一個(gè)子操作。 4.2 JSON；JSON格式。5 explainable_stmt，可被EXPLAIN執(zhí)行的SQL語句，包括的類型有： SELECT、INSERT、UPDATE、DELETE。38 4 Query Execution Plan of MySQL1. 執(zhí)行順序執(zhí)行五表

24、連接的查詢語句如下：EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true), (t3 FULL JOIN t4 ON true), t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;39 4 Query Execution Plan of MySQL1. 執(zhí)行順序1) 從第1行到第19行，表示了完整的查詢執(zhí)行計(jì)劃。2) 第1行到第3行，表明查詢計(jì)劃的結(jié)構(gòu)；id表示對(duì)象被操作的順序；id值大，先被執(zhí)行；如果相同，執(zhí)行順序從上到下。3) 從第4行器，每一行為一個(gè)節(jié)點(diǎn)，表示本節(jié)點(diǎn)被操作對(duì)象的可用信息，如索

25、引等。4) 表的連接次序?yàn)椋簍4 t5 t3 t1 t2。這和初始給定的連接次序不同，經(jīng)過優(yōu)化，外連接被消除；5) t4表的元組數(shù)最少，按照MySQL多表連接算法，表經(jīng)過排序后，順序?yàn)?t4 t5 t2 t3 t1。6) 因?yàn)閠5、t3、t1上有索引可以利用，所以t4上的一條元組確定后，則可以利用索引之間定位t5、t3、t1表上的元組，所以第5、6、7行的key列有索引可用；ref列表明這三個(gè)表都是引用了t4表的id4列。7) t2表的數(shù)據(jù)相對(duì)較多，且又沒有索引，最后被連接，連接使用了Extra列表明的塊嵌套循環(huán)連接算法，并且使用了連接緩存。40 4 Query Execution Plan of MySQL1. 執(zhí)行順序嵌套循環(huán)算法 -MySQL塊嵌套循環(huán)算法41 4 Query Execution Plan of MySQL2. 結(jié)點(diǎn)解析1) id：每個(gè)