mysql 连接查询在on中使用convert 导致扫描行数增长

因为表前期设计问题,导致要连接的两张表的主外键类型不一致.但是在生产库上已经调整为一致了,测试库没有调整.开发时Navicat美化sql会自动帮忙加上convert(`d`.`id` using utf8mb4)字样.不小心把这个sql复制到生产了.于是导致了sql变慢.

加上了convert转换的解释结果:

1	PRIMARY	<derived2>	ALL					102899300	Using filesort
2	DERIVED	a	ALL					8	
2	DERIVED	u	eq_ref	PRIMARY	PRIMARY	8	a.user_id	1	
3	DEPENDENT SUBQUERY	o	ALL					710	Using where
4	UNION	v	ALL					491	
4	UNION	u	eq_ref	PRIMARY	PRIMARY	8	v.members_user_id	1	
4	UNION	m	eq_ref	PRIMARY	PRIMARY	8	v.merchants_id	1	
4	UNION	d	ALL					105214	Using where; Using join buffer (Block Nested Loop)
5	DEPENDENT SUBQUERY	o	ALL					710	Using where
6	UNION	s	ALL					487	
6	UNION	u	eq_ref	PRIMARY	PRIMARY	8	s.user_id	1	
6	UNION	m	eq_ref	PRIMARY	PRIMARY	8	s.merchants_id	1	
6	UNION	d	ALL					105214	Using where; Using join buffer (Block Nested Loop)
7	DEPENDENT SUBQUERY	o	ALL					710	Using where
	UNION RESULT	<union2,4,6>	ALL						Using temporary

去掉的解释结果:

1	PRIMARY	<derived2>	ALL					986	Using filesort
2	DERIVED	a	ALL					8	
2	DERIVED	u	eq_ref	PRIMARY	PRIMARY	8	a.user_id	1	
3	DEPENDENT SUBQUERY	o	ALL					710	Using where
4	UNION	v	ALL					491	
4	UNION	u	eq_ref	PRIMARY	PRIMARY	8	v.members_user_id	1	
4	UNION	m	eq_ref	PRIMARY	PRIMARY	8	v.merchants_id	1	
4	UNION	d	eq_ref	PRIMARY	PRIMARY	202	m.dp_id	1	
5	DEPENDENT SUBQUERY	o	ALL					710	Using where
6	UNION	s	ALL					487	
6	UNION	u	eq_ref	PRIMARY	PRIMARY	8	s.user_id	1	
6	UNION	m	eq_ref	PRIMARY	PRIMARY	8	s.merchants_id	1	
6	UNION	d	eq_ref	PRIMARY	PRIMARY	202	m.dp_id	1	
7	DEPENDENT SUBQUERY	o	ALL					710	Using where
	UNION RESULT	<union2,4,6>	ALL						Using temporary

这里可以看到<derived2>的差别巨大,本来一个很小很小的应用,没有多少数据,加上convert后,居然会扫描出102899300上亿行的数据.

可能是因为convert是针对字符集的转换.那么会导致每一个字符算作一行数据.当然这只是一个猜想.