Explication détaillée des problèmes d'index et FROM_UNIXTIME dans MySQL

Cet article présente principalement les informations pertinentes sur les problèmes d'index et FROM_UNIXTIME dans MySQL. Les amis qui en ont besoin peuvent s'y référer. J'espère qu'il pourra aider tout le monde.

Zéro et arrière-plan

Après avoir simplement collecté quelques informations, j'ai découvert que ce problème de requête lente est profondément caché. Après avoir interrogé de nombreuses personnes, y compris le DBA, je ne le fais toujours pas. je ne connais pas la raison.

1. Problème

Il existe une base de données avec un champ défini comme suit.

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)

L'index est le suivant :

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)

Puis j'ai écrit un SQL comme suit :

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

Finalement, un jour, le DBA est venu et m'a donné un rapport disant que ce SQL était un SQL lent.

# 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;

J'étais sans voix. Ma base de données est indexée et SQL est soigneusement optimisé. Pourquoi SQL est-il lent ?

Lorsqu'on lui a demandé pourquoi SQL est lent, le DBA n'a pas pu répondre, et même ses collègues autour de lui n'ont pas pu répondre.

Je pensais secrètement avoir rencontré un point de connaissance profondément caché.

Il y a deux aspects suspects : 1. Il y a 6 index. 2. La rvalue est la fonction FROM_UNIXTIME.

J'ai donc vérifié la documentation officielle MYSQL et j'ai trouvé que 6 n'étaient pas des problèmes.

Tous les moteurs de stockage prennent en charge au moins 16 index par table et une longueur totale d'index d'au moins 256 octets
La plupart des moteurs de stockage ont des limites plus élevées.

Je soupçonne donc que. le problème est la fonction FROM_UNIXTIME.

Ensuite, regardez la section INDEX de MYSQL et trouvez quelques indices.

1.Pour trouver rapidement les lignes correspondant à une clause WHERE.
2. Pour éliminer les lignes de la considération
S'il y a le choix entre plusieurs index, MySQL utilise normalement l'index qui. trouve le plus petit nombre de lignes.
3 Si la table a un index à plusieurs colonnes, n'importe quel préfixe le plus à gauche de l'index peut être utilisé par l'optimiseur pour rechercher des lignes
4. plus efficacement si elles sont déclarées avec le même type et la même taille.
La comparaison de colonnes différentes (en comparant une colonne de chaîne à une colonne temporelle ou numérique, par exemple) peut empêcher l'utilisation d'index si les valeurs ne peuvent pas être comparées directement sans conversion. .

Quand j'ai vu l'article 4, il a été mentionné que différents types pouvaient conduire à aucune indexation. Se pourrait-il que la valeur de retour de FROM_UNIXTIME ne puisse pas être convertie en type chaîne ?

Requêtez donc la valeur de retour de la fonction FROM_UNIXTIME.

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

renvoie un type d'heure, alors pourquoi ne pas le forcer à un type de chaîne ?

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)

Vous pouvez voir cette fois que l'index est utilisé et qu'une seule donnée est analysée.

2. Conclusion

Cette fois, vous pouvez utiliser l'index en forçant la conversion de la valeur de retour de FROM_UNIXTIME.

Ce SQL ne peut donc pas utiliser l'index supérieur car les types de rvalues ​​​​et de lvalues ​​​​sont incohérents. .

Recommandations associées :

Comment créer un index pour une table de jointure liée à deux tables dans MySQL Explication graphique et textuelle détaillée

Champ de partition MySQL Est-il nécessaire de créer un index séparé pour la colonne ?

Introduction à la méthode de visualisation, de création et de suppression d'index dans MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!