MYSQL應(yīng)該是最流行的WEB后端數(shù)據(jù)庫。大量應(yīng)用于PHP，Ruby，Python，Java 等Web語言開發(fā)項目中，無論NOSQL發(fā)展多么快，都不影響大部分架構(gòu)師選擇MYSQL作為數(shù)據(jù)存儲。
? ? ? ?MYSQL如此方便和穩(wěn)定，以至于我們在開發(fā) WEB 程序的時候非常少想到它。即使想到優(yōu)化也是程序級別的，比方不要寫過于消耗資源的SQL語句?？墒浅酥?，在整個系統(tǒng)上仍然有非常多能夠優(yōu)化的地方。
1?優(yōu)化原理
說起MySQL的查詢優(yōu)化，相信大家會想到：不能使用SELECT *、不使用NULL字段、合理創(chuàng)建索引、為字段選擇合適的數(shù)據(jù)類型..... 你是否真的理解這些優(yōu)化技巧？是否理解其背后的工作原理？在實際場景下性能真有提升嗎？我想未必。因而理解這些優(yōu)化建議背后的原理就尤為重要，希望本文能讓你重新審視這些優(yōu)化建議，并在實際業(yè)務(wù)場景下合理的運(yùn)用。
? ? ? ?如果能在頭腦中構(gòu)建一幅MySQL各組件之間如何協(xié)同工作的架構(gòu)圖，有助于深入理解MySQL服務(wù)器。下圖展示了MySQL的邏輯架構(gòu)圖。
MySQL邏輯架構(gòu)
MySQL邏輯架構(gòu)整體分為三層，最上層為客戶端層，并非MySQL所獨有，諸如：連接處理、授權(quán)認(rèn)證、安全等功能均在這一層處理。
MySQL大多數(shù)核心服務(wù)均在中間這一層，包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)(比如：時間、數(shù)學(xué)、加密等函數(shù))。所有的跨存儲引擎的功能也在這一層實現(xiàn)：存儲過程、觸發(fā)器、視圖等。
最下層為存儲引擎，其負(fù)責(zé)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ù)想的合理方式運(yùn)行而已。
當(dāng)向MySQL發(fā)送一個請求的時候，MySQL到底做了些什么呢？
MySQL查詢過程
客戶端/服務(wù)端通信協(xié)議
MySQL客戶端/服務(wù)端通信協(xié)議是“半雙工”的：在任一時刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生。一旦一端開始發(fā)送消息，另一端要接收完整個消息才能響應(yīng)它，所以我們無法也無須將一個消息切成小塊獨立發(fā)送，也沒有辦法進(jìn)行流量控制。
客戶端用一個單獨的數(shù)據(jù)包將查詢請求發(fā)送給服務(wù)器，所以當(dāng)查詢語句很長的時候，需要設(shè)置max_allowed_packet參數(shù)。但是需要注意的是，如果查詢實在是太大，服務(wù)端會拒絕接收更多數(shù)據(jù)并拋出異常。
與之相反的是，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會很多，由多個數(shù)據(jù)包組成。但是當(dāng)服務(wù)器響應(yīng)客戶端請求時，客戶端必須完整的接收整個返回結(jié)果，而不能簡單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實際開發(fā)中，盡量保持查詢簡單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。
查詢緩存
在解析一個查詢語句前，如果查詢緩存是打開的，那么MySQL會檢查這個查詢語句是否命中查詢緩存中的數(shù)據(jù)。如果當(dāng)前查詢恰好命中查詢緩存，在檢查一次用戶權(quán)限后直接返回緩存中的結(jié)果。這種情況下，查詢不會被解析，也不會生成執(zhí)行計劃，更不會執(zhí)行。
MySQL將緩存存放在一個引用表（不要理解成table，可以認(rèn)為是類似于HashMap的數(shù)據(jù)結(jié)構(gòu)），通過一個哈希值索引，這個哈希值通過查詢本身、當(dāng)前要查詢的數(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)僵死一會兒。而且查詢緩存對系統(tǒng)的額外消耗也不僅僅在寫操作，讀操作也不例外：? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
1.任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條SQL語句永遠(yuǎn)不會命中緩存
2.如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會將結(jié)果存入緩存，也會帶來額外的系統(tǒng)消耗
基于此，我們要知道并不是什么情況下查詢緩存都會提高系統(tǒng)性能，緩存和失效都會帶來額外消耗，只有當(dāng)緩存帶來的資源節(jié)約大于其本身消耗的資源時，才會給系統(tǒng)帶來性能提升。但要如何評估打開緩存是否能夠帶來性能提升是一件非常困難的事情，也不在本文討論的范疇內(nèi)。如果系統(tǒng)確實存在一些性能問題，可以嘗試打開查詢緩存，并在數(shù)據(jù)庫設(shè)計上做一些優(yōu)化，比如：
1.用多個小表代替一個大表，注意不要過度設(shè)計
2.批量插入代替循環(huán)單條插入
3.合理控制緩存空間大小，一般來說其大小設(shè)置為幾十兆比較合適
4.可以通過SQL_CACHE和SQL_NO_CACHE來控制某個查詢語句是否需要進(jìn)行緩存
最后的忠告是不要輕易打開查詢緩存，特別是寫密集型應(yīng)用。如果你實在是忍不住，可以將query_cache_type設(shè)置為DEMAND，這時只有加入SQL_CACHE的查詢才會走緩存，其他查詢則不會，這樣可以非常自由地控制哪些查詢需要被緩存。
當(dāng)然查詢緩存系統(tǒng)本身是非常復(fù)雜的，這里討論的也只是很小的一部分，其他更深入的話題，比如：緩存是如何使用內(nèi)存的？如何控制內(nèi)存的碎片化？事務(wù)對查詢緩存有何影響等等，讀者可以自行閱讀相關(guān)資料，這里權(quán)當(dāng)拋磚引玉吧。
語法解析和預(yù)處理
MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一顆對應(yīng)的解析樹。這個過程解析器主要通過語法規(guī)則來驗證和解析。比如SQL中是否使用了錯誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等。預(yù)處理則會根據(jù)MySQL規(guī)則進(jìn)一步檢查解析樹是否合法。比如檢查要查詢的數(shù)據(jù)表和數(shù)據(jù)列是否存在等等。
查詢優(yōu)化
經(jīng)過前面的步驟生成的語法樹被認(rèn)為是合法的了，并且由優(yōu)化器將其轉(zhuǎn)化成查詢計劃。多數(shù)情況下，一條查詢可以有很多種執(zhí)行方式，最后都返回相應(yīng)的結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計劃。
MySQL使用基于成本的優(yōu)化器，它嘗試預(yù)測一個查詢使用某種執(zhí)行計劃時的成本，并選擇其中成本最小的一個。在MySQL可以通過查詢當(dāng)前會話的last_query_cost的值來得到其計算當(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個數(shù)據(jù)頁的隨機(jī)查找才能完成上面的查詢。這個結(jié)果是根據(jù)一些列的統(tǒng)計信息計算得來的，這些統(tǒng)計信息包括：每張表或者索引的頁面?zhèn)€數(shù)、索引的基數(shù)、索引和數(shù)據(jù)行的長度、索引的分布情況等等。
有非常多的原因會導(dǎo)致MySQL選擇錯誤的執(zhí)行計劃，比如統(tǒng)計信息不準(zhǔn)確、不會考慮不受其控制的操作成本（用戶自定義函數(shù)、存儲過程）、MySQL認(rèn)為的最優(yōu)跟我們想的不一樣（我們希望執(zhí)行時間盡可能短，但MySQL值選擇它認(rèn)為成本小的，但成本小并不意味著執(zhí)行時間短）等等。
MySQL的查詢優(yōu)化器是一個非常復(fù)雜的部件，它使用了非常多的優(yōu)化策略來生成一個最優(yōu)的執(zhí)行計劃：
1.重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢時，并不一定按照SQL中指定的順序進(jìn)行，但有一些技巧可以指定關(guān)聯(lián)順序）
2.優(yōu)化MIN()和MAX()函數(shù)（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值，具體原理見下文）
3.提前終止查詢（比如：使用Limit時，查找到滿足數(shù)量的結(jié)果集后會立即終止查詢）
4.優(yōu)化排序（在老版本MySQL會使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內(nèi)存中對其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序。對于I/O密集型應(yīng)用，效率會高很多）
隨著MySQL的不斷發(fā)展，優(yōu)化器使用的優(yōu)化策略也在不斷的進(jìn)化，這里僅僅介紹幾個非常常用且容易理解的優(yōu)化策略，其他的優(yōu)化策略，大家自行查閱吧。
查詢執(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在生成第一條結(jié)果時，就開始向客戶端逐步返回結(jié)果集了。 這樣服務(wù)端就無須存儲太多結(jié)果而消耗過多內(nèi)存，也可以讓客戶端第一時間獲得返回結(jié)果。需要注意的是，結(jié)果集中的每一行都會以一個滿足①中所描述的通信協(xié)議的數(shù)據(jù)包發(fā)送，再通過TCP協(xié)議進(jìn)行傳輸，在傳輸過程中，可能對MySQL的數(shù)據(jù)包進(jìn)行緩存然后批量發(fā)送。
回頭總結(jié)一下MySQL整個查詢執(zhí)行過程，總的來說分為6個步驟：
? ? ? ?1.客戶端向MySQL服務(wù)器發(fā)送一條查詢請求
? ? ? ?2.服務(wù)器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結(jié)果。否則進(jìn)入下一階段
? ? ? ?3.服務(wù)器進(jìn)行SQL解析、預(yù)處理、再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃
? ? ? ?4.MySQL根據(jù)執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢
? ? ? ?5.將結(jié)果返回給客戶端，同時緩存查詢結(jié)果
2?事務(wù)及引擎
? ? ? ?MySQL的存儲引擎可能是所有關(guān)系型數(shù)據(jù)庫產(chǎn)品中最具有特色的了，不僅可以同時使用多種存儲引擎，而且每種存儲引擎和MySQL之間使用插件方式這種非常松的耦合關(guān)系。
? ? ? ?由于各存儲引擎功能特性差異較大，需要關(guān)注如何來選擇合適的存儲引擎來應(yīng)對不同的業(yè)務(wù)場景。
一、MyISAM
o???特性
? ? ? ?1.不支持事務(wù)：MyISAM存儲引擎不支持事務(wù)，所以對事務(wù)有要求的業(yè)務(wù)場景不能使用
? ? ? ?2.表級鎖定：其鎖定機(jī)制是表級索引，這雖然可以讓鎖定的實現(xiàn)成本很小但是也同時大大降低了其并發(fā)性能
? ? ? ?3.讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀
? ? ? ?4.只會緩存索引：MyISAM可以通過key_buffer緩存以大大提高訪問性能減少磁盤IO，但是這個緩存區(qū)只會緩存索引，而不會緩存數(shù)據(jù)
o???適用場景
? ? ? ?1.不需要事務(wù)支持（不支持）
? ? ? ?2.并發(fā)相對較低（鎖定機(jī)制問題）
? ? ? ?3.數(shù)據(jù)修改相對較少（阻塞問題）
? ? ? ?4.以讀為主
? ? ? ?5.數(shù)據(jù)一致性要求不是非常高
o???最佳實踐
? ? ? ?1.盡量索引（緩存機(jī)制）
? ? ? ?2.調(diào)整讀寫優(yōu)先級，根據(jù)實際需求確保重要操作更優(yōu)先
? ? ? ?3.啟用延遲插入改善大批量寫入性能
? ? ? ?4.盡量順序操作讓insert數(shù)據(jù)都寫入到尾部，減少阻塞
? ? ? ?5.分解大的操作，降低單個操作的阻塞時間
? ? ? ?6.降低并發(fā)數(shù)，某些高并發(fā)場景通過應(yīng)用來進(jìn)行排隊機(jī)制
? ? ? ?7.對于相對靜態(tài)的數(shù)據(jù)，充分利用Query Cache可以極大的提高訪問效率
? ? ? ?8.MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進(jìn)行實際的數(shù)據(jù)訪問
二、InnoDB
o???特性
? ? ? ?1.具有較好的事務(wù)支持：支持4個事務(wù)隔離級別，支持多版本讀
? ? ? ?2.行級鎖定：通過索引實現(xiàn)，全表掃描仍然會是表鎖，注意間隙鎖的影響
? ? ? ?3.讀寫阻塞與事務(wù)隔離級別相關(guān)
? ? ? ?4.具有非常高效的緩存特性：能緩存索引，也能緩存數(shù)據(jù)
? ? ? ?5.整個表和主鍵以Cluster方式存儲，組成一顆平衡樹
? ? ? ?6.所有Secondary Index都會保存主鍵信息
o???適用場景
? ? ? ?1.需要事務(wù)支持（具有較好的事務(wù)特性）
? ? ? ?2.行級鎖定對高并發(fā)有很好的適應(yīng)能力，但需要確保查詢是通過索引完成
? ? ? ?3.數(shù)據(jù)更新較為頻繁的場景
? ? ? ?4.數(shù)據(jù)一致性要求較高
? ? ? ?5.硬件設(shè)備內(nèi)存較大，可以利用InnoDB較好的緩存能力來提高內(nèi)存利用率，盡可能減少磁盤 IO
o???最佳實踐
? ? ? ?1.主鍵盡可能小，避免給Secondary index帶來過大的空間負(fù)擔(dān)
? ? ? ?2.避免全表掃描，因為會使用表鎖
? ? ? ?3.盡可能緩存所有的索引和數(shù)據(jù)，提高響應(yīng)速度
? ? ? ?4.在大批量小插入的時候，盡量自己控制事務(wù)而不要使用autocommit自動提交
? ? ? ?5.合理設(shè)置innodb_flush_log_at_trx_commit參數(shù)值，不要過度追求安全性
? ? ? ?6.避免主鍵更新，因為這會帶來大量的數(shù)據(jù)移動
三、NDBCluster
o???特性
1.分布式：分布式存儲引擎，可以由多個NDBCluster存儲引擎組成集群分別存放整體數(shù)據(jù)的一部分
2.支持事務(wù)：和Innodb一樣，支持事務(wù)
3.可與mysqld不在一臺主機(jī)：可以和mysqld分開存在于獨立的主機(jī)上，然后通過網(wǎng)絡(luò)和mysqld通信交互
4.內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中，老版本所有數(shù)據(jù)和索引必須存在與內(nèi)存中
o? ?適用場景
1.具有非常高的并發(fā)需求
2.對單個請求的響應(yīng)并不是非常的critical
3.查詢簡單，過濾條件較為固定，每次請求數(shù)據(jù)量較少，又不希望自己進(jìn)行水平Sharding
o? ?最佳實踐
1.盡可能讓查詢簡單，避免數(shù)據(jù)的跨節(jié)點傳輸
2.盡可能滿足SQL節(jié)點的計算性能，大一點的集群SQL節(jié)點會明顯多余Data節(jié)點
3.在各節(jié)點之間盡可能使用萬兆網(wǎng)絡(luò)環(huán)境互聯(lián)，以減少數(shù)據(jù)在網(wǎng)絡(luò)層傳輸過程中的延時
3?緩存參數(shù)優(yōu)化
? ? ? ?從內(nèi)存中讀取一個數(shù)據(jù)庫的時間是微秒級別，而從一塊普通硬盤上讀取一個IO是在毫秒級別，二者相差3個數(shù)量級。所以，要優(yōu)化數(shù)據(jù)庫，首先第一步需要優(yōu)化的就是IO，盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO。從MySQL 數(shù)據(jù)庫IO相關(guān)參數(shù)（緩存參數(shù)）的角度來看看可以通過以下參數(shù)進(jìn)行IO優(yōu)化（建議級）：
? ? ? ?·? ?query_cache_type : 如果全部使用innodb存儲引擎，建議為0，如果使用MyISAM 存儲引擎，建議為2，同時在SQL語句中顯式控制是否使用query cache；
? ? ? ?·? ?query_cache_size: 根據(jù)命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進(jìn)行調(diào)整，一般不建議太大，256MB可能已經(jīng)差不多了，大型的配置型靜態(tài)數(shù)據(jù)可適當(dāng)調(diào)大；
? ? ? ?·? ? binlog_cache_size: 一般環(huán)境2MB～4MB是一個合適的選擇，事務(wù)較大且寫入頻繁的數(shù)據(jù)庫環(huán)境可以適當(dāng)調(diào)大，但不建議超過32MB；
? ? ? ?·? ? key_buffer_size: 如果不使用MyISAM存儲引擎，16MB足以，用來緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲引擎，在內(nèi)存允許的情況下，盡可能將所有索引放入內(nèi)存，簡單來說就是“越大越好”；
? ? ? ?·? ?bulk_insert_buffer_size: 如果經(jīng)常性的需要使用批量插入的特殊語句（上面有說明）來插入數(shù)據(jù)，可以適當(dāng)調(diào)大該參數(shù)至16MB～32MB，不建議繼續(xù)增大，某人8MB；
? ? ? ?·? ?innodb_buffer_pool_size: 如果不使用InnoDB存儲引擎，可以不用調(diào)整這個參數(shù)，如果需要使用，在內(nèi)存允許的情況下，盡可能將所有的InnoDB數(shù)據(jù)文件存放如內(nèi)存中，同樣將但來說也是“越大越好”；
? ? ? ?·? ?innodb_additional_mem_pool_size: 一般的數(shù)據(jù)庫建議調(diào)整到8MB～16MB，如果表特別多，可以調(diào)整到32MB，可以根據(jù)error log中的信息判斷是否需要增大；
? ? ? ?·? ?innodb_log_buffer_size: 默認(rèn)是1MB，系的如頻繁的系統(tǒng)可適當(dāng)增大至4MB～8MB。當(dāng)然如上面介紹所說，這個參數(shù)實際上還和另外的flush參數(shù)相關(guān)。一般來說不建議超過32MB；
? ? ? ?·? ?innodb_max_dirty_pages_pct: 根據(jù)以往的經(jīng)驗，重啟恢復(fù)的數(shù)據(jù)如果要超過1G的話，啟動速度會比較慢，幾乎難以接受，所以建議不大于1GB/innodb_buffer_pool_size(GB)*100這個值。當(dāng)然，如果你能夠忍受啟動時間比較長，而且希望盡量減少內(nèi)存至磁盤的flush，可以將這個值調(diào)整到90，但不建議超過90。
? ? ? ?注：以上取值范圍僅僅只是根據(jù)以往遇到的數(shù)據(jù)庫場景所得到的一些優(yōu)化經(jīng)驗值，并不一定適用于所有場景，所以在實際優(yōu)化過程中還需要大家自己不斷的調(diào)整分析。
4 SQL優(yōu)化
4.1?優(yōu)化目標(biāo)
1、減少?IO?次數(shù)
? ? ? ?IO永遠(yuǎn)是數(shù)據(jù)庫最容易瓶頸的地方，這是由數(shù)據(jù)庫的職責(zé)所決定的，大部分?jǐn)?shù)據(jù)庫操作中超過90%的時間都是?IO?操作所占用的，減少?IO?次數(shù)是?SQL?優(yōu)化中需要第一優(yōu)先考慮，當(dāng)然，也是收效最明顯的優(yōu)化手段。
2、降低?CPU?計算
? ? ? ?除了?IO?瓶頸之外，SQL優(yōu)化中需要考慮的就是?CPU?運(yùn)算量的優(yōu)化了。order by,group by,distinct …?都是消耗?CPU?的大戶（這些操作基本上都是?CPU?處理內(nèi)存中的數(shù)據(jù)比較運(yùn)算）。當(dāng)我們的?IO?優(yōu)化做到一定階段之后，降低?CPU計算也就成為了我們?SQL?優(yōu)化的重要目標(biāo)。
4.2?優(yōu)化方法
一、監(jiān)控分析
1、硬件資源監(jiān)控
? ? ? ?關(guān)注的主要數(shù)據(jù)庫服務(wù)器在IO和CPU方面的指標(biāo)。
2、mysql性能分析器
? ? ? ?可以利用mysql profiling（mysql性能分析器）來優(yōu)化sql語句，即查看SQL執(zhí)行消耗系統(tǒng)資源的信息（需要開啟才能應(yīng)用該功能）。
3、慢查詢分析
? ? ? ?通過慢日志查詢可以知道哪些SQL語句執(zhí)行效率低下，那些sql語句使用的頻率高等。
? ? ? ?對MySQL查詢語句的監(jiān)控、分析、優(yōu)化是MySQL優(yōu)化非常重要的一步。開啟慢查詢?nèi)罩竞?，由于日志記錄操作，在一定程度上會占用CPU資源影響mysql的性能，但是可以階段性開啟來定位性能瓶頸。
二、改變?SQL?執(zhí)行計劃
? ? ? ?明確了優(yōu)化目標(biāo)之后，我們需要確定達(dá)到我們目標(biāo)的方法。對于?SQL?語句來說，達(dá)到上述2個優(yōu)化目標(biāo)的方法其實只有一個，那就是改變?SQL?的執(zhí)行計劃，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)，以達(dá)到“減少IO次數(shù)”和“降低?CPU?計算”的目標(biāo)。
? ? ? ?使用explain命令查看query語句的性能：
? ? ? ?EXPLAIN select * from tablename;##查看執(zhí)行計劃中的sql性能
? ? ? ?上面這是最簡單的執(zhí)行計劃實例，來分析一下上面的這幾個字段。
1、id：id主要是用來標(biāo)識sql執(zhí)行順序，如果沒有子查詢，一般來說id只有1個，執(zhí)行順序也是從上到下。
2、select_type：每個select子句的類型，主要分成下面幾種：?
a:SIMPLE:查詢中不包含任何子查詢或者union
b:PRIMARY:查詢中包含了任何復(fù)雜的子部分，最外層的就會變成PRIMARY
c:SUBQUERY:在SELECT或者WHERE列表中包含了子查詢
d:DERIVED：在FROM中包含了子查詢
e:UNION:如果第二個SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION，如果UNION包含在FROM子句的子查詢中，外層SELECT會被標(biāo)記為：DERIVED
f：UNION RESULT從UNION表獲取結(jié)果的select
3、type：是指MySQL在表中找到所需行的方式，也就是訪問行的“類型”，從a開始，效率逐漸上升：?
a：all：全表掃描，效率最低
b：index:index會根據(jù)索引樹遍歷
c：range:索引范圍掃描，返回匹配值域的行。
d：ref:非唯一性索引掃描，返回匹配某個單獨值的所有行。一般是指多列的唯一索引中的某一列。
e：eq_ref:唯一性索引掃描，表中只有一條記錄與之匹配。
f：const、system：主要針對查詢中有常量的情況，如果結(jié)果只有一行會變成system
g：NULL:顯而易見，既不走表，也不走索引
4、possible_keys?
? ? ? ?possible_keys列預(yù)估了mysql能夠為當(dāng)前查詢選擇的索引，這個字段是完全獨立于執(zhí)行計劃中輸出的表的順序，意味著在實際查詢中可能用不到這些索引。?
? ? ? ?如果該字段為空則意味著沒有可使用的索引，這個時候你可以考慮為where后面的字段建立索引。
5、key?
? ? ? ?這個字段表示了mysql真實使用的索引（如果為NULL，則沒有使用索引）。如果mysql優(yōu)化過程中沒有加索引，可以強(qiáng)制加hint使用索引。
6、key_len?
? ? ? ?索引長度字段顧名思義，表示了mysql查詢中使用的索引的長度（最大可能長度），并非實際使用長度，理論上長度越短越好。key_len是根據(jù)表定義計算而得的，不是通過表內(nèi)檢索出的。
7、ref?
? ? ? ?這個字段一般是指一些常量用于選擇過濾（顯示索引的那一列被使用了，如果可能，是一個常量const）。
8、rows?
? ? ? ?預(yù)估結(jié)果集的條數(shù)，可能不一定完全準(zhǔn)確（根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)）。
9、Extra
? ? ? ?不適合在其他字段中顯示，但是十分重要的額外信息：
a：Using filesort：mysql對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引進(jìn)行排序讀取。也就是說mysql無法利用索引完成的排序操作成為“文件排序”。
b：Using temporary：使用臨時表保存中間結(jié)果，也就是說mysql在對查詢結(jié)果排序時使用了臨時表，常見于order by 和 group by。
c：Using index：表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index），避免了訪問表的數(shù)據(jù)行，效率高（不要使用select *）；如果同時出現(xiàn)Using where，表明索引被用來執(zhí)行索引鍵值的查找；如果沒用同時出現(xiàn)Using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。
d：Using join buffer：使用了鏈接緩存。
e：eq_ref:唯一性索引掃描，表中只有一條記錄與之匹配。
f：Impossible WHERE：where子句的值總是false，不能用來獲取任何元祖。
g：select tables optimized away：在沒有g(shù)roup by子句的情況下，基于索引優(yōu)化MIN/MAX操作或者對于MyISAM存儲引擎優(yōu)化COUNT（*）操作，不必等到執(zhí)行階段在進(jìn)行計算，查詢執(zhí)行計劃生成的階段即可完成優(yōu)化。
H：distinct：優(yōu)化distinct操作，在找到第一個匹配的元祖后即停止找同樣值得動作。
4.3?常見誤區(qū)
1、count(1)和count(primary_key)優(yōu)于count(*)
? ? ? ?很多人為了統(tǒng)計記錄條數(shù)，就使用?count(1)?和count(primary_key)?而不是?count(*)?，他們認(rèn)為這樣性能更好，其實這是一個誤區(qū)。對于有些場景，這樣做可能性能會更差，應(yīng)為數(shù)據(jù)庫對?count(*)?計數(shù)操作做了一些特別的優(yōu)化。
2、count(column)和count(*)是一樣的
? ? ? ?這個誤區(qū)甚至在很多的資深工程師或者是?DBA?中都普遍存在，很多人都會認(rèn)為這是理所當(dāng)然的。實際上，count(column)和?count(*)?是一個完全不一樣的操作，所代表的意義也完全不一樣。
? ? ? ?count(column)?是表示結(jié)果集中有多少個column字段不為空的記錄；
? ? ? ?count(*)?是表示整個結(jié)果集有多少條記錄。
3、select a,bfrom?…比?selecta,b,c from?…可以讓數(shù)據(jù)庫訪問更少的數(shù)據(jù)量
? ? ? ?這個誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據(jù)庫的存儲原理不是太了解。
? ? ? ?實際上，大多數(shù)關(guān)系型數(shù)據(jù)庫都是按照行（row）的方式存儲，而數(shù)據(jù)存取操作都是以一個固定大小的IO單元（被稱作block?或者?page）為單位，一般為4KB，8KB…大多數(shù)時候，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段（lob等特殊類型字段除外）。
? ? ? ?所以，我們是取一個字段還是多個字段，實際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實是一樣的。
? ? ? ?當(dāng)然，也有例外情況，那就是我們的這個查詢在索引中就可以完成，也就是說當(dāng)只取?a,b兩個字段的時候，不需要回表，而c這個字段不在使用的索引中，需要回表取得其數(shù)據(jù)。在這樣的情況下，二者的IO量會有較大差異。
4、order by一定需要排序操作
? ? ? ?我們知道索引數(shù)據(jù)實際上是有序的，如果我們的需要的數(shù)據(jù)和某個索引的順序一致，而且我們的查詢又通過這個索引來執(zhí)行，那么數(shù)據(jù)庫一般會省略排序操作，而直接將數(shù)據(jù)返回，因為數(shù)據(jù)庫知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了。
? ? ? ?實際上，利用索引來優(yōu)化有排序需求的?SQL，是一個非常重要的優(yōu)化手段
5、執(zhí)行計劃中有?filesort?就會進(jìn)行磁盤文件排序
? ? ? ?有這個誤區(qū)其實并不能怪我們，而是因為?MySQL?開發(fā)者在用詞方面的問題。filesort是我們在使用?explain?命令查看一條SQL?的執(zhí)行計劃的時候可能會看到在“Extra”一列顯示的信息。
? ? ? ?實際上，只要一條?SQL?語句需要進(jìn)行排序操作，都會顯示“Using filesort”，這并不表示就會有文件排序操作。
1、盡量少 join
? ? ? ?MySQL?的優(yōu)勢在于簡單，但這在某些方面其實也是其劣勢。MySQL優(yōu)化器效率高，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復(fù)雜的多表?Join，一方面由于其優(yōu)化器受限，再者在Join這方面所下的功夫還不夠，所以性能表現(xiàn)離Oracle等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。
2、盡量少排序
? ? ? ?排序操作會消耗較多的?CPU?資源，所以減少排序可以在緩存命中率高等?IO?能力足夠的場景下會較大影響?SQL的響應(yīng)時間。
? ? ? ?對于MySQL來說，減少排序有多種辦法，比如：
o? ? 上面誤區(qū)中提到的通過利用索引來排序的方式進(jìn)行優(yōu)化
o? ? 減少參與排序的記錄條數(shù)
o? ? 非必要不對數(shù)據(jù)進(jìn)行排序
o? ? 避免使用耗費(fèi)資源的操作，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語句會啟動SQL引擎 執(zhí)行，耗費(fèi)資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序
o? ? …
3、盡量避免 select *
? ? ? ?很多人看到這一點后覺得比較難理解，上面不是在誤區(qū)中剛剛說?select?子句中字段的多少并不會影響到讀取的數(shù)據(jù)嗎？
? ? ? ?是的，大多數(shù)時候并不會影響到?IO?量，但是當(dāng)我們還存在order by?操作的時候，select?子句中的字段多少會在很大程度上影響到我們的排序效率。
? ? ? ?此外，上面誤區(qū)中不是也說了，只是大多數(shù)時候是不會影響到IO量，當(dāng)我們的查詢結(jié)果僅僅只需要在索引中就能找到的時候，還是會極大減少IO量的。
4、盡量用join代替子查詢
? ? ? ?雖然Join性能并不佳，但是和MySQL的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認(rèn)這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。
5、盡量少or
? ? ? ?當(dāng)?where?子句中存在多個條件以“或”并存的時候，MySQL?的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題，再加上MySQL?特有的?SQL?與?Storage?分層架構(gòu)方式，造成了其性能比較低下，很多時候使用?union all?或者是union（必要的時候）的方式來代替“or”會得到更好的效果。
6、盡量用 union all 代替 union
? ? ? ?union?和?union all?的差異主要是前者需要將兩個（或者多個）結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會涉及到排序，增加大量的?CPU?運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時候，盡量使用?union all?而不是?union。
7、盡量早過濾
? ? ? ?這一優(yōu)化策略其實最常見于索引的優(yōu)化設(shè)計中（將過濾性更好的字段放得更靠前）。? ? ? ?在?SQL?編寫中同樣可以使用這一原則來優(yōu)化一些?Join?的?SQL。比如我們在多個表進(jìn)行分頁數(shù)據(jù)查詢的時候，我們最好是能夠在一個表上先過濾好數(shù)據(jù)分好頁，然后再用分好頁的結(jié)果集與另外的表?Join，這樣可以盡可能多的減少不必要的?IO?操作，大大節(jié)省?IO?操作所消耗的時間。
8、避免類型轉(zhuǎn)換
? ? ? ?這里所說的“類型轉(zhuǎn)換”是指?where?子句中出現(xiàn)?column?字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換：
? ? ? ?o??? 人為在column_name 上通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
? ? ? ?直接導(dǎo)致 MySQL（實際上其他數(shù)據(jù)庫也會有同樣的問題）無法使用索引，如果非要轉(zhuǎn)換，應(yīng)該在傳入的參數(shù)上進(jìn)行轉(zhuǎn)換
? ? ? ?o??? 由數(shù)據(jù)庫自己進(jìn)行轉(zhuǎn)換
? ? ? ?如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時我們又沒有做任何類型轉(zhuǎn)換處理，MySQL 可能會自己對我們的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作，也可能不進(jìn)行處理而交由存儲引擎去處理，這樣一來，就會出現(xiàn)索引無法使用的情況而造成執(zhí)行計劃問題。
??????? SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
? ? ? ?不要使用：SELECT emp.ename, emp.job FROM emp WHEREemp.empno = '7369'
9、能用DISTINCT的就不用GROUP BY
? ? ? ?group by?操作特別慢，比如：
? ? ? ?SELECT OrderID FROM DetailsWHERE UnitPrice > 10 GROUP BY OrderID
? ? ? ?可改為：?
? ? ? ?SELECT DISTINCT OrderID FROMDetails WHERE UnitPrice > 10
10、盡量不要用SELECT INTO語句
? ? ? ?SELECT INOT?語句會導(dǎo)致表鎖定，阻止其他用戶訪問該表
11、優(yōu)先優(yōu)化高并發(fā)的SQL，而不是執(zhí)行頻率低某些“大”SQL
? ? ? ?對于破壞性來說，高并發(fā)的?SQL?總是會比低頻率的來得大，因為高并發(fā)的SQL一旦出現(xiàn)問題，甚至不會給我們?nèi)魏未⒌臋C(jī)會就會將系統(tǒng)壓跨。而對于一些雖然需要消耗大量?IO?而且響應(yīng)很慢的?SQL，由于頻率低，即使遇到，最多就是讓整個系統(tǒng)響應(yīng)慢一點，但至少可能撐一會兒，讓我們有緩沖的機(jī)會。
12、從全局出發(fā)優(yōu)化，而不是片面調(diào)整
? ? ? ?SQL?優(yōu)化不能是單獨針對某一個進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的?SQL，尤其是在通過調(diào)整索引優(yōu)化?SQL的執(zhí)行計劃的時候，千萬不能顧此失彼，因小失大。
13、盡可能對每一條運(yùn)行在數(shù)據(jù)庫中的SQL進(jìn)行explain
? ? ? ?優(yōu)化?SQL，需要做到心中有數(shù)，知道?SQL的執(zhí)行計劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計劃問題。在對數(shù)據(jù)庫中運(yùn)行的?SQL?進(jìn)行了一段時間的優(yōu)化之后，很明顯的問題?SQL?可能已經(jīng)很少了，大多都需要去發(fā)掘，這時候就需要進(jìn)行大量的?explain?操作收集執(zhí)行計劃，并判斷是否需要進(jìn)行優(yōu)化。
14、其他優(yōu)化方式
? ? ? ?（1）適當(dāng)使用視圖加速查詢：把表的一個子集進(jìn)行排序并創(chuàng)建視圖，有時能加速查詢（特別是要被多次執(zhí)行的查詢）。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。視圖中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。
? ? ? ?（2）算法優(yōu)化：盡量避免使用游標(biāo)，因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。.使用基于游標(biāo)的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。與臨時表一樣，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用?FAST_FORWARD?游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。
? ? ? ?游標(biāo)提供了對特定集合中逐行掃描的手段，一般使用游標(biāo)逐行遍歷數(shù)據(jù)，根據(jù)取出的數(shù)據(jù)不同條件進(jìn)行不同的操作。尤其對多表和大表定義的游標(biāo)（大的數(shù)據(jù)集合）循環(huán)很容易使程序進(jìn)入一個漫長的等特甚至死機(jī)。
? ? ? ?在有些場合，有時也非得使用游標(biāo)，此時也可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時表中，再對臨時表定義游標(biāo)進(jìn)行操作，可時性能得到明顯提高。
? ? ? ?（3）封裝存儲過程：經(jīng)編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中，運(yùn)行效率高，可以降低客戶機(jī)和服務(wù)器之間的通信量，有利于集中控制，易于維護(hù)。
5?表結(jié)構(gòu)優(yōu)化
? ? ? ?由于MySQL數(shù)據(jù)庫是基于行（Row）存儲的數(shù)據(jù)庫，而數(shù)據(jù)庫操作?IO?的時候是以?page（block）的方式，也就是說，如果我們每條記錄所占用的空間量減小，就會使每個page中可存放的數(shù)據(jù)行數(shù)增大，那么每次?IO?可訪問的行數(shù)也就增多了。反過來說，處理相同行數(shù)的數(shù)據(jù)，需要訪問的?page?就會減少，也就是?IO?操作次數(shù)降低，直接提升性能。此外，由于我們的內(nèi)存是有限的，增加每個page中存放的數(shù)據(jù)行數(shù)，就等于增加每個內(nèi)存塊的緩存數(shù)據(jù)量，同時還會提升內(nèi)存交換中數(shù)據(jù)命中的幾率，也就是緩存命中率。
一、數(shù)據(jù)類型選擇
? ? ? ?數(shù)據(jù)庫操作中最為耗時的操作就是?IO?處理，大部分?jǐn)?shù)據(jù)庫操作?90%以上的時間都花在了?IO?讀寫上面。所以盡可能減少?IO讀寫量，可以在很大程度上提高數(shù)據(jù)庫操作的性能。我們無法改變數(shù)據(jù)庫中需要存儲的數(shù)據(jù)，但是我們可以在這些數(shù)據(jù)的存儲方式方面花一些心思。原則是：數(shù)據(jù)行的長度不要超過8020字節(jié)，如果超過這個長度的話在物理頁中這條數(shù)據(jù)會占用兩行從而造成存儲碎片，降低查詢效率；字段的長度在最大限度的滿足可能的需要的前提下，應(yīng)該盡可能的設(shè)得短一些，這樣可以提高查詢的效率，而且在建立索引的時候也可以減少資源的消耗。
? ? ? ?下面的這些關(guān)于字段類型的優(yōu)化建議主要適用于記錄條數(shù)較多，數(shù)據(jù)量較大的場景，因為精細(xì)化的數(shù)據(jù)類型設(shè)置可能帶來維護(hù)成本的提高，過度優(yōu)化也可能會帶來其他的問題：
? ? ? ?1、數(shù)字類型：非萬不得已不要使用DOUBLE，不僅僅只是存儲長度的問題，同時還會存在精確性的問題。同樣，固定精度的小數(shù)，也不建議使用DECIMAL，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲，可以大大節(jié)省存儲空間，且不會帶來任何附加維護(hù)成本。對于整數(shù)的存儲，在數(shù)據(jù)量較大的情況下，建議區(qū)分開?TINYINT / INT / BIGINT?的選擇，因為三者所占用的存儲空間也有很大的差別，能確定不會使用負(fù)數(shù)的字段，建議添加unsigned定義。當(dāng)然，如果數(shù)據(jù)量較小的數(shù)據(jù)庫，也可以不用嚴(yán)格區(qū)分三個整數(shù)類型。
? ? ? ?能夠用數(shù)字類型的字段盡量選擇數(shù)字類型而不用字符串類型的（電話號碼），這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。
? ? ? ?2、字符類型：非萬不得已不要使用?TEXT?數(shù)據(jù)類型，其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理。定長字段，建議使用?CHAR?類型（char查詢快，但是耗存儲空間，可用于用戶名、密碼等長度變化不大的字段），不定長字段盡量使用?VARCHAR（varchar查詢相對慢一些但是節(jié)省存儲空間，可用于評論等長度變化大的字段），且僅僅設(shè)定適當(dāng)?shù)淖畲箝L度，而不是非常隨意的給一個很大的最大長度限定，因為不同的長度范圍，MySQL也會有不一樣的存儲處理。
? ? ? ?3、時間類型：盡量使用TIMESTAMP類型，因為其存儲空間只需要DATETIME?類型的一半。對于只需要精確到某一天的數(shù)據(jù)類型，建議使用DATE類型，因為他的存儲空間只需要3個字節(jié)，比TIMESTAMP還少。不建議通過INT類型類存儲一個unix timestamp?的值，因為這太不直觀，會給維護(hù)帶來不必要的麻煩，同時還不會帶來任何好處。
? ? ? ?4、ENUM &SET：對于狀態(tài)字段，可以嘗試使用?ENUM?來存放，因為可以極大的降低存儲空間，而且即使需要增加新的類型，只要增加于末尾，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢？可以嘗試使用SET類型，即使存在多種屬性，同樣可以游刃有余，同時還可以節(jié)省不小的存儲空間。
? ? ? ?5、LOB類型：強(qiáng)烈反對在數(shù)據(jù)庫中存放?LOB類型數(shù)據(jù)，雖然數(shù)據(jù)庫提供了這樣的功能，但這不是他所擅長的，我們更應(yīng)該讓合適的工具做他擅長的事情，才能將其發(fā)揮到極致。在數(shù)據(jù)庫中存儲?LOB?數(shù)據(jù)就像讓一個多年前在學(xué)校學(xué)過一點Java的營銷專業(yè)人員來寫?Java?代碼一樣。
二、字符編碼
? ? ? ?字符集直接決定了數(shù)據(jù)在MySQL中的存儲編碼方式，由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會有較大的差異，所以通過使用合適的字符集，可以幫助我們盡可能減少數(shù)據(jù)量，進(jìn)而減少IO操作次數(shù)。
? ? ? ?1、純拉丁字符能表示的內(nèi)容，沒必要選擇?latin1?之外的其他字符編碼，因為這會節(jié)省大量的存儲空間；
? ? ? ?2、如果我們可以確定不需要存放多種語言，就沒必要非得使用UTF8或者其他UNICODE字符類型，這回造成大量的存儲空間浪費(fèi)；
? ? ? ?3、MySQL的數(shù)據(jù)類型可以精確到字段，所以當(dāng)我們需要大型數(shù)據(jù)庫中存放多字節(jié)數(shù)據(jù)的時候，可以通過對不同表不同字段使用不同的數(shù)據(jù)類型來較大程度減小數(shù)據(jù)存儲量，進(jìn)而降低?IO?操作次數(shù)并提高緩存命中率。
三、適當(dāng)拆分
? ? ? ?有些時候，我們可能會希望將一個完整的對象對應(yīng)于一張數(shù)據(jù)庫表，這對于應(yīng)用程序開發(fā)來說是很有好的，但是有些時候可能會在性能上帶來較大的問題。當(dāng)我們的表中存在類似于?TEXT?或者是很大的?VARCHAR類型的大字段的時候，如果我們大部分訪問這張表的時候都不需要這個字段，我們就該義無反顧的將其拆分到另外的獨立表中，以減少常用數(shù)據(jù)所占用的存儲空間。這樣做的一個明顯好處就是每個數(shù)據(jù)塊中可以存儲的數(shù)據(jù)條數(shù)可以大大增加，既減少物理?IO?次數(shù)，也能大大提高內(nèi)存中的緩存命中率。
? ? ? ?上面幾點的優(yōu)化都是為了減少每條記錄的存儲空間大小，讓每個數(shù)據(jù)庫中能夠存儲更多的記錄條數(shù)，以達(dá)到減少?IO?操作次數(shù)，提高緩存命中率。下面這個優(yōu)化建議可能很多開發(fā)人員都會覺得不太理解，因為這是典型的反范式設(shè)計，而且也和上面的幾點優(yōu)化建議的目標(biāo)相違背。
四、適度冗余
? ? ? ?為什么我們要冗余？這不是增加了每條數(shù)據(jù)的大小，減少了每個數(shù)據(jù)塊可存放記錄條數(shù)嗎？確實，這樣做是會增大每條記錄的大小，降低每條記錄中可存放數(shù)據(jù)的條數(shù)，但是在有些場景下我們?nèi)匀贿€是不得不這樣做：
? ? ? ?被頻繁引用且只能通過?Join 2張（或者更多）大表的方式才能得到的獨立小字段
? ? ? ?這樣的場景由于每次Join僅僅只是為了取得某個小字段的值，Join到的記錄又大，會造成大量不必要的?IO，完全可以通過空間換取時間的方式來優(yōu)化。不過，冗余的同時需要確保數(shù)據(jù)的一致性不會遭到破壞，確保更新的同時冗余字段也被更新
五、盡量使用?NOT NULL
? ? ? ?NULL?類型比較特殊，SQL?難優(yōu)化。雖然?MySQL NULL類型和?Oracle?的NULL有差異，會進(jìn)入索引中，但如果是一個組合索引，那么這個NULL?類型的字段會極大影響整個索引的效率。此外，NULL在索引中的處理也是特殊的，也會占用額外的存放空間。
? ? ? ?很多人覺得?NULL?會節(jié)省一些空間，所以盡量讓NULL來達(dá)到節(jié)省IO的目的，但是大部分時候這會適得其反，雖然空間上可能確實有一定節(jié)省，倒是帶來了很多其他的優(yōu)化問題，不但沒有將IO量省下來，反而加大了SQL的IO量。所以盡量確保DEFAULT?值不是?NULL，也是一個很好的表結(jié)構(gòu)設(shè)計優(yōu)化習(xí)慣。
6?索引優(yōu)化
? ? ? ?大家都知道索引對于數(shù)據(jù)訪問的性能有非常關(guān)鍵的作用，都知道索引可以提高數(shù)據(jù)訪問效率。為什么索引能提高數(shù)據(jù)訪問性能？他會不會有“副作用”？是不是索引創(chuàng)建越多，性能就越好？到底該如何設(shè)計索引，才能最大限度的發(fā)揮其效能？這篇文章主要是帶著上面這幾個問題來做一個簡要的分析，同時排除了業(yè)務(wù)場景所帶來的特殊性，請不要糾結(jié)業(yè)務(wù)場景的影響。
? ? ? ?索引為什么能提高數(shù)據(jù)訪問性能？
? ? ? ?很多人只知道索引能夠提高數(shù)據(jù)庫的性能，但并不是特別了解其原理，其實我們可以用一個生活中的示例來理解。我們讓一位不太懂計算機(jī)的朋友去圖書館確認(rèn)一本叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》的書是否在藏，這樣對他說：“請幫我借一本計算機(jī)類的數(shù)據(jù)庫書籍，是屬于?MySQL?數(shù)據(jù)庫范疇的，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》”。朋友會根據(jù)所屬類別，前往存放“計算機(jī)”書籍區(qū)域的書架，然后再尋找“數(shù)據(jù)庫”類存放位置，再找到一堆講述“MySQL”的書籍，最后可能發(fā)現(xiàn)目標(biāo)在藏（也可能已經(jīng)借出不在書架上）。在這個過程中：“計算機(jī)”->“數(shù)據(jù)庫”->“MySQL”->“在藏”->《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》其實就是一個“根據(jù)索引查找數(shù)據(jù)”的典型案例，“計算機(jī)”->“數(shù)據(jù)庫”->“MySQL”->“在藏”就是朋友查找書籍的索引。假設(shè)沒有這個索引，那查找這本書的過程會變成怎樣呢？朋友只能從圖書館入口一個書架一個書架的“遍歷”，直到找到《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》這本書為止。如果幸運(yùn)，可能在第一個書架就找到。但如果不幸呢，那就慘了，可能要將整個圖書館所有的書架都找一遍才能找到我們想要的這本書。注：這個例子中的“索引”是記錄在我們大腦中的，實際上，每個圖書館都會有一個非常全的實際存在的索引系統(tǒng)（大多位于入口顯眼處），由很多個貼上了明顯標(biāo)簽的小抽屜構(gòu)成。這個索引系統(tǒng)中存放這非常齊全詳盡的索引數(shù)據(jù)，標(biāo)識出我們需要查找的“目標(biāo)”在某個區(qū)域的某個書架上。而且每當(dāng)有新的書籍入庫，舊的書籍銷毀以及書記信息修改，都需要對索引系統(tǒng)進(jìn)行及時的修正。
? ? ? ?下面我們通過上面這個生活中的小示例，來分析一下索引，看看能的出哪些結(jié)論？
一、索引有哪些“副作用”？
? ? ? ?1、圖書的變更（增，刪，改）都需要修訂索引，索引存在額外的維護(hù)成本；
? ? ? ?2、查找翻閱索引系統(tǒng)需要消耗時間，索引存在額外的訪問成本；
? ? ? ?3、這個索引系統(tǒng)需要一個地方來存放，索引存在額外的空間成本。
二、索引是不是越多越好？
? ? ? ?1、如果我們的這個圖書館只是一個進(jìn)出中轉(zhuǎn)站，里面的新書進(jìn)來后很快就會轉(zhuǎn)發(fā)去其他圖書館而從這個館藏中“清除”，那我們的索引就只會不斷的修改，而很少會被用來查找圖書。
? ? ? ?所以，對于類似于這樣的存在大量和頻繁更新的數(shù)據(jù)，索引的維護(hù)成本會非常高，如果其檢索需求很少，而且對檢索效率并沒有非常高的要求的時候，我們并不建議創(chuàng)建索引，或者是盡量減少索引。
? ? ? ?2、如果我們的書籍量少到只有幾本或者就只有一個書架，索引并不會帶來什么作用，甚至可能還會浪費(fèi)一些查找索引所花費(fèi)的時間。
? ? ? ?所以，對于數(shù)據(jù)量極小到通過索引檢索還不如直接遍歷來得快的數(shù)據(jù)，也并不適合使用索引。
? ? ? ?3、如果我們的圖書館只有一個10平方的面積，現(xiàn)在連放書架都已經(jīng)非常擁擠，而且館藏還在不斷增加，我們還能考慮創(chuàng)建索引嗎？
? ? ? ?所以，當(dāng)我們連存儲基礎(chǔ)數(shù)據(jù)的空間都捉襟見肘的時候，我們也應(yīng)該盡量減少低效或者是去除索引。
三、索引該如何設(shè)計才高效？
? ? ? ?1、如果我們僅僅只是這樣告訴對方的：“幫我確認(rèn)一本數(shù)據(jù)庫類別的講述?MySQL的叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》的書是否在藏”，結(jié)果又會如何呢？朋友只能一個大類區(qū)域一個大類區(qū)域的去尋找“數(shù)據(jù)庫”類別，然后再找到“MySQL”范疇，再看到我們所需是否在藏。由于我們少說了一個“計算機(jī)類”，朋友就必須到每一個大類去尋找。
? ? ? ?所以，我們應(yīng)該盡量讓查找條件盡可能多的在索引中，盡可能通過索引完成所有過濾，回表只是取出額外的數(shù)據(jù)字段。
? ? ? ?2、如果我們是這樣說的：“幫我確認(rèn)一本講述?MySQL?的數(shù)據(jù)庫范疇的計算機(jī)叢書，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》，看是否在藏”。如果這位朋友并不知道計算機(jī)是一個大類，也不知道數(shù)據(jù)庫屬于計算機(jī)大類，那這位朋友就悲劇了。首先他得遍歷每個類別確認(rèn)“MySQL”存在于哪些類別中，然后從包含“MySQL”書籍中再看有哪些是“數(shù)據(jù)庫”范疇的（有可能部分是講述PHP或者其他開發(fā)語言的），然后再排除非計算機(jī)類的（雖然可能并沒有必要），然后才能確認(rèn)。
? ? ? ?所以，字段的順序?qū)M合索引效率有至關(guān)重要的作用，過濾效果越好的字段需要更靠前。
? ? ? ?3、如果我們還有這樣一個需求（雖然基本不可能）：“幫我將圖書館中所有的計算機(jī)圖書借來”。朋友如果通過索引來找，每次都到索引柜找到計算機(jī)書籍所在的區(qū)域，然后從書架上搬下一格（假設(shè)只能以一格為單位從書架上取下，類比數(shù)據(jù)庫中以block/page為單位讀?。?，取出第一本，然后再從索引柜找到計算機(jī)圖書所在區(qū)域，再搬下一格，取出一本…?如此往復(fù)直至取完所有的書。如果他不通過索引來找又會怎樣呢？他需要從第一個書架一直往后找，當(dāng)找到計算機(jī)的書，搬下一格，取出所有計算機(jī)的書，再往后，直至所有書架全部看一遍。在這個過程中，如果計算機(jī)類書籍較多，通過索引來取所花費(fèi)的時間很可能要大于直接遍歷，因為不斷往復(fù)的索引翻閱所消耗的時間會非常長。（延伸閱讀：可以參照Oracle的索引優(yōu)化進(jìn)行解讀，索引掃描還是全表掃描（Index Scan Or Full Table Scan））
? ? ? ?所以，當(dāng)我們需要讀取的數(shù)據(jù)量占整個數(shù)據(jù)量的比例較大抑或者說索引的過濾效果并不是太好的時候，使用索引并不一定優(yōu)于全表掃描。
? ? ? ?4、如果我們的朋友不知道“數(shù)據(jù)庫”這個類別可以屬于“計算機(jī)”這個大類，抑或者圖書館的索引系統(tǒng)中這兩個類別屬性并沒有關(guān)聯(lián)關(guān)系，又會怎樣呢？也就是說，朋友得到的是2個獨立的索引，一個是告知“計算機(jī)”這個大類所在的區(qū)域，一個是“數(shù)據(jù)庫”這個小類所在的區(qū)域（很可能是多個區(qū)域），那么他只能二者選其一來搜索我的需求。即使朋友可以分別通過2個索引檢索然后自己在腦中取交集再找，那這樣的效率實際過程中也會比較低下。
? ? ? ?所以，在實際使用過程中，一次數(shù)據(jù)訪問一般只能利用到1個索引，這一點在索引創(chuàng)建過程中一定要注意，不是說一條SQL語句中Where子句里面每個條件都有索引能對應(yīng)上就可以了。
? ? ? ?5、最后總結(jié)一下法則：不要在建立的索引的數(shù)據(jù)列上進(jìn)行下列操作：
◆避免對索引字段進(jìn)行計算操作
◆避免在索引字段上使用not，like ‘%L’，!=，<>，in，or連接
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換
◆避免在索引字段上使用函數(shù)
◆避免建立索引的列中使用空值。
7?架構(gòu)優(yōu)化
一、分布式和集群化
1、負(fù)載均衡
? ? ? ?負(fù)載均衡集群是由一組相互獨立的計算機(jī)系統(tǒng)構(gòu)成，通過常規(guī)網(wǎng)絡(luò)或?qū)Ｓ镁W(wǎng)絡(luò)進(jìn)行連接，由路由器銜接在一起，各節(jié)點相互協(xié)作、共同負(fù)載、均衡壓力，對客戶端來說，整個群集可以視為一臺具有超高性能的獨立服務(wù)器。MySQL一般部署的是高可用性負(fù)載均衡集群，具備讀寫分離，一般只對讀進(jìn)行負(fù)載均衡。
2、讀寫分離
? ? ? ?讀寫分離簡單的說是把對數(shù)據(jù)庫讀和寫的操作分開對應(yīng)不同的數(shù)據(jù)庫服務(wù)器，這樣能有效地減輕數(shù)據(jù)庫壓力，也能減輕io壓力。主數(shù)據(jù)庫提供寫操作，從數(shù)據(jù)庫提供讀操作，其實在很多系統(tǒng)中，主要是讀的操作。當(dāng)主數(shù)據(jù)庫進(jìn)行寫操作時，數(shù)據(jù)要同步到從的數(shù)據(jù)庫，這樣才能有效保證數(shù)據(jù)庫完整性。
3、數(shù)據(jù)切分
? ? ? ?通過某種特定的條件，將存放在同一個數(shù)據(jù)庫中的數(shù)據(jù)分散存放到多個數(shù)據(jù)庫上，實現(xiàn)分布存儲，通過路由規(guī)則路由訪問特定的數(shù)據(jù)庫，這樣一來每次訪問面對的就不是單臺服務(wù)器了，而是N臺服務(wù)器，這樣就可以降低單臺機(jī)器的負(fù)載壓力。數(shù)據(jù)切分一般包括垂直切分和水平切分。
? ? ? ?數(shù)據(jù)的垂直切分，也可以稱之為縱向切分。將數(shù)據(jù)庫想象成為由很多個一大塊一大塊的“數(shù)據(jù)塊”（表）組成，我們垂直的將這些“數(shù)據(jù)塊”切開，然后將他們分散到多臺數(shù)據(jù)庫主機(jī)上面。這樣的切分方法就是一個垂直（縱向）的數(shù)據(jù)切分。
? ? ? ?數(shù)據(jù)的垂直切分基本上可以簡單的理解為按照表、按照模塊來切分?jǐn)?shù)據(jù)，而水平切分就不再是按照表或者是功能模塊來切分了。一般來說，簡單的水平切分主要是將某個訪問極其平凡的表再按照某個字段的某種規(guī)則來分散到多個表之中，每個表中包含一部分?jǐn)?shù)據(jù)。
二、Cache與Search的利用
? ? ? ??通過引入Cache（Redis、Memcached），減少數(shù)據(jù)庫的訪問，降低磁盤的IO，增加性能。
? ? ? ?通過引入Search（Lucene、Solr、ElasticSearch），利用搜索引擎高效的全文索引和分詞算法，以及高效的數(shù)據(jù)檢索實現(xiàn)，來解決數(shù)據(jù)庫和傳統(tǒng)的Cache軟件完全無法解決的全文模糊搜索、分類統(tǒng)計查詢等功能。
