释放文件高水位:
试试 delete 和 truncate是否会释放文件高水位
试试 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/