Compréhension approfondie du débordement de lignes de données MySQL

Cet article vous apporte une compréhension approfondie du débordement de lignes de données MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

1. Commençons par les erreurs courantes

Au début de l'histoire, regardons un message d'erreur SQL courant :

Je pense que vous avez dû rencontrer ce type d'erreur à plusieurs reprises, en particulier pour les BG comme OMG, qui ont pour principal travail la production de contenu dans le stockage des lignes de contenu, les données. doit être vaste. C'est un sujet incontournable. La « grandeur » des données va ici bien au-delà du grand espace de stockage occupé. Elle inclut également le grand espace de stockage d'un seul champ (de table), la longue durée de conservation des données, la grande redondance des données, le grand volume causé par l'incohérence. des données chaudes et froides, et les problèmes d'accès. La valeur maximale change considérablement avec les points chauds, et un traitement logique complexe conduit à une pression de stockage de données amplifiée, etc. Revenons à la question du rapport d'erreurs, examinons d'abord la structure de ce tableau :

En voyant cela, je pense que chacun aura une réponse différente opinions Nous ne comparerons pas ici les avantages et les inconvénients des différentes méthodes de traitement. Nous décrirons uniquement les deux méthodes de traitement les plus fréquemment utilisées.

• Selon le guide des erreurs, changez les deux grands varchar (22288) en texte et blob

• Selon les caractéristiques de l'entreprise, réduisez le taille de varchar Longueur de stockage, ou divisé en plusieurs petits vachar et char selon les règles

Ces deux méthodes de traitement ont également leurs propres avantages et inconvénients Changer le champ en texte ou non en blob. ne fait qu'augmenter Avec l'augmentation de la capacité de stockage des données, seule l'indexation de préfixe ou de texte intégral peut être utilisée pour la page d'index de ce champ. Si le côté commercial stocke les données au format json, c'est un bon choix pour 5.7 de prendre en charge le type de données json. , qui peut être interrogé pour une seule sous-catégorie et une seule sortie. De même, s'il est réduit et divisé, cela dépendra davantage des scénarios commerciaux et des exigences logiques. La logique utilisée par l'entreprise doit être modifiée et le volume d'ingénierie doit également être évalué.

2. Exploration approfondie

Ensuite, analysons en profondeur certains concepts déroutants sur la taille limite « 65535 ».

1. "65535" n'est pas la limite maximale de N dans un seul varchar(N), mais le nombre total d'octets de champs de type champ non volumineux dans la table entière.

--------------------------------------------- -------------------------------------------------- --

Chaque table (quel que soit le moteur de stockage) a une taille de ligne maximale de 65 535 octets. Les moteurs de stockage peuvent imposer des contraintes supplémentaires sur cette limite, réduisant ainsi la taille maximale effective des lignes.

-- -------------------------------------------------- ---------------------------------------------

2. Le jeu de caractères aura un impact sur la capacité de stockage maximale du champ. Par exemple, les caractères UTF8 nécessitent 3 octets pour être stockés. Pour les colonnes VARCHAR (255) CHARACTER SET UTF8, il occupera 255×3 = 765 octets. Par conséquent, le tableau ne peut pas contenir plus de 65 535/765=85 colonnes. GBK est sur deux octets et ainsi de suite.

3. Les colonnes de longueur variable doivent également prendre en compte le nombre d'octets qui stockent la longueur réelle de la colonne lors de l'évaluation de la taille du champ. Par exemple, la colonne VARCHAR (255) CHARACTER SET UTF8 nécessite deux octets supplémentaires pour stocker les informations de longueur de valeur, donc la colonne nécessite jusqu'à 767 octets pour stocker. En fait, elle peut stocker jusqu'à 65 533 octets, et les deux restants. les octets stockent les informations de longueur.

4. Les colonnes BLOB, TEXT et JSON sont différentes des champs tels que varchar et char. Les informations sur la longueur des colonnes sont stockées indépendamment de la longueur de la ligne et peuvent atteindre 65 535 octets de stockage réel. >5. La définition de colonnes NULL réduira le nombre maximum de colonnes autorisées.

