mysql中索引与FROM

92次阅读

没有评论

共计 3168 个字符，预计需要花费 8 分钟才能阅读完成。

自动写代码机器人，免费开通

丸趣 TV 小编给大家分享一下 mysql 中索引与 FROM_UNIXTIME 的问题示例，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

零、背景

简单收集一些信息后，发现这个慢查询问题隐藏的很深，问了好多人包括 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(*)
 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 的问题示例”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

向 AI 问一下细节

正文完