MySQL表刪除數(shù)據(jù)，索引文件會(huì)不會(huì)變小？
本文公眾號(hào)來(lái)源：微觀技術(shù)
作者：Tom哥
“大家好，我是3y。今天給大家分享下MySQL的一些小知識(shí)。
“以前在公司的時(shí)候提交申請(qǐng)修改表結(jié)構(gòu)工單執(zhí)行DDL（比如增加一個(gè)列），DBA都會(huì)問(wèn)下表現(xiàn)在的數(shù)據(jù)量有多少，會(huì)不會(huì)影響到業(yè)務(wù)。
原來(lái)這跟DDL的原理有關(guān)阿（關(guān)鍵字：Online DDL）。
如果我要新增一個(gè)列：那需要新增一張表，然后將主表的數(shù)據(jù)導(dǎo)到新表中，等完成后再rename...如果數(shù)據(jù)量大，還需要考慮主從延遲的問(wèn)題。
這篇文章又讓我了解到：原來(lái)刪除數(shù)據(jù)，表的空間是不會(huì)釋放的...
一張千萬(wàn)級(jí)的數(shù)據(jù)表，刪除了一半的數(shù)據(jù)，你覺(jué)得B+樹(shù)索引文件會(huì)不會(huì)變小？
我們先來(lái)做個(gè)實(shí)驗(yàn)，看看表的大小是如何變化的？？
做個(gè)實(shí)驗(yàn)，讓數(shù)據(jù)說(shuō)話
1、首先，在mysql中創(chuàng)建一張用戶表，表結(jié)構(gòu)如下：
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用戶名',
`age` int(11) NOT NULL COMMENT '年齡',
`address` varchar(128) COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
2、造數(shù)據(jù)。用戶表中批量插入1000W條數(shù)據(jù)
@GetMapping("/insert_batch")
public Object insertBatch(@RequestParam("batch") int batch) {
// 設(shè)置批次batch=100000，共插入 1000W 條數(shù)據(jù)
for (int j = 1; j <= batch; j++) {
List<User> userList = new ArrayList<>();
for (int i = 1; i <= 100; i++) {
User user = User.builder().userName("Tom哥-" + ((j - 1) * 100 + i)).age(29).address("上海").build();
userList.add(user);
}
userMapper.insertBatch(userList);
}
return "success";
}
批量插入，每個(gè)批次100條記錄，100000個(gè)批次，共1000W條數(shù)據(jù)
3、查看表文件大小
索引文件大小約 595 M，最后修改時(shí)間 02:17
說(shuō)明：
MySQL 8.0 版本以前，表結(jié)構(gòu)是存在以 .frm為后綴的文件里獨(dú)享表空間存儲(chǔ)方式使用 .ibd文件來(lái)存放數(shù)據(jù)和索引，且每個(gè)表一個(gè).ibd文件
表數(shù)據(jù)既可以存在共享表空間，也可以是單獨(dú)文件。通過(guò)innodb_file_per_table參數(shù)控制。MySQL 5.6.6 版本之后，默認(rèn)是ON，這樣，每個(gè) InnoDB 表數(shù)據(jù)存儲(chǔ)在一個(gè)以 .ibd為后綴的文件中。
4、刪除 約500W條數(shù)據(jù)
@GetMapping("/delete_batch")
public Object deleteBatch(@RequestParam("batch") int batch) {
for (int j = 1; j <= batch; j++) {
List<Long> idList = new ArrayList<>();
for (int i = 1; i <= 100; i += 2) {
idList.add((long) ((j - 1) * 100 + i));
}
userMapper.deleteUser(idList);
}
return "success";
}
開(kāi)始時(shí)user表有1000W條數(shù)據(jù)，刪除若干后，目前剩余約 550W 條
5、在刪除約500W條記錄后，再次查看表文件大小
索引文件大小約 595 M，最后修改時(shí)間 10:34
實(shí)驗(yàn)結(jié)論：
對(duì)于千萬(wàn)級(jí)的表數(shù)據(jù)存儲(chǔ)，刪除大量記錄后，表文件大小并沒(méi)有隨之變小。好奇怪，是什么原因?qū)е碌模坎灰保酉聛?lái)，我們來(lái)深入剖析其中原因
數(shù)據(jù)表操作有新增、刪除、修改、查詢，其中查詢屬于讀操作，并不會(huì)修改文件內(nèi)容。修改文件內(nèi)容的是寫操作，具體分為有刪除、新增、修改三種類型。
接下來(lái)，我們開(kāi)始逐一分析
刪除數(shù)據(jù)
InnoDB 中的數(shù)據(jù)采用B+樹(shù)來(lái)組織結(jié)構(gòu)。如果對(duì)B+樹(shù)存儲(chǔ)結(jié)構(gòu)不清楚的話，可以先看下我之前寫的一篇文章，鞏固下基礎(chǔ)知識(shí)。
面試題：mysql 一棵 B+ 樹(shù)能存多少條數(shù)據(jù)？
假如表中已經(jīng)插入若干條記錄，構(gòu)造的B+樹(shù)結(jié)構(gòu)如下圖所示：
刪除id=7這條記錄，InnoDB引擎只是把id=7這條記錄標(biāo)記為刪除，但是空間保留。如果后面有id位于(6,19)區(qū)間內(nèi)的數(shù)據(jù)插入時(shí)，可以重復(fù)使用這個(gè)空間。
上圖，表示新插入一條id=16的記錄。
除了記錄可以復(fù)用外，數(shù)據(jù)頁(yè)也可以復(fù)用。當(dāng)整個(gè)頁(yè)從B+樹(shù)摘掉后，可以復(fù)用到任何位置。
比如，將page number=5頁(yè)上的所有記錄刪除以后，該page標(biāo)記為可復(fù)用。此時(shí)如果插入一條id=100的記錄需要使用新頁(yè)，此時(shí)page number=5便可以被復(fù)用了。
如果相鄰兩個(gè)page的利用率都很低，數(shù)據(jù)庫(kù)會(huì)將兩個(gè)頁(yè)的數(shù)據(jù)合并到其中一個(gè)page上，另一個(gè)page被標(biāo)記為可復(fù)用。
當(dāng)然，如果是像上面我們做的實(shí)驗(yàn)?zāi)菢樱瑢⒄麄€(gè)表的數(shù)據(jù)全部delete掉呢？所有的數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為可復(fù)用，但空間并沒(méi)有釋放，所以表文件大小依然沒(méi)有改變。
總結(jié)：delete命令只是把數(shù)據(jù)頁(yè)或記錄位置標(biāo)記為可復(fù)用，表空間并沒(méi)有被回收，該現(xiàn)象我們稱之為”空洞“
新增數(shù)據(jù)
如果是插入的數(shù)據(jù)是隨機(jī)的非主鍵有序，可能會(huì)造成數(shù)據(jù)頁(yè)分裂。
上圖可以看到，假如page number=5的數(shù)據(jù)頁(yè)已經(jīng)滿了，此時(shí)插入id=15的記錄，需要申請(qǐng)一個(gè)新的頁(yè)page number=6來(lái)保存數(shù)據(jù)。待頁(yè)分裂完成后，page number=5的最后位置就會(huì)留下一個(gè)可復(fù)用的空洞。
相反，如果數(shù)據(jù)是按照索引遞增順序插入的，那么索引是緊湊的，不會(huì)出現(xiàn)數(shù)據(jù)頁(yè)分裂。
修改數(shù)據(jù)
如果修改的是非索引值，那么并不會(huì)影響B(tài)+樹(shù)的結(jié)構(gòu)
比如，更新id=7的其它字段值，主鍵id保持不變。整個(gè)B+樹(shù)并沒(méi)有發(fā)生結(jié)構(gòu)調(diào)整。
但是，如果修改的內(nèi)容包含了索引，那么操作步驟是先刪除一個(gè)舊的值，然后再插入一個(gè)新值。可能會(huì)造成空洞。
分析發(fā)現(xiàn)，新增、修改、刪除數(shù)據(jù)，都可能造成表空洞，那么有沒(méi)有什么辦法壓縮表空間？？
客官，請(qǐng)繼續(xù)往下看
新建表
我們可以新建一個(gè)影子表B與原表A的結(jié)構(gòu)一致，然后按主鍵id由小到大，把數(shù)據(jù)從表A遷移到表B。由于表B是新表，并不會(huì)有空洞，數(shù)據(jù)頁(yè)的利用率更高。
待表A的數(shù)據(jù)全部遷移完成后，再用表B替換表A。
MySQL 5.5 版本之前，提供了一鍵命令，快捷式完成整個(gè)流程，轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表。
alter table 表名 engine=InnoDB
但是，該方案有個(gè)致命缺點(diǎn)，表重構(gòu)過(guò)程中，如果有新的數(shù)據(jù)寫入表A時(shí)，不會(huì)被遷移，會(huì)造成數(shù)據(jù)丟失。
Online DDL
為了解決上面問(wèn)題，MySQL 5.6 版本開(kāi)始引入 Online DDL，對(duì)流程做了優(yōu)化。
執(zhí)行步驟：
新建一個(gè)臨時(shí)文件 掃描表A主鍵的所有數(shù)據(jù)頁(yè)，生成B+ 樹(shù)，存儲(chǔ)到臨時(shí)文件中 在生成臨時(shí)文件過(guò)程中，如果有對(duì)表A做寫操作，操作會(huì)記錄到一個(gè)日志文件中 當(dāng)臨時(shí)文件生成后，再重放日志文件，將操作應(yīng)用到臨時(shí)文件 用臨時(shí)文件替換表A的數(shù)據(jù)文件 刪除舊的表A數(shù)據(jù)文件
與新建表的最大區(qū)別，增加了日志文件記錄和重放功能。遷移過(guò)程中，允許對(duì)表A做增刪改操作。
“我是3y，我們下期再見(jiàn)吧。
卷王之卷的《對(duì)線面試官》系列目前已經(jīng)連載24篇啦！每周更新兩篇，首發(fā)公眾號(hào)【面試造火箭】，歡迎持續(xù)關(guān)注：
【對(duì)線面試官】Java注解 【對(duì)線面試官】Java泛型 【對(duì)線面試官】 Java NIO 【對(duì)線面試官】Java反射 && 動(dòng)態(tài)代理 【對(duì)線面試官】多線程基礎(chǔ) 【對(duì)線面試官】 CAS 【對(duì)線面試官】synchronized 【對(duì)線面試官】AQS&&ReentrantLock 【對(duì)線面試官】線程池 【對(duì)線面試官】ThreadLocal 【對(duì)線面試官】CountDownLatch和CyclicBarrier 【對(duì)線面試官】List 【對(duì)線面試官】Map 【對(duì)線面試官】SpringMVC 【對(duì)線面試官】Spring基礎(chǔ) 【對(duì)線面試官】SpringBean生命周期 【對(duì)線面試官】Redis基礎(chǔ) 【對(duì)線面試官】Redis持久化 【對(duì)線面試官】Kafka基礎(chǔ) 【對(duì)線面試官】使用Kafka會(huì)考慮什么問(wèn)題？ 【對(duì)線面試官】MySQL索引 【對(duì)線面試官】MySQL 事務(wù)&&鎖機(jī)制&&MVCC
