Detailed steps for horizontal partition optimization of MySQL big data tables

The content of this article is about the detailed steps of horizontal partition optimization of MySQL big data tables. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Modify the running large table into a partitioned table

The code in this article is limited to horizontal partitioning by month based on data time. For other needs, you can modify the code to achieve it

1. Create a partitioned table

The table fields of this table are exactly the same as those of the original table, with partitions

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. Copy the original table data to the temporary table

• directly through the insert statement

insert into metric_data_tmp select * from metric_data;

• The amount of data is very large, you can use select into outfile, Load data file to export and import

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 the partition table and history table:

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

4. Automatically create next month’s partition through the scheduled task of the database

• Stored procedure

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 ;

• Create a scheduled task and execute the stored procedure regularly to create a partition

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. Others

• SQL to view table partitioning

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

The above is the detailed content of Detailed steps for horizontal partition optimization of MySQL big data tables. For more information, please follow other related articles on the PHP Chinese website!