快捷導(dǎo)航

MySQL學(xué)習(xí)記錄之KEY分區(qū)引發(fā)的血案

更新時(shí)間：2020年11月03日 11:59:06 作者：阿飛Javaer

這篇文章主要給大家介紹了關(guān)于MySQL學(xué)習(xí)記錄之KEY分區(qū)引發(fā)的血案的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

需求背景

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

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

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

方案選擇

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

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

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

性能測(cè)試

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

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

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

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

問(wèn)題思考

我們?cè)賮?lái)回想一下KEY分區(qū)的原理：通過(guò)MySQL內(nèi)置hash算法對(duì)分片鍵計(jì)算hash值后再對(duì)分區(qū)數(shù)取模。這個(gè)原理也可以從MySQL官網(wǎng)找到，請(qǐ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().

**這個(gè)世界上不會(huì)有這么渣渣的hash算法吧？**隨便寫(xiě)個(gè)什么算法也不至于這么不均勻吧？這時(shí)候我懷疑是否有一些什么配置引起的。但是show variables中并沒(méi)有任何與partition相關(guān)的變量。

這個(gè)時(shí)候，一萬(wàn)匹馬奔騰而過(guò)。會(huì)不會(huì)是文檔和源碼不同步導(dǎo)致的？好吧，看MySQL的源碼，畢竟，源碼才是最接近真相的地方。KEY分區(qū)相關(guān)源碼在文件sql_partition.cc中，筆者截取部分關(guān)鍵源碼，如下所示，初略觀察，并沒(méi)有什么不妥，先計(jì)算分區(qū)字段的hash值然后對(duì)分區(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");
 // 計(jì)算分區(qū)字段的hash值
 *func_value= file->calculate_key_hash_value(field_array);
 // 對(duì)分區(qū)數(shù)取模
 DBUG_RETURN((uint32) (*func_value % num_parts));
}

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

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

這個(gè)時(shí)候，又是一萬(wàn)匹馬奔騰而過(guò)。不過(guò) WHAT THE F**K 的同時(shí)，心里也是有點(diǎn)小激動(dòng)，因?yàn)榭赡苷业浇鉀Q辦法了（雖然還不知道MySQL內(nèi)置hash算法為毛會(huì)這樣），最后筆者再次對(duì)KEY分區(qū)測(cè)試并得出總結(jié)如下：

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

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