昨天,因公司业务上的需求,需要清空一张有 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 条数据秒查,空间也释放掉了!
领取专属 10元无门槛券
私享最新 技术干货