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

1.?“快速、精確和實(shí)現(xiàn)簡(jiǎn)單”

1.1.?三者永遠(yuǎn)只能滿足其二，必須舍掉一個(gè)

2.?排序優(yōu)化

2.1.?無(wú)論如何排序都是一個(gè)成本很高的操作，所以從性能角度考慮，應(yīng)盡可能避免排序或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序

2.2.?文件排序（filesort）

2.2.1.?MySQL需要自己進(jìn)行排序，如果數(shù)據(jù)量小則在內(nèi)存中進(jìn)行，如果數(shù)據(jù)量大則需要使用磁盤(pán)

2.2.2.?完全是在內(nèi)存中排序不需要任何磁盤(pán)文件時(shí)也是如此

2.3.?排序算法

2.3.1.?兩次傳輸排序（舊版本使用）

2.3.1.1.?讀取行指針和需要排序的字段，對(duì)其進(jìn)行排序，然后再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行

2.3.1.2.?即需要從數(shù)據(jù)表中讀取兩次數(shù)據(jù)，第二次讀取數(shù)據(jù)的時(shí)候，因?yàn)槭亲x取排序列進(jìn)行排序后的所有記錄，這會(huì)產(chǎn)生大量的隨機(jī)I/O，所以兩次傳輸排序的成本非常高

2.3.2.?單次傳輸排序（新版本使用）

2.3.2.1.?先讀取查詢所需要的所有列，然后再根據(jù)給定列進(jìn)行排序，最后直接返回排序結(jié)果

2.3.2.2.?不再需要從數(shù)據(jù)表中讀取兩次數(shù)據(jù)，對(duì)于I/O密集型的應(yīng)用來(lái)說(shuō)，這樣做的效率高了很多

2.3.2.3.?只需要一次順序I/O就可讀取所有的數(shù)據(jù)，而無(wú)須任何的隨機(jī)I/O

2.4.?MySQL在進(jìn)行文件排序時(shí)需要使用的臨時(shí)存儲(chǔ)空間可能會(huì)比想象的要大得多

2.5.?如果使用utf8mb4字符集，那么MySQL將會(huì)為每個(gè)字符預(yù)留4字節(jié)

2.6.?如果查詢中有LIMIT的話，LIMIT也會(huì)在文件排序之后應(yīng)用，所以即使需要返回較少的數(shù)據(jù)，臨時(shí)表和需要排序的數(shù)據(jù)量仍然會(huì)非常大

3.?將結(jié)果返回給客戶端

3.1.?執(zhí)行查詢的最后一個(gè)階段是將結(jié)果返回給客戶端

3.2.?MySQL將結(jié)果集返回客戶端是一個(gè)增量且逐步返回的過(guò)程

3.2.1.?服務(wù)器端無(wú)須存儲(chǔ)太多的結(jié)果，也就不會(huì)因?yàn)橐祷靥嘟Y(jié)果而消耗太多內(nèi)存

4.?MySQL查詢優(yōu)化器的局限性

4.1.?MySQL查詢優(yōu)化器只對(duì)少部分查詢不適用，而且我們往往可以通過(guò)改寫(xiě)查詢讓MySQL高效地完成工作

4.2.?UNION的限制

4.2.1.?MySQL無(wú)法將限制條件從UNION的外層“下推”到內(nèi)層，這使得原本能夠限制部分返回結(jié)果的條件無(wú)法應(yīng)用到內(nèi)層查詢的優(yōu)化上

4.2.2.?從臨時(shí)表中取出數(shù)據(jù)的順序并不是一定的，所以如果想獲得正確的順序，還需要在最后的LIMIT操作前加上一個(gè)全局的ORDER BY操作

4.3.?等值傳遞

4.3.1.?優(yōu)化器通過(guò)將列表復(fù)制到所有相關(guān)表中的相應(yīng)列來(lái)“共享”列表

4.4.?并行執(zhí)行

4.4.1.?MySQL無(wú)法利用多核特性來(lái)并行執(zhí)行查詢

5.?優(yōu)化特定類(lèi)型的查詢

5.1.?多數(shù)優(yōu)化技巧都和特定的版本有關(guān)，所以對(duì)于未來(lái)MySQL的版本未必適用

5.2.?優(yōu)化COUNT()查詢

5.2.1.?COUNT()是一個(gè)特殊的函數(shù)

5.2.1.1.?可以統(tǒng)計(jì)某列的值的數(shù)量

5.2.1.2.?可以統(tǒng)計(jì)行數(shù)

5.2.2.?用COUNT（）的時(shí)候，這種情況下通配符并不會(huì)像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上，它會(huì)忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)

5.2.2.1.?如果想要知道結(jié)果中的行數(shù)，應(yīng)該始終使用COUNT（*），這樣可以更清晰地傳達(dá)意圖，避免糟糕的性能表現(xiàn)

5.2.3.?常見(jiàn)的錯(cuò)誤之一是當(dāng)需要統(tǒng)計(jì)行數(shù)時(shí)，在COUNT()函數(shù)的括號(hào)內(nèi)指定了列名

