Eine kurze Diskussion über die MySQL-Zeilenanzahl

Wir alle kennen die MySQL-Funktion count(), mit der die Anzahl der Zeilen in jeder Tabelle gezählt wird. Wenn Ihre Tabelle jedoch immer größer wird und Sie die InnoDB-Engine verwenden, werden Sie feststellen, dass die Berechnungsgeschwindigkeit immer langsamer wird. In diesem Artikel stellen wir zunächst die Prinzipien und Gründe für die Implementierung von count () vor, analysieren dann die Leistung verschiedener Verwendungen von count und bieten schließlich eine Lösung für Tabellen, die häufig geändert werden müssen und bei denen die Anzahl der Zeilen gezählt werden muss.

Implementierung von Count()

InnoDB und MyISAM sind häufig verwendete Daten-Engines für MySQL. Aufgrund der Unterschiede in ihrer Implementierung ist auch die Effizienz von count()-Operationen unterschiedlich.

Bei MyISAM wird die Gesamtzahl der Zeilen in jeder Tabelle auf der Festplatte gespeichert. Bei Verwendung der count(*)-Berechnung ist die Effizienz daher sehr hoch und das Ergebnis wird direkt zurückgegeben. Wenn jedoch eine Where-Bedingung hinzugefügt wird, wird dennoch eine Suche durchgeführt, sodass die Effizienz nicht hoch ist.

Bei InnoDB werden beim Ausführen von count(*)-Operationen die Daten zeilenweise aus der Engine gelesen und dann die Anzahl akkumuliert. Natürlich sinkt die Effizienz, je größer die Tabelle wird.

Warum kann InnoDB also nicht wie MyISAM in der Tabelle aufzeichnen? Der Grund hierfür liegt darin, dass InnoDB über mehr Funktionen zur Transaktionsunterstützung verfügt als MyISAM, allerdings sind auch gewisse Kompromisse erforderlich. Aufgrund der Steuerung durch MVCC verfügt MySQL über die Fähigkeit zur Parallelität. Das heißt, gleichzeitig ist die Anzahl der Zeilen der von InnoDB zurückgegebenen Tabelle nicht festgelegt. Die Anzahl der von der Transaktion angezeigten Zeilen hängt mit der Konsistenzansicht zusammen, nachdem diese aktiviert wurde. Mit anderen Worten, die Datenversion, die jede Transaktion sehen kann, ist unterschiedlich und kann nur Zeile für Zeile beurteilt werden.

Wie die folgende Transaktion, unter der Annahme, dass Tabelle t 10.000 Datensätze hat:

Für Sitzung A ist Sitzung B nicht sichtbar, da sie nicht übermittelt wurde. Sitzung C wurde übermittelt, aber nachdem Sitzung A gestartet wurde, daher ist sie ebenfalls unsichtbar. Es sind also 10.000.

Was Sitzung B betrifft, wurde Sitzung C vor ihrem Start festgeschrieben und ein weiterer Eintrag eingefügt. Das Ergebnis ist also 10002.

Tatsächlich hat InnoDB Optimierungen bei der Ausführung von count(*)-Operationen vorgenommen. Da der normale Index bei der Ausführung von count(*)-Operationen den ID-Wert des Primärschlüssels speichert, wird für die Suche der kleinste normale Indexbaum gefunden, anstatt den Indexbaum des Primärschlüssels zu durchlaufen.

Eine allgemeine Regel beim Entwurf von Datenbanksystemen besteht darin, die logische Korrektheit zu gewährleisten und gleichzeitig die Menge der gescannten Daten zu reduzieren.

Darüber hinaus können Sie bei Verwendung von „show table status“ auch sehr schnell die Anzahl der Zeilen abfragen. Dabei ist jedoch zu beachten, dass dieser Befehl zur Stichprobennahme und Schätzung den Wert der Indexstatistik verwendet. Im offiziellen Dokument heißt es, der Fehler könne 40–50 % betragen.

Aber was sollen wir tun, wenn wir die Anzahl der Zeilen in einer Tabelle wirklich in Echtzeit abrufen müssen?

Anzahl der Tabellen manuell speichern

Verwenden Sie ein Cache-System zum Speichern von Zählungen

Für Tabellen, die aktualisiert werden, möchten Sie möglicherweise ein Cache-System zur Unterstützung verwenden. Redis wird beispielsweise verwendet, um die Gesamtzahl der Zeilen in einer Tabelle zu speichern.

Bei jedem Eintrag in der Datenbank erhöht und verringert sich der Redis-Zähler um eins. Dadurch erscheinen die Lese- und Schreibvorgänge zwar schnell, es gibt jedoch einige Probleme.

Das Cache-System verliert Aktualisierungen:

Die Daten im Redis-Speicher müssen regelmäßig mit der Festplatte synchronisiert werden, es gibt jedoch keine Möglichkeit, einen abnormalen Neustart von Redis zu verhindern. Beispielsweise wird Redis nach dem Einfügen von Daten in Redis neu gestartet und die Daten werden nicht auf der Festplatte gespeichert. Zu diesem Zeitpunkt können Sie nach dem Neustart von Redis den Vorgang count(*) aus der Datenbank ausführen und ihn dann auf Redis aktualisieren. Ein vollständiger Tabellenscan ist weiterhin möglich.

Die Logik ist nicht präzise:

Angenommen, auf einer Seite müssen die Anzahl der Zeilen in einer Tabelle und jedes Datenelement angezeigt werden. Bei der Implementierung können Sie zuerst die Menge von Redis und dann den Datensatz aus der Datenbank abrufen.

Es kann jedoch vorkommen, dass:

1. Die Datenbank stellt fest, dass der zuletzt eingefügte Datensatz in den 100 Zeilen steht, aber die Redis-Anzahl ist um 1 niedriger.
2. Die Datenbank hat 100 Zeilen gefunden und es gab keinen neuesten Datensatz, aber die Redis-Anzahl hat sich um 1 erhöht.

Für Sitzung B wird zum Zeitpunkt T2 festgestellt, dass die Anzahl der Redis-Einträge um eins kleiner ist als die der Datenbank.

Für Sitzung B wird zum Zeitpunkt T2 festgestellt, dass die Anzahl der Redis-Datensätze um 1 größer ist als die der Datenbank.

Tatsächlich tritt das Problem auf, weil Redis- und Datenbankdatensatzabfragen nicht in derselben Transaktion erfolgen.

Mit Datenbank speichern

MySQL selbst unterstützt Transaktionen dank der Unterstützung der InnoDB-Engine. Indem Sie den Redis-Einfügevorgang durch einen Aktualisierungsvorgang in der Datenbank ersetzen, können Sie daher die Transaktionsfunktionen auf RR-Ebene nutzen, um die Datengenauigkeit sicherzustellen.

Ein weiterer Punkt ist, dass dank der Unterstützung des Redo-Logs Absturzsicherheit gewährleistet werden kann, wenn MySQL auf eine Ausnahme stößt.

Ausführungseffizienz verschiedener Zählverwendungen

count() selbst ist eine Aggregatfunktion, die den zurückgegebenen Ergebnissatz zeilenweise auswertet. Wenn der Parameter nicht NULL ist, wird die Akkumulation fortgesetzt und schließlich das Ergebnis zurückgegeben.

Daher bedeuten count(*), count(id) und count(1) alle, die Gesamtzahl der Zeilen im Ergebnissatz zurückzugeben, die die Bedingungen erfüllen.

Und count(field) gibt die Felder in den Datenzeilen an, die die Bedingungen erfüllen und nicht NULL sind.

Für count(id) durchläuft InnoDB die gesamte Tabelle, nimmt jede Zeilen-ID heraus und gibt sie an die Serverebene weiter. Der Server stellt fest, ob die ID leer ist und sammelt sie dann.

Für count(1) durchläuft InnoDB die gesamte Tabelle, ruft den Wert jedoch nicht ab. Die Serverebene fügt 1 in sich selbst ein und sammelt sie dann an.

Daher ist count(1) schneller als count(*), da kein Parsen von Datenzeilen und Kopieren von Feldwerten erforderlich ist.

Wenn für count(field) das Feld als ungleich null definiert ist, wird es zeilenweise gelesen, es wird festgestellt, dass es nicht null sein kann, und dann wird es akkumuliert. Wenn es bei der Definition null sein kann, muss der Wert während der Ausführung entfernt und nur akkumuliert werden, wenn er nicht null ist.

Die Ausnahme ist count(*), das speziell optimiert ist. Es nimmt keine Werte an, sondern sammelt sie direkt zeilenweise und findet den kleinsten Indexbaum zur Berechnung.

Zusammenfassen

Die Ausführungseffizienz der MySQL count()-Funktion hängt mit der zugrunde liegenden Daten-Engine zusammen. MyISAM fügt keine Where-Bedingungen hinzu, die Abfrage ist sehr schnell, unterstützt jedoch keine Transaktionen. InnoDB unterstützt Transaktionen. Aufgrund der Implementierung von MVCC muss jedoch bei jeder Abfrage jede Zeile einzeln gescannt werden, was ineffizient ist.

Die Lösung besteht darin, einen externen Cache wie Redis zum Speichern von Datensätzen zu entwerfen. Es gibt jedoch Fälle von abnormalen Neustarts und ungenauen Daten. Eine Lösung besteht darin, zum Speichern der Datensätze eine neue Tabelle in InnoDB zu erstellen.

Schließlich nimmt InnoDB unabhängige Optimierungen für count(*) vor, während andere Zähloperationen zusätzliche Operationen erfordern.

Oben finden Sie eine kurze Erläuterung der Details von MySQL count, das die Anzahl der Zeilen zählt. Weitere Informationen zu MySQL count finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!