文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-709935.html

1.?MySQL的客戶端/服務(wù)器通信協(xié)議

1.1.?MySQL的客戶端和服務(wù)器之間的通信協(xié)議是“半雙工”的

1.2.?在任何時(shí)刻，要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是由客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生

1.3.?當(dāng)查詢的語(yǔ)句很長(zhǎng)的時(shí)候，參數(shù)max_allowed_packet就特別重要了

1.4.?一般的服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常很多，由多個(gè)數(shù)據(jù)包組成

1.5.?當(dāng)服務(wù)器開(kāi)始響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整地接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單地只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送數(shù)據(jù)

1.5.1.?在必要的時(shí)候一定要在查詢中加上LIMIT限制

1.6.?當(dāng)客戶端從服務(wù)器取數(shù)據(jù)時(shí)，看起來(lái)是一個(gè)拉數(shù)據(jù)的過(guò)程，但實(shí)際上是MySQL在向客戶端推送數(shù)據(jù)的過(guò)程

1.7.?MySQL通常需要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶端才能釋放這條查詢所占用的資源，所以接收全部結(jié)果并緩存通?？梢詼p少服務(wù)器的壓力，讓查詢能夠早點(diǎn)結(jié)束、早點(diǎn)釋放相應(yīng)的資源

1.8.?當(dāng)使用多數(shù)連接MySQL的庫(kù)函數(shù)從MySQL獲取數(shù)據(jù)時(shí)，其結(jié)果看起來(lái)都像是從MySQL服務(wù)器獲取數(shù)據(jù)，而實(shí)際上都是從這個(gè)庫(kù)函數(shù)的緩存獲取數(shù)據(jù)

1.9.?如果能夠盡早開(kāi)始處理這些結(jié)果集，就能大大減少內(nèi)存的消耗，在這種情況下可以不使用緩存來(lái)記錄結(jié)果而是直接處理

1.9.1.?對(duì)于服務(wù)器來(lái)說(shuō)，需要查詢完成后才能釋放資源，所以在和客戶端交互的整個(gè)過(guò)程中，服務(wù)器的資源都是被這個(gè)查詢所占用的

1.9.2.?用mysql_unbuffered_query()代替mysql_query()，PHP則不會(huì)緩存結(jié)果

2.?SHOW FULL PROCESSLIST命令

2.1.?該命令返回結(jié)果中的Command列，其就表示當(dāng)前的狀態(tài)

2.2.?Sleep

2.2.1.?線程正在等待客戶端發(fā)送新的請(qǐng)求

2.3.?Query

2.3.1.?線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端

2.4.?Locked

2.4.1.?在MySQL服務(wù)器層，該線程正在等待表鎖

2.5.?Analyzing and statistics

2.5.1.?線程正在檢查存儲(chǔ)引擎的統(tǒng)計(jì)信息，并優(yōu)化查詢

2.6.?Copying to tmp table [on disk]

2.6.1.?線程正在執(zhí)行查詢，并且將其結(jié)果集復(fù)制到一個(gè)臨時(shí)表中

2.6.2.?么是在做GROUP BY操作

2.6.3.?要么是在進(jìn)行文件排序操作

2.6.4.?或者是在進(jìn)行UNION操作

2.6.5.?“on disk”標(biāo)記，那表示MySQL正在將一個(gè)內(nèi)存臨時(shí)表放到磁盤上

2.7.?Sorting result

2.7.1.?線程正在對(duì)結(jié)果集進(jìn)行排序

3.?導(dǎo)致MySQL優(yōu)化器選擇錯(cuò)誤的執(zhí)行計(jì)劃

3.1.?統(tǒng)計(jì)信息不準(zhǔn)確

3.1.1.?MySQL服務(wù)器依賴存儲(chǔ)引擎提供的統(tǒng)計(jì)信息來(lái)評(píng)估成本，但是有的存儲(chǔ)引擎提供的信息是準(zhǔn)確的，有的偏差可能非常大

3.2.?成本指標(biāo)并不完全等同于運(yùn)行查詢的實(shí)際成本

3.3.?MySQL的最優(yōu)可能和你想的最優(yōu)不一樣

3.3.1.?MySQL只是基于其成本模型選擇最優(yōu)的執(zhí)行計(jì)劃，而有些時(shí)候這并不是最快的執(zhí)行方式

3.4.?MySQL從不考慮其他并發(fā)執(zhí)行的查詢，這可能會(huì)影響到當(dāng)前查詢的速度

3.5.?MySQL也并不是任何時(shí)候都是基于成本的優(yōu)化

3.5.1.?如果存在全文搜索的MATCH()子句，則在存在FULLTEXT索引的時(shí)候就使用全文索引

3.5.2.?即使有時(shí)候使用其他索引和WHERE條件可以遠(yuǎn)比這種方式要快，MySQL也仍然會(huì)使用對(duì)應(yīng)的全文索引

3.6.?MySQL不會(huì)考慮不受其控制的操作的成本

3.6.1.?執(zhí)行存儲(chǔ)函數(shù)或者用戶自定義函數(shù)的成本

4.?優(yōu)化策略

4.1.?靜態(tài)優(yōu)化

4.1.1.?不依賴于特別的數(shù)值

4.1.2.?在第一次完成后就一直有效，即使使用不同的參數(shù)重復(fù)執(zhí)行查詢也不會(huì)發(fā)生變化

