一个用户反映先上一个sql语句执行时间慢得无法接受。sql语句看上去很简单（本文描述中修改了表名和字段名):select count(*)fromajoinb ona.`s` = b.`s` where a.`l` > '2014-03-30 00:55:00' and a.`l`
分析
从explain的结果看，查询用了b作为驱动表。上一篇文章我们介绍到，mysql选择jion顺序是分别分析各种join顺序的代价后，选择最小代价的方法。这个join只涉及到两个表，自然也与optimizer_search_depth无关。于是我们的问题就是，我们预期的那个join顺序的为什么没有被选中？mysql tips: mysql提供straight_join语法，强制设定连接顺序。explain select count(*)fromastraight_joinb ona.`s` = b.`s` where a.`l` > '2014-03-30 00:55:00' and a.`l`
mysql tips: explain结果中，join的查询代价可以用依次连乘rows估算。
join顺序对了，简单的分析查询代价：普通join是1038165*1, straight_join是 63*1038165. 貌似mysql没有错。但一定哪里不对！
发现异常
回到我们最初的设想。我们预计表a作为驱动表，是因为认为表b能够用上ix_s索引，而实际上staight_join的时候确实用上了，但这个结果与我们预期的又不同。我们知道，索引的过滤性是决定了一个索引在查询中是否会被选中的重要因素，那么是不是b.s的过滤性不好呢？
mysql tips: show index from tbname返回结果中cardinality的值可以表明一个索引的过滤性。
show index的结果太多，也可以从information_schema表中取。mysql> select * from information_schema.statistics where table_name='b' and index_name='ix_s'/g*************************** 1. row ***************************table_catalog: def table_schema: test table_name: b non_unique: 1 index_schema: test index_name: ix_s seq_in_index: 1column_name: s collation: acardinality: 1038165 sub_part: null packed: null nullable: yes index_type: btree comment: index_comment: 可以这个索引的cardinality: 1038165，已经很大了。那这个表的估算行是多少呢。show table status like 'b'/g*************************** 1. row *************************** name: b engine: innodb version: 10 row_format: compact rows: 1038165 avg_row_length: 114 data_length: 119160832max_data_length: 0 index_length: 109953024 data_free: 5242880 auto_increment: null create_time: 2014-05-23 00:24:25 update_time: null check_time: null collation: utf8_general_ci checksum: null create_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 *fromastraight_joinb onbinary a.`s` = b.`s` where a.`l` > '2014-03-30 00:55:00' and a.`l`
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+
| 1 | simple | a | range | ix_l | ix_l | 4 | null | 63 | using where |
| 1 | simple | b | all | ix_s | null | null | null | 1038165 | range checked for each record (index map: 0x4) |
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+
由于结果是select *， 无法使用覆盖索引，因此第二行的key就显示为null. (笔者泪：要是早出这个结果查起来可方便多了）。
优化
当然最直接的想法就是修改两个表的s字段的定义，改成相同即可。这个方法可以避免修改业务代码，但ddl代价略大。这里提供两种在sql语句方面的优化。1、select count(*) fromb join (select s fromawhere a.`l` > '2014-03-30 00:55:00' and a.`l` '2014-03-30 00:55:00' and a.`l`
小结
做join的字段尽量设计为类型完全相同。