力荐：一条update语句引发的“血案”

有一次得到应用同学的反馈，有一个前端应用登录很慢，已经开始影响业务登录了，稍后DBA介入，发现是由于CPU使用率过高导致，为了能够缓解问题和进一步分析，做了一些改进措施，最后问题得到了化解，但是对于这个问题后续也进行了更多的分析，也算是事后诸葛亮吧。

 

整个分享的思路如下：

问题背景

提出疑问

问题的对比测试

问题的验证

问题总结

 

 

 

问题背景  

 

查看慢日志的情况如下：   

 

两个查询的统计信息如下，可以看到平均执行时间竟然都在40s左右。

 

 

涉及的SQL语句如下，这个也是当时从慢日志中得到的。

 

 

相关的表只有一个，表结构如下：

 

 

整个调用过程的要点是下面的形式，里面有一个update操作，字段APNS_PUSH_ID为varchar

 

 

其实单独运行的语句就类似下面的形式：

 

这样一个update语句竟然很慢，着实感到很奇怪，因为单独执行，查看执行计划是没有问题的。

 

 

提出疑问  

 

对于这个问题的疑问如下：

 

1、对于字符型字段作为索引，目前来看没有很直接的原因发现字符型索引和数字型索引存在巨大的差别。从后来我单独得到的执行计划和后来复现情况来看，没有发现存在很巨大的差别。

 

2、对于慢日志中得到的语句，看到内部已经做了转换。

 

 

而对于这种转换，可能关注点都在NAME_CONST这个部分，在查看了一些资料之后，发现在其他版本和环境中，主要是和字符集转换有关，但是单独执行上面的转换语句，查看执行计划没有任何问题。

 

3、在5.1版本中发现了相应的bug描述，但是目前的环境是在5.6，所以问题应该已经得到修复。

 

我希望得到一些确切的信息，能够复现，能够找到一些相关的bug或者相关的解决方案。

 

 

问题的对比测试  

 

 

我找了套环境尝试复现这个问题，我把表里的数据复制到一个测试环境，然后写了下面的存储过程来复现和对比。

 

 

测试前，保证handler是初始化状态

 

 

然后运行存储过程，其实这个过程就是当时问题发生时的一个调用环节。

 

 

查看Handler的状态，可以看到Handler_read_next的值极高，其实这是一个全表扫描。

 

 

而如果单独执行同样的sql语句。

 

 

查看Handler的情况，Handler_read_rnd_next为0，很显然是一个索引扫描。

 

 

如果查看单独update语句的执行计划，是看不到太多的明细信息的。

 

 

我们可以打开trace，MySQL 5.6以后有一个特性，可以试试。

  

 

可以看到内部做了字符集的转换，而转换的过程其实也可以这么理解，convert(`push_list_s`.`APNS_PUSH_ID` using utf8)这个操作是把全表的APNS_PUSH_ID先做转换和push_id做匹配，这也就无形中导致了全表扫描。

 

 

 

执行单个语句，查看trace的情况。

 

 

 可以看到解析的时候是在做键值的匹配。

  

 

对于这个问题，经过这样的分析测试，会发现在存储过程中和单独执行的场景中还是存在差别的，而问题的关键就在于字段APNS_PUSH_ID的字符集，

 

 

所以唯一的差别就在于字符集，MySQL对于字符集的支持非常灵活，数据库级，表级，字段级别都可以定制,而对于这个问题的直接修复，就是统一字段” APNS_PUSH_ID”的字符集为表级的UTF8。

 

 

问题的验证  

 

问题的验证步骤如下：

 

 

统一字符集之后，再次执行，就会发现效率就会大大提高。

 

 

而且MySQL的回复如下：

Problem is that the stored routine does not explicitly declare the charset of the parameter that is passed to the stored routine. It must match the column's charset to which you're comparing it to. 

 

 

问题总结  

 

其实对于问题还是需要刨根问底，找到了问题的症结，就会让我们在处理问题的时候更加坦然。我自己也尝试从Oracle的对比中得到一些解决问题的思路，但是Oracle对于字符集的支持是统一管理方式的，所以也是无果而终，不过这种对比方式给了我一些思路。对于字符集的设定，虽然灵活方便，但是也要使用统一得当。

 

 作者介绍  杨建荣

 

• 【DBAplus社群】联合发起人；

• Oracle ACE-A、YEP成员，现就职于搜狐畅游，拥有6年以上的数据库开发和运维经验，曾任amdocs DBA，擅长电信数据业务，数据库迁移和性能调优；

• 拥有Oracle 10g OCP，OCM，MySQL OCP认证，对shell，ava有一定的功底，曾在2015年数据库大会进行关于数据迁移和升级的主题分享，现在每天仍在孜孜不倦的进行技术分享，每天通过微信，技术博客共享，已连续坚持800多天。

本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2016-05-23