MySQL優化-所需了解的基礎知識

時隔一年半，期間一直想寫但卻覺得沒有實質性的內容可記錄，本文為 [高性能MySQL] 的學習日誌整理分享(感興趣建議讀原書)。

優化應貫穿整個產品開發周期中，開發過程中考慮一些性能問題與影響，總比出問題才開始重構優化代價要低，所以這些優化知識其實應算需具備的常識。

1、MySQL構架的一些知識

1.1縱觀全貌，邏輯結構如下:

關於優化，是一個比較複雜的過程，可能涉及操作系統配置、網路/磁碟IO、內存、文件系統與應用程序等，知識深入並且面比較廣，本文只針對MySQL伺服器本身的一些可優化點做記錄。

1.2選擇合適的Storage engine很關鍵，MySQL鎖定模型和並發性如下：

部分存儲引擎說明：

MyISAM

1. MyISAM 是表級別鎖。

2. 並且不支持數據恢復(服務突然崩潰應該使用之前進行check)。

3. 不支持事務。

4. 只有索引被緩存在內存中。

5. 數據緊密存儲, 數據讀取比較快速。

InnoDB

1. 合適大量短事務，使用MVCC機制控制並發鎖定問題，行鎖。

2. 默認隔離級別為Repeatable Read, 並且使用Next-key locking策略防止幻讀。

3. InnoDB表基於聚簇索引建立，非主鍵索引也包含主鍵列，所以主鍵定義盡量小一些。

Memory

1. 數據存儲於內存，重啟表結構保留，但數據丟失。2. 支持哈希索引，但是表級加鎖，只適合低寫入，並且不支持TEXT和BLOB。

註：Temporary table 和 Memory table是兩個不同的東西，臨時表只在單個連接中可見，連接斷開則消失。

Archive

1. 只支持insert和select，並且不支持索引。插入時用zlib演算法壓縮，佔用空間小。2. 任何查詢都會導致全表掃描。

NDB Cluster:

1. 包含Data Node, Manager Node, SQL Node, 數據冗餘於多個數據節點。

2. 要求高性能的網路環境，解決複製的延時問題。

3. 該引擎不作為一個通用的存儲引擎設計，使用前需要深入了解它是否符合應用場景，它很龐雜。

關於存儲引擎的選擇參考因素：

1. 事務， InnoDB處理事務型場景很好。

2. 並發，如果少量插入並且大量讀取，數據可接受崩潰丟失的情況下，MyISAM效率很好。

3. 備份，是否需要聯機備份。

4. 崩潰恢復，MyISAM在大量數據情況下崩潰恢復時間很長。

5. 特有特性，如有時候使用到Memory引擎。

對於一般性的應用，InnoDB通常是很常規的引擎選擇。

2、關於字符集的關係(雖與優化無關, 但值得一提)

2.1 創建內置對象

對於MySQL服務創建的對象，在不指定字符集情況下：table繼承database, database繼承server default character。

所以，通常建議在my.cnf中指定資料庫默認的字符集, 如下:

[mysqld]
collation-server=utf8_general_ci
character-set-server=utf8

同時建議建立資料庫的時候顯式定義資料庫字符集，便於遷移或升級(如果遷移的目標伺服器沒定義的話, dump後import就可能字符集就不對應上，導致亂碼等)。

2.2 client / server 通信使用的字符集

通信使用的字符集設置通常包括character_set_client、character_set_connection、character_set_result，它們的關係如下圖：

在mysql客戶端的命令下使用show variables like "%character%"; 顯示當前連接使用的字符集參數。

註：對於mysql客戶端命令，通過配置下面參數以修改上面所說的參數(通過mysql --help --verbose查看默認載入配置文件的路徑)：

[mysql]
default-character-set=utf8

對於JDBC，可以url中加入如useUnicode=yes&characterEncoding=UTF-8來指定特定的字符集，通常建議都使用統一的一種字符集以防止出現未知麻煩。

3、優化數據類型

優化數據類型通常包含以下幾點說明：

1. 更小的數據類型通常更快，對於使用ORM自動生成的表結構通常不合適(如有些項目使用hibernate自動創建表，一般來說不建議，剛開始省時間，代價後面還是需要還回來)。

2. 盡量避免使用NULL, mysql難以優化可使用空列的查詢,可以考慮使用0/特殊值/空串等替代，如果計劃對該列進行索引，就盡量避免設置為空。。

3. 整數有tinyint,smallint,mediumint,int,bigint，它們分別需要8,16,24,32,64位存儲空間。

4. 主鍵：通常整數作為主鍵有非常快的索引速度，並且可以自增長，盡量避免使用字元串類型作為標識符，特別注意完全隨機的字元串(MD5，SHA1或者UUID),它們產生的新值會被任意地保存很多的空間範圍內，減慢INSERT（插入的值被隨機放入索引中，導致分頁，隨機磁碟和聚集存儲引擎上的聚集索引碎片）和一些SELECT(因為它分布在磁碟和內存中的各個地方，導致多次的讀取)查詢。

Note: 如果對一些毫不關心性能或者數據量極小的應用，自動生成代碼等情況為了趕項目就用吧，UUID比MD5，SHA1相比不均勻分布並且有一定的順序性。

5. 字元串類型(VRAHCR與CHAR), VARCHAR使用可變長字元串, ROW_FORMAT默認為dynamic，如果使用FIXED則消耗固定長度空間。VARCHAR額外的使用1-2位元組存儲長度(小於255則使用1位元組)。MySQL會使用固定的內存來存儲varchar的值，如使用varchar(20)和varchar(50)來保存同樣長度的數據，即使它們在磁碟上的消耗是一樣的，但是內存消耗卻相差很大。

4、關於索引的一些知識

對於MySQL索引，默認是B-TREE(目前只有這個)，所以了解一些B-TREE的知識比較有幫助(需要查找專門講解該結構的書籍)，有一些可以參考的點：

1. 如果要對長字元串做索引，索引會變得很大並且很慢，則可模擬哈希索引，取前一部分衝突概率較小的字元串。

2. 多餘索引： 如果對(A, B)列建立組合索引，則不必要再單獨對A列建立索引(對於B-Tree而言)，由於最左前綴匹配使得A索引生效，但對B單獨建索引則不是多餘(因為不是最左前綴)。

3. InnoDB在事務提交後才會給行解鎖，所以盡量減少對行的鎖定。鎖定超過需要的行會增加競爭並減少並發。InnoDB只有在存儲引擎級過濾不需要的數據才能不鎖定不需要的行，如果是返回給MySQL服務後再使用where過濾的話，已經無法避免對不需要的行進行鎖定。如下圖則對不需要的第一行進行鎖定 (對於MySQL 5.1 或更新的版本, InnoDB可以在伺服器過濾後進行對不需要的行釋放，舊版本會一直鎖定到事務提交, 建議使用最新的穩定MySQL版本):

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！