MySQL大数据表水平分区优化

你在学习数据库相关的知识吗？本文《MySQL大数据表水平分区优化》，主要介绍的内容就涉及到MySQL，如果你想提升自己的开发能力，就不要错过这篇文章，大家要知道编程理论基础和实战操作都是不可或缺的哦！

将运行中的大表修改为分区表

本文章代码仅限于以数据时间按月水平分区，其他需求可自行修改代码实现

1. 创建一张分区表

这张表的表字段和原表的字段一摸一样，附带分区

CREATE TABLE `metric_data_tmp`  (
    id bigint primary key auto_increment,
    metric varchar(128),
    datadt datetime not null unqine,
    value decimal(30, 6)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
partition by range (to_days(DATADT)) (
    PARTITION p201811 VALUES LESS THAN (to_days("2018-12-01")),
    PARTITION p201812 VALUES LESS THAN (to_days("2019-01-01")),
    PARTITION p201901 VALUES LESS THAN (to_days("2019-02-01")),
    PARTITION p201902 VALUES LESS THAN (to_days("2019-03-01")),
);

2. 将原表数据复制到临时表

• 直接通过

  insert into metric_data_tmp select * from metric_data;

  • 数据量非常大，可使用

    SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM metric_data;
    LOAD DATA INFILE 'data.txt' INTO TABLE metric_data_tmp FIELDS TERMINATED BY ',';

    3. 重命名分区表和历史表：

    rename table metric_data to metric_data_bak;
    rename table metric_data_tmp to metric_data;

    4. 通过数据库的定时任务定时自动创建下月的分区

    • 存储过程

    delimiter $$
    use `db_orbit`$$
    drop procedure if exists `create_partition_by_month`$$
    create procedure `create_partition_by_month`(in_schemaname varchar(64), in_tablename varchar(64))
    begin
        # 用于判断需要创建的表分区是否已经存在
        declare rows_cnt int unsigned;
        # 要创建表分区的时间
        declare target_date timestamp;
        #分区的名称，格式为p201811
        declare partition_name varchar(8);
            
        #要创建的分区时间为下个月
        set target_date = date_add(now(), interval 1 month);
        set partition_name = date_format( target_date, 'p%Y%m' );
            
        # 判断要创建的分区是否存在
        select count(1) into rows_cnt from information_schema.partitions t where table_schema = in_schemaname and table_name = in_tablename and ifnull(t.partition_name, '') = partition_name;
        if rows_cnt = 0 then
            set @sql = concat(
                'alter table `', 
                in_schemaname, 
                '`.`', 
                in_tablename, 
                '`',
                ' add partition (partition ', 
                partition_name, 
                " values less than (to_days('",
                date_format(DATE_ADD(target_date, INTERVAL 1 month), '%Y-%m-01'), 
                "')) engine = innodb);" 
            );
            prepare stmt from @sql;
            execute stmt;
            deallocate prepare stmt;
         else
           select concat("partition `", partition_name, "` for table `",in_schemaname, ".", in_tablename, "` already exists") as result;
         end if;
    end$$
    delimiter ;

    • 创建定时任务，定时执行存储过程创建分区

    DELIMITER $$
    #该表所在的数据库名称
    USE `db_orbit`$$
    CREATE EVENT IF NOT EXISTS `generate_partition_for_metric_data`
    ON SCHEDULE EVERY 1 MONTH   #执行周期，还有天、月等等
    STARTS '2019-03-15 00:00:00'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT 'Creating partitions'
    DO BEGIN
        #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
        CALL db_orbit.create_partition_by_month('db_orbit', 'metric_data');
    END$$
    DELIMITER ;

    5.其他

    • 查看表分区情况的SQL

    select 
        partition_name part,  
        partition_expression expr, 
        partition_description descr, 
        table_rows  
    from information_schema.partitions where table_name='metric_data';

    今天带大家了解了MySQL的相关知识，希望对你有所帮助；关于数据库的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~