Le partitionnement des tables de base de données peut-il améliorer l'efficacité de l'insertion ?

Le partitionnement des tables de base de données peut améliorer l'efficacité de l'insertion ; le partitionnement des tables de base de données peut améliorer l'efficacité des ajouts, suppressions, modifications et requêtes de tables. Le principe de l'opération d'insertion de partition de table de base de données : lors de l'écriture d'un enregistrement, la couche de partition ouvre et verrouille toutes les tables sous-jacentes, puis détermine quelle partition accepte l'enregistrement, puis écrit l'enregistrement dans la table sous-jacente correspondante.

Qu'est-ce qu'une cloison ?

Le partitionnement consiste à décomposer la table selon des règles et à diviser les données en plusieurs emplacements de stockage, qui peuvent être sur le même disque ou sur des machines différentes. Après le partitionnement, il reste une table en surface, mais les données sont hachées vers plusieurs emplacements. Lorsque l'application lit et écrit, elle fonctionne toujours sur le nom de la grande table et la base de données organise automatiquement les données partitionnées.

Les partitions peuvent être divisées en deux types :

1. Partitionnement horizontal (partitionnement horizontal)

Cette forme de partitionnement consiste à partitionner les lignes du tableau. De cette manière, les ensembles de données séparés par des colonnes physiques dans différents groupes peuvent être combinés pour effectuer un partitionnement individuel (partition unique) ou collectif (une ou plusieurs partitions). . Toutes les colonnes définies dans le tableau se trouvent dans chaque ensemble de données,

donc les caractéristiques du tableau sont toujours conservées.

