本文轉(zhuǎn)載自http://www.jianshu.com/p/d7665192aaaf
說起MySQL的查詢優(yōu)化,相信大家積累一堆技巧:不能使用SELECT *、不使用NULL字段、合理創(chuàng)建索引、為字段選擇合適的數(shù)據(jù)類型..... 你是否真的理解這些優(yōu)化技巧?是否理解其背后的工作原理?在實際場景下性能真有提升嗎?我想未必。因而理解這些優(yōu)化建議背后的原理就尤為重要,希望本文能讓你重新審視這些優(yōu)化建議,并在實際業(yè)務(wù)場景下合理的運用。
MySQL邏輯架構(gòu)
如果能在頭腦中構(gòu)建一幅MySQL各組件之間如何協(xié)同工作的架構(gòu)圖,有助于深入理解MySQL服務(wù)器。下圖展示了MySQL的邏輯架構(gòu)圖。
MySQL邏輯架構(gòu),來自:高性能MySQL
MySQL邏輯架構(gòu)整體分為三層,最上層為客戶端層,并非MySQL所獨有,諸如:連接處理、授權(quán)認證、安全等功能均在這一層處理。
MySQL大多數(shù)核心服務(wù)均在中間這一層,包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)(比如:時間、數(shù)學、加密等函數(shù))。所有的跨存儲引擎的功能也在這一層實現(xiàn):存儲過程、觸發(fā)器、視圖等。
最下層為存儲引擎,其負責MySQL中的數(shù)據(jù)存儲和提取。和Linux下的文件系統(tǒng)類似,每種存儲引擎都有其優(yōu)勢和劣勢。中間的服務(wù)層通過API與存儲引擎通信,這些API接口屏蔽了不同存儲引擎間的差異。
MySQL查詢過程
我們總是希望MySQL能夠獲得更高的查詢性能,最好的辦法是弄清楚MySQL是如何優(yōu)化和執(zhí)行查詢的。一旦理解了這一點,就會發(fā)現(xiàn):很多的查詢優(yōu)化工作實際上就是遵循一些原則讓MySQL的優(yōu)化器能夠按照預(yù)想的合理方式運行而已。
當向MySQL發(fā)送一個請求的時候,MySQL到底做了些什么呢?
MySQL查詢過程
客戶端/服務(wù)端通信協(xié)議
MySQL客戶端/服務(wù)端通信協(xié)議是“半雙工”的:在任一時刻,要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù),要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù),這兩個動作不能同時發(fā)生。一旦一端開始發(fā)送消息,另一端要接收完整個消息才能響應(yīng)它,所以我們無法也無須將一個消息切成小塊獨立發(fā)送,也沒有辦法進行流量控制。
客戶端用一個單獨的數(shù)據(jù)包將查詢請求發(fā)送給服務(wù)器,所以當查詢語句很長的時候,需要設(shè)置max_allowed_packet參數(shù)。但是需要注意的是,如果查詢實在是太大,服務(wù)端會拒絕接收更多數(shù)據(jù)并拋出異常。
與之相反的是,服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會很多,由多個數(shù)據(jù)包組成。但是當服務(wù)器響應(yīng)客戶端請求時,客戶端必須完整的接收整個返回結(jié)果,而不能簡單的只取前面幾條結(jié)果,然后讓服務(wù)器停止發(fā)送。因而在實際開發(fā)中,盡量保持查詢簡單且只返回必需的數(shù)據(jù),減小通信間數(shù)據(jù)包的大小和數(shù)量是一個非常好的習慣,這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。
查詢緩存
在解析一個查詢語句前,如果查詢緩存是打開的,那么MySQL會檢查這個查詢語句是否命中查詢緩存中的數(shù)據(jù)。如果當前查詢恰好命中查詢緩存,在檢查一次用戶權(quán)限后直接返回緩存中的結(jié)果。這種情況下,查詢不會被解析,也不會生成執(zhí)行計劃,更不會執(zhí)行。
MySQL將緩存存放在一個引用表(不要理解成table,可以認為是類似于HashMap的數(shù)據(jù)結(jié)構(gòu)),通過一個哈希值索引,這個哈希值通過查詢本身、當前要查詢的數(shù)據(jù)庫、客戶端協(xié)議版本號等一些可能影響結(jié)果的信息計算得來。所以兩個查詢在任何字符上的不同(例如:空格、注釋),都會導(dǎo)致緩存不會命中。
如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時表、mysql庫中的系統(tǒng)表,其查詢結(jié)果
都不會被緩存。比如函數(shù)NOW()或者CURRENT_DATE()會因為不同的查詢時間,返回不同的查詢結(jié)果,再比如包含CURRENT_USER或者CONNECION_ID()的查詢語句會因為不同的用戶而返回不同的結(jié)果,將這樣的查詢結(jié)果緩存起來沒有任何的意義。
既然是緩存,就會失效,那查詢緩存何時失效呢?MySQ