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%';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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字段
该操作通过多次单表操作，多次对表进行备份和恢复，从而释放了碎片空间，适用于不允许停机的业务。
如果系统业务允许在一段时间内停止服务，可以备份整个数据库，然后清空，重建mysql数据根目录，然后恢复整个数据库。
思路2 ：
删除部分旧数据：适用于可以删除部分旧数据的场景
create
table newtb like oldtb if no exist;-->新建newtb表，表结构同旧表
insert
into newtb select * from oldtb where time > 'xxx' and
time<='xxx';
-->备份表的新数据，可以按时间段备份最近一个月或半个月
drop
table oldtb;
-->drop旧表释放表空间
alter
tables newtb rename to oldtb;
-->重命名新表“newdtb”
思路3：直接优化表：optimize
table 表名;
select
table_name,data_free,engine from information_schema.tables where
table_schema='mydb';
-->查看库中各表data_free值，单位是字节，data_free/1024/1024/1024即为理论上释放后可以恢复空间G。
show
table status like 'oldtb';-->单个表的data_free大小
mysql>
OPTIMIZE TABLE
hellodb.students;
-->显示不支持，实际上已进行重建和分析，空间已经回收
+------------------+----------+----------+-------------------------------------------------------------------+
| Table
| Op
| Msg_type | Msg_text
|
+------------------+----------+----------+-------------------------------------------------------------------+
| hellodb.students | optimize | note
| Table does not support optimize, doing recreate + analyze instead
|
| hellodb.students | optimize | status |
OK
|
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.08 sec)