mysql表空間文件過大,mysql臨時文件太大

本文目錄一覽：

• 1、如何壓縮Mysql數據庫
• 2、怎麼修改mysql數據庫臨時表空間大小
• 3、MySQL可以通過配置限制表空間的大小嗎?
• 4、linux怎麼修改mysql數據庫臨時表空間大小

如何壓縮Mysql數據庫

壓縮表從名字上來看，簡單理解為壓縮後的表，也就是把原始表根據一定的壓縮算法按照一定的壓縮比率壓縮後生成的表。

1.1 壓縮能力強的產品

表壓縮後從磁盤佔用上看要比原始表要小很多。如果你熟悉列式數據庫，那對這個概念一定不陌生。比如，基於 PostgreSQL 的列式數據庫 Greenplum；早期基於 MySQL 的列式數據庫 inforbright；或者 Percona 的產品 tokudb 等，都是有壓縮能力非常強的數據庫產品。

1.2 為什麼要用壓縮表？

情景一：磁盤大小為 1T，不算其他的空間佔用，只能存放 10 張 100G 大小的表。如果這些表以一定的比率壓縮後，比如每張表從 100G 壓縮到 10G，那同樣的磁盤可以存放 100 張表，表的容量是原來的 10 倍。情景二：默認 MySQL 頁大小 16K，而 OS 文件系統一般塊大小為 4K，所以在 MySQL 在刷臟頁的過程中，有一定的概率出現頁沒寫全而導致數據壞掉的情形。比如 16K 的頁寫了 12K，剩下 4K 沒寫成功，導致 MySQL 頁數據損壞。這個時候就算通過 Redo Log 也恢復不了，因為幾乎有所有的關係數據庫採用的 Redo Log 都記錄了數據頁的偏移量，此時就算通過 Redo Log 恢復後，數據也是錯誤的。所以 MySQL 在刷臟數據之前，會把這部分數據先寫入共享表空間里的 DOUBLE WRITE BUFFER 區域來避免這種異常。此時如果 MySQL 採用壓縮表，並且每張表頁大小和磁盤塊大小一致，比如也是 4K，那 DOUBLE WRITE BUFFER 就可以不需要，這部分開銷就可以規避掉了。查看文件系統的塊大小：

root@ytt-pc:/home/ytt#  tune2fs -l /dev/mapper/ytt–pc–vg-root  | grep -i ‘block size’Block size:               4096

1.3 壓縮表的優勢

壓縮表的優點非常明顯，佔用磁盤空間小！由於佔用空間小，從磁盤置換到內存以及之後經過網絡傳輸都非常節省資源。

簡單來講：節省磁盤 IO，減少網絡 IO。

1.4 壓縮表的缺陷

當然壓縮表也有缺點，壓縮表的寫入（INSERT,UPDATE,DELETE）比普通表要消耗更多的 CPU 資源。

壓縮表的寫入涉及到解壓數據，更新數據，再壓縮數據，比普通表多了解壓和再壓縮兩個步驟，壓縮和解壓縮需要消耗一定的 CPU 資源。所以需要選擇一個比較優化的壓縮算法。

1.5 MySQL 支持的壓縮算法

這塊是 MySQL 所有涉及到壓縮的基礎，不僅僅用於壓縮表，也用於其它地方。比如客戶端請求到 MySQL 服務端的數據壓縮；主從之間的壓縮傳輸；利用克隆插件來複制數據庫操作的壓縮傳輸等等。

從下面結果可以看到 MySQL 支持的壓縮算法為 zlib 和 zstd，MySQL 默認壓縮算法為 zlib，當然你也可以選擇非 zlib 算法，比如 zstd。至於哪種壓縮算法最優，暫時沒辦法簡單量化，依賴表中的數據分佈或者業務請求。

怎麼修改mysql數據庫臨時表空間大小

