說起MySQL的查詢優(yōu)化，相信不管是初級的小菜鳥還是浸淫多年的大神都能信口說出來個一二三憔晒。例如不能使用SELECT *某宪、合理創(chuàng)建索引、使用explain命令檢查sql語句..... 只是你知其然潮峦，是否真的知其所以然囱皿？是否理解其背后的工作原理？在實際場景下性能真有提升嗎忱嘹？我想可能很多人心里就要打一個問號了嘱腥。因此理解這些優(yōu)化建議背后的原理就顯得尤為重要了。因為它可以幫助我們從整體出發(fā)去考慮問題的解決方案拘悦，而不是零零碎碎的做一些邊邊角的工作齿兔。
如果能在頭腦中構(gòu)建一幅MySQL各組件之間如何協(xié)同工作的架構(gòu)圖，那將非常有助于深入理解MySQL服務(wù)器础米。下圖展示了MySQL的邏輯架構(gòu)圖分苇。
MySQL邏輯架構(gòu)整體分為三層，最上層為客戶端層椭盏，負責mysql數(shù)據(jù)庫與外界的交互允睹。諸如：連接處理畔师、授權(quán)認證、安全過濾等瘦癌。
MySQL大多數(shù)核心服務(wù)均在中間這一層艾帐，包括查詢解析乌叶、分析、優(yōu)化柒爸、緩存准浴、內(nèi)置函數(shù)(比如：時間、數(shù)學(xué)捎稚、加密等函數(shù))乐横。所有的跨存儲引擎的功能也在這一層實現(xiàn)：存儲過程、觸發(fā)器今野、視圖等葡公。
最下層為存儲引擎，其負責MySQL中的數(shù)據(jù)存儲和提取条霜。每種存儲引擎都有其優(yōu)勢和劣勢催什。（一般來講，除非是整張表的數(shù)據(jù)只用來讀的操作宰睡，否則全部推薦使用innodb引擎蒲凶。因為innodb會支持一些關(guān)系數(shù)據(jù)庫的高級功能，如事務(wù)功能和行級鎖拆内，而myisam不支持旋圆。）
中間的服務(wù)層通過API與存儲引擎通信，這些API接口屏蔽了不同存儲引擎間的差異麸恍。
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ù)量是一個非常好的習(xí)慣第步，這也是查詢中盡量避免使用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é)果緩存起來沒有任何的意義混蔼。
既然是緩存，就會存在失效機制珊燎。MySQL的查詢緩存系統(tǒng)會跟蹤查詢中涉及的每個表惭嚣，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效悔政。正因為如此料按，在任何的寫操作時，MySQL必須將對應(yīng)表的所有緩存都設(shè)置為失效卓箫。如果查詢緩存非常大或者碎片很多，這個操作就可能帶來很大的系統(tǒng)消耗垄潮，甚至導(dǎo)致系統(tǒng)僵死烹卒。所以查詢緩存是一把雙刃劍，它的使用與否是需要先進行論證的弯洗。如果是寫密集的應(yīng)用旅急，更要慎重。
語法解析和預(yù)處理
MySQL通過關(guān)鍵字將SQL語句進行解析牡整，并生成一顆對應(yīng)的解析樹藐吮。這個過程解析器主要通過語法規(guī)則來驗證和解析。比如SQL中是否使用了錯誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等逃贝。預(yù)處理則會根據(jù)MySQL規(guī)則進一步檢查解析樹是否合法谣辞。比如檢查要查詢的數(shù)據(jù)表和數(shù)據(jù)列是否存在等。經(jīng)過前面的步驟生成的語法樹被認為是合法的了沐扳，并且由優(yōu)化器將其轉(zhuǎn)化成查詢計劃泥从。多數(shù)情況下，一條查詢可以有很多種執(zhí)行方式沪摄，最后都返回相應(yīng)的結(jié)果躯嫉。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計劃纱烘。
查詢執(zhí)行引擎
在完成解析和優(yōu)化階段以后，MySQL會生成對應(yīng)的執(zhí)行計劃祈餐，查詢執(zhí)行引擎根據(jù)執(zhí)行計劃給出的指令逐步執(zhí)行得出結(jié)果擂啥。整個執(zhí)行過程的大部分操作均是通過調(diào)用存儲引擎實現(xiàn)的接口來完成，這些接口被稱為handler API帆阳。查詢過程中的每一張表由一個handler實例表示哺壶。實際上，MySQL在查詢優(yōu)化階段就為每一張表創(chuàng)建了一個handler實例舱痘，優(yōu)化器可以根據(jù)這些實例的接口來獲取表的相關(guān)信息变骡，包括表的所有列名、索引統(tǒng)計信息等芭逝。存儲引擎接口提供了非常豐富的功能塌碌，但其底層僅有幾十個接口，這些接口像搭積木一樣完成了一次查詢的大部分操作旬盯。
返回結(jié)果給客戶端
查詢執(zhí)行的最后一個階段就是將結(jié)果返回給客戶端台妆。即使查詢不到數(shù)據(jù)，MySQL仍然會返回這個查詢的相關(guān)信息胖翰，比如該查詢影響到的行數(shù)以及執(zhí)行時間等接剩。如果查詢緩存被打開且這個查詢可以被緩存，MySQL也會將結(jié)果存放到緩存中萨咳。
總結(jié)一下MySQL整個查詢執(zhí)行過程懊缺，總的來說分為5個步驟：
一、客戶端向MySQL服務(wù)器發(fā)送一條查詢請求
二培他、服務(wù)器首先檢查查詢緩存鹃两，如果命中緩存，則立刻返回存儲在緩存中的結(jié)果舀凛。否則進入下一階段
三俊扳、服務(wù)器進行SQL解析、預(yù)處理猛遍、再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃
四馋记、MySQL根據(jù)執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢
五懊烤、將結(jié)果返回給客戶端梯醒，同時緩存查詢結(jié)果
性能優(yōu)化建議
下面會從3個不同方面給出一些優(yōu)化建議。但是要明白奸晴，關(guān)于性能的優(yōu)化冤馏，沒有絕對的真理。一切都應(yīng)該是在實際的業(yè)務(wù)場景下通過測試來驗證你關(guān)于執(zhí)行計劃以及響應(yīng)時間的假設(shè)寄啼。
1逮光、Scheme設(shè)計與數(shù)據(jù)類型優(yōu)化
選擇數(shù)據(jù)類型只要遵循小而簡單的原則就好代箭，越小的數(shù)據(jù)類型通常會更快，占用更少的磁盤涕刚、內(nèi)存嗡综，處理時需要的CPU周期也更少。越簡單的數(shù)據(jù)類型在計算時需要更少的CPU周期杜漠，比如极景，整型就比字符操作代價低，因而會使用整型來存儲ip地址驾茴，使用DATETIME來存儲時間盼樟，而不是使用字符串。
2創(chuàng)建高性能索引
索引是提高MySQL查詢性能的一個重要途徑锈至，但過多的索引可能會導(dǎo)致過高的磁盤使用率以及過高的內(nèi)存占用晨缴，從而影響應(yīng)用程序的整體性能。應(yīng)當盡量避免事后才想起添加索引峡捡，因為事后可能需要監(jiān)控大量的SQL才能定位到問題所在击碗，而且添加索引的時間肯定是遠大于初始添加索引所需要的時間，可見索引的添加也是非常有技術(shù)含量的们拙。
3使用explain檢查sql語句的執(zhí)行情況
Explain語法：explain select … from … [where …]
例如：explain select * from news;
輸出：
+----+-------------+-------+-------+-------------------+---------+---------+-------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------
下面對各個屬性進行了解：
1稍途、id：這是SELECT的查詢序列號
2、select_type：select_type就是select的類型砚婆，可以有以下幾種：
3械拍、table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
4、type：這列最重要装盯，顯示了連接使用了哪種類別,有無使用索引殊者，是使用Explain命令分析性能瓶頸的關(guān)鍵項之一。一般來說验夯，得保證查詢至少達到range級別，最好能達到ref摔刁，否則就可能會出現(xiàn)性能問題挥转。
5、possible_keys：列指出MySQL能使用哪個索引在該表中找到行
6共屈、key：顯示MySQL實際決定使用的鍵（索引）绑谣。如果沒有選擇索引，鍵是NULL
7拗引、key_len：顯示MySQL決定使用的鍵長度借宵。如果鍵是NULL，則長度為NULL矾削。使用的索引的長度壤玫。在不損失精確性的情況下豁护，長度越短越好
8、ref：顯示使用哪個列或常數(shù)與key一起從表中選擇行欲间。
9楚里、rows：顯示MySQL認為它執(zhí)行查詢時必須檢查的行數(shù)。
10猎贴、Extra：包含MySQL解決查詢的詳細信息班缎，也是關(guān)鍵參考項之一。
