关于删除mysql大表数据并释放存储空间的两种方式

        删除表数据我们通常会使用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 r
ecreate + 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删除半年前的个日志表的分区数据：

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

每月执行这个存储过程来删除日志表的分区数据。