MYSQL教程mysql中索引与FROM_UNIXTIME的问题

《MYSQL教程mysql中索引与FROM_UNIXTIME的问题》要点：
本文介绍了MYSQL教程mysql中索引与FROM_UNIXTIME的问题，希望对您有用。如果有疑问，可以联系我们。

MYSQL入门零、配景

MYSQL入门这周四收到许多告警,找DBA看了看,发现有个慢查询.

MYSQL入门简单收集一些信息后,发现这个慢查询问题暗藏的很深,问了好多人包括DBA都不知道原因.

MYSQL入门一、问题

MYSQL入门有一个DB,有一个字段,界说如下.

MYSQL入门
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)

MYSQL入门索引如下:

MYSQL入门
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)

MYSQL入门然后我写了一个SQL如下:

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

MYSQL入门终于有一天DBA过来了,扔给我一个流水,说这个SQL是慢SQL.

MYSQL入门
# 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;

MYSQL入门我马上无语了,我的DB都是加了索引,SQL都是精心优化了的,怎么是慢SQL呢?

MYSQL入门问为什么是慢SQL,DBA答不上来,问了四周的同事也都答不上来.

MYSQL入门我心里暗想遇到一个暗藏很深的知识点了.

MYSQL入门令人怀疑的处所有两个：1.有6个索引. 2. 右值是 FROM_UNIXTIME 函数.

MYSQL入门于是查询MYSQL官方文档,发现6个不是问题.

MYSQL入门All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes.??
Most storage engines have higher limits.

MYSQL入门于是狐疑问题是 FROM_UNIXTIME 函数了.

MYSQL入门然后看看MYSQL的INDEX末节,找到一点蛛丝马迹.

MYSQL入门1.To find the rows matching a WHERE clause quickly.
2. To eliminate rows from consideration.
?If there is a choice between multiple indexes,MySQL normally uses the index that finds the smallest number of rows.
3.If the table has a multiple-column index,any leftmost prefix of the index can be used by the optimizer to look up rows.
4. MySQL can use indexes on columns more efficiently if they are declared as the same type and size.
?Comparison of dissimilar columns (comparing a string column to a temporal or numeric column,for example) may prevent use of indexes if values cannot be compared directly without conversion.

MYSQL入门看到第4条的时候,提到不同类型可能导致不走索引,难道 FROM_UNIXTIME 的返回值不克不及转化为字符串类型?

MYSQL入门于是查询 FROM_UNIXTIME 函数的返回值.

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

MYSQL入门返回的是一个光阴类型,那强制转化为字符串类型呢?

MYSQL入门
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)

MYSQL入门这次可以看到,使用了索引,只扫描了一个数据.

MYSQL入门二、结论

MYSQL入门这次对 FROM_UNIXTIME 的返回值强制转化一下就可以应用上索引了.

MYSQL入门所以这个SQL不克不及利用上索引是右值与左值的类型不一致导致的. .

MYSQL入门好了,不多说了,这篇文章算是一个插曲,后面继续先容算法吧.

编程之家PHP培训学院每天发布《MYSQL教程mysql中索引与FROM_UNIXTIME的问题》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!