高性能MySQL-3rd-（六）查询性能优化
2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>
/*?*??--------------------------------------------------------????????????*?????高性能MySQL-3rd-Baron?Schwartz-筆記?????????????*?????第六章?查詢性能優(yōu)化????*/??--------------------------------------------------------======================================================
? ? 6.1 為什么查詢速度會(huì)變慢
? ? 查詢有生命周期大致順序：從客戶端，到服務(wù)器，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃，執(zhí)行，返回結(jié)果給客戶端，其中執(zhí)行是最重要的階段。包括了大量的檢索數(shù)據(jù)到存儲(chǔ)引擎的調(diào)用，調(diào)用后的數(shù)據(jù)處理，分組和排序。
? ? 查詢?cè)诿總€(gè)部分都會(huì)花費(fèi)時(shí)間，包括網(wǎng)絡(luò)、CPU計(jì)算、生成執(zhí)行計(jì)劃、鎖等待（互斥等待），尤其是向存儲(chǔ)引擎調(diào)用操作，這些調(diào)用需要在內(nèi)存操作、在CPU操作、內(nèi)存不足時(shí)導(dǎo)致I/O操作。
? ? 了解以上就可能知道查詢速度會(huì)變慢的思考角度。
======================================================
? ? 6.2 慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問
? ? 優(yōu)化數(shù)據(jù)訪問，就是優(yōu)化訪問的數(shù)據(jù)，操作對(duì)象是要訪問的數(shù)據(jù)，兩方面，是否向服務(wù)器請(qǐng)求了大量不需要的數(shù)據(jù)，二是是否逼迫MySQL掃描額外的記錄（沒有必要掃描）。
? ? 請(qǐng)求不需要數(shù)據(jù)的典型案例：不加LIMIT（返回全部數(shù)據(jù)，只取10條）、多表關(guān)聯(lián)Select * 返回全部列（多表關(guān)聯(lián)查詢時(shí)*返回多個(gè)表的全部列）、還是Select *（可能寫程序方面或代碼復(fù)用方面有好處，但還要權(quán)衡）、重復(fù)查詢相同數(shù)據(jù)（真需要這樣，可以緩存下來，移動(dòng)開發(fā)這個(gè)很有必要本地存儲(chǔ)）。
? ? 標(biāo)志額外掃描的三個(gè)指標(biāo)：響應(yīng)時(shí)間（自己判斷是否合理值）、掃描的行數(shù)、返回的行數(shù)，一般掃描行數(shù)>返回行數(shù)。
? ? 掃描的行數(shù)需要與一個(gè)“訪問類型”概念關(guān)聯(lián)，就是 Explain 中的 type，explain的type結(jié)果由差到優(yōu)分別是：ALL（全表掃描）、index（索引掃描）、range（范圍掃描）、ref（唯一索引查詢 key_col=xx）、const（常數(shù)引用）等。從“訪問類型”可以明白，索引讓 MySQL 以最高效、掃描行數(shù)最少的方式找到需要的記錄。
? ? 書中有個(gè)例子，說明在where中使用已是索引的列和取消該列的索引后兩種結(jié)果，type由ref變?yōu)锳ll，預(yù)估要訪問的rows從10變?yōu)?073，差異非常明顯。
======================================================
? ? 6.3 重構(gòu)查詢的方式
? ? 第一：將一個(gè)復(fù)雜查詢拆分為數(shù)個(gè)小且簡(jiǎn)單的查詢，數(shù)據(jù)返回也快。
? ? 第二：切分查詢，如刪除10萬條數(shù)據(jù)，可以切分為10次，每次刪除1萬條。
? ? 第三：分解關(guān)聯(lián)查詢：
? ? 以上做法好處是，充分利用前一步緩存，減少鎖競(jìng)爭(zhēng)，in(123, 456,...)也更高效，減少冗余記錄，等等。
======================================================
? ? 6.4 查詢執(zhí)行的基礎(chǔ)（知識(shí)）
? ? MySQL執(zhí)行查詢執(zhí)行路徑，如下圖，關(guān)鍵要解釋的是 客戶端發(fā)送請(qǐng)求，如果查詢緩存有結(jié)果，則直接返回。
? ?
? ? 客戶端/服務(wù)器通訊協(xié)議，具體細(xì)節(jié)不關(guān)注，只知道它是“半雙工”工作，要么客戶端向服務(wù)器發(fā)送數(shù)據(jù)，要么服務(wù)器向客戶端發(fā)送數(shù)據(jù)，兩個(gè)動(dòng)作不會(huì)同時(shí)發(fā)生；另外，發(fā)送數(shù)據(jù)都只有發(fā)送完成后才能動(dòng)作，這就是為什么要加LIMIT。
? ? 另外，注意當(dāng)客戶端從服務(wù)器獲取數(shù)據(jù)時(shí)，看起來是從服務(wù)器獲取數(shù)據(jù)，實(shí)際上是從庫函數(shù)的緩存中獲取數(shù)據(jù)，想想PHP的 mysql_query()，此時(shí)數(shù)據(jù)已經(jīng)到了PHP的緩存中，而mysql_unbuffered_query()不會(huì)緩存結(jié)果。
? ? MySQL中的關(guān)聯(lián)（join）查詢，總體來說，MySQL認(rèn)為任何一個(gè)查詢都是一次關(guān)聯(lián)，不光是查詢兩個(gè)表匹配才叫關(guān)聯(lián)。所以，理解MySQL如何執(zhí)行關(guān)聯(lián)查詢至關(guān)重要。MySQL的關(guān)聯(lián)是：嵌套循環(huán)關(guān)聯(lián)，舉例如下：
? ? 結(jié)合書中關(guān)于多表關(guān)聯(lián)的案例，參考一個(gè)實(shí)際例子 《MySQL SQL優(yōu)化之 STRAIGHT_JOIN》 全面介紹優(yōu)化過程。
? ? 排序優(yōu)化，不管怎么樣，從性能角度，應(yīng)該盡可能避免排序，或者盡可能避免對(duì)大量數(shù)據(jù)進(jìn)行排序。第三章講了索引排序，快速，當(dāng)不能直接使用索引時(shí)，MySQL就會(huì)自己進(jìn)行排序，數(shù)據(jù)量小時(shí)在內(nèi)存中排序，數(shù)據(jù)量大時(shí)使用到磁盤。量小于“排序緩沖區(qū)”時(shí)，MySQL使用內(nèi)存進(jìn)行“快速排序”。如果內(nèi)存不夠，MySQL先將數(shù)據(jù)分塊，每塊使用快速排序，然后將各塊結(jié)果放在硬盤上，然后合并（merge），最后返回排序結(jié)果。
? ? 注意：MySQL排序過程統(tǒng)稱為文件排序（filesort），概念上的，即使排序發(fā)生在內(nèi)存，而不是磁盤文件中。
? ? MySQL有兩種排序算法，兩次傳輸排序（舊版）、單次傳輸排序（新版）。兩種各有各的最好和最差的應(yīng)用場(chǎng)景，注意 max_length_for_sort_data 是臨界值，不超過時(shí)使用單次傳輸，超過使用兩次傳輸。MySQL自動(dòng)判斷，具體參考第八章中“文件排序優(yōu)化”。
? ? 兩次傳輸排序（舊版），讀取行指針和需要排序的字段，對(duì)其進(jìn)行排序，然后再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行。顯然是兩次傳輸，特別是讀取排序后的數(shù)據(jù)時(shí)（第二次）大量隨機(jī)I/O，所以兩次傳輸成本高。
? ? 單次傳輸排序（新版），一次讀取出所有需要的或SQL查詢指定的列，然后根據(jù)排序列，排序，直接返回排序后的結(jié)果。順序I/O，缺點(diǎn)：如果列多，額外占用空間。
? ? 注意：MySQL排序時(shí)使用的空間比想象大很多，為什么？因?yàn)镸ySQL要為每一個(gè)排序記錄分配足夠長(zhǎng)的空間存放，VARCHAR滿長(zhǎng)度（聲明的完整長(zhǎng)度），使用UTF8字符集時(shí)，為每個(gè)字符預(yù)留3個(gè)字節(jié)。所以會(huì)很大！
? ? 結(jié)合關(guān)聯(lián)查詢，排序會(huì)更復(fù)雜。如果ORDER BY排序列都在第一個(gè)表（驅(qū)動(dòng)表），那么在關(guān)聯(lián)處理時(shí)，先對(duì)驅(qū)動(dòng)表排序，Explain結(jié)果中Extra會(huì)有Using filesort；除此之外所有情況，都會(huì)在關(guān)聯(lián)結(jié)束后，將結(jié)果放在臨時(shí)表中進(jìn)行最終排序，Extra中會(huì)有Using temporary;Using filesort。如果還有LIMIT，也會(huì)在排序后應(yīng)用。可以，排序需要的空間大！
? ? 注意：MySQL5.6以后，有所優(yōu)化，如果有LIMIT會(huì)只排序需要的，而不是所有，拋棄不滿足條件的結(jié)果。
? ?查詢執(zhí)行引擎，相對(duì)于查詢優(yōu)化，查詢執(zhí)行簡(jiǎn)單些了，MySQL只根據(jù)執(zhí)行計(jì)劃輸出的指令逐步執(zhí)行。指令都是調(diào)用存儲(chǔ)引擎的API來完成，一般稱為 handler API，實(shí)際上，MySQL優(yōu)化階段為每個(gè)表都創(chuàng)建了一個(gè) handler 實(shí)例，（類似于VC++編程中的句柄？），用 handler 實(shí)例獲取表的信息（列名、索引統(tǒng)計(jì)信息等）。
? ? 注意：存儲(chǔ)引擎接口不豐富，底層僅幾十個(gè)，但功能豐富！如某接口實(shí)現(xiàn)了查詢第一行，又有一個(gè)接口實(shí)現(xiàn)了查詢下一行，有了這兩個(gè)就可以全表掃描了！
? ? 返回結(jié)果給客戶端，有結(jié)果集返回結(jié)果集，沒結(jié)果，返回影響的行數(shù)。一般MySQL也會(huì)將這個(gè)結(jié)果緩存下來，存放到查詢緩存中。
? ? 注意：MySQL返回結(jié)果是一個(gè)增量、逐步返回的過程，例如，關(guān)聯(lián)操作中，當(dāng)一個(gè)嵌套循環(huán)處理到最后一個(gè)關(guān)聯(lián)表，并開始生成第一條結(jié)果時(shí)，MySQL就可以開始向客戶端逐步返回結(jié)果集了。好處：服務(wù)器端無須存儲(chǔ)太多結(jié)果，也不會(huì)因?yàn)榉祷氐慕Y(jié)果太多而消耗太多內(nèi)存，也使客戶端第一時(shí)間獲得返回結(jié)果。結(jié)果是以TCP協(xié)議封包發(fā)送的，TCP的傳輸過程，可能會(huì)對(duì)封包進(jìn)行緩存然后批量發(fā)送。
======================================================
? ? 6.5 MySQL查詢優(yōu)化器的局限性
? ? 不熟悉 JOIN USING 和 JOIN ON 的請(qǐng)看 紅薯作品?MySQL 三種關(guān)聯(lián)查詢的方式: ON vs USING vs 傳統(tǒng)風(fēng)格
? ? 一個(gè)是關(guān)聯(lián)子查詢，沒看明白，回來再讀。
? ? 一個(gè)UNION限制，無法將限制條件從外層下推到內(nèi)層，改造例子如下
? ? 等值傳遞：講的IN列表，MySQL會(huì)將IN列表的值傳到各個(gè)過濾子句，如果IN列表太大，會(huì)造成額外消耗，優(yōu)化和執(zhí)行都很慢。
? ? 并行執(zhí)行，MySQL無法執(zhí)行并行查詢，不用白費(fèi)力氣了。
? ? 哈希關(guān)聯(lián)，MySQL不支持哈希關(guān)聯(lián)，所有關(guān)聯(lián)都是嵌套循環(huán)關(guān)聯(lián)。
? ? 松散索引掃描，MySQL不支持松散（跳躍），仍需要掃描每一個(gè)條目。
? ? 最大值和最小值，MySQL對(duì) MIN()和MAX()做得不好?？匆粋€(gè)例子，強(qiáng)制使用索引來優(yōu)化（use index(xx))。
? ? 在同一個(gè)表上查詢和更新，MySQL不允許這樣。
======================================================
? ? 6.6 查詢優(yōu)化器的提示（hint）
? ? 講到了很多提示，意在如果我們對(duì)優(yōu)化器選擇的執(zhí)行計(jì)劃不滿意，使用提示來控制最終的執(zhí)行計(jì)劃，如上面的 USE INDEX(PRIMARY)，其他還有：HIGH_PRIORITY、LOW_PRIORITY、DELAYED、STRAIGHT_JOIN（上文提到過）、SQL_SMALL_RESULT、SQL_BIG_RESULT、SQL_BUFFER_RESULT、SQL_CACHE、SQL_NO_CACHE、SQL_CALC_FOUND_ROWS、FOR UPDATE、LOCK IN SHARE MODE、USE INDEX、IGNORE INDEX、FORCE INDEX等等。
======================================================
? ? 6.7 優(yōu)化特定類型的查詢
? ? 6.7.1 優(yōu)化 COUNT()查詢
? ? COUNT()常被誤解（難道這本書里說的對(duì)的？），COUNT()有兩個(gè)作用，1、統(tǒng)計(jì)非NULL列的列植的數(shù)量，2、統(tǒng)計(jì)返回?cái)?shù)據(jù)集的行數(shù)；常用的是COUNT(*)，*常被誤解為所有列，實(shí)際上在操作時(shí)是忽略所有列，而直接統(tǒng)計(jì)所有行數(shù)。COUNT(*)中的*與SELECT *中的*是不同的。如果你真想統(tǒng)計(jì)結(jié)果集的行數(shù)，就用 COUNT(*)而不要使用 COUNT(aCol)。
? ?通常以為 MyISAM執(zhí)行COUNT(*)最快，實(shí)際上是有條件的，只有不用 WHERE時(shí)，因?yàn)镸ySQL根本不用掃描數(shù)據(jù)行，也無須去計(jì)算，會(huì)直接利用存儲(chǔ)引擎的特性去獲得這個(gè)值。當(dāng)帶上 WHERE 上，就需要去掃描去計(jì)算了。
? ?書中一個(gè)優(yōu)化的例子，將條件反轉(zhuǎn)后可大大加速，如查詢 id > 5 的數(shù)量有4097行，而反轉(zhuǎn)，查詢 id < 5 的，只有幾行，然后 用總行數(shù)（用 COUNT(*) 獲取-常數(shù)不費(fèi)計(jì)算）減去 id < 5的，大大優(yōu)化。但這種情況貌似我提前可以知道 id > 5的數(shù)據(jù)比 id < 5 的數(shù)據(jù)多很多才可以。
? ?能使用近似值的就不必追求精確計(jì)算值，代價(jià)太高！
? ?6.7.2 優(yōu)化關(guān)聯(lián)查詢
? ?這個(gè)話題基本整本書都在討論（還是很暈），注意一下：
? ?1）確保ON或USING子句中的列上有索引，在創(chuàng)建索引時(shí)就要考慮到關(guān)聯(lián)的順序。
轉(zhuǎn)載于:https://my.oschina.net/zhmsong/blog/194326
總結(jié)
以上是生活随笔為你收集整理的高性能MySQL-3rd-（六）查询性能优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： jQuery 实现上下,左右滑动
- 下一篇： JS function立即调用的几种写法