Table InnoDB, les tailles de stockage des colonnes NULL et NOT NULL sont les mêmes
Table MyISAM, la colonne NULL nécessite un espace supplémentaire pour enregistrer si sa valeur est NULL. Chaque NULL nécessite un bit supplémentaire (arrondi à l'octet le plus proche). La longueur maximale de ligne est calculée comme suit :
longueur de ligne = 1 + (somme des longueurs de colonnes) + (nombre de colonnes NULL + delete_flag + 7)/8 + (nombre de variables -length columns )

Table statique, delete_flag = 1, la table statique identifie si la ligne a été supprimée en enregistrant un peu dans la ligne.
Table dynamique, delete_flag = 0, ce flag est stocké au début de la ligne dynamique, la table dynamique peut être spécifiée selon
6. Pour la table InnoDB, les tailles de stockage des colonnes NULL et NOT NULL sont les mêmes

7. InnoDB permet à une seule table d'avoir jusqu'à 1 000 colonnes

8. Les clés primaires Varchar ne prennent en charge que non. plus de 767 octets ou 768/2 = 384 champs sur deux octets ou 767/3 = 255 champs sur trois octets, tandis que GBK est sur deux octets et UTF8 sur trois octets.

Différents moteurs ont des index différents. restrictions

La longueur de chaque colonne d'innodb ne peut pas être supérieure à 767 octets ; la somme des longueurs de toutes les colonnes d'index ne peut pas être supérieure à 3072 octets
La longueur de chaque colonne de myisam ne peut pas être supérieure à 1000 octets, la somme des longueurs de toutes les colonnes d'index ne peut pas être supérieure à 1000 octets

3. La vraie faute

Parlons de la faute commerciale rencontrée aujourd'hui Un grand nombre des erreurs suivantes se sont produites dans l'industrie en ligne, empêchant le programme d'écrire. données :

En suivant les invites et la réflexion normale, notre première réaction est que l'entreprise a les problèmes suivants :

• Les champs de la structure de table définie dépassent la limite

• La longueur des données insérées dans un certain champ dépasse la valeur maximale définie pour le champ modifié

Vérifiez ensuite La structure des tables de base de données de l'entreprise est la suivante :

La première raison a été rapidement éliminée, car l'erreur commerciale n'a pas été signalé lors de la création de la table. S'il s'agit de la somme des champs non volumineux de la table, 65535, une erreur se produira lors de la création de la table et l'entreprise ne signalera l'erreur que lors de l'écriture et via la table de la bibliothèque. structure, nous pouvons également constater qu'un grand nombre de champs de type mediumblob , la somme des champs non grands est bien inférieure à 65535.

Selon le SQL spécifique fourni par l'entreprise, les champs non volumineux tels que appversion, datadata, elt_stamp et id ne dépassent pas la limite. Le champ de type mediumblob peut stocker jusqu'à 16 M de données commerciales. est loin d’atteindre cette ampleur. Selon le message d'erreur, j'ai modifié les champs non volumineux tels que appversion, datadata, elt_stamp et id en type blob, mais cela ne peut toujours pas être résolu. (Selon l'analyse précédente, cela ne doit pas être la source du problème).

Après m'être calmé, j'ai découvert qu'il y avait en fait un autre détail qui avait été ignoré. Le taux d'échec de l'entreprise n'est pas de 100%, ce qui signifie qu'il y a encore des requêtes réussies et des requêtes échouées. J'ai trouvé qu'il y avait effectivement une différence dans la quantité de données ou un champ de type mediumblob. Donc, la première chose qui me vient à l'esprit maintenant est de savoir si le paramètre max_allowed_packet a été ajusté à la baisse. Oui, une seule requête dépasse la taille et est rejetée. J'ai vérifié la valeur configurée (comme indiqué ci-dessous). la longueur des données de SQL est loin. Ce n'est pas si grand, donc cette raison est exclue.

Après avoir vérifié ici, nous excluons essentiellement plusieurs problèmes courants, puis examinons les limites d'un autre paramètre : innodb_page_size, celui-ci La valeur par défaut est de 16 Ko, avec deux lignes de données par page, donc chaque ligne contient un maximum de 8 Ko de données.

Après avoir vérifié la table de données, Row_format est Compact, nous pouvons alors en déduire que la cause du problème devrait être Le format de stockage d'approche par défaut d'innodb stockera les 864 premiers octets de chaque blob champ dans la page, donc si le blob dépasse un certain nombre, la taille d'une seule ligne dépassera 8k, donc une erreur sera signalée. En comparant le SQL réussi et échoué de la rédaction commerciale, cette inférence a également été appliquée. Alors, comment résoudre ce problème maintenant ?

• Table fractionnée d'entreprise, les grands champs sont stockés dans des tables séparées

• Résolvez le problème en résolvant la méthode de stockage de Row_format

  En raison du nombre d'éléments stockés dans une seule table métier n'est pas important et la logique métier n'est pas adaptée au fractionnement, nous devons donc résoudre ce problème sur Row_format.

Le format de fichier Barracuda comporte deux nouveaux formats d'enregistrement de ligne, compressé et dynamique. Les deux nouveaux formats utilisent une méthode de débordement de ligne complète pour stocker les données BLOB. la page de données et les données réelles sont stockées dans la page BLOB. Une autre caractéristique du format d'enregistrement de ligne compressé est que les données qui y sont stockées seront compressées à l'aide de l'algorithme zlib.

Les opérations de modification associées sont relativement simples :

1 Modifier les variables globales MySQL :

SET GLOBAL innodb_file_format. ='Barracuda';

2. Modifiez en douceur les attributs de la table d'origine :

ROW_FORMAT=COMPRESSED

4. Continuer à apprendre

À travers ce cas, nous pouvons extraire deux points qui méritent d'être étudiés en profondeur :

1. À propos de innodb_page_size

À partir de MySQL 5.6, innodb_page_size peut définir la page de données Innodb sur 8K, 4K et la valeur par défaut est 16K. Ce paramètre doit être ajouté à my.cnf lors de l'initialisation initiale. Si la table a été créée puis modifiée, une erreur sera signalée au démarrage de MySQL.

Alors que dois-je faire si je dois modifier cette valeur avant la version 5.6 ? La seule façon est de travailler sur le code source, puis de reconstruire MySQL.

UNIV_PAGE_SIZE est la taille de la page de données. La valeur par défaut est 16K. Cette valeur peut être définie à la puissance 2. Cette valeur peut être définie sur 4k, 8k, 16k, 32K, 64K. En même temps, après avoir modifié UNIV_PAGE_SIZE, vous devez modifier UNIV_PAGE_SIZE_SHIFT. La valeur est UNIV_PAGE_SIZE à la puissance 2, donc les paramètres des pages de données sont les suivants :

Parlons ensuite de l'impact de la définition de innodb_page_size sur différentes valeurs sur les performances de MySQL. La table testée contient 100 millions d'enregistrements et la taille du fichier est de 30 Go. .

①Scénario de lecture et d'écriture (50 % de lecture, 50 % d'écriture)

16K, moins de pression sur le CPU, une moyenne de 20%

8K, la pression du CPU est 30 % ~ 40 %, mais le débit de sélection est supérieur à 16K

②Scénario de lecture (lecture à 100 %)

La différence entre 16K et 8K n'est pas évidente

InnoDB Buffer La page de gestion du Pool elle-même a également un coût. Plus il y a de Pages, plus la liste de gestion sera longue et de même taille. Par conséquent, lorsque notre ligne de données elle-même est relativement longue (insertion de gros blocs), des pages plus grandes sont plus propices à l'amélioration de la vitesse, car plus de lignes peuvent être placées sur une seule page, et la taille de chaque écriture d'E/S est plus grande et moins d'IOPS écrit plus de données. . Lorsque la longueur de la ligne dépasse 8 Ko, s'il s'agit d'une page de 16 Ko, certains types de chaînes seront forcés d'être convertis en TEXTE et le corps principal de la chaîne sera transféré vers la page d'extension, ce qui nécessitera une IO supplémentaire pour lire le colonne, et une page plus grande. Des longueurs de ligne plus grandes sont prises en charge, et les pages de 64 Ko peuvent prendre en charge des longueurs de ligne d'environ 32 Ko sans utiliser de pages d'extension. Cependant, s'il s'agit d'une lecture et d'une écriture aléatoires de lignes courtes, il n'est pas approprié d'utiliser une page aussi grande, ce qui entraînera une diminution de l'efficacité des E/S, et les E/S de grande taille ne peuvent lire qu'une petite partie.

2. À propos de Row_format

Le moteur de stockage Innodb enregistre les enregistrements sous forme de lignes. Avant la version 1.0.x d'InnoDB, le moteur de stockage InnoDB fournissait deux formats : Compact et Redondant pour stocker les données d'enregistrement de ligne. Le innodb_plugin de MySQL 5.1 introduit un nouveau format de fichier : Barracuda, qui a deux nouveaux formats de lignes : compressé et dynamique. Et compact et redondant sont collectivement appelés Antelope. Vous pouvez utiliser la commande SHOW TABLE STATUS LIKE 'table_name' ; pour afficher le format de ligne utilisé par la table actuelle, où la colonne row_format indique le type de structure d'enregistrement de ligne actuellement utilisé.

Dans la version MySQL 5.6, la version Compact par défaut, msyql 5.7.9 et versions ultérieures, le format de ligne par défaut est déterminé par la variable innodb_default_row_format, la valeur par défaut est DYNAMIC, vous pouvez également spécifier ROW_FORMAT=DYNAMIC lors de la création de la table (cela peut être utilisé pour ajuster dynamiquement le format de stockage de la table). Si vous souhaitez modifier le mode ligne d'une table existante en compressé ou dynamique, vous devez d'abord définir le format de fichier sur Barracuda (set global innodb_file_format=Barracuda;). Utilisez ensuite ALTER TABLE tablename ROW_FORMAT=COMPRESSED; pour le modifier afin qu'il prenne effet, sinon la modification ne sera pas valide et il n'y aura aucune invite.

①compact

Si la longueur de la valeur de la colonne blob est >, alors les 768 premiers octets sont toujours sur la page de données et les autres sont placés sur la page de débordement. (hors page), comme indiqué ci-dessous :

Les types de champs blob ou de longueur variable mentionnés ci-dessus incluent les types de champs blob, texte et varchar, où la longueur de la valeur de la colonne varchar est supérieure à un certain nombre N. Les pages de débordement seront stockées sous le jeu de caractères latin1, la valeur N peut être calculée comme suit : La taille de bloc par défaut d'innodb est de 16 Ko. Depuis le stockage innodb. La table moteur est une table organisée en index, les nœuds feuilles au bas de l'arborescence sont des listes chaînées bidirectionnelles, donc chaque page a au moins deux lignes d'enregistrements, ce qui détermine que lorsque innodb stocke une ligne de données, il ne peut pas dépasser 8 Ko, moins le nombre d'octets occupés par les autres valeurs de colonne, qui est approximativement égal à N.

②compressé ou dynamique

Utiliser le débordement de ligne complet pour le blob, c'est-à-dire que l'enregistrement d'index clusterisé (page de données) ne conserve qu'un pointeur de 20 octets, pointant vers l'adresse du segment de débordement où il est réellement stocké :

Format de ligne dynamique, le fait que le stockage des colonnes soit placé sur la page hors page dépend principalement de taille de ligne, la ligne la plus grande sera placée dans la ligne. La colonne la plus longue est placée hors page jusqu'à ce que la page de données puisse stocker les deux lignes suivantes. Les colonnes TEXT/BLOB sont toujours stockées dans la page de données lorsque

compressé est similaire à dynamique dans sa structure physique, mais les lignes de données de la table sont compressées et stockées à l'aide de l'algorithme zlib. Utilisé lorsqu'il existe de nombreux types de colonnes blob longues, il peut réduire l'utilisation de hors-page et réduire l'espace de stockage (environ 50 %, veuillez vous référer au précédent rapport "[Rapport d'évaluation de la base de données] Numéro 3 : InnoDB, TokuDB Compression Performance" Test résultats), mais nécessite un processeur plus élevé. Le pool de mémoire tampon peut stocker à la fois des versions compressées et non compressées des données, il occupe donc également plus de mémoire.

