Detaillierte Einführung in information_schema in MySQL

Die

information_schema-Datenbank ist die Datenbank, die mit dem MySQL-System geliefert wird, das Zugriff auf Datenbankmetadaten bietet. Es fühlt sich an, als wäre information_schema wie eine Enzyklopädie von MySQL-Instanzen, die die meisten Informationen aufzeichnet, die wir in der Datenbank auflösen müssen, z. B. Zeichensatz , berechtigungsbezogene Informationen, Datenbankentität Objekt Informationen, externe Inspektion Einschränkungen, Partitionen, komprimierte Tabellen, Tabelleninformationen, Index Informationen, Parameter, Optimierungen, Sperren und Transaktionen usw. Durch information_schema können wir den Betriebsstatus der gesamten MySQL-Instanz sehen, die grundlegenden Informationen der MySQL-Instanz verstehen und sogar die Datenbank optimieren und warten. Man kann sagen, dass es sich um eine echte Enzyklopädie handelt, haha.

Das Folgende ist eine allgemeine Klassifizierung dieser Tabellen, die auf einigen kleinen Erfahrungen basiert, die ich gelernt habe, so dass sie für alle praktisch ist. Wenn es Mängel gibt, weisen Sie sie bitte darauf hin und ich werde sie so bald wie möglich ändern möglich.

1: Systemtabellen zu Zeichensätzen und Sortierungen

CHARACTER_SETS: Speichert datenbankbezogene Zeichensatzinformationen (Speicher). Speicher-Engine)

COLLATIONS: Die Sortierregeln, die dem Zeichensatz entsprechen

COLLATION_CHARACTER_SET_APPLICABILITY: Es handelt sich um eine Entsprechung zwischen einem Zeichensatz und dem Verbindungskorrekturlesen Das ist alles

Lassen Sie uns über den Unterschied zwischen Zeichensätzen und Sortierungen sprechen:

Zeichensätze (Zeichensätze) speichern Zeichenfolgen, was sich auf die kleinste Zeichenfolge in bezieht menschliche Sprache Bedeutungsvolle Symbole. Zum Beispiel „A“, „B“ usw.;

Sortierungsregeln beziehen sich auf Vergleichsregeln zwischen Zeichen im gleichen Zeichensatz

Jede Zeichenfolge Es entspricht nur ein Zeichensatz, aber ein Zeichensatz kann mehreren Zeichenfolgen entsprechen, von denen eine die Standardzeichenfolge (Standardsortierung) ist

Der Name der Zeichenfolge in MySQL folgt der Namenskonvention: entsprechend der Zeichenfolge Begins mit dem Zeichensatznamen; endet mit _ci (zeigt die Groß-/Kleinschreibung an), _cs (zeigt die Groß-/Kleinschreibung an) oder _bin (zeigt den Vergleich nach Kodierungswert an). Beispiel: In der Zeichenfolge „utf8_general_ci“ sind die Zeichen „a“ und „A“ gleichwertig

Sehen Sie sich die MySQL--Variablen an, die sich auf Zeichensatz und Sortierung beziehen :

character_set_server: der standardmäßige interne Betriebszeichensatz

character_set_client: der von den Client-Quelldaten verwendete Zeichensatz

character_set_connection: Verbindungsschicht-Zeichensatz

character_set_results: AbfrageErgebniszeichensatz

character_set_database: Der Standardzeichensatz der aktuell ausgewählten Datenbank

character_set_system: Zeichensatz für Systemmetadaten (Feldname usw.)

Look bei MySQL erneut Der Zeichensatzkonvertierungsprozess in:

(1). Bei Operationen werden die Anforderungsdaten von „character_set_connection“ in einen internen Betriebszeichensatz konvertiert. Die Bestimmungsmethode lautet wie folgt:

Verwenden Sie den CHARACTER SET-Einstellungswert jedes Datenfelds.

Wenn Wenn der obige Wert nicht vorhanden ist, verwenden Sie den DEFAULT-Einstellungswert der entsprechenden Datentabelle (MySQL-Erweiterung, Nicht-SQL-Standard).

Wenn der obige Wert nicht vorhanden ist, verwenden Sie den DEFAULT CHARACTER SET-Einstellungswert der entsprechenden Datenbank;

Wenn der oben genannte Wert nicht vorhanden ist, verwenden Sie „character_set_server“, um den Wert festzulegen.

(3). Konvertieren Sie die Operationsergebnisse aus dem internen Operationszeichensatz in „character_set_results“.

2: Einige Tabellen im Zusammenhang mit Berechtigungen:

SCHEMA_PRIVILEGES

