记一次简单的生产环境Mysql调优

记一次简单的生产环境Mysql调优

第一次写博客,有漏洞的地方希望大佬指正

背景:优化公司老系统中的一条慢sql,查看执行过程如下:

sql虽然比较简单,平时执行也没发现什么问题,但一到生产环境系统频繁调用,问题就显现出来了,整个系统停滞不前,打开Navicat的服务器监控发现里面出现好多执行中的sql甚至出现死锁

查看knowledge_default_operation_log表发现数据10多万条,knowledge_base_info表1万多条,由第第一张图可以明显的看出knowledge_default_operation_log表没有走索引,查看该表发现knowledge_base_id字段上有索引,怎么回事?

根据以往的经验又查看knowledge_base_id与knowledge_id的字段类型,没问题都是varchar类型啊,往下看发现了问题所在

统一修改排序规则为utf8_general_ci及最终调整sql结构后执行结果如下:

执行时间平均在0.17s左右,线上该模块慢查询消失,网上查看资料得知

由于knowledge_default_operation_log表取出的knowledge_base_id字段是utf8_general_ci字符集,而knowledge_base_info表里面的knowledge_id是utf8_bin字符集,这里需要做字符集转换,字符集转换遵循由小到大的原则,两者可能存在字符转换,转换了之后,由于knowledge_default_operation_log上面的索引仍然是utf8_general_ci字符集,所以这个索引就被执行计划忽略了,然后knowledge_default_operation_log表只能选择全表扫描。knowledge_base_info表如果筛选出来的记录不止1条,那么knowledge_default_operation_log就会被全表扫描多次,性能就变慢了。

结尾注意:生产环境修改字符集不能用alter table xx charset utf8_general_ci,因为生产执行的查询比较多会引起阻塞,另外也不能修改已经存在字段值的字符集,我是在业务低峰时期用pt-online-schema-change改的。