4.1.3.?編譯時(shí)優(yōu)化

4.2.?動(dòng)態(tài)優(yōu)化

4.2.1.?和查詢的上下文有關(guān)

4.2.2.?在每次查詢的時(shí)候都重新評(píng)估

4.2.3.?運(yùn)行時(shí)優(yōu)化

5.?優(yōu)化類型

5.1.?重新定義聯(lián)接表的順序

5.1.1.?數(shù)據(jù)表的聯(lián)接并不總是按照在查詢中指定的順序進(jìn)行

5.2.?將外聯(lián)接轉(zhuǎn)化成內(nèi)聯(lián)接

5.2.1.?并不是所有的OUTER JOIN語(yǔ)句都必須以外聯(lián)接的方式執(zhí)行

5.3.?使用代數(shù)等價(jià)變換規(guī)則

5.4.?優(yōu)化COUNT()、MIN()和MAX()

5.4.1.?索引和列是否可為空通?？梢詭椭鶰ySQL優(yōu)化這類表達(dá)式

5.5.?預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式

5.5.1.?當(dāng)MySQL檢測(cè)到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候，就會(huì)一直把該表達(dá)式作為常數(shù)進(jìn)行優(yōu)化處理

5.6.?覆蓋索引掃描

5.6.1.?當(dāng)索引中的列包含所有查詢中需要使用的列的時(shí)候，MySQL就可以使用索引返回需要的數(shù)據(jù)，而無(wú)須查詢對(duì)應(yīng)的數(shù)據(jù)行

5.7.?子查詢優(yōu)化

5.7.1.?將子查詢轉(zhuǎn)換為一種效率更高的形式，從而減少多個(gè)查詢多次對(duì)數(shù)據(jù)進(jìn)行訪問(wèn)

5.8.?提前終止查詢

5.8.1.?在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候，MySQL總是能夠立刻終止查詢

5.8.2.?一個(gè)典型的例子就是當(dāng)使用了LIMIT子句的時(shí)候

5.9.?類似這種“不同值/不存在”的優(yōu)化一般可用于DISTINCT、NOT EXIST()或者LEFT JOIN類型的查詢

5.10.?⑩等值傳播

5.11.?⑾列表IN()的比較

5.11.1.?IN()完全等同于多個(gè)OR條件的子句，因?yàn)檫@兩者是完全等價(jià)的

5.11.2.?在MySQL中這點(diǎn)是不成立的，MySQL將IN()列表中的數(shù)據(jù)先進(jìn)行排序，然后通過(guò)二分查找的方式來(lái)確定列表中的值是否滿足條件，這是一個(gè)O（logn）復(fù)雜度的操作，等價(jià)地轉(zhuǎn)換成OR查詢的復(fù)雜度為O（n），對(duì)于IN()列表中有大量取值的時(shí)候，MySQL的處理速度將會(huì)更快

6.?表和索引的統(tǒng)計(jì)信息

6.1.?存儲(chǔ)引擎則給優(yōu)化器提供對(duì)應(yīng)的統(tǒng)計(jì)信息，包括：每個(gè)表或者索引有多少個(gè)頁(yè)面、每個(gè)表的每個(gè)索引的基數(shù)是多少、數(shù)據(jù)行和索引的長(zhǎng)度是多少、索引的分布信息等

7.?聯(lián)接查詢

7.1.?MySQL對(duì)任何聯(lián)接都執(zhí)行嵌套循環(huán)聯(lián)接操作，即MySQL先在一個(gè)表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套循環(huán)到下一個(gè)表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止

7.2.?在MySQL 8.0.20版本之后，已經(jīng)不再使用基于塊的嵌套循環(huán)聯(lián)接操作，取而代之的是哈希聯(lián)接

7.2.1.?這讓聯(lián)接操作性能變得更好，特別是當(dāng)數(shù)據(jù)集可以全部存儲(chǔ)在內(nèi)存時(shí)

7.3.?通常多表聯(lián)接的時(shí)候，可以有多種不同的聯(lián)接順序來(lái)獲得相同的執(zhí)行結(jié)果

7.4.?執(zhí)行計(jì)劃

7.4.1.?如果你對(duì)某個(gè)查詢執(zhí)行EXPLAIN EXTENDED后，再執(zhí)行SHOWWARNINGS，就可以看到重構(gòu)出的查詢

7.5.?讓查詢進(jìn)行更少的回溯和重讀操作

7.5.1.?可以使用STRAIGHT_JOIN關(guān)鍵字重寫(xiě)查詢，讓優(yōu)化器按照你認(rèn)為的最優(yōu)的聯(lián)接順序執(zhí)行

7.6.?n個(gè)表的聯(lián)接可能有n的階乘種聯(lián)接順序

7.6.1.?優(yōu)化器選擇使用“貪婪”搜索的方式查找“最優(yōu)”的聯(lián)接順序

7.6.2.?當(dāng)需要聯(lián)接的表超過(guò)optimizer_search_depth的限制的時(shí)候，就會(huì)選擇“貪婪”搜索模式了

7.7.?查詢不能重新排序，聯(lián)接優(yōu)化器可以利用這一點(diǎn)通過(guò)消除選擇來(lái)減小搜索空間

到了這里，關(guān)于讀高性能MySQL（第4版）筆記11_查詢性能優(yōu)化（中）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！