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 문insert语句

insert into metric_data_tmp select * from metric_data;

• 数据量非常大，可使用select into outfile, Load data file

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

데이터 양이 매우 많습니다. 외부 파일로 선택, 데이터 파일 로드 방법을 사용하여 내보내고 가져올 수 있습니다

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

3. 파티션 테이블 및 기록 테이블 이름 바꾸기:

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 ;

4. 데이터베이스의 예약된 작업

• 저장 프로시저

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 ;

를 통해 다음 달 파티션을 자동으로 생성합니다. 정기적으로 저장 프로시저를 사용하여 파티션을 생성합니다

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

5. 기타

테이블 파티션 상태를 확인하는 SQL🎜🎜🎜rrreee🎜🎜

위 내용은 MySQL 빅 데이터 테이블의 수평 파티션 최적화를 위한 세부 단계의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!