如何对MySQL中的大表进行数据归档

pt-archiver原理解析

作为MySQL DBA，可以说应该没有不知道pt-archiver了，作为pt-toolkit套件中的重要成员，往往能够轻松帮助DBA解决数据归档的问题。例如线上一个流水表，业务仅仅只需要存放最近3个月的流水数据，三个月前的数据做归档即可，那么pt-archiver就可以轻松帮你完成这件事情，甚至你可以配置成自动任务，无需人工干预。

作为DBA，我们应该知其然更应该知其所以然，这样我们也能够放心地使用pt工具。相信很多DBA都研究过pt-online-schema-change的原理，那么今天我们深入刨一刨pt-archiver的工作原理。

一、原理观察

土人有土办法，我们直接开启general log来观察pt-archiver是如何完成归档的。

命令

pt-archiver --source h=127.0.0.1,u=xucl,p=xuclxucl,P=3306,D=xucl,t=t1 --dest h=127.0.0.1,P=3306,u=xucl,p=xuclxucl,D=xucl_archive,t=t1 --progress 5000 \

--statistics --charset=utf8mb4 --limit=10000 --txn-size 1000 --sleep 30

常用选项

--analyze

指定工具完成数据归档后对表执行'ANALYZE TABLE' *** 作。指定方法如'--analyze=ds'，s代表源端表，d代表目标端表，也可以单独指定。

--ask-pass

命令行提示密码输入，保护密码安全，前提需安装模块perl-TermReadKey。

--buffer

指定缓冲区数据刷新到选项'--file'指定的文件并且在提交时刷新。

只有当事务提交时禁用自动刷新到'--file'指定的文件和刷新文件到磁盘，这意味着文件是被 *** 作系统块进行刷新，因此在事务进行提交之前有一些数据隐式刷新到磁盘。默认是每一行 *** 作后进行文件刷新到磁盘。

--bulk-delete

指定单个语句删除chunk的方式来批量删除行，会隐式执行选项'--commit-each'。

使用单个DELETE语句删除每个chunk对应的表行，通常的做法是通过主键进行逐行的删除，批量删除在速度上会有很大的提升，但如果有复杂的'WHERE'条件就可能会更慢。

--[no]bulk-delete-limit

默认值：yes

指定添加选项'--bulk-delete'和'--limit'到进行归档的语句中。

--bulk-insert

使用LOAD DATA LOCAL INFILE的方法，通过批量插入chunk的方式来插入行(隐式指定选项'--bulk-delete'和'--commit-each')

而不是通过逐行单独插入的方式进行，它比单行执行INSERT语句插入的速度要快。通过隐式创建临时表来存储需要批量插入的行(chunk)，而不是直接进行批量插入 *** 作，当临时表中完成每个chunk之后再进行统一数据加载。为了保证数据的安全性，该选项会强制使用选项'--bulk-delete'，这样能够有效保证删除是在插入完全成功之后进行的。

--channel

指定当主从复制环境是多源复制时需要进行归档哪个主库的数据，适用于多源复制中多个主库对应一个从库的情形。

--charset，-A

指定连接字符集。

--[no]check-charset

默认值：yes

指定检查确保数据库连接时字符集和表字符集相同。

--[no]check-columns

默认值：yes

指定检查确保选项'--source'指定的源端表和'--dest'指定的目标表具有相同的字段。

不检查字段在表的排序和字段类型，只检查字段是否在源端表和目标表当中都存在，如果有不相同的字段差异，则工具报错退出。如果需要禁用该检查，则指定'--no-check-columns'。

--check-slave-lag

指定主从复制延迟大于选项'--max-lag'指定的值之后暂停归档 *** 作。默认情况下，工具会检查所有的从库，但该选项只作用于指定的从库(通过DSN连接方式)。

--check-interval

默认值：1s

如果同时指定了选项'--check-slave-lag'，则该选项指定的时间为工具发现主从复制延迟时暂停的时间。每进行 *** 作100行时进行一次检查。

--columns，-c

指定需要归档的表字段，如有多个则用','(逗号)隔开。

--commit-each

指定按每次获取和归档的行数进行提交，该选项会禁用选项'--txn-size'。

在每次获取表数据并进行归档之后，在获取下一次数据和选项'--sleep'指定的休眠时间之前，进行事务提交和刷新选项'--file'指定的文件，通过选项'--limit'控制事务的大小。

--host，-h

