mysql中索引与FROM_UNIXTIME的问题

站长资源 2024/11/24 佚名

2 0 1

零、背景

这周四收到很多告警，找DBA看了看，发现有个慢查询。

简单收集一些信息后，发现这个慢查询问题隐藏的很深，问了好多人包括DBA都不知道原因。

一、问题

有一个DB, 有一个字段, 定义如下.

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 [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)

然后我写了一个SQL如下:

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

终于有一天DBA过来了, 扔给我一个流水，说这个SQL是慢SQL。

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

我顿时无语了，我的DB都是加了索引，SQL都是精心优化了的，怎么是慢SQL呢？

问为什么是慢SQL，DBA答不上来，问了周围的同事也都答不上来。

我心里暗想遇到一个隐藏很深的知识点了。

令人怀疑的地方有两个：1.有6个索引。 2. 右值是 FROM_UNIXTIME 函数。

于是查询MYSQL官方文档，发现6个不是问题。

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.

于是怀疑问题是 FROM_UNIXTIME 函数了。

然后看看MYSQL的INDEX小节，找到一点蛛丝马迹。

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.

看到第4条的时候，提到不同类型可能导致不走索引，难道 FROM_UNIXTIME 的返回值不能转化为字符串类型？

于是查询 FROM_UNIXTIME 函数的返回值。

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

返回的是一个时间类型，那强制转化为字符串类型呢？

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)

这次可以看到, 使用了索引，只扫描了一个数据。

二、结论

这次对 FROM_UNIXTIME 的返回值强制转化一下就可以利用上索引了。

所以这个SQL不能利用上索引是右值与左值的类型不一致导致的。。

好了，不多说了，这篇文章算是一个插曲，后面继续介绍算法吧。

mysql,from,unixtime,mysql,fromunixtime,mysqlfrom,unixtime

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

评论“mysql中索引与FROM_UNIXTIME的问题”

暂无评论...

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒经典版》的玩家都可以获得奖励。

更新日志

2024年11月24日

mysql中索引与FROM_UNIXTIME的问题

MySQL基础教程第一篇 mysql5.7.18安装和连接教程

浅谈MySQL event 计划任务

评论“mysql中索引与FROM_UNIXTIME的问题”

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

更新日志

友情链接