需求背景
業(yè)務(wù)表tb_image部分?jǐn)?shù)據(jù)如下所示，其中id唯一，image_no不唯一。image_no表示每個文件的編號，每個文件在業(yè)務(wù)系統(tǒng)中會生成若干個文件，每個文件的唯一ID就是字段id：
業(yè)務(wù)表tb_image的一些情況如下：
方案選擇
根據(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é)如下：
如下圖所示，是筆者把分區(qū)數(shù)調(diào)整為127并插入100w數(shù)據(jù)后的情況，通過SQL證明每個分區(qū)的數(shù)據(jù)量幾乎一樣：
總結(jié)回顧
MySQL的KEY分區(qū)這么大的使用陷阱，居然在官方上沒有任何說明，這讓筆者感到非常震驚。此外還有MySQL bug：Bug #72428 Partition by KEY() results in uneven data distribution
正在看此文并有很強(qiáng)烈興趣的同學(xué)，可以嘗試更深入這個問題。筆者接下來也會找個時間，根據(jù)MySQL源碼深入挖掘其hash算法的實現(xiàn)為什么對分區(qū)數(shù)如此敏感。
到此這篇關(guān)于MySQL學(xué)習(xí)記錄之KEY分區(qū)引發(fā)的血案的文章就介紹到這了,更多相關(guān)MySQL KEY分區(qū)血案內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：合肥 阿里 呼和浩特 公主嶺 沈陽 天津 惠州 牡丹江
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL學(xué)習(xí)記錄之KEY分區(qū)引發(fā)的血案》，本文關(guān)鍵詞 MySQL,學(xué)習(xí),記錄,之,KEY,分區(qū),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
