Detaillierte Erläuterung der Index- und FROM_UNIXTIME-Probleme in MySQL

In diesem Artikel werden hauptsächlich relevante Informationen zu den Problemen von Index und FROM_UNIXTIME in MySQL vorgestellt. Freunde, die sie benötigen, können sich auf

Null, Hintergrund

Ich habe diesen Donnerstag viele Benachrichtigungen erhalten. Ich habe beim DBA nachgefragt und eine langsame

Abfrage gefunden.

Nachdem ich einfach ein paar Informationen gesammelt hatte, stellte ich fest, dass dieses langsame Abfrageproblem tief im Verborgenen liegt. Nachdem ich viele Leute, einschließlich des DBA, gefragt habe, weiß ich immer noch nicht, warum.

1. Problem

Es gibt eine Datenbank mit einem wie folgt definierten Feld

MySQL [d_union_stat]> desc t_local_cache_log_meta;
+----------------+--------------+------+-----+---------------------+
| Field     | Type     | Null | Key | Default       |
+----------------+--------------+------+-----+---------------------+
| c_id      | int(11)   | NO  | PRI | NULL        |
| c_key     | varchar(128) | NO  | MUL |           |
| c_time     | int(11)   | NO  | MUL | 0          |
| c_mtime    | varchar(45) | NO  | MUL | 0000-00-00 00:00:00 |
+----------------+--------------+------+-----+---------------------+
17 rows in set (0.01 sec)

Der Index lautet wie folgt:

MySQL [d_union_stat]> show index from t_local_cache_log_meta \G     
*************************** 1. row ***************************
    Table: t_local_cache_log_meta
  Non_unique: 0
   Key_name: PRIMARY
 Column_name: c_id
  Collation: A
 Cardinality: 6517096
  Index_type: BTREE
*************************** 2. row ***************************
.
.
.
*************************** 6. row ***************************
    Table: t_local_cache_log_meta
  Non_unique: 1
   Key_name: index_mtime
 Column_name: c_mtime
  Collation: A
 Cardinality: 592463
  Index_type: BTREE
6 rows in set (0.02 sec)

Dann habe ich ein SQL wie folgt geschrieben:

SELECT 
  count(*)
FROM
  d_union_stat.t_local_cache_log_meta
where
  `c_mtime` < FROM_UNIXTIME(1494485402);

Eines Tages kam schließlich der DBA vorbei und gab mir einen Überblick und sagte, dass dieses SQL langsames SQL sei.

# Time: 170518 11:31:14
# Query_time: 12.312329 Lock_time: 0.000061 Rows_sent: 0 Rows_examined: 5809647
SET timestamp=1495078274;
DELETE FROM `t_local_cache_log_meta` WHERE `c_mtime`< FROM_UNIXTIME(1494473461) limit 1000;

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

Auf die Frage, warum SQL langsam ist, konnte der DBA nicht antworten, und selbst die Kollegen in seinem Umfeld konnten keine Antwort geben.

Ich dachte insgeheim, ich sei auf einen tief verborgenen Wissenspunkt gestoßen.

Es gibt zwei verdächtige Aspekte: 1. Es gibt 6 Indizes. 2. Der R-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 Bytes. Die meisten Speicher-Engines haben höhere Grenzwerte.

Ich vermute also, dass das Problem an der FROM_UNIXTIME-Funktion liegt.

Dann schauen Sie sich den INDEX-Bereich 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 eine Auswahl zwischen mehreren Indizes besteht, MySQL verwendet normalerweise den Index, der die kleinste Anzahl von Zeilen findet. 3. Wenn die Tabelle einen mehrspaltigen Index hat, kann der Optimierer jedes
ganz linkePräfix des Index zum Nachschlagen verwenden Zeilen.4. MySQL kann Indizes für Spalten effizienter verwenden, wenn sie
als denselben Typ und dieselbe Größe deklariert werden Vergleich unterschiedlicher Spalten (Vergleich einer Zeichenfolge
). Spalte zu einer zeitlichen oder numerischen Spalte, zum Beispiel) kann vordie Verwendung von Indizes verhindern, wenn Werte nicht dirdirekt ohne Konvertierung verglichen werden können. Als ich Artikel 4 sah, wurde erwähnt, dass unterschiedliche Typen zu keiner Indizierung führen können. Könnte es sein, dass der Rückgabewert von FROM_UNIXTIME nicht in den Typ

string

konvertiert werden kann? Fragen Sie also den Rückgabewert der Funktion FROM_UNIXTIME

ab

.
MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/135.html" target="_blank">return</a>

s ein date

/datetime aus einer Version von unix_timestamp.MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/135.html" target="_blank">return</a>s a date /datetime from a version of unix_timestamp.

gibt eine Zeit zurück Typ, wie wäre es, ihn auf einen String-Typ zu zwingen?

MySQL [d_union_stat]> explain SELECT 
  ->   *
  -> FROM
  ->   t_local_cache_log_meta
  -> where
  ->   `c_mtime` = CONCAT(FROM_UNIXTIME(1494485402)) \G
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: t_local_cache_log_meta
     type: ref
possible_keys: index_mtime
     key: index_mtime
   key_len: 137
     ref: const
     rows: 1
    Extra: Using where
1 row in set (0.01 sec)

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

2. Fazit

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

Daher kann diese SQL den oberen Index nicht verwenden, da die Typen von R-Werten und L-Werten inkonsistent sind. .

Okay, mehr muss ich nicht sagen, dieser Artikel ist nur ein Zwischenspiel, wir stellen den Algorithmus später weiter vor.

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der Index- und FROM_UNIXTIME-Probleme in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!