mysql分割槽表佔用大量容量處理（最佳化）及歸檔分割槽表

海yo發表於2024-11-08
MySql

背景

生產環境中，某個分割槽表兩三年了，佔用磁碟1T多，需要對前幾年的資料做歸檔

點選檢視程式碼 
SELECT
  table_schema as '資料庫',
  table_name as '表名',
  table_rows as '記錄數',
  truncate(data_length/1024/1024, 2) as '資料容量(MB)',
  truncate(index_length/1024/1024, 2) as '索引容量(MB)',
  truncate(DATA_FREE/1024/1024, 2) as '碎片佔用(MB)'
from 
  information_schema.tables
where 
  table_schema='庫名'and table_name='表名'
order by 
  data_length desc, index_length desc;

分析

碎片佔用大量的容量，很多分割槽表都沒資料

一種可能是統計資訊沒更新。

TABLE_ROWS 列顯示的是表或分割槽的行數估計值，而不是實際行數。這個估計值可能不準確，尤其是在表資料頻繁變化的情況下。該表從生產上線之後就沒有最佳化。可能就存在該問題。

一種可能是資料頁未釋放。

即使分割槽中的資料已經被刪除，資料頁可能還沒有被完全釋放，因此 資料容量 和 索引容量 仍然顯示佔用空間。

解決方法

透過ANALYZE TABLE xxxx,來分析和最佳化表

該命令主要用於收集表的統計資訊，這些資訊對於查詢最佳化器來說非常重要，因為最佳化器會根據這些統計資訊來選擇最合適的執行計劃。
它具體完成了以下任務：
1.更新索引統計資訊：這個命令會掃描表中的資料，並重新計算索引的分佈情況，如鍵值的分佈等。這對於最佳化查詢效能非常有幫助，因為查詢最佳化器可以根據最新的統計資訊來做出更好的決策。
2.檢查並修復某些型別的表損壞：雖然 ANALYZE TABLE 並不是一個專門用來修復損壞表的工具，但它可以在一定程度上幫助識別表中可能存在的問題。如果表確實存在一些輕微的問題，該命令可能會嘗試解決這些問題。
3.輸出表的狀態資訊：執行 ANALYZE TABLE 後，MySQL 會返回一個結果集，其中包含關於表的狀態資訊，例如是否成功進行了分析、是否有錯誤發生等。

透過OPTIMIZE TABLE xxxx，來最佳化表

用於對錶進行最佳化，主要目的是透過重組表的資料檔案和索引檔案來提高表的效能。當表經歷了大量的插入、刪除或更新操作後，資料檔案和索引檔案可能會變得碎片化，這會影響查詢效率。OPTIMIZE TABLE 命令可以幫助減少這種碎片化，回收未使用的空間，重新組織資料和索引，從而提升表的讀寫效能。
它具體完成了以下任務：
1.回收空間：當表中的行被刪除時，這些行佔用的空間並不會立即歸還給作業系統，而是被標記為可用空間供未來的插入操作使用。OPTIMIZE TABLE 可以回收這些未使用的空間，並將它們歸還給作業系統。
2.重組資料檔案：隨著表的使用，資料檔案可能會變得碎片化。OPTIMIZE TABLE 會建立一個新的資料檔案，將舊的資料檔案中的資料按順序寫入新檔案中，然後刪除舊的資料檔案。這個過程有助於減少資料檔案的碎片化，提高磁碟的讀取效率。
3.重組索引檔案：與資料檔案類似，索引檔案也可能變得碎片化。OPTIMIZE TABLE 還會對索引檔案進行重組，確保索引樹更加平衡，從而加快索引查詢的速度。
4.更新統計資訊：在某些儲存引擎中，OPTIMIZE TABLE 也會更新儲存引擎內部使用的統計資訊，這有助於查詢最佳化器更好地選擇執行計劃。

排查

mysql> SELECT COUNT(*) AS actual_rows FROM 表名 PARTITION (p_2022_11_04);
mysql> SELECT * FROM 表名 PARTITION (p_2022_11_04) LIMIT 10;

