Mysql在生产环境中快速清理数据及表空间释放

Dear 丶 2022-05-19 13:24 363阅读 0赞

**Mysql****数据快速清理及表空间释放**

**1****、TABLES****表主要字段说明：**

MySQL的 information\_schema 数据库中的TABLES 表记录了MySQL数据库中每个表占用的空间、表记录的行数，更新时间，说明等，这个表主要字段如下：

**TABLE\_SCHEMA :** **数据库名**

**TABLE\_NAME****：表名**

**ENGINE****：所使用的存储引擎**

**TABLES\_ROWS****：记录数，即表的行数**

**DATA\_LENGTH****：数据大小**

**INDEX\_LENGTH****：索引大小**

**CREATE\_TIME****：创建时间**

**UPDATE\_TIME****：最近更新时间**

**DATA\_FREE****：该参数与mysql****碎片有关，如果是共享表空间，该字段表示共享表空间的大小而非数据的大小。只有使用独占表空间时，该字段才表示该表的剩余空间；**

说明：当MySQL从列表中删除一行内容，该段空间就会被留空。在一段时间内执行大量删除操作后，往往会使碎片空间变得比存储列表内容所使用的空间更大。

通俗的讲：Data\_free字段即为多占的物理空间，通过‘show table status’可以查看指定表的Data\_free字段，对应的值就是多占用的物理空间，drop表重建或是重新导入可以释放这部分空间。

mysql> use hellodb

mysql> show table status like 'students%';

\+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| students  | InnoDB |      10 | Compact    |   27 |            606 |       16384 |               0 |        32768 |         0 |             28 | 2017-11-28 15:31:15 | NULL        | NULL       | utf8\_general\_ci |     NULL |                |         |

| students1 | InnoDB |      10 | Compact    |    8 |           2048 |       16384 |               0 |        32768 |         0 |              9 | 2018-06-11 20:16:53 | NULL        | NULL       | utf8\_general\_ci |     NULL |                |         |

\+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------

此时可以使用optimize整理表的碎片：

注意：该操作执行的时候会把该表格先写入一个tmp临时表，所以磁盘剩余空间必须大于表空间，否则会执行失败。

mysql> optimize table classes,students;

5.6.X以前的版本会提示该表不支持optimize，5.6.X的版本已经支持Innodb了。

**2****、生产****案例：释放表空间**

**思路1****：**

**数据不删除：备份表，drop****表，导入备份的表到数据库；**

show table status like 'oldtb%';  -->重点关注Data\_free字段，对应的值就是多占用的物理空间，即碎片空间

mysqldump -uroot -p123456  mydb oldtb >/mnt/oldtb.sql  \-->导出可以恢复系统空间的表格。

drop table oldtb;                        \-->drop旧表释放表空间

source /mnt/oldtb.sql;                -\->导出备份的表到数据库。

show table status like 'oldtb%';  -->对比恢复前后的状态，重点关注Data\_free字段