MySQL使用分区快速归档数据
2023-01-07 15:08 abce 阅读(486) 评论(0) 编辑 收藏 举报对于我们中的许多人来说，空间限制一直是一个无尽而痛苦的过程，特别是在具有大量事务和几天内数据增长就能超过百GB的系统中。
在这篇文章中，我将分享一个解决方案，无论表的大小如何，使用表分区都可以在几秒钟内删除这个空间并从表中删除行，而不会对数据库造成任何额外的负载。
任何人想到的删除行的第一种方法是使用SQL中的DELETE查询。假设，一个人想要从一个表中删除超过一年的行，这种操作的查询将像这样:
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分区，这是一种更快的方法。
非分区表：
# ls -lh myabc*#* -rw-r-----. 1 mysql mysql 104G Oct 11 05:47 myabc.ibd
分区表：
# 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
分区表是如何快速删除数据和释放空间的？
为了归档到分区表中的旧数据，我们将创建一个空表，它在结构上与原始表相同，但不像原始表那样有多个分区。创建了这个表后，我们将在几秒钟内完成将新创建的空表与原始表的一个分区交换。
在本例中，我们使用的是一个基于日期范围进行分区的表。
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
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)
我们将创建一个与分区表相同的表，但是我们将从这个新创建的表中删除分区。
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)
文件系统上占用的空间：
# 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
为了交换分区，我们需要执行一个查询，该查询需要在表上锁定元数据，并且几乎是瞬时完成的。
mysql> ALTER TABLE myabc EXCHANGE PARTITION p03 WITH TABLE myabc_swap_p3;
该操作需要获取元数据锁，虽然时间很短，还是建议在低峰期做该操作。
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)
交换后，新表就有了分区中的所有数据，对应的分区就是空的了。
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)
磁盘空间也释放了：
# 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
现在就可以删除交换表而不用锁定原表了；使用该方式也可以加速删除操作。
mysql> drop table if exists myabc_swap_p3; Query OK, 0 rows affected (5.23 sec)
我们在这里所做的是使用MySQL分区相关的函数来解决客户的挑战，即更快地清除x天以上的数据。但是，分区并不是一个放之四海而皆准的解决方案，分区有它自己的注意事项。
如果一个表上有很多二级索引，并且搜索查询不仅限于分区键，那么查询性能可能会呈指数级下降。如果不能对表进行分区，那么pt-archiver是一个很好的工具。
