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

So implementieren Sie das Strategiemuster in Javascript

Inhaltsverzeichnis Überblick Code-Implementierung...

Detaillierte Erklärung mehrerer Möglichkeiten zum Entfernen der Lücke zwischen Inline-Blockelementen in CSS

Bei der Arbeit an mobilen Seiten werden in letzte...

So verwenden Sie Homebrew unter Linux richtig

Viele Leute verwenden Linux Homebrew. Hier sind d...

Wählen Sie die besten Voreinstellungen aus, um vollständige Kompatibilität mit allen Browsern zu erreichen.

Wir wissen, dass die Eigenschaften des Auswahltags...

Tutorial-Diagramm zur Installation von mysql8.0.18 unter Linux (Centos7)

1 Holen Sie sich das Installationsressourcenpaket...

IIS 7.5 verwendet das URL-Rewrite-Modul, um eine Webseitenumleitung zu erreichen

Wir alle wissen, dass Apache in der Konfiguration...

Detaillierte Erläuterung des CocosCreator-Nachrichtenverteilungsmechanismus

Überblick Dieser Artikel beginnt mit der Einführu...

MySQL5.7.03 Ersatz der höheren Version durch MySQL 5.7.17 Installationsprozess und Lösungen für gefundene Probleme

1. Wie installiert man? 1. [Ausführen] -> [cmd...

Analyse des Ausschnitts des Hintergrundbildes des Neunerrasters mit adaptiver Breite und Höhe

<br />Basierend auf dem ursprünglichen, in B...

Zusammenfassung der CSS-Methoden zum Löschen von Floats

Float wird häufig im Layout von Webseiten verwend...

Detailliertes Tutorial zur Installation von MySQL 5.7.26 auf CentOS7.4

In CentOS ist standardmäßig MariaDB installiert, ...

Grafisches Tutorial zur Installation und Konfiguration von mysql 5.7.17 winx64.zip

Vorwort: Ich habe Win10 neu installiert und gleic...

So verwenden Sie natives JS zum Implementieren von Touch-Sliding-Überwachungsereignissen

Vorwort Ich habe heute eine kleine Demo geschrieb...

Detaillierte Erklärung der Top-Befehlsausgabe in Linux

Vorwort Ich glaube, jeder hat den Befehl top unte...

Entwicklungsdetails von Vue3-Komponenten

Inhaltsverzeichnis 1. Einleitung 2. Komponentenen...