Detaillierte Schritte zur horizontalen Partitionsoptimierung von MySQL-Big-Data-Tabellen

Der Inhalt dieses Artikels befasst sich mit den detaillierten Schritten der horizontalen Partitionsoptimierung von MySQL-Big-Data-Tabellen. Ich hoffe, dass er für Freunde hilfreich ist.

Ändern Sie die laufende große Tabelle in eine partitionierte Tabelle

Der Code in diesem Artikel ist auf die horizontale Partitionierung nach Monat basierend auf beschränkt Datenzeit. Sie können den Code ändern, um andere Anforderungen selbst zu implementieren

1. Erstellen Sie eine partitionierte Tabelle

Die Tabellenfelder dieser Tabelle sind genau die gleichen der Originaltabelle, mit Partitionen

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. Kopieren Sie die Originaltabellendaten in die temporäre Tabelle

• direkt über die insert-Anweisung

insert into metric_data_tmp select * from metric_data;

• Die Datenmenge ist sehr groß, Sie können die select into outfile, Load data file-Methode zum Exportieren und Importieren verwenden

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. Benennen Sie die Partitionstabelle und die Verlaufstabelle um:

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

4. Erstellen Sie automatisch Partitionen für den nächsten Monat durch geplante Aufgaben in der Datenbank

• Speicherprozedur

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 ;

• Geplante Aufgaben erstellen, gespeicherte Prozeduren regelmäßig ausführen, um Partitionen zu erstellen

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

• SQL zur Überprüfung des Tabellenpartitionsstatus

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

Das obige ist der detaillierte Inhalt vonDetaillierte Schritte zur horizontalen Partitionsoptimierung von MySQL-Big-Data-Tabellen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!