mysql中如何優化表釋放表空間

2023-02-22 06:01:02

mysql優化表釋放表空間

方法一：optimize table table_name

這是我們經常見到的方法，這裡就不在過多介紹。

方法二：alter table table_name engine=engine_name

該方法通常用於切換表的引擎，例如MyISAM轉為InnoDB，但是同樣適用於釋放表空間，只不過切換後的引擎和原來的engine相同罷了。

注意事項

1.這兩種方式都適用於我們常用的表，myisam和innodb。

2.優化表的時候會鎖表，資料表越大，耗時越長，因此不要在網站忙時進行表優化。

mysql空間釋放

今天還原了一個資料庫。發現資料量有數十GB，想著看看哪些表的資料量多，於是查詢了下。資料還真不少。3000多萬行，這兒一張表居然有近1400萬資料。....慚愧。以前沒做什麼處理。

USE information_schema; # 使用資料庫後設資料;
SELECT table_name,table_rows FROM TABLES WHERE table_schema = 'tjpro' order by table_rows desc; #查詢表名和錶行數，資料庫為tjpro 
SELECT sum(table_rows) FROM tables WHERE table_schema = 'tjpro'; #統計行數
SELECT table_name,table_rows FROM tables WHERE table_schema = 'tjpro' order by table_rows desc limit 100; #查詢最多行數的表前100

使用delete刪除mysql資料的時候，mysql並沒有把資料檔案刪除，而是將資料檔案的標識位刪除，沒有整理檔案，因此不會徹底釋放空間。被刪除的資料將會被儲存在一個連結清單中，當有新資料寫入的時候，mysql會利用這些已刪除的空間再寫入。即，刪除操作會帶來一些資料碎片，正是這些碎片在佔用硬碟空間。 -- 引自mysql

如何整理，清理這些空間呢，請往下看。

查詢佔用空間大小

這時候，我刪除了資料最多的mis_system_data_remind_mas 表(測試庫喲)之後。我再次查詢該表佔用的空間大小。

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename（你的表名）';

預設是M為單位。第二欄。約為2.8個GB大小。

然後清理一下。使用：OPTIMIZE TABLE

清理碎片

官方推薦使用 OPTIMIZE TABLE 命令來優化表，該命令會重新利用未使用的空間，並整理資料檔案的碎片。

等待的時間有點長。等完成後，再次查詢空間：

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename（你的表名）';

可以看到，釋放的空間約為2.8GB。

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

mysql中如何優化表釋放表空間

目錄

mysql優化表釋放表空間

mysql空間釋放

查詢佔用空間大小

清理碎片

總結

熱門文章