mysql⼤批量删除存储_关于删除mysql⼤表数据并释放存储空
间的两种⽅式
下⾯我们来做⼀个⼩实验来测试这两种删数据的⽅式
⾸先建⼀张名为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：
导⼊⼀些测试数据后再看：
mysql中delete语句
⽅式⼀：使⽤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
|
+---------------+----------+----------+-----------------------------------------
--------------------------+
| st_log | optimize | note | Table does not support optimize, doing r
ecreate + analyze instead |
| st_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删除半年前的个⽇志表的分区数据：
BEGIN
DECLARE 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 for
SELECT t.drop_sql
FROM 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 THEN
LEAVE 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每⽉执⾏这个存储过程来删除⽇志表的分区数据。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。