问题描述
一个用户反映先线一个sql语句执行时间慢得无法接受。sql语句看上去很简单（本文描述中修改了表名和字段名):
select count(*) from a join b on a.`s` = b.`s` where a.`l` > '2014-03-30 00:55:00' and a.`l`
且查询需要的字段都建了索引，表结构如下：
create table `a` (`l` timestamp not null default '2000-01-01 00:00:00',`i` varchar(32) character set utf8 collate utf8_bin default null,`a` varchar(32) character set utf8 collate utf8_bin default null,`s` varchar(64) character set utf8 collate utf8_bin default null,`f` tinyint(4) default null,`v` varchar(256) character set utf8 collate utf8_bin default '',`n` varchar(64) character set utf8 collate utf8_bin default null,key `ix_l` (`l`),key `ix_i` (`i`),key `ix_s` (`s`)) engine=innodb default charset=utf8;create table `b` (`r` timestamp not null default '2000-01-01 00:00:00',`v` varchar(32) default null,`u` varchar(32) default null,`c` varchar(16) default null,`s` varchar(64) default null,`i` varchar(64) default null,`e` bigint(32) default null,`es` varchar(128) default null,key `ix_r` (`r`),key `ix_c` (`c`),key `ix_s` (`s`)) engine=innodb default charset=utf8;
从语句看，这个查询计划很自然的，就应该是先用a作为驱动表，先后使用 a.l和b.s这两个索引。而实际上explain的结果却是：
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+| 1 | simple | b | index | ix_s | ix_s | 195 | null | 1038165 | using index || 1 | simple | a | ref | ix_l,ix_s | ix_s | 195 | test.b.s | 1 | using where |+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
分析
从explain的结果看，查询用了b作为驱动表。
上一篇文章我们介绍到，mysql选择jion顺序是分别分析各种join顺序的代价后，选择最小代价的方法。
这个join只涉及到两个表，自然也与optimizer_search_depth无关。于是我们的问题就是，我们预期的那个join顺序的为什么没有被选中？
mysql tips: mysql提供straight_join语法，强制设定连接顺序。
explain select count(*) from a straight_join b on a.`s` = b.`s` where a.`l` > '2014-03-30 00:55:00' and a.`l` select * from information_schema.statistics where table_name='b' and index_name='ix_s'\g*************************** 1. row ***************************table_catalog: deftable_schema: testtable_name: bnon_unique: 1index_schema: testindex_name: ix_sseq_in_index: 1column_name: scollation: acardinality: 1038165sub_part: nullpacked: nullnullable: yesindex_type: btreecomment:index_comment:
可以这个索引的cardinality: 1038165，已经很大了。那这个表的估算行是多少呢。
show table status like 'b'\g*************************** 1. row ***************************name: bengine: innodbversion: 10row_format: compactrows: 1038165avg_row_length: 114data_length: 119160832max_data_length: 0index_length: 109953024data_free: 5242880auto_increment: nullcreate_time: 2014-05-23 00:24:25update_time: nullcheck_time: nullcollation: utf8_general_cichecksum: nullcreate_options:comment:1 row in set (0.00 sec)
从rows: 1038165看出，ix_s这个索引的区分度被认为非常好，已经近似于唯一索引。
mysql tips: 在show table status结果中看到的rows用于表示表的当前行数。对于myisam表这是一个精确值，但对innodb这是个估算值。
虽然是估算值，但优化器是以此为指导的，也就是说，上面的某个explain里面的数据完全不符合期望：staight_join结果中第二行的rows。
阶段结论
我们发现整个错误的逻辑是这样的：以a为驱动表的执行计划，由于索引b.s的rows估计为1038165导致优化器认为代价大于以b为驱动表。而实际上这个索引的区分度为1.（当然对explan结果比较熟悉的同学会发现，第二行的type字段和extra字段一起诡异了)
也就是说，straight_join得到的每一行去b中查询的时候，都走了全表扫描。在mysql里面出现这种情况的最常见的是类型转换。比如一个字符串字段，虽然包含的是全数字，但查询的时候传入的不是字符串格式。
在这个case里面，两个都是字符串。因此，就是字符集相关了。
回到两个表结构，发现s字段的声明差别在于 collate utf8_bin -- 这个就是本case的根本原因了：a表得到的s值是utf8_bin，优化器认为类型不同，无法直接用上索引b.ix_s过滤。
至于为什么还会用上索引，这个是因为覆盖索引带来“误解”。
mysql tips：若查询的所有结果能够从某个索引完全得到，则会优先用遍历索引替代遍历数据。
作为验证，
mysql> explain select * from a straight_join b on binary a.`s` = b.`s` where a.`l` > '2014-03-30 00:55:00' and a.`l` '2014-03-30 00:55:00' and a.`l` '2014-03-30 00:55:00' and a.`l` < '2014-03-30 01:00:00' ;
从前面的分析知道是由于b.s定义为utf8_bin.
mysql tips: mysql中字符集命名规则中, xxx_bin与xxx的区别为大小写是否敏感。
这里我们将a.s全部增加binary限定，先转为小写，就是将临时结果集转成utf8_bin，之后使用b.s匹配时就能够直接利用索引。
其实两个改写方法的本质相同，区别是写法1是隐式转换。理论上说写法2速度更快些。
小结
做join的字段尽量设计为类型完全相同。