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

Beispiel-Tutorial für MySQL-Datenbanktransaktionen

MySQL-Lösung zur funktionalen Indexoptimierung

Beispiel-Tutorial für MySQL-Datenbanktransaktionen

Vuex implementiert einfache Warenkorbfunktion

Detaillierter Prozess zur Implementierung des 2048-Minispiels im WeChat-Applet

Detaillierte Erklärung der gemischten Vererbung in Vue

Eine kurze Analyse der Probleme mit JS-Originalwerten und Referenzwerten

XHTML-Tags haben ein schließendes Tag

Implementierungsskript für geplante Datenbanksicherung unter Linux

Erweiterte benutzerdefinierte JavaScript-Ausnahme

Beispielcode zur Implementierung eines Foto-Stacking-Effekts mit CSS

Linux weist eine Sicherheitslücke bei der Rechteausweitung bei Sudo auf, jeder Benutzer kann auch Root-Befehle ausführen

Artikel empfehlen

Detaillierte Erklärung der Truncate-Verwendung in MySQL

So ändern Sie den vorherigen Befehl, wenn in der MySQL-Eingabeaufforderung ein Eingabefehler auftritt

Detailliertes Tutorial zur Installation von MySQL unter Linux

Zusammenfassung der Grundlagen der Vue-Komponenten

MySql-Lerntag 03: Verbindungs- und Abfragedetails zwischen Datentabellen

Die Hintergrundfarbe oder das Bild im Div-Container wächst mit dem Wachstum

Detaillierte Erläuterung der Verwendung und der zugrunde liegenden Prinzipien von MySQL-Tabellenpartitionen

Anwendungsbeispiele für den Mysql Inner Join (unbedingt lesen)

Einige Hinweise zum Ändern des innodb_data_file_path-Parameters von MySQL

Beim Upgrade von MySQL auf 5.7 meldet WordPress beim Importieren von Daten den Fehler 1067

Implementierung der MySQL-Mehrversions-Parallelitätskontrolle MVCC

Detaillierte Erklärung zur Verwendung von Teleport in Vue3

Drei Möglichkeiten zum Implementieren eines Textfarbverlaufs in CSS

Verschaffen Sie sich ein umfassendes Verständnis des Prototypobjekts in JavaScript

JavaScript zur einfachen Verknüpfung von Provinzen und Gemeinden