关于删除mysql大表数据并释放存储空间的两种方式
来源:互联网 发布:淘宝妈妈装店铺推荐 编辑:程序博客网 时间:2024/06/03 23:48
删除表数据我们通常会使用delete语句来删除,但是执行完delete语句删除了表中部分数据后你会发现表对应的存储文件大小并不会缩小,如果要进一步释放被删除数据的空间需要执行optimize table tablename命令来进行优化(详情可看看这篇文章:http://blog.51yip.com/mysql/1222.html)。如果表的数据量非常巨大比如项目里一些日积月累不断变大的日志表,使用delete语句删除部分过期的日志数据可能需要执行很长时间,想要释放存储空间使用optimize优化也要优化很长时间,用truncate把整个表也不合适,这种情况下建表的时候可以将表建成分区表,删数据的时候直接删表分区。
下面我们来做一个小实验来测试这两种删数据的方式
首先建一张名为test_log以时间字段为分区的表:
CREATE TABLE `test_log` ( `product_id` varchar(20) DEFAULT NULL COMMENT '客户端的产品标识', `client_version` varchar(20) DEFAULT NULL COMMENT '客户端的版本号简称', `os_type` varchar(20) DEFAULT NULL COMMENT '用户手机操作系统的类型', `imsi` varchar(50) DEFAULT NULL COMMENT '用户手机的 IMSI 号', `insert_time` datetime DEFAULT NULL COMMENT '当前日志记录的入库时间') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试日志表'partition by range (to_days(insert_time))(PARTITION p0 VALUES LESS THAN (to_days('2016-01-01')),PARTITION p1 VALUES LESS THAN (to_days('2016-02-01')),PARTITION p2 VALUES LESS THAN (to_days('2016-03-01')),PARTITION p3 VALUES LESS THAN (to_days('2016-04-01')),PARTITION p4 VALUES LESS THAN (to_days('2016-05-01')),PARTITION p5 VALUES LESS THAN (to_days('2016-06-01')),PARTITION p6 VALUES LESS THAN (to_days('2016-07-01')),PARTITION p7 VALUES LESS THAN (to_days('2016-08-01')),PARTITION p8 VALUES LESS THAN (to_days('2016-09-01')),PARTITION p9 VALUES LESS THAN (to_days('2016-10-01')),PARTITION p10 VALUES LESS THAN (to_days('2016-11-01')),PARTITION p11 VALUES LESS THAN (to_days('2016-12-01')),PARTITION p12 VALUES LESS THAN MAXVALUE);
可以看到个表分区的文件都为默认的空表大小96kb:
导入一些测试数据后再看:
方式一:使用delete语句删除
删除p0分区对应的日期小于‘2016-01-01’的数据:
mysql> delete from test_log where insert_time < '2016-01-01';Query OK, 15360 rows affected (0.15 sec)再看看分区表文件,可以看到只更新了时间,但是存储大小没有变化:
执行optimize table test_log进行优化:
mysql> optimize table test_log;+---------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------+----------+----------+-------------------------------------------------------------------+| test.test_log | optimize | note | Table does not support optimize, doing recreate + analyze instead || test.test_log | optimize | status | OK |+---------------+----------+----------+-------------------------------------------------------------------+2 rows in set (2.14 sec)再看看分区表文件,发现p0分区的文件变回了默认大小96kb,空间已经释放:
需要特别注意的是,这种方式并不适合所有的表,optimize优化操作期间会造成锁表。
方式二:删除表分区来删除数据
执行ALTER TABLE test_log DROP PARTITION p1删除分区p1的数据:
mysql> ALTER TABLE test_log DROP PARTITION p1;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0看看表分区文件,发现p1的分区文件已经被删除了自然也就不存在释放存储空间的问题了:
一般日志表都需要定时删除,我们可以写一个存储过程来定时执行,如果有多张表,分区信息也相同的话,也可以放一个存储过程里一起删除:
先创建一张分区信息表dict_table_partion:
CREATE TABLE `dict_table_partion` ( `par_name` varchar(10) DEFAULT NULL, `par_date` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;导入分区信息数据:
再建一张存储删除分区语句的信息表drop_partion_sql:
CREATE TABLE `drop_partion_sql` ( `drop_sql` varchar(300) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
导入需要的删除的表分区语句:
新建一个存储过程pro_del_partion删除半年前的个日志表的分区数据:
BEGINDECLARE v_par_date date DEFAULT DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01') - INTERVAL + 6 + 1 MONTH;DECLARE done INT DEFAULT 0; DECLARE v_drop_sql varchar(1000); DECLARE cur_index_sql cursor forSELECT t.drop_sqlFROM drop_partion_sql t; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;SET @par_name = (SELECT par_name FROM dict_table_partion WHERE par_date = v_par_date);OPEN cur_index_sql; index_sql_loop:LOOP FETCH cur_index_sql INTO v_drop_sql; IF done=1 THENLEAVE index_sql_loop;END IF; SET @s = concat(v_drop_sql, ' ', @par_name);SELECT @s;PREPARE stmt FROM @s;EXECUTE stmt;DEALLOCATE PREPARE stmt;END LOOP index_sql_loop;END每月执行这个存储过程来删除日志表的分区数据。
- 关于删除mysql大表数据并释放存储空间的两种方式
- mysql中删除数据表数据的两种方式
- List删除数据的两种方式
- 大数据的两种处理方式
- Mysql 删除数据的两种方法
- 关于mysql 删除数据后物理空间未释放
- 关于mysql 删除数据后物理空间未释放
- 关于mysql 删除数据后物理空间未释放(转载)
- 关于mysql 删除数据后物理空间未释放
- 关于mysql 删除数据后物理空间未释放(转载)
- 关于mysql 删除数据后物理空间未释放(转载)
- oracle定时删除表空间的数据并释放表空间
- oracle定时删除表空间的数据并释放表空间
- MySQL中删除数据的两种方法
- MySQL删除数据后磁盘空间的释放情况
- Mysql删除数据后磁盘空间未释放的解决办法
- MySQL删除数据后磁盘空间的释放总结
- MySQL删除数据后磁盘空间的释放情况
- IOS运行时
- 字符串匹配算法--BF算法(暴力破解法)+KMP算法
- msm8953-dtsi的编译
- 浅谈大型网站架构技术进化
- iOS蓝牙开发CoreBluetooth快速入门
- 关于删除mysql大表数据并释放存储空间的两种方式
- Git 服务器搭建
- 自定义圆形加载控件,百分比加载--西域黄老板
- 泛型
- 要是老大放手让你来做、你敢吗?
- 学习杂物(四)c#学习笔记
- 【福利】前端开发用互联网API大全
- 安装oracle遇到的一些小问题
- Spring Data 系列之JPA