Detaillierte Erklärung des Unterschieds zwischen temporärer MySQL-Tabelle und Partitionstabelle

Temporäre Tabellen und Speichertabellen

Eine Speichertabelle ist eine Tabelle, die die Speicher-Engine verwendet. Die Syntax zum Erstellen einer Tabelle lautet create table … engine=memory. Die Daten dieses Tabellentyps werden im Speicher abgelegt und beim Neustart des Systems gelöscht, die Tabellenstruktur bleibt jedoch weiterhin bestehen. Abgesehen von diesen beiden „seltsam“ aussehenden Merkmalen handelt es sich von den anderen Merkmalen her um einen normalen Tisch.

Temporäre Tabellen können verschiedene Engine-Typen verwenden. Wenn Sie eine temporäre Tabelle mit der InnoDB-Engine oder der MyISAM-Engine verwenden, werden die Daten auf die Festplatte geschrieben. Natürlich können auch temporäre Tabellen die Memory Engine verwenden.

Temporäre Tabelleneigenschaften

• Die Syntax zum Erstellen einer Tabelle lautet „create temporary table …“.
• Auf eine temporäre Tabelle kann nur von der Sitzung zugegriffen werden, die sie erstellt hat, und sie ist für andere Threads nicht sichtbar. Daher ist die von Sitzung A in der Abbildung erstellte temporäre Tabelle t für Sitzung B nicht sichtbar.
• Eine temporäre Tabelle kann denselben Namen wie eine normale Tabelle haben.
• Wenn Sitzung A über eine temporäre Tabelle und eine normale Tabelle mit demselben Namen verfügt, greifen die Anweisung „show create“ sowie die Anweisungen „add“, „delete“, „modify“ und „query“ auf die temporäre Tabelle zu.
• Der Befehl „show tables“ zeigt keine temporären Tabellen an.

Da auf die temporäre Tabelle nur von der Sitzung zugegriffen werden kann, in der sie erstellt wurde, wird sie beim Beenden der Sitzung automatisch gelöscht. Gerade aufgrund dieser Eigenschaft eignen sich temporäre Tabellen besonders gut für Join-Optimierungsszenarien.

Erstellen Sie eine temporäre Tabelle temp_t wie t1.
Tabelle temp_t ändern, Index hinzufügen(b);
in temp_t einfügen, auswählen * von t2, wobei b>=1 und b<=2000;
wähle * von t1, verbinde temp_t auf (t1.b=temp_t.b);

Temporäre Tabellen verschiedener Sitzungen können denselben Namen haben. Wenn mehrere Sitzungen gleichzeitig eine Join-Optimierung ausführen, besteht kein Grund zur Sorge, dass die Tabellenerstellung aufgrund doppelter Tabellennamen fehlschlägt. Sie müssen sich keine Sorgen um die Löschung von Daten machen. Wenn Sie eine normale Tabelle verwenden und die Verbindung des Clients während der Prozessausführung abnormal getrennt oder die Datenbank abnormal neu gestartet wird, müssen Sie die mitten im Prozess generierten Datentabellen bereinigen. Da temporäre Tabellen automatisch wiederverwendet werden, ist dieser zusätzliche Vorgang nicht erforderlich. Anwendung temporärer Tabellen

Datenbankübergreifende Abfrage der Sharded-Datenbank und des Tabellensystems

Das allgemeine Szenario beim Sharding von Datenbanken und Tabellen besteht darin, eine logisch große Tabelle auf verschiedene Datenbankinstanzen zu verteilen. Zum Beispiel. Teilen Sie eine große Tabelle ht gemäß Feld f in 1024 Untertabellen auf und verteilen Sie sie dann auf 32 Datenbankinstanzen.

Die Auswahl des Partitionsschlüssels basiert auf der „Reduzierung datenbank- und tabellenübergreifender Abfragen“. Wenn die meisten Anweisungen die Gleichheitsbedingung f enthalten, sollte f als Partitionsschlüssel verwendet werden. Auf diese Weise kann die Proxy-Ebene nach der Analyse der SQL-Anweisung ermitteln, an welche Untertabelle die Anweisung zur Abfrage weitergeleitet werden soll. Zum Beispiel

wähle v aus ht, wobei f=N;

Zu diesem Zeitpunkt können wir die Tabellenpartitionierungsregeln (z. B. N%1024) verwenden, um zu bestätigen, in welcher Tabelle die erforderlichen Daten platziert werden. Dieser Anweisungstyp muss nur auf eine Shard-Tabelle zugreifen und ist die beliebteste Anweisungsform für Shard-Datenbanken und -Tabellen.

Wenn es jedoch einen weiteren Index k für diese Tabelle gibt und die Abfrageanweisung wie folgt lautet:

