背景
事情是这样的，让我娓娓道来...
前几天客户向我咨询一条SQL，为了客户隐私屏蔽了关键字，改成自己测试环境语句
代码语言：javascript复制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是订单表主键
代码语言：javascript复制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
代码语言：javascript复制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不支持...
代码语言：javascript复制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数据库。
