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

2023-12-07 19:40

本文主要是介绍力荐:一条update语句引发的“血案”,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!


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

 

整个分享的思路如下:

问题背景

提出疑问

问题的对比测试

问题的验证

问题总结

 

 

 

问题背景  

 

查看慢日志的情况如下:   

 

20160523104236648.png


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

 

20160523104225269.png 

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

 

20160523104214391.png

 

相关的表只有一个,表结构如下:

 20160523104203959.png

 

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

 

20160523104151649.png

 

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


20160523104142749.png 

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

 

 

提出疑问  

 

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

 

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

 

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

 

20160523104133365.png

 

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

 

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

 

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

 

 

问题的对比测试  

 

 

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

 

20160523104122879.png
 

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

 

20160523104114181.png

 

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

 

20160523104105891.png

 

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

 

20160523104054722.png
 

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

 

20160523104044141.png
 

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

 

20160523104034742.png
 

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

 

20160523104025847.png

 

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

  

20160523104012439.png

 

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

 

20160523104002856.png
20160523103949569.png

 

 

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

 

20160523103934660.png
 

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

  20160523103924682.png

20160523103906784.png

 

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

 

20160523103850958.png 

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

 

 

问题的验证  

 

问题的验证步骤如下:

 

20160523103839424.png

 

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

 

20160523103827314.png

 

而且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

这篇关于力荐:一条update语句引发的“血案”的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/467113

相关文章

总有一条路,我们很迷茫

十年前,我家还处于一个贫穷落后的小山村,周围的人会根据我父母的收入来对待我,而十年后的今天,我家的那座小山村医成为重点开发的地区,一夜之间我家成了所谓的土豪,周围的人依然根据我家的收入对待我。现实,什么是现实?这就是现实。从那一刻,我开始明白要想得到别人的尊重,首先你得有别人尊重的实力。 所以,这么多年来不管自己过得多累,走得多艰辛,我都会一直坚持。在人生前进的道路,我们总会经历风雨,难免感到迷

封装MySQL操作时Where条件语句的组织

在对数据库进行封装的过程中,条件语句应该是相对难以处理的,毕竟条件语句太过于多样性。 条件语句大致分为以下几种: 1、单一条件,比如:where id = 1; 2、多个条件,相互间关系统一。比如:where id > 10 and age > 20 and score < 60; 3、多个条件,相互间关系不统一。比如:where (id > 10 OR age > 20) AND sco

【Python知识宝库】上下文管理器与with语句:资源管理的优雅方式

🎬 鸽芷咕:个人主页  🔥 个人专栏: 《C++干货基地》《粉丝福利》 ⛺️生活的理想,就是为了理想的生活! 文章目录 前言一、什么是上下文管理器?二、上下文管理器的实现三、使用内置上下文管理器四、使用`contextlib`模块五、总结 前言 在Python编程中,资源管理是一个重要的主题,尤其是在处理文件、网络连接和数据库

FPGA开发:条件语句 × 循环语句

条件语句 if_else语句 if_else语句,用来判断是否满足所给定的条件,根据判断的结果(真或假)决定执行给出的两种操作之一。 if(表达式)语句; 例如: if(a>b) out1=int1; if(表达式)         语句1; else         语句2; 例如: if(a>b)out1=int1;elseout1=int2; if(表达式1) 语句1; els

mysql 修改表结构语句

主要还是要参考mysql的官方网站 http://dev.mysql.com/doc/refman/5.7/en/alter-table.html 简单例子: alter table_name alter column old_column_name new_column_name int unsigned;

MySQL学习笔记-join语句类型

join从句的类型:内链接(inner) 全外连接(full outer) 左外连接(left outer) 右外连接(right outer) 交叉链接(cross) 连接条件:使用ON设定连接条件,也可以用WHERE代替 · ON:设定连接条件 · WHERE:进行结果集记录的过滤 一,内连接inner join:  内连接是返回左表及右表符合连接条件的记录,在MySQL中JO

Oracle和Sql_Server 部分sql语句的区别

比如:A表中, 字段:gxmlflag  number;  比如数据:20210115 字段:gxmldate date ;    比如数据:2021-01-15 09:50:50 一、在Oracle数据库中: 1、insert 和 update 语句: t.gxmlflag = to_char(sysdate,'yyyymmdd'),t.gxmldate=sysdate 比如:update f

PostgreSql中WITH语句的使用

https://blog.csdn.net/chuan_day/article/details/44809125 PostgreSql中WITH语句的使用 With语句是为庞大的查询语句提供了辅助的功能。这些语句通常是引用了表表达式或者CTEs(一种临时数据的存储方式),可以看做是一个查询语句的临时表。在With语句中可以使用select,insert,update,delete语句。当然wit

【语句】如何将列表拼接成字符串并截取20个字符后面的

base_info = "".join(tree.xpath('/html/head/script[4]/text()'))[20:] 以下是对这个语句的详细讲解: tree.xpath('/html/head/script[4]/text()')部分: tree:通常是一个已经构建好的 HTML 文档树对象,它是通过相关的 HTML 解析库(比如 lxml)对 HTML 文档进行解

Shell脚本判断、if语句

1、条件测试类型 整数测试 字符测试 文件测试 2、条件测试的表达式 [ 条件表达式 ][[ 条件表达式 ]]test 条件表达式 示例: [root@node1 ~]# test -e file && echo true || echo false # 测试是否又file这个文件false [root@node1 ~]# touch file