Mysql index and FROM_UNIXTIME, mysqlfrom_unixtime

Mysql index and FROM_UNIXTIME, mysqlfrom_unixtime

Zero, Background

On Thursday, I received a lot of alarms. I looked at the DBA and found a slow query.

After collecting some information, I found that the slow query problem was hidden. Many people, including DBAs, did not know the cause.

I. Problems

There is a DB with 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(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)

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 an SQL statement as follows:

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

One day, the DBA finally came over and threw me a piece of water, saying that this SQL is a slow 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;

I was speechless, my DB was indexed, and SQL was carefully optimized. Why is it slow SQL?

Ask why it is slow SQL. the DBA cannot answer the question and cannot answer questions from colleagues around him.

I want to have a deep hidden knowledge point.

Two items are suspected: 1. Six indexes. 2. the right value is the FROM_UNIXTIME function.

Query the MYSQL official documentation and find that the six are not the problem.

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.

So I suspect the problem is the FROM_UNIXTIME function.

Then let's look at the INDEX section of MYSQL and find some clues.

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.

When we see 4th entries, different types may lead to no indexing. Can the returned value of FROM_UNIXTIME not be converted to the string type?

The returned value of the FROM_UNIXTIME function is queried.

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

If a time type is returned, is it forcibly converted to a string type?

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: refpossible_keys: index_mtime     key: index_mtime   key_len: 137     ref: const     rows: 1    Extra: Using where1 row in set (0.01 sec)

This time, we can see that only one data is scanned using an index.

Ii. Conclusion

This time, the returned value of FROM_UNIXTIME is forcibly converted to use the index.

Therefore, this SQL statement cannot be caused by inconsistent types of the right and left values of the index. .

Well, I will not talk about it much. This article is an episode. Let's continue to introduce algorithms later.