: Stellt relevante Berechtigungen für die Datenbank bereit. Diese Tabelle ist eine Speichertabelle von MySQL. Aus der DB herausgezogen.

TABLE_PRIVILEGES

: Stellt Informationen zu Tabellenberechtigungen bereit, die aus der Tabelle mysql.tables_priv geladen werden.

COLUMN_PRIVILEGES

: Diese Tabelle kann von Ihnen gelöscht werden können das von der Tabelle autorisierte Benutzerobjekt, die Tabelle, die Datenbank und die erteilten Berechtigungen sehen. Wenn Sie bei der Autorisierung die Option „with grant“ hinzufügen, können wir sehen, dass der Wert von PRIVILEGE_TYPE YES sein muss.

USER_PRIVILEGES

: Stellt Informationen zu Tabellenberechtigungen bereit. Die Informationen werden aus der Tabelle mysql.user geladen. Über die Tabelle können wir die Ebene von MySQL deutlich erkennen Berechtigungsebenen, SCHEMA-, TABLE- und COLUMN-Ebenen, diese werden natürlich alle basierend auf dem Benutzer gewährt. Es ist ersichtlich, dass die MySQL-Autorisierung auch sehr detailliert ist und spaltenspezifisch sein kann. Dies ist in bestimmten Anwendungsszenarien, beispielsweise bei der Überwachung, immer noch sehr nützlich.

3: Einige Tabellen, die Entitätsobjekte des Datenbanksystems speichern:

COLUMNS: speichert die Feldinformationen der Tabelle, aller Speicher-Engines

INNODB_SYS_COLUMNS: speichert die Metadaten von INNODB, die auf der SYS_COLUMNS-Statistik basieren der Tisch.

ENGINES: Engine-Typ, ob diese Engine unterstützt wird, Beschreibung, ob sie Dinge unterstützt, ob sie verteilte Transaktionen unterstützt, ob sie den Rollback-Punkt von Dingen unterstützen kann

EVENTS: Zeichnet Ereignisse in MySQL auf, ähnlich wie geplante Jobs

FILES : Diese Tabelle enthält Informationen zu den im MySQL-Tabellenbereich gespeicherten Dateien und zum Speicherort der Datei. Die Daten dieser Tabelle werden aus dem InnoDB-Speicher abgerufen, sodass diese Tabelle selbst auch eine Speichertabelle ist Mal neu starten. Das ist die Tabelle INNODB_SYS_DATAFILES, über die wir weiter unten sprechen werden. Beachten Sie außerdem, dass diese Tabelle temporäre Tabelleninformationen enthält und daher nicht der Tabelle SYS_DATAFILES entspricht. Sie müssen sich immer noch INNODB_SYS_DATAFILES ansehen. Wenn der Undo-Tablespace auch mit InnoDB konfiguriert ist, wird er ebenfalls aufgezeichnet.

PARAMETER: Die Parametertabelle speichert einige Parameter von gespeicherten Prozeduren und Methoden sowie Rückgabewertinformationen von gespeicherten Prozeduren. Speicher und Methoden werden in ROUTINEN gespeichert.

PLUGINS: Grundsätzlich Informationen zum MySQL-Plugin, ob es aktiv ist Status und andere Informationen. Tatsächlich zieht SHOW PLUGINS selbst moralische Daten durch diese Tabelle

ROUTINEN: einige Informationen über gespeicherte Prozeduren und Methodenfunktionen, aber diese Informationen umfassen keine benutzerdefinierten, sondern nur einige Informationen darüber das System.

SCHEMATA: Diese Tabelle gibt an, wie viele Datenbanken sich unter der Instanz befinden und den Standardzeichensatz der Datenbank.

TRIGGERS: Dies Tabellendatensätze sind die Informationen des Triggers, einschließlich aller zugehörigen Informationen. System- und eigene vom Benutzer erstellte Trigger.

ANSICHTEN: Die Informationen von Ansicht sind auch die grundlegenden Ansichtsinformationen des Systems und des Benutzers.

Diese Tabellen speichern einige Datenbankentitätsobjekte, die für uns praktisch sind, um sie abzufragen und zu verwalten. Für einen DBA können diese Tabellen unsere Arbeit erheblich erleichtern und das Vervollständigen und Abfragen der Datenbank erheblich erleichtern Information.

4: Einige Tabellen zu Einschränkungsfremdschlüsseln:

REFERENTIAL_CONSTRAINTS: Diese Tabelle enthält Informationen zu Fremdschlüsseln, und zwar nur zu Fremdschlüsseln Informationen

