MYSQL應該是最流行的WEB后端數(shù)據(jù)庫目养。大量應用于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ù)場景下合理的運用。
? ? ? ?如果能在頭腦中構(gòu)建一幅MySQL各組件之間如何協(xié)同工作的架構(gòu)圖甥啄，有助于深入理解MySQL服務(wù)器存炮。下圖展示了MySQL的邏輯架構(gòu)圖。
MySQL邏輯架構(gòu)
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)化器能夠按照預想的合理方式運行而已。
當向MySQL發(fā)送一個請求的時候希坚，MySQL到底做了些什么呢边苹？
MySQL查詢過程
客戶端/服務(wù)端通信協(xié)議
MySQL客戶端/服務(wù)端通信協(xié)議是“半雙工”的：在任一時刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)裁僧，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生。一旦一端開始發(fā)送消息蜡吧，另一端要接收完整個消息才能響應它搭儒，所以我們無法也無須將一個消息切成小塊獨立發(fā)送，也沒有辦法進行流量控制获洲。
客戶端用一個單獨的數(shù)據(jù)包將查詢請求發(fā)送給服務(wù)器阱表，所以當查詢語句很長的時候，需要設(shè)置max_allowed_packet參數(shù)昌妹。但是需要注意的是捶枢，如果查詢實在是太大，服務(wù)端會拒絕接收更多數(shù)據(jù)并拋出異常飞崖。
與之相反的是烂叔，服務(wù)器響應給用戶的數(shù)據(jù)通常會很多，由多個數(shù)據(jù)包組成固歪。但是當服務(wù)器響應客戶端請求時蒜鸡，客戶端必須完整的接收整個返回結(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é)果的信息計算得來贪婉。所以兩個查詢在任何字符上的不同（例如：空格、注釋）卢肃，都會導致緩存不會命中疲迂。
如果查詢中包含任何用戶自定義函數(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必須將對應表的所有緩存都設(shè)置為失效。如果查詢緩存非常大或者碎片很多拘悦，這個操作就可能帶來很大的系統(tǒng)消耗齿兔，甚至導致系統(tǒng)僵死一會兒。而且查詢緩存對系統(tǒng)的額外消耗也不僅僅在寫操作础米，讀操作也不例外：? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
1.任何的查詢語句在開始之前都必須經(jīng)過檢查分苇，即使這條SQL語句永遠不會命中緩存
2.如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后椭盏，會將結(jié)果存入緩存组砚，也會帶來額外的系統(tǒng)消耗
基于此吻商，我們要知道并不是什么情況下查詢緩存都會提高系統(tǒ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來控制某個查詢語句是否需要進行緩存
最后的忠告是不要輕易打開查詢緩存，特別是寫密集型應用葡公。如果你實在是忍不住罐农，可以將query_cache_type設(shè)置為DEMAND，這時只有加入SQL_CACHE的查詢才會走緩存催什，其他查詢則不會涵亏，這樣可以非常自由地控制哪些查詢需要被緩存。
當然查詢緩存系統(tǒng)本身是非常復雜的蒲凶，這里討論的也只是很小的一部分气筋，其他更深入的話題，比如：緩存是如何使用內(nèi)存的旋圆？如何控制內(nèi)存的碎片化宠默？事務(wù)對查詢緩存有何影響等等，讀者可以自行閱讀相關(guān)資料灵巧，這里權(quán)當拋磚引玉吧光稼。
語法解析和預處理
MySQL通過關(guān)鍵字將SQL語句進行解析，并生成一顆對應的解析樹孩等。這個過程解析器主要通過語法規(guī)則來驗證和解析艾君。比如SQL中是否使用了錯誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等。預處理則會根據(jù)MySQL規(guī)則進一步檢查解析樹是否合法肄方。比如檢查要查詢的數(shù)據(jù)表和數(shù)據(jù)列是否存在等等冰垄。
查詢優(yōu)化
經(jīng)過前面的步驟生成的語法樹被認為是合法的了，并且由優(yōu)化器將其轉(zhuǎn)化成查詢計劃权她。多數(shù)情況下虹茶，一條查詢可以有很多種執(zhí)行方式，最后都返回相應的結(jié)果隅要。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計劃蝴罪。
MySQL使用基于成本的優(yōu)化器，它嘗試預測一個查詢使用某種執(zhí)行計劃時的成本步清，并選擇其中成本最小的一個要门。在MySQL可以通過查詢當前會話的last_query_cost的值來得到其計算當前查詢的成本虏肾。
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)化器認為大概需要做6391個數(shù)據(jù)頁的隨機查找才能完成上面的查詢。這個結(jié)果是根據(jù)一些列的統(tǒng)計信息計算得來的欢搜，這些統(tǒng)計信息包括：每張表或者索引的頁面?zhèn)€數(shù)封豪、索引的基數(shù)、索引和數(shù)據(jù)行的長度炒瘟、索引的分布情況等等吹埠。
有非常多的原因會導致MySQL選擇錯誤的執(zhí)行計劃，比如統(tǒng)計信息不準確疮装、不會考慮不受其控制的操作成本（用戶自定義函數(shù)缘琅、存儲過程）、MySQL認為的最優(yōu)跟我們想的不一樣（我們希望執(zhí)行時間盡可能短廓推，但MySQL值選擇它認為成本小的胯杭，但成本小并不意味著執(zhí)行時間短）等等。
MySQL的查詢優(yōu)化器是一個非常復雜的部件受啥，它使用了非常多的優(yōu)化策略來生成一個最優(yōu)的執(zhí)行計劃：
1.重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢時做个，并不一定按照SQL中指定的順序進行，但有一些技巧可以指定關(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密集型應用，效率會高很多）
隨著MySQL的不斷發(fā)展涨缚，優(yōu)化器使用的優(yōu)化策略也在不斷的進化轧粟，這里僅僅介紹幾個非常常用且容易理解的優(yōu)化策略，其他的優(yōu)化策略脓魏，大家自行查閱吧兰吟。
查詢執(zhí)行引擎
? ? ? ?在完成解析和優(yōu)化階段以后，MySQL會生成對應的執(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é)議進行傳輸，在傳輸過程中躯嫉，可能對MySQL的數(shù)據(jù)包進行緩存然后批量發(fā)送纱烘。
回頭總結(jié)一下MySQL整個查詢執(zhí)行過程，總的來說分為6個步驟：
? ? ? ?1.客戶端向MySQL服務(wù)器發(fā)送一條查詢請求
? ? ? ?2.服務(wù)器首先檢查查詢緩存祈餐，如果命中緩存擂啥，則立刻返回存儲在緩存中的結(jié)果。否則進入下一階段
? ? ? ?3.服務(wù)器進行SQL解析帆阳、預處理啤它、再由優(yōu)化器生成對應的執(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è)務(wù)場景。
一旬盯、MyISAM
o???特性
? ? ? ?1.不支持事務(wù)：MyISAM存儲引擎不支持事務(wù)台妆，所以對事務(wù)有要求的業(yè)務(wù)場景不能使用
? ? ? ?2.表級鎖定：其鎖定機制是表級索引翎猛，這雖然可以讓鎖定的實現(xiàn)成本很小但是也同時大大降低了其并發(fā)性能
? ? ? ?3.讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入接剩，但讀本身并不會阻塞另外的讀
? ? ? ?4.只會緩存索引：MyISAM可以通過key_buffer緩存以大大提高訪問性能減少磁盤IO切厘，但是這個緩存區(qū)只會緩存索引，而不會緩存數(shù)據(jù)
o???適用場景
? ? ? ?1.不需要事務(wù)支持（不支持）
? ? ? ?2.并發(fā)相對較低（鎖定機制問題）
? ? ? ?3.數(shù)據(jù)修改相對較少（阻塞問題）
? ? ? ?4.以讀為主
? ? ? ?5.數(shù)據(jù)一致性要求不是非常高
o???最佳實踐
? ? ? ?1.盡量索引（緩存機制）
? ? ? ?2.調(diào)整讀寫優(yōu)先級懊缺，根據(jù)實際需求確保重要操作更優(yōu)先
? ? ? ?3.啟用延遲插入改善大批量寫入性能
? ? ? ?4.盡量順序操作讓insert數(shù)據(jù)都寫入到尾部疫稿，減少阻塞
? ? ? ?5.分解大的操作，降低單個操作的阻塞時間
? ? ? ?6.降低并發(fā)數(shù)鹃两，某些高并發(fā)場景通過應用來進行排隊機制
? ? ? ?7.對于相對靜態(tài)的數(shù)據(jù)遗座，充分利用Query Cache可以極大的提高訪問效率
? ? ? ?8.MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進行實際的數(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ā)有很好的適應能力梯醒，但需要確保查詢是通過索引完成
? ? ? ?3.數(shù)據(jù)更新較為頻繁的場景
? ? ? ?4.數(shù)據(jù)一致性要求較高
? ? ? ?5.硬件設(shè)備內(nèi)存較大筋帖，可以利用InnoDB較好的緩存能力來提高內(nèi)存利用率，盡可能減少磁盤 IO
o???最佳實踐
? ? ? ?1.主鍵盡可能小冤馏，避免給Secondary index帶來過大的空間負擔
? ? ? ?2.避免全表掃描日麸，因為會使用表鎖
? ? ? ?3.盡可能緩存所有的索引和數(shù)據(jù)，提高響應速度
? ? ? ?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不在一臺主機：可以和mysqld分開存在于獨立的主機上杜漠，然后通過網(wǎng)絡(luò)和mysqld通信交互
4.內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中极景，老版本所有數(shù)據(jù)和索引必須存在與內(nèi)存中
o? ?適用場景
1.具有非常高的并發(fā)需求
2.對單個請求的響應并不是非常的critical
3.查詢簡單，過濾條件較為固定驾茴，每次請求數(shù)據(jù)量較少盼樟，又不希望自己進行水平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ù)進行IO優(yōu)化（建議級）：
? ? ? ?·? ?query_cache_type : 如果全部使用innodb存儲引擎，建議為0械拍，如果使用MyISAM 存儲引擎突勇，建議為2，同時在SQL語句中顯式控制是否使用query cache殊者；
? ? ? ?·? ?query_cache_size: 根據(jù)命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進行調(diào)整与境，一般不建議太大验夯，256MB可能已經(jīng)差不多了猖吴，大型的配置型靜態(tài)數(shù)據(jù)可適當調(diào)大；
? ? ? ?·? ? binlog_cache_size: 一般環(huán)境2MB～4MB是一個合適的選擇挥转，事務(wù)較大且寫入頻繁的數(shù)據(jù)庫環(huán)境可以適當調(diào)大海蔽，但不建議超過32MB；
? ? ? ?·? ? key_buffer_size: 如果不使用MyISAM存儲引擎绑谣，16MB足以党窜，用來緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲引擎借宵，在內(nèi)存允許的情況下幌衣，盡可能將所有索引放入內(nèi)存，簡單來說就是“越大越好”壤玫；
? ? ? ?·? ?bulk_insert_buffer_size: 如果經(jīng)常性的需要使用批量插入的特殊語句（上面有說明）來插入數(shù)據(jù)豁护，可以適當調(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: 默認是1MB对粪，系的如頻繁的系統(tǒng)可適當增大至4MB～8MB右冻。當然如上面介紹所說装蓬，這個參數(shù)實際上還和另外的flush參數(shù)相關(guān)。一般來說不建議超過32MB纱扭；
? ? ? ?·? ?innodb_max_dirty_pages_pct: 根據(jù)以往的經(jīng)驗牍帚，重啟恢復的數(shù)據(jù)如果要超過1G的話，啟動速度會比較慢乳蛾，幾乎難以接受暗赶，所以建議不大于1GB/innodb_buffer_pool_size(GB)*100這個值。當然肃叶，如果你能夠忍受啟動時間比較長蹂随，而且希望盡量減少內(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)化目標
1、減少?IO?次數(shù)
? ? ? ?IO永遠是數(shù)據(jù)庫最容易瓶頸的地方勿决，這是由數(shù)據(jù)庫的職責所決定的乒躺，大部分數(shù)據(jù)庫操作中超過90%的時間都是?IO?操作所占用的，減少?IO?次數(shù)是?SQL?優(yōu)化中需要第一優(yōu)先考慮低缩，當然嘉冒，也是收效最明顯的優(yōu)化手段。
2咆繁、降低?CPU?計算
? ? ? ?除了?IO?瓶頸之外讳推，SQL優(yōu)化中需要考慮的就是?CPU?運算量的優(yōu)化了。order by,group by,distinct …?都是消耗?CPU?的大戶（這些操作基本上都是?CPU?處理內(nèi)存中的數(shù)據(jù)比較運算）么介。當我們的?IO?優(yōu)化做到一定階段之后娜遵，降低?CPU計算也就成為了我們?SQL?優(yōu)化的重要目標。
4.2?優(yōu)化方法
一壤短、監(jiān)控分析
1设拟、硬件資源監(jiān)控
? ? ? ?關(guān)注的主要數(shù)據(jù)庫服務(wù)器在IO和CPU方面的指標。
2久脯、mysql性能分析器
? ? ? ?可以利用mysql profiling（mysql性能分析器）來優(yōu)化sql語句纳胧，即查看SQL執(zhí)行消耗系統(tǒng)資源的信息（需要開啟才能應用該功能）。
3帘撰、慢查詢分析
? ? ? ?通過慢日志查詢可以知道哪些SQL語句執(zhí)行效率低下跑慕，那些sql語句使用的頻率高等。
? ? ? ?對MySQL查詢語句的監(jiān)控、分析核行、優(yōu)化是MySQL優(yōu)化非常重要的一步牢硅。開啟慢查詢?nèi)罩竞螅捎谌罩居涗洸僮髦パ谝欢ǔ潭壬蠒加肅PU資源影響mysql的性能减余，但是可以階段性開啟來定位性能瓶頸。
二惩系、改變?SQL?執(zhí)行計劃
? ? ? ?明確了優(yōu)化目標之后位岔，我們需要確定達到我們目標的方法。對于?SQL?語句來說堡牡，達到上述2個優(yōu)化目標的方法其實只有一個抒抬，那就是改變?SQL?的執(zhí)行計劃，讓他盡量“少走彎路”晤柄，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)擦剑，以達到“減少IO次數(shù)”和“降低?CPU?計算”的目標。
? ? ? ?使用explain命令查看query語句的性能：
? ? ? ?EXPLAIN select * from tablename;##查看執(zhí)行計劃中的sql性能
? ? ? ?上面這是最簡單的執(zhí)行計劃實例可免，來分析一下上面的這幾個字段葵诈。
1搏嗡、id：id主要是用來標識sql執(zhí)行順序锦聊，如果沒有子查詢蓉坎，一般來說id只有1個飞袋，執(zhí)行順序也是從上到下治笨。
2妓笙、select_type：每個select子句的類型宰衙，主要分成下面幾種：?
a:SIMPLE:查詢中不包含任何子查詢或者union
b:PRIMARY:查詢中包含了任何復雜的子部分肉康，最外層的就會變成PRIMARY
c:SUBQUERY:在SELECT或者WHERE列表中包含了子查詢
d:DERIVED：在FROM中包含了子查詢
e:UNION:如果第二個SELECT出現(xiàn)在UNION之后闯估，則被標記為UNION，如果UNION包含在FROM子句的子查詢中吼和，外層SELECT會被標記為：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列預估了mysql能夠為當前查詢選擇的索引，這個字段是完全獨立于執(zhí)行計劃中輸出的表的順序薪韩，意味著在實際查詢中可能用不到這些索引确沸。?
? ? ? ?如果該字段為空則意味著沒有可使用的索引，這個時候你可以考慮為where后面的字段建立索引俘陷。
5罗捎、key?
? ? ? ?這個字段表示了mysql真實使用的索引（如果為NULL，則沒有使用索引）拉盾。如果mysql優(yōu)化過程中沒有加索引桨菜，可以強制加hint使用索引。
6捉偏、key_len?
? ? ? ?索引長度字段顧名思義倒得，表示了mysql查詢中使用的索引的長度（最大可能長度），并非實際使用長度夭禽，理論上長度越短越好霞掺。key_len是根據(jù)表定義計算而得的，不是通過表內(nèi)檢索出的讹躯。
7菩彬、ref?
? ? ? ?這個字段一般是指一些常量用于選擇過濾（顯示索引的那一列被使用了，如果可能潮梯，是一個常量const）骗灶。
8、rows?
? ? ? ?預估結(jié)果集的條數(shù)秉馏，可能不一定完全準確（根據(jù)表統(tǒng)計信息及索引選用情況耙旦，大致估算出找到所需的記錄所需要讀取的行數(shù)）。
9萝究、Extra
? ? ? ?不適合在其他字段中顯示免都，但是十分重要的額外信息：
a：Using filesort：mysql對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引進行排序讀取糊肤。也就是說mysql無法利用索引完成的排序操作成為“文件排序”琴昆。
b：Using temporary：使用臨時表保存中間結(jié)果，也就是說mysql在對查詢結(jié)果排序時使用了臨時表馆揉，常見于order by 和 group by业舍。
c：Using index：表示相應的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í)行階段在進行計算椒拗，查詢執(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(*)?堵未，他們認為這樣性能更好腋舌，其實這是一個誤區(qū)。對于有些場景兴溜，這樣做可能性能會更差侦厚，應為數(shù)據(jù)庫對?count(*)?計數(shù)操作做了一些特別的優(yōu)化耻陕。
2拙徽、count(column)和count(*)是一樣的
? ? ? ?這個誤區(qū)甚至在很多的資深工程師或者是?DBA?中都普遍存在，很多人都會認為這是理所當然的诗宣。實際上膘怕，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ù)量其實是一樣的。
? ? ? ?當然换薄，也有例外情況玉雾，那就是我們的這個查詢在索引中就可以完成，也就是說當只取?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?就會進行磁盤文件排序
? ? ? ?有這個誤區(qū)其實并不能怪我們延刘，而是因為?MySQL?開發(fā)者在用詞方面的問題。filesort是我們在使用?explain?命令查看一條SQL?的執(zhí)行計劃的時候可能會看到在“Extra”一列顯示的信息六敬。
? ? ? ?實際上碘赖，只要一條?SQL?語句需要進行排序操作，都會顯示“Using filesort”外构，這并不表示就會有文件排序操作普泡。
1、盡量少 join
? ? ? ?MySQL?的優(yōu)勢在于簡單审编，但這在某些方面其實也是其劣勢撼班。MySQL優(yōu)化器效率高，但是由于其統(tǒng)計信息的量有限垒酬，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多砰嘁。對于復雜的多表?Join眯亦，一方面由于其優(yōu)化器受限，再者在Join這方面所下的功夫還不夠般码，所以性能表現(xiàn)離Oracle等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離妻率。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩板祝。
2宫静、盡量少排序
? ? ? ?排序操作會消耗較多的?CPU?資源，所以減少排序可以在緩存命中率高等?IO?能力足夠的場景下會較大影響?SQL的響應時間券时。
? ? ? ?對于MySQL來說孤里，減少排序有多種辦法，比如：
o? ? 上面誤區(qū)中提到的通過利用索引來排序的方式進行優(yōu)化
o? ? 減少參與排序的記錄條數(shù)
o? ? 非必要不對數(shù)據(jù)進行排序
o? ? 避免使用耗費資源的操作橘洞，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語句會啟動SQL引擎 執(zhí)行捌袜，耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序
o? ? …
3、盡量避免 select *
? ? ? ?很多人看到這一點后覺得比較難理解炸枣，上面不是在誤區(qū)中剛剛說?select?子句中字段的多少并不會影響到讀取的數(shù)據(jù)嗎虏等？
? ? ? ?是的，大多數(shù)時候并不會影響到?IO?量适肠，但是當我們還存在order by?操作的時候霍衫，select?子句中的字段多少會在很大程度上影響到我們的排序效率。
? ? ? ?此外侯养，上面誤區(qū)中不是也說了敦跌，只是大多數(shù)時候是不會影響到IO量，當我們的查詢結(jié)果僅僅只需要在索引中就能找到的時候逛揩，還是會極大減少IO量的柠傍。
4、盡量用join代替子查詢
? ? ? ?雖然Join性能并不佳辩稽，但是和MySQL的子查詢比起來還是有非常大的性能優(yōu)勢惧笛。MySQL的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經(jīng)存在多年搂誉，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在徐紧，一直沒有太大改善。雖然官方也在很早就承認這一問題炭懊，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題拂檩。
5侮腹、盡量少or
? ? ? ?當?where?子句中存在多個條件以“或”并存的時候，MySQL?的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題稻励，再加上MySQL?特有的?SQL?與?Storage?分層架構(gòu)方式父阻，造成了其性能比較低下愈涩，很多時候使用?union all?或者是union（必要的時候）的方式來代替“or”會得到更好的效果。
6加矛、盡量用 union all 代替 union
? ? ? ?union?和?union all?的差異主要是前者需要將兩個（或者多個）結(jié)果集合并后再進行唯一性過濾操作履婉，這就會涉及到排序，增加大量的?CPU?運算斟览，加大資源消耗及延遲毁腿。所以當我們可以確認不可能出現(xiàn)重復結(jié)果集或者不在乎重復結(jié)果集的時候，盡量使用?union all?而不是?union苛茂。
7已烤、盡量早過濾
? ? ? ?這一優(yōu)化策略其實最常見于索引的優(yōu)化設(shè)計中（將過濾性更好的字段放得更靠前）。? ? ? ?在?SQL?編寫中同樣可以使用這一原則來優(yōu)化一些?Join?的?SQL妓羊。比如我們在多個表進行分頁數(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ù)進行轉(zhuǎn)換
? ? ? ?直接導致 MySQL（實際上其他數(shù)據(jù)庫也會有同樣的問題）無法使用索引费韭，如果非要轉(zhuǎn)換，應該在傳入的參數(shù)上進行轉(zhuǎn)換
? ? ? ?o??? 由數(shù)據(jù)庫自己進行轉(zhuǎn)換
? ? ? ?如果我們傳入的數(shù)據(jù)類型和字段類型不一致庭瑰，同時我們又沒有做任何類型轉(zhuǎn)換處理星持，MySQL 可能會自己對我們的數(shù)據(jù)進行類型轉(zhuǎ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?語句會導致表鎖定捡鱼，阻止其他用戶訪問該表
11八回、優(yōu)先優(yōu)化高并發(fā)的SQL，而不是執(zhí)行頻率低某些“大”SQL
? ? ? ?對于破壞性來說驾诈，高并發(fā)的?SQL?總是會比低頻率的來得大缠诅，因為高并發(fā)的SQL一旦出現(xiàn)問題，甚至不會給我們?nèi)魏未⒌臋C會就會將系統(tǒng)壓跨乍迄。而對于一些雖然需要消耗大量?IO?而且響應很慢的?SQL管引，由于頻率低，即使遇到闯两，最多就是讓整個系統(tǒng)響應慢一點褥伴，但至少可能撐一會兒谅将，讓我們有緩沖的機會。
12重慢、從全局出發(fā)優(yōu)化饥臂，而不是片面調(diào)整
? ? ? ?SQL?優(yōu)化不能是單獨針對某一個進行，而應充分考慮系統(tǒng)中所有的?SQL似踱，尤其是在通過調(diào)整索引優(yōu)化?SQL的執(zhí)行計劃的時候隅熙，千萬不能顧此失彼，因小失大屯援。
13猛们、盡可能對每一條運行在數(shù)據(jù)庫中的SQL進行explain
? ? ? ?優(yōu)化?SQL，需要做到心中有數(shù)狞洋，知道?SQL的執(zhí)行計劃才能判斷是否有優(yōu)化余地弯淘，才能判斷是否存在執(zhí)行計劃問題。在對數(shù)據(jù)庫中運行的?SQL?進行了一段時間的優(yōu)化之后吉懊，很明顯的問題?SQL?可能已經(jīng)很少了庐橙，大多都需要去發(fā)掘，這時候就需要進行大量的?explain?操作收集執(zhí)行計劃借嗽，并判斷是否需要進行優(yōu)化态鳖。
14、其他優(yōu)化方式
? ? ? ?（1）適當使用視圖加速查詢：把表的一個子集進行排序并創(chuàng)建視圖恶导，有時能加速查詢（特別是要被多次執(zhí)行的查詢）浆竭。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作惨寿。視圖中的行要比主表中的行少邦泄，而且物理順序就是所要求的順序，減少了磁盤I/O裂垦，所以查詢工作量可以得到大幅減少顺囊。
? ? ? ?（2）算法優(yōu)化：盡量避免使用游標，因為游標的效率較差蕉拢，如果游標操作的數(shù)據(jù)超過1萬行特碳，那么就應該考慮改寫。.使用基于游標的方法或臨時表方法之前晕换，應先尋找基于集的解決方案來解決問題午乓，基于集的方法通常更有效。與臨時表一樣届巩，游標并不是不可使用硅瞧。對小型數(shù)據(jù)集使用?FAST_FORWARD?游標通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時恕汇。在結(jié)果集中包括“合計”的例程通常要比使用游標執(zhí)行的速度快腕唧。如果開發(fā)時間允許，基于游標的方法和基于集的方法都可以嘗試一下瘾英，看哪一種方法的效果更好枣接。
? ? ? ?游標提供了對特定集合中逐行掃描的手段，一般使用游標逐行遍歷數(shù)據(jù)缺谴，根據(jù)取出的數(shù)據(jù)不同條件進行不同的操作但惶。尤其對多表和大表定義的游標（大的數(shù)據(jù)集合）循環(huán)很容易使程序進入一個漫長的等特甚至死機。
? ? ? ?在有些場合湿蛔，有時也非得使用游標膀曾，此時也可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時表中，再對臨時表定義游標進行操作阳啥，可時性能得到明顯提高添谊。
? ? ? ?（3）封裝存儲過程：經(jīng)編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中，運行效率高察迟，可以降低客戶機和服務(wù)器之間的通信量斩狱，有利于集中控制，易于維護扎瓶。
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?處理截歉，大部分數(shù)據(jù)庫操作?90%以上的時間都花在了?IO?讀寫上面。所以盡可能減少?IO讀寫量烟零，可以在很大程度上提高數(shù)據(jù)庫操作的性能瘪松。我們無法改變數(shù)據(jù)庫中需要存儲的數(shù)據(jù)咸作，但是我們可以在這些數(shù)據(jù)的存儲方式方面花一些心思。原則是：數(shù)據(jù)行的長度不要超過8020字節(jié)宵睦，如果超過這個長度的話在物理頁中這條數(shù)據(jù)會占用兩行從而造成存儲碎片记罚，降低查詢效率；字段的長度在最大限度的滿足可能的需要的前提下壳嚎，應該盡可能的設(shè)得短一些桐智，這樣可以提高查詢的效率，而且在建立索引的時候也可以減少資源的消耗烟馅。
? ? ? ?下面的這些關(guān)于字段類型的優(yōu)化建議主要適用于記錄條數(shù)較多说庭，數(shù)據(jù)量較大的場景，因為精細化的數(shù)據(jù)類型設(shè)置可能帶來維護成本的提高郑趁，過度優(yōu)化也可能會帶來其他的問題：
? ? ? ?1刊驴、數(shù)字類型：非萬不得已不要使用DOUBLE，不僅僅只是存儲長度的問題穿撮，同時還會存在精確性的問題缺脉。同樣，固定精度的小數(shù)悦穿，也不建議使用DECIMAL攻礼，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲，可以大大節(jié)省存儲空間栗柒，且不會帶來任何附加維護成本礁扮。對于整數(shù)的存儲，在數(shù)據(jù)量較大的情況下瞬沦，建議區(qū)分開?TINYINT / INT / BIGINT?的選擇太伊，因為三者所占用的存儲空間也有很大的差別，能確定不會使用負數(shù)的字段逛钻，建議添加unsigned定義僚焦。當然，如果數(shù)據(jù)量較小的數(shù)據(jù)庫曙痘，也可以不用嚴格區(qū)分三個整數(shù)類型芳悲。
? ? ? ?能夠用數(shù)字類型的字段盡量選擇數(shù)字類型而不用字符串類型的（電話號碼），這會降低查詢和連接的性能边坤，并會增加存儲開銷名扛。這是因為引擎在處理查詢和連接會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了茧痒。
? ? ? ?2肮韧、字符類型：非萬不得已不要使用?TEXT?數(shù)據(jù)類型，其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理。定長字段弄企，建議使用?CHAR?類型（char查詢快超燃，但是耗存儲空間，可用于用戶名桩蓉、密碼等長度變化不大的字段）淋纲，不定長字段盡量使用?VARCHAR（varchar查詢相對慢一些但是節(jié)省存儲空間劳闹，可用于評論等長度變化大的字段）院究，且僅僅設(shè)定適當?shù)淖畲箝L度，而不是非常隨意的給一個很大的最大長度限定本涕，因為不同的長度范圍业汰，MySQL也會有不一樣的存儲處理。
? ? ? ?3菩颖、時間類型：盡量使用TIMESTAMP類型样漆，因為其存儲空間只需要DATETIME?類型的一半。對于只需要精確到某一天的數(shù)據(jù)類型晦闰，建議使用DATE類型放祟，因為他的存儲空間只需要3個字節(jié)，比TIMESTAMP還少呻右。不建議通過INT類型類存儲一個unix timestamp?的值跪妥，因為這太不直觀，會給維護帶來不必要的麻煩声滥，同時還不會帶來任何好處眉撵。
? ? ? ?4、ENUM &SET：對于狀態(tài)字段落塑，可以嘗試使用?ENUM?來存放纽疟，因為可以極大的降低存儲空間，而且即使需要增加新的類型台谊，只要增加于末尾绞绒，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)髓介。如果是存放可預先定義的屬性數(shù)據(jù)呢？可以嘗試使用SET類型蟆肆，即使存在多種屬性，同樣可以游刃有余洲愤，同時還可以節(jié)省不小的存儲空間颓芭。
? ? ? ?5、LOB類型：強烈反對在數(shù)據(jù)庫中存放?LOB類型數(shù)據(jù)柬赐，雖然數(shù)據(jù)庫提供了這樣的功能亡问，但這不是他所擅長的，我們更應該讓合適的工具做他擅長的事情，才能將其發(fā)揮到極致州藕。在數(shù)據(jù)庫中存儲?LOB?數(shù)據(jù)就像讓一個多年前在學校學過一點Java的營銷專業(yè)人員來寫?Java?代碼一樣束世。
二、字符編碼
? ? ? ?字符集直接決定了數(shù)據(jù)在MySQL中的存儲編碼方式床玻，由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會有較大的差異毁涉，所以通過使用合適的字符集，可以幫助我們盡可能減少數(shù)據(jù)量锈死，進而減少IO操作次數(shù)贫堰。
? ? ? ?1、純拉丁字符能表示的內(nèi)容待牵，沒必要選擇?latin1?之外的其他字符編碼其屏，因為這會節(jié)省大量的存儲空間；
? ? ? ?2缨该、如果我們可以確定不需要存放多種語言偎行，就沒必要非得使用UTF8或者其他UNICODE字符類型，這回造成大量的存儲空間浪費贰拿；
? ? ? ?3蛤袒、MySQL的數(shù)據(jù)類型可以精確到字段，所以當我們需要大型數(shù)據(jù)庫中存放多字節(jié)數(shù)據(jù)的時候膨更，可以通過對不同表不同字段使用不同的數(shù)據(jù)類型來較大程度減小數(shù)據(jù)存儲量妙真，進而降低?IO?操作次數(shù)并提高緩存命中率询一。
三、適當拆分
? ? ? ?有些時候嫡锌，我們可能會希望將一個完整的對象對應于一張數(shù)據(jù)庫表啦桌，這對于應用程序開發(fā)來說是很有好的验烧，但是有些時候可能會在性能上帶來較大的問題。當我們的表中存在類似于?TEXT?或者是很大的?VARCHAR類型的大字段的時候，如果我們大部分訪問這張表的時候都不需要這個字段，我們就該義無反顧的將其拆分到另外的獨立表中驮审，以減少常用數(shù)據(jù)所占用的存儲空間熙掺。這樣做的一個明顯好處就是每個數(shù)據(jù)塊中可以存儲的數(shù)據(jù)條數(shù)可以大大增加芽突，既減少物理?IO?次數(shù)睬澡，也能大大提高內(nèi)存中的緩存命中率啄糙。
? ? ? ?上面幾點的優(yōu)化都是為了減少每條記錄的存儲空間大小静陈，讓每個數(shù)據(jù)庫中能夠存儲更多的記錄條數(shù)燕雁，以達到減少?IO?操作次數(shù)，提高緩存命中率鲸拥。下面這個優(yōu)化建議可能很多開發(fā)人員都會覺得不太理解拐格，因為這是典型的反范式設(shè)計，而且也和上面的幾點優(yōu)化建議的目標相違背刑赶。
四捏浊、適度冗余
? ? ? ?為什么我們要冗余？這不是增加了每條數(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有差異，會進入索引中趁桃，但如果是一個組合索引辽话，那么這個NULL?類型的字段會極大影響整個索引的效率肄鸽。此外，NULL在索引中的處理也是特殊的油啤，也會占用額外的存放空間典徘。
? ? ? ?很多人覺得?NULL?會節(jié)省一些空間，所以盡量讓NULL來達到節(jié)省IO的目的益咬，但是大部分時候這會適得其反逮诲，雖然空間上可能確實有一定節(jié)省，倒是帶來了很多其他的優(yōu)化問題幽告，不但沒有將IO量省下來梅鹦，反而加大了SQL的IO量。所以盡量確保DEFAULT?值不是?NULL冗锁，也是一個很好的表結(jié)構(gòu)設(shè)計優(yōu)化習慣齐唆。
6?索引優(yōu)化
? ? ? ?大家都知道索引對于數(shù)據(jù)訪問的性能有非常關(guān)鍵的作用，都知道索引可以提高數(shù)據(jù)訪問效率冻河。為什么索引能提高數(shù)據(jù)訪問性能箍邮？他會不會有“副作用”？是不是索引創(chuàng)建越多芋绸，性能就越好媒殉？到底該如何設(shè)計索引，才能最大限度的發(fā)揮其效能摔敛？這篇文章主要是帶著上面這幾個問題來做一個簡要的分析，同時排除了業(yè)務(wù)場景所帶來的特殊性全封，請不要糾結(jié)業(yè)務(wù)場景的影響马昙。
? ? ? ?索引為什么能提高數(shù)據(jù)訪問性能？
? ? ? ?很多人只知道索引能夠提高數(shù)據(jù)庫的性能刹悴，但并不是特別了解其原理行楞，其實我們可以用一個生活中的示例來理解。我們讓一位不太懂計算機的朋友去圖書館確認一本叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》的書是否在藏土匀，這樣對他說：“請幫我借一本計算機類的數(shù)據(jù)庫書籍子房，是屬于?MySQL?數(shù)據(jù)庫范疇的，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》”就轧。朋友會根據(jù)所屬類別证杭，前往存放“計算機”書籍區(qū)域的書架，然后再尋找“數(shù)據(jù)庫”類存放位置妒御，再找到一堆講述“MySQL”的書籍解愤，最后可能發(fā)現(xiàn)目標在藏（也可能已經(jīng)借出不在書架上）。在這個過程中：“計算機”->“數(shù)據(jù)庫”->“MySQL”->“在藏”->《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》其實就是一個“根據(jù)索引查找數(shù)據(jù)”的典型案例乎莉，“計算機”->“數(shù)據(jù)庫”->“MySQL”->“在藏”就是朋友查找書籍的索引送讲。假設(shè)沒有這個索引奸笤，那查找這本書的過程會變成怎樣呢？朋友只能從圖書館入口一個書架一個書架的“遍歷”哼鬓，直到找到《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》這本書為止监右。如果幸運，可能在第一個書架就找到异希。但如果不幸呢健盒，那就慘了，可能要將整個圖書館所有的書架都找一遍才能找到我們想要的這本書宠互。注：這個例子中的“索引”是記錄在我們大腦中的味榛，實際上，每個圖書館都會有一個非常全的實際存在的索引系統(tǒng)（大多位于入口顯眼處）予跌，由很多個貼上了明顯標簽的小抽屜構(gòu)成搏色。這個索引系統(tǒng)中存放這非常齊全詳盡的索引數(shù)據(jù)，標識出我們需要查找的“目標”在某個區(qū)域的某個書架上券册。而且每當有新的書籍入庫频轿，舊的書籍銷毀以及書記信息修改，都需要對索引系統(tǒng)進行及時的修正烁焙。
? ? ? ?下面我們通過上面這個生活中的小示例航邢，來分析一下索引，看看能的出哪些結(jié)論骄蝇？
一膳殷、索引有哪些“副作用”？
? ? ? ?1九火、圖書的變更（增赚窃，刪，改）都需要修訂索引岔激，索引存在額外的維護成本勒极；
? ? ? ?2、查找翻閱索引系統(tǒng)需要消耗時間虑鼎，索引存在額外的訪問成本辱匿；
? ? ? ?3、這個索引系統(tǒng)需要一個地方來存放炫彩，索引存在額外的空間成本匾七。
二、索引是不是越多越好媒楼？
? ? ? ?1乐尊、如果我們的這個圖書館只是一個進出中轉(zhuǎn)站，里面的新書進來后很快就會轉(zhuǎn)發(fā)去其他圖書館而從這個館藏中“清除”划址，那我們的索引就只會不斷的修改扔嵌，而很少會被用來查找圖書限府。
? ? ? ?所以，對于類似于這樣的存在大量和頻繁更新的數(shù)據(jù)痢缎，索引的維護成本會非常高胁勺，如果其檢索需求很少，而且對檢索效率并沒有非常高的要求的時候独旷，我們并不建議創(chuàng)建索引署穗，或者是盡量減少索引。
? ? ? ?2嵌洼、如果我們的書籍量少到只有幾本或者就只有一個書架案疲，索引并不會帶來什么作用，甚至可能還會浪費一些查找索引所花費的時間麻养。
? ? ? ?所以褐啡，對于數(shù)據(jù)量極小到通過索引檢索還不如直接遍歷來得快的數(shù)據(jù)，也并不適合使用索引鳖昌。
? ? ? ?3备畦、如果我們的圖書館只有一個10平方的面積，現(xiàn)在連放書架都已經(jīng)非常擁擠许昨，而且館藏還在不斷增加懂盐，我們還能考慮創(chuàng)建索引嗎？
? ? ? ?所以糕档，當我們連存儲基礎(chǔ)數(shù)據(jù)的空間都捉襟見肘的時候莉恼，我們也應該盡量減少低效或者是去除索引。
三速那、索引該如何設(shè)計才高效类垫？
? ? ? ?1、如果我們僅僅只是這樣告訴對方的：“幫我確認一本數(shù)據(jù)庫類別的講述?MySQL的叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》的書是否在藏”琅坡，結(jié)果又會如何呢？朋友只能一個大類區(qū)域一個大類區(qū)域的去尋找“數(shù)據(jù)庫”類別残家，然后再找到“MySQL”范疇榆俺，再看到我們所需是否在藏。由于我們少說了一個“計算機類”坞淮，朋友就必須到每一個大類去尋找茴晋。
? ? ? ?所以，我們應該盡量讓查找條件盡可能多的在索引中回窘，盡可能通過索引完成所有過濾诺擅，回表只是取出額外的數(shù)據(jù)字段。
? ? ? ?2啡直、如果我們是這樣說的：“幫我確認一本講述?MySQL?的數(shù)據(jù)庫范疇的計算機叢書烁涌，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》苍碟，看是否在藏”。如果這位朋友并不知道計算機是一個大類撮执，也不知道數(shù)據(jù)庫屬于計算機大類微峰，那這位朋友就悲劇了。首先他得遍歷每個類別確認“MySQL”存在于哪些類別中抒钱，然后從包含“MySQL”書籍中再看有哪些是“數(shù)據(jù)庫”范疇的（有可能部分是講述PHP或者其他開發(fā)語言的）蜓肆，然后再排除非計算機類的（雖然可能并沒有必要），然后才能確認谋币。
? ? ? ?所以仗扬，字段的順序?qū)M合索引效率有至關(guān)重要的作用，過濾效果越好的字段需要更靠前蕾额。
? ? ? ?3早芭、如果我們還有這樣一個需求（雖然基本不可能）：“幫我將圖書館中所有的計算機圖書借來”。朋友如果通過索引來找凡简，每次都到索引柜找到計算機書籍所在的區(qū)域逼友，然后從書架上搬下一格（假設(shè)只能以一格為單位從書架上取下，類比數(shù)據(jù)庫中以block/page為單位讀瘸由）帜乞，取出第一本，然后再從索引柜找到計算機圖書所在區(qū)域筐眷，再搬下一格黎烈，取出一本…?如此往復直至取完所有的書。如果他不通過索引來找又會怎樣呢匀谣？他需要從第一個書架一直往后找照棋，當找到計算機的書，搬下一格武翎，取出所有計算機的書烈炭，再往后，直至所有書架全部看一遍宝恶。在這個過程中符隙，如果計算機類書籍較多，通過索引來取所花費的時間很可能要大于直接遍歷垫毙，因為不斷往復的索引翻閱所消耗的時間會非常長霹疫。（延伸閱讀：可以參照Oracle的索引優(yōu)化進行解讀，索引掃描還是全表掃描（Index Scan Or Full Table Scan））
? ? ? ?所以综芥，當我們需要讀取的數(shù)據(jù)量占整個數(shù)據(jù)量的比例較大抑或者說索引的過濾效果并不是太好的時候丽蝎，使用索引并不一定優(yōu)于全表掃描。
? ? ? ?4膀藐、如果我們的朋友不知道“數(shù)據(jù)庫”這個類別可以屬于“計算機”這個大類屠阻，抑或者圖書館的索引系統(tǒng)中這兩個類別屬性并沒有關(guān)聯(lián)關(guān)系红省，又會怎樣呢？也就是說栏笆，朋友得到的是2個獨立的索引类腮，一個是告知“計算機”這個大類所在的區(qū)域，一個是“數(shù)據(jù)庫”這個小類所在的區(qū)域（很可能是多個區(qū)域）蛉加，那么他只能二者選其一來搜索我的需求蚜枢。即使朋友可以分別通過2個索引檢索然后自己在腦中取交集再找，那這樣的效率實際過程中也會比較低下针饥。
? ? ? ?所以厂抽，在實際使用過程中，一次數(shù)據(jù)訪問一般只能利用到1個索引丁眼，這一點在索引創(chuàng)建過程中一定要注意筷凤，不是說一條SQL語句中Where子句里面每個條件都有索引能對應上就可以了。
? ? ? ?5苞七、最后總結(jié)一下法則：不要在建立的索引的數(shù)據(jù)列上進行下列操作：
◆避免對索引字段進行計算操作
◆避免在索引字段上使用not藐守，like ‘%L’，!=蹂风，<>卢厂，in，or連接
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換
◆避免在索引字段上使用函數(shù)
◆避免建立索引的列中使用空值惠啄。
7?架構(gòu)優(yōu)化
一慎恒、分布式和集群化
1、負載均衡
? ? ? ?負載均衡集群是由一組相互獨立的計算機系統(tǒng)構(gòu)成撵渡，通過常規(guī)網(wǎng)絡(luò)或?qū)Ｓ镁W(wǎng)絡(luò)進行連接融柬，由路由器銜接在一起，各節(jié)點相互協(xié)作趋距、共同負載粒氧、均衡壓力，對客戶端來說节腐，整個群集可以視為一臺具有超高性能的獨立服務(wù)器靠欢。MySQL一般部署的是高可用性負載均衡集群，具備讀寫分離铜跑，一般只對讀進行負載均衡。
2骡澈、讀寫分離
? ? ? ?讀寫分離簡單的說是把對數(shù)據(jù)庫讀和寫的操作分開對應不同的數(shù)據(jù)庫服務(wù)器锅纺，這樣能有效地減輕數(shù)據(jù)庫壓力，也能減輕io壓力肋殴。主數(shù)據(jù)庫提供寫操作囤锉，從數(shù)據(jù)庫提供讀操作坦弟，其實在很多系統(tǒng)中，主要是讀的操作官地。當主數(shù)據(jù)庫進行寫操作時酿傍，數(shù)據(jù)要同步到從的數(shù)據(jù)庫，這樣才能有效保證數(shù)據(jù)庫完整性驱入。
3赤炒、數(shù)據(jù)切分
? ? ? ?通過某種特定的條件，將存放在同一個數(shù)據(jù)庫中的數(shù)據(jù)分散存放到多個數(shù)據(jù)庫上亏较，實現(xiàn)分布存儲莺褒，通過路由規(guī)則路由訪問特定的數(shù)據(jù)庫，這樣一來每次訪問面對的就不是單臺服務(wù)器了雪情，而是N臺服務(wù)器遵岩，這樣就可以降低單臺機器的負載壓力。數(shù)據(jù)切分一般包括垂直切分和水平切分巡通。
? ? ? ?數(shù)據(jù)的垂直切分尘执，也可以稱之為縱向切分。將數(shù)據(jù)庫想象成為由很多個一大塊一大塊的“數(shù)據(jù)塊”（表）組成宴凉，我們垂直的將這些“數(shù)據(jù)塊”切開誊锭，然后將他們分散到多臺數(shù)據(jù)庫主機上面。這樣的切分方法就是一個垂直（縱向）的數(shù)據(jù)切分跪解。
? ? ? ?數(shù)據(jù)的垂直切分基本上可以簡單的理解為按照表炉旷、按照模塊來切分數(shù)據(jù)，而水平切分就不再是按照表或者是功能模塊來切分了叉讥。一般來說窘行，簡單的水平切分主要是將某個訪問極其平凡的表再按照某個字段的某種規(guī)則來分散到多個表之中，每個表中包含一部分數(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)計查詢等功能冠息。
