Probleme mit Index und FROM_UNIXTIME in MySQL

Null, Hintergrund

Ich habe diesen Donnerstag viele Warnmeldungen erhalten. Ich habe den DBA gebeten, einen Blick darauf zu werfen, und habe eine langsame Abfrage gefunden.

Nachdem ich einfach einige Informationen gesammelt hatte, stellte ich fest, dass dieses Problem der langsamen Abfrage sehr tief verborgen war. Ich fragte viele Leute, einschließlich des DBA, aber niemand kannte den Grund.

1. Problem

Es gibt eine Datenbank mit einem wie folgt definierten Feld.

MySQL [d_union_stat]> desc t_local_cache_log_meta;
+----------------+--------------+------+-----+---------------------+
| Feld | Typ | Null | Schlüssel | Standard |
+----------------+--------------+------+-----+---------------------+
| c_id | int(11) | NEIN | PRI | NULL |
| c_key | varchar(128) | NEIN | MUL | |
| c_zeit | int(11) | NEIN | MUL | 0 |
| c_mtime | varchar(45) | NEIN | MUL | 0000-00-00 00:00:00 |
+----------------+--------------+------+-----+---------------------+
17 Zeilen im Satz (0,01 Sek.)

Der Index lautet wie folgt:

MySQL [d_union_stat]> zeige Index von t_local_cache_log_meta \G     
*************************** 1. Reihe ***************************
    Tabelle: t_local_cache_log_meta
  Nicht eindeutig: 0
   Schlüsselname: PRIMARY
 Spaltenname: c_id
  Sortierung: A
 Kardinalität: 6517096
  Indextyp: BTREE
*************************** 2. Reihe ***************************
.
.
.
*************************** 6. Reihe ***************************
    Tabelle: t_local_cache_log_meta
  Nicht_eindeutig: 1
   Schlüsselname: index_mtime
 Spaltenname: c_mtime
  Sortierung: A
 Kardinalität: 592463
  Indextyp: BTREE
6 Reihen im Satz (0,02 Sek.)

Dann habe ich ein SQL wie folgt geschrieben:

WÄHLEN 
  zählen(*)
AUS
  d_union_stat.t_local_cache_log_meta
Wo
  `c_mtime` < FROM_UNIXTIME(1494485402);

Schließlich kam eines Tages der DBA vorbei, warf mir eine Transaktion zu und sagte, dass dieses SQL ein langsames SQL sei.

# Zeit: 170518 11:31:14
# Abfragezeit: 12.312329 Sperrzeit: 0.000061 Gesendete Zeilen: 0 Untersuchte Zeilen: 5809647
SET-Zeitstempel=1495078274;
LÖSCHEN AUS `t_local_cache_log_meta`, WO `c_mtime`< FROM_UNIXTIME(1494473461) Limit 1000;

Ich war sprachlos. Meine Datenbank war indiziert und das SQL wurde sorgfältig optimiert. Warum war das SQL also langsam?

Auf die Frage, warum SQL langsam sei, konnte der DBA keine Antwort geben. Und auch als ich seine Kollegen fragte, konnten diese mir keine Antwort geben.

Ich dachte mir, dass ich auf ein sehr verborgenes Stück Wissen gestoßen sei.

Es gibt zwei verdächtige Punkte: 1. Es gibt 6 Indizes. 2. Der richtige Wert ist die Funktion FROM_UNIXTIME.

Also habe ich die offizielle MySQL-Dokumentation überprüft und festgestellt, dass 6 kein Problem darstellt.

Alle Speicher-Engines unterstützen mindestens 16 Indizes pro Tabelle und eine Gesamtindexlänge von mindestens 256 Byte.
Die meisten Speicher-Engines haben höhere Limits.

Daher vermutete ich, dass das Problem bei der Funktion FROM_UNIXTIME lag.

Schauen Sie sich dann den INDEX-Abschnitt von MYSQL an und finden Sie einige Hinweise.

1. Um schnell die Zeilen zu finden, die einer WHERE-Klausel entsprechen.
2. Um Zeilen aus der Betrachtung auszuschließen.
Wenn die Auswahl zwischen mehreren Indizes besteht, verwendet MySQL normalerweise den Index, der die geringste Zeilenanzahl findet.
3. Wenn die Tabelle einen mehrspaltigen Index hat, kann der Optimierer jedes ganz linke Präfix des Index zum Nachschlagen von Zeilen verwenden.
4. MySQL kann Indizes auf Spalten effizienter nutzen, wenn sie als derselbe Typ und dieselbe Größe deklariert sind.
Der Vergleich unähnlicher Spalten (z. B. der Vergleich einer Zeichenfolgenspalte mit einer zeitlichen oder numerischen Spalte) kann die Verwendung von Indizes verhindern, wenn Werte nicht direkt ohne Konvertierung verglichen werden können.

Als ich Artikel 4 sah, wurde erwähnt, dass unterschiedliche Typen dazu führen können, dass der Index nicht befolgt wird. Kann der Rückgabewert von FROM_UNIXTIME nicht in einen Zeichenfolgentyp konvertiert werden?

Fragen Sie anschließend den Rückgabewert der Funktion FROM_UNIXTIME ab.

MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.

Der zurückgegebene Wert ist ein Zeittyp. Wie wäre es, ihn in einen Zeichenfolgentyp zu zwingen?

MySQL [d_union_stat]> SELECT erklären 
  -> *
  -> VON
  -> t_local_cache_log_meta
  -> wobei
  -> `c_mtime` = CONCAT(FROM_UNIXTIME(1494485402)) \G
*************************** 1. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: t_local_cache_log_meta
     Typ: ref
mögliche Schlüssel: index_mtime
     Schlüssel: index_mtime
   Schlüssellänge: 137
     Verweis: const
     Reihen: 1
    Extra: Verwenden von „where“
1 Zeile im Satz (0,01 Sek.)

Dieses Mal können wir sehen, dass der Index verwendet wird und nur ein Datensatz gescannt wird.

2. Fazit

Dieses Mal können wir den Index verwenden, indem wir eine Konvertierung des Rückgabewerts von FROM_UNIXTIME erzwingen.

Daher kann dieses SQL den oberen Index nicht verwenden, da die Typen des rechten und des linken Werts inkonsistent sind. .

Okay, mehr verrate ich nicht. Dieser Artikel ist nur ein Zwischenspiel. Ich werde den Algorithmus später weiter vorstellen.