Enfin, j'ai fait référence à "High Performance MySQL" et j'ai donné quelques suggestions sur l'utilisation de types de champs longs variables tels que BLOB :

① Les grands champs peuvent gaspiller beaucoup d'espace dans InnoDB. Par exemple, si la valeur du champ stocké n'est qu'un octet de plus que ce que requiert la ligne, la page entière sera utilisée pour stocker les octets restants, gaspillant ainsi la majeure partie de l'espace de la page. De même, si vous avez une valeur qui ne dépasse que légèrement la taille de 32 pages, 96 pages seront réellement utilisées.

②Une valeur trop longue peut empêcher l'index d'être utilisé comme condition WHERE dans la requête, ce qui entraînerait une exécution lente. MySQL doit lire toutes les colonnes avant d'appliquer la condition WHERE, cela peut donc amener MySQL à demander à InnoDB de lire une grande partie du stockage étendu, puis de vérifier la condition WHERE et de supprimer toutes les données inutiles.

③ Il existe de nombreux grands champs dans une table. Il est préférable de les combiner et de les stocker dans une seule colonne. Il est préférable que tous les grands champs partagent un espace de stockage étendu plutôt que d'avoir chaque champ ayant sa propre page.

④ Utilisez COMPRESS() pour compresser des champs volumineux, puis enregistrez-les sous forme de BLOB, ou compressez-les dans l'application avant de les envoyer à MySQL. Vous pouvez obtenir des avantages d'espace et des gains de performances significatifs.

⑤ Le stockage étendu désactive le hachage adaptatif, car toute la longueur de la colonne doit être complètement comparée pour savoir si les données sont correctes.

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!