Problems with index and FROM_UNIXTIME in mysql

Zero, Background

I received a lot of alerts this Thursday. I asked the DBA to take a look and found a slow query.

After simply collecting some information, I found that this slow query problem was hidden very deeply. I asked many people, including the DBA, but no one knew the reason.

1. Problem

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

Then I wrote a SQL as follows:

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

Finally one day the DBA came over, threw me a transaction, and said that this SQL was a slow 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;

I was speechless. My DB was indexed and the SQL was carefully optimized, so why was the SQL slow?

When asked why the SQL was slow, the DBA couldn't answer, and even when I asked colleagues around him, they couldn't answer either.

I thought to myself that I had encountered a very hidden piece of knowledge.

There are two suspicious points: 1. There are 6 indexes. 2. The right value is the FROM_UNIXTIME function.

So I checked the official MYSQL documentation and found that 6 were not a 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 suspected that the problem was with the FROM_UNIXTIME function.

Then 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 I saw Article 4, it mentioned that different types may result in not following the index. Can't the return value of FROM_UNIXTIME be converted to a string type?

Then query the return value of the FROM_UNIXTIME function.

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

The returned value is a time type, how about forcing it into 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: 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)

This time we can see that the index is used and only one data is scanned.

2. Conclusion

This time, we can use the index by forcing a conversion on the return value of FROM_UNIXTIME.

Therefore, this SQL cannot use the upper index because the types of the right value and the left value are inconsistent. .

Okay, I won’t say much more. This article is just an interlude. I will continue to introduce the algorithm later.