以MySQL 8.0 來說，通過查看 8.0 的官方文檔得知，8.0 的臨時表空間分為會話臨時表空間和全局臨時表空間，會話臨時表空間存儲用戶創建的臨時表和當 InnoDB 配置為磁盤內部臨時表的存儲引擎時由優化器創建的內部臨時表，當會話斷開連接時，其臨時表空間將被截斷並釋放回池中；也就是說，在 8.0 中有一個專門的會話臨時表空間，當會話被殺掉後，可以回收磁盤空間；而原來的 ibtmp1 是現在的全局臨時表空間，存放的是對用戶創建的臨時表進行更改的回滾段，在 5.7 中 ibtmp1 存放的是用戶創建的臨時表和磁盤內部臨時表；

也就是在 8.0 和 5.7 中 ibtmp1 的用途發生了變化，5.7 版本臨時表的數據存放在 ibtmp1 中，在 8.0 版本中臨時表的數據存放在會話臨時表空間，如果臨時表發生更改，更改的 undo 數據存放在 ibtmp1 中；

實驗驗證：將之前的查詢結果保存成臨時表，對應會話是 45 號，通過查看對應字典表，可知 45 號會話使用了 temp_8.ibt 這個表空間，通過把查詢保存成臨時表，可以用到會話臨時表空間，如下圖：

下一步殺掉 45 號會話，發現 temp_8.ibt 空間釋放了，變為了初始大小，狀態為非活動的，證明在 mysql8.0 中可以通過殺掉會話來釋放臨時表空間。

總結：在 mysql5.7 時，殺掉會話，臨時表會釋放，但是僅僅是在 ibtmp 文件里標記一下，空間是不會釋放回操作系統的。如果要釋放空間，需要重啟數據庫；在 mysql8.0 中可以通過殺掉會話來釋放臨時表空間。

MySQL可以通過配置限制表空間的大小嗎?

innodb的可以用以下方式設置

[mysqld]

innodb_data_file_path = ts #用來容納InnoDB數據表的表空間：可能涉及一個以上的文件；每一個表空間文件的最大長度都必須以B,MB,GB為單位給出；表空間文件的名字必須以分號隔開；最後一個表空間文件還以帶有一個autoextend屬性和一個最大長度(max:n)。如：ibdata1:1G;ibdata2:1G:autoextend:max:2G。默認設置是ibdata1:10M:autoextend

innodb_autoextend_increment = n #帶有autoextend屬性的表空間文件每次加大多少兆位元組(默認是8MB)，該屬性不涉及具體的數據表文件，那些文件的增大速度相對是比較小的

示例：

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

myisam:

與系統有關

linux怎麼修改mysql數據庫臨時表空間大小

先來說說臨時表的概念。 臨時表顧名思義，就是臨時的，用完銷毀掉的表。 數據既可以保存在臨時的文件系統上，也可以保存在固定的磁盤文件系統上。 臨時表有下面幾種：

1全局臨時表

這種臨時表從數據庫實例啟動後開始生效，在數據庫實例銷毀後失效。在MySQL裏面這種臨時表對應的是內存表，即memory引擎。

2會話級別臨時表

這種臨時表在用戶登錄系統成功後生效，在用戶退出時失效。在MySQL里的臨時表指的就是以 create temporary table 這樣的關鍵詞創建的表。

3事務級別臨時表

這種臨時表在事務開始時生效，事務提交或者回滾後失效。 在MySQL裏面沒有這種臨時表，必須利用會話級別的臨時表間接實現。

4檢索級別臨時表

這種臨時表在SQL語句執行之間產生，執行完畢後失效。 在MySQL裏面這種臨時表不是很固定，跟隨MySQL默認存儲引擎來變化。比如默認存儲引擎是MyISAM,臨時表的引擎就是MyISAM,並且文件生成形式以及數據運作形式和MyISAM一樣，只是數據保存在內存里；如果默認引擎是INNODB，那麼臨時表的引擎就是INNODB，此時它的所有信息都保存在共享表空間ibdata裏面。

MySQL 5.7對於InnoDB存儲引擎的臨時表空間做了優化。在MySQL 5.7之前，INNODB引擎的臨時表都保存在ibdata裏面，而ibdata的貪婪式磁盤佔用導致臨時表的創建與刪除對其他正常表產生非常大的性能影響。在MySQL5.7中，對於臨時表做了下面兩個重要方面的優化：