TABLE_CONSTRAINTS: Diese Tabelle enthält zugehörige Einschränkungsinformationen

INNODB_SYS_FOREIGN_COLS: Diese Tabelle speichert auch die Fremdschlüssel von INNODB. Die Metadateninformationen stimmen mit den Informationen überein gespeichert in SYS_FOREIGN_COLS

INNODB_SYS_FOREIGN: Die in INNODB gespeicherten Metadateninformationen zu Fremdschlüsseln stimmen mit den in SYS_FOREIGN_COLS gespeicherten Informationen überein, jedoch nur für Für INNODB

KEY_COLUMN_USAGE: Alle eingeschränkten Spalten in der Datenbank werden gespeichert und der Name und die Kategorie der Einschränkung werden ebenfalls aufgezeichnet

Warum sollten Fremdschlüssel und Einschränkungen separat aufgelistet werden? Es scheint, dass dies eine unabhängige Sache ist. Obwohl die meisten unserer Produktionsumgebungen keine Fremdschlüssel verwenden, da dies die Leistung beeinträchtigt, ist es dennoch eine gute Idee, Einschränkungen wie Eindeutigkeitsbeschränkungen angemessen zu nutzen.

5: Einige Tabellen zum Thema Management:

GLOBAL_STATUS, GLOBAL_VARIABLES, SESSION_STATUS, SESSION_VARIABLES: Dies sind die vier Tabellen Zeichnen Sie die Systemvariablen und den Status (globale und Sitzungsinformationen) auf. Als DBA glaube ich, dass jeder mit ihnen vertraut ist und diese Tabellen auch neu geladen werden, wenn das System neu gestartet wird. Das heißt, die Speichertabelle.

PARTITIONEN: Informationen zur MySQL-Partitionstabelle. Über diese Tabelle können wir die zugehörigen Informationen zur Partition (partitionierte Tabellen in der Datenbank sowie Partitionen der Partitionstabelle) abfragen Weitere Informationen zu Partitionen finden Sie unter MySQL-Partitionsverwaltung

PROCESSLIST: show Processlist ruft tatsächlich Daten aus dieser Tabelle ab, und die Daten von PROCESSLIST sind seine Grundlage. Da es sich um eine Speichertabelle handelt, entsprechen wir einer Abfrage im Speicher, und diese Vorgänge sind sehr schnell.

INNODB_CMP_PER_INDEX, INNODB_CMP_PER_INDEX_RESET: Diese beiden Tabellen speichern Informationen über die Komprimierung der INNODB-Informationstabelle, über die gesamte Tabelle und Indexinformationen Beachten Sie, dass bei einer INNODB-komprimierten Tabelle sowohl die Daten als auch der Sekundärindex komprimiert werden, da die Daten selbst auch als Clustered-Index betrachtet werden können. Eine kurze Einführung in Komprimierungstabellen finden Sie in der information_schema-Reihe 11.

INNODB_CMPMEM, INNODB_CMPMEM_RESET: Diese beiden Tabellen speichern Pufferpoolinformationen über die komprimierten Seiten von MySQL INNODB. Beachten Sie jedoch, dass diese beiden Tabellen zum Sammeln aller Informationen verwendet werden In der Tabelle hat dies erhebliche Auswirkungen auf die Leistung und ist daher standardmäßig deaktiviert. Wenn wir diese Funktion aktivieren möchten, müssen wir den Parameter innodb_cmp_per_index_enabled auf ON setzen.

INNODB_BUFFER_POOL_STATS: Die Tabelle enthält Informationen über den Pufferpool von INNODB, die mit den von show engine innodb status bereitgestellten Informationen identisch sind. Es ist auch die Informationsquelle für den Innodb-Status der Show-Engine.

INNODB_BUFFER_PAGE_LRU, INNODB_BUFFER_PAGE: Gepflegte Informationen in Bezug auf INNODB LRU LIST Weitere Informationen finden Sie in der Erklärung des Herausgebers zum Innodb-Pufferpool

INNODB_BUFFER_PAGE: Diese Tabelle ist ziemlich cool, sie speichert die im Puffer gepufferten Seitendaten. Das Abfragen dieser Tabelle hat schwerwiegende Auswirkungen auf die Leistung. Führen Sie diese Anweisung niemals in unserer eigenen Produktionsbibliothek aus, es sei denn, Sie können eine kurze Pause im Dienst akzeptieren. Weitere Informationen finden Sie in den Hinweisen des Herausgebers zum Innodb-Pufferpool

INNODB_SYS_DATAFILES: Diese Tabelle ist eine Entsprechung zwischen dem Dateispeicherort der aufgezeichneten Tabelle und dem Tabellenbereich (INNODB)

