MySQL-Lösung zur funktionalen Indexoptimierung

Bei der Verwendung von MySQL führen viele Entwickler häufig Funktionsberechnungen für einige Spalten durch, was dazu führt, dass Indizes nicht verwendet werden können. Bei großen Datenmengen ist die Abfrageeffizienz gering. Um diese Situation zu beheben, optimiert dieser Artikel MySQL 5.7 und MySQL 8.0 auf unterschiedliche Weise.

1. MySQL 5.7

MySQL 5.7 unterstützt keine Funktionsindizes. Wenn Sie also auf einen Funktionsindex stoßen, müssen Sie ihn ändern. Andernfalls kann der Index während der Ausführung nicht verwendet werden, selbst wenn ein Index für das abgefragte Feld vorhanden ist, und es wird ein vollständiger Tabellenscan durchgeführt. Die Abfragezeit für Tabellen mit großen Datenmengen wird länger sein. Die konkreten Fälle sind wie folgt:

1.1 Testtabellen und Daten erstellen

mysql> testdb verwenden;
Datenbank geändert
mysql> Tabelle erstellen tb_function (ID int Primärschlüssel auto_increment, Name varchar (100), Erstellungszeit datetime);
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> einfügen in tb_function(name,create_time) values('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
Abfrage OK, 1 Zeile betroffen (0,02 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('CWQSsar3qcssg','2020-07-01 15:00:00');
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('vxfqrt2adafz','2020-07-01 21:30:00');
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> einfügen in tb_function(name,creatE_time) Werte('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('tuilklmdadq','2020-07-02 15:32:00');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('wesv2wqdshehq','2020-07-02 20:32:00');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('89yoijnlkwr1','2020-07-03 02:56:00');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('olj;nsaaq','2020-07-03 08:41:00');
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> einfügen in tb_function(name,creatE_time) values('ygo;jkdsaq','2020-07-03 16:20:00'); 
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> wähle * aus tb_function;
+----+--------------------------------------+---------------------+
| ID | Name | Erstellungszeit |
+----+--------------------------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 01.07.2020 12:00:00 |
| 2 | CWQSsar3qcssg | 01.07.2020 15:00:00 |
| 3 | vxfqrt2adafz | 01.07.2020 21:30:00 |
| 4 | etxzwrwbdhegqgaheqhag | 02.07.2020 01:30:00 |
| 5 | awrs433fsgvsfwtwg | 02.07.2020 03:30:00 |
| 6 | awrs433fsgvsfwtwg | 02.07.2020 07:32:00 |
| 7 | awrs433fsgvsfwtwg | 02.07.2020 10:32:00 |
| 8 | tuilklmdadq | 02.07.2020 15:32:00 |
| 9 | wesv2wqdshehq | 02.07.2020 20:32:00 |
| 10 | 89yoijnlkwr1 | 03.07.2020 02:56:00 |
| 11 | olj;nsaaq | 03.07.2020 08:41:00 |
| 12 | ygo;jkdsaq | 03.07.2020 16:20:00 |
+----+--------------------------------------+---------------------+
12 Zeilen im Satz (0,00 Sek.)

1.2 Erstellen Sie einen Index

Erstellen Sie einen Index für das Feld „create_time“

mysql> Tabelle ändern tb_function Schlüssel hinzufügen idx_create_time(create_time);
Abfrage OK, 0 Zeilen betroffen (0,13 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

1.3 Abfrage nach Zeit

Abfrage aller am 01.07.2020 erstellten Datensätze

mysql> wähle * aus tb_function, wobei date(Erstellungszeit)='2020-07-01';
+----+--------------------+---------------------+
| ID | Name | Erstellungszeit |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 01.07.2020 12:00:00 |
| 2 | CWQSsar3qcssg | 01.07.2020 15:00:00 |
| 3 | vxfqrt2adafz | 01.07.2020 21:30:00 |
+----+--------------------+---------------------+
3 Zeilen im Satz (0,00 Sek.)

Der Ausführungsplan sieht wie folgt aus:

mysql> erläutern Sie „select * from tb_function where date(create_time)='2020-07-01';“
+----+----------+-------------+------------+------+---------------+---------+---------+------+---------+------+---------+---------+---------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------------+------------+------+---------------+---------+---------+------+---------+------+---------+---------+---------+
| 1 | SIMPLE | tb_function | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100,00 | Verwenden von „where“ |
+----+----------+-------------+------------+------+---------------+---------+---------+------+---------+------+---------+---------+---------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Aus dem Ausführungsplan ist ersichtlich, dass ein vollständiger Scan durchgeführt wurde

1.4 Optimierung

Da MySQL 5.7 keine funktionalen Indizes unterstützt, müssen Sie die SQL-Schreibweise ändern, um die Indizierung zu implementieren (oder virtuelle Spalten verwenden). Das obige SQL kann geändert werden zu

mysql> select * from tb_function where create_time>='2020-07-01' und create_time<date_add('2020-07-01',INTERVAL 1 Tag);
+----+--------------------+---------------------+
| ID | Name | Erstellungszeit |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 01.07.2020 12:00:00 |
| 2 | CWQSsar3qcssg | 01.07.2020 15:00:00 |
| 3 | vxfqrt2adafz | 01.07.2020 21:30:00 |
+----+--------------------+---------------------+
3 Zeilen im Satz (0,00 Sek.)

Der Ausführungsplan sieht wie folgt aus:

mysql> erklären Sie „select * from tb_function where create_time>='2020-07-01' und create_time<date_add('2020-07-01',INTERVAL 1 day);“.
+----+----------+-------------+------------+-------+-----------------+-----------------+--------+------+------+------+----------+----------+----------+-----------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------------+------------+-------+-----------------+-----------------+--------+------+------+------+----------+----------+----------+-----------------------+
| 1 | SIMPLE | tb_function | NULL | Bereich | idx_create_time | idx_create_time | 6 | NULL | 3 | 100,00 | Indexbedingung wird verwendet |
+----+----------+-------------+------------+-------+-----------------+-----------------+--------+------+------+------+----------+----------+----------+-----------------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Es ist ersichtlich, dass der Index nach der Änderung verwendet wird.

2. MySQL 8.0

Die Indexfunktion von MySQL 8.0 fügt einen funktionalen Index hinzu. Tatsächlich wurde die virtuelle Spaltenfunktion in MySQL 5.7 eingeführt, und auch der Funktionsindex von MySQL 8.0 basiert auf virtuellen Spalten. Die Implementierung des obigen Falls in MySQL 8.0 wird wie folgt beschrieben.

2.1 Erstellen eines funktionalen Indexes

Erstellen Sie die obige Tabelle und die Daten auf der MySQL 8.0-Instanz und erstellen Sie dann einen Funktionsindex von create_time. Das SQL lautet wie folgt:

mysql> alter table tb_function add key idx_create_time((date(create_time))); -- Beachten Sie die Klammern um die Felder. Abfrage OK, 0 Zeilen betroffen (0,10 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

2.2 Abfrage nach Zeit

mysql> wähle * aus tb_function, wobei date(Erstellungszeit)='2020-07-01';
+----+--------------------+---------------------+
| ID | Name | Erstellungszeit |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 01.07.2020 12:00:00 |
| 2 | CWQSsar3qcssg | 01.07.2020 15:00:00 |
| 3 | vxfqrt2adafz | 01.07.2020 21:30:00 |
+----+--------------------+---------------------+
3 Zeilen im Satz (0,00 Sek.)

Der Ausführungsplan sieht wie folgt aus

mysql> erläutern Sie „select * from tb_function where date(create_time)='2020-07-01';“
+----+----------+-------------+------------+------+-----------------+-----------------+---------+-----------+-------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------------+------------+------+-----------------+-----------------+---------+-----------+-------+------+------+------+
| 1 | EINFACH | tb_funktion | NULL | ref | idx_create_time | idx_create_time | 4 | const | 3 | 100,00 | NULL |
+----+----------+-------------+------------+------+-----------------+-----------------+---------+-----------+-------+------+------+------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Es ist ersichtlich, dass nach dem Erstellen des entsprechenden Funktionsindex in MySQL 8.0 die entsprechende Funktionsberechnung auch für die Abfragespalte durchgeführt werden kann, ohne die SQL-Schreibmethode zu ändern.

Es gibt weitere Szenarien, die für die Optimierung von MySQL-Funktionsindizes und MySQL 8.0-Funktionsindizes getestet werden können. Es wird empfohlen, dass Sie es ausprobieren, um Ihre SQL-Umschreib- und Optimierungsfunktionen zu verbessern.

Oben finden Sie den detaillierten Inhalt des Optimierungsplans für den MySQL-Funktionsindex. Weitere Informationen zum MySQL-Funktionsindex und Optimierungsplan finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!

Das könnte Sie auch interessieren:

MySQL-Optimierungslösung: Aktivieren Sie das Protokoll für langsame Abfragen
Eine kurze Diskussion zur MySQL-Select-Optimierungslösung
MySQL-Abfrageoptimierung: Eine Tabellenoptimierungslösung für 1 Million Daten
Gründe und Optimierungslösungen für langsames MySQL-Limit-Paging mit großen Offsets
Eine kurze Diskussion über die MySQL-Optimierungslösung für große Tabellen
Referenz zur MySQL-Optimierungslösung
Mehrere gängige Optimierungslösungen für MySQL

<<: Das Vue-Projekt implementiert eine Fortschrittsbalkenfunktion für den Dateidownload

>>: Detaillierte Erläuterung verschiedener Fehlerbehandlungen, wenn Nginx nicht gestartet werden kann

So erstellen Sie einen MySQL-Cluster mit hoher Verfügbarkeit und Leistung

MySQL-Lösung zur funktionalen Indexoptimierung

So erstellen Sie einen MySQL-Cluster mit hoher Verfügbarkeit und Leistung

CSS Sticky Footer Mehrere Implementierungen

Auszeichnungssprache - Bildersetzung

So schreiben Sie DROP TABLE in verschiedene Datenbanken

So installieren Sie MySQL mit Yum auf Centos7 und stellen eine Remote-Verbindung her

Installations-Tutorial zur dekomprimierten Version von MySQL 5.7.23 mit Bildern und Text

Lösen Sie schnell das Problem, dass CentOS in VMware nicht auf das Internet zugreifen kann

Vue erzielt einen nahtlosen Karusselleffekt (Laufschrift)

Node implementiert Suchfeld für Fuzzy-Abfragen

Detaillierte Erklärung der Socket (TCP)-Bindung aus dem Linux-Quellcode

Artikel empfehlen

Detaillierte Schritte zum Erstellen eines NFS-Dateifreigabeservers unter Linux

Implementierung des Docker View Container Log-Befehls

MySQL-Schleife fügt zig Millionen Daten ein

Zusammenfassung verschiedener Möglichkeiten zum Erstellen von Objekten in js und ihrer Vor- und Nachteile

vue cli3 implementiert die Schritte der Verpackung nach Umgebung

Klicken Sie auf den Ankerlink in JS, um reibungslos zu scrollen und die obere Position frei anzupassen

Analyse von zwei Implementierungsmethoden zum Hinzufügen von statischem Routing in Linux

Zusammenfassung der Operatoroperationen, die in JavaScript sehr fehleranfällig sind

Implementierung von 2D- und 3D-Transformationen in CSS3

Schreiben von qualitativ hochwertigem Code – Praxisbuchauszüge zur Web-Frontend-Entwicklung

Warum kann mein Tomcat nicht starten?

Erkennung und Lösung von Vue.$set-Fehlerfallen

Detailliertes Installationstutorial für MySQL 5.7.11 unter Win7

Eine kurze Diskussion über den magischen Schrägstrich im Nginx Reverse Proxy

Apache Calcite-Code zur Dialektkonvertierung