mysql explain时，possible_keys有索引，但是key为null没有使用索引

场景：　有三个表，表２连接表３查询很快，几毫秒；但是表２连接查询表４很慢，耗时几分钟。　注：都是用uid字段做关联

查询结果如下截图：

mysql> select concat_ws( '|' , '2016-08-23' , t3.country ,  count(distinct t2.id ) ) 
    -> from t_reconnect_task t2 join incent.t_user_info t3  
    -> on ( t2.uid = t3.uid   and date_format(t2.create_time,'%Y-%m-%d')= '2016-08-23' ) 
    -> group by t3.country  ;
+------------------------------------------------------------------------+
| concat_ws( '|' , '2016-08-23' , t3.country ,  count(distinct t2.id ) ) |
+------------------------------------------------------------------------+
| 2016-08-23|AR|6                                                        |
| 2016-08-23|AT|5                                                        |
| 2016-08-23|AU|2                                                        |
| 2016-08-23|BD|6                                                        |
| 2016-08-23|BG|3                                                        |
| 2016-08-23|BR|36                                                       |
| 2016-08-23|NI|2                                                        |
| 2016-08-23|NL|7                                                        |
| 2016-08-23|NZ|3                                                        |
| 2016-08-23|US|1253                                                     |
| 2016-08-23|UY|4                                                        |
| 2016-08-23|VE|6                                                        |
| 2016-08-23|ZA|3                                                        |
+------------------------------------------------------------------------+
60 rows in set (0.09 sec)

mysql> select concat_ws( '|' , '2016-08-23' ,   t4.ab_test , count(distinct t2.id ) ) 
    -> from t_reconnect_task t2  join test_uid_abtest_ref t4 
    -> on ( t2.uid =  t4.uid ) 
    -> where date_format(t2.create_time,'%Y-%m-%d')= '2016-08-23' 
    -> group by   t4.ab_test ;

+-------------------------------------------------------------------------+
| concat_ws( '|' , '2016-08-23' ,   t4.ab_test , count(distinct t2.id ) ) |
+-------------------------------------------------------------------------+
| 2016-08-23|-1|446                                                       |
| 2016-08-23|0|1030                                                       |
| 2016-08-23|1|939                                                        |
+-------------------------------------------------------------------------+
3 rows in set (3 min 34.17 sec)

ｅｘｐｌａｉｎ如下截图：

mysql> 
mysql> explain
    -> select concat_ws( '|' , '2016-08-23' ,   t4.ab_test , count(distinct t2.id ) ) 
    -> from t_reconnect_task t2  join test_uid_abtest_ref t4 
    -> on ( t2.uid =  t4.uid and date_format(t2.create_time,'%Y-%m-%d')= '2016-08-23' ) 
    -> group by   t4.ab_test ;
+----+-------------+-------+------+-------------------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys           | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+-------+------+-------------------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | t4    | ALL  | test_uid_abtest_ref_ix1 | NULL | NULL    | NULL | 16415 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ALL  | NULL                    | NULL | NULL    | NULL | 25298 | Using where; Using join buffer  |
+----+-------------+-------+------+-------------------------+------+---------+------+-------+---------------------------------+
2 rows in set (0.00 sec)

mysql> explain
    -> select concat_ws( '|' , '2016-08-23' , t3.country ,  count(distinct t2.id ) ) 
    -> from t_reconnect_task t2 join incent.t_user_info t3  
    -> on ( t2.uid = t3.uid   and date_format(t2.create_time,'%Y-%m-%d')= '2016-08-23' ) 
    -> group by t3.country  ;
+----+-------------+-------+--------+---------------+------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL | NULL    | NULL            | 25298 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t3    | eq_ref | UID           | UID  | 194     | giftcard.t2.uid |     1 | Using where                                  |
+----+-------------+-------+--------+---------------+------+---------+-----------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

分析：　其实possible_keys已经查询到了唯一键uid，但是key没有使用。对比表3和表4的区别：

CREATE TABLE `t_user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '设备id',
  `country` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '国家',
  `time_zone` int(2) NOT NULL COMMENT '时区',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UID` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=17134 DEFAULT CHARSET=utf8 COLLATE=utf8_bin      

CREATE TABLE `test_uid_abtest_ref` (
  `uid` varchar(64) NOT NULL COMMENT '用户id',
  `ab_test` int(4) NOT NULL DEFAULT '0' COMMENT 'ab_test类型',
  UNIQUE KEY `test_uid_abtest_ref_ix1` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户abtest类型临时表' 

解决方法： 

 alter table test_uid_abtest_ref change uid  `uid` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '用户UID';

验证：

mysql> select concat_ws( '|' , '2016-08-23' ,   t4.ab_test , count(distinct t2.id ) ) 
    -> from t_reconnect_task t2  join test_uid_abtest_ref t4 
    -> on ( t2.uid =  t4.uid and date_format(t2.create_time,'%Y-%m-%d')= '2016-08-23' ) 
    -> group by   t4.ab_test ;
+-------------------------------------------------------------------------+
| concat_ws( '|' , '2016-08-23' ,   t4.ab_test , count(distinct t2.id ) ) |
+-------------------------------------------------------------------------+
| 2016-08-23|-1|446                                                       |
| 2016-08-23|0|1030                                                       |
| 2016-08-23|1|939                                                        |
+-------------------------------------------------------------------------+
3 rows in set (0.14 sec)