Detaillierte Erklärung der Zählung ohne Filterbedingungen in MySQL

zählen(*)

erreichen

1. MyISAM: Speichert die Gesamtzahl der Zeilen in der Tabelle auf der Festplatte und kann die Anzahl der Zeilen für Abfragen ohne Filterbedingungen direkt zurückgeben.

Wenn eine Count(*)-Filterbedingung vorliegt, kann MyISAM nicht schnell zurückkehren.

2. InnoDB: Daten zeilenweise aus der Speicher-Engine lesen und dann die Anzahl akkumulieren

Aufgrund von MVCC ist unklar, wie viele Zeilen InnoDB gleichzeitig zurückgeben soll.

Beispiel

Angenommen, die Tabelle t hat 10.000 Datensätze

Im letzten Moment fragen drei Sitzungen gleichzeitig die Gesamtzahl der Zeilen von t ab, aber die Ergebnisse sind unterschiedlich

Die Standardtransaktionsisolationsstufe von InnoDB ist RR, die durch MVCC implementiert wird

• Jede Transaktion muss feststellen, ob jede Datensatzzeile für sie selbst sichtbar ist

Optimierung

1. InnoDB ist eine indexorganisierte Tabelle

• Clustered Index Tree: Blattknoten sind Daten
• Sekundärer Indexbaum: Blattknoten sind Primärschlüsselwerte

2. Der vom sekundären Indexbaum belegte Speicherplatz ist viel kleiner als der des gruppierten Indexbaums

3. Der Optimierer durchläuft den kleinsten Indexbaum, um die Menge der gescannten Daten zu minimieren und gleichzeitig die richtige Logik sicherzustellen.

• Bei Zähloperationen ohne Filterbedingungen ist der Effekt derselbe, unabhängig davon, welcher Indexbaum durchlaufen wird.
• Der Optimierer wählt den besten Indexbaum für count(*)

Tabellenstatus anzeigen

mysql> TABELLENSTATUS ANZEIGEN\G;
*************************** 1. Reihe ***************************
 Name: t
 Engine: InnoDB
 Version: 10
 Row_format: Dynamisch
 Zeilen: 100256
 Durchschnittliche Zeilenlänge: 47
 Datenlänge: 4734976
Max_data_length: 0
 Indexlänge: 5275648
 Datenfrei: 0
 Auto_increment: NULL
 Erstellungszeit: 2019-02-01 17:49:07
 Update_time: NULL
 Check_time: NULL
 Sortierung: utf8_general_ci
 Prüfsumme: NULL
 Erstellungsoptionen:
 Kommentar:

SHOW TABLE STATUS schätzt auch durch Stichproben (sehr ungenau), mit einem Fehler von 40%~50%

Wartungszähler

Cache

planen

• Verwenden Sie Redis, um die Gesamtzahl der Zeilen in der Tabelle zu speichern (keine Filterbedingungen).
• Jedes Mal, wenn eine Zeile in diese Tabelle eingefügt wird, zählt Redis +1, und jedes Mal, wenn eine Zeile gelöscht wird, zählt Redis -1

Mangel

Verlorene Updates

1. Redis kann Updates verlieren

2. Lösung: Führen Sie count(*) einmal in der Datenbank aus, nachdem Redis abnormal neu gestartet wurde

• Ungewöhnliche Neustarts sind selten, daher sind die Kosten für einen vollständigen Tabellenscan akzeptabel.

Unpräzise Logik – fatal

1. Szenario: Anzeige der Gesamtzahl der Betriebsdatensätze und der 100 aktuellsten Betriebsdatensätze

2. Redis und MySQL sind zwei unterschiedliche Speichersysteme und unterstützen keine verteilten Transaktionen. Daher ist es unmöglich, eine genaue und konsistente Ansicht zu erhalten.

Zeitpunkt A

Zum Zeitpunkt T3 findet Sitzung B die 100 Zeilen, die den zuletzt eingefügten Datensatz enthalten, aber Redis hat +1 noch nicht hinzugefügt, was eine logische Inkonsistenz darstellt.

Zeitpunkt B

Zum Zeitpunkt T3 stellt Sitzung B fest, dass die 100 Zeilen nicht den zuletzt eingefügten Datensatz enthalten, Redis jedoch 1 hinzugefügt hat, was eine logische Inkonsistenz darstellt.

Datenbank

