mysql釋放檔案高水位

釋放檔案高水位： 

試試 delete 和 truncate是否會釋放檔案高水位 

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.23-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table t3; 
Query OK, 0 rows affected (0.36 sec) 

mysql> create table t3(id int primary key ,name varchar(10)); 
Query OK, 0 rows affected (0.04 sec) 



mysql> 
mysql> 
mysql> DELIMITER // 
mysql> 
mysql> CREATE PROCEDURE insert_t3 (v_id INT) 
-> BEGIN 
-> DECLARE l_sql VARCHAR(500); 
-> declare a int; 
-> repeat 
-> SET l_sql=CONCAT('insert into t3 values (',v_id,',''name',v_id,''')'); 
-> SET @sql=l_sql; 
-> 
-> PREPARE stmt1 FROM @sql; 
-> EXECUTE stmt1 ; 
-> set v_id=v_id-1; 
-> until v_id=0 
-> end repeat; 
-> DEALLOCATE PREPARE stmt1; 
-> END; 
-> // 
Query OK, 0 rows affected (0.00 sec) 

mysql> delimiter ; 
mysql> call insert_t3(2000); 
Query OK, 0 rows affected (7.30 sec) 

mysql> 
[mysql@localhost test]$ ll | grep t3 
-rw-rw----. 1 mysql mysql 8586 May 12 15:26 t3.frm 
-rw-rw----. 1 mysql mysql 163840 May 12 15:27 t3.ibd 
mysql> delete from t3; 
Query OK, 2000 rows affected (0.35 sec) 

mysql> commit; 
Query OK, 0 rows affected (0.00 sec) 
[mysql@localhost test]$ ll | grep t3 
-rw-rw----. 1 mysql mysql 8586 May 12 15:26 t3.frm 
-rw-rw----. 1 mysql mysql 163840 May 12 15:28 t3.ibd 

mysql> truncate table t3; 
Query OK, 0 rows affected (0.04 sec) 

[mysql@localhost test]$ ll | grep t3 
-rw-rw----. 1 mysql mysql 8586 May 12 15:26 t3.frm 
-rw-rw----. 1 mysql mysql 98304 May 12 15:29 t3.ibd      --truncate的時候 .ibd檔案時間變了，但是.frm檔案的時間沒變

mysql> call insert_t3(2000); 
Query OK, 0 rows affected (7.56 sec) 

[mysql@localhost test]$ ll | grep t3 
-rw-rw----. 1 mysql mysql 8586 May 12 15:26 t3.frm 
-rw-rw----. 1 mysql mysql 163840 May 12 15:30 t3.ibd 

mysql> delete from t3; 
Query OK, 2000 rows affected (0.33 sec) 

mysql> alter table t3 engine=innodb; 
Query OK, 0 rows affected (0.10 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
[mysql@localhost test]$ ll | grep t3 
-rw-rw----. 1 mysql mysql 8586 May 12 15:31 t3.frm 
-rw-rw----. 1 mysql mysql 98304 May 12 15:31 t3.ibd    --時間都變了

總結： 
1 delete 不會釋放檔案高水位 
2 truncate會釋放 ，實際是把.ibd檔案刪掉了，再建一個。
3 delete + alter engine=innodb會釋放， 看起來這個會重建表的，

轉載請註明源出處