切換語言為:簡體

MySQL 回收表碎片實踐教程

  • 爱糖宝
  • 2024-10-23
  • 2050
  • 0
  • 0

前言:

在 MySQL 資料庫中,隨著資料的增刪改操作,表空間可能會出現碎片化,這不僅會佔用額外的儲存空間,還可能降低表的掃描效率,特別是一些大表,在進行資料清理後會產生大量的碎片。本篇文章我們一起來學習下如何進行碎片回收以及相關注意點。

檢視表碎片大小

一般 MySQL 資料庫都是開啟 innodb_file_per_table 引數的,這代表每個表使用獨立的表空間,即每個表的資料及索引儲存在一個獨立的 表名.ibd 檔案裡,如果某個表有大量碎片,ibd 檔案佔用磁碟空間會非常大,碎片回收掉後 ibd 檔案也會顯著減小。

首先我們要確定哪些表需要進行回收碎片操作,MySQL 系統表 information.TABLES 中的 DATA_FREE 欄位顯示的是可用的空閒空間量(單位:位元組),它可以幫助你估計碎片的程度,如果 DATA_FREE 很大,那麼這個表的碎片量一般也比較大。

MySQL 回收表碎片實踐教程

如果某個表比較大或者變動特別頻繁,你可以看下這個表的 DATA_FREE 大小,看是否需要回收碎片,也可以從系統表中篩選出碎片量大於 100M 的表或者碎片率達到多少的表,這類表一般是需要進行碎片回收的。下面幾條查詢 SQL 可能對你有所幫助:

# 檢視某個表的詳細資訊(包含碎片大小)
select 
table_schema as '資料庫',
TABLE_NAME as '表名',
sys.FORMAT_BYTES(data_length) as '資料容量',
sys.FORMAT_BYTES(index_length) as '索引容量',
sys.FORMAT_BYTES(data_length+index_length) as '總容量' ,
sys.FORMAT_BYTES(DATA_FREE) as '碎片大小'
from information_schema.tables where TABLE_SCHEMA = 'db_name' and TABLE_NAME = 'tb_name';

# 按碎片大小排序
SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.DATA_FREE,
       sys.FORMAT_BYTES(DATA_LENGTH+INDEX_LENGTH) as '總容量' ,
       sys.FORMAT_BYTES(DATA_FREE) as '碎片大小'
FROM information_schema.tables t
WHERE
    t.table_schema NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    )
AND t.table_type = 'BASE TABLE' ORDER BY `DATA_FREE` DESC LIMIT 20

# 檢視碎片率大於0.3的表
select 
table_schema as '資料庫',
TABLE_NAME as '表名',
sys.FORMAT_BYTES(DATA_LENGTH+INDEX_LENGTH) as '總容量' ,
sys.FORMAT_BYTES(DATA_FREE) as '碎片大小',
(DATA_FREE / (data_length + index_length)) AS '碎片率'
FROM information_schema.tables t
WHERE
	t.table_schema NOT IN (
		'mysql',
		'information_schema',
		'performance_schema',
		'sys'
	) AND t.table_type = 'BASE TABLE' and (DATA_FREE / (data_length + index_length)) > 0.3

以上三條 SQL 基本能覆蓋日常所需場景,想要回收表碎片的話,可以按照不同場景執行相關 SQL 來查詢,比如是想回收碎片比較多的表還是碎片率比較大的表。找到需要回收碎片的表後,下一步就是評估進行正式回收碎片操作了。

回收表碎片

對於 InnoDB 儲存引擎的表,可以用 optimize table table_name; 或者 alter table table_name engine = innodb; 兩種方式進行回收。

OPTIMIZE TABLE 對於 InnoDB 表來說,實際上會執行一個重建表的操作,這與 ALTER TABLE ... FORCE 類似。這個過程會重新組織表的資料和索引,更新索引統計資訊,並釋放聚簇索引中未使用的空間。它可以在一定程度上減少表佔用的空間,並提高訪問表時的 IO 效率。OPTIMIZE TABLE 對於常規的和分割槽的 InnoDB 表使用 online DDL ,這減少了併發的 DML 操作的停機時間。OPTIMIZE TABLE 僅在操作的準備階段和提交階段短暫地獲取獨佔的表鎖,在準備階段,後設資料會被更新並且建立一箇中間表,在提交階段,將提交表後設資料更改。

ALTER TABLE ... ENGINE = InnoDB 命令實際上是將表的儲存引擎重新設定為 InnoDB 。在這個過程中,MySQL 會對錶進行重建,會回收掉未使用的空間。在 5.6 及以後的版本中,這個操作會使用 Online DDL ,減少對併發 DML 操作的影響。它透過建立一個臨時檔案,掃描表的資料頁,並將操作記錄在日誌檔案中,最後將臨時檔案替換原表的資料檔案。此方法只適用於 InnoDB 引擎表。

總的來說,兩者都可以用於整理 InnoDB 表的碎片,但是 OPTIMIZE TABLE 更側重於專門的碎片整理和空間回收,還可以用於其他儲存引擎。而 ALTER TABLE ... ENGINE=InnoDB 主要是更改儲存引擎屬性時附帶的一些空間最佳化。在實際使用中,可以根據具體情況選擇合適的方式來回收 InnoDB 表的空間。

需要注意的一點是,儘管二者操作都是 Online DDL ,但回收操作還是儘量在業務低峰期執行,特別是大表,回收操作還是需要一段時間的。除此之外,要確保有足夠的磁碟空間進行回收操作,因為執行期間會生成臨時檔案,進一步佔用磁碟空間,執行完成後纔會刪除臨時檔案。例如你要對一個 200G 的表進行回收操作,預估能回收掉 50G 碎片,則要確保磁碟空間至少剩餘 150G,一般建議剩餘空間在表大小以上。如果你的磁碟剩餘空間不足則無法完成回收操作。

總結:

本篇文章介紹瞭如何檢視 InnoDB 表的碎片以及如何進行回收。生產環境中,建議定期巡檢 MySQL 系統中的表碎片,並在業務低峰期執行回收操作。回收表碎片是一種良好的資料庫維護實踐,可以提高資料庫查詢效能,同時也可以提高儲存效率和管理簡便性。

0則評論

您的電子郵件等資訊不會被公開,以下所有項目均必填

OK! You can skip this field.