Eine Zusammenfassung der Gründe, warum MySQL keinen Datumsfeldindex verwendet

Hintergrund

In einer Tabelle wird das Feld „dataTime“ auf den Typ „varchar“ eingestellt, die gespeicherten Daten liegen im Datumsformat vor und es wird ein Index für das Feld eingestellt. Im Protokolldatensatz gibt es jedoch eine langsame Abfrage zu dieser Tabelle. Die Abfrageanweisung lautet:
select * from digitaltwin_meteorological where dataTime > '2021-10-15';
EXPLAIN analysiert die SQL-Anweisung und stellt fest, dass die SQL-Anweisung einen vollständigen Tabellenscan durchführt. Warum wird die Indexspalte dataTime in SQL verwendet und warum wird der vollständige Tabellenscan durchgeführt?

erkunden

1: Zuerst wurde angenommen, dass der Feldtyp „dataTime“ varchar sei, sodass MySQL den Index in Zeichenfolgenreihenfolge statt in Datumsgrößenreihenfolge sortierte. Bei der Durchführung von Bereichsabfragen konnte die Indexbereichspartitionierung daher nicht in Datumsreihenfolge durchgeführt werden. Also habe ich dataTime in den Typ datatime geändert und die Anweisung analysiert. Dabei habe ich festgestellt, dass es sich immer noch um einen vollständigen Tabellenscan handelte.

2: Ändern Sie den Wert der Abfragebedingung,

 Wählen Sie Anzahl(*) aus digitaltwin_meteorological, wobei Datenzeit > „2021-10-15“ ist;

Das Ausführungsergebnis ist 3910.

ERKLÄREN Sie, wählen Sie * aus digitaltwin_meteorological, wobei dataTime > '2021-10-15' ist;

Das Ergebnis der SQL-Anweisungsanalyse ist ein vollständiger Tabellenscan:

Wir ändern die Abfragebedingung auf 16, um zu sehen, wie viele Datensätze vorhanden sind:

 Wählen Sie Anzahl(*) aus digitaltwin_meteorological, wobei Datenzeit > „2021-10-16“ ist;

Das Abfrageergebnis ist 2525. Analysieren wir die Abfrageanweisung für Nummer 16:

ERKLÄREN Sie, wählen Sie * aus digitaltwin_meteorological, wobei dataTime > '2021-10-16' ist;

Das Ausführungsergebnis ist eine Bereichsabfrage, die den Index verwendet:

Daraus ist ersichtlich, dass MySQL bei einer großen Anzahl abgefragter Datensätze einen vollständigen Tabellenscan durchführt, da davon ausgegangen wird, dass ein vollständiger Tabellenscan effizienter ist. Wenn die Abfrage nur wenige Datensätze enthält, verwendet MySQL eine Indexabfrage.
Die Gesamtdatenmenge in der Tabelle beträgt 19714 Datensätze. Dies bedeutet, dass MySQL eine Indexabfrage verwendet, wenn 2525/19714 = 13 % ist. Wenn 3910/19714 = 20 % ist, führt MySQL einen vollständigen Tabellenscan durch.

3: Wir ändern dataTime in datetime. Müssen wir dann der Abfragebedingung Anführungszeichen hinzufügen? Wir entfernen die Anführungszeichen der dataTime-Abfragebedingung und sehen uns das Ergebnis an:

ERKLÄREN Sie, wählen Sie * aus digitaltwin_meteorological, wobei dataTime > 2021-10-16;

Es ist ersichtlich, dass es sich nach dem Entfernen der Anführungszeichen wieder um einen vollständigen Tabellenscan handelt. Daher muss der Wert der Abfragebedingung in Anführungszeichen gesetzt werden, unabhängig davon, ob der Feldtyp varchar oder datetime ist. Ohne Anführungszeichen führt MySQL einige Berechnungen mit diesem Wert durch. Tatsächlich ist 2021-10-16 ohne Anführungszeichen nicht mehr das Datum des 16. Schauen wir uns das folgende SQL an:

 Wählen Sie count(*) aus digitaltwin_meteorological, wobei dataTime > 2021-10-16 ist;

Das Berechnungsergebnis ist 19714, also die Daten der gesamten Tabelle. Daher muss auch die Datums-/Uhrzeitabfragebedingung in Anführungszeichen gesetzt werden.

4: Bei der obigen Analyse geht es ausschließlich um dataTime im Fall des datetime-Typs. Der ursprüngliche Feldtyp ist varchar. Wenn wir ihn also in den Typ varchar ändern, gilt die obige Schlussfolgerung dann immer noch? Wir ändern den Typ und führen SQL erneut aus:

ERKLÄREN Sie, wählen Sie * aus digitaltwin_meteorological, wobei dataTime > '2021-10-16' ist;

Es ist ersichtlich, dass Abfrage Nr. 16 nach der Änderung zum Typ varchar zu einem vollständigen Tabellenscan statt einem Bereichsscan wird.
Ändern Sie die Bedingung in Nummer 17 und sehen Sie sich das Ausführungsergebnis an:

ERKLÄREN Sie, wählen Sie * aus digitaltwin_meteorological, wobei dataTime > '2021-10-17' ist;

Die Abfrage am 17. verwendeten Index ist eine Abfrage. Wir sehen, dass das Datenvolumen für den 17. 1749 beträgt.
Wenn der Feldtyp varchar ist, wird daher in 1749/19714=9 % der Fälle der Index verwendet und in 2525/19714=13 % der Fälle wird die vollständige Tabelle gescannt.
Das heißt, wenn der Datentyp datetime ist und das Abfrageergebnis 13 % ausmacht, wird die Indexabfrage ausgeführt, und wenn der Datentyp varchar ist und das Abfrageergebnis 13 % der Daten in der gesamten Tabelle ausmacht, wird der gesamte Tabellenscan ausgeführt. Dies ist einer der Gründe, warum wir den Datumstyp auf datetime statt auf varchar festlegen.

Zusammenfassen

Aus der obigen Analyse können wir folgende Schlussfolgerungen ziehen:
1. Wenn bei Bereichsabfragen die Menge der abgefragten Daten einen bestimmten Bereich erreicht, geht MySQL davon aus, dass ein vollständiger Tabellenscan effizienter ist, und führt anstelle eines Index einen vollständigen Tabellenscan durch.
2. Der Wert des Datums-/Uhrzeitfeldtyps muss bei der Abfrage ebenfalls in Anführungszeichen gesetzt werden, da MySQL ihn sonst nicht nach Datum verarbeitet.
3. Bei Daten im Datumsformat, die auf den Typ „varchar“ eingestellt sind, ist der kritische Wert für die Bereichsabfrage zur Verwendung einer Indexabfrage oder eines vollständigen Tabellenscans niedriger als der kritische Wert für die Abfrage des Typs „datetime“ zur Verwendung einer Indexabfrage oder eines vollständigen Tabellenscans. Wenn also Daten des Datumstyps auf den Typ „datetime“ eingestellt sind, besteht eine höhere Wahrscheinlichkeit für die Verwendung einer Indexabfrage.

Dies ist das Ende dieses Artikels darüber, warum MySQL den Datumsfeldindex nicht verwendet. Weitere relevante Inhalte zum MySQL-Datumsfeldindex finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!