Introduction détaillée à information_schema dans MySQL

La base de données

information_schema est la base de données fournie avec le système MySQL, qui permet d'accéder aux métadonnées de la base de données. On a l'impression que information_schema est comme une encyclopédie d'instances MySQL, enregistrant la plupart des informations que nous devons résoudre dans la base de données, telles que jeu de caractères , autorisations liées, entité de base de données objet informations, inspection externe Contraintes, partitions, tables compressées, informations sur les tables, informations index, paramètres, optimisations, verrous et transactions, etc. Grâce à information_schema, nous pouvons voir l'état d'exécution de l'ensemble de l'instance MySQL, comprendre les informations de base de l'instance MySQL et même optimiser et maintenir la base de données. Cela peut être considéré comme une véritable encyclopédie, haha.

Ce qui suit est une classification générale de ces tableaux basée sur quelques petites expériences que j'ai apprises, afin qu'elle convienne à tout le monde. S'il y a des lacunes, veuillez les signaler et je les modifierai dès que possible. possible.

1 : Tables système liées aux jeux de caractères et aux classements

CHARACTER_SETS : Stocke les informations sur les jeux de caractères liées à la base de données (mémoire moteur de stockage)

COLLATIONS : Les règles de tri correspondant au jeu de caractères

COLLATION_CHARACTER_SET_APPLICABILITY : C'est une correspondance entre un jeu de caractères et la relecture de connexion C'est tout

Parlons de la différence entre les jeux de caractères et les classements :

Les jeux de caractères (jeux de caractères) stockent des chaînes, qui font référence à la plus petite chaîne de langage humain Symboles significatifs. Par exemple, 'A', 'B', etc. ;

Les règles de classement comparent les chaînes. Les classements font référence à des règles de comparaison entre les caractères du même jeu de caractères

Chaque séquence de caractères Cela correspond uniquement à un jeu de caractères, mais un jeu de caractères peut correspondre à plusieurs séquences de caractères, dont l'une est la séquence de caractères par défaut (Collation par défaut)

Le nom de la séquence de caractères dans MySQL suit la convention de dénomination : correspondant à la séquence de caractères Commence avec le nom du jeu de caractères ; se termine par _ci (indiquant le respect de la casse), _cs (indiquant le respect de la casse) ou _bin (indiquant la comparaison par valeur de codage). Par exemple : Dans la séquence de caractères "utf8_general_ci", les caractères "a" et "A" sont équivalents

Regardez les variables MySQL liées au jeu de caractères et au classement  :

character_set_server : le jeu de caractères de fonctionnement interne par défaut

character_set_client : le jeu de caractères utilisé par les données source du client

character_set_connection : jeu de caractères de la couche de connexion

character_set_results : RequêteJeu de caractères du résultat

character_set_database : Le jeu de caractères par défaut de la base de données actuellement sélectionnée

character_set_system : Jeu de caractères des métadonnées du système (nom du champ, etc.)

Regardez chez MySQL à nouveau Le processus de conversion du jeu de caractères dans :

(1) Lorsque le serveur MySQL reçoit la requête, il convertit les données de la requête de Character_set_client en

(2). opérations, les données de la demande sont converties de Character_set_connection en un jeu de caractères de fonctionnement interne, et la méthode de détermination est la suivante :

Utilisez la valeur de réglage CHARACTER SET de chaque champ de données

If ; la valeur ci-dessus n'existe pas, utilisez la valeur de paramètre DEFAULT de la table de données correspondante CHARACTER SET (extension MySQL, standard non SQL)

Si la valeur ci-dessus n'existe pas, utilisez la valeur de paramètre DEFAULT CHARACTER SET ; de la base de données correspondante ;

Si la valeur ci-dessus n'existe pas, utilisez alors caractère_set_server pour définir la valeur.

(3). Convertissez les résultats de l'opération du jeu de caractères d'opération interne en Character_set_results.

2 : Quelques tables liées aux autorisations :

