mysql中索引與FROM_UNIXTIME的問題詳解

本文主要介紹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(*)
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, My normally thatusesSQL 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 canuse indexes 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) mayvent s.

##看到第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中兩個表關聯的連接表如何建立索引圖文詳解

MySQL分割區字段列有必要再單獨建立索引嗎？

MySQL實作檢視與建立以及刪除索引的方法介紹#

以上是mysql中索引與FROM_UNIXTIME的問題詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！