INNODB_TEMP_TABLE_INFO: Diese Tabelle zeichnet Datensätze auf. Alle von verwendeten Informationen Alle Benutzer von INNODB können nur im Speicher aufgezeichnet werden und haben keine Persistenz.

INNODB_METRICS: Stellt verschiedene Leistungsindizes von INNODB bereit, das eine Ergänzung zu INFORMATION_SCHEMA darstellt und MySQL-Systemstatistiken sammelt. Diese Statistiken können manuell so konfiguriert werden, dass sie aktiviert oder deaktiviert werden. Folgende Parameter sind steuerbar: innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all.

INNODB_SYS_VIRTUAL: Die Tabelle speichert Informationen über die virtuellen Spalten der INNODB-Tabelle. In MySQL 5.7 werden natürlich zwei Arten von generierten Spalten unterstützt, nämlich Virtual Generated Spalte und gespeicherte generierte Spalte: Ersteres speichert nur die generierte Spalte im Datenwörterbuch (Metadaten der Tabelle) und speichert diese Datenspalte nicht auf der Festplatte. Letzteres speichert die generierte Spalte auf der Festplatte, anstatt sie zu lesen Es wird jedes Mal bei der Einnahme berechnet. Letzteres speichert offensichtlich Daten, die aus vorhandenen Daten berechnet werden können, erfordert mehr Speicherplatz und hat keinen Vorteil im Vergleich zum tatsächlichen Speichern einer Datenspalte. Daher ist in MySQL 5.7 der Typ der generierten Spalte nicht angegeben und der Standardwert Es handelt sich um eine virtuelle Säule.

INNODB_CMP, INNODB_CMP_RESET: Speichert relevante Informationen zum Komprimieren der INNODB-Informationstabelle. Weitere Informationen finden Sie in den empfohlenen Hinweisen.

Warum werden diese Tabellen als verwaltungsbezogene Tabellen aufgeführt? Weil ich der Meinung bin, dass sie wie Verbindungs-, Partitions-, Komprimierungstabellen, Innodb-Pufferpools und andere Tabellen sind. Wir können die Korrelation unserer Datenbank anhand dieser Tabellen deutlich erkennen .Funktionsstatus, insbesondere durch einige Variablen, ist es für uns einfacher, den Betriebsstatus von MySQL zu erkennen, was unsere Verwaltung erleichtert. Zu den relevanten Hinweisen gehören die empfohlene Erläuterung des Innodb-Pufferpools, die MySQL-Partitionsverwaltung und die Informationsschema-Serie 11. Das sind alles meine eigenen Notizen.

6: Einige Tabellen zu Tabelleninformationen und Indexinformationen

TABLES, TABLESPACES, INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES:

TABLES Da Es besteht kein Zweifel daran, dass es sich bei dieser Tabelle um die aufgezeichneten Tabelleninformationen in der Datenbank handelt, einschließlich Systemdatenbanken und vom Benutzer erstellten Datenbanken. Die Quelle des Anzeigetabellenstatus wie „test1“G ist diese Tabelle.

TABLESPACES ist der markierte aktive Tabellenbereich. Diese Tabelle liefert keine Informationen über den innodb-Tabellenbereich und ist für uns nicht von großem Nutzen, da unsere Produktionsbibliothek gezwungen ist, INNODB zu verwenden

INNODB_SYS_TABLES Diese Tabelle muss aus dem Datenwörterbuch SYS_TABLES abgerufen werden. Diese Tabelle enthält Informationen zum Format und den Speichereigenschaften der -Tabelle , einschließlich Zeilenformat, komprimierter Seitengröße und Informationen auf Bitebene (falls zutreffend).

enthält Informationen zum Tabellenbereich über INNODB. Tatsächlich stimmt es mit den INNODB-Informationen in SYS_TABLESPACES überein.

STATISTIK: Diese Tabelle enthält Indexinformationen zur Tabelle und zugehörige Informationen zu allen Indizes.

INNODB_SYS_INDEXES: Stellt Informationen zum Index der INNODB-Tabelle bereit. Die in der SYS_INDEXES-Tabelle gespeicherten Informationen sind grundsätzlich dieselben, außer dass letztere Indexinformationen für alle Speicher-Engines bereitstellt Letzteres stellt nur Indexinformationen für die INNODB-Tabelle bereit.

INNODB_SYS_TABLESTATS:

Diese Tabelle zeichnet die INNODB-Tabelleninformationen von MySQL auf und der MySQL-Optimierer schätzt tatsächlich die entsprechenden Indexinformationen , Es handelt sich um die statistischen Informationen der MySQL-Datenbank