Wähle v aus ht, wobei k >= M, sortiere nach t_modified, Beschreibungsgrenze 100;

Da das Partitionsfeld f derzeit nicht in der Abfragebedingung verwendet wird, können wir nur alle Zeilen durchsuchen, die die Bedingungen in allen Partitionen erfüllen, und dann die Order-by-Operation einheitlich ausführen. In diesem Fall gibt es zwei gängige Ansätze:

Die Implementierung der Sortierung im Prozesscode der Proxy-Schicht übt großen Druck auf das Proxy-Ende aus und kann insbesondere leicht zu Problemen wie unzureichendem Speicher und CPU-Engpässen führen.

Aggregieren Sie die aus jeder Unterdatenbank erhaltenen Daten in einer Tabelle in einer MySQL-Instanz und führen Sie dann logische Operationen an dieser aggregierten Instanz aus.

Erstellen Sie eine temporäre Tabelle temp_ht in der Zusammenfassungsdatenbank, die drei Felder enthält: v, k und t_modifified.

Auf jeder Unterbibliothek ausführen

Wähle v, k, t_modified aus ht_x, wobei k >= M, sortiere nach t_modified, Beschreibungsgrenze 100;

Fügen Sie die Ergebnisse der Ausführung der Unterdatenbank in die Tabelle temp_ht ein.

implementieren

Wähle v aus temp_ht, sortiert nach t_modified, Beschreibungslimit 100;

Warum kann eine temporäre Tabelle umbenannt werden?

Erstellen Sie eine temporäre Tabelle temp_t (ID int Primärschlüssel)engine=innodb;

Bei der Ausführung dieser Anweisung erstellt MySQL eine FRM-Datei für diese InnoDB-Tabelle, um die Tabellenstrukturdefinition und einen Ort zum Speichern der Tabellendaten zu speichern.

Diese frm-Datei wird im temporären Dateiverzeichnis abgelegt. Das Dateinamensuffix lautet .frm und das Präfix lautet "#sql{Prozess-ID}_{Thread-ID}_Seriennummer". Mit dem Befehl „select @@tmpdir“ können Sie das temporäre Dateiverzeichnis der Instanz anzeigen.

Die Prozess-ID dieses Prozesses ist 1234, die Thread-ID von Sitzung A ist 4 und die Thread-ID von Sitzung B ist 5. Daher enthalten die von Sitzung A und Sitzung B erstellten temporären Tabellen keine doppelten Dateien auf der Festplatte.

MySQL verwaltet Datentabellen. Neben physischen Dateien gibt es auch einen Mechanismus im Speicher, um verschiedene Tabellen zu unterscheiden. Jede Tabelle entspricht einem table_def_key. Bei temporären Tabellen fügt table_def_key „Server-ID+Thread-ID“ zum „Datenbanknamen+Tabellennamen“ hinzu.

Das heißt, die beiden von Sitzung A und Sitzung B erstellten temporären Tabellen t1 haben unterschiedliche table_def_key- und Datenträgerdateinamen, sodass sie koexistieren können.

Verhalten von Partitionstabellen auf Engine-Ebene

ATE TABELLE `t` (
		`ftime` datetime NICHT NULL,
		`c` int(11) DEFAULT NULL,
		SCHLÜSSEL (`ftime`)
) ENGINE=InnoDB STANDARD CHARSET=latin1
PARTITION NACH BEREICH (JAHR(ftime))
Der
B
 (PARTITION p_2017 WERTE WENIGER ALS (2017) ENGINE = InnoDB,
 	PARTITION p_2018 WERTE WENIGER ALS (2018) ENGINE = InnoDB,
 	PARTITION p_2019 WERTE WENIGER ALS (2019) ENGINE = InnoDB,
 PARTITION p_others WERTE KLEINER ALS MAXIMALER WERT ENGINE = InnoDB);
 einfügen in t-Werte('2017-4-1',1),('2018-4-1',1); 

Beim Initialisieren der Tabelle werden nur zwei Datenzeilen eingefügt. Die Select-Anweisung von sessionA sperrt die Lücke zwischen den beiden Datensätzen von ftime. Die Lücke und der Sperrstatus sind in der Abbildung dargestellt:

Das heißt, die Lücke zwischen den beiden Datensätzen 2017-4-1 und 2018-4-1 wird gesperrt, und dann sollten beide Einfügeanweisungen von Sitzung B in den Sperrwartezustand wechseln. In Bezug auf die Wirkung kann die erste Einfügeanweisung jedoch erfolgreich ausgeführt werden, da für die Engine p2018 und p2019 unterschiedliche Tabellen sind und der nächste Datensatz von 2017 nicht 2018-4-1, sondern das Supremum in p2018 ist. Daher ist der Index zum Zeitpunkt t1 wie in der Abbildung dargestellt:

Aufgrund der Regeln der Partitionstabelle führt Sitzung A nur p2018 aus. Sitzung B kann 2018-2-1 einfügen, muss jedoch auf die Lückensperre von Sitzung A warten, um in 2017-12-1 schreiben zu können.

Für die MYISAM-Engine:

Da Sitzung A 100 Sekunden lang schläft und MyISAM nur Tabellensperren unterstützt, sperrt dieses Update das Lesen der gesamten Tabelle t. Das Ergebnis ist jedoch, dass die erste Anweisung von B ausgeführt werden kann und die zweite Anweisung in den Sperrwartezustand wechselt.

Dies liegt daran, dass die MyISAM-Tabellensperre nur auf der Engine-Ebene implementiert ist. Die von sessionA hinzugefügte Tabellensperre befindet sich auf p2018, sodass nur auf der Partition ausgeführte Abfragen blockiert werden und Abfragen auf anderen Partitionen nicht betroffen sind. Es scheint, dass die Partitionstabelle nicht schlecht ist, warum also nicht verwenden? Ein Grund, warum wir die Partitionstabelle verwenden, ist, dass die einzelne Tabelle zu groß ist. Wenn wir die Partitionstabelle nicht verwenden, müssen wir die manuelle Tabellenpartitionierungsmethode verwenden.

Für die manuelle Tabellenpartitionierung müssen t_2017, t_2018 und t_2019 erstellt werden, d. h. alle zu aktualisierenden Untertabellen müssen gefunden und nacheinander ausgeführt werden. Dies unterscheidet sich nicht von der partitionierten Tabelle. In der einen entscheidet der Server, welche Partition verwendet wird, während in der anderen der Code der Anwendungsschicht entscheidet, welche Untertabelle verwendet wird. Daher besteht kein tatsächlicher Unterschied zur Engine-Schicht. Tatsächlich liegt der Hauptunterschied auf Serverebene: beim Verhalten beim Öffnen der Tabelle.

Partitionierungsstrategie

Immer wenn zum ersten Mal auf eine partitionierte Tabelle zugegriffen wird, muss MySQL auf alle Partitionen zugreifen: Wenn viele Partitionen vorhanden sind, z. B. 1000 Partitionen überprüft wurden und beim Starten von MySQL das open_files_limit standardmäßig auf 1024 eingestellt ist, wird beim Zugriff auf die Tabelle ein Fehler gemeldet, da alle Dateien geöffnet sind und die Obergrenze überschritten wurde.

Die von mysiam verwendete Partitionierungsstrategie wird als allgemeine Partitionierungsstrategie bezeichnet und jeder Zugriff auf die Partition wird von der Serverebene gesteuert. Es gibt schwerwiegende Leistungsprobleme.

Die InnoDB-Engine führt eine lokale Partitionierungsstrategie ein, die das Verhalten beim Öffnen von Partitionen innerhalb von InnoDB selbst verwaltet.

Verhalten von Partitionstabellen auf Serverebene

Aus der Serverebene ist eine Partitionstabelle einfach nur eine Tabelle.

Obwohl B nur auf der Partition 2017 arbeitet, hält A die MDL-Sperre der gesamten Tabelle t, was die ALTER-Anweisung von B blockiert. Wenn Sie eine gemeinsame Shard-Tabelle verwenden, kommt es zu keinem MDL-Konflikt mit der Abfrageanweisung einer anderen Shard-Tabelle.

Zusammenfassung:

• Wenn MySQL zum ersten Mal eine partitionierte Tabelle öffnet, muss es auf alle Partitionen zugreifen
• Auf Serverebene wird dies als dieselbe Tabelle betrachtet, sodass alle Partitionen die MDL-Sperre teilen.
• Auf Engine-Ebene werden diese als unterschiedliche Tabellen betrachtet, sodass nach der MDL-Sperre nur auf die erforderlichen Partitionen entsprechend den Partitionstabellenregeln zugegriffen wird.

Anwendungsszenarien für Partitionstabellen

Der Vorteil partitionierter Tabellen besteht darin, dass sie für das Unternehmen transparent sind. Im Vergleich zu benutzerdefinierten partitionierten Tabellen ist der Geschäftscode, der partitionierte Tabellen verwendet, einfacher und partitionierte Tabellen können historische Daten problemlos bereinigen.

Der Vorgang „Alter Table t Drop Partition“ löscht die Partitionsdatei. Seine Wirkung ist ähnlich wie die von „Drop“. Im Vergleich zum Löschen hat es die Vorteile einer höheren Geschwindigkeit und einer geringeren Auswirkung auf das System.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.