mysql表关联查询索引不生效问题

 

 

今天在执行sql语句时,使用表关联查询,结果发现子查询中的索引未使用,直接使用了全表查询,如图所示:

 

找了半天原因,最后发现,是由于字符集设置问题导致的

 

 当将两个字段的字符集统一后,查询结果如下:

 Mysql 参考文档中也明确说非二进制String应该使用相同字符集:

To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size.
For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a utf8 column with a latin1 column precludes use of an index.

 

 

参考文献:

1. http://www.cnblogs.com/weizhenlu/p/5970392.html

2. http://fatkun.com/2011/05/mysql-alter-charset.html
3. https://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html