SCHEMA_PRIVILEGES : Fournit les autorisations pertinentes pour la base de données. Cette table est une table mémoire de MySQL. Extrait de la base de données.

TABLE_PRIVILEGES : Fournit des informations relatives aux autorisations de table, qui sont chargées à partir de la table mysql.tables_priv

COLUMN_PRIVILEGES : Cette table peut être effacée. peut voir l'objet utilisateur autorisé par la table, la base de données de la table et les autorisations accordées. Si vous ajoutez l'option with grant lors de l'autorisation, nous pouvons voir que la valeur de PRIVILEGE_TYPE doit être OUI.

USER_PRIVILEGES : Fournit des informations relatives aux autorisations de la table. Les informations sont chargées à partir de la table mysql.user

À travers la table, nous pouvons voir clairement le niveau de MySQL. niveaux d'autorisation, SCHEMA, TABLE, COLUMN, bien sûr, tous sont accordés en fonction de l'utilisateur. On constate que l'autorisation MySQL est également assez détaillée et peut être spécifique aux colonnes. Cela reste très utile dans certains scénarios d'application, comme l'audit.

3 : Quelques tables qui stockent les objets d'entité du système de base de données :

COLUMNS : stocke les informations de champ de la table, tous les moteurs de stockage

INNODB_SYS_COLUMNS : stocke les métadonnées d'INNODB, qui s'appuie sur la statistique SYS_COLUMNS existe sur le tableau.

MOTEURS : type de moteur, s'il prend en charge ce moteur, description, s'il prend en charge les choses, s'il prend en charge les transactions distribuées, s'il peut prendre en charge le point de restauration des choses

ÉVÉNEMENTS : enregistre les événements dans MySQL, similaires aux tâches planifiées

FILES : this Cette table fournit des informations sur les fichiers stockés dans l'espace table MySQL et l'emplacement de stockage des fichiers. Les données de cette table sont extraites de la mémoire d'InnoDB, donc cette table elle-même est également une table mémoire, extrayez-la à nouveau chaque fois. au moment où vous redémarrez. C'est la table INNODB_SYS_DATAFILES dont nous allons parler ci-dessous. Une autre chose à noter est que cette table contient des informations de table temporaires, elle n'est donc pas équivalente à la table SYS_DATAFILES. Vous devez toujours consulter INNODB_SYS_DATAFILES. Si l'espace table d'annulation est également configuré avec InnoDB, il sera également enregistré.

PARAMÈTRES : La table des paramètres stocke certains paramètres des procédures stockées et des méthodes, ainsi que les informations sur la valeur de retour des procédures stockées. Le stockage et les méthodes sont stockés dans ROUTINES.

PLUGINS : Fondamentalement, des informations sur le plug-in MySQL, s'il est actif statut et d'autres informations. En fait, SHOW PLUGINS extrait lui-même des données morales via ce tableau

ROUTINES : quelques informations sur les procédures stockées et les fonctions de méthode, mais ces informations n'incluent pas celles définies par l'utilisateur, juste quelques informations sur le système.

SCHEMATA : Ce tableau indique le nombre de bases de données qu'il y a sous l'instance et le jeu de caractères par défaut de la base de données

TRIGGERS : Ceci. Les enregistrements de table sont les informations du trigger, y compris toutes les informations associées. Système et propres déclencheurs créés par l'utilisateur.

VUES : les informations de vue sont également les informations de base sur la vue du système et de l'utilisateur.

Ces tables stockent les objets d'entité de certaines bases de données, qui nous permettent d'interroger et de gérer facilement. Pour un administrateur de base de données, ces tables peuvent grandement faciliter notre travail et rendre plus rapide et plus pratique la complétion et l'interrogation de la base de données. Informations connexes.

4 : Quelques tables liées aux clés étrangères de contrainte :

REFERENTIAL_CONSTRAINTS : Cette table fournit des informations relatives aux clés étrangères, et uniquement liées aux clés étrangères informations

TABLE_CONSTRAINTS : Cette table fournit des informations sur les contraintes associées

