国产精品9999久久久久,99久久国产综合精品成人影院,久久久久黑人强伦姧人妻

本文主要介紹mysql中索引與from_unixtime的問題的相關資料,需要的朋友可以參考下，希望能幫助到大家。

零、背景

簡單收集一些信息后，發現這個慢查詢問題隱藏的很深，問了好多人包括dba都不知道原因。

一、問題

有一個DB, 有一個字段, 定義如下.

mysql?[d_union_stat]&gt;?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]&gt;?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`?<p>終于有一天DBA過來了, 扔給我一個流水，說這個SQL是慢SQL。<br></p><p class="jb51code"><br></p><pre class="brush: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`<p>我頓時無語了，我的DB都是加了索引，SQL都是精心優化了的，怎么是慢SQL呢？</p><p>問為什么是慢SQL，DBA答不上來， 問了周圍的同事也都答不上來。</p><p>我心里暗想遇到一個隱藏很深的知識點了。</p><p>令人懷疑的地方有兩個：1.有6個索引。 2. 右值是 FROM_UNIXTIME 函數。</p><p>于是查詢MYSQL官方文檔，發現6個不是問題。<br></p><p>All storage engines support at least 16 indexes per table and a total index Length of at least 256 bytes.?? <br>Most storage engines have higher limits.</p><p>于是懷疑問題是 FROM_UNIXTIME 函數了。</p><p>然后看看MYSQL的INDEX小節，找到一點蛛絲馬跡。<br></p><p>1.To find the rows matching a WHERE clause quickly.<br>2. To eliminate rows from consideration. <br>?if there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows. <br>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.<br>4. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. <br>?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.</p><p>看到第4條的時候，提到不同類型可能導致不走索引，難道 FROM_UNIXTIME 的返回值不能轉化為字符串類型？</p><p>于是查詢 FROM_UNIXTIME 函數的返回值。<br></p><p>MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.</p><p>返回的是一個時間類型，那強制轉化為字符串類型呢？<br></p><p class="jb51code"><br></p><pre class="brush:sql;">MySQL?[d_union_stat]&gt;?explain?SELECT?  ??-&gt;???*  ??-&gt;?FROM  ??-&gt;???t_local_cache_log_meta  ??-&gt;?where  ??-&gt;???`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)