• Tragen Sie den Zählwert in eine separate Zähltabelle C in der Datenbank ein
• Mit der Crash-Safe-Funktion von InnoDB wird das Problem des Crash-Loss gelöst
• Mit der Transaktionsunterstützungsfunktion von InnoDB wird das Problem der konsistenten Ansicht gelöst
• Zum Zeitpunkt T3 wurde die Transaktion von Sitzung B in Sitzung A noch nicht festgeschrieben, sodass der Zählwert +1 von Tabelle C für sich selbst nicht sichtbar ist und die Logik konsistent ist.

Durchführung der Zählung

Semantik

1. count() ist eine Aggregatfunktion, die den zurückgegebenen Ergebnissatz zeilenweise beurteilt.

Wenn der Parameterwert der Zählfunktion nicht NULL ist, wird der kumulierte Wert um 1 erhöht, andernfalls erfolgt keine Erhöhung und der kumulierte Wert wird zurückgegeben.

2. Anzahl (Feld F)

• Feld F kann NULL sein
• Gibt die Gesamtzahl der Nicht-NULL-Felder im Ergebnissatz an, die die Bedingungen erfüllen.

3. Anzahl(Primärschlüssel-ID), Anzahl(1), Anzahl(*)

• Darf nicht NULL sein
• Gibt die Gesamtzahl der Ergebnismengen an, die die zurückgegebenen Bedingungen erfüllen.

4. Die InnoDB-Engine gibt alle Felder zurück, die die Serverebene benötigt.

• count(*) ist eine Ausnahme, es gibt nicht die gesamte Zeile zurück, sondern nur die leere Zeile

Leistungsvergleich

Anzahl (Feld F)

1. Wenn für Feld F definiert ist, dass es keinen NULL-Wert zulässt, lesen Sie dieses Feld zeilenweise aus dem Datensatz und akkumulieren Sie es nach der Beurteilung zeilenweise.

• Aufgrund der Tabellenstruktur ist es unmöglich, dass dieses Feld NULL ist.

2. Wenn Feld F so definiert ist, dass es NULL zulässt, lesen Sie dieses Feld zeilenweise aus dem Datensatz und akkumulieren Sie es zeilenweise, nachdem Sie die Beurteilung getroffen haben.

• Gemessen an der Tabellenstruktur kann dieses Feld NULL sein
• Bestimmen Sie, ob der Feldwert tatsächlich NULL ist

3. Wenn es keinen Sekundärindex für Feld F gibt, können Sie nur die gesamte Tabelle durchlaufen (Clusterindex).

4. Da InnoDB das Feld F zurückgeben muss, kann der Optimierer weniger Optimierungsentscheidungen treffen

• Sie können beispielsweise nicht den besten Index zum Tranieren auswählen

Anzahl (Primärschlüssel-ID)

• InnoDB durchläuft die gesamte Tabelle (Clusterindex), nimmt den ID-Wert jeder Zeile heraus und gibt ihn an die Serverebene zurück.
• Nachdem die Serverebene die ID erhalten hat, stellt sie fest, dass sie nicht NULL sein kann, und sammelt sie dann zeilenweise
• Der Optimierer kann den besten Index zum Durchlaufen auswählen

Anzahl(1)

1. Die InnoDB-Engine durchläuft die gesamte Tabelle (Clusterindex), nimmt aber keine Werte an
2. Die Serverebene trägt in jede zurückgegebene Zeile die Zahl 1 ein, um zu ermitteln, ob sie NULL ist, und akkumuliert sie zeilenweise.
3. count(1) ist schneller als count(Primärschlüssel-ID), da count(Primärschlüssel-ID) zwei Operationen umfasst.

• Analysieren von Datenzeilen
• Feldwert kopieren

zählen(*)

1. count(*) ruft nicht alle Werte ab, sondern ist speziell darauf optimiert, keine Werte abzurufen, da "*" definitiv nicht NULL ist und zeilenweise akkumuliert
2. Kein Wert: InnoDB gibt eine leere Zeile zurück, um der Serverebene mitzuteilen, dass sie nicht NULL ist und gezählt werden kann

Effizienz-Ranking

1. Anzahl(Feld F) < Anzahl(Primärschlüssel-ID) < Anzahl(1) ≈ Anzahl(*)
2. Versuchen Sie, count(*) zu verwenden.

Beispiel

mysql> ANZEIGEN TABELLE ERSTELLEN prop_action_batch_reward\G;
*************************** 1. Reihe ***************************
 Tabelle: prop_action_batch_reward
