我在專案中對 MySQL 做的優化

zhoupq發表於2017-08-10
MySql優化

原標題：我在 MySQL 上做了哪些優化
原文連結：zhoupq.com/我在-MySQL-上做…
轉載請註明出處

　　本文記錄了我這一年的時間裡是如何對專案中用到的 MySQL 進行優化。帶有一定的主觀性和侷限性，請各位支援的同時，不吝賜教。

　　

安裝


　　這是同事分享給我的。安裝資料庫也是一門學問，千萬不要被安裝的簡單性而忽略一些細節。針對於 Win os 伺服器而言，MySQL 的安裝版可以選則三種不同的伺服器型別：

  • Developer Machine(開發機器)
    　　為 MySQL 分配最少分系統資源
  • Server Machine（伺服器）
    　　為 MySQL 分配一定比例的系統資源
  • Dedicated MySQL Server Machine（專用MySQL伺服器）
    　　為 MySQL 分配所有的系統資源

　　如果你跟我一樣不幸，不僅選擇了 Win os 做伺服器系統，還選擇了 Developer Machine(開發機器)，兄弟抱一個，不要哭，重灌。發生這些上述不幸的原因已經不重要，需要做的是必須切換成 Dedicated MySQL Server Machine（專用MySQL伺服器）。

　　重灌切換之後，你會發現，之前安裝的一定是假的 MySQL。

主鍵


　　主鍵或者唯一鍵可以用作某條記錄的唯一標誌符。主鍵生成有兩種方式：
+ 自增
+ UUID

自增


　　自增方式有個重要因素是“步長”，也就是則增的幅度，在單機模式下，一般步長為1。若是在分散式資料庫系統下，步長設為節點的數量，這樣一來，就可以避免主鍵重複的情況。建議預估好節點的數量，步長不可小於節點數。

UUID


　　UUID 可以更有效地避免自增主鍵帶來的煩惱，但是它也有不足之處：
+ UUID 過長，增大資料庫總容量，降低效能
+ UUID 無序，插入資料時根據主鍵定址費時

　　針對上述 UUID 的缺點，推特開發了“雪花演算法”，並開源。其中心思想是利用時間戳、資料中心碼、機器碼、序列號組成有規則的 UUID，使其有序降低效能消耗。

　　我在專案中使用了“自增+步長2”，因為使用了主從，雖然不是分散式，但是雙資料來源也是兩個節點，採用這種方式保險一些。

　　更多內容請移步我的其他博文：資料庫自增長主鍵與-UUID
　　推薦 MySQL 使用自增ID主鍵和UUID 作為主鍵的優劣比較詳細過程（從百萬到千萬表記錄測試）

資料型別

長度


　　我一直秉承殺雞就用殺雞刀，宰牛採用宰牛刀的原則。一個“tab_NAME” 的資料型別非要整一個 “VARCHAR(500)”，這是浪費，過多的長度分配會造成空間佔用太多，最終造成效能下降。有同事說我杞人憂天，一個庫才二十幾張表，即使每個欄位都設成500，也不過如此嘛。從短期的結果上來看，結果沒有受到明顯影響。但是別忘了，我們是來解決問題的，如果因為我們的操作違反了約定，造成嚴重後果，那麼我們將揹負罪過。“量身定做”的好處不言而喻，列的長度亦是如此。

NULL


　　儘量不將列設為 NULL，從業務角度上看，NULL 是錯誤的，試想，既然是 NULL，哪有何必存在這個列呢？反之，既然存在這個列，那麼 NULL 便失去了意義。讓我設計表的時候，我都會給列設定一個初始值，“tab_UPDATETIME” 就設定為 “CURRENT_TIMESTAMP”，“tab_STATUS” 就設定為 “1”或者“0”。

　　從開發維護的角度看，如果不確定列是否為 NULL，那麼在 SQL 中，就必須加上 “AND tab_NAME != NULL AND tab_NAME != ''”，很容易被忽略，程式碼越多，出錯的概率就越大。

索引


　　好的索引是一顆仙丹，可以讓遲緩的查詢得到質的提升，否則，就是一碗毒藥。

