Compréhension des lignes de données MySQL et des mécanismes de débordement de lignes

La colonne

tutoriel vidéo mysql présente les lignes de données et les mécanismes de débordement de lignes.

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

1. Quels sont les formats de lignes ?

Vous pouvez consulter vos paramètres de format de ligne MySQL comme ci-dessous.

En fait, les lignes de données MySQL ont deux formats, l'un est le format Compact dans l'image et l'autre est le format Redondant.

Compact est un format de ligne compact, conçu pour permettre de stocker davantage de lignes de données dans une seule page de données.

Vous avez un avant-goût, à quel point il est excitant de pouvoir stocker plus de lignes de données dans une seule page de données. MySQL lit les données du disque en unités de pages de données si cela peut être fait dans une seule page de données. s'il y a plus de rangées, cela ne signifierait-il pas que moins d'espace est utilisé et que l'efficacité globale monte en flèche ?

Présentation officielle du site : Compact peut économiser 20 % de stockage par rapport au format redondant.

Compact a été introduit à partir de MySQL5.0 Après MySQL5.1, le format de ligne est défini par défaut sur Compact. Par conséquent, ce que décrit cet article concerne également le format Compact.

2. A quoi ressemble le format ligne compacte ?

Vous devez savoir que certaines colonnes du tableau peuvent être nulles et que certaines colonnes sont des types varchar de longueur variable.

Comment le format de ligne Compact organise et décrit ces informations ? Comme indiqué ci-dessous :

Chaque partie peut contenir plus de données que les 1, 2 et 3 que j'ai marqués ci-dessus.

Afin de vous donner une sensation et une compréhension plus intuitives, je viens de sélectionner une partie à vous montrer.

3. Quelle quantité de données une seule ligne de MySQL peut-elle stocker ?

Dans les paramètres MySQL, une seule ligne de données peut stocker jusqu'à 65 535 octets de données (notez qu'il s'agit d'octets et non de caractères)

Mais lorsque vous créez une table de données comme suit Une erreur s'est produite :

MySQL n'autorise pas la création d'une colonne d'une longueur de 65535 octets, car chaque ligne de la page de données a la colonne cachée que nous mentionné ci-dessus.

Réduisez donc la longueur de varchar à 65532 octets pour réussir à créer la table

Notez que 65535 ici fait référence à des octets, pas à des caractères.

Donc, si vous changez le jeu de caractères au format d'encodage utf8, alors le N dans varchar(N) fait en fait référence à N caractères, pas à N octets. Donc, si vous créez le tableau comme ci-dessous, vous obtiendrez une erreur.

Si encode=utf8, trois octets représentent un caractère. Alors 65535/3 = 21845 caractères.

4. Comment est le format Compact compact ?

MySQL effectue des lectures d'E/S aléatoires à chaque fois

Par défaut, la taille de la page de données est de 16 Ko. Plusieurs lignes sont stockées dans la page de données.

Cela signifie que plus il y a de lignes de données pouvant être stockées dans une page de données, moins MySQL effectuera de temps d'E/S dans son ensemble ? Les performances sont plus rapides ?

L'idée d'implémentation du format Compact est la suivante : lorsque le type de colonne est VARCHAR, VARBINARY, BLOB ou TEXT, les données de la colonne dépassant 768 octets sont placées dans d'autres pages de données.

Comme indiqué ci-dessous :

Est-il clair de voir toute l'histoire ici ?

MySQL fait cela pour empêcher efficacement qu'une seule colonne varchar ou une colonne Texte soit trop grande, ce qui entraîne trop peu d'enregistrements de lignes stockés dans une seule page de données, provoquant une montée en flèche des E/S et une occupation de la mémoire.

5. Qu'est-ce que le débordement de ligne ?

Alors, qu'est-ce que le débordement de ligne ?

Si la taille par défaut de la page de données est de 16 Ko, convertie en octet : 16*1024 = 16384 octets

Alors avez-vous remarqué qu'il y a une différence entre les 16384 octets qui peuvent être stockés dans une seule page et le maximum de 65 535 octets pouvant être stockés sur une seule ligne. Combien de fois ?

En d'autres termes, si la ligne de données que vous souhaitez stocker dépasse 65532 octets, vous ne pourrez pas l'écrire. Si la ligne de données que vous souhaitez stocker fait moins de 65 535 octets mais est supérieure à 16 384 octets, vous pouvez l'insérer avec succès, mais une page de données ne peut pas stocker les données que vous avez insérées. A ce moment-là, la file d'attente va définitivement déborder !

En fait, dans les paramètres MySQL, le débordement de ligne ne se produit que lorsque le bord de 16 384 octets est atteint.

Pour les lignes de types varchar, texte, etc. Un débordement de ligne se produit lorsque la longueur de ce stockage de colonne atteint plusieurs centaines d'octets.

6. Ligne Comment déborder ?

Regardez cette image :

Dans les paramètres MySQL, lorsque la longueur de la colonne varchar atteint 768 octets, les 768 premiers octets de la colonne seront traités comme préfixe Stocké dans les lignes, les données excédentaires débordent et sont stockées dans la page de débordement, puis les deux sont associées via un pointeur de décalage. Il s'agit du mécanisme de débordement de ligne.

7. Pensez à une question

Je me demande si vous avez déjà pensé à une telle question :

Tout d'abord, vous devez savoir que MySQL utilise l'index clusterisé B + Tree, dans ce B + Tree, les nœuds non-feuilles stockent uniquement les index mais pas les données, et les nœuds feuilles stockent des données réelles. En même temps, les nœuds feuilles pointent vers la page de données.

Alors lorsqu'une seule ligne ne peut pas être stockée, pourquoi ne pas la stocker dans deux pages de données ? Tout comme l'image ci-dessous ~.

S'il est stocké sur un seul nœud, j'utiliserai plusieurs nœuds pour stocker la banque principale ! Peut-être que mon B+Tee peut devenir plus grand et plus grand de cette façon (c'est en fait une fausse idée)

La carte cérébrale correspondante à cette mauvaise description est la suivante :

La raison pour laquelle MySQL ne fait pas cela est la suivante :

MySQL veut stocker plus de lignes de données dans une page de données, et il doit stocker au moins deux lignes de données. Sinon, la signification de B+Tree sera perdue. B+Tree dégénère également en une liste chaînée inefficace.

Vous pouvez goûter cette phrase bleue. Lorsqu'il dit que chaque page de données doit stocker au moins deux lignes de données, il ne veut pas dire que la page de données ne peut pas stocker une seule ligne. Vous pouvez vraiment simplement y écrire une ligne de données, puis aller prendre un repas et faire autre chose. Il n'y a toujours qu'une seule ligne de données dans cette page de données.

Ce que cette phrase signifie, c'est que lorsque vous écrivez une ligne de données sur cette page de données, même si elle est très volumineuse, elle atteindra la limite de la page de données, mais via le mécanisme de débordement de ligne. Il est toujours garanti que vos prochaines données pourront être écrites sur cette page de données.

La carte mentale correcte est la suivante :

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!