Tabelle erstellen: CREATE TABLE `prop_action_batch_reward` (
 `id` bigint(20) NICHT NULL,
 `Quelle` int(11) DEFAULT NULL,
 `serial_id` bigint(20) NICHT NULL,
 `create_time` datetime NICHT NULL STANDARD CURRENT_TIMESTAMP,
 `Benutzer-IDs` Mitteltext,
 `serial_index` tinyint(4) STANDARD '0',
 Primärschlüssel (`id`),
 EINDEUTIGER SCHLÜSSEL `uniq_serial_id_source_index` (`serial_id`,`source`,`serial_index`),
 SCHLÜSSEL `idx_create_time` (`Erstellungszeit`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8

Anzahl (Feld F)

Kein Index

Es gibt keinen Index für Benutzer-IDs, aber InnoDB muss das Feld „Benutzer-IDs“ zurückgeben, sodass es nur den gruppierten Index durchlaufen kann.

mysql> EXPLAIN SELECT COUNT(Benutzer-IDs) FROM prop_action_batch_reward;
+----+--------------+--------------------------+------+---------------+------+------+------+------+------+------+------+------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+--------------------------+------+---------------+------+------+------+------+------+------+------+------+
| 1 | EINFACH | prop_action_batch_reward | ALLE | NULL | NULL | NULL | NULL | 16435876 | NULL |
+----+--------------+--------------------------+------+---------------+------+------+------+------+------+------+------+------+

mysql> AUSWAHL ANZAHL(Benutzer-IDs) AUS prop_action_batch_reward;
+-----------------+
| Anzahl(Benutzer-IDs) |
+-----------------+
|17689788 |
+-----------------+
1 Zeile im Satz (10,93 Sek.)

Mit Index

1. Es gibt einen Index auf serial_id, der von uniq_serial_id_source_index durchlaufen werden kann

2. Da InnoDB jedoch das Feld „serial_id“ zurückgeben muss, wird es nicht die logisch äquivalente idx_create_time durchlaufen.

• Wenn idx_create_time ausgewählt ist und das Feld serial_id zurückgegeben wird, bedeutet dies, dass die Tabelle zurückgegeben werden muss.

mysql> ERKLÄREN SIE, WERDEN SIE ANZAHL(Serien-ID) AUS prop_action_batch_reward AUSGEWÄHLT;
+----+--------------+-----------+---------------+---------------+-----------------------------+--------+------+----------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+-----------+---------------+---------------+-----------------------------+--------+------+----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | index | NULL | uniq_serial_id_source_index | 15 | NULL | 16434890 | Index wird verwendet |
+----+--------------+-----------+---------------+---------------+-----------------------------+--------+------+----------+-------------+

mysql> ANZAHL(Serien-ID) AUSWÄHLEN AUS prop_action_batch_reward;
+------------------+
| Anzahl(Serien-ID) |
+------------------+
|17705069 |
+------------------+
1 Zeile im Satz (5,04 Sek.)

Anzahl (Primärschlüssel-ID)

Der Optimierer wählt den optimalen Index idx_create_time zum Durchlaufen anstelle des gruppierten Indexes

mysql> ERKLÄREN SIE: SELECT COUNT(id) FROM prop_action_batch_reward;
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16436797 | Index wird verwendet |
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql> ANZAHL(id) AUSWÄHLEN AUS prop_action_batch_reward;
+-------------+
| Anzahl(ID) |
+-------------+
|17705383 |
+-------------+
1 Reihe im Satz (4,54 Sek.)

Anzahl(1)

mysql> ERKLÄREN SIE COUNT AUSWÄHLEN (1) VON prop_action_batch_reward;
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16437220 | Index wird verwendet |
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql> ANZAHL AUSWÄHLEN(1) AUS prop_action_batch_reward;
+----------+
| Anzahl(1) |
+----------+
|17705808 |
+----------+
1 Reihe im Satz (4,12 Sek.)

zählen(*)

mysql> ERKLÄREN SIE, WERDEN SIE COUNT(*) AUSGEWÄHLT VON prop_action_batch_reward;
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
| 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16437518 | Index wird verwendet |
+----+--------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql> ANZAHL AUSWÄHLEN(*) AUS prop_action_batch_reward;
+----------+
| Anzahl(*) |
+----------+
|17706074|
+----------+
1 Zeile im Satz (4,06 Sek.)

Verweise

„MySQL Praxis 45 Vorlesungen“

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.