Introduction détaillée aux tables de partition dans MySQL

Signification

MySQL peut définir les données stockées dans chaque partition en utilisant la clause PARTITION BY lors de la création d'une table. Lors de l'exécution d'une requête, l'optimiseur filtre les partitions qui ne contiennent pas les données dont nous avons besoin en fonction de la définition de la partition, de sorte que la requête n'a pas besoin d'analyser toutes les partitions - seules les partitions contenant les données requises peuvent être trouvées.

L'un des principaux objectifs du partitionnement est de stocker les données dans différentes tables avec une granularité plus grossière. Cela permet de stocker les données associées ensemble. De plus, cela sera très pratique lorsque nous souhaitons supprimer par lots les données de la partition entière en une seule fois.

Le partitionnement peut jouer un rôle important dans les scénarios suivants :

• La table est trop grande pour contenir tout la mémoire, ou seulement dans la table. La dernière partie contient un point chaud les données et le reste sont des données historiques

• Les données de la table de partition sont plus faciles à maintenir

• Les données de la table de partition peuvent être distribuées dans différents Sur les appareils physiques

• Les tables de partition peuvent être utilisées pour éviter certains goulots d'étranglement particuliers

• Si nécessaire, des partitions indépendantes peuvent être sauvegardées et restaurées

La table de partition elle-même présente également quelques limitations, les points suivants sont particulièrement importants :

• Une table ne peut avoir qu'un maximum de 1024 partitions

• Dans MySQL 5.1, l'expression de partition doit être un entier ou une expression qui renvoie un entier. Dans MySQL5.5, les colonnes peuvent être utilisées directement pour le partitionnement dans certains scénarios

• Les contraintes de clé étrangère ne peuvent pas être utilisées dans les tables partitionnées

• Si la partition S'il y a des colonnes de clé primaire ou d'index unique dans le champ, alors toutes les colonnes de clé primaire et les colonnes d'index uniques doivent être incluses

Principe de la table partitionnée

Il n'y a aucune différence entre la gestion du moteur de stockage de chaque table sous-jacente dans la partition et 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 est juste à ajouter. un index identique à chaque table sous-jacente. Du point de vue du moteur de stockage, il n'y a aucune différence entre la table sous-jacente et 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.

Les opérations sur la table de partition sont effectuées selon la logique de fonctionnement suivante :

Requête SELECT

Lors de l'interrogation d'une table de partition, la couche de partition s'ouvre d'abord et verrouille tout dans la table des couches inférieures, l'optimiseur détermine d'abord 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 INSÉRER

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

Opération DELETE

Lors de la suppression d'un enregistrement, la partition La couche ouvre et verrouille d'abord toutes les tables sous-jacentes, puis détermine la partition correspondant aux données, et enfin effectue une opération de suppression sur la table sous-jacente correspondante

Opération UPDATE

Lorsqu'un enregistrement est mis à jour, la couche de partition est ouverte en premier et verrouille toutes les tables sous-jacentes. MySQL détermine d'abord dans quelle partition l'enregistrement 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 et enfin dans laquelle écrire. la table sous-jacente et met à jour les données d'origine. Supprimez la table sous-jacente là où elle se trouve.

Ces opérations prennent en charge le filtrage.

Bien que chaque opération "ouvre et verrouille d'abord 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 lui-même des verrous au niveau des lignes, 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.

Types de tables de partition

MySQL prend en charge une variété de tables de partition. La plus courante que nous voyons est le partitionnement basé sur des plages. Chaque stockage de partition se situe dans une certaine plage. . L'expression de partition peut être une colonne ou une expression contenant des colonnes.

Par exemple, le tableau suivant stocke les ventes de chaque année dans différentes partitions :

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION Différentes fonctions peuvent être utilisées dans la clause de partition. Mais il y a une exigence, la valeur renvoyée par l'expression doit être un entier défini et ne peut pas être une constante.

MySQL prend également en charge les valeurs de clé, le partitionnement de hachage et de liste, etc.

Comment utiliser les tables partitionnées

