【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

*IT界農民工*發表於2020-11-11

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

遷移資料常用

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

1、匯出檔案 - mysqldump 命令 

‍mysqldump 是 Mysql 自帶的邏輯備份工具。其備份原理是通過協議連線到 Mysql 資料庫,將需要備份的資料查詢出來轉換成對應的 insert 語句。當需要還原這些資料時,只要執行這些 insert 語句,即可將對應的資料還原。 

常用命令:

  • 匯出所有資料庫

mysqldump -uroot -p123456 --all-databases  >/tmp/all.sql
  • 匯出指定資料庫

mysqldump -uroot -p123456 --databases  db  >/tmp/db.sql
  • 匯出指定表

mysqldump -uroot -p123456 --databases  db  --tables a >/tmp/a.sql
  • 根據條件匯出資料

mysqldump -uroot -p123456 --databases db --tables a --where='id=1' >/tmp/a.sql
  • 只匯出表結構

mysqldump -uroot -p123456 --no-data --databases db  >/tmp/db.sql

2、匯入檔案 - source 命令

source 命令可以將匯出的 sql 檔案匯入進指定資料庫。

操作步驟:

  • use 資料庫;

  • soucre 已匯出的 sql 檔案。

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

遷移思路

1. 資料庫A歷史資料遷移到 hdfs(一種分散式檔案系統)上進行歸檔;

2. 刪除資料庫A已歸檔的表,使用 drop 命令;

3. 資料庫A上新建表,用於資料庫B遷移;

4. 資料庫B中除未完成單外都遷移至資料庫A;

5. 指令碼進行刪除資料庫B上已遷移資料。 

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

阿丁開講

1、引數介紹

在 Innodb 儲存引擎中,innodb_file_per_table 引數是用來控制表資料的儲存方式的。

當引數為 OFF 的時候,所有資料都存放於預設路徑下名為 ibdata* 的共享表空間裡,即將資料庫所有的表資料及索引檔案存放到一個檔案中。在刪除資料表的時候,ibdata* 檔案不會自動收縮。

當引數為 ON 的時候,每一個表都將儲存在一個以 .ibd 為字尾的檔案中。這樣每個表都有了自己獨立的表空間,通過 drop table 命令就可以將表空間進行回收。 

從 Mysql 5.6.6 版本開始,innodb_file_per_table 預設為 ON 狀態。 

2、引數設定

通過 show variables like '%per_table%' 命令,可以檢視 innodb_file_per_table 引數的當前狀態:

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

如果想修改引數的狀態,可通過 SET GLOBAL 動態地修改為 ON 或 OFF,也可以在 my.cnf 中做永久性修改。需要注意的是,在 my.cnf 中修改後生效的話需要重啟 mysqld 服務。 

疑問:如果之前引數為 OFF 狀態,設定為 ON 狀態後,表空間如何分配?

答案是僅對後續操作生效。

什麼意思呢?修改前的資料還維持原狀,也就是說之前的資料繼續存放於 ibdata* 檔案中,修改後的使用獨立表空間。 

所以建議在開始就將該引數設定為 ON 狀態。

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

第二天

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少! 

阿丁二次開講

在這之前要先介紹下 Innodb 儲存資料所用的 B+ 樹結構,畫個圖你理解下:

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

在圖中,P 代表一頁資料,R 代表一行資料。

假設我們要刪掉 R2 這條記錄,InnoDB 引擎只會將其標記為刪除狀態,並不會真正把這行資料所佔的空間釋放掉,也就是說這個坑位還留著。如果後續所插入的資料在 R1 與 R3 之間的話,這個空間是可以被使用上的。

假設我們恰好刪除了 R1、R2、R3 這三條記錄,也就是說 P1 這一頁的資料都被刪掉了,那麼 P1 所在的空間都會被標記為可複用。如果插入的資料需要使用新頁的話,P1 的坑位就可以被利用起來了。 

那麼你可能會問了,我插入的資料恰好巧妙的避開了這些位置呢。那我還能說啥,騷唄。這樣會造成很多空間被浪費,如果刪除大量的資料的話,被浪費的空間也會是巨大的。

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

optimize table 的本質是 ALTER TABLE xxx ENGINE = InnoDB;

在5.5版本之前,重建表的過程是這樣的:

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

然後用臨時檔案替換舊錶,這樣便實現了表的重建。

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

注意

1、控制遷移速度,防止主從延遲導致線上故障;

2、建立大表時,使用下面的建表語句可節省 50% 左右的空間:

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

3、使用 optimize table 壓縮表時,需要留夠一定的空間。

關於作者

作者:大家好,我是萊烏,BAT搬磚工一枚。從小公司進入大廠,一路走來收穫良多,想將這些經驗分享給有需要的人,因此建立了公眾號「IT界農民工」。定時更新,希望能幫助到你。

【漫畫】活見鬼,明明刪除了資料,空間卻沒減少!

 

相關文章