Nutzungs- und Leistungsoptimierungstechniken für die Aggregatfunktion „count“ in MySQL

Die Umgebung dieses Artikels ist Windows 10 und die MySQL-Version ist 5.7.12-log

1. Grundlegende Verwendung

Es gibt zwei grundlegende Funktionen von count:

• Zählen Sie die Anzahl der Daten in einer Spalte;
• Zählen Sie die Anzahl der Zeilen im Ergebnissatz.

Wird verwendet, um die Anzahl der Daten zu ermitteln, die die Bedingungen erfüllen. Es gibt jedoch einige Situationen, in denen sich der verwendete Eindruck unterscheidet. Beispielsweise ist der Effekt unterschiedlich, wenn die Zählung auf eine oder mehrere Spalten angewendet wird oder wenn * zum Ausdrücken der gesamten Zeile verwendet wird.

Die Beispieltabelle sieht wie folgt aus:

CREATE TABLE `NewTable` (
`id` int(11) NULL DEFAULT NULL ,
`name` varchar(30) NULL DEFAULT NULL ,
`Land` varchar(50) NULL DEFAULT NULL ,
`Provinz` varchar(30) NULL DEFAULT NULL ,
`Stadt` varchar(30) NULL DEFAULT NULL 
)ENGINE=InnoDB 

1.1 NULL-Werte nicht zählen

Wenn NULL-Werte vorhanden sind, werden sie in den zurückgegebenen Ergebnissen herausgefiltert.

Wählen Sie Anzahl (Land) aus Person aus.

Die zurückgegebenen Ergebnisse lauten wie folgt:

Wenn das Datenelement, das die Bedingungen erfüllt, nicht vorhanden ist, gibt die Struktur 0 zurück. Diese Methode wird häufig verwendet, um zu bestimmen, ob Daten vorhanden sind, die die Bedingungen erfüllen. Der zurückgegebene Datentyp ist Bigint.

1.2 Verarbeitung der Anzahl(*)

Die Verarbeitung von count(*) ist etwas anders. Es wird die Anzahl aller Daten zurückgegeben, aber es werden keine NULL-Werte herausgefiltert. Es ist nicht gleichbedeutend mit der Erweiterung in alle Spalten, sondern ignoriert direkt alle Spalten und zählt alle Zeilen direkt. Die Anweisung lautet wie folgt:

wähle count(*) aus Person;

Die zurückgegebenen Ergebnisse lauten wie folgt:

Wenn Sie die Anzahl aller Daten zurückgeben möchten, aber keine Spalten einschließen möchten, die alle NULL sind, können Sie count(*) nicht verwenden. In 1.1 wird jedoch erwähnt, dass count NULL herausfiltert, wenn es auf eine Spalte einwirkt. Ist es also richtig, es direkt so zu schreiben?

Wählen Sie die Anzahl (ID, „Name“, Land, Provinz, Stadt) aus der Person aus.

Das ist falsch. Count kann nur für eine einzelne Spalte verwendet werden, nicht für mehrere Spalten. Daher ist die obige Schreibweise falsch.

Darüber hinaus wurde die count(*)-Anweisung in der MyISAM-Speicher-Engine optimiert. Die Anzahl der Datenzeilen in jeder Tabelle wird in der Speicher-Engine gespeichert und kann schnell abgerufen werden. In transaktionalen Speicher-Engines wie InnoDB sind jedoch mehrere Transaktionen beteiligt.

1.3 Verarbeitung von count(distinct …)

count(distinct ...) gibt die Anzahl der Zeilen zurück, die unterschiedlich, aber nicht NULL sind. Dies unterscheidet sich von der ausschließlichen Verwendung von distinct, da distinct keine NULL-Werte filtert. Weitere Einzelheiten finden Sie unter So verwenden Sie distinct in MySQL.

– Wenn keine Daten vorhanden sind, die die Bedingungen erfüllen, wird 0 zurückgegeben;
- Diese Anweisung kann auf mehrere Spalten angewendet werden. Wenn es einen Unterschied zwischen den Spalten gibt, wird die gesamte Datenzeile als unterschiedlich betrachtet, was den gleichen Effekt hat wie die Anwendung von distinct auf mehrere Spalten.

Wählen Sie die Anzahl (DISTINCT country) aus der Person aus.

Die zurückgegebenen Ergebnisse lauten wie folgt:

Für die Kombination aus count(*) und count(distinct) gilt jedoch Folgendes:

Wählen Sie Anzahl (DISTINCT *) von Person aus;

Diese Anweisung ist falsch und kann nicht ausgeführt werden. Sie unterscheidet sich daher von „select count(DISTINCT *) from person“.

