MySQL中delete和truncate表后，表空间未释放？

昨天，因公司业务上的需求，需要清空一张有 2 千 8 百多万条数据的表，当然，清空之前得有备份啦。一番操作之后，发现耗时太长，delete 好几次都因为时间太长而断开了操作，后来我就用 truncate 命令进行清空操作，今早一过来查看，还以为也断连了，后来 count 了一下，发现只有 7 条数据，也就是说，表昨天就已经清空了，本该庆幸的时候，可我又发现了有些不正常的现象，7 条数据啊，怎么 ccount 出来需要 25 秒钟？然后去从机上看了下，也需要 32 秒。这就不科学了啊。然后继续摸索，直接 select *之后，发现查询 7 条数据用了 8min 多。这肯定有问题啦，于是去服务器上数据空间目录看下了，这个表还有 24G，空间一点没有释放掉。也就是说数据是假清了？

用以下命令可以直观的看出某个表的状态，其中 Engine 是 InnoDB 还是 MyISAM，Rows 是表示表里面有多少条，Data_length 是表的大小，Index_length 是表的索引大小。这可以直观的反映出你的表空间是否释放了！

mysql>show table status like'sms_record';

+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

|Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time|Update_time|Check_time|Collation|Checksum|Create_options|Comment|

+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

|sms_record|InnoDB|10|Compact|8|2048|16384||131072||NULL|2018-08-1409:06:36|NULL|NULL|utf8_general_ci|NULL|||

+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

1rowinset(0.00sec)

以下是知识点整理：

truncate table tablename;

该命令可以清空一个表里的所有数据，并归 1 自增 ID 的值。但 myisam 的表和 innodb 的表在使用上有一定的区别。

myisam 表会清空所有数据，并释放表空间，即硬盘空间会得到释放。

innodb 表也会清空所有数据，但不释放表空间。

Innodb 数据库对于已经删除的数据只是标记为删除，并不真正释放所占用的磁盘空间，这就导致 InnoDB 数据库文件不断增长。如果想彻底释放这些已经删除的数据，需要把数据库导出，删除 InnoDB 数据库文件，然后再倒入。 这种方法当然不行啦，生产的数据库不能顺便启停。所以我们也可以用 optimize 方式来情况表空间。optimize table tablename;

举一反三：

1、创建数据库的时候设置 innodb_file_per_table，这样 InnoDB 会对每个表创建一个数据文件，然后只需要运行 OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。

编辑 my.ini 或 my.cnf 在 innodb 段中加入 innodb_file_per_table=1 # 1 为启用，0 为禁用

通过 mysql 语句可以查看该变量的值：mysql> show variables like ‘%per_table%’;为 on 就是表示打开。

2、后来也查了一下 delete 的用法上会不会造成这样的后果，发现 delete 也会有这种情况存在，但是分为两个条件

（1）delete from table_name where 条件删除数据后，数据表占用的空间大小不会变。

（2）不跟条件直接 delete 的时候。如：delete from table_name 清除了数据，同时数据表的空间也会变为 0。

这是因为删除操作后在数据文件中留下碎片所致。DELETE 只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间。

3、在 OPTIMIZE TABLE 运行过程中，MySQL 会锁定表。因此，这个操作一定要在业务低峰的时间段进行。

经过上述分析后，一波 optimize 操作之后，我的那张表又恢复了神速了，8 条数据秒查，空间也释放掉了！