MySQL使用分区快速归档数据

对于我们中的许多人来说，空间限制一直是一个无尽而痛苦的过程，特别是在具有大量事务和几天内数据增长就能超过百GB的系统中。

在这篇文章中，我将分享一个解决方案，无论表的大小如何，使用表分区都可以在几秒钟内删除这个空间并从表中删除行，而不会对数据库造成任何额外的负载。

任何人想到的删除行的第一种方法是使用SQL中的DELETE查询。假设，一个人想要从一个表中删除超过一年的行，这种操作的查询将像这样:

?

1	DELETE FROM myabc WHERE from_date < DATE_SUB(NOW(),INTERVAL 1 YEAR);

上面的问题很简单，但有一些注意事项:

·服务器业务将呈指数级增长，并可能影响服务器上的常规流量。

·为了加快上面的查询，我们需要创建一个适当的索引，这样查询就可以在最短的时间内执行，并且对服务器性能的影响更小。

·如果我们使用binlog_format作为ROW，将创建大量的binlog日志，这可能会阻塞服务器的I/O，并需要额外的空间清理。

·即使在删除所有行之后，空间也不会被释放。MySQL不会收缩表空间，存储空间不会被释放给文件系统。为了将它释放到文件系统中，我们需要通过运行ANALYZE或ALTER重新创建表。

解决这个问题的一种方法是使用Percona pt-archiver。但是pt-archiver进程需要时间，因为它还要考虑系统负载、副本延迟和指定的参数来限制pt-archiver进程，而不影响正在进行的流量。

这里我建议使用MySQL分区，这是一种更快的方法。

非分区表：

?

1 2	# ls -lh myabc*#* -rw-r-----. 1 mysql mysql 104G Oct 11 05:47 myabc.ibd

分区表：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

# ls -lh myabc*#* -rw-r-----. 1 mysql mysql 9.0G Oct 11 05:47 myabc#P#p01.ibd -rw-r-----. 1 mysql mysql 9.0G Oct 11 05:47 myabc#P#p02.ibd -rw-r-----. 1 mysql mysql  10G Oct 11 05:47 myabc#P#p03.ibd -rw-r-----. 1 mysql mysql  11G Oct 11 05:47 myabc#P#p04.ibd -rw-r-----. 1 mysql mysql  11G Oct 11 05:47 myabc#P#p05.ibd -rw-r-----. 1 mysql mysql  12G Oct 11 05:47 myabc#P#p06.ibd -rw-r-----. 1 mysql mysql  12G Oct 11 05:47 myabc#P#p07.ibd -rw-r-----. 1 mysql mysql  13G Oct 11 05:47 myabc#P#p08.ibd -rw-r-----. 1 mysql mysql  14G Oct 11 05:47 myabc#P#p09.ibd -rw-r-----. 1 mysql mysql  14G Oct 11 05:47 myabc#P#p10.ibd -rw-r-----. 1 mysql mysql  15G Oct 11 05:47 myabc#P#p11.ibd -rw-r-----. 1 mysql mysql  15G Oct 11 05:47 myabc#P#p12.ibd -rw-r-----. 1 mysql mysql  16G Oct 11 05:47 myabc#P#p13.ibd -rw-r-----. 1 mysql mysql  16G Oct 11 05:47 myabc#P#p14.ibd -rw-r-----. 1 mysql mysql  17G Oct 11 05:47 myabc#P#p15.ibd -rw-r-----. 1 mysql mysql  17G Oct 11 05:47 myabc#P#p16.ibd -rw-r-----. 1 mysql mysql  16G Oct 11 05:47 myabc#P#p17.ibd -rw-r-----. 1 mysql mysql  13G Oct 11 05:47 myabc#P#p18.ibd -rw-r-----. 1 mysql mysql  96M Oct 11 05:45 myabc#P#p19.ibd

　

分区表是如何快速删除数据和释放空间的？

为了归档到分区表中的旧数据，我们将创建一个空表，它在结构上与原始表相同，但不像原始表那样有多个分区。创建了这个表后，我们将在几秒钟内完成将新创建的空表与原始表的一个分区交换。

在本例中，我们使用的是一个基于日期范围进行分区的表。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