指定连接的数据库IP地址。

--port，-P

指定连接的数据库Port端口。

--user，-u

指定连接的数据库用户。

--password，-p

指定连接的数据库用户密码。

--socket，-S

指定使用SOCKET文件连接。

--databases，-d

指定连接的数据库

--source

指定需要进行归档 *** 作的表，该选项是必须指定的选项，使用DSN方式表示。

--dest

指定要归档到的目标端表，使用DSN方式表示。

如果该选项没有指定的话，则默认与选项'--source'指定源端表为相同表。

--where

指定通过WHERE条件语句指定需要归档的数据，该选项是必须指定的选项。不需要加上'WHERE'关键字，如果确实不需要WHERE条件进行限制，则指定'--where 1=1'。

--file

指定表数据需要归档到的文件。使用类似MySQL DATE_FORMAT()格式化命名方式。

文件内容与MySQL中SELECT INTO OUTFILE语句使用相同的格式，文件命名选项如下所示：

%Y：年，4位数(Year, numeric, four digits)

%m：月，2位数(Month, numeric (01..12))

%d：日，2位数(Day of the month, numeric (01..31))

%H：小时(Hour (00..23))

%i：分钟(Minutes, numeric (00..59))

%s：秒(Seconds (00..59))

%D：数据库名(Database name)

%t：表名(Table name)

例如：--file '/var/log/archive/%Y-%m-%d-%D.%t'

--output-format

指定选项'--file'文件内容输出的格式。

默认不指定该选项是以制表符进行字段的分隔符，如果指定该选项，则使用','(逗号)作为字段分隔符，使用'"'(双引号)将字段括起。用法示例：'--output-format=dump'。

--for-update

指定为每次归档执行的SELECT语句添加FOR UPDATE子句。--share-lock

指定为每次归档执行的SELECT语句添加LOCK IN SHARE MODE子句。

--header

指定在文件中第一行写入字段名称作为标题。

--ignore

指定为INSERT语句添加IGNORE选项。

--limit

默认值：1

指定每条语句获取表和归档表的行数。

--local

指定不将OPTIMIZE和ANALYZE语句写入binlog。

--max-lag

默认值：1s

指定允许主从复制延迟时长的最大值，单位秒。如果在每次获取行数据之后主从延迟超过指定的值，则归档 *** 作将暂停执行，暂停休眠时间为选项'--check-interval'指定的值。待休眠时间结束之后再次检查主从延迟时长，检查方法是通过从库查询的'Seconds_Behind_Master'值来确定。如果主从复制延迟一直大于该参数指定值或者从库停止复制，则 *** 作将一直等待直到从库重新启动并且延迟小于该参数指定值。

--no-delete

指定不删除已被归档的表数据。

--optimize

指定工具完成数据归档后对表执行'OPTIMIZE TABLE' *** 作。指定方法如'--analyze=ds'，s代表源端表，d代表目标端表，也可以单独指定。

--primary-key-only

指定只归档主键字段，是选项'--columns=主键'的简写。

如果工具归档的 *** 作是进行DELETE清除时最有效，因为只需读取主键一个字段而无需读取行所有字段。

--progress

指定每多少行打印进度信息，打印当前时间，已用时间以及多少行进行归档。

--purge

指定执行的清除 *** 作而不是归档 *** 作。允许忽略选项'--dest'和'--file'进行 *** 作，如果只是清除 *** 作可以结合选项'--primary-key-only'会更高效。

--quiet，-q

指定工具静默执行，不输出任何的执行信息。

--replace

指定写入选项'--dest'指定目标端表时改写INSERT语句为REPLACE语句。

--retries

默认值：1

指定归档 *** 作遇到死锁或超时的重试次数。当重试次数超过该选项指定的值时，工具将报错退出。

--run-time

指定工具归档 *** 作在退出之前需要运行的时间。允许的时间后缀名为s=秒，m=分，h=小时，d=天，如果没指定，默认为s。

--[no]safe-auto-increment

默认值：yes

指定不使用自增列(AUTO_INCREMENT)最大值对应的行进行归档。

该选项在进行归档清除时会额外添加一条WHERE子句以防止工具删除单列升序字段具有的具有AUTO_INCREMENT属性最大值的数据行，为了在数据库重启之后还能使用到AUTO_INCREMENT对应的值，但这会引起无法归档或清除字段对应最大值的行。

--set-vars

默认：

