Modifiez le champ de partition en ligne dans la partition de la table de partition MySQL et apprenez-en plus sur la partition plus tard (1)

L'entreprise utilise la partition en ligne, et le champ de partition d'une table est erroné et doit être reconstruit. Il s'avère qu'il n'y a aucun moyen de le faire directement avec un seul SQL, comme modifier le champ de clé primaire ou modifier l'index. champ. Au lieu de cela, nous devons créer une table temporaire et disposer de temps d'arrêt. J'ai donc lu attentivement la documentation et étudié les détails de la partition.

Lorsque mon entreprise le met en ligne, pendant la période de faible pointe d'activité à 1 heure du matin, exécutez :

Créer une table temporaire

CREATE TABLE tbname_TMP (    
SHARD_ID INT NOT NULL,    
...
    xxx_DATE DATETIME NOT NULL,    
    PRIMARY KEY (xxx_DATE,shard_id)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY LIST(MONTH(xxx_DATE)) (    
    PARTITION m1 VALUES IN (1),    
    PARTITION m2 VALUES IN (2),    
    PARTITION m3 VALUES IN (3),    
    PARTITION m4 VALUES IN (4),    
    PARTITION m5 VALUES IN (5),    
    PARTITION m6 VALUES IN (6),    
    PARTITION m7 VALUES IN (7),    
    PARTITION m8 VALUES IN (8),    
    PARTITION m9 VALUES IN (9),    
    PARTITION m10 VALUES IN (10),    
    PARTITION m11 VALUES IN (11),    
    PARTITION m12 VALUES IN (12)
    );

Changer le nom de la table, modifier la structure de la table

RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;

Importer les données originales

insert into xxx select * from xxx_DELETEDxxx_DELETED;

OK, tout est fait, le l'ensemble du processus prend 50 minutes, la structure de la table d'opérations générales change et l'importation des données après le basculement de basculement MMM, Le temps d'arrêt réel n'inclut pas le temps de modification du champ de partition de la structure de la table, seul le temps de basculement de basculement est de 30 secondes

Partition MySQL, j'ai lu les informations officielles en anglais, le niveau de traduction est limité, certaines ne sont pas traduites en chinois, et sont postées directement en anglais.
Table de partition à 1 liste

mysql> CREATE TABLE `eh` (
    ->   `id` int(11) NOT NULL,
    ->   `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,
    ->   `ENTITLEMENT_ID` bigint(20) NOT NULL,
    ->   `USER_ID` bigint(20) NOT NULL,
    ->   `DATE_CREATED` datetime NOT NULL,
    ->   `STATUS` smallint(6) NOT NULL,
    ->   `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `DATE_MODIFIED` datetime NOT NULL,
    ->   PRIMARY KEY (`DATE_MODIFIED`,`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    -> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))
    -> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,
    ->  PARTITION m2 VALUES IN (2) ENGINE = InnoDB,
    ->  PARTITION m3 VALUES IN (3) ENGINE = InnoDB,
    ->  PARTITION m4 VALUES IN (4) ENGINE = InnoDB,
    ->  PARTITION m5 VALUES IN (5) ENGINE = InnoDB,
    ->  PARTITION m6 VALUES IN (6) ENGINE = InnoDB,
    ->  PARTITION m7 VALUES IN (7) ENGINE = InnoDB,
    ->  PARTITION m8 VALUES IN (8) ENGINE = InnoDB,
    ->  PARTITION m9 VALUES IN (9) ENGINE = InnoDB,
    ->  PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
    ->  PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
    ->  PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.10 sec)

Table de partition à 2 rangs

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

3 créer une plage en utilisant moins de caractères

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)

PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

modifier la structure de la table, ajouter un nouveau bloc de partition

ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
 PARTITION p3 VALUES LESS THAN ('u'),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

4 Partitionnement des colonnes de liste

character column
CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),
    PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo')
);

colonne de date

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

5 Partitionnement HASH

int column,it can use digital function
CREATE TABLE employeesint (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(MOD(store_id,4))
PARTITIONS 4;

Si vous n'incluez pas de clause PARTITIONS, le nombre de partitions est par défaut de 1. ci-dessous :

CREATE TABLE employeestest (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id);

colonne de date

CREATE TABLE employees2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

tronquer toutes les lignes de données : altérer la table rcx truncate PARTITION;

6 Partitionnement de HASH LINÉAIRE

CREATE TABLE employees_linear (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION PAR HASH LINÉAIRE (ANNÉE (embauché))
PARTITIONS 4;

Étant donné une expression expr, la partition dans laquelle l'enregistrement est stocké lorsque le hachage linéaire est utilisé est numéro de partition N parmi num partitions, où N est dérivé selon l'algorithme suivant :
(1) Trouvez la prochaine puissance de 2 supérieure à num. Nous appelons cette valeur V, elle peut être calculée comme :
V ; = PUISSANCE(2, CEILING(LOG(2, num)))
(Supposons que num soit 13. Alors LOG(2,13) ​​​​​​est 3,7004397181411. CEILING(3,7004397181411) vaut 4, et V = PUISSANCE(2 ,4 ), qui vaut 16.)
(2) Définissez N = F(column_list) & (V - 1>(3) Tandis que N >= num :
Définissez V = CEIL(). V/2)
Ensemble N = N & (V - 1)

[Note] Le principe de calcul de & en SQL est : Par exemple,
Convertissez le décimal en binaire et vous obtenez http://www.php.cn/

Alignez d'abord vers la droite, par exemple, devenez 0011 et 1000, jugez en fonction du numéro de chaque chiffre, si les deux valent 1, alors le résultat La position correspondante est 1, sinon c'est 0
Si c'est 1011 et 1000, le résultat est 1000
Si c'est 0110 et 1010, le résultat est 0010
Mais 3 c'est 0011, 8 c'est 1000, donc le résultat de 3&8 est 0

CEILING(X) CEIL(X) : renvoie la plus petite valeur entière qui n'est pas inférieure à X.

LOG(X) LOG(B,X) : Si elle est appelée avec un paramètre, cette fonction renverra le logarithme naturel de X.
POWER(X,Y) : renvoie la valeur résultante de X élevée à la puissance Y.

Méthode de calcul de la tuile dans laquelle les données sont distribuées :
Supposons que la table t1, utilisant un partitionnement de hachage linéaire et comportant 6 partitions, soit créée à l'aide de cette instruction :

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

Supposons maintenant que vous souhaitiez insérer deux enregistrements dans t1 ayant les valeurs de colonne col3 '2003-04-14' et '1998-10-19'. Le numéro de partition pour le premier d'entre eux. ceux-ci sont déterminés comme suit :

 V = POWER(2, CEILING( LOG(2,6) )) = 8
 N = YEAR('2003-04-14') & (8 - 1)
    = 2003 & 7
    = 3
 (3 >= 6 is FALSE: record stored in partition #3)

Le numéro de la partition où est stocké le deuxième enregistrement est calculé comme indiqué ici :

 V = 8 N = YEAR('1998-10-19') & (8-1)   = 1998 & 7   = 6
 (6 >= 6 is TRUE: additional step required)
 N = 6 & CEILING(8 / 2)   = 6 & 3   = 2
 (2 >= 6 is FALSE: record stored in partition #2)

L'avantage du partitionnement par hachage linéaire est que l'ajout, la suppression, la fusion et le fractionnement de partitions sont beaucoup plus rapides, ce qui peut être bénéfique lorsqu'il s'agit de tables contenant des quantités extrêmement importantes (téraoctets) de données. l'inconvénient est que les données sont moins susceptibles d'être réparties uniformément entre les partitions par rapport à la distribution obtenue en utilisant un partitionnement par hachage classique.

Une des questions : Comment MySQL utilise-t-il un SQL sans utiliser de table temporaire ? Supprimer le champ de partition ? Convertir une table partitionnée en table normale ?

Ce qui précède explique comment modifier le champ de partition en ligne dans la partition de la table de partition MySQL. Nous en apprendrons davantage sur la partition (1) plus tard. attention à PHP pour plus de contenu connexe. Site chinois (www.php.cn) !