mysql>show create table myabc\G *************************** 1. row ***************************        Table: myabc Create Table: CREATE TABLE `myabc` (   `emp_no` int(11) NOT NULL,   `salary` int(11) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL,   `response_code` blob NOT NULL,   PRIMARY KEY (`emp_no`,`from_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE  COLUMNS(from_date) (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB,  PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB,  PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB,  PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB,  PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB,  PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB,  PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB,  PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB,  PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB,  PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB,  PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB,  PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB,  PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB,  PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB,  PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB,  PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB,  PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB,  PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB,  PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */   1 row in set (0.00 sec)

表的总大小是105GB

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> SELECT     table_schema,     table_name,     table_rows,     round( data_length / 1024 / 1024 / 1024 ) DATA_MB,     round( index_length / 1024 / 1024 / 1024 ) INDEX_GB,     round( data_free / 1024 / 1024 / 1024 ) FREE_MB,     round( data_length / 1024 / 1024 / 1024 )+ round( index_length / 1024 / 1024 )+ round( data_free / 1024 / 1024 / 1024 ) TOTAL_MB FROM     information_schema.TABLES WHERE     table_schema = 'abce'     AND table_name = 'myabc'; +--------------+------------+------------+---------+----------+---------+---------+ | table_schema | table_name | table_rows | DATA_GB | INDEX_GB | FREE_GB | TOTAL_GB| +--------------+------------+------------+---------+----------+---------+---------+ | abce         | myabc      | 2845404    | 105     | 0        | 0       | 105     | +--------------+------------+------------+---------+----------+---------+---------+   1 row in set (0.00 sec)

我们将创建一个与分区表相同的表，但是我们将从这个新创建的表中删除分区。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> CREATE TABLE myabc_swap_p3 LIKE myabc; Query OK, 0 rows affected (0.23 sec)   mysql> ALTER TABLE myabc_swap_p3 REMOVE PARTITIONING; Query OK, 0 rows affected (0.32 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> SELECT count(*) FROM myabc PARTITION (p03); +----------+ | count(*) | +----------+ | 57395    | +----------+ 1 row in set (0.00 sec)     mysql> SELECT count(*) FROM myabc_swap_p3; +----------+ | count(*) | +----------+ | 0        | +----------+ 1 row in set (0.02 sec)

文件系统上占用的空间：

?

1 2 3 4

# ls -lrth *myabc*3* -rw-r-----. 1 mysql mysql  96K Nov 21 03:54 myabc_swap_p3.ibd -rw-r-----. 1 mysql mysql 8.5K Nov 21 03:56 myabc_swap_p3.frm -rw-r-----. 1 mysql mysql  10G Nov 21 03:56 myabc#P#p03.ibd

为了交换分区，我们需要执行一个查询，该查询需要在表上锁定元数据，并且几乎是瞬时完成的。

?

1	mysql> ALTER TABLE myabc EXCHANGE PARTITION p03 WITH TABLE myabc_swap_p3;

该操作需要获取元数据锁，虽然时间很短，还是建议在低峰期做该操作。

?

1 2 3 4 5 6 7

mysql> show processlist; +----+------+-----------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+ | Id | User | Host      | db        | Command | Time | State                           | Info                                                                   | Rows_sent | Rows_examined | +----+------+-----------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+ |  4 | root | localhost | abce      | Query   |   0  | Waiting for table metadata lock | ALTER TABLE myabc EXCHANGE PARTITION p03 WITH TABLE myabc_swap_p3      |         0 |             0 | +----+------+-----------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+ 1 row in set (0.00 sec)

交换后，新表就有了分区中的所有数据，对应的分区就是空的了。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql>  SELECT count(*) FROM myabc PARTITION (p03); +----------+ | count(*) | +----------+ |        0 | +----------+ 1 row in set (0.00 sec)   mysql>  SELECT count(*) FROM myabc_swap_p3; +----------+ | count(*) | +----------+ |    57395 | +----------+ 1 row in set (0.02 sec)

磁盘空间也释放了：

?

1 2 3 4

# ls -lrth *myabc*3* -rw-r-----. 1 mysql mysql  10G Nov 21 03:54 myabc_swap_p3.ibd -rw-r-----. 1 mysql mysql 8.5K Nov 21 03:56 myabc_swap_p3.frm -rw-r-----. 1 mysql mysql  96K Nov 21 03:56 myabc#P#p03.ibd

现在就可以删除交换表而不用锁定原表了；使用该方式也可以加速删除操作。

?

1 2	mysql> drop table if exists myabc_swap_p3;  Query OK, 0 rows affected (5.23 sec)

我们在这里所做的是使用MySQL分区相关的函数来解决客户的挑战，即更快地清除x天以上的数据。但是，分区并不是一个放之四海而皆准的解决方案，分区有它自己的注意事项。

如果一个表上有很多二级索引，并且搜索查询不仅限于分区键，那么查询性能可能会呈指数级下降。如果不能对表进行分区，那么pt-archiver是一个很好的工具。