Gründe, warum MySQL 8.0-Statistiken ungenau sind

Vorwort

Unabhängig davon, ob es sich um Oracle oder MySQL handelt, führen die in der neuen Version eingeführten neuen Funktionen einerseits zu Verbesserungen der Funktionalität, Leistung und Benutzerfreundlichkeit des Produkts usw., können aber andererseits auch einige Probleme mit sich bringen, wie etwa Codefehler oder durch falsche Verwendung durch den Kunden verursachte Probleme usw.

Fallstudie

MySQL 5.7-Szenario

(1) Erstellen Sie zunächst zwei Tabellen und fügen Sie Daten ein

mysql> Version auswählen();
+------------+
| version() |
+------------+
| 5.7.30-Protokoll |
+------------+
1 Zeile im Satz (0,00 Sek.)

mysql> zeigen erstellen Tabelle Test\G
*************************** 1. Reihe ***************************
    Tabelle: Test
Tabelle erstellen: CREATE TABLE `test` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NICHT NULL STANDARD '',
 `pad` char(60) NICHT NULL STANDARD '',
 Primärschlüssel (`id`),
 SCHLÜSSEL `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 Zeile im Satz (0,00 Sek.)

mysql> zeigen erstellen Tabelle sbtest1\G
*************************** 1. Reihe ***************************
    Tabelle: sbtest1
Tabelle erstellen: CREATE TABLE `sbtest1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NICHT NULL STANDARD '',
 `pad` char(60) NICHT NULL STANDARD '',
 Primärschlüssel (`id`),
 SCHLÜSSEL `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 Zeile im Satz (0,00 Sek.)

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
| 100 |
+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle count(*) aus sbtest1;
+----------+
| Anzahl(*) |
+----------+
| 1000000 |
+----------+
1 Zeile im Satz (0,14 Sek.)

(2) Überprüfen Sie die statistischen Informationen der beiden Tabellen, beide sind relativ genau

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='test' ist;
+--------------+------------+------------+
| Tabellenschema | Tabellenname | Tabellenzeilen |
+--------------+------------+------------+
| prüfen | testen | 100 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='sbtest1' ist;
+--------------+------------+------------+
| Tabellenschema | Tabellenname | Tabellenzeilen |
+--------------+------------+------------+
| Prüfung | sbtest1 | 947263 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

(3) Wir fügen weiterhin 10 Millionen Datensätze in die Testtabelle ein und überprüfen die Statistiken erneut. Die Statistiken sind immer noch relativ genau, da die Statistiken standardmäßig aktualisiert werden, wenn die Datenänderung 10 % überschreitet.

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
| 10000100 |
+----------+
1 Reihe im Satz (1,50 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='test' ist;
+--------------+------------+------------+
| Tabellenschema | Tabellenname | Tabellenzeilen |
+--------------+------------+------------+
| Prüfung | Prüfung | 9749036 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

MySQL 8.0-Szenario

(1) Als nächstes betrachten wir die Situation unter 8.0. Auf ähnliche Weise erstellen wir zwei Tabellen und fügen dieselben Datensätze ein.

mysql> Version auswählen();
+-------------+
| version() |
+-------------+
| 8.0.20 |
+-------------+
1 Zeile im Satz (0,00 Sek.)

mysql> zeigen erstellen Tabelle Test\G
*************************** 1. Reihe ***************************
    Tabelle: Test
Tabelle erstellen: CREATE TABLE `test` (
 `id` int unsigned NICHT NULL AUTO_INCREMENT,
 `k` int unsigned NICHT NULL STANDARD '0',
 `c` char(120) NICHT NULL STANDARD '',
 `pad` char(60) NICHT NULL STANDARD '',
 Primärschlüssel (`id`),
 SCHLÜSSEL `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 Zeile im Satz (0,00 Sek.)

mysql> zeigen erstellen Tabelle sbtest1\G
*************************** 1. Reihe ***************************
    Tabelle: sbtest1
