優化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語句

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 ',';

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大數據表水平分區的詳細方法，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。