wait_timeout=10000

innodb_lock_wait_timeout=1

lock_wait_timeout=60

工具归档时指定参数值，如有多个用','(逗号)分隔。如'--set-vars=wait_timeout=5000'。

--skip-foreign-key-checks

指定使用语句SET FOREIGN_KEY_CHECKS = 0禁用外键检查。

--sleep

指定工具在通过SELECT语句获取归档数据需要休眠的时间，默认值是不进行休眠。在休眠之前事务并不会提交，并且选项'--file'指定的文件不会被刷新。如果指定选项'--commit-each'，则在休眠之前会进行事务提交和文件刷新。

--statistics

指定工具收集并打印 *** 作的时间统计信息。

统计信息示例如下：

'

Started at 2008-07-18T07:18:53, ended at 2008-07-18T07:18:53

Source: D=db,t=table

SELECT 4

INSERT 4

DELETE 4

Action Count TimePct

commit10 0.1079 88.27

select 5 0.0047 3.87

deleting 4 0.0028 2.29

inserting 4 0.0028 2.28

other 0 0.0040 3.29

--txn-size

默认：1

指定每个事务处理的行数。如果是0则禁用事务功能。

--version

显示工具的版本并退出。

--[no]version-check

默认值：yes

检查Percona Toolkit、MySQL和其他程序的最新版本。

--why-quit

指定工具打印当非因完成归档行数退出的原因。

在执行一个自动归档任务时该选项与选项'--run-time'一起使用非常方便，这样可以确定归档任务是否在指定的时间内完成。如果同时指定了选项'--statistics'，则会打印所有退出的原因。

二、原理解析

根据general log的输出，我们整理出时序表格如下

三、其他说明

咋一看这个过程貌似也没有什么问题，但是，假如在原表扫描出数据，插入到新表的过程中，旧数据发生了变化怎么办？

带着这个疑问，我们进行了源码的跟踪，我们在pt-archiver的6839行打上了断点

然后我分别在几个session窗口做了如下动作

最后pt-archiver输出如下：

# A software update is available:

TIMEELAPSED COUNT

2020-04-08T09:13:21 0 0

2020-04-08T09:13:21 0 1

Started at 2020-04-08T09:13:21, ended at 2020-04-08T09:13:51

Source: A=utf8mb4,D=xucl,P=3306,h=127.0.0.1,p=...,t=t1,u=xucl

Dest: A=utf8mb4,D=xucl_archive,P=3306,h=127.0.0.1,p=...,t=t1,u=xucl

SELECT 1

INSERT 1

DELETE 1

Action Count TimePct

sleep 130.0002 99.89

inserting 1 0.0213 0.07

commit 2 0.0080 0.03

select 2 0.0017 0.01

deleting 1 0.0005 0.00

other 0 0.0008 0.00

很明显，id=3这条记录并没有进行归档（我们这里是改了条件列，实际生产中可能是更改了其他列，造成归档数据不准确）

那么如何来解决这种情况的发生呢？

显然，数据库在数据库中可以通过加排它锁来防止其他程序修改对应的数据，pt-archiver其实早就已经帮我们考虑到了这样的情况，pt-archiver提供了两种选择

--for-update：Adds the FOR UPDATE modifier to SELECT statements

--share-lock：Adds the LOCK IN SHARE MODE modifier to SELECT statements

四、总结

pt-archiver作为归档工具无疑是MySQL DBA日常运维的大利器之一，在使用过程中在知道如何使用的基础上也能够知晓其原理

归档过程中最好能对归档记录进行加锁 *** 作，以免造成归档数据不准确

在主从环境中，归档过程最好控制速度，以免造成主从延迟

尽量控制好chunk的大小，不要过大，造成大事务

分区介绍：

一、什么是分区？

所谓分区，就是将一个表分成多个区块进行 *** 作和保存，从而降低每次 *** 作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。

二、分区作用

1.可以逻辑数据分割，分割数据能够有多个不同的物理文件路径。

2.可以存储更多的数据，突破系统单个文件最大限制。

3.提升性能，提高每个分区的读写速度，提高分区范围查询的速度。

4.可以通过删除相关分区来快速删除数据

5.通过跨多个磁盘来分散数据查询，从而提高磁盘I/O的性能。

6.涉及到例如SUM()、COUNT()这样聚合函数的查询，可以很容易的进行并行处理。

