精华内容
下载资源
问答
  • 记一次线上MySQL数据归档方案

    由于线上的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登录到线上查看情况,完美!!!😄😄😄
    在这里插入图片描述

    写在后面应该看不见:
    说实话我怀疑经理在框我,这点数据就跑不动了???仔细检查一番,确实就这点数据量😪😪😪

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

    在这里插入图片描述

    1.概述

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

    展开全文
  • MySQL数据归档的几种操作方法介绍

    千次阅读 2021-01-18 18:40:33
    下面来说说几种常见的数据归档方式。一、使用分区,再利用分区交换技术能够很好地把指定分区中的数据移动到指定表中,这个需要在项目之处就进行此操作。具体可以看博客分区章节,这几不做介绍。二、利用存...

    使用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 - 新建存储...

    当有一张表数据量很大,真正项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。

    解决方案如下:

    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

    如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!

    展开全文
  • 下面来说说几种常见的数据归档方式。一、使用分区,再利用分区交换技术能够很好地把指定分区中的数据移动到指定表中,这个需要在项目之处就进行此操作。二、利用存储过程和事件来定期进行数据的导出删除操...
  • 在线创建索引和字段3.在线事务4.支持索引同步2.tokudb安装步骤1.yum install jemalloc -y2.vim/etc/my.cnf #添加如下[mysqld_safe]malloc-lib=/usr/lib64/libjemalloc.so.13.echo never >/sys/kerne...
  • 数据库有一张表数据量很大,真正WEB项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。1 - 创建一个新表,表结构和索引与旧表一模一样create table table_news like table_name;2 - 新建存储过程,查询30...
  • 生产线历史数据归档是数据库运维的一项日常基本工作。在建表设计时,通常都将数据流水表(如:日志、用户登录历史,软件下载记录,用户属性更改历史表等)设计为范围分区表、间隔分区表(11G),当超过业务要求的保留...
  • 1.通用数据归档方法 #1. 创建归档表,一般在原表名后面添加_bak。 CREATE TABLE `ota_order_bak` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `order_id` varchar(255) DEFAULT NULL COMMENT '...
  • RDS for MySQL 通过分区归档历史数据原始表分区用于分区维护的存储过程每月调用存储过程的事件随着数据的积累,数据量的增加,越来越多的表体积变的庞大,不但影响查询的执行时间,而且使得管理工作(比如添加删除...
  • db_recovery_file_dest string /u01/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G 2、删除日志 cd $ORACLE_BASE/flash_recovery_area/orcl/archivelog 转移或清除对应的归档日志, 删除...
  • MySQL中InnoDB索引数据结构(B+树)详解 参考文章:CodingLabs - MySQL索引背后的数据结构及算法原理 MySQL中常用存储引擎有哪些?它们相互之间有什么区别? - 知乎 (zhihu.com) 一、MySQL中的数据存储引擎 存储引擎...
  • MYSQL索引数据结构

    2021-01-19 15:53:44
    2、索引数据结构二叉树,红黑树,B树,HASH,B+树 3、MYSQL存储引擎InnoDB和MyISAM 在mysql5之后,支持的存储引擎有十几个,但是常用的就那么几种,而且默认支持的也是InnoDB,不同的存储引擎都有各自的特点,以...
  • ORACLE 数据归档之三

    2021-04-13 11:54:14
    Oracle 数据压缩(Compression)步骤(转载) 本文只是叙述运维中压缩表的步骤,具体原理请自行查阅相关文档,不足之处敬请指正。 --1.查看某个用户下最大的30个表 SELECT * FROM (SELECT OWNER, SEGMENT_NAME,...
  • 此时我们可以保留一段时间内的数据,其他日期久远的数据我们可以根据情况进行删除或归档。 根据常规认知,不管是直接删除无效数据还是归档无效数据后,清空原表的无效数,都需要使用delete语句删除。然而根据博主敖...
  • 对于分区表,可以建立不分区索引。也就是说表分区,但是索引不分区。以下着重介绍分区表的分区索引索引与表一样,也可以分区。索引分为两类:locally partition index(局部分区索引)、globally partition index...
  • 唯一索引可以作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键)主键和唯一索引主键保证数据库里面的每一行都是唯一的,比如...
  • 1、执行命令:pt-archiver --source h=127.0.0.1,P=58886,D=test,t=t_archive --no-check-charset --where 'insertdate1) 注意--source后的DSN之间不能空格出现...2) --limit表示,每组一次删除多少条数据(注意:如...
  • 3.创建基表t12存放P2规则的数据。4.用基表t11和分区表T1的P1分区交换。把表t11的数据放到到P1分区5.用基表t12和分区表T1p2分区交换。把表t12的数据存放到P2分区。----1.未分区表和分区表中一个分区交换createtablet1...
  • MySQL 索引

    2021-01-19 20:11:14
    一、索引作用提供了类似于书中目录的作用,目的是为了优化查询(一)、索引种类1、B树索引(Balance Tree)作用Btree的设计理念,就是让查询能够快速锁定范围,特别适合于范围查询。种类B树B+树 相邻叶子节点上有双向指针...
  • 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键) 主键和唯一索引 主键保证数据库里面的每一行都是唯一的,比如...
  • 前言 在MySQL官方提到,改善操作...所有MySQL数据类型都可以建立索引。 尽管可能会为查询中使用的每个可能的列创建索引,但不必要的索引会浪费空间和时间,使MySQL难以确定要使用的索引索引还会增加插入,更新...
  • 1 控制文件参数文件init.ora记录了控制文件的位置控制文件中的主要信息:数据库的名字,检查点信息,数据库创建的时间戳,所有的数据文件,联机日志文件,归档日志文件信息,备份信息等。有了这些信息,oracle就知道...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 40,256
精华内容 16,102
关键字:

数据归档索引