Explication approfondie des index et des structures MySQL

B-tree

B-Tree est également appelé arbre de recherche multi-chemins équilibré (non binaire). L'utilisation de la structure B-tree peut être significative. réduire Localisez le processus intermédiaire rencontré lors de l'enregistrement, accélérant ainsi l'accès.
Valeur clé du nœud enfant gaucheL'algorithme de récupération des données par clé dans B-Tree est très intuitif : effectuez d'abord une recherche binaire à partir du nœud racine, s'il est trouvé. Ensuite, le les données du nœud correspondant sont renvoyées, sinon le nœud pointé par le pointeur de l'intervalle correspondant est recherché de manière récursive jusqu'à ce que le nœud soit trouvé ou que le pointeur nul soit trouvé. La première recherche réussit et la seconde échoue.

(la clé est la valeur clé de l'enregistrement. Pour différents enregistrements de données, la clé est différente les unes des autres ; les données sont les données de l'enregistrement de données à l'exception de la clé)

B tree

B Tree est un B-tree amélioré.

(la clé est la valeur clé de l'enregistrement. Pour différents enregistrements de données, la clé est différente les unes des autres ; les données sont les données de l'enregistrement de données à l'exception de la clé)

Semblable à B-Tree Comparé à, B Tree présente les différences suivantes :

• La limite supérieure du pointeur de chaque nœud est 2d au lieu de 2d 1.

• Les nœuds internes ne stockent pas de données, seuls les nœuds feuilles ne stockent pas de pointeurs.

Alors pourquoi la base de données utilise-t-elle B-tree

Le disque mécanique de l'ordinateur Afin d'amortir le temps d'attente des mouvements mécaniques, le disque va accéder à plusieurs ? éléments de données à la fois. Pas un, une telle unité d'information lue à la fois est une page. Nous pouvons utiliser le nombre de pages lues ou écrites comme approximation principale de l'accès au disque temps total. . A tout moment, les algorithmes de B Tree n'ont tous besoin de conserver qu'un certain nombre de pages en mémoire. La conception de B-tree prend en compte la pré-lecture du disque Un nœud B-tree est généralement aussi grand qu'une page (page) de disque complète, et la taille de la page de disque limite les enfants d'un B-. Le nœud d'arbre peut contenir. Le nombre (facteur de branchement), bien sûr, cela dépend aussi de la taille d'un mot-clé par rapport à une page.

Afin de minimiser les opérations d'E/S, les lectures sur disque sont lues à l'avance à chaque fois, et la taille est généralement un multiple entier de la page. Même si un seul octet doit être lu, le disque lira une page de données (généralement 4 Ko) et la placera dans la mémoire. La mémoire et le disque échangent des données en unités de pages. Parce que le principe de localité veut que lorsqu’une donnée est habituellement utilisée, les données proches seront également utilisées immédiatement.

B-Tree : Si une récupération nécessite l'accès à 4 nœuds, le concepteur du système de base de données utilise le principe de lecture anticipée du disque pour concevoir la taille du nœud comme une page, puis la lecture d'un nœud n'en nécessite qu'un seul. Opération /O , pour terminer cette opération de récupération, jusqu'à 3 E/S sont nécessaires (le nœud racine réside en mémoire). Plus l'enregistrement de données est petit, plus de données sont stockées dans chaque nœud, plus la hauteur de l'arborescence est petite, moins il y a d'opérations d'E/S et l'efficacité de la récupération augmente.

Arbre B : les nœuds non-feuilles stockent uniquement les clés, ce qui réduit considérablement la taille des nœuds non-feuilles, de sorte que chaque nœud peut stocker plus d'enregistrements L'arborescence est plus courte et les opérations d'E/S sont moindres<.>. B Tree a donc de meilleures performances.

Qu'est-ce qu'un index ?

Un index est simplement une structure de données.

Le coût de l'indexation

L'indexation a également un coût : le fichier d'index lui-même consomme de l'espace de stockage et l'index augmente la charge d'insertion, de suppression et de modification des enregistrements. De plus, MySQL. des ressources sont également consommées pour maintenir les index, donc plus d'index n'est pas toujours mieux. Généralement, il n'est pas recommandé de construire un index dans deux circonstances

Le premier cas est que les enregistrements de la table sont relativement petits
L'autre cas où il n'est pas recommandé de construire un index est que la sélectivité de l'index est faible. La soi-disant sélectivité d'index (Sélectivité) fait référence au rapport entre les valeurs d'index uniques (également appelées cardinalité) et le nombre d'enregistrements de table (#T)

Catégorie d'index

1 . Index ordinaire

2. Index unique
3. Index de clé primaire
4. Index combiné

Index utilisé dans MySQL

B Tree est couramment utilisé comme index dans MySQL. , Mais L'implémentation diffère selon l'index clusterisé et l'index non clusterisé.

Index clusterisé et index non clusterisé

Le soi-disant index clusterisé signifie que le fichier d'index principal et le fichier de données sont le même fichier. L'index clusterisé est principalement utilisé dans le moteur de stockage Innodb. Dans cette implémentation d'index, les données sur les nœuds feuilles de B Tree sont les données elles-mêmes et la clé est la clé primaire. Comme indiqué ci-dessous :

(table t1)

(table t2)

(fichier correspondant à la base de données)
Car d'InnoDB Les fichiers de données eux-mêmes doivent être agrégés par clé primaire, donc InnoDB exige que la table ait une clé primaire (MyISAM peut ne pas en avoir une si cela n'est pas explicitement spécifié, le système MySQL sélectionnera automatiquement une colonne qui peut identifier de manière unique la). enregistrement de données comme clé primaire. Si aucune colonne de ce type n'existe, alors MySQL génère automatiquement un champ implicite comme clé primaire pour la table InnoDB. La longueur de ce champ est de 6 octets et le type est long.

Les principales différences entre les moteurs de stockage de données MyISAM et InnoDB dans la base de données MySQL

 :
MyISAM n'est pas sécurisé sur le plan transactionnel, tandis qu'InnoDB est sécurisé sur le plan transactionnel.
La granularité des verrous MyISAM est au niveau de la table, tandis qu'InnoDB prend en charge le verrouillage au niveau de la ligne.
MyISAM prend en charge l'index de type texte intégral, mais InnoDB ne prend pas en charge l'index de texte intégral.
MyISAM est relativement simple, il est donc meilleur qu'InnoDB en termes d'efficacité. Les petites applications peuvent envisager d'utiliser MyISAM.
Les tables MyISAM sont enregistrées sous forme de fichiers. L'utilisation du stockage MyISAM dans le transfert de données multiplateforme évitera bien des problèmes.
Les tables InnoDB sont plus sécurisées que les tables MyISAM. Vous pouvez passer des tables non transactionnelles aux tables transactionnelles (alter table tablename type=innodb) tout en garantissant que les données ne seront pas perdues.
Scénario d'application :
MyISAM gère les tables non transactionnelles. Il offre un stockage et une récupération à grande vitesse, ainsi que des capacités de recherche en texte intégral. Si votre application doit effectuer un grand nombre de requêtes SELECT, MyISAM est un meilleur choix.
InnoDB est utilisé pour les applications de traitement de transactions et possède de nombreuses fonctionnalités, notamment la prise en charge des transactions ACID. Si votre application doit effectuer un grand nombre d'opérations INSERT ou UPDATE, vous devez utiliser InnoDB, qui peut améliorer les performances des opérations simultanées multi-utilisateurs.

Supplément

Stockage de la mémoire principale

Processus de récupération
Lorsque le système a besoin de lire la mémoire principale, le signal d'adresse est mis sur le bus d'adresse et transmis au mémoire principale. Une fois que la mémoire principale a lu le signal d'adresse, elle analyse le signal et localise l'unité de stockage spécifiée, puis place les données de cette unité de stockage sur le bus de données pour que d'autres composants puissent les lire.
Le processus d'écriture dans la mémoire principale est similaire. Le système place l'adresse de l'unité et les données à écrire respectivement sur le bus d'adresses et le bus de données. La mémoire principale lit le contenu des deux bus et effectue les opérations d'écriture correspondantes.
On voit ici que le temps d'accès à la mémoire principale n'est que linéairement lié au nombre d'accès. Puisqu'il n'y a pas d'opération mécanique, la "distance" des données accédées deux fois n'aura aucun impact sur le temps. Par exemple, récupérer d'abord La consommation de temps pour récupérer A0 puis A1 est la même que pour récupérer A0 puis D3

Principe d'accès au disque

Lorsque les données doivent être lues à partir du disque, le Le système transmettra l'adresse logique des données au disque, le circuit de contrôle du disque traduit l'adresse logique en une adresse physique selon la logique d'adressage, c'est-à-dire détermine sur quelle piste et sur quel secteur se trouvent les données à lire. Afin de lire les données dans ce secteur, la tête magnétique doit être placée sur ce secteur. Pour y parvenir, la tête magnétique doit se déplacer pour s'aligner avec la piste correspondante. Ce processus est appelé recherche, et le temps passé. est appelé temps de recherche. Ensuite, la rotation du disque Le secteur cible tourne sous la tête. Le temps passé dans ce processus est appelé temps de rotation.

Ce qui précède est une explication approfondie et détaillée de l'index et de la structure MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !