专栏首页MySQL故障优化案例MySQL Cases-SQL导致CPU使用率100%处理
原创

MySQL Cases-SQL导致CPU使用率100%处理

背景

事情是这样的,让我娓娓道来...

前几天客户向我咨询一条SQL,为了客户隐私屏蔽了关键字,改成自己测试环境语句

WITH tabs AS
(
SELECT ROW_NUMBER() OVER(PARTITION by O_ORDERPRIORITY ORDER BY O_TOTALPRICE DESC) as my_rowid,
o_orderkey,
O_CUSTKEY
O_TOTALPRICE from orders where O_ORDERDATE >'1998-01-01' and O_ORDERDATE<'1998-12-30'
)
update tabs set O_TOTALPRICE = O_TOTALPRICE+1 where my_rowid>1;

看到这个条SQL写法还是有问题的,我按照他的意思做了改写,我们先分析他要的语句的逻辑

按照O_ORDERPRIORITY订单属性分组,更新每组中价格不是最大的那个值,那么好,按照这个意思,我该写成如下,o_orderkey是订单表主键

update orders
set O_TOTALPRICE = O_TOTALPRICE + 1
where o_orderkey
          in
      (select o_orderkey
       from (
                SELECT ROW_NUMBER() OVER (PARTITION by O_ORDERPRIORITY ORDER BY O_TOTALPRICE DESC) as my_rowid,
                       o_orderkey
                from orders
                where O_ORDERDATE > '1998-01-01'
                  and O_ORDERDATE < '1998-12-30'
            ) x
       where x.my_rowid > 1
      );

我提醒MySQL中批量更新要分批执行

CPU100%

又过了几天客户,说CPU 100%了,查询慢SQL正式,前几天那个关联更新...

那么这个SQL为什么这么慢呢...先说下Oracle中的解决办法,可以改写成merge into引导SQL走hash join,可以的话并且加适当的并行,MySQL8.0不支持merge into

merge into orders o using 
(select o_orderkey
       from (
                SELECT ROW_NUMBER() OVER (PARTITION by O_ORDERPRIORITY ORDER BY O_TOTALPRICE DESC) as my_rowid,
                       o_orderkey
                from orders
                where O_ORDERDATE > '1998-01-01'
                  and O_ORDERDATE < '1998-12-30'
            ) x
       where x.my_rowid > 1) x
on (x.o_orderkey = o.o_orderkey)
when matched then update set o.O_TOTALPRICE = o.O_TOTALPRICE+1

那么我们看下执行计划,关于format=tree的使用可以参考https://cloud.tencent.com/developer/article/1876791这篇文章,但是很遗憾这个SQL不支持...

explain format=tree update tpch.orders set O_TOTALPRICE = O_TOTALPRICE + 1 where o_orderkey
          in
      (select o_orderkey
       from (
                SELECT ROW_NUMBER() OVER (PARTITION by O_ORDERPRIORITY ORDER BY O_TOTALPRICE DESC) as my_rowid,
                       o_orderkey
                from tpch.orders
                where O_ORDERDATE > '1998-01-01'
                  and O_ORDERDATE < '1998-12-30'
            ) x
       where x.my_rowid > 1
      );
--输出结果:
<not executable by iterator executor>

那么看普通版本的执行计划

被驱动表是没有索引的,这个SQL在MySQL中肯定是没办法执行出结果的,

表中一共几十万行数据,但是由于匹配因素,关联影响到了20亿行,那么到这里这个案例就结束了

结论:

MySQL并不适合OLAP数据分析型SQL,由于是在8.0支持分析函数的情况下,在生产中执行还是要小心,他并不向Oracle那么高效,还有需要提升学习的地方

那么,对于MySQL关联更新你有什么好的建议吗?

更多文章欢迎关注本人公众号,搜dbachongzi或扫二维码

作者:姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证,擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Chrome的BUG导致CPU使用率飙升至100%

    Google Chrome 中目前发现了一个新的 Bug,使用 JavaScript 创建一个循环,最终导致 Google Chrome 耗尽计算机上的所有 C...

    Fundebug
  • MySQL 最佳实践:CPU 100%,MySQL 到底在干什么

    在日常工作中,发现 MySQL 的状态不太对劲的时候,一般都会看看监控指标,很多时候会看到熟悉的一幕:CPU 使用率又爆了。本文会简单介绍一下 MySQL 和 ...

    王文安@DBA
  • 云数据库MySQL CPU飙升排查流程

    在日常使用MySQL的过程中,会遇到 CPU 使用率过高甚至达到 100% 的情况。CPU飙升会导致数据库无法连接,事务无法提交等一系列问题。本文基于日常问题处...

    苏欣
  • MySQL 案例:为什么 kill 不掉线程

    在日常的使用过程中,时不时会遇到个别,或者大量的连接堆积在 MySQL 中的现象,这时一般会考虑使用 kill 命令强制杀死这些长时间堆积起来的连接,尽快释放连...

    王文安@DBA
  • 腾讯云数据库(MySQL)监控最佳指南

    作者:赵珣  腾讯云监控工程师 简介 云数据库 MySQL(TencentDB for MySQL)是腾讯云基于开源数据库 MySQL 专业打造的一种高性能分...

    腾讯云监控团队
  • 解决macOS上使用node10开发vue导致cpu利用率一直100%的方法

    网上查了很多资料, 基本都是在讲是webpack调用 fsevents 出错,然后频繁查找node_modules目录所致。本地install了还是不停的100...

    飞奔去旅行
  • MySQL数据库CPU问题一则

    MySQL 一般出现 CPU 负载过高问题的时候,我们都会去看下故障期间的慢sql日志,然后找出全表扫描、索引不合理、函数运算过多的sql,让开发同学优化下。实...

    用户1278550
  • DBbrain诊断日 | DBA休假,数据库CPU使用率过高怎么办?

    为更好的帮助DBA运维数据库,腾讯云将于每月12日在社群直播开展DBbrain诊断日,腾讯云高级产品经理迪B哥直播解析经典数据库运维难题,结合腾讯云数据库智能...

    腾讯云数据库 TencentDB
  • Mysql慢SQL分析及优化

    从数据库角度看:每个SQL执行都需要消耗一定I/O资源,SQL执行的快慢,决定资源被占用时间的长短。假设总资源是100,有一条慢SQL占用了30的资源共计1分钟...

    常见_youmen
  • MySQL CPU性能定位

    墨墨导读:经常会看到看到cpu 使用率非常高的情况。在这种情况下,资源的使用监控分析才是性能故障分析的根本首要任务,通过这些分析,理解服务器如何运行,资源损耗在...

    数据和云
  • MySQL数据库优化二三事

    点击上方蓝字“ITester软件测试小栈“关注我,每周一、三、五早上 08:30准时推送,每月不定期赠送技术书籍。

    ITester软件测试小栈
  • 下班前的一个CPU负载过高问题

    今天是星期一,也是双十一,问了一圈周边的人,好像没有买东西的居多,大家都是不知道该买啥好,看来像我一样的老年人变多了,工作了一天,累了,写完早点休息了。...

    AsiaYe
  • 从库mysqldump会导致复制中断

    ERROR NO 是1756,而且只是 Slave_SQL_Running 停了。

    老叶茶馆
  • 实例解析:MySQL性能瓶颈排查定位,实现毫秒级完成180秒的任务

    登入服务器后,我们的目的是首先要确认当前到底是哪些进程引起的负载高,以及这些进程卡在什么地方,瓶颈是什么。

    数据和云
  • MySQL导致的CPU高负载问题

    在某个新服务器上,新建了一个MySQL的实例,该服务器上面只有MySQL这一个进程,但是CPU的负载却居高不下,使用top命令查询的结果如下:

    AsiaYe
  • MySQL中,21个写SQL的好习惯

    每一个好习惯都是一笔财富,本文分SQL后悔药, SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~

    xjjdog
  • MySQL 中的 21 个好习惯你要养成

    每一个好习惯都是一笔财富,本文分SQL后悔药, SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~

    cxuan
  • MySQL中,21个写SQL的好习惯

    本文公众号来源:捡田螺的小男孩 作者:捡田螺的小男孩 本文已收录至我的GitHub

    Java3y
  • 数据库 SQL 开发和操作行为规范

    预编译语句可以重复使用这些计划,减少 SQL 编译所需要的时间,还可以解决动态 SQL 所带来的 SQL 注入的问题;只传参数,比传递 SQL 语句更高效;相同...

    happyJared

扫码关注云+社区

领取腾讯云代金券