Tables partitionnées dans MySQL : introduction détaillée et techniques d'optimisation

À mesure que la quantité de données continue de croître, le stockage et l'interrogation de données dans MySQL deviennent de plus en plus difficiles. La table de partition est une fonction de la base de données MySQL, qui peut résoudre le problème du volume de données important et de la lenteur des requêtes. Cet article présentera en détail la table de partition dans MySQL et fournira plusieurs conseils d'optimisation.

1. Qu'est-ce que la table de partition MySQL ?

La table de partition MySQL est une fonctionnalité introduite après la version 5.1 de MySQL. Elle permet de diviser une grande table en plusieurs petites sous-tables. Les données de chaque sous-table peuvent être séparées, stockées et interrogées selon des conditions spécifiées. Par exemple, les données de transaction d'un utilisateur peuvent être séparées en différents sous-tableaux par année ou par mois. Chaque sous-table peut être gérée indépendamment, améliorant considérablement l'efficacité des requêtes et de la maintenance.

2. Pourquoi devez-vous utiliser la table de partition MySQL ?

1. Améliorer l'efficacité des requêtes

Normalement, une faible efficacité des requêtes se produit lorsque de grandes tables stockent des données. Lorsque la quantité de données est importante, l'opération de requête prendra beaucoup de temps et occupera une grande quantité de ressources système. L'utilisation de tables partitionnées permet d'effectuer des opérations de requête uniquement sur des sous-tables spécifiques, améliorant ainsi considérablement l'efficacité des requêtes.

2. Réduire les coûts de stockage

L'utilisation de tables de partition peut séparer les données en différentes sous-tables pour le stockage, réduisant ainsi l'espace de stockage de chaque table de données. Cela réduit les coûts de stockage.

3. Entretien pratique

Chaque sous-table peut être entretenue indépendamment sans utiliser la table entière, ce qui rend la maintenance plus pratique.

3. Comment créer une table de partition MySQL ?

Le processus de création d'une table partitionnée est similaire à la création d'une table normale. La différence réside dans la nécessité de spécifier la méthode et les champs de partitionnement. Par exemple, nous créons une table d'enregistrement de transactions partitionnée par date. Le code est le suivant :

CREATE TABLE trade_records
(
id INT(11) NOT NULL AUTO_INCREMENT,
trade_time DATETIME NOT NULL,
trade_amount INT(11) NOT NULL,
PRIMARY KEY (id, trade_time)
)
PARTITION BY RANGE (YEAR(trade_time))
(
PARTITION p0 VALUES LESS THAN (2015),
PARTITION p1 VALUES LESS THAN (2016),
PARTITION p2 VALUES LESS THAN (2017),
PARTITION p3 VALUES LESS THAN (2018),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

Dans ce code, lorsque nous créons la table, nous utilisons la clause PARTITION BY RANGE et précisons que le champ trade_time est utilisé comme base. et l'année est utilisée. Et cinq sous-tables sont utilisées pour le partitionnement, de 2015 à durée illimitée. De plus, une clé primaire commune est spécifiée dans le code pour garantir l'unicité entre le champ de clé de partition et la clé primaire.

En plus du partitionnement par plage, vous pouvez également partitionner par liste ou par hachage. En prenant le mode liste comme exemple, nous créons une table d'enregistrement de transactions partitionnée par région. Le code est le suivant :

CREATE TABLE trade_records
(
    id INT(11) NOT NULL AUTO_INCREMENT,
    trade_time DATETIME NOT NULL,
    trade_amount INT(11) NOT NULL,
    location VARCHAR(50) NOT NULL,
    PRIMARY KEY (id, trade_time)
)
PARTITION BY LIST (location)
(
    PARTITION p_domestic VALUES IN ('Shanghai', 'Beijing'),
    PARTITION p_hongkong VALUES IN ('Hong Kong'),
    PARTITION p_others VALUES IN (DEFAULT)
);

Dans ce code, nous utilisons la clause PARTITION BY LIST lors de la création de la table et spécifions le champ de localisation comme base. , répartis selon les régions. Trois sous-tables sont utilisées pour le partitionnement. Parmi elles, la sous-table par défaut p_others peut recevoir des régions autres que les partitions nommées.

4. Compétences d'optimisation de la table de partition MySQL

1. Divisez raisonnablement le nombre de partitions

Lors de la division des partitions, il doit être déterminé en fonction de la situation réelle. Il est généralement recommandé de le contrôler autour de 10-20. Un trop grand nombre de sous-tables augmente les coûts de maintenance et nécessite plus de temps pour exécuter les requêtes.

2. Utilisez la clé de partition appropriée

Le choix d'une clé de partition appropriée peut améliorer l'efficacité des requêtes. Si la clé de partition sélectionnée peut diviser les données en différentes sous-tables, seules les sous-tables correspondantes doivent être accessibles pendant la requête, ce qui peut réduire considérablement le temps de requête. Toutefois, si vous choisissez une clé de partitionnement qui ne partitionne pas efficacement les données, la durée de la requête augmentera.

3. Évitez les requêtes entre partitions

Les requêtes entre partitions peuvent impliquer plusieurs sous-tables, ce qui réduira l'efficacité. Par conséquent, lorsque vous effectuez des requêtes, évitez autant que possible les requêtes entre partitions.

4. Entretenir régulièrement la table de partition

Bien que la table de partition puisse réduire les coûts de stockage et faciliter la maintenance, en raison de l'utilisation de plusieurs sous-tables, le temps de requête augmentera également en conséquence. Par conséquent, la table doit être conservée avant l'interrogation, par exemple en supprimant les données inutiles ou en optimisant l'index pour améliorer l'efficacité des requêtes.

5. Utilisez les outils officiellement fournis par MySQL pour l'optimisation

MySQL Official fournit de nombreux outils et astuces qui peuvent être utilisés pour optimiser les performances des tables partitionnées. Par exemple, utilisez l'outil EXPLAIN officiellement fourni pour analyser les problèmes de performances dans les instructions de requête ; utilisez l'outil pt-online-schema-change pour modifier la table de partition afin d'éviter tout impact sur la table pendant le processus de modification.

En bref, le partitionnement des tables est une méthode importante pour l'optimisation de MySQL. En divisant raisonnablement les sous-tables, en sélectionnant les clés de partition appropriées et en entretenant régulièrement les tables, l'efficacité des requêtes peut être considérablement améliorée et les coûts de stockage peuvent être réduits. Cependant, l'utilisation de tables de partition présente également des inconvénients et, en même temps, certains principes et précautions doivent être suivis pour garantir son fonctionnement normal.

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!