INNODB_SYS_FOREIGN_COLS : Cette table stocke également INNODB sur les clés étrangères. Les informations sur les métadonnées sont cohérentes avec les informations stockées dans SYS_FOREIGN_COLS

INNODB_SYS_FOREIGN : les informations de métadonnées stockées dans INNODB sur les clés étrangères sont cohérentes avec les informations stockées dans SYS_FOREIGN_COLS, mais uniquement pour For INNODB

KEY_COLUMN_USAGE : Toutes les colonnes contraintes de la base de données seront enregistrées, et le nom et la catégorie de la contrainte seront également enregistrés

Pourquoi les clés étrangères et les contraintes devraient-elles être répertoriées séparément ? Parce que cela semble être une chose indépendante. Bien que la plupart de nos environnements de production n'utilisent pas de clés étrangères car cela réduirait les performances, c'est toujours une bonne idée d'utiliser les options de contraintes de manière raisonnable, telles que les contraintes uniques.

5 : Quelques tables sur la gestion :

GLOBAL_STATUS, GLOBAL_VARIABLES, SESSION_STATUS, SESSION_VARIABLES : Ce sont les quatre tables enregistrez respectivement les variables et l'état du système (informations globales et de session). En tant qu'administrateur de base de données, je pense que tout le monde les connaît, et ces tables sont également rechargées au redémarrage du système. C'est-à-dire la table mémoire.

PARTITIONS : Informations relatives à la table de partition MySQL. Grâce à cette table, nous pouvons interroger les informations associées à la partition (tables partitionnées dans la base de données, ainsi que les partitions de la table de partition. et les informations sur les données de chaque partition), veuillez vous référer à MySQL Partition Management

pour plus de détails sur les partitions

PROCESSLIST : show processlist extrait en fait les données de cette table, et les données de PROCESSLIST en sont la base. Puisqu'il s'agit d'une table mémoire, nous équivalent à une requête en mémoire, et ces opérations sont très rapides.

INNODB_CMP_PER_INDEX, INNODB_CMP_PER_INDEX_RESET : Ces deux tables stockent des informations sur la compression de la table d'informations INNODB, sur l'intégralité de la table et des informations d'index. sachez que pour une table compressée INNODB, les données et l'index secondaire seront compressés, car les données elles-mêmes peuvent également être considérées comme un index clusterisé. Il y a une brève introduction aux tables de compression dans la série information_schema 11.

INNODB_CMPMEM, INNODB_CMPMEM_RESET : Ces deux tables stockent des informations sur le pool de tampons sur les pages compressées de MySQL INNODB, mais une chose à noter est que ces deux tables sont utilisées pour collecter toutes les informations lors de l'utilisation du table, cela aura un impact sérieux sur les performances, il est donc désactivé par défaut. Si nous voulons activer cette fonction, nous devons définir le paramètre innodb_cmp_per_index_enabled sur ON.

INNODB_BUFFER_POOL_STATS : Le tableau fournit des informations sur le pool de tampons d'INNODB, qui sont les mêmes que les informations fournies par show engine innodb status. C'est également la source d'informations pour afficher le statut innodb du moteur.

INNODB_BUFFER_PAGE_LRU, INNODB_BUFFER_PAGE : informations conservées relatives à INNODB LRU LIST Pour plus de détails, veuillez consulter les notes de l'éditeur explication du pool de tampons innodb

INNODB_BUFFER_PAGE : Cette table est plutôt sympa, elle stocke les données de la page mises en mémoire tampon dans le tampon. L'interrogation de cette table aura un impact sérieux sur les performances. N'exécutez pas cette instruction sur notre propre bibliothèque de production à moins que vous puissiez accepter une courte pause dans le service. Pour plus de détails, veuillez consulter les notes de l'éditeur sur le pool de tampons innodb

. INNODB_SYS_DATAFILES : Cette table est une correspondance entre l'emplacement de stockage des fichiers de la table enregistrée et l'espace table (INNODB)

