Mysql在生产环境中快速清理数据及表空间释放 |
您所在的位置:网站首页 › 单片机读sd卡文件 › Mysql在生产环境中快速清理数据及表空间释放 |
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备份表的新数据,可以按时间段备份最近一个月或半个月 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)
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |