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 de index et FROM_UNIXTIME dans mysql Les amis qui en ont besoin peuvent se référer à

<.>

Zéro, arrière-plan

J'ai reçu beaucoup d'alertes ce jeudi, j'ai vérifié auprès du DBA et j'ai trouvé une

requête lente.

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, dont le DBA, je n'en connais toujours 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)

Ensuite, 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 aperçu, disant que ce SQL était du 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. Même les collègues autour de moi n'ont pas pu y 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 FROM_UNIXTIME

fonction .

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 vient de 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

Si il y a un 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
à gauchede l'index peut être utilisé par l'optimiseur pour rechercher. rows.4. MySQL peut utiliser les index sur les colonnes plus efficacement s'ils sont
déclarésde même type et de même taille Comparaison de colonnes différentes (en comparant une
chaîne. colonne à une colonne temporelle ou numérique, par exemple) peut empêcherl'utilisation des index si les valeurs ne peuvent pas être comparées directementectement sans conversion.

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

string ?

Alors interrogez la valeur de retour de la fonction FROM_UNIXTIME

.

MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/135.html" target="_blank">return<p>est un <a href="http://www.php.cn/wiki/1255.html" target="_blank">date<code>MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/135.html" target="_blank">return</a>s a <a href="http://www.php.cn/wiki/1255.html" target="_blank">date</a> /datetime from a version of unix_timestamp. /datetime à partir d'une version d'unix_timestamp.

renvoie une heure tapez, que diriez-vous de le forcer à un type 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)

Cette fois, vous pouvez voir 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. .

D'accord, rien de plus à dire, cet article n'est qu'un intermède, continuons à présenter l'algorithme plus tard.

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!