INNODB_TEMP_TABLE_INFO : Cette table enregistre les enregistrements Toutes les informations utilisées par tous les utilisateurs d'INNODB ne peut être enregistré qu'en mémoire et n'a aucune persistance.

INNODB_METRICS : Fournit divers indices de performances d'INNODB, qui est un complément à INFORMATION_SCHEMA et collecte les statistiques du système MySQL. Ces statistiques peuvent être configurées manuellement pour être activées ou désactivées. Les paramètres suivants sont contrôlables : innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all.

INNODB_SYS_VIRTUAL : La table stocke des informations sur les colonnes virtuelles de la table INNODB. Bien sûr, cela est relativement simple Dans MySQL 5.7, deux types de colonnes générées sont pris en charge, à savoir les colonnes générées virtuelles. Colonne et colonne générée stockée, la première enregistre uniquement la colonne générée dans le dictionnaire de données (métadonnées de la table) et ne conserve pas cette colonne de données sur le disque ; la seconde conservera la colonne générée sur le disque au lieu de la lire. à chaque fois. Il est calculé lors de la prise. Évidemment, ce dernier stocke des données qui peuvent être calculées à partir de données existantes, nécessite plus d'espace disque et n'a aucun avantage par rapport au stockage réel d'une colonne de données. Par conséquent, dans MySQL 5.7, le type de colonne générée n'est pas spécifié et la valeur par défaut est. C'est une colonne virtuelle.

INNODB_CMP, INNODB_CMP_RESET : stocke les informations pertinentes sur la compression de la table d'informations INNODB. Veuillez consulter les notes recommandées pour plus de détails.

Pourquoi ces tables sont-elles répertoriées comme tables liées à la gestion ? Parce que je pense qu'elles sont comme des tables de connexion, de partition, de compression, de pool de tampons innodb et d'autres tables. Nous pouvons clairement voir la corrélation de notre base de données à travers ces tables. État de la fonction, notamment grâce à certaines variables, il nous est plus facile de voir l'état d'exécution de MySQL, ce qui facilite notre gestion. Les notes pertinentes incluent l'explication recommandée du pool de tampons innodb, la gestion des partitions MySQL et la série information_schema 11. Ce sont toutes mes propres notes.

6 : Quelques tableaux sur les informations de table et les informations d'index

TABLES, TABLESPACES, INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES :

TABLES Là Il ne fait aucun doute que cette table correspond aux informations de table enregistrées dans la base de données, y compris les bases de données système et les bases de données créées par l'utilisateur. La source de l'affichage de l'état de la table comme 'test1'G est cette table

TABLESPACES est l'espace table actif marqué. Cette table ne fournit pas d'informations sur l'espace table innodb, et elle ne nous est pas d'une grande utilité, car notre bibliothèque de production est obligée d'utiliser INNODB

;

INNODB_SYS_TABLES Cette table repose sur son extraction du dictionnaire de données SYS_TABLES. Ce tableau fournit des informations sur le format et les caractéristiques de stockage de la table , y compris le format des lignes, la taille de la page compressée et les informations au niveau des bits (le cas échéant).

fournit des informations sur l'espace table sur INNODB. En fait, cela est cohérent avec les informations INNODB dans SYS_TABLESPACES.

STATISTIQUES : Cette table fournit des informations d'index sur la table et des informations associées sur tous les index.

INNODB_SYS_INDEXES : Fournit des informations relatives à l'index de la table INNODB. Les informations stockées dans la table SYS_INDEXES sont fondamentalement les mêmes, sauf que cette dernière fournit des informations d'index pour tous les moteurs de stockage. ce dernier fournit uniquement des informations d'index pour la table INNODB.

INNODB_SYS_TABLESTATS :

Cette table est plus importante. Elle enregistre les informations de la table INNODB de MySQL et l'optimiseur MySQL estimera les informations d'index appropriées pour la sélection SQL. , Ce sont les informations statistiques de la Base de données MySQL

