目錄
5 性能調優
5.1 表設計
5.2 字段類型的選取
5.2.1 針對數值類型
5.2.2 字元類型
5.2.3 時間類型
5.2.4 小技巧：快速修改表結構
5.2.5 pt-online-schema-change線上更改表結構
5.2.6 MySQL5.6線上DDL更改表測試
5.3 采用合适的鎖機制
5.3.1 表鎖
5.3.2 行鎖
5.3.3 InnoDB引擎與MyISAM引擎的性能對比
5.4 選擇合适的事務隔離級别
5 性能調優
5.1 表設計
5.2 字段類型的選取
- 選擇的原則：保小不保大，能用占用位元組少的字段就不用大字段。
- 依據：更小的字段類型占用的記憶體就更少，占用的磁盤空間和磁盤I/O也會更少，而且還會占用更少的帶寬。
5.2.1 針對數值類型
- 數值類型
最小值和最大值代表的是寬度
2. 錄入手機号可用BIGINT
一般程式的字元集是gbk或utf8, gbk占用2位元組，utf8占用3位元組，那麼11×3就是33位元組，而bigint(20)寬度為20，隻占用8位元組，從性能上考慮，應該設定為bigin
3. 年齡、用數字表示的狀态等均可采用TINYINT
采用tinyint完全可以滿足需要，int占用的是4位元組，而tinyint才占用1個位元組。
5.2.2 字元類型
- char(N)用于儲存固定長度的字元串，長度最大為255，比指定長度大的值将被截短，而比指定長度小的值将會用空格進行填補。
- varchar(N)用于儲存可變長度的字元串，長度最大為65535，隻存儲字元串實際需要的長度（它會增加一個額外位元組來存儲字元串本身的長度）, varchar使用額外的1～2位元組來存儲值的長度，如果列的最大長度小于或等于255，則使用1位元組，否則就是用2位元組。
- char和varchar跟字元編碼也有密切聯系，latin1占用1個位元組，gbk占用2個位元組，utf8占用3個位元組。
5.2.3 時間類型
- 日常建表時應優先選擇timestamp類型
- 關閉自動更新：更改預設值為NULL
- 在MySQL5.6中，year(2)類型會自動轉換為year(4)，如12-- >2012
5.2.4 小技巧：快速修改表結構
- 修改varchar類型
建立新表--->鎖表--- >複制表；Slave 也要執行一遍。
5.2.5 pt-online-schema-change線上更改表結構
存在風險
5.2.6 MySQL5.6線上DDL更改表測試
MySQL5.6之後執行Alter table的規則：
- 執行alter table表時，對該表的增、删、改、查均不會鎖表。
- 如果在這之前，該表有被通路，那需要等其執行完畢後，才可以執行alter table，否則會存在鎖表現象。
- 在淩晨上線時，一定要觀察一下，此時此刻是否有某個慢SQL在對該表進行操作，以免改表時出現鎖等待現象。
5.3 采用合适的鎖機制
MySQL的鎖形式:
- 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。MyISAM引擎屬于這種類型。
- 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。InnoDB引擎屬于這種類型。
- 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。NDB屬于這種類型。
5.3.1 表鎖
MyISAM存儲引擎隻支援表鎖：
- 對MyISAM表的讀操作（加讀鎖），不會阻塞其他程序對同一表的讀請求，但會阻塞對同一表的寫請求。隻有當讀鎖釋放後，才會執行其他程序的寫操作。
- 對MyISAM表的寫操作（加寫鎖），會阻塞其他程序對同一表的讀和寫操作，隻有當寫鎖釋放後，才會執行其他程序的讀寫操作。
5.3.2 行鎖
行鎖：
- InnoDB存儲引擎是通過給索引上的索引項加鎖來實作的，這就意味着：隻有通過索引條件檢索資料，InnoDB才會使用行級鎖，否則，InnoDB将使用表鎖。
- 在并發通路比較高的情況下，如果大量事務因無法立即獲得所需的鎖而挂起，會占用大量計算機資源，造成嚴重的性能問題，甚至拖垮資料庫，這時需要通過設定合适的鎖等待逾時閥值參數innodb_lock_wait_timeout來解決，一般設定為100秒即可。
行鎖轉表鎖：
- 隻有通過索引條件檢索資料，InnoDB才會使用行級鎖，否則，InnoDB将使用表鎖。
死鎖：
- 兩個事務都需要獲得對方持有的排他鎖才能繼續完成事務，這種循環鎖等待就是典型的死鎖。
- 發生死鎖後，InnoDB一般都能自動檢測到，它會讓一個事務釋放鎖并回退，另一個事務則獲得鎖，繼續完成事務。死鎖是無法避免的，我們可以通過調整業務的邏輯來盡量減少死鎖出現的機率。
5.3.3 InnoDB引擎與MyISAM引擎的性能對比
- 建議盡量不要混合使用多種存儲引擎，這樣容易帶來更複雜的問題。
- 從測試的結果來看，InnoDB每秒處理的資料為1549個，而MyISAM僅僅為154個。
- MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。試想一下，一個程序請求某個MyISAM表的讀鎖，同時另一個程序也請求同一表的寫鎖，MySQL該如何處理呢？答案是寫程序先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫請求一般要比讀請求重要。這也正是MyISAM表不太适合有大量更新操作和查詢操作應用的原因，因為大量的更新操作會造成查詢操作很難獲得讀鎖，進而可能永遠阻塞。
- InnoDB用于事務處理應用程式，具有衆多特性，包括支援ACID事務、行鎖等。如果應用中需要執行大量的讀寫操作，則應該使用InnoDB，這樣可以提高多使用者并發操作的性能。對于MyISAM引擎，在MySQL5.5版本裡Oracle公司支援的已經很少了，以後記憶體資料庫是一種趨勢，是以建議優先選擇InnoDB引擎。
5.4 選擇合适的事務隔離級别
- 隔離級别越高，越能保證資料的完整性和一緻性，但是對并發性能的影響也越大。
- ORACLE/SQL SERVER的預設隔離級别是Read Committed（讀送出），MySQL的預設隔離級别是Repeatable Read（可重複讀）
TODO
參考：
- 《MySQL管理之道：性能調優、高可用和監控》第二版