MySQL. Pseudo-Big-Data-Optimierung / Sudo Null IT-News

Während ich an verschiedenen interessanten Aufgaben arbeitete, hörte ich nur von der Existenz von Programmen, die mit Big Data (im Rahmen eines einzelnen Servers) arbeiten. Und dann kam der Tag, an dem sich ein Kunde bei uns meldete, dessen Seite sehr lange geladen hatte. Die Aufgabe zur Lösung dieses Problems wurde von meinem Kollegen gestellt. Es verging einige Zeit, bis er mich zu sich rief, um ein für mich so abwegiges Spektakel zu zeigen, verbunden mit der Menge an Tabellen, in denen Daten nach dem Import von Kategorien, Merkmalen, Produktmarken standen.

22 Millionen Kategorieoptionen schienen uns mehr als eine anständige Menge, umso mehr – das Gewicht der Tabelle mit Kategorien entpuppte sich als etwas mehr als 1,6 Gib. Bei diesen Größen begannen die Seiten länger zu laden, als wir möchten. Da die Fristen für die Lösung des Problems kurz sind (je früher, desto besser), entschied sich das Management, zwei Einheiten von Programmierern für diese Aufgabe bereitzustellen, mich und meinen Kollegen. Als ich das Modul in zwei Teile teilte, bekam ich nicht die meiste Sahne in Bezug auf das Volumen, aber keinen weniger wichtigen Bereich, da das Laden von Daten viel Zeit in Anspruch nahm.

▍ Aufgabe

Die Herausforderung besteht darin, Abfragen so zu optimieren, dass ihre Ausführungszeit nicht mehr als eine Sekunde beträgt.

Es gibt folgende Tabellen:

1. Kommunikationstabelle

Zeilenanzahl 995371: CREATE TABLE `features_variants_links` ( `variant_id` bigint(64) UNSIGNED NOT NULL, `image_link` varchar(255) default ”, PRIMARY KEY (`variant_id`) )ENGINE=MyISAM DEFAULT CHARSET=UTF8
2. Kommunikationstabelle:

Zeilenanzahl 2285984: CREATE TABLE feature_variants_descriptions (variant_id bigint(64) UNSIGNED NOT NULL, name varchar(255) default ”, description text, lang_code char(2) NOT NULL default ‘ru’, PRIMARY KEY (variant_id,lang_code) )ENGINE =MyISAM-STANDARDZEICHENSATZ=UTF8
3. Kommunikationstabelle:

Zeilenanzahl 1142994: CREATE TABLE feature_variants (variant_id bigint(64) UNSIGNED NOT NULL, feature_id bigint(64) UNSIGNED NOT NULL, PRIMARY KEY (variant_id,feature_id) , KEY feature_id (feature_id) , KEY variation_id (variant_id) )ENGINE=MyISAM DEFAULT ZEICHENSATZ=UTF8

Längste Laufzeit für einen Prozess abfragen:

SELECT SQL_CALC_FOUND_ROWS * FROM Feature_Varianten AS Varianten JOIN Features_Varianten_Links AS Varianten_Links ON Varianten.Varianten_ID = Varianten_Links.Varianten_ID JOIN Feature_Varianten_Beschreibungen AS Varianten_Beschreibungen ON Varianten.Varianten_ID = Varianten_Beschreibungen.Varianten_ID WHERE Varianten_Links.Feature_ID IN (127) UND Varianten_Beschreibungen.lang_code=”en” ORDER BY Varianten .Variante ASC LIMIT 0, 10

Ausführungszeit der Anfrage:

Dies ist die längste Anfrage in meiner Hälfte des Moduls. Ich werde das Problem lösen, indem ich die Struktur der Tabellen ändere. Der zweite Schritt besteht darin, die Abfrage selbst zu optimieren.

▍ Lösung

Das erste, was mir sofort ins Auge fällt und das ich nicht übersehen kann, ist die gebräuchlichste Methode zum Zählen von Zeilen mit dem Modifikator “

SQL_CALC_FOUND_ROWS

‘ und Begleitfunktion ‘

GEFUNDEN_ROWS()

