由于线上的MySQL实时表数据量太大，即使建了索引查询速度也不理想，上周下班前经理让我对线上MySQL的七张源数据层面的实时表进行归档，现表仅保留近三天的数据，三天之前的数据全部归档到历史表中
 
 
一、基本思想
 
考虑到按照时间进行归档，因此MySQL按时间创建分区表，并且动态维护每张历史表的分区，将三天前的数据插入到历史表中，根据时间的不同会落到不同的分区中；校验数据量在没有丢失的情况下删除原表数据并记录日志。
 
二、前期准备
 
所谓磨刀不误砍柴工，首先需要了解MySQL分区表的理论、如何创建分区表、如何动态维护分区表…
 
2.1 MySQL创建分区表
 
原表的字段基本都是varchar类型，为了方便分区创建历史表的同时增加一个归档日期字段，并按照该字段进行分区给定一个初始分区
 
DROP TABLE IF EXISTS `aj_gaaj_archive `;
CREATE TABLE `aj_gaaj_archive `
(
    ...,
    `BACKUP_TIME` date comment '归档日期'
) PARTITION BY RANGE (to_days(`BACKUP_TIME`))(
        partition p20201224 values less than (to_days('20201225'))
        );
 
查看分区表的分区情况
 
SELECT partition_name                   part,
       partition_expression             expr,
       partition_description            descr,
       FROM_DAYS(partition_description) lessthan_sendtime,
       table_rows
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = SCHEMA()
  AND TABLE_NAME = 'aj_gaaj_archive';
 
2.2 MySQL分区表维护
 
使用过hive的都知道，hive是可以进行动态分区的，根据数据的不同动态的添加分区，据了解MySQL不支持这种功能，因此需要我们手动的去增加分区，从一位老哥的博客中拔下一段方便维护分区表的存储过程 [传送门]
 
