溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

mysql億級(jí)大表重構(gòu)方案介紹

發(fā)布時(shí)間：2020-08-11 13:23:32 來(lái)源：ITPUB博客 閱讀：432 作者：caoyutingtjpu 欄目：MySQL數(shù)據(jù)庫(kù)

mysql億級(jí)大表重構(gòu)方案介紹

 作者：sylar版權(quán)所有[文章允許轉(zhuǎn)載,但必須以鏈接方式注明源地址,否則追究法律責(zé)任.]

【情況簡(jiǎn)述】

   本文主要分享的博主將mysql生產(chǎn)環(huán)境上億大表按照一定規(guī)則拆分成若干個(gè)小表并遷移的思路、實(shí)現(xiàn)方式、注意事項(xiàng)等等。

【背景說(shuō)明】

   生產(chǎn)環(huán)境favourite表5.8億，情況如下：

表名

表結(jié)構(gòu)

rows

數(shù)據(jù)庫(kù)版本

favourite

CREATE TABLE `favourite` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`target_type` int(11) NOT NULL,

`target_id` int(11) NOT NULL,

`created_at` datetime NOT NULL,

`status` smallint(6) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

  UNIQUE KEY `uniq_user_target` (`user_id`,`target_type`,`target_id`),

  KEY `idx_targetid` (`target_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4

587312519

5.7.12


  下面sql因表的量級(jí)變的比較慢，已無(wú)法通過(guò)調(diào)整索引或調(diào)整sql進(jìn)行優(yōu)化：

SQL

time

SELECT count(1) AS count_1 FROM `favourite` WHERE `favourite`.target_id = 636 AND `favourite`.target_type = 1

4.7S

SELECT `favourite`.target_id AS `favourite_target_id` FROM `favourite` WHERE `favourite`.user_id = 338072 AND `favourite`.target_type = 0 AND `favourite`.status = 0 ORDER BY `favourite`.id DESC

2.25S

DELETE FROM favourite WHERE user_id = 17327373 AND target_id = 917 AND target_type = 1

0.9S

  為了業(yè)務(wù)響應(yīng)比較快，決定拆分favourite表。經(jīng)過(guò)業(yè)務(wù)溝通，user_id使用較為頻繁，故通過(guò)user_id拆分，拆分規(guī)則根據(jù)user_id%1024打算到1024表，映射關(guān)系如下：

user_id%1024 =0  =>favourite_0000

user_id%1024 =1  =>favourite_0001

user_id%1024 =2  =>favourite_0002

……

user_id%1024 =1023  =>favourite_1023


注意：

1)拆分一定要根據(jù)業(yè)務(wù)情況來(lái)決定，不能一概而論！


【思路說(shuō)明】

1、配置好canal ，canal是阿里開(kāi)源的獲取binlog信息的軟件。從第一步開(kāi)始到最后結(jié)束，canal一直不停獲取binlog信息。

2、在不影響業(yè)務(wù)的數(shù)據(jù)庫(kù)上（此處用的從庫(kù)）將favourite導(dǎo)出成1024個(gè)表對(duì)應(yīng)的文件

3、將導(dǎo)出備份文件導(dǎo)入生產(chǎn)環(huán)境

4、將canal獲取的數(shù)據(jù)導(dǎo)入到1024個(gè)分表（一直進(jìn)行直到結(jié)束）

5、待分表數(shù)據(jù)與原大表數(shù)據(jù)差不多時(shí)，在業(yè)務(wù)不繁忙時(shí)，切favourite業(yè)務(wù)讀操作

6、切生產(chǎn)favourite寫(xiě)操作

7、待canal無(wú)新的記錄產(chǎn)生，整個(gè)業(yè)務(wù)切換完畢

8、結(jié)束


注意：

1)使用canal獲取binlog信息，注意參數(shù)設(shè)置為

    binlog_format=row

    binlog_row_image=full（默認(rèn)是FULL，以防有些實(shí)例設(shè)置為minimal）

     binlog_row_image=minimal，此時(shí)主庫(kù)進(jìn)行delete from a where target_id =，在binlog記錄的也是這樣的操作，而如果target_id是唯一索引，則到了binlog記錄的是delete from a where id（id是主鍵）。同樣生產(chǎn)是delete favourite 是根據(jù)`user_id`,`target_type`,`target_id`，根據(jù)前面所說(shuō)記錄到binlog的是 delete from favourite where id，canal需要在業(yè)務(wù)發(fā)生切換時(shí)候記錄變更的情況，可是獲取的是刪除的id，新的分表的id不會(huì)跟舊的favouriteid相同（因?yàn)榕f的favourite的id已經(jīng)到了8億多，新的分表沿用舊表id是很浪費(fèi)的情況）并不知道user_id是哪個(gè)，無(wú)法對(duì)新表進(jìn)行同步。

2)服務(wù)先切讀再切寫(xiě)的這個(gè)方式，在切換時(shí)間內(nèi)會(huì)有一定數(shù)據(jù)誤差的。比如用戶在業(yè)務(wù)切換讀操作的同時(shí)，往favourite表插入了一條數(shù)據(jù)；可是寫(xiě)操作沒(méi)有切過(guò)來(lái)，數(shù)據(jù)落入舊的favourite表，用戶就會(huì)發(fā)現(xiàn)異常影響用戶的體驗(yàn)。這個(gè)情況直到canal將所有信息同步到新的分表后才解決。如果自己的業(yè)務(wù)無(wú)法接受這個(gè)情況，可以根據(jù)實(shí)際情況調(diào)整，不一定按照此方式切換服務(wù)。


【方案說(shuō)明】

   根據(jù)上面思路，需要解決兩個(gè)問(wèn)題

1）如何配置canal。canal是阿里推出的獲取binlog的開(kāi)源產(chǎn)品，我們此次canal調(diào)用是java工程師幫忙寫(xiě)的代碼，博主不懂java，故省略canal配置信息。

2）如何將favourite導(dǎo)出成1024個(gè)分表所需的數(shù)據(jù)，然后倒入指定庫(kù)。

下面主要說(shuō)明問(wèn)題2實(shí)現(xiàn)的方式，一共有兩種：

items

方案一

方案二

實(shí)現(xiàn)手段

mysqldump

mycat

拆分耗時(shí)

4.5Hour

2Hour

準(zhǔn)備時(shí)間

3Hour,需要加函數(shù)索引

<1Hour,準(zhǔn)備mycat環(huán)境和mycat對(duì)應(yīng)的數(shù)據(jù)庫(kù)

優(yōu)點(diǎn)

不需要配置mycat環(huán)境

時(shí)間比方案一節(jié)省2Hour,導(dǎo)入目標(biāo)環(huán)境后不需在初始化id

缺點(diǎn)

耗時(shí)太久、導(dǎo)入目標(biāo)環(huán)境后還需要初始化id

需要熟悉mycat配置、分庫(kù)規(guī)則

具體方式

Step1.在從庫(kù)建立函數(shù)索引,耗時(shí)3Hour

Step2.在從庫(kù)使用mysqldump的--where參數(shù)導(dǎo)出

Step3.導(dǎo)入目標(biāo)庫(kù)并初始化id

Step1.搭建mycat環(huán)境，并配置好相關(guān)規(guī)則

Step2.使用mysqldump備份文件

Step3.將備份文件導(dǎo)入mycat

Step4.在mycat對(duì)應(yīng)庫(kù)初始化id

Step5.將處理后的文件導(dǎo)入目標(biāo)庫(kù)

【方式一:mysqldump】

Step1.在從庫(kù)建立函數(shù)索引,耗時(shí)3Hour

   alter table favourite  add `vis_user_id` int(11) GENERATED ALWAYS AS ((`user_id` % 1024)) STORED;

注意：

1）要在從庫(kù)建立函數(shù)索引，影響會(huì)降低很多，如果能把讓生產(chǎn)不訪問(wèn)該從庫(kù)更好。確保生產(chǎn)環(huán)境訪問(wèn)該從庫(kù)時(shí)沒(méi)有select * from favourite where …..這樣的命令

2）如果數(shù)據(jù)庫(kù)版本低于5.7無(wú)法使用函數(shù)索引，那么step2.mysqldump備份一次開(kāi)啟4個(gè)并發(fā)進(jìn)程，一次耗時(shí)230秒；如果有索引，則為30-60秒

 

Step2.在從庫(kù)使用mysqldump的--where參數(shù)導(dǎo)出

思路：

1）使用--where=" user%1024=0001"導(dǎo)出成按拆分規(guī)則命名的文件，該例子對(duì)應(yīng)文件名為0001.sql，一共會(huì)產(chǎn)生1024個(gè)這樣的文件。
2）然后根據(jù)導(dǎo)出的文件名用sed命令替換表名（sed是shell命令）

具體腳本如下：


mycat分表是一個(gè)邏輯庫(kù)對(duì)應(yīng)一個(gè)分表，所以需要?jiǎng)?chuàng)建1024個(gè)邏輯庫(kù)對(duì)應(yīng)1024個(gè)分表，考慮到機(jī)器IO，打算將1024個(gè)庫(kù)分到4個(gè)實(shí)例（數(shù)據(jù)庫(kù)均是單實(shí)例，如果有條件，可以分到更多的機(jī)器上，速度會(huì)快些）

  需要修改的配置文件：server.xml、schema.xml、rule.xml及其對(duì)應(yīng)的partition-hash-int.txt。具體修改請(qǐng)查看附件conf.zip

mysql億級(jí)大表重構(gòu)方案介紹conf.zip


注意：

1)需要提前創(chuàng)建1024個(gè)邏輯庫(kù)

2)這里是根據(jù)方案一提到的函數(shù)索引對(duì)應(yīng)的虛擬列vis_user_id來(lái)分的，這樣可以直接用mycat的枚舉分庫(kù)，如果不想用虛擬列，可以用mycat hash來(lái)劃分，這個(gè)對(duì)于數(shù)值劃分方式等同于user%1024，這個(gè)詳情參考mycat權(quán)威指南

3)需要提前在四個(gè)邏輯庫(kù)里創(chuàng)建好用于mycat訪問(wèn)的數(shù)據(jù)庫(kù)用戶

4)在創(chuàng)建完1024個(gè)邏輯庫(kù)后，登入mycat，再創(chuàng)建favourite表，這樣每個(gè)邏輯庫(kù)都有該表

 

Step2.使用mysqldump備份文件

   為了能快速導(dǎo)入mycat，故根據(jù)mycat分成4個(gè)實(shí)例規(guī)則（可以有誤差，不一定要完全一樣），導(dǎo)出4份不同數(shù)據(jù)，以便可以同時(shí)4份文件灌入mycat

使用mysqldump導(dǎo)出4個(gè)文件，以下備份同時(shí)進(jìn)行，耗時(shí)20分鐘：

  • mysqldump -u$USERNAME -p$PASSWORD -S $SOCKET --default-character-set=utf8mb4 -c --set-gtid-purged=OFF --skip-add-locks --skip-quick  --no-create-db --log-error=/data/cyt0324.log --skip-add-drop-table    kuaikan favourite --where=" mod(user_id,1024)<256 "     >   /data/favourite_256.sql
  • mysqldump ……………………  --where=" mod(user_id,1024)>=256 and mod(user_id,1024) <512   "     >   /data/favourite_512.sql
  • mysqldump ……………………  --where=" mod(user_id,1024)>=512 and mod(user_id,1024)< 768 "     >   /data/favourite_768.sql
  • mysqldump ……………………  --where=" mod(user_id,1024) >=768"     >   /data/favourite_1024.sql

注意：

1)請(qǐng)?jiān)趶膸?kù)或業(yè)務(wù)不去訪問(wèn)的數(shù)據(jù)庫(kù)上進(jìn)行備份

2)上面設(shè)置的參數(shù)請(qǐng)根據(jù)實(shí)際情況調(diào)整，一定要加上-c   --skip-add-locks參數(shù)，否則導(dǎo)入mycat會(huì)異常

Step3.將備份文件導(dǎo)入mycat

   將步驟2導(dǎo)出的四個(gè)備份文件同時(shí)灌入mycat，整個(gè)耗費(fèi)時(shí)間不足90分鐘。

注意:

1)請(qǐng)將該操作在后臺(tái)執(zhí)行，可以用screen命令來(lái)實(shí)現(xiàn)。因?yàn)閙ycat是長(zhǎng)連接，即使中途斷掉后，后面的語(yǔ)句可能還會(huì)繼續(xù)執(zhí)行，以防出現(xiàn)這樣情況，請(qǐng)放到后臺(tái)執(zhí)行

 

Step4.在mycat對(duì)應(yīng)庫(kù)初始化id

此處耗時(shí)30分鐘

1）在1024分庫(kù)上初始化id,去掉虛擬列，具體腳本如下


5.將處理后的文件導(dǎo)入目標(biāo)庫(kù)

此處耗時(shí)30分鐘

將處理后的備份文件導(dǎo)入生產(chǎn)新庫(kù)，并行導(dǎo)入，并行度最好不要超過(guò)3，因?yàn)閐ump導(dǎo)出后insert一次是3萬(wàn)左右，并行度太高，機(jī)器IO會(huì)hold不住，腳本如下：


點(diǎn)擊(此處)折疊或打開(kāi)

  1. function instance()
  2. {
  3.              log
  4.              echo "-----端口號(hào)為"$port"的mysql實(shí)例開(kāi)始按表并發(fā)倒入:開(kāi)始時(shí)間為"`date "+%Y-%m-%d %H:%M:%S"`
  5.              #調(diào)用執(zhí)行函數(shù)
  6.              dumpAllTable

  8. }



  12. #將要備份的單表從大到小輸出到日志里面
  13. function log()
  14. {
  15.             BACKUP_DIR="/data/backup/"
  16.             #過(guò)濾掉MySQL自帶的DB
  17.             if [ -e ${BACKUP_DIR}/cyt.log ];
  18.                then rm -rf ${BACKUP_DIR}/cyt.log;
  19.             fi;
  20.             for filename in `du -sk ${BACKUP_DIR}/*.sql | sort -nr |awk '{print $2}'`
  21.                do
  22.                a="kk_favourite"
  23.                echo "mysql -u${DB_USER} -p${DB_PASSWORD} --socket=${socket} --host=${host} -A ${a} --tee=/data/pat.log -e \"source ${filename}\"">>$BACKUP_DIR/cyt.log;
  24.              done
  25. }

  27.    

  29. #調(diào)用函數(shù)log，查看log日志調(diào)用并發(fā)函數(shù)實(shí)現(xiàn)多線程備份
  30. function dumpAllTable()
  31. {
  32.         local schemaFile="${BACKUP_DIR}/cyt.log"
  33.         #最大的表先備份（因多進(jìn)程并發(fā)，最短完成時(shí)間依賴于最大表的完成）
  34.         allTable=`cat $schemaFile | wc -l`
  35.         i_import=0
  36.         declare -a array_cmds
  37.         i_array=0
  38.         while read file; do
  39.                 i_import=`expr $i + 1`
  40.                 array_cmds[i_array]="${file}"
  41.                 i_array=`expr ${i_array} + 1`
  42.         done < ${BACKUP_DIR}/cyt.log
  43.         execConcurrency "${threadsNum}" "${array_cmds[@]}"
  44.         delta_ret_val=`echo $?`
  45. }





  51. #并發(fā)函數(shù)
  52. function execConcurrency()
  53. {
  54.         #并發(fā)數(shù)據(jù)量
  55.         local thread=$1
  56.         #并發(fā)命令
  57.         local cmd=$2
  58.         #定義管道，用于控制并發(fā)線程
  59.         tmp_fifofile="/tmp/$$.fifo"
  60.         mkfifo $tmp_fifofile
  61.         #輸入輸出重定向到文件描述符6
  62.         exec 6<>$tmp_fifofile
  63.         rm -f $tmp_fifofile
  64.         #向管道壓入指定數(shù)據(jù)的空格
  65.         for ((i=0;i<$thread;i++)); do
  66.                 echo
  67.         done >&6
  68.         #遍歷命令列表
  69.         while [ "$cmd" ]; do
  70.                 #從管道取出一個(gè)空格（如無(wú)空格則阻塞，達(dá)到控制并發(fā)的目的）
  71.                 read -u6
  72.                 #命令執(zhí)行完后壓回一個(gè)空格
  73.                 { eval $2;echo >&6; } & #> /dev/null 2>&1 &
  74.                 shift
  75.                 cmd=$2
  76.         done
  77.         #等待所有的后臺(tái)子進(jìn)程結(jié)束
  78.         wait
  79.         #關(guān)閉df6
  80.         exec 6>&-
  81. }


  84. #主函數(shù)
  85. function main()
  86. {
  87.         #獲取本地IP地址
  88.         host=""
  89.         port=
  90.         DATE=`date +%F`
  91.         socket=""
  92.         DB_USER=''
  93.         #數(shù)據(jù)庫(kù)用戶對(duì)應(yīng)的密碼
  94.         DB_PASSWORD=''
  95.         #記錄開(kāi)始的時(shí)間
  96.         BEGIN=`date "+%Y-%m-%d %H:%M:%S"`
  97.         BEGIN_T=`date -d "$BEGIN" +%s`
  98.         echo '--------------開(kāi)始按表并發(fā)備份:開(kāi)始時(shí)間為 '$BEGIN
  99.         #設(shè)置并發(fā)備份的線程數(shù)
  100.         threadsNum=8
  101.         #調(diào)用instance函數(shù)
  102.         instance
  103.         echo '--------------backup all database successfully?。?！結(jié)束時(shí)間:' `date "+%Y-%m-%d %H:%M:%S"`
  104. }

  106. main

【小節(jié)】

由于方案二速度比方案一節(jié)省至少2小時(shí)以上的時(shí)間，且導(dǎo)入目標(biāo)庫(kù)后無(wú)需其他處理，故生產(chǎn)環(huán)境遷移使用的方案二



向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手