　　索引我做了三點優化：

  • 勿濫用索引
  • 最左字首索引
  • 字首索引

勿濫用索引


　　索引不是越多越好，因為生成索引需要時間，而且索引佔表物理空間。表一大，查詢速度多少會受影響。我親眼看到同事建好表時候，無微不至地為每一個欄位都建了索引，或者為每一個條件都建了索引，形如條件“a=1 and b =2 and c=3 and d=4”，為其建了“a”、“b”、“c”、“d”四個索引。浪費！低效！這種情況，應當使用“最左字首索引”。

最左字首索引


　　在生成聯合索引時會碰到最左字首索引，什麼是最左字首索引呢？就是在聯合索引中，從最左邊的索引開始匹配，直到遇上“like”、“>”、“>=”、“<”、“<=”等範圍匹配時停止，即使後面有“=”都不再匹配。

　　簡單舉例：現有欄位“a”、“b”、“c”、“d”組成的聯合索引“abcd”，SQL 條件部分為：

  1. a=1 and b =2 and c=3 and d=4
  2. a=1 and b =2 and d=4 and c=3
  3. a=1 and b =2 and c>=3 and d=4

　　1 用到索引為“abcd”，2 用到的索引為“abd”， （2 同 1）3 用到的索引為“abc”。條件的順序很重要。跟自拍一樣，臉大的站後面。
　　
　　抱歉，上述第二點同第一點，同樣用到的索引為“abcd”。
　　
　　利用 EXPLAIN 工具分析：

// 建表語句略，已知建立了組合索引 (abcd) 
mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.c = 'e' AND t.d = 'f';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set

mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.d = 'f' AND t.c = 'e';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set複製程式碼

字首索引


　　字首索引是針對某個欄位而言，我們知道 MySQL 中，有一個全文索引，“BOLD”、“TEXT” 是不能建全文索引。想想也能理解，這麼長的全文索引得佔多少空間，這顯然是不現實的。最好的辦法是為其建立“左字首索引”，只取字串的前面一小段作為索引，具體取多少，決定於多長的字元可以儘可能多的確定唯一記錄。“varchar”同樣受用。

　　更多內容請移步我的其他博文：MySQL 高效能索引之字首索引

多表聯合查詢


　　不管是 Heibernate 在程式碼中拼 SQL，還是 MyBatis 在 Mapper.xml 中寫 SQL，由於資料庫正規化的規範，導致為完成某項查詢，必須聯合多表查詢。一個 LEFT JOIN 很常見，三四個 LEFT JOIN 呢？

　　多個 LEFT JOIN 肯定不行，即使有索引，也很容易造成全表掃描，為了減少該情況發生的概率，我一般會採取兩種方法：

  • 反正規化
  • 臨時表

反正規化

衡量一個 DBA 的水平有多高，得看他反正規化能力有多強。
—— 知乎

　　比如我要根據 A表 的日期，關聯 B表，統計出每個日期下某個屬性的數量。我可以在A表中新增一列，用來儲存“數量”，雖然違反了正規化，但是效能上得到了提升。我覺得這是一筆劃算的買賣。

　　規範化是為了技術服務，而技術是為業務服務。規範化也就是套路，能保證不出錯，但是並不能解決特殊問題，特殊問題還需要特殊處理。

臨時表


　　當需要聯合三張表以上時，輕微的反正規化已經不適用了，推薦用臨時表，或者物化檢視，但是 MySQL 的物化檢視實現起來比較困難。事實上，我用的就是臨時表，將四張表的部分資料抽離出來，儲存在一張臨時表中，制定一個“計劃”，每天凌晨會自動更新。

  • 好處
    　　加快查詢速度
  • 缺點
    • 會在某一時刻（凌晨）資料庫IO過高
    • 可能會出現異常，做好事務管理，讓其回滾，重新執行，再有問題，就需要人工干預
    • 資料準確性會延遲一天，適合非敏感業務

　　以上是僅針對資料庫做的優化，至於快取（一級快取、二級快取），那屬於持久層框架的職責，不在此文記錄範圍之內。

相關文章