Die Geschichte einer MySQL-Optimierung

Es geht um die Optimierung in der MySQL-Datenbank.

Dies geschah, als wir ein System für E-Mail-Newsletter erstellten. Unser System sollte täglich zig Millionen Briefe senden. Das Senden eines Briefes ist keine leichte Aufgabe, obwohl alles ziemlich primitiv aussieht:

1. Sammeln Sie einen Brief von HTML Creative und ersetzen Sie personalisierte Daten.
2. Fügen Sie ein Pixel zum Anzeigen von Nachrichten hinzu und ersetzen Sie alle Links in der Nachricht durch Ihre eigenen, um Klicks zu verfolgen.
3. Überprüfen Sie vor dem Senden, ob die E-Mail nicht auf der schwarzen Liste steht.
4. Senden Sie eine E-Mail an einen bestimmten Pool.

Ich werde Ihnen mehr über den zweiten Absatz erzählen:
Microservice Mail-Builder bereitet einen Brief zum Senden vor:

• findet alle Links im Brief;
• Für jeden Link wird eine eindeutige UUID mit 32 Zeichen generiert.
• Ersetzt den ursprünglichen Link durch einen neuen und speichert die Daten in der Datenbank.

Daher werden alle Quelllinks durch uuid ersetzt und die Domain wird in unsere geändert. Wenn Sie über diesen Link eine GET-Anfrage erhalten, stellen wir das Originalbild als Proxy zur Verfügung oder leiten zum Originallink weiter. Das Speichern erfolgt in der MySQL-Datenbank. Wir speichern die generierte UUID zusammen mit dem ursprünglichen Link und einigen Metainformationen (Benutzer-E-Mail, Mailing-ID und andere Daten). Die Denormalisierung hilft uns bei einer Anfrage, alle erforderlichen Daten zum Speichern von Statistiken abzurufen oder eine Art Trigger-Kette zu starten.

Problem Nummer 1

Die Erzeugung von UUID in uns hing vom Zeitstempel ab.

Da Mailings normalerweise in einem bestimmten Zeitraum stattfinden und viele Instanzen von Microservice zum Zusammenstellen eines Briefes gestartet werden, stellte sich heraus, dass einige der Benutzeroberflächen sehr ähnlich waren. Dies ergab eine geringe Selektivität. UPD: Da die Daten ähnlich waren, war die Arbeit mit dem Bi-Tree nicht sehr effektiv.

Wir haben dieses Problem mit dem uuid-Modul in Python gelöst, bei dem keine Zeitabhängigkeit besteht.
Solch eine implizite Sache reduzierte die Geschwindigkeit von Indizes.

Wie läuft die Lagerung?

Die Struktur der Tabelle war wie folgt:

CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Zum Zeitpunkt der Erstellung sah alles logisch aus:
UUID ist ein Primärschlüssel und auch ein Clustered-Index. Wenn wir in diesem Feld eine Auswahl treffen, wählen wir einfach den Datensatz aus, da alle Werte genau dort gespeichert sind. Dies war eine bewusste Entscheidung. Erfahren Sie mehr über den Clustered-Index.

Alles war großartig, bis der Tisch wuchs.

Problem Nummer 2

Wenn Sie mehr über den Cluster-Index lesen, können Sie sich über diese Nuance informieren:

Wenn Sie der Tabelle eine neue Zeile hinzufügen, wird diese nicht am Ende der Datei, nicht am Ende der flachen Liste, sondern durch Sortieren zu dem gewünschten Zweig der ihr entsprechenden Baumstruktur hinzugefügt.

Somit nahm mit zunehmender Last die Einführzeit zu.

Die Lösung bestand darin, eine andere Tabellenstruktur zu verwenden.

 CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Da der Primärschlüssel jetzt automatisch inkrementiert wird und MySQL den Cache der letzten Einfügestelle speichert, erfolgt die Einfügung jetzt immer am Ende, d. H. Innodb ist für das Schreiben von sequentiell ansteigenden Werten optimiert.

Die Details dieser Optimierung habe ich im Postgres-Quellcode gefunden. MySQL implementiert eine sehr ähnliche Optimierung.
Natürlich musste ich einen eindeutigen Schlüssel hinzufügen, damit es keine Konflikte gab, aber wir haben die Einfügegeschwindigkeit erhöht.

Da die Basis noch weiter wächst, haben wir darüber nachgedacht, alte Daten zu löschen. Die Verwendung von DELETE für das Feld Inserted ist absolut nicht optimal - dies ist eine sehr lange Zeit, und der Platz wird erst freigegeben, wenn wir den Befehl optimize table ausführen. Diese Operation blockiert übrigens den Tisch komplett - das hat uns überhaupt nicht gepasst.

Aus diesem Grund haben wir beschlossen, unsere Tabelle in Partitionen aufzuteilen.
1 Tag - 1 Partition, die alten werden automatisch gelöscht, wenn die Zeit gekommen ist.

Problem Nummer 3

Wir hatten die Möglichkeit, die alten Daten zu löschen, aber wir hatten nicht die Möglichkeit, aus der gewünschten Partition auszuwählen, da mysql mit select`e nur uuid angibt, nicht weiß, in welcher Partition wir danach suchen sollen, und in allen sucht.

Die Lösung wurde aus Problem 1 geboren - fügen Sie der generierten UUID einen Zeitstempel hinzu. Nur diesmal haben wir etwas anders gemacht: Wir haben einen Zeitstempel an einer zufälligen Stelle in der Zeile eingefügt, nicht am Anfang oder am Ende; vorher und nachher haben sie ein Strichsymbol hinzugefügt, damit es mit einem regulären Ausdruck erhalten werden kann.

Mit dieser Optimierung konnten wir das Datum abrufen, an dem die UUID generiert wurde, und bereits eine Auswahl treffen, die den spezifischen Wert des Felds Einfügen angibt. Jetzt lesen wir die Daten sofort von der Partition, die wir benötigen.

Dank Dingen wie ROW_FORMAT = COMPRESSED und der Änderung der Codierung in latin1 konnten wir noch mehr Speicherplatz auf der Festplatte sparen.