MySQL-Lösung zur funktionalen Indexoptimierung

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

Artikel empfehlen

Erfahren Sie in einem Artikel, wie Sie mehrere Tomcats bereitstellen und starten und Projekte migrieren

Inhaltsverzeichnis Tomcat bereitstellen 1. Herunt...

uniapp Beispielcode zur Implementierung der globalen Freigabe von WeChat-Miniprogrammen

Inhaltsverzeichnis Erstellen einer global freigeg...

Implementierung der Änderung von Konfigurationsdateien im Docker-Container

1. Betreten Sie den Container docker run [Option]...

Lösung für Klickereignisfehler bei Verwendung von Better-Scroll bei der Vue-Mobilentwicklung

Vor Kurzem habe ich Vue verwendet, um das Entwick...

Lösung für die Meldung „Die exklusive Sperre dieser Konfigurationsdatei ist fehlgeschlagen“ auf der virtuellen VMware-Maschine

Nach einem abnormalen Herunterfahren von VMware w...

Reacts Methode zur Realisierung einer sekundären Verknüpfung

In diesem Artikel wird der spezifische Code von R...

Codeanalyse von Benutzervariablen in MySQL-Abfrageanweisungen

Im vorherigen Artikel haben wir die MySQL-Optimie...

Tutorial zur Installation von PHP5, Deinstallation von PHP und Installation von PHP7 auf CentOS

Installieren Sie zunächst PHP5 ganz einfach yum i...

Website-Leistung: Bild- und Cookie-Optimierung und Optimierung mobiler Anwendungen

In den vorherigen Abschnitten haben wir Aspekte zu...

Installationstutorial für Docker unter Linux

Das Docker-Paket ist bereits im Standard-Reposito...

jQuery implementiert Formularvalidierungsfunktion

Beispiel für die Validierung eines jQuery-Formula...

Lösung zum Anwenden von CSS3-Transformationen auf Hintergrundbilder

CSS-Transformationen sind zwar cool, wurden aber ...

Implementierung der Docker-Container-Verbindung und -Kommunikation

Die Portzuordnung ist nicht die einzige Möglichke...

Eine kurze Diskussion über die Platzierung von Skripten in HTML

Früher dachte ich, dass Skripte überall in HTML p...

Beispiel für die Implementierung der Hochverfügbarkeit von Keepalived+Nginx

1. Einführung in Keepalived Keepalived wurde ursp...