透過檢查實際行數和資料，可以看出該分割槽確實沒有資料。

注意：

ANALYZE TABLE可能會導致短暫的表鎖定，尤其是在高併發環境下。且ANALYZE TABLE和OPTIMIZE TABLE分析和最佳化大表可能消耗較多的CPU和I/O資源。
建議如下：
1.選擇低峰時段：儘量在業務低峰時段執行這些操作，以減少對生產系統的影響。
2.監控系統資源：在執行這些操作時，監控系統的CPU、記憶體和I/O使用情況，確保系統資源充足。
3.測試環境：如果可能，先在測試環境中執行這些操作，評估其影響。
4.分批次處理：對於非常大的表，可以考慮分批次進行最佳化和分析。

處理：

分割槽容量

點選檢視程式碼 
SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    TABLE_ROWS, 
    DATA_LENGTH, 
    INDEX_LENGTH, 
    (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE,
    DATA_LENGTH / 1073741824.0 AS DATA_SIZE_GB,
    INDEX_LENGTH / 1073741824.0 AS INDEX_SIZE_GB,
    (DATA_LENGTH + INDEX_LENGTH) / 1073741824.0 AS TOTAL_SIZE_GB
FROM 
    INFORMATION_SCHEMA.PARTITIONS 
WHERE 
    TABLE_SCHEMA = '庫名' 
    AND TABLE_NAME = '表名';

低峰期最佳化

檢查最佳化後分割槽表各分割槽大小

檢查最佳化後表的總大小

刪除沒有資料的分割槽

ALTER TABLE 表名 DROP PARTITION p_2023_01_26;

自此最佳化結束，一下子釋放了快1.9個T的容量，大快人心！！！

備份思路

如果有資料需要歸檔備份，備份思路如下：
建立一個臨時表，再往裡面插入特定分割槽的資料，再透過mysqldump匯出歸檔

表結構（脫敏）

show create table abc
| abc | CREATE TABLE `abc` (
  `APP_ID` varchar(32) DEFAULT NULL COMMENT 'app編號',
  `ABC_CODE` varchar(512) DEFAULT NULL COMMENT '二維碼號',
  `CARD_ID` varchar(64) DEFAULT NULL COMMENT '卡ID',
  `CODE_TYPE` varchar(32) DEFAULT NULL COMMENT '二維碼型別',
  `CRT_DATE` varchar(32) NOT NULL DEFAULT '0' COMMENT '日期',
  `CRT_TIME` varchar(32) DEFAULT NULL COMMENT '時間',
.......
  PRIMARY KEY (`CRT_DATE`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(CRT_DATE)
(PARTITION p_2022_11_04 VALUES LESS THAN ('20221105') ENGINE = InnoDB,
 PARTITION p_2022_11_05 VALUES LESS THAN ('20221106') ENGINE = InnoDB,
 PARTITION p_2022_11_06 VALUES LESS THAN ('20221107') ENGINE = InnoDB,
 PARTITION p_2022_11_07 VALUES LESS THAN ('20221108') ENGINE = InnoDB,
 PARTITION p_2022_11_08 VALUES LESS THAN ('20221109') ENGINE = InnoDB,
 PARTITION p_2022_11_09 VALUES LESS THAN ('20221110') ENGINE = InnoDB,
.......

備份方案1：

建立臨時表

CREATE TABLE abc_2022_11_04 LIKE abc;

插入特定分割槽資料

INSERT INTO abc_2022_11_04 SELECT * FROM abc PARTITION (p_2022_11_04);

透過mysqldump匯出來

mysqldump -u your_username -p 庫名 abc_2022_11_04 > abc_p_2022_11_04.sql

刪除臨時表

DROP TABLE abc_2022_11_04

備份方案2：

mysqldump -u root -p 庫名 表名 --where="CRT_DATE < '20221105'" > abc_p_2022_11_04.sql

注意：如果表中有大量資料，建議在低峰時段進行備份，以減少對生產環境的影響。

相關文章