索引和FROM_UNIXTIME在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 [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)

SELECT     count(*)  FROM    d_union_stat.t_local_cache_log_meta  where    `c_mtime` <p>终于有一天DBA过来了, 扔给我一个流水，说这个SQL是慢SQL。</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 <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>。</p><p>于是查询MYSQL官方文档，发现6个不是问题。<br></p><p><a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a> 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> <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a> 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 <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>most 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 <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>d as the same type and size. <br> Comparison of dissimilar columns (comparing a <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a> column to a temporal or numeric column, <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a> example) may <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>ent use of indexes if values cannot be compared <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>ectly without conversion.</p><p>看到第4条的时候，提到不同类型可能导致不走索引，难道 FROM_UNIXTIME 的返回值不能转化为<a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>类型？</p><p>于是查询 FROM_UNIXTIME <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>。<br></p><p>MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a>s a <a href="http://www.php.cn/wiki/1160.html" target="_blank">mysql</a> /datetime from a version of unix_timestamp.</p><p>返回的是一个时间类型，那强制转化为字符串类型呢？</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)