MySQL使用KEY分區時有哪些常見的誤區

這篇文章運用簡單易懂的例子給大家介紹MySQL使用KEY分區時有哪些常見的誤區，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

需求背景

業務表tb_image部分數據如下所示，其中id唯一，image_no不唯一。image_no表示每個文件的編號，每個文件在業務系統中會生成若干個文件，每個文件的唯一ID就是字段id：

業務表tb_image的一些情況如下：

• 根據image_no查詢和根據id查詢；
• 存量數據2kw；
• 日增長4w左右；
• 日查詢量20w左右；
• 非ToC系統，所以并發的天花板可見；
   

方案選擇

根據上面對業務的分析，分庫分表完全沒有必要。單庫分表的話，由于要根據image_no和id查詢，所以，一種方案是冗余分表（即一份數據以image_no為分片鍵保存，另一份數據以id為分片鍵保存）；另一種方案是只以image_no為分片鍵，而基于id的查詢需求，業務層進行結果歸并或者引入第三方中間件。

考慮到單庫分表比較復雜，所以決定使用分區特性，而且容量評估分區表方案128個分區（每個分區數據量kw級別）完全能保證業務至少穩定運行15年（圖中橙色部分是比較貼合自身業務實際增長情況）：

另外，由于RANGE, LIST, HASH分區都不支持VARCHAR列，所以決定采用KEY分區，官方介紹它的原理是以MySQL內置hash算法然后對分區數取模。

性能測試

選定分片鍵為image_no，并且決定分區數為128后，就要灌入數據進行可行性和性能測試了。分區數選擇128的原因是：11億/1kw=110≈128，另外程序員情節，喜歡用2的N次方，你懂的。然而， 這個分區數128就是一切噩夢的開始 。

我嘗試先插入10w數據到128個分區中，插入后，讓我驚訝的現象出現了： 所有奇數編號分區（p1, p3, p5, … , p2n-1）中居然沒有一條數據 ，同時，任何一個偶數編號分區卻有很多的數據，而且還不是很均勻。如下圖所示：

說明：奇數編號分區的ibd文件大小都是112k，這是創建分區表時初始化大小，實際并沒有任何數據。我們可以通過SQL： select partition_name, partition_expression, table_rows from information_schema.partitions where table_schema = schema() and table_name='image_subpart' ;驗證，其部分結果如下圖所示：

難道10w條數據還不夠說明問題？平均下來每個分區可是有近800條數據！好吧，來點猛的：我再插入990w條數據，總計1kw數據。結果還是一樣，奇數編號分區沒有數據，偶數編號都有分區。

問題思考

我們再來回想一下KEY分區的原理： 通過MySQL內置hash算法對分片鍵計算hash值后再對分區數取模 。這個原理也可以從MySQL官網找到，請戳鏈接：22.2.5 KEY Partitioning: https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html，截取原文如下：

Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. NDB Cluster uses MD5() for this purpose; for tables using other storage engines, the server employs its own internal hashing function which is based on the same algorithm as PASSWORD().

**這個世界上不會有這么渣渣的hash算法吧？**隨便寫個什么算法也不至于這么不均勻吧？這時候我懷疑是否有一些什么配置引起的。但是show variables中并沒有任何與partition相關的變量。

這個時候，一萬匹馬奔騰而過。會不會是文檔和源碼不同步導致的？好吧，看MySQL的源碼，畢竟， 源碼才是最接近真相的地方 。KEY分區相關源碼在文件sql_partition.cc中，筆者截取部分關鍵源碼，如下所示，初略觀察，并沒有什么不妥，先計算分區字段的hash值然后對分區數取模：

/**
 Calculate part_id for (SUB)PARTITION BY KEY
 @param file        Handler to storage engine
 @param field_array     Array of fields for PARTTION KEY
 @param num_parts      Number of KEY partitions
 @param func_value[out]   Returns calculated hash value
 @return Calculated partition id
*/
inline
static uint32 get_part_id_key(handler *file,
               Field **field_array,
               uint num_parts,
               longlong *func_value)
{
 DBUG_ENTER("get_part_id_key");
 // 計算分區字段的hash值
 *func_value= file->calculate_key_hash_value(field_array);
 // 對分區數取模
 DBUG_RETURN((uint32) (*func_value % num_parts));
}

懷著絕望的心情，請出搜索引擎搜索：“KEY分區數據不均勻”，搜索結果中的CSDN論壇（ https://bbs.csdn.net/topics/390857704）里有個民間高手華夏小卒回答如下：

一個同事根據password函數，分析并測出，key分區，只能指定分區數目為質數，才能保證每個分區都有數據。我測了下，從11個分區，到17個分區。 只有11,13,17 ，這3個分區的數據是基本平均分布的。

這個時候，又是一萬匹馬奔騰而過。不過 WHAT THE F**K 的同時，心里也是有點小激動，因為可能找到解決辦法了（雖然還不知道MySQL內置hash算法為毛會這樣），最后筆者再次對KEY分區測試并得出總結如下：

1. 如果設置40，64，128等偶數個分區數（PARTITIONS 64），會導致編號為奇數的分區（p1, p3, p5, p7, … p2n-1）完全插不進數據；
2. 如果設置63，121（PARTITIONS 63）這種奇數但非質數個分區數，所有分區都會有數據，但是不均勻；
3. 如果設置137，31這種質數個分區數（PARTITIONS 137），所有分區都會有數據，并且非常均勻；

如下圖所示，是筆者把分區數調整為127并插入100w數據后的情況，通過SQL證明每個分區的數據量幾乎一樣：

關于MySQL使用KEY分區時有哪些常見的誤區就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。