Detaillierte Erklärung, wie MySQL bestimmt, ob eine InnoDB-Tabelle ein unabhängiger Tablespace oder ein gemeinsam genutzter Tablespace ist

Vorwort

InnoDB speichert Daten in Tablespaces. In der Standardkonfiguration gibt es eine Datei mit einer Anfangsgröße von 10 MB und dem Namen ibdata1. Diese Datei ist die Standard-Tablespace-Datei (Tablespace-Datei). Benutzer können sie über den Parameter innodb_data_file_path festlegen. Es können mehrere Datendateien vorhanden sein. Wenn innodb_file_per_table nicht festgelegt ist, werden die Daten dieser Tabellen des InnoDB-Speichertyps in diesem gemeinsam genutzten Tablespace abgelegt. Wenn die Systemvariable innodb_file_per_table = 1 ist, generiert die Tabelle des InnoDB-Speichermotortyps einen unabhängigen Tablespace. Die Benennungsregel des unabhängigen Tablespace lautet: Tabellenname.idb. Diese separaten Tablespace-Dateien speichern nur die Daten, den Index, das Einfügepuffer-BITMAP und andere Informationen der Tabelle. Andere Informationen werden weiterhin im gemeinsam genutzten Tablespace gespeichert. Wie kann also bestimmt werden, welche Tabellen in der Datenbank unabhängige Tablespaces und welche Tabellen gemeinsam genutzte Tablespaces sind?

Logische Speicherstruktur von InnoDB

Methode 1: Identifizieren über IBD-Datei

Wenn die Speicher-Engine der Tabelle InnoDB ist und der Tablespace ein gemeinsam genutzter Tablespace ist, gibt es im entsprechenden Verzeichnis der Datenbank keine Datei „Tabellenname.ibd“. Für eine Tabelle in einem unabhängigen Tabellenbereich gibt es eine Datei „Tabellenname.ibd“. Diese Methode ist jedoch sehr dumm. Für eine Produktionsumgebung ist es keine gute Methode, eine große Anzahl von Tabellen auf diese Weise zu beurteilen.

mysql> Variablen wie „innodb_file_per_table“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| innodb_file_per_table | EIN |
+--------------------------+----------+
1 Zeile im Satz (0,01 Sek.)
 
mysql> MyDB verwenden;
Einlesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten
 
Datenbank geändert
mysql> Tabelle Independent_tablespace erstellen (Name varchar (64));
Abfrage OK, 0 Zeilen betroffen (0,03 Sek.)
 
mysql> beenden
 
[root@DB-Server ~]# cd /data/mysql/MyDB/
[root@DB-Server MyDB]# ls -lrt Independent_tablespace*
-rw-rw---- 1 mysql mysql 8560 21. August 22:05 Independent_tablespace.frm
-rw-rw---- 1 mysql mysql 98304 21. August 22:05 Independent_tablespace.ibd
[root@DB-Server MyDB]#

Setzen Sie innodb_file_per_table=0 in der Konfigurationsdatei my.cnf, starten Sie den MySQL-Dienst neu, erstellen Sie die Tabelle common_tablespace und Sie sehen nur die Datei common_tablespace.frm im Datenverzeichnis.

mysql> Variablen wie „innodb_file_per_table“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| innodb_file_per_table | AUS |
+--------------------------+----------+
1 Zeile im Satz (0,00 Sek.)
 
mysql> MyDB verwenden;
Einlesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten
 
Datenbank geändert
mysql> Tabelle erstellen common_tablespace(name varchar(64));
Abfrage OK, 0 Zeilen betroffen (0,02 Sek.)
 
mysql> beenden
Tschüss
[root@DB-Server MyDB]# ls -lrt common_tablespace*
-rw-rw---- 1 mysql mysql 8560 21. August 22:08 common_tablespace.frm
[root@DB-Server MyDB]#

Methode 2: Verwenden Sie zur Bestimmung INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES.

MySQL 5.6

MySQL 5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES Diese Systemtabelle bietet Informationen zum Format und den Speichereigenschaften von Tabellen, einschließlich Zeilenformat, Bit-Level der Komprimierungsseitengröße (sofern zutreffend) und Informationen zum INNODB-Tabellenbereich.

Die Tabelle INNODB_SYS_TABLESPACES bietet Metadaten zu InnoDB-Tablespaces, die den Informationen in der Tabelle SYS_TABLESPACES im InnoDB-Datenwörterbuch entsprechen.

