mysql timestamp类型设置索引无效

幸福糖

大家好，最近遇到一个问题，表结构如

1. CREATE TABLE `testone` (   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   
2.            `sorder` VARCHAR(50) NOT NULL,
   
3.            `trantime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
   
4.            `trantype` VARCHAR(120) NOT NULL,
   
5.            `income` DECIMAL(10,2) NOT NULL,
   
6.            `abstract` VARCHAR(255) NOT NULL,
   
7.            `remarks` VARCHAR(255) NOT NULL,
   
8.            `status` TINYINT(2) NOT NULL,
   
9.            `created` INT(11) NOT NULL,
   
10.            PRIMARY KEY (`id`),
    
11.            KEY `trantime` (`trantime`)
    
12. ) ENGINE=MYISAM DEFAULT CHARSET=utf8;

复制代码

然后线上数据有200W左右,SELECT COUNT(DISTINCT(trantime))/COUNT(*) FROM testone;的到的结果约为0.4；

我做了一个根据交易时间(trantime)的搜索功能[trantime索引是发现这个功能查询很慢的时候加上去的];但是加这个索引只用了10多秒，就好了，结果查询根本都用不上索引。然后我导出了5W条数据在我本地的windows环境和linux环境下，在windows环境下查询不用强制使用索引就用到了索引的，但是在linux下查询就没有用到索引了，强制也不行查询如下：
DESC SELECT * FROM testone FORCE INDEX(trantime) WHERE trantime = '2013-01-01 11:00:00';
结果是 [竟然不能上传图片]
id      select_type    table          type             possible_keys      key         key_len         ref             rows           Extra
1        SIMPLE       testone         ALL               (NULL)            (NULL)         (NULL)       (NULL)       50000      Useing where

不知道为啥 在linux下 就没有用到索引，线上的也是linux环境。我把这个timestamp类型切换为datetime依旧用不了索引,然后删除了索引再重建也是不行的。
后来在一篇博客里面看到了一个函数str_to_date();我尝试这个函数结果可以利用到索引了如下：
DESC SELECT * FROM testone WHERE trantime = STR_TO_DATE('2014-07-31 23:58:14','%Y-%m-%d %T');
结果是
id      select_type    table          type             possible_keys      key              key_len         ref             rows           Extra
1        SIMPLE       testone         ref               trantime            trantime         4               const              5              Useing where

原以为这样就可以了，但是直接查询
SELECT * FROM testone WHERE trantime = STR_TO_DATE('2014-07-31 23:58:14','%Y-%m-%d %T');
得到的结果却是空,用语句
SELECT * FROM testone WHERE trantime = '2014-07-31 23:58:14';
又是有5条语句的[但是这个用不到索引]。哎问题依旧没有解决。

经过几个版本的测试,貌似和环境没有关系,配置文件各个参数比较分析，也没有多大关系，那么推断应该只能是版本的问题了。现在我
本地的windowsl集成环境mysql版本是                   :5.0.51b-community-nt-log，   可以用到索引；
本地LNMP环境mysql版本                                      :5.5.3-m3-community-log，      不可用到索引；
本地LAMP环境mysql版本                                      :5.1.51-community-log,             可以用到索引 ;
线上LNMP环境mysql版本                                      :5.5.3-m3-log                             不可用到索引;
本地windows单独mysql版本(故意下的这个)          :5.5.3-m3-community,               不可用到索引;
到这里可以勉强推断和mysql版本有关系,这样的问题除了修改类型或者更改版本,就利用当前的版本能通过什么方法解决这个问题么，然后导致这个问题的根源大家能解答下么？

希望大家指点下。谢谢各位了！

kider

我这边也试了下，当前版本5.1.31-enterprise-gpl-pro-log，能用的到。
这是因为不同版本，SQL优化器会有调整，MySQL自身会智能选择是否用索引，这个也与数据量的不同也会不同，有曾经也遇到过这样的情况，你最好还是采用正式版本。

幸福糖

kider 发表于 2015-2-4 19:08
我这边也试了下，当前版本5.1.31-enterprise-gpl-pro-log，能用的到。
这是因为不同版本，SQL优化器会有调 ...

嗯，后来我们也决定了升级mysql的版本。谢谢你额。{:soso_e100:}