Details zur MySQL-Sortierfunktion

1. Problemszenario

Eine neue Funktion zum Exportieren von Transaktionsdatensätzen wurde eingeführt. Die Logik ist ganz einfach: Exportieren Sie die entsprechenden Daten basierend auf den Abfragebedingungen. Aufgrund der großen Datenmenge wurde bei der Abfrage der Datenbank eine Paginierungsabfrage verwendet, wobei jedes Mal 1.000 Datensätze abgefragt wurden.

Der Selbsttest verläuft normal, die Testumgebung ist normal und die nach dem Online-Gehen durch die Betriebsrückmeldung exportierten Daten weisen doppelte Datensätze auf .

Ich dachte zunächst, dass es sich um ein Problem mit der Geschäftslogik handele Review den Code daher noch einmal, konnte die Ursache des Problems jedoch immer noch nicht finden. Schließlich musste ich die SQL Anweisung herausnehmen und separat ausführen, die Daten exportieren und vergleichen. Ich stellte fest, dass dies durch die Unordnung der Abfrageergebnisse SQL Anweisung verursacht wurde.

2. Ursachenanalyse

Die Abfrageanweisung wird in absteigender Reihenfolge nach create_time sortiert und nach limit paginiert. Normalerweise gibt es keine Probleme. Bei einer hohen Geschäftsparallelität und damit einer großen Anzahl identischer create_time Werte führt eine auf limit basierende Paginierung jedoch zu Störungen.

Das folgende Szenario tritt auf: Beim Sortieren nach create_time , wenn create_time den gleichen Wert hat, führt das Überschreiten des limit dazu, dass die Paging-Daten in der falschen Reihenfolge sind.

Wenn beispielsweise 1000 Daten abgefragt werden, darunter ein Stapel mit dem Datensatzwert „ create_time “ „ 2021-10-28 12:12:12 “, und einige dieser Daten mit derselben Erstellungszeit auf der ersten Seite und einige auf der zweiten Seite erscheinen, werden beim Abfragen der Daten auf der zweiten Seite möglicherweise die Daten angezeigt, die auf der ersten Seite abgefragt wurden.

Mit anderen Worten: Die Daten springen hin und her, erscheinen eine Zeit lang auf der ersten Seite und eine Zeit lang auf der zweiten Seite, was dazu führt, dass ein Teil der exportierten Daten wiederholt wird und ein Teil fehlt.

Ich habe die offizielle Dokumentation zu MySQL 5.7 und 8.0 überprüft und die Beschreibung lautet wie folgt:

Wenn mehrere Zeilen identische Werte in den ORDER BY-Spalten haben, kann der Server diese Zeilen in beliebiger Reihenfolge zurückgeben und dies je nach Gesamtausführungsplan unterschiedlich tun. Mit anderen Worten, die Sortierreihenfolge dieser Zeilen ist in Bezug auf die ungeordneten Spalten nicht deterministisch.

Um das oben Gesagte zusammenzufassen: Wenn Sie ORDER BY zum Sortieren von Spalten verwenden und mehrere Zeilen mit denselben Daten in der entsprechenden ( ORDER BY Spalte) vorhanden sind, gibt der ( Mysql )Server diese Zeilen in beliebiger Reihenfolge zurück. Je nach dem gesamten Ausführungsplan kann dies auch auf unterschiedliche Weise geschehen.

Um es einfach auszudrücken: Wenn bei per ORDER BY abgefragten Daten mehrere Zeilen mit identischen Daten in ORDER BY vorhanden sind, gibt Mysql diese in zufälliger Reihenfolge zurück. Dies führt zu einer ungeordneten Situation, auch wenn eine Sortierung verwendet wird.

3. Lösung

Die grundlegende Lösung des oben genannten Problems besteht darin, eine Duplizierung von Werten in ORDER BY zu vermeiden. Daher können weitere Dimensionen hinzugefügt werden, beispielsweise andere Sortierspalten wie die ID.

