MySQL中delete和truncate表后,表空间未释放?

Mysql IT敢客 4个月前 (08-14) 1726次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

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

MySQL 中 delete 和 truncate 表后,表空间未释放?

       用以下命令可以直观的看出某个表的状态,其中 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 |               0 |       131072 |         0 |           NULL | 2018-08-14 09:06:36 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

       以下是知识点整理:

       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 条数据秒查 :wink: ,空间也释放掉了!


       以上内容补充一些知识点:

独立表空间参数 innodb_file_per_table 详解

参数列表:

Command-Line Format –innodb_file_per_table  
System Variable Name innodb_file_per_table
  Variable Scope Global
  Dynamic Variable Yes
Permitted Values (<= 5.6.5) Type boolean
  Default OFF
Permitted Values (>= 5.6.6) Type boolean
  Default ON

innodb_file_per_table 参数为 on 或者在 my.cnf 中为 1 都表示开启独立表空间,在 mysql5.6.6 之后是默认开启的,在 mysql5.6.6 之前是默认关闭的!

二者优缺点

共享表空间:

优点:可以将表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。数据和文件放在一起方便管理。
缺点:所有的数据和索引存放到一个文件中,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。
独立表空间:
在配置文件(my.cnf)中设置: innodb_file_per_table = 1
优点:
1.每个表都有自已独立的表空间。
2.每个表的数据和索引都会存在自已的表空间中。
3.可以实现单表在不同的数据库中移动。
4.空间可以回收(除 drop table 操作处,表空不能自已回收)
a.Drop table 操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b.对于使 innodb-plugin 的 Innodb 使用 turncate table 也会使空间收缩。
c.对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
缺点:
1.单表增加过大,如超过 100 个 G。
2.相比较之下,使用独占表空间的效率以及性能会更高一点。


IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL 中 delete 和 truncate 表后,表空间未释放?
喜欢 (6)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址