. Die Datensätze dieser Tabelle werden im Speicher aufgezeichnet. Es handelt sich um eine Speichertabelle, die nach jedem Neustart erneut aufgezeichnet werden kann Zeichnen Sie die Daten des letzten Mals nur nach dem Neustart auf. Mit dieser Tabelle ist es für uns bequemer, den Index zu verwalten. Wir können die Häufigkeit der Indexverwendung abfragen, die Bereinigung selten verwendeter Indizes erleichtern und die Effizienz der Tabelle verbessern aktualisiereneinfügen usw., Speicherplatz sparen. INNODB_SYS_FIELDS

: In dieser Tabelle werden Indexfeldinformationen und Feldrankings der INNODB-Tabelle erfasst.

INNODB_FT_CONFIG

: In dieser Tabelle werden Volltextindexinformationen

gespeichert INNODB_FT_DEFAULT_S

TOP

WORD: Diese Tabelle speichert Stoppwortinformationen und wird zum Abgleichen des Volltextindex verwendet, der mit dem INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD Dieses STOPWORD muss vor der Erstellung des Index erstellt werden und das Feld muss als varchar angegeben werden. Stoppwort ist das, was wir Stoppwörter nennen. Bei verschiedenen Zeichensätzen und Sortiermethoden wird der Treffer fehlschlagen oder die Daten können nicht gefunden werden Möglichkeiten, Wörter zu ordnen. Mit dieser Funktion können wir unnötige Felder herausfiltern. INNODB_FT_INDEX_TABLE: Diese Tabelle speichert Indexnutzungsinformationen über die INNODB-Tabelle mit Volltextindex. Ebenso kann diese Tabelle nur verwendet werden, nachdem innodb_ft_aux_table festgelegt wurde 🎜>

INNODB_FT_INDEX_CACHE

: Diese Tabelle speichert Datensatzinformationen vor dem Einfügen und dient auch dazu, eine teure Indexreorganisation während DML zu vermeiden

7: Über einige Tabellen zur MySQL-Optimierung

OPTIMIZER_TRACE: Stellt Informationen bereit, die von der Optimierungs-Tracking-Funktion generiert wurden. Diesbezüglich habe ich auch einen kleinen Test durchgeführt, MySQL Tracking Optimizer Trial

PROFILING: SHOW PROFILE kann einen detaillierten Einblick in den Arbeitsstatus von Serverausführungsanweisungen bieten. Und es kann Ihnen auch helfen, die Zeit zu verstehen, die zum Ausführen von Anweisungen benötigt wird. Einige Einschränkungen sind die nicht implementierte Funktionalität, die Unfähigkeit, Anweisungen von anderen Verbindungen anzuzeigen und zu analysieren, und die beim Parsen anfallenden Kosten.

SHOW PROFILES zeigt mehrere kürzlich an den Server gesendete Anweisungen an. Die Anzahl der Anweisungen wird gemäß der Sitzungsvariablen profiling_history_size definiert. Der Standardwert ist 15 und der Maximalwert ist 100. Das Setzen auf 0 entspricht dem Ausschalten der Analysefunktion. Weitere Informationen finden Sie im MySQL-Profil

INNODB_FT_BEING_

DELETE

D,INNODB_FT_DELETED:

INNODB_FT_BEING_DELETED

Diese Tabelle ist eine Momentaufnahme von INNODB_FT_DELETED, nur in OPTIMIZE TABLE wird nur verwendet, wenn . Ausführliche Informationen finden Sie in meinem OPTIMIZE TABLE Brief 8: Einige Tabellen im Zusammenhang mit MySQL-Transaktionen und Sperren

INNODB_LOCKS: Holen Sie sich jetzt Sperren , umfasst jedoch keine nicht erworbenen Sperren und ist nur für INNODB.

INNODB_LOCK_WAITS: Informationen zum Warten auf Systemsperren, einschließlich Aufzeichnungen einer oder mehrerer blockierter Zeilen sowie Sperrinformationen zu Sperranforderungen und blockierten Änderungsanforderungen usw.

INNODB_TRX: Enthält Informationen zu allen ausgeführten Transaktionen (INNODB) und gibt an, ob die Transaktion blockiert ist oder eine Sperre erfordert.

Unerledigte Dinge und blockierte Prozesse können wir über diese Tabellen leicht abfragen. Ist das nicht bequemer? Weitere Informationen finden Sie in der information_schema-Reihe acht (Dinge, Sperren)

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in information_schema in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!