第十三章 為什麼表資料删掉一半，表檔案大小不變 ?

參數 innodb_file_per_table

表資料既可以存在共享表空間裡，也可以是單獨的檔案。這個行為是由參數 ​

​innodb_file_per_table​

​ 控制的：

• OFF：表的資料放在系統共享表空間，也就是跟資料字典放在一起
• ON：每個 InnoDB 表資料存儲在一個以​

  ​.ibd​

  ​ 為字尾的檔案中
• 建議将這個值設定為：ON

• 如果把一個表單獨放在一個檔案裡，通過 drop table 指令，系統就會直接删除這個檔案
• 如果把一個表放在共享表空間中，通過 drop table 指令，即使表删掉了，空間也是不會回收的

資料删除流程

InnoDB 裡的資料都是用 B+ 樹的結構組織的

• 假設，我們要删掉 R4 這個記錄，InnoDB 引擎隻會把 R4 這個記錄标記為删除
• 也就是隻做邏輯删除，不做實體删除；之後如果再次插入一個 ID 在 300 和 600 之間的記錄時可複用該空間

如果我們删掉了一個資料頁上的所有記錄，會怎麼樣 ?

整個資料頁就可以被複用了

資料頁的複用 和 記錄的複用 差別 ?

• 記錄的複用：

• 隻限于符合範圍條件的資料。
• 比如上面的這個例子，R4 這條記錄被删除後，如果插入一個 ID 是​

  ​400​

  ​ 的行，可以直接複用這個空間。
• 但如果插入的是一個 ID 是​

  ​800​

  ​ 的行，就不能複用這個位置了

• 資料頁的複用：

• 當整個資料頁從 B+ 樹裡面摘掉以後，可以複用到任何位置。
• 以 上圖 為例，如果将資料頁 page A 上的所有記錄删除以後，page A 會被标記為可複用。
• 這時候如果要插入一條 ID=​

  ​50​

  ​ 的記錄需要使用新頁的時候，page A 是可以被複用的。

• 如果相鄰的兩個資料頁使用率都很小，系統就會把這兩個頁上的資料合到其中一個頁上，另外一個資料頁就被标記為可複用。

• 這裡的相鄰是指B+樹上的邏輯相鄰，而不是表空間檔案的實體相鄰

如果我們用 delete 指令把整個表的資料都删除了，結果會怎樣 ?

• 所有的資料頁都會被标記為可複用。但是磁盤上，檔案不會變小。
• 使用delete删除資料時，隻是标記為已删除，而不是真正的表空間資料回收，是以資料依然是存在的，隻是不能被再次查詢出來。
• 是以，可以了解為 可以被複用，而沒有被回收，是以檔案大小是不會改變的。

造成 “空洞” 的原因 ?

1. 删除記錄
2. 插入記錄
3. 更新索引值

假設圖 1 中 page A 已經滿了，這時我要再插入一行資料，會怎樣呢 ?

• 由于 page A 滿了，再插入一個 ID 是​

  ​550​

  ​ 的資料時，就不得不再申請一個新的頁面 page B 來儲存資料了。
• 頁分裂完成後，page A 的末尾就留下了空洞（注意：實際上，可能不止 1 個記錄的位置是空洞）

重建表

重建表可以解決 “空洞” 問題

如果現在有一個表 A，需要做空間收縮，為了把表中存在的空洞去掉，你可以怎麼做呢 ?

• 可以建立一個與表 A 結構相同的表 B，然後按照主鍵 ID 遞增的順序，把資料一行一行地從表 A 裡讀出來再插入到表 B 中。
• 由于表 B 是建立的表，是以表 A 主鍵索引上的空洞，在表 B 中就都不存在了。
• 表 B 的主鍵索引更緊湊，資料頁的使用率也更高。
• 如果我們把表 B 作為臨時表，資料從表 A 導入表 B 的操作完成後，用表 B 替換 表 A，從效果上看，就起到了收縮表 A 空間的作用。

關于 ​

​Online DDL​

​

在 MySQL 5.5 之前：

這個指令的執行流程跟我們前面描述的差不多，差別隻是這個臨時表 B 不需要你自己建立，MySQL 會自動完成轉存資料、交換表名、删除舊表的操作。

顯然，花時間最多的步驟是往臨時表插入資料的過程，如果在這個過程中，有新的資料要寫入到表 A 的話，就會造成資料丢失。是以，在整個 DDL 過程中，表 A 中不能有更新。也就是說，這個 DDL 不是 Online 的。

在 MySQL 5.6 之後引入的 Online DDL，對這個操作流程做了優化：

1. 建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；
2. 用資料頁中表 A 的記錄生成 B+ 樹，存儲到臨時檔案中；
3. 生成臨時檔案的過程中，将所有對 A 的操作記錄在一個日志檔案（​

   ​row log​

   ​）中，對應的是圖中 state2 的狀态；
4. 臨時檔案生成後，将日志檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案，對應的就是圖中 state3 的狀态；
5. 用臨時檔案替換表 A 的資料檔案。

DDL 之前是要拿 MDL 寫鎖的，這樣還能叫 Online DDL 嗎 ?

• Online DDL 其實是會先擷取MDL寫鎖，再退化成MDL讀鎖；
• 但MDL寫鎖持有時間比較短，是以可以稱為Online；
• 而MDL讀鎖，不阻止資料增删查改，但會​

  ​阻止其它線程修改表結構​

  ​。

為什麼不直接解鎖使用呢 ?

為了保護自己，禁止其他線程對這個表同時做 DDL。

Online 和 inplace

關于 ​

​inplace​

​ ?

• 根據表 A 重建出來的資料是放在 “​

  ​tmp_file​

  ​” 裡的，這個臨時檔案是 InnoDB 在内部建立出來的
• 整個 DDL 過程都在 InnoDB 内部完成
• 對于 server 層來說，沒有把資料挪動到臨時表，是一個“原地”操作，這就是“inplace”名稱的來源。

• 在存儲引擎中實作，對于 server 層來說，看不到具體的過程，這種方式叫做 inplace

​

​tmp_file​

​ 也是要占用臨時空間的

• 重建表的語句：

alter table t engine=innodb,ALGORITHM=inplace;      

• 拷貝表的語句：

alter table t engine=innodb,ALGORITHM=copy;      

當你使用 ALGORITHM=copy 的時候，表示的是強制拷貝表，對應的流程就是 MySQL 5.5 之前的圖 的操作過程

對于 ​

​alter table​

​ 語句：

• ​

  ​inplace​

  ​ 模式表示在 InnoDB 引擎中複制表
• 而​

  ​copy​

  ​ 模式表示強制複制表，是在 server 層複制的表
• ​

  ​inplace​

  ​​ 在引擎層 建立​

  ​tmp_file​

  ​​ 檔案，​

  ​copy​

  ​​ 需要在​

  ​server​

  ​ 層建立臨時表
• 前者是​

  ​Online DDL​

  ​​ ，而後者就是​

  ​DDL​

  ​

• Online 的含義就是在操作時不會阻塞對原表的增删改功能
• DDL 過程如果是 Online 的，就一定是 inplace 的
• 反過來未必，也就是說 inplace 的 DDL，有可能不是 Online 的

• 截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空間索引 (SPATIAL index) 就屬于這種情況。

• ​

  ​optimize table​

  ​：重建表
• ​

  ​analyze table​

  ​：重新對表的索引資訊統計，沒有修改資料，這個過程中加了 MDL 讀鎖
• ​

  ​optimize table​

  ​：optimize table + analyze table