MySQL5.7 把臨時表的數據以及回滾信息（僅限於未壓縮表）從共享表空間裏面剝離出來，形成自己單獨的表空間，參數為innodb_temp_data_file_path。

在MySQL5.7 中把臨時表的相關檢索信息保存在系統信息表中：information_schema.innodb_temp_table_info. 而MySQL 5.7之前的版本想要查看臨時表的系統信息是沒有太好的辦法。

需要注意的一點就是，雖然INNODB臨時表有自己的表空間，但是目前還不能自己定義臨時表空間文件的保存路徑，只能是繼承innodb_data_home_dir。此時如果想要拿其他的磁盤，比如內存盤來充當臨時表空間的保存地址，只能用老辦法，做軟鏈。舉個小例子：

我現在用的OS是 Ubuntu12.X，想用tmpfs文件系統充當臨時表空間，

root@ytt-master-VirtualBox:/usr/local/mysql/data# ln -s/run/shm/ /usr/local/mysql/data/tmp_space2

root@ytt-master-VirtualBox:/usr/local/mysql/data#ls -l | grep ‘shm’

lrwxrwxrwx1 root root 9 Nov 13 10:28tmp_space2 – /run/shm/

然後把

innodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextend

添加到my.cnf里的[mysqld]下面一行

重啟MySQL服務後，

mysqlselect @@innodb_temp_data_file_path\G

***************************1. row ***************************

@@innodb_temp_data_file_path:tmp_space2/ibtmp2:200M:autoextend

1 rowin set (0.00 sec)

先寫一個批量創建臨時表的存儲過程：

DELIMITER$$

USE`t_girl`$$

DROPPROCEDURE IF EXISTS `sp_create_temporary_table`$$

CREATEDEFINER=`root`@`localhost` PROCEDURE `sp_create_temporary_table`(

IN f_cnt INT UNSIGNED )

BEGIN

DECLARE i INT UNSIGNED DEFAULT 1;

WHILE i = f_cnt

DO

SET @stmt = CONCAT(‘create temporarytable tmp’,i,’ ( id int, tmp_desc varchar(60));’);

PREPARE s1 FROM @stmt;

EXECUTE s1;

SET i = i + 1;

END WHILE;

DROP PREPARE s1;

END$$

DELIMITER;

現在來創建10張臨時表：

mysqlcall sp_create_temporary_table(10);

QueryOK, 0 rows affected (0.07 sec)

如果在以前，我們只知道創建了10張臨時表，但是只能憑記憶或者手工記錄下來臨時表的名字等信息。

現在可以直接從數據字典裏面檢索相關數據。

mysql select * frominformation_schema.innodb_temp_table_info;

+———-+————–+——–+——-+———————-+—————+

|TABLE_ID | NAME | N_COLS | SPACE| PER_TABLE_TABLESPACE | IS_COMPRESSED |

+———-+————–+——–+——-+———————-+—————+

| 56 | #sql1705_2_9 | 5 | 36 | FALSE | FALSE |

| 55 | #sql1705_2_8 | 5 | 36 | FALSE |FALSE |

| 54 | #sql1705_2_7 | 5 | 36 | FALSE | FALSE |

| 53 | #sql1705_2_6 | 5 | 36 | FALSE | FALSE |

| 52 | #sql1705_2_5 | 5 | 36 | FALSE |FALSE |

| 51 | #sql1705_2_4 | 5 | 36 | FALSE | FALSE |

| 50 | #sql1705_2_3 | 5 | 36 | FALSE | FALSE |

| 49 | #sql1705_2_2 | 5 | 36 | FALSE |FALSE |

| 48 | #sql1705_2_1 | 5 | 36 | FALSE | FALSE |

| 47 | #sql1705_2_0 | 5 | 36 | FALSE | FALSE |

+———-+————–+——–+——-+———————-+—————+

10rows in set (0.00 sec)

功能性我就寫到這裡，大家性能方面如果有興趣可以找時間去測試。