MySQL使用KEY分區(qū)時有哪些常見的誤區(qū)

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

需求背景

業(yè)務(wù)表tb_image部分?jǐn)?shù)據(jù)如下所示，其中id唯一，image_no不唯一。image_no表示每個文件的編號，每個文件在業(yè)務(wù)系統(tǒng)中會生成若干個文件，每個文件的唯一ID就是字段id：

業(yè)務(wù)表tb_image的一些情況如下：

• 根據(jù)image_no查詢和根據(jù)id查詢；
• 存量數(shù)據(jù)2kw；
• 日增長4w左右；
• 日查詢量20w左右；
• 非ToC系統(tǒng)，所以并發(fā)的天花板可見；
   

方案選擇

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

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

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

性能測試

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

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

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

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

問題思考

我們再來回想一下KEY分區(qū)的原理： 通過MySQL內(nèi)置hash算法對分片鍵計算hash值后再對分區(qū)數(shù)取模 。這個原理也可以從MySQL官網(wǎng)找到，請戳鏈接：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相關(guān)的變量。

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

/**
 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");
 // 計算分區(qū)字段的hash值
 *func_value= file->calculate_key_hash_value(field_array);
 // 對分區(qū)數(shù)取模
 DBUG_RETURN((uint32) (*func_value % num_parts));
}

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

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

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

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

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

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