Si nous voulons interroger des enregistrements sur une période donnée à partir d'une très grande table, comment devons-nous interroger cette table et comment pouvons-nous le rendre plus efficace ?

Étant donné que la quantité de données est très importante, nous ne pouvons certainement pas analyser la table entière à chaque fois que nous interrogeons. Compte tenu de la consommation d'espace et de la maintenance des index, nous ne souhaitons pas utiliser d'index. Même si vous utilisez des index, vous constaterez que les données ne sont pas agrégées de la manière souhaitée, ce qui entraîne une grande fragmentation et finit par amener une requête à générer des milliers d'E/S aléatoires. En effet, Lorsque la quantité de données est extrêmement importante, l'index B-Tree ne peut plus fonctionner.

Nous pouvons donc choisir des méthodes plus grossières mais moins coûteuses pour récupérer des données, comme l'indexation uniquement d'un petit morceau de métadonnées correspondantes sur une grande quantité de données.

C'est exactement ce que fait le partitionnement. Comprendre le partitionnement peut être considéré comme la forme initiale d'un index. Parce que les partitions n'ont pas besoin de structures de données supplémentaires pour enregistrer les données dans chaque partition - les partitions n'ont pas besoin de localiser avec précision l'emplacement de chaque élément de données, il n'y a donc pas besoin de structures de données supplémentaires - le coût est donc très faible . Seule une expression simple est nécessaire pour exprimer quelles données sont stockées dans chaque partition.

Afin d'assurer l'évolutivité de grandes quantités de données, il existe généralement deux stratégies :

1. Scanner les données dans leur intégralité sans aucun index : Tant que la condition WHERE peut être utilisée pour limiter les données requises à quelques partitions, l'efficacité est très élevée. L'utilisation de cette stratégie suppose que les données n'ont pas besoin d'être entièrement placées en mémoire et suppose également que toutes les données requises se trouvent sur le disque. La mémoire étant relativement petite, les données seront rapidement extraites de la mémoire, le cache ne jouera donc aucun rôle. Cette stratégie convient lorsque de grandes quantités de données sont accessibles de manière normale.

2. Indexer les données et séparer les points chauds : Si les données présentent des "points chauds" évidents et qu'à l'exception de cette partie des données, d'autres données sont rarement consultées, alors vous pouvez placer ces données de point d'accès dans une partition distincte afin que les données de cette partition puissent être mises en cache en mémoire. De telles requêtes ne peuvent accéder qu'à une petite table partitionnée, peuvent utiliser des index et peuvent également utiliser efficacement le cache.

Dans quelles circonstances des problèmes surviendront-ils

Les deux stratégies de partitionnement présentées ci-dessus reposent sur deux hypothèses très importantes : les requêtes peuvent être filtrées. un grand nombre de partitions supplémentaires et les partitions elles-mêmes n'entraîneront pas beaucoup de coûts supplémentaires.

Il s'avère que ces deux hypothèses peuvent poser problème dans certains scénarios :

• Les colonnes de partition et les colonnes d'index ne correspondent pas : Si défini Le une incompatibilité entre la colonne d'index et la colonne de partition empêchera la requête d'effectuer le filtrage des partitions.

• Choisir une partition peut coûter cher : Différents types de partitions sont implémentés différemment, leurs performances varient donc. En particulier avec le partitionnement par plages, le coût de l'interrogation des partitions auxquelles appartient une ligne qualifiante peut être très élevé car le serveur doit analyser la liste de toutes les définitions de partition pour trouver la bonne réponse.

• Le coût d'ouverture et de verrouillage de toutes les tables sous-jacentes peut être élevé : Lorsqu'une requête accède à une table partitionnée, MySQL doit ouvrir et verrouiller toutes les tables sous-jacentes. est une autre surcharge des tables partitionnées.

• Le coût de maintenance des partitions peut être élevé : Certaines opérations de maintenance de partitions peuvent être très rapides, comme l'ajout ou la suppression de partitions. Certaines opérations, telles que la réorganisation des partitions ou des instructions ALTER similaires, peuvent être très coûteuses car ces opérations nécessitent la copie de données.

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!