“. Im MyISAM-Speichersubsystem ist diese Option zum Zählen der Gesamtzahl von Zeilen in einer Stichprobe nicht immer ratsam, da die genaue Anzahl von Zeilen vom Speichersystem zwischengespeichert wird. Was nicht über “InnoDB” gesagt werden kann, in dem solche Abfragen oft eine größere Geschwindigkeit zeigen.

Wenn Sie den Modifikator “SQL_CALC_FOUND_ROWS” entfernen und die Zeilen in der zweiten Abfrage mit der COUNT-Funktion zählen
dann erhalten Sie folgendes Ergebnis:

Nach den vorgenommenen Änderungen wurde die Ausführungszeit der Abfrage um 4 Sekunden reduziert. Verglichen mit dem, was es war, scheint der Unterschied nicht signifikant zu sein, aber 4 Sekunden sind vor dem Hintergrund der vorherigen Ladezeit schon viel.

Beim Durchsehen der Tabelle stellte ich fest, dass die Daten in der Tabelle „feature_variants_descriptions“ regelmäßig dupliziert werden. Um dies zu vermeiden, ist es notwendig, die Tabelle in drei Teile zu unterteilen. Beschreibungen und Namen von Merkmalen werden im ersten und zweiten gespeichert. Und im dritten – die Beziehung zwischen der Merkmalsvariante, ihrem Namen und ihrer Beschreibung. Wir haben folgende Tabellen:


Zeilenanzahl 224248:CREATE TABLE test_feature_descriptions ( feature_descriptions_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT, lang_code CHAR(2) NOT NULL default ‘ru’, description text , PRIMARY KEY (feature_descriptions_id) , KEY (lang_code))ENGINE=MyISAM DEFAULT CHARSET= UTF8


Zeilenanzahl 10294: CREATE TABLE test_feature_name ( feature_name_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT, lang_code CHAR(2) NOT NULL default ‘ru’, name varchar(255) default ” , PRIMARY KEY (feature_name_id) , KEY (lang_code)) ENGINE=MyISAM STANDARDZEICHENSATZ=UTF8

Zeilenanzahl 224998: CREATE TABLE test_feature_name_descriptions ( feature_name_descriptions_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, variation_id BIGINT(64) UNSIGNED NOT NULL, feature_name_id BIGINT(64) UNSIGNED NOT NULL, feature_descriptions_id BIGINT(64) UNSIGNED NOT NULL , PRIMARY KEYs (feature_id) _description , KEY (variant_id, feature_descriptions_id, feature_name_id))ENGINE=MyISAM DEFAULT CHARSET=UTF8

Wie Sie sehen können, hat sich die Datenmenge um etwa das Vierfache verringert. Die Anfrage sieht nun so aus:

SELECT * FROM Merkmalsvarianten AS Varianten JOIN Merkmale_Varianten_Links AS Varianten_Links EIN Varianten.Varianten_ID = Varianten_Links.Varianten_ID LEFT JOIN test_feature_name_descriptions AS Varianten_Beschreibungen ON Varianten.Varianten_ID = Varianten_Beschreibungen.Varianten_ID LEFT JOIN test_feature_name.Feature_Name_id = Varianten_Beschreibungen.Feature_Name_ID LEFT JOIN test_feature_descriptions ON test_feature_descriptions ON varianten_beschreibungen.feature_descriptions_id WHERE varianten_links.feature_id IN (127) AND test_feature_name.lang_code=”ru” AND test_feature_descriptions.lang_code=”ru” ORDER BY test_feature_name.name ASC LIMIT 0, 10

Wenn es in der Tabelle „feature_name_descriptions“ keine Beschreibung oder keinen Featurenamen gibt, sind die Namens- und/oder Beschreibungsfelder einfach leer und werden für weitere Logik benötigt, die den Rahmen dieses Artikels sprengen würde. Im ersten Fall, wenn wir “JOIN feature_name_descriptions” verwenden, verlieren wir “variants_links.feature_id”, das noch keinen Eintrag in “feature_name_descriptions” hat. Deshalb habe ich mich für “LEFT JOIN test_feature_name_description” entschieden.