Tabelle erstellen: CREATE TABLE `sbtest1` (
 `id` int unsigned NICHT NULL AUTO_INCREMENT,
 `k` int unsigned NICHT NULL STANDARD '0',
 `c` char(120) NICHT NULL STANDARD '',
 `pad` char(60) NICHT NULL STANDARD '',
 Primärschlüssel (`id`),
 SCHLÜSSEL `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 Zeile im Satz (0,00 Sek.)

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
| 100 |
+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle count(*) aus sbtest1;
+----------+
| Anzahl(*) |
+----------+
| 1000000 |
+----------+
1 Zeile im Satz (0,02 Sek.)

(2) Überprüfen Sie die statistischen Informationen der beiden Tabellen, beide sind relativ genau

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='test' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| prüfen | testen | 100 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='sbtest1' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| Prüfung | sbtest1 | 947468 |
+--------------+------------+------------+
1 Zeile im Satz (0,01 Sek.)

(3) In ähnlicher Weise fügen wir weiterhin 10 Millionen Datensätze in die Testtabelle ein und überprüfen die Statistik erneut. Wir stellen fest, dass table_rows immer noch 100 Datensätze anzeigt, was eine große Abweichung darstellt.

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
| 10000100 |
+----------+
1 Zeile im Satz (0,33 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='test' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| prüfen | testen | 100 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

Ursachenanalyse

Was sind also die Ursachen für ungenaue Statistiken? Tatsächlich speichert MySQL 8.0 die statistischen Informationen in den Ansichtstabellen und Statistiken zwischen, um die Abfrageeffizienz von information_schema zu verbessern. Die Ablaufzeit des Caches wird durch den Parameter information_schema_stats_expiry bestimmt, der standardmäßig 86400 Sekunden beträgt. Wenn Sie die neuesten statistischen Informationen abrufen möchten, können Sie die folgenden zwei Methoden verwenden:

(1) Tabelle analysieren

(2) Setzen Sie information_schema_stats_expiry=0

Weiter erkunden

Was sind also die Folgen ungenauer statistischer Informationen? Wird es den Ausführungsplan beeinflussen? Als nächstes testen wir es noch einmal

Test 1: Die Anzahl der Datensätze in der Tabelle „Test“ beträgt 100 und die Anzahl der Datensätze in der Tabelle „sbtest1“ beträgt 1 Million.

Führen Sie das folgende SQL aus und überprüfen Sie den Ausführungsplan. Es wird NLJ verwendet. Der kleine Tabellentest wird als treibende Tabelle (vollständiger Tabellenscan) verwendet, und die große Tabelle sbtest1 wird als getriebene Tabelle (Primärschlüsselzuordnung) verwendet. Die Ausführungseffizienz ist sehr schnell.

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
| 100 |
+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle count(*) aus sbtest1;
+----------+
| Anzahl(*) |
+----------+
| 1000000 |
+----------+
1 Zeile im Satz (0,02 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='test' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| prüfen | testen | 100 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='sbtest1' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| Prüfung | sbtest1 | 947468 |
+--------------+------------+------------+
1 Zeile im Satz (0,01 Sek.)

mysql> select t.* from test t inner join sbtest1 t1 on t.id=t1.id where tc='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977';
+----+--------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
| Ich würde | k | c | Pad |
+----+--------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+--------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

mysql> erklären Sie select t.* from test t inner join sbtest1 t1 on t.id=t1.id where tc='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' und t1.c='08566691963-88624912351-16662227201-4664
+----+----------+----------+---------+--------+---------------+--------+-----------+---------+-----------+-----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+--------+---------------+--------+-----------+---------+-----------+-----------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 10.00 | Verwenden von where |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id | 1 | 10.00 | Verwenden von where |
+----+----------+----------+---------+--------+---------------+--------+-----------+---------+-----------+-----------+----------+-------------+
2 Zeilen im Satz, 1 Warnung (0,00 Sek.)

Test 2: Die Tabelle test hat etwa 10 Millionen Datensätze und die Tabelle sbtest1 hat 1 Million Datensätze

Führen Sie SQL erneut aus und überprüfen Sie den Ausführungsplan. Er folgt ebenfalls NLJ. Die kleine Tabelle sbtest1 wird als treibende Tabelle verwendet und die große Tabelle test wird als getriebene Tabelle verwendet. Dies ist auch der richtige Ausführungsplan.

mysql> wähle count(*) aus Test;
+----------+
| Anzahl(*) |
+----------+
| 10000100 |
+----------+
1 Zeile im Satz (0,33 Sek.)

mysql> wähle count(*) aus sbtest1;
+----------+
| Anzahl(*) |
+----------+
| 1000000 |
+----------+
1 Zeile im Satz (0,02 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='test' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| prüfen | testen | 100 |
+--------------+------------+------------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle table_schema, table_name, table_rows aus Tabellen, in denen table_name='sbtest1' ist;
+--------------+------------+------------+
| TABELLENSCHEMA | TABELLENNAME | TABELLENZEILEN |
+--------------+------------+------------+
| Prüfung | sbtest1 | 947468 |
+--------------+------------+------------+
1 Zeile im Satz (0,01 Sek.)

mysql> select t.* from test t inner join sbtest1 t1 on t.id=t1.id where tc='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977';
+----+--------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
| Ich würde | k | c | Pad |
+----+--------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+--------+---------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+
1 Zeile im Satz (0,37 Sek.)

mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where tc='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977';
+----+----------+----------+---------+--------+---------------+--------+---------+---------+------------+--------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+--------+---------------+--------+---------+---------+------------+--------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 947468 | 10.00 | Verwenden von where |
| 1 | SIMPLE | t | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | 10.00 | Verwenden von where |
+----+----------+----------+---------+--------+---------------+--------+---------+---------+------------+--------+----------+-------------+
2 Zeilen im Satz, 1 Warnung (0,01 Sek.)

Warum hat der Optimierer nicht den falschen Ausführungsplan gewählt? Wie im vorherigen Artikel erwähnt, speichert MySQL 8.0 Metadateninformationen in der Datenwörterbuchtabelle unter der MySQL-Bibliothek. Die Bibliothek information_schema bietet Benutzern nur relativ praktische Ansichten zum Abfragen. Wenn der Optimierer einen Ausführungsplan auswählt, erhält er daher statistische Informationen aus der Datenwörterbuchtabelle und generiert einen korrekten Ausführungsplan.

Zusammenfassen

Um die Abfrageeffizienz von information_schema zu verbessern, speichert MySQL 8.0 die statistischen Informationen in den Ansichtstabellen und Statistiken zwischen. Die Ablaufzeit des Caches wird durch den Parameter information_schema_stats_expiry bestimmt (es wird empfohlen, den Parameterwert auf 0 zu setzen). Dies kann dazu führen, dass Benutzer beim Abfragen der entsprechenden Ansichten nicht die neuesten und genauesten statistischen Informationen erhalten können, hat jedoch keinen Einfluss auf die Auswahl des Ausführungsplans.

Oben sind die Einzelheiten zu den Gründen aufgeführt, warum die MySQL 8.0-Statistiken ungenau sind. Weitere Informationen zu ungenauen MySQL 8.0-Statistiken finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!

Das könnte Sie auch interessieren:

Beispiel für einen Persistenzbetrieb mit Gearman + MySQL
Detaillierte Erläuterung der Bereitstellung von MySQL mit Docker (Datenpersistenz)
Detaillierte Erklärung der Java-Emoji-Persistenz in MySQL
Neue Funktionen von MySQL 8: So ändern Sie persistente globale Variablen
Neue Funktionen von MySQL 8: Detaillierte Erklärung der Persistenz des automatisch inkrementierten Primärschlüssels
Übersicht über MySQL-Statistiken
Detaillierte Erläuterung der persistenten MySQL-Statistiken

<<: Javascript-Grundlagen zu integrierten Objekten

>>: 5 Befehle zur Verwendung des Rechners in der Linux-Befehlszeile

Dockerfile-Implementierungscode beim Starten von zwei Prozessen in einem Docker-Container

Artikel empfehlen

Detaillierte Erläuterung zur Lösung des Konflikts von Projekt-URLs, der durch das Festlegen des Standardpfads von Tomcat verursacht wird

Vorwort Tomcat ist ein hervorragender Java-Contai...

Gründe, warum MySQL 8.0-Statistiken ungenau sind

Dockerfile-Implementierungscode beim Starten von zwei Prozessen in einem Docker-Container

Detaillierte Erläuterung des Vuex-Gesamtfalls

CSS- und HTML- und Front-End-Technologie-Schichtendiagramm

Lösen Sie das Problem, dass Zeilenhöhe = Elementhöhe, der Text aber nicht vertikal zentriert ist

Detaillierte grafische Erklärung zum Löschen des Keep-Alive-Cache

Die Architektur und Praxis der Synchronisierung von Meituan DB-Daten mit dem Data Warehouse

Vorschläge zum Erstellen geschäftlicher HTML-E-Mails

Details zur Verwendung von aktiviertem Vue in untergeordneten Komponenten

Warum wird in React nicht empfohlen, einen Index als Schlüssel zu verwenden?

Eine kurze Diskussion über die Lösung des Problems der nativen Seitenkompatibilität mit IE9

Artikel empfehlen

Detaillierte Erläuterung zur Lösung des Konflikts von Projekt-URLs, der durch das Festlegen des Standardpfads von Tomcat verursacht wird

Beispiel zum Erstellen eines Datenbanknamens mit Sonderzeichen in MySQL

Lösen Sie schnell das Problem des langsamen Starts nach der Tomcat-Neukonfiguration

So verwenden Sie JSONP in Vue

Linux Redis-Sentinel-Konfigurationsdetails

Docker verwendet CMD- oder ENTRYPOINT-Befehle, um mehrere Dienste gleichzeitig zu starten

Eine kurze Diskussion zu this.$store.state.xx.xx in Vue

Detaillierte Erklärung der atomaren DDL-Syntax von MySQL 8.0

Einführung in den Swap-Speicher des Linux-Systems

MySQL 5.7.29 + Win64-Dekomprimierungsversion Installations-Tutorial mit Bildern und Text

Lösung für das Problem, dass sich der mysql8.0.11-Client nicht anmelden kann

Detaillierte Erklärung der MySQL/Java-Server-Unterstützung für Emoji und Problemlösung

Analyse der Vorteile von Klassen gegenüber IDs beim Kommentieren von HTML-Elementen

Implementierung der Header-Informationen für Nginx-Operationsantworten

Detaillierte Analyse des Explain-Ausführungsplans in MySQL