Wählen Sie * aus tb_order, Reihenfolge nach Erstellungszeit, ID desc;

Auf diese Weise wird, wenn create_time gleich ist, nach der ID sortiert, und die ID ist definitiv unterschiedlich, sodass das oben genannte Problem nicht mehr auftritt.

4. Erweitern Sie Ihr Wissen

Tatsächlich wurden die obigen Inhalte auf der offiziellen Mysql -Website klar erläutert und es werden auch Beispiele angegeben. Nachfolgend finden Sie eine kurze Zusammenfassung der Inhalte und Beispiele der offiziellen Website.

4.1 Abfrageoptimierung einschränken

Wenn wir nur einen Teil eines Ergebnissatzes abfragen, sollten wir nicht alle Daten abfragen und anschließend die nicht benötigten Daten verwerfen, sondern diese durch die Grenzbedingung einschränken.

Wenn die Having-Bedingung nicht verwendet wird, kann MySQL die Limit-Bedingung optimieren:

• Wenn Sie nur wenige Datensätze abfragen müssen, empfiehlt es sich, limit zu verwenden, damit Mysql den Index verwenden kann, während Mysql normalerweise die gesamte Tabelle scannt.
• Wenn Sie limit row_count und order by zusammen verwenden, beendet MySQL die Sortierung, sobald es den ersten row_count -Ergebnissatz findet, statt den gesamten Ergebnissatz zu sortieren. Wenn Sie zu diesem Zeitpunkt indexbasiert arbeiten, ist die Geschwindigkeit höher. Wenn eine Dateisortierung erforderlich ist, werden möglicherweise einige oder alle der qualifizierten Ergebnisse sortiert, bevor row_count -Ergebnissatz gefunden wird. Aber sobald das row_count -Ergebnis gefunden wurde, wird der Rest nicht sortiert. Eine Ausprägung dieser Funktion besteht darin, dass die Reihenfolge der zurückgegebenen Ergebnisse bei Abfragen mit oder ohne Begrenzung, wie bereits erwähnt, unterschiedlich sein kann.
• Wenn Sie limit row_count und „distinct“ zusammen verwenden, wird MySQL sofort angehalten, nachdem die eindeutige Zeile im row_count -Ergebnissatz gefunden wurde.
• In einigen Fällen können Sie eine Gruppierung implementieren, indem Sie den Index sequenziell lesen (oder sortieren) und dann Zusammenfassungen berechnen, bis sich der Index ändert. In diesem Fall zählt limit row_count keine unnötigen group by .
• Sobald MySQL die erforderliche Zeilenanzahl an den Client gesendet hat, bricht es die Abfrage ab, sofern nicht SQL_CALC_FOUND_ROWS verwendet wurde. In diesem Fall können Sie SELECT FOUND_ROWS() verwenden, um die Anzahl der Zeilen abzurufen.
• LIMIT 0 gibt schnell eine leere Sammlung zurück und wird häufig verwendet, um die Gültigkeit von SQL zu überprüfen. Es kann auch verwendet werden, um den Typ des Ergebnissatzes in der Anwendung zu ermitteln. Im MySQL-Client können Sie --column-type-info verwenden, um den Ergebnisspaltentyp anzuzeigen.
• Wenn Sie zum Auflösen der Abfrage eine temporäre Tabelle verwenden, berechnet Mysql anhand limit row_count , wie viel Speicherplatz benötigt wird.
• Wenn order by -Anweisung keinen Index verwendet und eine Grenzbedingung vorliegt, kann der Optimierer auf die Verwendung von Merge-Dateien verzichten und stattdessen einen Memory- filesort -Vorgang zum Sortieren der Zeilen im Speicher verwenden.

Nachdem wir nun einige Funktionen von limit kennengelernt haben, kehren wir zum Schwerpunkt dieses Artikels zurück, der kombinierten Verwendung von limit row_count und order by .

4.2 Limit und Order by gemeinsam verwenden

