淺談MySQL表空間回收的正確姿勢(shì)
不知道大家有沒有遇到這樣的一種情況,線上業(yè)務(wù)在MySQL表上做增刪改查操作,隨著時(shí)間的推移,表里面的數(shù)據(jù)越來越多,表數(shù)據(jù)文件越來越大,數(shù)據(jù)庫(kù)占用的空間自然也逐漸增長(zhǎng)
為了縮小磁盤上表數(shù)據(jù)文件占用的空間,我們?cè)谧畲蟮囊粡垬I(yè)務(wù)表中用delete命令刪除了一半兒的舊數(shù)據(jù),刪除之后,磁盤上表數(shù)據(jù)文件并沒有縮小,即使刪除整張表的數(shù)據(jù),文件依然沒有變小,這是為什么呢?
本文將詳細(xì)的分析上述問題,并給出正確回收表空間的方法
前置說明
目前大部分MySQL數(shù)據(jù)庫(kù)都是用的 InnoDB 引擎,所以如無特殊說明,文中的實(shí)例都是基于InnoDB引擎的
在MySQL配置中有個(gè)配置項(xiàng)叫 innodb_file_per_table
將它設(shè)置為1之后,
每個(gè)表的數(shù)據(jù)會(huì)單獨(dú)存儲(chǔ)在一個(gè)以 .ibd
為后綴的文件中
如果 innodb_file_per_table
沒有開啟的話,
表的數(shù)據(jù)是存儲(chǔ)在系統(tǒng)的共享表空間,這樣即使刪除了表,共享表空間也不會(huì)釋放這部分空間
所以,通常情況下,都是將 innodb_file_per_table
選項(xiàng)設(shè)置為 1, 同時(shí)為了能直觀的看到表數(shù)據(jù)文件的大小變化,文中的實(shí)例也都是基于開啟了 此選項(xiàng)來說明的
問題重現(xiàn)
新建一張表ta
,表的結(jié)構(gòu)如下
mysql> show create table ta\G *************************** 1. row *************************** Table: ta Create Table: CREATE TABLE `ta` ( `id` int(11) NOT NULL, `ia` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
使用下面的存儲(chǔ)過程,向 ta
中批量插入數(shù)據(jù)
delimiter // create procedure multinsert(in beg int,in cnt int) begin declare icnt int default 0; declare tmp int default 0; while icnt < cnt do set icnt = icnt + 1; set tmp = beg + icnt; insert into ta(id,ia) values(tmp,tmp); end while; end// delimiter ;
在MySQL控制臺(tái)執(zhí)行 call multinsert(0,100000)
命令,往 ta
表插入10萬條數(shù)據(jù)
mysql> call multinsert(0,100000); mysql> select count(*) from ta; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.02 sec)
查看磁盤上ta
表的數(shù)據(jù)文件 ta.ibd
的大小
[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ [root@ecs-centos-7 test]# ls -l ta.ibd -rw-r----- 1 mysql mysql 11534336 1月 3 23:14 ta.ibd
從上面的結(jié)果可以知道,ta
表插入10萬條數(shù)據(jù)之后,ta.ibd
大小為 11534336 字節(jié)( 大約 11M )
現(xiàn)在我們使用 delete
命令刪除一半兒表數(shù)據(jù)( 5萬行記錄 )
mysql> delete from ta where id between 1 and 50000; Query OK, 10000 rows affected (0.03 sec) mysql> select count(*) from ta; +----------+ | count(*) | +----------+ | 50000 | +----------+ 1 row in set (0.02 sec)
刪除操作完成之后,再次查看磁盤上 ta.ibd
的大小
[root@ecs-centos-7 test]# cd /var/lib/mysql/test/ [root@ecs-centos-7 test]# ls -l ta.ibd -rw-r----- 1 mysql mysql 11534336 1月 3 23:14 ta.ibd
從上面的結(jié)果可以知道,ta
表刪除了一半兒,也就是5萬行數(shù)據(jù)之后,ta.ibd
的大小是 11534336 字節(jié)( 約11M )
也就是說 ta
表刪除數(shù)據(jù)前后,磁盤上表數(shù)據(jù)文件并沒有縮小
要弄明白數(shù)據(jù)文件為什么沒有縮小,就需要深入了解刪除數(shù)據(jù)的原理
刪除數(shù)據(jù)原理
我們都知道,InnoDB里的數(shù)據(jù)都是用B+樹組織的,關(guān)于B+樹的知識(shí)請(qǐng)參考 理解B+樹
圖(1)
上面是InnoDB的索引示意圖,其中用虛線框起來的節(jié)點(diǎn)是屬于Page1數(shù)據(jù)頁(yè),葉子節(jié)點(diǎn)存儲(chǔ)的是索引對(duì)應(yīng)的數(shù)據(jù),它們按照索引從小到大的順序組成了一個(gè)有序數(shù)組
假如我們要?jiǎng)h除Page1頁(yè)中索引key值為 13 的數(shù)據(jù),也即上圖中紅色部分
InnoDB引擎會(huì)把索引key值為13的節(jié)點(diǎn)標(biāo)記為已刪除,它并不會(huì)回收節(jié)點(diǎn)真實(shí)的物理空間,只是將它標(biāo)記為已刪除的節(jié)點(diǎn),后續(xù)是可以復(fù)用的,所以,刪除表記錄,磁盤上數(shù)據(jù)文件不會(huì)縮小
你可能會(huì)說,上面只是刪除了Page1頁(yè)中一個(gè)節(jié)點(diǎn)的數(shù)據(jù),那如果把Page1頁(yè)中節(jié)點(diǎn)數(shù)據(jù)全部刪除了,應(yīng)該會(huì)回收Page1頁(yè)的空間吧?
答案是,不會(huì)回收
當(dāng)Page1頁(yè)數(shù)據(jù)全部刪除了,整個(gè)數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為已刪除,并且整個(gè)數(shù)據(jù)頁(yè)都可以復(fù)用,所以,這種情況下,磁盤上的數(shù)據(jù)文件仍然不會(huì)縮小
數(shù)據(jù)的復(fù)用
數(shù)據(jù)的復(fù)用涉及到數(shù)據(jù)節(jié)點(diǎn)的插入、刪除、轉(zhuǎn)移以及數(shù)據(jù)頁(yè)的合并等操作,具體的操作流程相關(guān)的細(xì)節(jié)請(qǐng)參考 理解B+樹,這里就不再重復(fù)說明了
- 數(shù)據(jù)節(jié)點(diǎn)的復(fù)用
在上面 圖(1) 中,當(dāng)刪除了索引key值為 13 的節(jié)點(diǎn)后,此節(jié)點(diǎn)就被標(biāo)記為可復(fù)用的
如果之后又插入了一條索引key值在 7 到 18 之間的記錄時(shí),就會(huì)復(fù)用原來索引key值為13的數(shù)據(jù)節(jié)點(diǎn)
但是如果之后插入的記錄的索引key值不在 7 到 18 之間時(shí),可能就無法復(fù)用原來索引key值為13的數(shù)據(jù)節(jié)點(diǎn)
也就是說,數(shù)據(jù)節(jié)點(diǎn)的復(fù)用,需要索引key值滿足一定的范圍條件
- 數(shù)據(jù)頁(yè)的復(fù)用
在 圖(1) 當(dāng)刪除了Page1數(shù)據(jù)頁(yè)全部數(shù)據(jù)節(jié)點(diǎn)后,Page1整頁(yè)都是可復(fù)用的,當(dāng)插入的記錄需要用到新頁(yè)的時(shí)候,Page1就可以被復(fù)用
當(dāng)相鄰的數(shù)據(jù)頁(yè)利用率比較低的時(shí)候,有可能會(huì)把它們合并到其中一個(gè)數(shù)據(jù)頁(yè)中,這時(shí),另外一個(gè)數(shù)據(jù)頁(yè)就空出來了,這個(gè)空出來的數(shù)據(jù)頁(yè)就變成可復(fù)用的了
哪些操作會(huì)造成數(shù)據(jù)空洞
我們用 delete
命令刪除一條記錄后,InnoDB只是把對(duì)應(yīng)的數(shù)據(jù)節(jié)點(diǎn)標(biāo)記為已刪除且可復(fù)用的,這些可空著的等待使用的數(shù)據(jù)節(jié)點(diǎn)可以看作是一個(gè)一個(gè)的數(shù)據(jù)空洞
- 刪除數(shù)據(jù)
刪除數(shù)據(jù)的時(shí)候,會(huì)造成數(shù)據(jù)空洞,前面已經(jīng)解釋過,這里不再贅述了
- 插入數(shù)據(jù)
如果數(shù)據(jù)是按照索引大小順序插入,這個(gè)時(shí)候數(shù)據(jù)頁(yè)是緊湊的,不會(huì)出現(xiàn)數(shù)據(jù)空洞
如果是從索引中間插入的話,有可能會(huì)造成頁(yè)分裂,分裂之后的頁(yè)有可能出現(xiàn)數(shù)據(jù)空洞,下圖就是插入導(dǎo)致頁(yè)分裂的一個(gè)例子
如圖所示,分裂前葉子頁(yè)面已經(jīng)滿了,這時(shí)數(shù)據(jù)排列得很緊湊
現(xiàn)在插入了一個(gè)索引key值為15的數(shù)據(jù),插入之后,Page1 頁(yè)分裂成了上圖中 Page1,Page2
兩個(gè)頁(yè)面
分裂之后,Page1 頁(yè)面出現(xiàn)了兩個(gè)空洞,這兩個(gè)數(shù)據(jù)節(jié)點(diǎn)是可復(fù)用的,而 Page2頁(yè)面剛好滿了
- 更新數(shù)據(jù)
更新數(shù)據(jù)可以看成先刪除再插入,也是有可能造成數(shù)據(jù)空洞
比如: id
是表 ta
的主鍵, update ta set id = 10 where id = 1
語(yǔ)句把 id = 1
修改為 id = 10
,相當(dāng)于先刪除 id = 1
的記錄,再插入 id = 10
的記錄,這種情況是會(huì)產(chǎn)生數(shù)據(jù)空洞的
但是如果是類似 update ta set ia = ia + 1 where id = 1
這種沒有更改主鍵值的語(yǔ)句是不會(huì)造成空洞的
所以,更新數(shù)據(jù)可能會(huì)造成數(shù)據(jù)空洞
總結(jié)下來就是,表的增刪改操作,可能會(huì)造成數(shù)據(jù)空洞的,而線上的服務(wù)會(huì)對(duì)表進(jìn)行大量的增刪改操作,數(shù)據(jù)空洞存在的可能性比較大
如何收縮表空間
既然一張表,經(jīng)過大量無規(guī)則的增刪改操作之后,會(huì)產(chǎn)生大量的數(shù)據(jù)空洞
那如果我們新建一張和原來有數(shù)據(jù)空洞的表結(jié)構(gòu)相同的新表,然后把舊表中的數(shù)據(jù)按照索引升序依次插入到新表中,待舊表數(shù)據(jù)全部插入到新表之后,刪除舊表,再把新表重命名為舊表的名字
由于新表中葉子節(jié)點(diǎn)數(shù)據(jù)是按順序添加的,所以頁(yè)面是很緊湊的, 頁(yè)面利用率很高,需要的頁(yè)面比舊表少了很多,這樣舊表中索引上的空洞在新表就不存在了,新表數(shù)據(jù)文件占用的磁盤空間自然就會(huì)縮小,這樣就實(shí)現(xiàn)了表空間的收縮的目的
下面介紹的幾種收縮表空間的方法,雖然方法不同,但是基本的原理都是通過重建表的形式來達(dá)到目的的
- truntace table 表名
此操作等于 drop + create
,先刪除表,然后再創(chuàng)建一個(gè)同名的新表,當(dāng)然,再執(zhí)行 truncate table
命令之前需要先保存一份舊表的數(shù)據(jù), 命令執(zhí)行完成之后,再把這份數(shù)據(jù)導(dǎo)入新表
- alter table 表名 engine=InnoDB
這個(gè)操作是遍歷舊表主鍵索引的數(shù)據(jù)頁(yè),把數(shù)據(jù)頁(yè)中的記錄生成B+樹結(jié)構(gòu),存儲(chǔ)到磁盤上的臨時(shí)文件中,數(shù)據(jù)頁(yè)遍歷完了之后,用臨時(shí)文件替換掉舊表的數(shù)據(jù)文件
從MySQL5.6版本之后,這個(gè)操作是 Online DDL 的,需要說明的是,這種方法需要掃描表數(shù)據(jù)文件,對(duì)于大表來說是非常耗時(shí)的,如果是針對(duì)線上服務(wù)的話,需要避開業(yè)務(wù)高峰期,小心操作。
注意:
在重建表的時(shí)候,InnoDB 不會(huì)把整張表占滿,每個(gè)頁(yè)留了大概10%左右的數(shù)據(jù)節(jié)點(diǎn) 給后續(xù)的更新用, 也就是說,其實(shí)重建表之后并不是最緊湊的
假如有這么一個(gè)過程: 將表 t 重建一次,
插入一部分?jǐn)?shù)據(jù),但是插入的這些數(shù)據(jù),用掉了一部分的預(yù)留空間,
這種情況下,再重建一次表 t,就可能會(huì)出現(xiàn)重建表后比重建之前占用的空間還要大
小結(jié)
本文從一個(gè)實(shí)際的問題出發(fā),重現(xiàn)問題、分析問題到解決問題,每一步都進(jìn)行了詳細(xì)的分析,限于篇幅,有些細(xì)節(jié)沒有深入,需要讀者自行了解
到此這篇關(guān)于淺談MySQL表空間回收的正確姿勢(shì)的文章就介紹到這了,更多相關(guān)MySQL表空間回收內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
詳解MySQL誤操作后怎樣進(jìn)行數(shù)據(jù)恢復(fù)
在大家日常操作數(shù)據(jù)庫(kù)時(shí)候難免會(huì)因?yàn)椤按笠狻倍`操作,那么誤操作后怎樣進(jìn)行數(shù)據(jù)恢復(fù)呢,下面跟著小編一起來學(xué)習(xí)學(xué)習(xí)。2016-08-08mysql開啟慢查詢(EXPLAIN SQL語(yǔ)句使用介紹)
這篇文章主要介紹了mysql開啟慢查詢 EXPLAIN SQL語(yǔ)句,需要的朋友可以參考下2018-01-01MyEclipse連接MySQL數(shù)據(jù)庫(kù)報(bào)錯(cuò)解決辦法
我們現(xiàn)在一般網(wǎng)站都是利用的MySQL數(shù)據(jù)庫(kù)搭建網(wǎng)站的,但是在網(wǎng)上看到很多網(wǎng)友吐槽數(shù)據(jù)庫(kù)連接不上的問題,現(xiàn)在我就結(jié)合相關(guān)資料向提出一些我個(gè)人的見解,希望對(duì)大家解決問題有幫助2014-01-01在MySQL中使用通配符時(shí)應(yīng)該注意的問題
這篇文章主要介紹了在MySQL中使用通配符時(shí)應(yīng)該注意的問題,主要是下劃線的使用容易引起的錯(cuò)誤,需要的朋友可以參考下2015-05-05