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会释放， 看起来这个会重建表的，

转载请注明源出处

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-1650835/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-1650835/