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

原创

姚崇

修改于 2021-09-18 11:35:03

1K0

修改于 2021-09-18 11:35:03

文章被收录于专栏：MySQL故障优化案例MySQL故障优化案例

背景

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

前几天客户向我咨询一条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>

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