7.可以备份和恢复独立的分区，这对大数据量很有好处。

三、分区能支持的引擎

MySQL支持大部分引擎创建分区，入MyISAM、InnoDB等；不支持MERGE和CSV等来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分区。

四、确认MySQL支持分区

从MySQL5.1开始引入分区功能，可以如下方式查看是否支持：

老版本用：SHOW VARIABLES LIKE '%partition%'

新版本用：show plugins

五、分区类型

1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

例如，可以将一个表通过年份划分成两个分区，2001 -2010年、2011-2020。

2. LIST分区：类似于RANGE分区，LIST是列值匹配一个离散值集合中的某个值来进行选择。

比如 根据字段 把值为1、3、5的放到一起，2、4、6的另外放到一起 等等...

3. HASH分区：基于用户定义的表达式的返回值来进行选择分区，该表达式使用将要插入到表中的这些行的列值来进行计算，这个函数必须产生非负整数值。

通过HASH运算来进行分区，分布的比较均匀

4. KEY分区：类似于按HASH分区，由MySQL服务器提供其自身的哈希函数。

按照KEY进行分区类似于按照HASH分区

六、使用分区注意事项

1. 如果表中存在primary key 或者 unique key 时，分区的列必须是paimary key或者unique key的一个组成部分，也就是说，分区函数的列只能从pk或者uk这些key中取子集

2. 如果表中不存在任何的paimary key或者unique key，则可以指定任何一个列作为分区列

3. 5.5版本前的RANGE、LIST、HASH分区要求分区键必须是int；MySQL5.5及以上，支持非整形的RANGE和LIST分区，即：range columns 和 list columns (可以用字符串来进行分区)。

七、分区命名

1. 分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。应当注意的是，分区的名字是不区分大小写的。

2. 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。

八、 创建分区

1. RANGE分区：

解读：以上为 uuid小于5时放到p0分区下，uuid大于5且小于10放到p1分区下，uuid大于10且小于15放到p2分区下，uuid大于15 一直到最大值的存在p3分区下

2. LIST分区：

解读：以上为uuid 等于1/2/3/5时放到p0分区，7/9/10放到p1分区，11/15放到p2分区。当时用insert into时 如果uuid的值不存在p0/p1/p2分区时，则会插入失败而报错。

3. HASH分区:

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE分区和LIST分区中必须明确指定一个指定的列值或列值集合以指定应该保存在哪个分区中。而在HASH分区中，MySQL会自动完成这些工作，要做的只是基于将要被哈希的列值指定一个表达式，以及指定被分区的表将要被分割成的分区数量，如：

解读：MySQL自动创建3个分区，在执行insert into时，根据插入的uuid通过算法来自动分配区间。

注意：

(1) 由于每次插入、更新、删除一行，这个表达式都要计算一次，这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。

(2) 最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致的增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，就越能有效地使用该表达式来进行HASH分区。

3.1：线性HASH分区

线性HASH分区在“PARTITION BY”子句中添加“LINEAR”关键字。

线性HASH分区的有点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有及其大量数据的表。它的缺点在于各个分区间数据的分布不大可能均衡。

4. KEY分区

类似于HASH分区，HASH分区允许用户自定义的表达式，而KEY分区则不允许使用用户自定义的表达式；HASH分区只支持整数分区，KEY分区支持除了blob和text类型之外的其他数据类型分区。

与HASH分区不同，创建KEY分区表的时候，可以不指定分区键，默认会选择使用主键或唯一键作为分区键，没有主键或唯一键，就必须指定分区键。

解读：根据分区键来进行分区

5. 子分区

子分区是分区表中，每个分区的再次分割，适合保存非常大量的数据。

解读：主分区使用RANGE按照年来进行分区，有3个RANGE分区。这3个分区中又被进一步分成了2个子分区，实际上，整个表被分成了3 * 2 = 6个分区。每个子分区按照天进行HASH分区。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

(1) 在MySQL5.1中，对于已经通过RANGE或LIST分区了的表在进行子分区是可能的。子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为复合分区。

(2) 每个分区必须有相同数量的子分区。

(3) 如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。

(4) 每个SUBPARTITION子句必须包含（至少）子分区的一个名字。

(5) 在每个子分区内，子分区的名字必须是惟一的，目前在整个表中，也要保持唯一。例如：

子分区可以用于特别大的表，可以在多个磁盘间分配数据和索引。例如：

九、MySQL分区处理NULL值的方式