Ich habe einige Zeit damit verbracht, die Tabelle INNODB_SYS_TABLESPACES unter der Datenbank INFORMATION_SCHEMA zu verstehen und habe dann ein SQL geschrieben, um zu bestimmen, ob es sich bei diesen InnoDB-Engine-Tabellen um unabhängige oder gemeinsam genutzte Tabellenbereiche handelt.

Gemeinsam genutzter Tablespace:

SELECT TABLE_SCHEMA
 ,Tabellenname
 ,Tabellentyp
 ,N'Gemeinsam genutzter Tabellenbereich' AS TABLE_SPACE
 ,MOTOR
 ,VERSION
 ,Tabellenzeilen
 ,Durchschnittliche Zeilenlänge
 ,CREATE_TIME
 ,UPDATE_TIME
VON INFORMATION_SCHEMA.TABLES T
LINKS JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
Wobei I.NAME NULL ist und T.TABLE_SCHEMA='MyDB' und T.ENGINE='InnoDB'; 

Dieses Skript hat jedoch einen kleinen Fehler. Bei Tabellennamen, die Sonderzeichen enthalten, können Fehler auftreten. Dies liegt daran, dass, wenn der Tabellenname Sonderzeichen enthält, der Dateiname oder NAME in INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES maskiert wird, wie unten gezeigt

Unabhängiger Tischbereich

SELECT TABLE_SCHEMA
 ,Tabellenname
 ,Tabellentyp
 ,N'Unabhängiger Tabellenbereich' AS TABLE_SPACE
 ,MOTOR
 ,VERSION
 ,Tabellenzeilen
 ,Durchschnittliche Zeilenlänge
 ,CREATE_TIME
 ,UPDATE_TIME
VON INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WO T.TABLE_SCHEMA='MyDB' UND T.ENGINE='InnoDB'; 

Methode 3: INFORMATION_SCHEMA.INNODB_SYS_TABLES-Bestimmung

MySQL 5.7

Wenn es sich um MySQL 5.7 handelt, gibt es eine Methode mehr als bei MySQL 5.6. In INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES von MySQL 5.7 gibt es ein zusätzliches Feld SPACE_TYPE, aber seine Werte sind alle Single. In INFORMATION_SCHEMA.INNODB_SYS_TABLES gibt es auch ein zusätzliches Feld SPACE_TYPE und seine Werte sind Single und System, die jeweils separate Tablespaces und gemeinsam genutzte Tablespaces darstellen.

#Separater Tabellenbereich

Wählen Sie * aus Information_Schema.InnoDB_sys_tables
WO SPACE_TYPE='Einzeln';
 
 
 
SELECT TABLE_SCHEMA
 ,Tabellenname
 ,Tabellentyp
 ,N'Unabhängiger Tabellenbereich' AS TABLE_SPACE
 ,MOTOR
 ,VERSION
 ,Tabellenzeilen
 ,Durchschnittliche Zeilenlänge
 ,CREATE_TIME
 ,UPDATE_TIME
VON INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WO T.TABLE_SCHEMA='IhreDatenbank' UND T.ENGINE='InnoDB';

#Gemeinsam genutzter Tablespace

Wählen Sie * aus Information_Schema.InnoDB_sys_tables
WO SPACE_TYPE='System';
 
 
 
SELECT TABLE_SCHEMA
 ,Tabellenname
 ,Tabellentyp
 ,N'Gemeinsam genutzter Tabellenbereich' AS TABLE_SPACE
 ,MOTOR
 ,VERSION
 ,Tabellenzeilen
 ,Durchschnittliche Zeilenlänge
 ,CREATE_TIME
 ,UPDATE_TIME
VON INFORMATION_SCHEMA.TABLES T
LINKS JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
Wobei I.NAME NULL ist und T.TABLE_SCHEMA='IhreDatenbank' und T.ENGINE='InnoDB'

Methode 4: INFORMATION_SCHEMA.INNODB_TABLES-Bestimmung

MySQL 8.0

Wenn es sich um MySQL 8.0 handelt, gibt es noch eine weitere Methode, nämlich die Beurteilung über INFORMATION_SCHEMA.INNODB_TABLES. Diese neu hinzugefügte Systemtabelle kann über das Feld SPACE_TYPE gemeinsam genutzte Tablespaces von unabhängigen Tablespaces unterscheiden.

Wählen Sie * aus Information_Schema.InnoDB_Tables, wobei Space_Type = 'Einzeln' ist.

Quellen:

https://dev.mysql.com/doc/refman/8.0/en/innodb-tables-table.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-i_s-tables.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablespaces-table.html#innodb-sys-tablespaces-table-flag-column

https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tablespaces-table.html

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.