Mysql字符集对关联查询的影响

最近有个需要对生产环境数据进行修复，量比较大，单个sql关联千万数据的表更新几万到几十万不等，sql如下：

UPDATE month_201601_count t1

JOIN app_car_sum_daily_event_copy t2 ON (

t1.carid = t2.car_id

AND t1.date = DAYOFMONTH(t2.date)

)

SET t1.accelerateTimes = t2.accelerate_times,

 t1.decelerateTimes = t2.decelerate_times,

 t1.sharpTurnTimes = t2.sharp_turn_times

WHERE

t2.date >= '2016-01-01'

AND t2.date < '2016-02-01';

直接在生产环境跑，也没怎么在意，毕竟sql之前开发提过来经过优化执行过的，应该没什么问题，几个小时过去回头来看的时候居然一个sql都没跑完，show innodb状态看到该事务才扫描出几万条数据，按这效率估计一个星期

才能跑完一条sql，赶紧停了看看情况，首先看了一下执行计划:

没有按照事先考虑好的t2表驱动t1表，如果用t1表驱动t2在date字段有函数用不了索引，而且t1表还是用date字段做了分区的表，主键为（carid,date）,按理想中的t2驱动t1表主键索引应该都会用上，还能定位t1的分区表，现在这样的方式效率肯定大打折扣，再仔细看看执行计划发现ref列出现了func，意思就是在对t2表值进行关联时了用了function，这个有点坑，立马用explain extended加show warnings看下具体情况，因为update语句不支持extended选项写了个关联查询的语句：

这就坑了，居然在关联字段carid上面做了字符集转换，去查看表结构发现t1 表为utf8、t2 表时utf8mb4，utf8mb4是utf8的超集，在编码上有所不同，mysql内部会发生转换，那么把t2表的字符集同样修改为utf8再看执行计划：

变成了预期所想的t2作为驱动表，ref列已经出现链接字段的情况，依然看到有func，不过这不影响，因为函数应用在t2表关联时的条件值上面，在生产环境中改了t2表字符集之后单个sql执行时间在一分钟左右，成功解决问题

发生这种函数转化有可能是全表扫描了，虽然说执行计划显示用了索引其实有可能并没有，可以开trace查看,开trace很简单

set optimizer_trace="enabled=on"，并把optimizer_trace_max_mem_size调大点，不然计划内容多了装不完，下面把t1和t2两个表字符集对换做的验证：

可以看到驱动变为了t2，t1也使用了primary索引，转换变成了t2表的字符集，这可以得出个结论字符集转换都是由低向高转化的，再看看trace跟踪信息

可以看出t1表是使用了全表扫描的，并不像显示的那样使用了索引，最开始没有调换字符集的时候t2表是使用了索引，但是发生了函数转化依然会降低执行效率

在生产环境中一定需要注意字符集统一，当然也可以在只需要emoji表情的表设置utf8mb4，前提得控制好关联查询，统一字符集可以避免时间久了搞忘，从而产生这种关联查询效率低的问题

转载于:https://blog.51cto.com/xiaozhong991/1890003