十、分区管理概述

可以对分区进行添加、删除、重新定义、合并或拆分等管理 *** 作。

① RANGE和LIST分区的管理

1. 删除分区语句如：alter table tbl_test drop partition p0

注意：

(1) 当删除了一个分区，也同时删除了该分区中所有的数据。

(2) 可以通过show create table tbl_test来查看新的创建表的语句。

(3) 如果是LIST分区的话，删除的数据不能新增进来，因为这些行的列值包含在已经删除了的分区的值列表中。

2. 添加分区语句如：alter table tbl_test add partition(partition p3 values less than(50))

注意：

(1) 对于RANGE分区的表，只可以添加新的分区到分区列表的最高端。

(2) 对于LIST分区的表，不能添加已经包含在现有分区值列表中的任意值。

3. 如果希望能不丢失数据的条件下重新定义分区，可以使用如下语句：

REORGANIZE会对分区的数据进行重构。

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)

(1) 拆分分区如：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10))

或者如：

ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5))

(2) 合并分区如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5))

4. 删除所有分区，但保留数据，形式：ALTER TABLE tbl_name remove partitioning

② HASH和KEY分区的管理

1. 减少分区数量语句如：ALTER TABLE tbl_name COALESCE PARTITION 2

2. 添加分区数量语句如：ALTER TABLE tbl_name add PARTITION partitions 2

③ 其他分区管理语句

1. 重建分区：类似于先删除保存在分区中的所有记录，然后重新插入它们，可用于整理分区碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3

2. 优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB或TEXT类型的列）做了许多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION来收回没有使用的空间，并整理分区数据文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3

3. 分析分区：读取并保存分区的键分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3

4. 检查分区：检查分区中的数据或索引是否已经被破坏，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3

5. 修补分区：修补被破坏的分区，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3

十、查看分区信息

1. 查看分区信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G

2. 查看分区上的数据：select * from tbl_test partition(p0)

3. 查看MySQL会 *** 作的分区：explain partitions select * from tbl_test where uuid = 2

十一、 局限性

1. 最大分区数目不能超过1024，一般建议对单表的分区数不要超过50个。

2. 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

3. 不支持外键。

4. 不支持全文索引，对分区表的分区键创建索引，那么这个索引也将被分区。

5. 按日期进行分区很合适，因为很多日期函数可以用。但是对字符串来说合适的分区函数不太多。

6. 只有RANGE和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

7. 临时表不能被分区。

8. 分区表对于单条记录的查询没有优势。

9. 要注意选择分区的成本，没插入一行数据都需要按照表达式筛选插入的分区。

10. 分区字段尽量不要可以为null

我们的业务只存近一段时间的数据，因此有大量表需要清理 历史 数据，目前使用的delete清理数据，存在以下问题。为避免同时支持大量delete，我们的清理任务只在低峰期串行执行，导致任务过多时需要排队，甚至失败的情况；数据清理使用delete语句，表数据量较大时，对数据库造成很大压力；即使我们删除了旧数据，已删除的数据仍占据存储空间，底层数据文件并没有立刻变小，以至于形成数据空洞。

查看MySQL官方文档时，发现了分区表，因此基于官方文档总结一下。

MySQL逻辑上为一个表，物理上存储在多个文件中，这是 MySQL 支持的功能（5.1 开始）, 8.0 版本只 InnoDB 和 NDB 支持分区表。

优点：

缺点：

根据分区表键值的范围把数据存储到表的不同分区中，适用于以时间或日期作为分区类型，方便数据清理。

小提示：

1.当插入数据分区不存在时会报错：Table has no partition for value xxx

2.Range类型分区字段必须是数值，时间类型可用函数转换为数值；

3.分区字段列值可以为null，所有为null的数据将存在最小的分区中；

按分区键取值的列表进行分区，每一行数据须找到对应的分区列表，否则数据插入失败

小提示：

根据指定分区表达式的整数值以及分区数进行数据划分(mod函数)

小提示：

按键分区类似于按哈希分区，只是哈希分区使用用户定义的表达式，用于键分区的哈希函数由 MySQL 服务器提供。NDB 集群为此使用 MD5() 对于使用其他存储引擎的表，服务器使用自己的内部哈希函数。

小提示：

子分区(subpartitioning)也称为复合分区(composite partitioning) ，是已分区表中每个分区的进一步划分

小提示：

小提示：

---