2. Leistungsoptimierung

Normalerweise erfordert der count(*)-Vorgang das Scannen einer großen Anzahl von Zeilen in der Datentabelle. Das Vermeiden des Scannens einer großen Datenmenge ist der Schlüssel zur Optimierung der Anweisung. Dieses Problem kann aus den folgenden zwei Perspektiven betrachtet werden.

2.1 Optimierung auf Datenbankebene

2.1.1 Für count(*)

MySQL wurde intern für count(*) optimiert. Die Abfrage mit Explain lautet wie folgt:

ERKLÄREN Sie, wählen Sie count(*) von person aus;

Es ist ersichtlich, dass die Abfrage weder einen vollständigen Tabellenscan noch einen Index verwendet und nicht einmal die Datentabelle abfragen muss. In der obigen Beispieldatenbank ist die Speicher-Engine der Bibliothek InnoDB und es gibt weder einen Primärschlüssel noch einen Index.

2.2 Zählen einer einzelnen Spalte

Die Abfrage lautet wie folgt:

ERKLÄREN Sie, wählen Sie Anzahl (Land) aus Personen aus, bei denen ID > 2 ist;

Es wurde festgestellt, dass die gesamte Tabelle ohne Primärschlüssel und Index gescannt wurde. Eine der direktesten Möglichkeiten, das Scannen einer großen Anzahl von Zeilen in den Daten zu vermeiden, ist die Verwendung von Indizes:

Wenn die ID als allgemeiner Index festgelegt ist: INDEX abc (id) USING BTREE.

Führen Sie die Abfrage wie folgt aus:

ERKLÄREN Sie, wählen Sie Anzahl (Land) aus Personen aus, bei denen ID > 2 ist;

Die Ergebnisse sind wie folgt:

Zu diesem Zeitpunkt wird festgestellt, dass der Index nicht verwendet wird und der vollständige Tabellenscan dennoch durchgeführt wird. Wenn Folgendes ausgeführt wird:

ERKLÄREN Sie, wählen Sie Anzahl (Land) aus Personen aus, bei denen ID > 4 ist;

Die Ergebnisse sind wie folgt:

Dies ist eine Bereichsabfrage mit einem Index, die offensichtlich besser ist als die obige.

Die Frage ist jedoch, warum wir manchmal Indizes verwenden und manchmal nicht? In der ersten Abfrage oben wurden mögliche Schlüssel erkannt, aber nicht verwendet? Wenn es jemand weiß, bitte erklären!

Legen Sie „id“ als Primärschlüssel fest und führen Sie die Abfrage wie folgt aus:

ERKLÄREN Sie, wählen Sie Anzahl (Land) aus Personen aus, bei denen ID > 2 ist;

Die Ergebnisse sind wie folgt:

2.2 Optimierung auf Anwendungsebene

Zur Optimierung auf Anwendungsebene können Sie die Einführung eines Cache-Subsystems in die Systemarchitektur in Erwägung ziehen, beispielsweise das früher häufig verwendete Memcached oder das heute sehr beliebte Redis, was jedoch die Komplexität des Systems erhöht.

Beispiele für MySQL-Gruppierungs- und Aggregatfunktionen (Summe, Anzahl usw.)

Sehen wir uns zunächst die MySQL-Aggregatfunktionen an.

Eine spezielle Funktion in MySQL: Aggregatfunktion, SUM, COUNT, MAX, MIN, AVG usw. Der grundlegende Unterschied zwischen diesen Funktionen und anderen Funktionen besteht darin, dass sie im Allgemeinen auf mehrere Datensätze einwirken. Zum Beispiel:

SELECT SUM(Punktzahl) FROM Tabelle

Mit diesem SQL soll die Summe aller Score-Spalten in der Tabelle abgefragt werden.

Als nächstes erklären wir anhand eines Beispiels die Verwendung von Aggregatfunktionen in der Group-By-Anweisung.

Die Büchertabelle sieht wie folgt aus:

Jetzt müssen wir die Städte gruppieren und die Anzahl der Datensätze in jeder Gruppe ermitteln. Wir benötigen die Aggregationsfunktion „Anzahl“.

SELECT *,Anzahl(*) FROM Buch GROUP BY Stadt

Das Ergebnis ist:

Teilen Sie zunächst die zurückgegebenen Datensätze in mehrere Gruppen basierend auf der Stadt auf. Dies ist die wörtliche Bedeutung von GROUP BY. Nach der Gruppierung werden Aggregatfunktionen verwendet, um verschiedene Felder (einen oder mehrere Datensätze) in jeder Gruppe zu bearbeiten.