《MYSQL教程mysql中索引與FROM_UNIXTIME的問題》要點：
本文介紹了MYSQL教程mysql中索引與FROM_UNIXTIME的問題，希望對您有用。如果有疑問，可以聯系我們。
MYSQL入門零、配景
MYSQL入門這周四收到許多告警,找DBA看了看,發現有個慢查詢.
MYSQL入門簡單收集一些信息后,發現這個慢查詢問題暗藏的很深,問了好多人包括DBA都不知道原因.
MYSQL入門一、問題
MYSQL入門有一個DB, 有一個字段, 界說如下.
MYSQL入門
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入門索引如下:
MYSQL入門
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)
MYSQL入門然后我寫了一個SQL如下:
MYSQL入門
SELECT
count(*)
FROM
d_union_stat.t_local_cache_log_meta
where
`c_mtime` < FROM_UNIXTIME(1494485402);
MYSQL入門終于有一天DBA過來了, 扔給我一個流水,說這個SQL是慢SQL.
MYSQL入門
# 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;
MYSQL入門我馬上無語了,我的DB都是加了索引,SQL都是精心優化了的,怎么是慢SQL呢?
MYSQL入門問為什么是慢SQL,DBA答不上來, 問了四周的同事也都答不上來.
MYSQL入門我心里暗想遇到一個暗藏很深的知識點了.
MYSQL入門令人懷疑的處所有兩個：1.有6個索引. 2. 右值是 FROM_UNIXTIME 函數.
MYSQL入門于是查詢MYSQL官方文檔,發現6個不是問題.
MYSQL入門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.
MYSQL入門于是狐疑問題是 FROM_UNIXTIME 函數了.
MYSQL入門然后看看MYSQL的INDEX末節,找到一點蛛絲馬跡.
MYSQL入門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.
MYSQL入門看到第4條的時候,提到不同類型可能導致不走索引,難道 FROM_UNIXTIME 的返回值不克不及轉化為字符串類型?
MYSQL入門于是查詢 FROM_UNIXTIME 函數的返回值.
MYSQL入門MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.
MYSQL入門返回的是一個光陰類型,那強制轉化為字符串類型呢?
MYSQL入門
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)
MYSQL入門這次可以看到, 使用了索引,只掃描了一個數據.
MYSQL入門二、結論
MYSQL入門這次對 FROM_UNIXTIME 的返回值強制轉化一下就可以應用上索引了.
MYSQL入門所以這個SQL不克不及利用上索引是右值與左值的類型不一致導致的. .
MYSQL入門好了,不多說了, 這篇文章算是一個插曲,后面繼續先容算法吧.
維易PHP培訓學院每天發布《MYSQL教程mysql中索引與FROM_UNIXTIME的問題》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/9857.html