Wie im zweiten Punkt oben erwähnt, besteht eine der Eigenschaften der Kombination aus limit row_count und order by , dass die Reihenfolge, in der die Ergebnisse zurückgegeben werden, ungewiss ist. Ein Faktor, der den Ausführungsplan beeinflusst, ist limit . Daher kann die Reihenfolge der zurückgegebenen Ergebnisse unterschiedlich sein, wenn dieselbe Abfrageanweisung mit oder limit ausgeführt wird limit

Im folgenden Beispiel wird die Sortierabfrage basierend auf der Kategoriespalte ausgeführt, während die ID und die Bewertung unsicher sind:

mysql> SELECT * FROM Bewertungen ORDER BY Kategorie;
+----+----------+--------+
| ID | Kategorie | Bewertung |
+----+----------+--------+
| 1 | 1 | 4,5 |
| 5 | 1 | 3,2 |
| 3 | 2 | 3,7 |
| 4 | 2 | 3,5 |
| 6 | 2 | 3,5 |
| 2 | 3 | 5,0 |
| 7 | 3 | 2,7 |
+----+----------+--------+

Wenn eine Abfrageanweisung ein Limit enthält, kann sie sich auf Daten mit demselben Kategoriewert auswirken:

mysql> SELECT * FROM Bewertungen ORDER BY Kategorie LIMIT 5;
+----+----------+--------+
| ID | Kategorie | Bewertung |
+----+----------+--------+
| 1 | 1 | 4,5 |
| 5 | 1 | 3,2 |
| 4 | 2 | 3,5 |
| 3 | 2 | 3,7 |
| 6 | 2 | 3,5 |
+----+----------+--------+

Die Ergebnispositionen der ID 3 und 4 haben sich geändert.

In der Praxis ist es oft sehr wichtig, die Reihenfolge der Abfrageergebnisse beizubehalten. In diesem Fall müssen andere Spalten eingeführt werden, um die Reihenfolge der Ergebnisse sicherzustellen.

Nachdem die ID im obigen Beispiel eingeführt wurde, lauten die Abfrageanweisung und die Ergebnisse wie folgt:

mysql> SELECT * FROM Bewertungen ORDER BY Kategorie, ID;
+----+----------+--------+
| ID | Kategorie | Bewertung |
+----+----------+--------+
| 1 | 1 | 4,5 |
| 5 | 1 | 3,2 |
| 3 | 2 | 3,7 |
| 4 | 2 | 3,5 |
| 6 | 2 | 3,5 |
| 2 | 3 | 5,0 |
| 7 | 3 | 2,7 |
+----+----------+--------+
​
mysql> SELECT * FROM Bewertungen ORDER BY Kategorie, ID LIMIT 5;
+----+----------+--------+
| ID | Kategorie | Bewertung |
+----+----------+--------+
| 1 | 1 | 4,5 |
| 5 | 1 | 3,2 |
| 3 | 2 | 3,7 |
| 4 | 2 | 3,5 |
| 6 | 2 | 3,5 |
+----+----------+--------+

Es ist ersichtlich, dass beim Hinzufügen der Sortierung der ID-Spalte kein Unordnungsproblem auftritt, selbst wenn category gleich sind. Dies steht im Einklang mit unserer ursprünglichen Lösung.

5. Zusammenfassung

Ursprünglich haben wir aufgrund gelegentlicher Fallstricke in der Praxis über die Optimierung von limit Mysql gesprochen und eine Lösung bereitgestellt, die den Geschäftsanforderungen entsprach und Fehler in der Geschäftslogik vermied.

Viele Freunde verwenden order by und limit -Anweisungen für Abfragen. Wenn Sie diese Optimierungsfunktionen von Mysql jedoch nicht kennen, sind Sie möglicherweise in die Falle getappt, aber die Datenmenge hat die Präsentation nicht ausgelöst.

Dies ist das Ende dieses Artikels über die Details der MySQL-Sortierfunktionen. Weitere relevante MySQL-Sortierfunktionen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!