5.2.4.?計(jì)算精確值非常復(fù)雜，而計(jì)算近似值則非常簡(jiǎn)單

5.2.4.1.?可以增加類(lèi)似Memcached這樣的外部緩存系統(tǒng)

5.3.?優(yōu)化聯(lián)接查詢

5.3.1.?確保ON或者USING子句中的列上有索引

5.3.1.1.?沒(méi)有用到的索引只會(huì)帶來(lái)額外的負(fù)擔(dān)

5.3.2.?確保任何GROUP BY和ORDER BY中的表達(dá)式只涉及一個(gè)表中的列，這樣MySQL才有可能使用索引來(lái)優(yōu)化這個(gè)過(guò)程

5.3.3.?當(dāng)升級(jí)MySQL的時(shí)候需要注意：聯(lián)接語(yǔ)法、運(yùn)算符優(yōu)先級(jí)等其他可能會(huì)發(fā)生變化的地方

5.3.4.?使用WITH ROLLUP優(yōu)化GROUP BY

5.3.4.1.?分組查詢的一個(gè)變種就是要求MySQL對(duì)返回的分組結(jié)果再做一次超級(jí)聚合

5.3.4.2.?在應(yīng)用程序中做超級(jí)聚合是更好的，雖然這需要給客戶端返回更多的結(jié)果

5.3.4.3.?最好的辦法是盡可能地將WITH ROLLUP功能轉(zhuǎn)移到應(yīng)用程序中處理

5.4.?優(yōu)化LIMIT和OFFSET子句

5.4.1.?在系統(tǒng)中需要進(jìn)行分頁(yè)操作的時(shí)候，我們通常會(huì)使用LIMIT加上偏移量的辦法實(shí)現(xiàn)，同時(shí)加上合適的ORDER BY子句

5.4.2.?在頁(yè)面中限制分頁(yè)的數(shù)量

5.4.3.?優(yōu)化大偏移量的性能

5.4.4.?盡可能地使用索引覆蓋掃描，而不是查詢所有的行

5.4.5.?將LIMIT查詢轉(zhuǎn)換為已知位置的查詢，讓MySQL通過(guò)范圍掃描獲得對(duì)應(yīng)的結(jié)果

5.4.6.?OFFSET的問(wèn)題

5.4.6.1.?會(huì)導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉

5.4.6.2.?可以使用書(shū)簽記錄上次取數(shù)據(jù)的位置，那么下次就可以直接從該書(shū)簽記錄的位置開(kāi)始掃描，這樣就可以避免使用OFFSET

5.4.6.2.1.?無(wú)論翻頁(yè)到多么靠后，其性能都會(huì)很好

5.4.6.3.?冗余表，冗余表只包含主鍵列和需要做排序的數(shù)據(jù)列

5.5.?更好的設(shè)計(jì)

5.5.1.?將具體的頁(yè)數(shù)換成“下一頁(yè)”按鈕

5.5.1.1.?假設(shè)每頁(yè)顯示20條記錄，那么我們每次查詢時(shí)都是用LIMIT返回21條記錄并只顯示20條

5.5.1.2.?如果第21條存在，那么就顯示“下一頁(yè)”按鈕，否則就說(shuō)明沒(méi)有更多的數(shù)據(jù)，也就無(wú)須顯示“下一頁(yè)”按鈕了

5.5.2.?先獲取并緩存較多的數(shù)據(jù)

5.5.2.1.?緩存1000條——然后每次分頁(yè)都從這個(gè)緩存中獲取

5.5.2.2.?如果結(jié)果集小于1000，就可以在頁(yè)面上顯示所有的分頁(yè)鏈接，因?yàn)閿?shù)據(jù)都在緩存中，所以這樣做不會(huì)對(duì)性能造成影響

5.5.2.3.?如果結(jié)果集大于1000，則可以在頁(yè)面上設(shè)計(jì)一個(gè)額外的“找到的結(jié)果多于1000條”之類(lèi)的按鈕

5.5.3.?比每次生成全部結(jié)果集再拋棄不需要的數(shù)據(jù)的效率高很多

5.6.?優(yōu)化SQL CALC FOUND ROWS

5.6.1.?在LIMIT語(yǔ)句中加上SQL_CALC_FOUND_ROWS提示（hint），這樣就可以獲得去掉LIMIT以后滿足條件的行數(shù)，因此可以作為分頁(yè)的總數(shù)

5.6.2.?加上這個(gè)提示以后，不管是否需要，MySQL都會(huì)掃描所有滿足條件的行，然后再拋棄掉不需要的行，而不是在滿足LIMIT的行數(shù)后就終止掃描

5.6.3.?該提示的代價(jià)可能非常高

5.6.4.?當(dāng)需要精確結(jié)果的時(shí)候，再單獨(dú)使用COUNT（*）來(lái)滿足需求，這時(shí)如果能夠使用索引覆蓋掃描則通常也會(huì)比SQL_CALC_FOUND_ROWS快得多

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

5.7.1.?經(jīng)常需要手工地將WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化

5.7.2.?除非你確實(shí)需要服務(wù)器消除重復(fù)的行，否則一定要使用UNION ALL

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