关于比较字段使用不同字符集导致的索引失效问题

最近遇到这么一个问题，两个表通过user_id关联

where a.user_id = b.user_id

且两个表中user_id均加了索引

table a
key (user_id)

table b
unique key (user_id)

但实际查询很慢， 约3605ms。为什么这么慢呢？ 通过explain extended命令可知未走索引

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	ALL					3533678
1	SIMPLE	a	ref	user_id	user_id	162	func	1	Using index condition

2 rows in set, 1 warning (0.00 sec)

查看警告

show warnings\G
  Level: Note
   Code: 1003
Message: /* select#1 */ ... where (convert(`b`.`user_id` using utf8mb4) = `a`.`user_id`)
1 row in set (0.01 sec)

可知实际的执行过程中对b表索引列使用了函数。 原来是因为字符编码格式不匹配导致索引失效

a.user_id(UTF8MB4_UNICODE_CI) = b.user_id(utf8_bin)

后面改成统一使用utf8，此时查询就很快了，约14ms, 提升了约258倍。

改成统一使用utf8后 再次执行explain

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	user_id				79
1	SIMPLE	b	eq_ref	user_id	userid	122	a.user_id	1

参考文档

http://stackoverflow.com/ques...

http://jorgenloland.blogspot....

http://jaskey.github.io/blog/...