說起 MySQL 的查詢優(yōu)化，相信大家收藏了一堆奇技淫巧：不能使用 SELECT *、不使用 NULL 字段、合理創(chuàng)建索引、為字段選擇合適的數(shù)據(jù)類型.....
你是否真的理解這些優(yōu)化技巧？是否理解它背后的工作原理？在實(shí)際場(chǎng)景下性能真有提升嗎？我想未必。
因而理解這些優(yōu)化建議背后的原理就顯得尤為重要，希望本文能讓你重新審視這些優(yōu)化建議，并在實(shí)際業(yè)務(wù)場(chǎng)景下合理的運(yùn)用。
如果能在頭腦中構(gòu)建一幅 MySQL 各組件之間如何協(xié)同工作的架構(gòu)圖，將有助于深入理解 MySQL 服務(wù)器。下圖是 MySQL 的邏輯架構(gòu)圖：
MySQL 邏輯架構(gòu)
MySQL 的邏輯架構(gòu)整體分為三層，最上層為客戶端層，并非 MySQL 所獨(dú)有，諸如：連接處理、授權(quán)認(rèn)證、安全等功能均在這一層處理。
MySQL 的大多數(shù)核心服務(wù)均在中間這一層，包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)(比如：時(shí)間、數(shù)學(xué)、加密等函數(shù))。所有的跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)：存儲(chǔ)過程、觸發(fā)器、視圖等。
最下層為存儲(chǔ)引擎，負(fù)責(zé) MySQL 中的數(shù)據(jù)存儲(chǔ)和提取。和 Linux 下的文件系統(tǒng)類似，每種存儲(chǔ)引擎都有其優(yōu)勢(shì)和劣勢(shì)。
中間的服務(wù)層通過 API 與存儲(chǔ)引擎通信，這些 API 接口屏蔽了不同存儲(chǔ)引擎間的差異。
我們總是希望 MySQL 能夠獲得更高的查詢性能，最好的辦法是弄清楚 MySQL 是如何優(yōu)化和執(zhí)行查詢的。
一旦理解了這一點(diǎn)，就會(huì)發(fā)現(xiàn)：很多的查詢優(yōu)化工作實(shí)際上就是遵循一些原則讓 MySQL 的優(yōu)化器能夠按照預(yù)想的合理方式運(yùn)行。
當(dāng)向 MySQL 發(fā)送一個(gè)請(qǐng)求的時(shí)候，MySQL 到底做了些什么呢？
MySQL 查詢過程
MySQL 的客戶端/服務(wù)端通信協(xié)議是“半雙工”的：在任一時(shí)刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。
一旦一端開始發(fā)送消息，另一端要接收完整個(gè)消息才能響應(yīng)它，所以我們無法也無須將一個(gè)消息切成小塊獨(dú)立發(fā)送，也沒有辦法進(jìn)行流量控制。
客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢請(qǐng)求發(fā)送給服務(wù)器，所以當(dāng)查詢語句很長的時(shí)候，需要設(shè)置 max_allowed_packet 參數(shù)。
但是需要注意的是，如果查詢實(shí)在是太大，服務(wù)端會(huì)拒絕接收更多數(shù)據(jù)并拋出異常。
與之相反的是，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會(huì)很多，由多個(gè)數(shù)據(jù)包組成。但是當(dāng)服務(wù)器響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整的接收整個(gè)返回結(jié)果，而不能簡單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。
因而在實(shí)際開發(fā)中，盡量保持查詢簡單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣，這也是查詢中盡量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。
在解析一個(gè)查詢語句前，如果查詢緩存是打開的，那么 MySQL 會(huì)檢查這個(gè)查詢語句是否命中查詢緩存中的數(shù)據(jù)。
如果當(dāng)前查詢恰好命中查詢緩存，在檢查一次用戶權(quán)限后直接返回緩存中的結(jié)果。這種情況下，查詢不會(huì)被解析，也不會(huì)生成執(zhí)行計(jì)劃，更不會(huì)執(zhí)行。
MySQL 將緩存存放在一個(gè)引用表（不要理解成 table，可以認(rèn)為是類似于 HashMap 的數(shù)據(jù)結(jié)構(gòu)），通過一個(gè)哈希值索引。
這個(gè)哈希值通過查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫、客戶端協(xié)議版本號(hào)等一些可能影響結(jié)果的信息計(jì)算得來。
所以兩個(gè)查詢?cè)谌魏巫址系牟煌ɡ纾嚎崭�、注釋），都�?huì)導(dǎo)致緩存不會(huì)命中。
如果查詢中包含任何用戶自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、MySQL 庫中的系統(tǒng)表，其查詢結(jié)果都不會(huì)被緩存。
比如函數(shù) NOW() 或者 CURRENT_DATE() 會(huì)因?yàn)椴煌牟樵儠r(shí)間，返回不同的查詢結(jié)果。
再比如包含 CURRENT_USER 或者 CONNECION_ID() 的查詢語句會(huì)因?yàn)椴煌挠脩舳祷夭煌慕Y(jié)果，將這樣的查詢結(jié)果緩存起來沒有任何的意義。
既然是緩存，就會(huì)失效，那查詢緩存何時(shí)失效呢？MySQL 的查詢緩存系統(tǒng)會(huì)跟蹤查詢中涉及的每個(gè)表，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。
正因?yàn)槿绱�，在任何的寫操作時(shí)，MySQL 必須將對(duì)應(yīng)表的所有緩存都設(shè)置為失效。
如果查詢緩存非常大或者碎片很多，這個(gè)操作就可能帶來很大的系統(tǒng)消耗，甚至導(dǎo)致系統(tǒng)僵死一會(huì)兒。
而且查詢緩存對(duì)系統(tǒng)的額外消耗也不僅僅在寫操作，讀操作也不例外：
任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條 SQL 語句永遠(yuǎn)不會(huì)命中緩存。
如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會(huì)將結(jié)果存入緩存，也會(huì)帶來額外的系統(tǒng)消耗。
基于此，我們要知道并不是什么情況下查詢緩存都會(huì)提高系統(tǒng)性能，緩存和失效都會(huì)帶來額外消耗，只有當(dāng)緩存帶來的資源節(jié)約大于其本身消耗的資源時(shí)，才會(huì)給系統(tǒng)帶來性能提升。
但如何評(píng)估打開緩存是否能夠帶來性能提升是一件非常困難的事情，也不在本文討論的范疇內(nèi)。
如果系統(tǒng)確實(shí)存在一些性能問題，可以嘗試打開查詢緩存，并在數(shù)據(jù)庫設(shè)計(jì)上做一些優(yōu)化，比如：
用多個(gè)小表代替一個(gè)大表，注意不要過度設(shè)計(jì)。
批量插入代替循環(huán)單條插入。
合理控制緩存空間大小，一般來說其大小設(shè)置為幾十兆比較合適。
可以通過 SQL_CACHE 和 SQL_NO_CACHE 來控制某個(gè)查詢語句是否需要進(jìn)行緩存。
最后的忠告是不要輕易打開查詢緩存，特別是寫密集型應(yīng)用。如果你實(shí)在是忍不住，可以將 query_cache_type 設(shè)置為 DEMAND。
這時(shí)只有加入 SQL_CACHE 的查詢才會(huì)走緩存，其他查詢則不會(huì)，這樣可以非常自由地控制哪些查詢需要被緩存。
當(dāng)然查詢緩存系統(tǒng)本身是非常復(fù)雜的，這里討論的也只是很小的一部分，其他更深入的話題沒有涉及，比如：緩存是如何使用內(nèi)存的？如何控制內(nèi)存的碎片化？事務(wù)對(duì)查詢緩存有何影響等等。
MySQL 通過關(guān)鍵字將 SQL 語句進(jìn)行解析，并生成一棵對(duì)應(yīng)的解析樹。這個(gè)過程解析器主要通過語法規(guī)則來驗(yàn)證和解析。比如 SQL 中是否使用了錯(cuò)誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等。
預(yù)處理則會(huì)根據(jù) MySQL 規(guī)則進(jìn)一步檢查解析樹是否合法。比如檢查要查詢的數(shù)據(jù)表和數(shù)據(jù)列是否存在等等。
經(jīng)過前面的步驟生成的語法樹被認(rèn)為是合法的了，并且由優(yōu)化器將其轉(zhuǎn)化成查詢計(jì)劃。
多數(shù)情況下，一條查詢可以有很多種執(zhí)行方式，最后都返回相應(yīng)的結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。
MySQL 使用基于成本的優(yōu)化器，它嘗試預(yù)測(cè)一個(gè)查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個(gè)。
在 MySQL 可以通過查詢當(dāng)前會(huì)話的 last_query_cost 的值來得到其計(jì)算當(dāng)前查詢的成本。
mysql> select * from t_message limit 10;
...省略結(jié)果集
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+
示例中的結(jié)果表示優(yōu)化器認(rèn)為大概需要做 6391 個(gè)數(shù)據(jù)頁的隨機(jī)查找才能完成上面的查詢。
這個(gè)結(jié)果是根據(jù)一些列的統(tǒng)計(jì)信息計(jì)算得來的，這些統(tǒng)計(jì)信息包括：每張表或者索引的頁面?zhèn)�數(shù)、索引的基數(shù)、索引和數(shù)據(jù)行的長度、索引的分布情況等等。
有非常多的原因會(huì)導(dǎo)致 MySQL 選擇錯(cuò)誤的執(zhí)行計(jì)劃，比如統(tǒng)計(jì)信息不準(zhǔn)確、不會(huì)考慮不受其控制的操作成本（用戶自定義函數(shù)、存儲(chǔ)過程）。
MySQL 認(rèn)為的最優(yōu)跟我們想的不一樣（我們希望執(zhí)行時(shí)間盡可能短，但 MySQL 值選擇它認(rèn)為成本小的，但成本小并不意味著執(zhí)行時(shí)間短）等等。
MySQL 的查詢優(yōu)化器是一個(gè)非常復(fù)雜的部件，它使用了非常多的優(yōu)化策略來生成一個(gè)最優(yōu)的執(zhí)行計(jì)劃：
重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢時(shí)，并不一定按照 SQL 中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）。
優(yōu)化 MIN() 和 MAX() 函數(shù)（找某列的最小值，如果該列有索引，只需要查找 B+Tree 索引最左端，反之則可以找到最大值，具體原理見下文）。
提前終止查詢（比如：使用 LIMIT 時(shí)，查找到滿足數(shù)量的結(jié)果集后會(huì)立即終止查詢）。
優(yōu)化排序（在老版本 MySQL 會(huì)使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內(nèi)存中對(duì)其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序。對(duì)于 I/O 密集型應(yīng)用，效率會(huì)高很多）。
隨著 MySQL 的不斷發(fā)展，優(yōu)化器使用的優(yōu)化策略也在不斷的進(jìn)化，這里僅僅介紹幾個(gè)非常常用且容易理解的優(yōu)化策略，其他的優(yōu)化策略，大家自行查閱吧。
QQ客服