运维处理工单学到的sql优化

背景：公司业务线升级，更改业务线服务项名称，牵扯到已发行的批次券名称的更改，我的工单任务需要写sql刷数据实现这些名称的修改

涉及到的数据库两个数据表，我需要从A表中查询出在B表中关联的服务项只有1条数据并且所关联的这个服务项为所升级的业务线的服务项值所对应的批次，并且修改该A表中对应批次的名称。

我想到的sql如下：

---

UPDATE `t_batch` SET batch_name=replace(batch_name,'AAA','BBB') WHERE batch_name LIKE '%AAA' and all_service = 0 and id IN
(SELECT batch_id FROM t_batch_service WHERE service_id = & AND batch_id in
    (
    SELECT batch_id from t_batch_service GROUP BY batch_id HAVING COUNT(*)=1 
    )
) and status in(*,*,*,*) ;

---

sql解析

A	SELECT batch_id from mar_batch_service GROUP BY batch_id HAVING COUNT(*)=1	查询出批次服务项列表中根据批次id分组后的总量为1的批次id（为外部嵌套查询提供批次的过滤条件）
B	SELECT batch_id FROM mar_batch_service WHERE service_id = * AND batch_id in（A）	筛选出服务项id为& 并且批次id在A条件中的批次id
C	UPDATE `t_batch` SET batch_name=replace(batch_name,'AAA','BBB') WHERE batch_name LIKE '%AAA' and all_service = 0 and id IN（in）	更新批次表中批次在B范围内并且all_service = 0 的 名称为特定类型的批次名称

后续发现这样写的sql很繁琐原因有2 

1.内层两个自连接的sql可以合并成一个

2.这种in方法写出的sql是慢sql类型，会造成mar_batch_service的锁表，效率很低，

解决问题方法

问题	更改后
1.内层两个自连接的sql可以合并成一个	UPDATE `t_batch` SET batch_name=replace(batch_name,'AAA','BBB') WHERE batch_name LIKE '%AAA' and all_service = 0 and id IN( SELECT batch_id FROM t_batch_service WHERE service_id = 27 GROUP BY batch_id HAVING COUNT(*)=1 ) and status in(0,1,2,3,5);
2.这种in方法写出的sql是慢sql类型，会造成mar_batch_service的锁表，效率很低，	UPDATE t_batch a,(SELECT batch_id FROM t_batch_service where service_id=& GROUP BY batch_id HAVING COUNT(*)=1)b SET a.batch_name=replace(batch_name,'AAA','BBB') WHERE a.batch_name LIKE '%AAA' and a.all_service = 0 and a.status in(0,1,2,3,5) and a.id =b.batch_id;
写法2	select count(*) from t_batch a,(SELECT batch_id FROM t_batch_service b join (SELECT batch_id from t_batch_service GROUP BY batch_id HAVING COUNT(*)=1)c on b.batch_id=c.batch_id and service_id = &)d WHERE a.batch_name LIKE '%速运券' and a.all_service = 0 and a.status in(0,1,2,3,5) and a.id =d.batch_id;

分析：查询方式慢的原因：

参考:https://blog.csdn.net/zuishengmengsi_/article/details/55258826

where a.id = b.id的方式连接表数据，会以笛卡尔积的方式进行连接

join的连接方式中会以on后边的条件先连接

拓展：cross join也是一种笛卡尔积的方式连接，  即A ×B 这种效率很低