Les enregistrements de cette table sont enregistrés dans la mémoire. Elle sera à nouveau enregistrée après chaque redémarrage, elle peut donc être enregistrée. enregistrez uniquement les données de la dernière fois. Statistiques de la base de données après le redémarrage. Avec cette table, il est plus pratique pour nous de maintenir l'index. Nous pouvons interroger le nombre d'utilisations de l'index, faciliter le nettoyagesupprimerles index rarement utilisés et améliorer l'efficacité de la mise à jour de la table. insérer etc., économiser de l'espace disque.

INNODB_SYS_FIELDS : Cette table enregistre les informations sur les champs d'index de la table INNODB et le classement des champs

INNODB_FT_CONFIG : Cette table stocke les informations d'index en texte intégral

INNODB_FT_DEFAULT_STOPWORD : Cette table stocke les informations sur les mots vides et est utilisée pour correspondre à l'index de texte intégral, qui est le même que celui d'innodb. 🎜>INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD Ce STOPWORD doit être créé avant de créer l'index, et le champ doit être spécifié comme varchar. Le mot vide est ce que nous appelons les mots vides. Lors de la recherche en texte intégral, la liste des mots vides sera lue et récupérée sous différents jeux de caractères et méthodes de tri, l'accès échouera ou les données ne seront pas trouvées, selon le mot vide. façons d’ordonner les mots. Nous pouvons utiliser cette fonctionnalité pour filtrer les champs inutiles.

INNODB_FT_INDEX_TABLE : Cette table stocke les informations d'utilisation de l'index sur la table INNODB avec index en texte intégral. De même, cette table ne peut être utilisée qu'après avoir défini innodb_ft_aux_table. 🎜>

INNODB_FT_INDEX_

CACHE : Cette table stocke les informations d'enregistrement avant l'insertion, et permet également d'éviter une réorganisation coûteuse de l'index pendant DML

7 : À propos quelques tableaux liés à l'optimisation MySQL

OPTIMIZER_TRACE

 : Fournit des informations générées par la fonction de suivi de l'optimisation À ce sujet, j'ai également fait un petit test, MySQL Tracking Optimizer Trial <.>PROFILAGE :

SHOW PROFILE peut fournir une vue approfondie de l'état de fonctionnement des instructions d'exécution du serveur. Et cela peut également vous aider à comprendre le temps nécessaire pour exécuter les instructions. Certaines limitations sont la fonctionnalité qu'il n'implémente pas, l'incapacité d'afficher et d'analyser les instructions provenant d'autres connexions et le coût encouru lors de l'analyse.

SHOW PROFILES affiche plusieurs instructions récemment envoyées au serveur. Le nombre d'instructions est défini en fonction de la variable de session profiling_history_size. La valeur par défaut est 15 et la valeur maximale est 100. Le mettre à 0 équivaut à désactiver la fonction d’analyse. Pour plus de détails, voir le profil MySQL

INNODB_FT_BEING_

DELETE

D,INNODB_FT_DELETED : INNODB_FT_BEING_DELETED Cette table est un instantané de INNODB_FT_DELETED, uniquement dans OPTIMIZE TABLE. ne doit être utilisé que lorsque . Pour des informations détaillées, veuillez consulter mon brief OPTIMIZE TABLE 8 : Quelques tables liées aux transactions et verrous MySQL

INNODB_LOCKS

 : Obtenez-le maintenant. , mais n'inclut pas les verrous qui n'ont pas été acquis et concerne uniquement INNODB.

INNODB_LOCK_WAITS

 : informations relatives à l'attente de verrouillage du système, y compris les enregistrements d'une ou plusieurs lignes bloquées, ainsi que les informations de verrouillage des demandes de verrouillage et des demandes de modification bloquées, etc.

INNODB_TRX

 : contient des informations relatives à toutes les transactions en cours d'exécution (INNODB) et indique si la transaction est bloquée ou nécessite un verrou.

Nous pouvons facilement interroger les choses inachevées et les processus bloqués via ces tables. N'est-ce pas plus pratique pour plus de détails, voir la série information_schema huit (choses, verrous)

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!