Shulou(Shulou.com)05/31 Report--

Editor to share with you an example of indexing and FROM_UNIXTIME in mysql, I believe most people don't know much about it, so share this article for your reference. I hope you can learn a lot after reading this article. Let's learn about it together.

Zero, background

After simply collecting some information, I found that the slow query problem was hidden deeply, and many people, including DBA, did not know why.

First, problems

There is a DB and a field, defined as follows.

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 | NO | MUL | | c_time | int (11) | NO | MUL | 0 | c_mtime | varchar (45) | NO | MUL | 0000-00-0000: 00:00 | | +-+ 17 rows in set (0.01 sec) |

The index is as follows:

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: BTREE6 rows in set (0.02 sec)

Then I wrote a SQL as follows:

SELECT count (*) FROM d_union_stat.t_local_cache_log_metawhere `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: 5809647SET 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-> `cmtime` = CONCAT (FROM_UNIXTIME (1494485402))\ row * * id: 1 select_type: SIMPLE table: t _ local_cache_log_meta type: refpossible_keys: index_mtime key: index_mtime key_len: 137ref: const rows: 1 Extra: Using where1 row in set (0.01sec)

As you can see this time, the index is used and only one data is scanned.

II. Conclusion

This time, you can take advantage of the index by forcing the conversion of the return value of FROM_UNIXTIME.

So the fact that the SQL cannot take advantage of the upper index is caused by the inconsistency between the right value and the left value. .

The above is all the content of the article "examples of indexing and FROM_UNIXTIME problems in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.