Étapes détaillées pour l'optimisation de la partition horizontale des tables Big Data MySQL

Le contenu de cet article concerne les étapes détaillées de l'optimisation des partitions horizontales des tables Big Data MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Modifier la grande table en cours d'exécution en une table partitionnée

Le code de cet article est limité au partitionnement horizontal par mois en fonction de heure des données. Vous pouvez modifier le code pour implémenter d'autres exigences par vous-même

1. Créer une table partitionnée

Les champs de cette table sont exactement les mêmes que ceux-là. de la table d'origine, avec des 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. Copiez les données de la table d'origine dans la table temporaire

• directement via le insert déclaration

insert into metric_data_tmp select * from metric_data;

• La quantité de données est très importante, vous pouvez utiliser la méthode select into outfile, Load data file pour exporter et importer

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. Renommez la table de partition et la table d'historique :

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

4. Créez automatiquement la partition du mois prochain via des tâches planifiées dans la base de données

• 3. 🎜>

procédure stockée

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 ;

Créez des tâches planifiées et exécutez régulièrement des procédures stockées pour créer des partitions

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

SQL pour vérifier l'état de la partition de table

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!