Un exemple simple : une table contenant dix années d'enregistrements de factures peut être divisée en dix partitions différentes, chaque partition contenant les enregistrements d'une des années. (Remarque : nous parlerons de la méthode de partitionnement spécifique utilisée ici plus tard. Nous pouvons d'abord dire une chose, elle doit être divisée par une certaine colonne d'attribut. Par exemple, la colonne utilisée ici est l'année)

2. Partitionnement vertical (Partitionnement vertical)

Cette méthode de partitionnement réduit généralement la largeur de la table cible en divisant la table verticalement, de sorte que certaines colonnes soient divisées en partitions spécifiques, et chaque partition contient les lignes correspondant aux colonnes.

Prenons un exemple simple : une table contient de grandes colonnes de texte et BLOB. Ces colonnes de texte et BLOB ne sont pas fréquemment consultées. À l'heure actuelle, ces colonnes de texte et BLOB rarement utilisées doivent être divisées en une autre partition. vitesse d’accès tout en assurant la corrélation de leurs données.

Principe de la table de partition

La table de partition est implémentée par plusieurs tables sous-jacentes liées. Ces tables sous-jacentes sont également représentées par des objets handle, nous avons donc. peut également accéder directement à chaque partition. Le moteur de stockage gère les tables sous-jacentes de la partition de la même manière que la gestion des tables ordinaires (toutes les tables sous-jacentes doivent utiliser le même moteur de stockage. L'index de la table de partition sert simplement à ajouter un index identique). à chaque table sous-jacente. Du point de vue du moteur de stockage, la table sous-jacente n'est pas différente d'une table ordinaire, et le moteur de stockage n'a pas besoin de savoir s'il s'agit d'une table ordinaire ou d'une partie d'une table partitionnée.

L'opération sur la table de partition est effectuée selon la logique de fonctionnement suivante :

sélectionner la requête :

Lors de l'interrogation d'une table de partition, la couche de partition s'ouvre et se verrouille d'abord all La table sous-jacente, l'optimiseur détermine si certaines partitions peuvent être filtrées, puis appelle l'interface du moteur de stockage correspondante pour accéder aux données de chaque partition

opération d'insertion :

Lors de l'écriture d'un enregistrement, la couche de partition Ouvrez et verrouillez toutes les tables sous-jacentes, puis déterminez quelle partition accepte cet enregistrement, puis écrivez l'enregistrement dans la table sous-jacente correspondante

opération de suppression :

Lorsqu'un enregistrement est supprimé, le couche de partition Ouvrez et verrouillez d'abord toutes les tables sous-jacentes, puis déterminez la partition correspondant aux données, et enfin supprimez la table sous-jacente correspondante

opération de mise à jour :

Lors de la mise à jour d'une donnée, la partition la couche est ouverte en premier et verrouille toutes les tables sous-jacentes. MySQL détermine d'abord dans quelle partition se trouve l'enregistrement qui doit être mis à jour, puis extrait les données et les met à jour, puis détermine dans quelle partition les données mises à jour doivent être placées, puis écrit dans laquelle la table sous-jacente et met à jour les données d'origine. Supprimez la table sous-jacente là où elle se trouve

Bien que chaque opération ouvre et verrouille toutes les tables sous-jacentes, cela ne signifie pas que la table de partition verrouille la table entière pendant le traitement. Si le moteur de stockage peut implémenter des verrous au niveau des lignes, comme innodb, le verrou de table correspondant sera libéré au niveau de la partition. Ce processus de verrouillage et de déverrouillage est similaire aux requêtes sur Innodb ordinaire.

Dans le scénario suivant, le cloisonnement peut jouer un très grand rôle :

A : La table est trop grande pour contenir tout le monde. Il fait chaud données en mémoire, ou seulement dans la dernière partie de la table, et le reste sont des données historiques

B : Les données de la table de partition sont plus faciles à conserver. Par exemple, si vous souhaitez supprimer une grande quantité. de données par lots, vous pouvez utiliser la méthode d'effacement de la partition entière. De plus, vous pouvez également optimiser, vérifier, réparer et d'autres opérations sur une partition indépendante

C : Les données de la table de partition peuvent être distribuées sur différents appareils physiques, utilisant ainsi efficacement plusieurs périphériques matériels

D : Vous pouvez utiliser des tables de partition pour éviter certains goulots d'étranglement particuliers, tels que : l'accès mutuellement exclusif à un seul index dans innodb, la concurrence de verrouillage d'inode dans le système de fichiers ext3, etc.

E : Si nécessaire, vous peut également sauvegarder et restaurer le partitionnement indépendant, qui fonctionne très bien dans des scénarios avec de très grands ensembles de données

F : Optimisez la requête. Lorsque la colonne de partition est incluse dans la clause Where, vous ne pouvez utiliser que les partitions nécessaires pour améliorer l'efficacité de la requête. En même temps, lors de l'interrogation de fonctions d'agrégation telles que sum() et count(). , vous pouvez ajouter chacune des partitions. Les partitions sont traitées en parallèle et, à la fin, seuls les résultats de toutes les partitions doivent être résumés.

La partition de la base de données MySQL traite toujours null comme une valeur plus petite que toute valeur non nulle. C'est la même chose que l'opération d'ordre dans la base de données qui gère les valeurs nulles lors du tri par ordre croissant. est toujours au premier plan. Par conséquent, pour différents types de partitions, la base de données MySQL gère null différemment.

Pour le partitionnement par plage, si null est inséré dans la colonne de partition, la base de données MySQL placera la valeur dans la partition la plus à gauche. Notez que si la partition est supprimée, tout le contenu sous la partition sera supprimé du disque. , la partition où se trouve null est supprimée et la valeur null est également supprimée.

Pour utiliser null dans la partition de liste, il doit être explicitement défini dans la valeur de hachage de la partition, sinon une erreur sera signalée lors de l'insertion de null. Les partitions de hachage et de clé gèrent null différemment des partitions de plage et de liste. Toute fonction de partition renverra null sous la forme 0.

Partition

Le partitionnement consiste à diviser la base de données ou ses éléments constitutifs en différentes parties indépendantes

--C'est une méthode de pré-organisation du stockage de table

mysql prend en charge le partitionnement horizontal

Distribuer des lignes de table spécifiques sous forme de sous-ensembles de lignes

La distribution des partitions s'effectue sur le stockage physique

--telle que définie par l'utilisateur en cas de besoin Règles de spécification pour

- Chaque partition est stockée comme sa propre unité

Division des données

- Les données sont divisées en sous-ensembles

– Type et expression de partition font partie de la définition du tableau

– L'expression peut être un entier ou une fonction qui renvoie une valeur entière.

--Cette valeur détermine dans quelle partition chaque enregistrement sera stocké selon la définition

1. La clé primaire et la clé unique doivent être incluses dans la clé de partition, sinon la clé primaire. Une clé et une clé unique seront créées. "ERREUR 1503 (HY000)" sera signalée lors de l'indexation

2. Lors de l'ajout d'une partition à une partition de plage, vous ne pouvez ajouter la partition qu'après la valeur maximale

<.>3. Le moteur de toutes les partitions doit être le même

4. Champs de partition de partitionnement : entier, expression numérique, colonne de date, expression de fonction de date (telle que year(), to_days(), to_seconds( ), unix_timestamp())

Gestion des partitions

Ajouter une partition

ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

Supprimer la partition

– lorsqu'une partition est supprimée, toutes les données de la partition sont également supprimées.

ALTER TABLE sale_data DROP PARTITION p201010;

Fusion de partitions

Le SQL suivant fusionne p201001 – p201009 en 3 partitions p2010Q1 – p2010Q3

ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);

Recommandations associées : "

PHP tutoriel》, "tutoriel mysql"

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!