create procedure auto_set_partitions(in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                                     in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                                     in partition_number int, in partitiontype int, in gaps int)
L_END:
begin
    declare max_partition_description varchar(255) default '';
    declare p_name varchar(255) default 0;
    declare p_description varchar(255) default 0;
    declare isexist_partition varchar(255) default 0;
    declare i int default 1;

    -- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
    select partition_name
    into isexist_partition
    from information_schema.partitions
    where table_schema = databasename
      and table_name = tablename
    limit 1;
    -- 如果不存在则打印错误并退出存储过程
    if isexist_partition <=> '' then
        select 'partition table not is exist' as "ERROR";
        leave L_END;
    end if;

    -- 获取最大[降序获取]的分区描述[值]
    select partition_description
    into max_partition_description
    from information_schema.partitions
    where table_schema = databasename
      and table_name = tablename
    order by partition_description desc
    limit 1;

    -- 如果最大分区没有,说明没有手动分区,则无法创建自动分区
    if max_partition_description <=> '' then
        select 'partition table is error' as "ERROR";
        leave L_END;
    end if;

    -- 替换前后的单引号[''两个引号表示一个单引号的转义]
    -- set max_partition_description = REPLACE(max_partition_description, '''', '');
    -- 或使用如下语句
    set max_partition_description = REPLACE(max_partition_description - 1, '\'', '');

    -- 自动创建number个分区
    while (i <= partition_number)
        do
            if (partitiontype = 0) then
                -- 每个分区按天递增,递增gaps天
                set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i * gaps day);
            elseif (partitiontype = 1) then
                -- 每个分区按月递增,递增gaps月
                set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i * gaps month);
            else
                -- 每个分区按年递增,递增gaps年
                set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i * gaps year);
            end if;
            -- 删除空格
            set p_name = REPLACE(p_description, ' ', '');
            -- 例如10.20的记录实际是less than 10.21
            set p_description = DATE_ADD(p_description, interval 1 day);
            -- 如果有横杆替换为空
            set p_name = REPLACE(p_name, '-', '');
            -- 删除时间冒号
            set p_name = REPLACE(p_name, ':', '');
            -- alter table tablename add partition ( partition pname values less than ('2017-02-20 10:05:56') );
            set @sql = CONCAT('ALTER TABLE ', tablename, ' ADD PARTITION ( PARTITION p', p_name,
                              ' VALUES LESS THAN (TO_DAYS(\'', p_description, '\')))');
            -- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
            -- 打印sql变量
            -- select @sql;
            -- 准备sql语句
            PREPARE stmt from @sql;
            -- 执行sql语句
            EXECUTE stmt;
            -- 释放资源
            DEALLOCATE PREPARE stmt;
            -- 递增变量
            set i = (i + 1);

        end while;
end;
 
三、本地实施
 
因为归档的表属于ODS级别，不得不慎重啊，稍有不慎就要跑路的😄😄😄，因此先导出一张表到本地测试一波
 
3.1 备份原表
 
简单粗暴
 
create table aj_gaaj_bk_20201229 as select * from aj_gaaj;
 
3.2 创建历史表
 
为了安全，处理了字段名
 
DROP TABLE IF EXISTS `aj_gaaj_archive `;
CREATE TABLE `aj_gaaj_archive `
(
    `z1`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z2`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z3`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z4`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z5`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z6`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z7`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z8`      varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci     NULL DEFAULT NULL,
    `z9`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z1`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z2`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z3`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z4`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z5`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z6`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z7`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z8`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z9`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z1`      varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci     NULL DEFAULT NULL,
    `z2`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z3`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z4`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z5`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z6`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z7`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z8`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `z9`      varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    `BACKUP_TIME` date comment '归档日期'
) ENGINE = InnoDB
  CHARACTER SET = utf8
  COLLATE = utf8_general_ci
  ROW_FORMAT = Compact
    PARTITION BY RANGE (to_days(`BACKUP_TIME`))(
        partition p20201225 values less than (to_days('20201226'))
        );
 
3.3 同步数据
 
首次同步，之后同步修改时间范围即可
 
insert into aj_gaaj_archive 
select *, substr(INSERT_TIME, 1, 8)
from aj_gaaj
where substr(INSERT_TIME, 1, 8) < date_format(now(),'%Y%m%d');
 
查看数据是否落入分区中
 
 
3.4 校验数据
 
校验同步的分区数据行数是否丢失，若行数一致则删除原表数据，否则保留原表数据之后操作在做商榷
 
select count(*) from aj_gaaj_archive  partition (p20201225);
select count(*) from aj_gaaj where substr(INSERT_TIME, 1, 8) = '20201225';

-- 删除原表数据
delete
from aj_gaaj
where substr(INSERT_TIME, 1, 8) < date_format(now(), '%Y%m%d');
 
四、线上实施
 
4.1 备份原表
 
CREATE TABLE aj_gaaj_bk_20201229 AS SELECT * FROM aj_gaaj;
CREATE TABLE aj_pnbd_bk_20201229 AS SELECT * FROM aj_pnbd;
CREATE TABLE aj_pnck_bk_20201229 AS SELECT * FROM aj_pnck;
CREATE TABLE aj_pnsc_bk_20201229 AS SELECT * FROM aj_pnsc;
CREATE TABLE fl_afds_bk_20201229 AS SELECT * FROM fl_afds;
CREATE TABLE fl_dfxx_bk_20201229 AS SELECT * FROM fl_dfxx;
CREATE TABLE aj_other_pspe_bk_20201229 AS SELECT * FROM aj_other_pspe;
 
4.2 创建历史表
 
获取原表最小的时间，并以此为最小分区数创建初始分区，调用auto_set_partitions增加分区至当前日期
 
4.3 同步数据
 
-- 创建存储过程执行归档
create procedure data_archiving()
begin
    -- 归档三天前的数据
    declare backup_time varchar(255) default date_format(date_sub(now(), interval 3 day), '%Y%m%d');
    -- =============================== aj_gaaj =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'aj_gaaj_archive', 1, 0, 1);
    -- 归档数据
    insert into aj_gaaj_archive
    select *, substr(INSERT_TIME, 1, 8)
    from aj_gaaj
    where substr(INSERT_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('aj_gaaj', 'aj_gaaj_archive', backup_time);
    -- =============================== aj_other_pspe =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'aj_other_pspe_archive', 1, 0, 1);
    -- 归档数据
    insert into aj_other_pspe_archive
    select *, substr(INSERT_TIME, 1, 8)
    from aj_other_pspe
    where substr(INSERT_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('aj_other_pspe', 'aj_other_pspe_archive', backup_time);
    -- =============================== aj_pnbd =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'aj_pnbd_archive', 1, 0, 1);
    -- 归档数据
    insert into aj_pnbd_archive
    select *, substr(BD_TIME, 1, 8)
    from aj_pnbd
    where substr(BD_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('aj_pnbd', 'aj_pnbd_archive', backup_time);
    -- =============================== aj_pnck =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'aj_pnck_archive', 1, 0, 1);
    -- 归档数据
    insert into aj_pnck_archive
    select *, substr(INSERT_TIME, 1, 8)
    from aj_pnck
    where substr(INSERT_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('aj_pnck', 'aj_pnck_archive', backup_time);
    -- =============================== aj_pnck =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'aj_pnsc_archive', 1, 0, 1);
    -- 归档数据
    insert into aj_pnsc_archive
    select *, substr(INSERT_TIME, 1, 8)
    from aj_pnsc
    where substr(INSERT_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('aj_pnsc', 'aj_pnsc_archive', backup_time);
    -- =============================== fl_afds =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'fl_afds_archive', 1, 0, 1);
    -- 归档数据
    insert into fl_afds_archive
    select *, substr(INSERT_TIME, 1, 8)
    from fl_afds
    where substr(INSERT_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('fl_afds', 'fl_afds_archive', backup_time);
    -- =============================== fl_dfxx =======================================
    -- 增加分区
    call auto_set_partitions('hcss_can_ods', 'fl_dfxx_archive', 1, 0, 1);
    -- 归档数据
    insert into fl_dfxx_archive
    select *, substr(INSERT_TIME, 1, 8)
    from fl_dfxx
    where substr(INSERT_TIME, 1, 8) = backup_time;
    -- 校验数据
    call check_data('fl_dfxx', 'fl_dfxx_archive', backup_time);
end;
 
4.4 校验数据
 
为方便观察归档情况，创建归档日志表记录
 
create table bk_log
(
    execute_time datetime comment '执行时间',
    bk_time      date comment '备份时间',
    tbl_name     varchar(255) comment '备份的表',
    src_num      bigint comment '原表数据',
    bk_num       bigint comment '备份数据',
    log          varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci comment '日志'
);
 
创建数据校验存储过程
 
create procedure check_data(in src_tbl varchar(255),
                            in archive_tbl varchar(255),
                            in check_time varchar(255))
begin
    -- 校验数据
    -- src_tbl 原表名
    -- archive_tbl 归档表名
    -- check_time 待校验的时间

	-- aj_pnbd表字段比较特殊，不知道当时建表是怎么想的
    if src_tbl <> 'aj_pnbd' then
        set @src_sql =
                concat('select count(*) into @src_num from ', src_tbl, ' where substr(INSERT_TIME, 1, 8) = ',
                       check_time);
        set @delete_sql =
                concat('delete from ', src_tbl, ' where substr(INSERT_TIME, 1, 8) = ', check_time);
    else
        set @src_sql =
                concat('select count(*) into @src_num from ', src_tbl, ' where substr(BD_TIME, 1, 8) = ',
                       check_time);
        set @delete_sql =
                concat('delete from ', src_tbl, ' where substr(BD_TIME, 1, 8) = ', check_time);
    end if;
    set @archive_sql =
            concat('select count(*) into @archive_num from ', archive_tbl, ' partition (p', check_time, ')');
    -- 放置产生bug导致数据被删除
    set @src_num = 1;
    set @archive_num = -1;
    prepare stmt from @src_sql;
    execute stmt;
    prepare stmt from @archive_sql;
    execute stmt;
    if @src_num = @archive_num then
        prepare stmt from @delete_sql;
        execute stmt;
        insert into bk_log
        values (now(), check_time, concat(src_tbl, ' -> ', archive_tbl), @src_num, @archive_num, '归档成功，删除原表数据');
    else
        insert into bk_log
        values (now(), check_time, concat(src_tbl, ' -> ', archive_tbl), @src_num, @archive_num, '数据不一致，保留原表数据');
    end if;
    deallocate prepare stmt;
    set @src_num = null;
    set @archive_num = null;
    set @delete_sql = null;
    set @src_sql = null;
    set @archive_sql = null;
end;
 
4.5 定时调用
 
create event event_auto_archive
    on schedule every 1 day
        starts '2020-12-30 05:00:00'
    do
    begin
        call data_archiving();
    end;
 
早晨上班战战兢兢地打开vpn登录到线上查看情况，完美！！！😄😄😄
 
 
 
 
写在后面应该看不见：
 说实话我怀疑经理在框我，这点数据就跑不动了？？？仔细检查一番，确实就这点数据量😪😪😪
 

 
1.概述
 
使用相对低配的大磁盘机器配置为 ES 的 Warm Nodes，可以通过 index.routing.allocation.require.box_type 来设置索引是冷数据或者热数据。如果索引极少使用，可以 close 索引，然后在需要搜索的时候 open 即可。

 
使用MySQL的过程，经常会遇到一个问题，比如说某张”log”表，用于保存某种记录，随着时间的不断的累积数据，但是只有最新的一段时间的数据是有用的；这个时候会遇到性能和容量的瓶颈，需要将表中的历史数据进行归档。下面来说说几种常见的数据归档方式。
 
一、使用分区，再利用分区交换技术能够很好地把指定分区中的数据移动到指定表中，这个需要在项目之处就进行此操作。
 
具体可以看博客分区章节，这几不做介绍。
 
二、利用存储过程和事件来定期进行数据的导出删除操作。
 
1 、创建一个新表，表结构和索引与旧表一模一样
 
create table table_new like table_old;
 
1
 
createtabletable_newliketable_old;
 
2 、新建存储过程，查询30天的数据并归档进新数据库，然后把30天前的旧数据从旧表里删除
 
delimiter $
 
create procedure sp()
 
begin
 
insert into tb_new select * from table_old where rectime < NOW() - INTERVAL 30 DAY;
 
delete from db_smc.table_old where rectime < NOW() - INTERVAL 30 DAY;
 
end
 
1
 
2
 
3
 
4
 
5
 
6
 
delimiter$
 
createproceduresp()
 
begin
 
insertintotb_newselect*fromtable_oldwhererectime
 
deletefromdb_smc.table_oldwhererectime
 
end
 
3、创建EVENT，每天晚上凌晨00：00定时执行上面的存储过程
 
create event if not exists event_temp
 
on schedule every 1 day
 
on completion preserve
 
do call sp();
 
1
 
2
 
3
 
4
 
createeventifnotexistsevent_temp
 
onscheduleevery1day
 
oncompletionpreserve
 
docallsp();
 
备注：第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，应急解决方案如下：
 
1、执行show full processlist;查看所有MySQL线程。
 
2、执行SELECT * FROM information_schema.INNODB_TRX\G; 查看是否有错误线程，线程id在show full processlist;的结果中状态为sleep。
 
3、kill进程id。
 
另外写存储过程的时候可以控制事务的大小，比如说可以根据时间字段每次归档一天或者更小时间段的数据，这样就不会有大事务的问题，里面还可以加入日志表，每次归档操作的行为都写入日志表，以后查起来也一目了然。
 
三、使用percona-toolkit的pt-archiver工具来进行历史数据归档，支持删除和不删除元数据的选择。
 
pt-archiver使用的场景:
 
1、清理线上过期数据。
 
2、清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器。
 
3、两张表之间的数据不完全相同，希望合并。此时加上–ignore或–replace选项，可以轻松实现。
 
4、导出线上数据，到线下数据作处理。
 
其它作用：
 
1、用于清理过期数据purge
 
$ pt-archiver --source h=10.99.73.9,P=3306,u=mha,p=123456,D=sbtest,t=sbtest \
 
--no-check-charset \
 
--where 'id<50000' \
 
--purge \
 
--limit=2 \
 
--statistics
 
1
 
2
 
3
 
4
 
5
 
6
 
$pt-archiver--sourceh=10.99.73.9,P=3306,u=mha,p=123456,D=sbtest,t=sbtest\
 
--no-check-charset\
 
--where'id<50000'\
 
--purge\
 
--limit=2\
 
--statistics
 
注意：--source后的DSN之间不能空格出现，否则会出错。 --where条件的值，有字符串的，要用引号括起来。--limit表示，每组一次删除多少条数据(注意：如果数据比较多时，也可以设置大一些，减少循环次数)，最终的清理操作，还是通过Where pK=xx来处理的。
 
2、用于把数据导出文件，不用删除原表中数据
 
$ pt-archiver --source h=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest \
 
--dest h=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest_like \
 
--where 'id>50000' \
 
--progress 5000 \
 
--no-delete \
 
--file "/tmp/pt-archiver.dat" \
 
--limit=10000 \
 
--txn-size=10000 \
 
--statistics
 
1
 
2
 
3
 
4
 
5
 
6
 
7
 
8
 
9
 
$pt-archiver--sourceh=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest\
 
--desth=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest_like\
 
--where'id>50000'\
 
--progress5000\
 
--no-delete\
 
--file"/tmp/pt-archiver.dat"\
 
--limit=10000\
 
--txn-size=10000\
 
--statistics
 
参数说明：
 
--statistics：结束的时候给出统计信息：开始的时间点，结束的时间点，查询的行数，归档的行数，删除的行数，以及各个阶段消耗的总的时间和比例，便于以此进行优化。
 
--where：给出表中要处理的数据的查询条件。
 
--progress：每处理progress指定的行数后，就打印一次信息。
 
--no-delete：表示不删除原来的数据，注意：如果不指定此参数，所有处理完成后，都会清理原表中的数据。
 
--limit：表示每次事务删除多少条数据，默认1条(注意：如果数据比较多时，也可以设置大一些，减少循环次数)。
 
--txn-size：每个事务提交的数据行数(包括读写操作)，批量提交，增加该值可以提升归档性能。
 
--file：数据存放的文件，最好指定绝对路径，文件名可以灵活地组合(另外，我测试过写文件与不写文件速度几乎差不多，原本以为不写文件速度会快)。
 
%d Day of the month, numeric (01..31)
 
%H Hour (00..23)
 
%i Minutes, numeric (00..59)
 
%m Month, numeric (01..12)
 
%s Seconds (00..59)
 
%Y Year, numeric, four digits
 
%D Database name
 
%t Table name
 
1
 
2
 
3
 
4
 
5
 
6
 
7
 
8
 
9
 
10
 
11
 
12
 
13
 
14
 
15
 
%dDayofthemonth,numeric(01..31)
 
%HHour(00..23)
 
%iMinutes,numeric(00..59)
 
%mMonth,numeric(01..12)
 
%sSeconds(00..59)
 
%YYear,numeric,fourdigits
 
%DDatabasename
 
%tTablename
 
注意字符集问题
 
Tips：如果你的数据库字符集是utf8的话，需要在运行pt-archive的机器上，在/etc/my.cnf文件中的[client]下面添加default-character-set = utf8，否则导出的文件内容中文会乱码，我就被这个问题坑了。
 
测试归档
 
首先压测10万数据。
 
mysql> select count(1) from sbtest;
 
+----------+
 
| count(1) |
 
+----------+
 
| 100000 |
 
+----------+
 
1 row in set (0.04 sec)
 
1
 
2
 
3
 
4
 
5
 
6
 
7
 
mysql>selectcount(1)fromsbtest;
 
+----------+
 
|count(1)|
 
+----------+
 
|100000|
 
+----------+
 
1rowinset(0.04sec)
 
创建一张归档表，表结构跟原表一样。
 
mysql> CREATE TABLE `sbtest_like` like sbtest;
 
1
 
mysql>CREATETABLE`sbtest_like`likesbtest;
 
开始进行归档表操作，不删除原有表数据记录(如果想删除原表数据需要去掉--no-delete参数即可)
 
$ pt-archiver --source h=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest \
 
--dest h=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest_like \
 
--where 'id>50000' \
 
--progress 5000 \
 
--no-delete \
 
--file "/tmp/pt-archiver.dat" \
 
--limit=10000 \
 
--txn-size=10000 \
 
--statistics
 
TIME ELAPSED COUNT
 
2019-01-16T04:25:28 0 0
 
2019-01-16T04:25:29 0 5000
 
2019-01-16T04:25:29 1 10000
 
2019-01-16T04:25:30 2 15000
 
2019-01-16T04:25:31 3 20000
 
2019-01-16T04:25:32 4 25000
 
2019-01-16T04:25:32 4 30000
 
2019-01-16T04:25:33 5 35000
 
2019-01-16T04:25:34 6 40000
 
2019-01-16T04:25:35 7 45000
 
2019-01-16T04:25:36 8 49999
 
Started at 2019-01-16T04:25:28, ended at 2019-01-16T04:25:36
 
Source: D=sbtest,P=3306,h=10.10.0.109,p=...,t=sbtest,u=root
 
Dest: D=sbtest,P=3306,h=10.10.0.109,p=...,t=sbtest_like,u=root
 
SELECT 49999
 
INSERT 49999
 
DELETE 0
 
Action Count Time Pct
 
inserting 49999 5.2583 65.16
 
commit 10 0.1377 1.71
 
print_file 49999 0.1275 1.58
 
select 6 0.0629 0.78
 
other 0 2.4839 30.78
 
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
 
34
 
$pt-archiver--sourceh=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest\
 
--desth=10.10.0.109,P=3306,u=root,p=123456,D=sbtest,t=sbtest_like\
 
--where'id>50000'\
 
--progress5000\
 
--no-delete\
 
--file"/tmp/pt-archiver.dat"\
 
--limit=10000\
 
--txn-size=10000\
 
--statistics
 
TIMEELAPSEDCOUNT
 
2019-01-16T04:25:2800
 
2019-01-16T04:25:2905000
 
2019-01-16T04:25:29110000
 
2019-01-16T04:25:30215000
 
2019-01-16T04:25:31320000
 
2019-01-16T04:25:32425000
 
2019-01-16T04:25:32430000
 
2019-01-16T04:25:33535000
 
2019-01-16T04:25:34640000
 
2019-01-16T04:25:35745000
 
2019-01-16T04:25:36849999
 
Startedat2019-01-16T04:25:28,endedat2019-01-16T04:25:36
 
Source:D=sbtest,P=3306,h=10.10.0.109,p=...,t=sbtest,u=root
 
Dest:D=sbtest,P=3306,h=10.10.0.109,p=...,t=sbtest_like,u=root
 
SELECT49999
 
INSERT49999
 
DELETE0
 
ActionCountTimePct
 
inserting499995.258365.16
 
commit100.13771.71
 
print_file499990.12751.58
 
select60.06290.78
 
other02.483930.78
 
看一下最终处理结果：
 
mysql> select count(*) from sbtest where id>50000;
 
+----------+
 
| count(*) |
 
+----------+
 
| 50000 |
 
+----------+
 
1 row in set (0.01 sec)
 
mysql> select count(*) from sbtest_like;
 
+----------+
 
| count(*) |
 
+----------+
 
| 49999 |
 
+----------+
 
1 row in set (0.01 sec)
 
1
 
2
 
3
 
4
 
5
 
6
 
7
 
8
 
9
 
10
 
11
 
12
 
13
 
14
 
15
 
mysql>selectcount(*)fromsbtestwhereid>50000;
 
+----------+
 
|count(*)|
 
+----------+
 
|50000|
 
+----------+
 
1rowinset(0.01sec)
 
mysql>selectcount(*)fromsbtest_like;
 
+----------+
 
|count(*)|
 
+----------+
 
|49999|
 
+----------+
 
1rowinset(0.01sec)
 
从结果看，跟我们理解的有点偏差，少归档了一条数据。但是如果你把归档条件改为 <50000 ，那么结果就又是正确的了，如下展示。
 
mysql> select count(*) from sbtest where id<50000;
 
+----------+
 
| count(*) |
 
+----------+
 
| 49999 |
 
+----------+
 
1 row in set (0.02 sec)
 
mysql> select count(*) from sbtest_like;
 
+----------+
 
| count(*) |
 
+----------+
 
| 49999 |
 
+----------+
 
1 row in set (0.01 sec)
 
1
 
2
 
3
 
4
 
5
 
6
 
7
 
8
 
9
 
10
 
11
 
12
 
13
 
14
 
15
 
mysql>selectcount(*)fromsbtestwhereid<50000;
 
+----------+
 
|count(*)|
 
+----------+
 
|49999|
 
+----------+
 
1rowinset(0.02sec)
 
mysql>selectcount(*)fromsbtest_like;
 
+----------+
 
|count(*)|
 
+----------+
 
|49999|
 
+----------+
 
1rowinset(0.01sec)
 
这就要引入另外一个参数了：--safe-auto-increment
 
此参数的意思就是不归档最大AUTO_INCREMENT的行，默认为Yes。是为了防止在服务器重新启动时重新使用AUTO_INCREMENT值。如果你需要归档最大AUTO_INCREMENT的行，加上--no-safe-auto-increment参数即可。
 
生产环境中一般都是根据日期来归档数据，比如常见需求保留30天即可，此时where可以这么写 CreateTime <= date_add(now(), interval -30 day)。
 
除了用pt-archiver归档之外，还有一个特别大的用处，我给称之为“无锁导入数据”。在数据归档中还有一种需求(我经常遇到)，为了不影响业务在某些情况下会对一些日志表或者其他表做归档，当表特别大时第一次处理此表就不太好处理，并且就算把表数据删除了后而表文件还是无法缩小。这个时候就可以用MySQL的神奇rename命令对表进行重命名，当然是业务允许情况下，如rename table Deal to Deal_201801, Deal_2018 to Deal，此操作是一个原子操作且特别快。做完这个动作之后，一般还会有一个需求就是把原表中某一段时间的数据导入到新的表中，可能是业务跑批需要或者后台查询需要。导数据该怎么弄呢？很自然可能想到使用insert into Deal select * from Deal_201801 where ...导入操作，但是不好意思，在导入数据的时候可能无法对新表进行操作，会导致业务异常。
 
如果换其他方式呢？写Python或Shell把数据读出来写入到文件，然后再从文件读出循环插入到新表，这当然是可以的。但当数据特别多时，也需要写多线程了。其实这个时候就可以借助pt-archiver进行数据导入了，从老的表读出来然后直接插入到新的表，他的原理与我们上面说的方式类似，但是它更友好，且更快。
 
四、使用union或union all来进行结果合并
 
当历史数据进行归档后，这个时候就有需求了。当需要查看历史数据和现有表数据时有没有什么好的方法呢？其实可以使用union或union all来进行多表结果合并操作。
 
在数据库中，union和union all关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。union在进行表联接时会筛选掉重复的记录，包括左表去重(会给左表所有字段创建为一个主键)，然后再返回结果。
 
select * from test_union1
 
union
 
select * from test_union2
 
1
 
2
 
3
 
select*fromtest_union1
 
union
 
select*fromtest_union2
 
这个语句的执行流程是这样的：
 
1. 创建一个内存临时表，这个临时表会存入左表字段，创建主键。
 
2. 执行右表，并存入临时表中。在存入临时表时，如果已经存在了相同条目就会违反唯一性约束，所以插入失败；然后继续执行插入。
 
3. 临时表中按行取出数据，返回结果，并删除临时表。
 
可以看到，这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键的唯一性约束，实现了 union 的语义。如果表数据量大的话可能会导致用磁盘进行排序。
 
而union all只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，union all要比union快很多，也不需要临时表了。所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，语法如下：
 
select * from test_union1
 
union all
 
select * from test_union2
 
1
 
2
 
3
 
select*fromtest_union1
 
unionall
 
select*fromtest_union2
 
使用union组合查询的结果集有两个最基本的规则：
 
1. 所有查询中的列数和列的顺序必须相同。
 
2. 数据类型必须兼容。
 
虽然这个可以简便解决数据查询问题，但是还是需要代码层面的调整。
 
union还有一个地方可能会用到，如web项目中经常会碰到整站搜索的问题，即客户希望在网站的搜索框中输入一个词语，然后在整个网站中只要包含这个词的页面都要出现在搜索结果中。由于一个web项目不可能用一张表就全部搞定的，所以这里一般都是要用union联合搜索来解决整个问题的。
 
如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。

 
当有一张表数据量很大，真正项目只用到一个月内的数据，因此把一个月前的旧数据定期归档。
 
解决方案如下：
 
1 - 创建一个新表，表结构和索引与旧表一模一样
 
create table table_archive like table_name;
 
2 - 新建存储过程，查询30天的数据并归档进新数据库，然后把30天前的旧数据从旧表里删除
 
delimiter $
 
create procedure sp()
 
begin
 
insert into table_archiveselect * fromtable_name where rectime < now() - interval 30 day;
 
delete from db_smc.table_name where rectime < now() - interval 30 day;
 
end
 
3 - 创建event，每天晚上凌晨00：00定时执行上面的存储过程
 
create event if not exists event_temp
 
on schedule every 1 day
 
on completion preserve
 
do call sp();
 
备注：
 
第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错error 1205 (hy000): lock wait timeout exceeded; try restarting transaction，应急解决方案如下：
 
1、执行show full processlist;查看所有mysql线程
 
2、执行select * from information_schema.innodb_trx\g; 查看是否有错误线程，线程id在show full processlist;的结果中状态为sleep
 
3、kill 进程id
 
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！