Durch Reduzierung der Datenmenge konnte die Geschwindigkeitsanzeige verbessert werden. Aber noch lang. Für eine solche Tabellenstruktur bietet sich der Typ „InnoDB“ an. Da “MyISAM” keine Fremdschlüssel unterstützt. Aus Gründen des Experiments habe ich mich entschieden, es zuerst ohne die Fremdschlüsselbeziehung zu versuchen und den Unterschied zu messen. Hier ist das Ergebnis:

Auch ohne Fremdschlüssel ist InnoDB im Vorteil. Variante mit Funktion

GEFUNDEN_ROWS()

:

Gewonnen etwa eine halbe Sekunde. Aber hier gibt es einige Nuancen. Diese Option kann bereits seit MySQL 8.0.17 als veraltet gelten, und die Entwickler versprechen, den Modifikator in Zukunft zu entfernen

SQL_CALC_FOUND_ROWS

und zugehörige Funktion. Daher ist es (wenn immer möglich) besser, diese Methode zu vermeiden und zu verwenden

ZÄHLEN

wie vom Autor der Dokumentation empfohlen.

Link zur Quelle
.

Für solche Anfragen zeigt sich das InnoDB-Speichersystem also effizienter. Wenn Sie der Tabelle “test_feature_name_descriptions” einen FOREIGN KEY hinzufügen, können Sie eine noch etwas bessere Leistung erzielen. Ich habe zwei Fremdschlüssel im Feld “feature_name_id” und “feature_name_descriptions_id” hinzugefügt.

Auch ohne Fremdschlüssel ist InnoDB im Vorteil. Variante mit Funktion

GEFUNDEN_ROWS()

In dieser Phase habe ich die maximale Leistung erreicht. Sie können die Ausführungsgeschwindigkeit in Sekundenbruchteilen erreichen, indem Sie eine weitere Tabelle hinzufügen, wodurch die Anzahl der Elemente in der Stichprobe auf Hunderte reduziert wird. Aber diese Methode sprengt bereits den Rahmen der in meiner Hälfte des Moduls gezeigten Tabellen, und dieser Artikel wäre nicht mehr relevant, da ich in dieser Version keinen Leistungsabfall gefunden hätte.

Die zweite Option besteht darin, die Sortierung „ORDER BY“ zu opfern, indem dem Benutzer beispielsweise die Wahl der Sortierung gegeben wird, indem er auf die entsprechende Schaltfläche klickt und Daten mit Ajax geladen werden. Ohne sie betrug die Geschwindigkeit der ersten Anfrage 0,00096500 Sekunden. Da die Tabellen nirgendwo optimiert werden können, können Sie mit der Optimierung der Abfrage selbst beginnen. Die erste Anfrage kann in Ruhe gelassen werden, da wir die aus der Anfrage erhaltenen Daten benötigen. Die Anfrage zum Abrufen der Datenmenge sieht folgendermaßen aus:

ANZAHL AUSWÄHLEN

FROM Merkmalsvarianten AS Varianten JOIN Merkmale_Varianten_Links AS Varianten_Links ON Varianten.Varianten_ID = Varianten_Links.Varianten_ID WHERE Varianten_Links.Feature_ID IN (127)

Ich entfernte

LINKS VERBINDEN

da es in meinem Fall keinen Einfluss auf die Anzahl der Elemente in der Auswahl hat.

SORTIEREN NACH

und

GRENZE

, wurden in früheren Abfragen ausgeschlossen. Das Ergebnis ist:
▍ Zusammenfassung

Mein Kollege hatte eine ähnliche Geschichte. In den Anfangsstadien der Entwicklung hatten unsere Vorgänger nicht damit gerechnet, dass sich mit zunehmendem Datenvolumen die Geschwindigkeit der Abfrageausführung ändern und Datenduplizierungen auftreten würden. Infolgedessen stellte sich heraus, dass die Daten nicht so groß waren. Auch im Code wurde die Methode dahingehend verbessert, dass sie nicht nur ORDER BY und LIMIT, sondern auch LEFT JOIN aus der Abfrage entfernt. Für kleine Datenmengen ist diese Problemlösung durchaus geeignet, wächst das Volumen aber auf richtig große Datenmengen an, dann sieht die Lösung anders aus. Und wie – ich schlage vor, in den Kommentaren zu diskutieren. Telegrammkanal mit Dienstprogrammenundgemütlich plaudern

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *