SQL优化之多表关联批量更新

1、背景

清洗表cookie_clean_t的数据分配清洗，清洗成功的结果记录在成功表cookie_short_succ_t中，间隔10分钟根据成功表cookie_short_succ_t的数据更新清洗表cookie_clean_t中数据状态为成功。清洗表数据量50多万条！

2、实现方案

2.1、版本V1

UPDATE cookie_clean_t cct SET cct.state=1 WHERE cct.state=0
AND cct.uuid IN(SELECT csst.uuid FROM cookie_short_succ_t csst WHERE csst.ctime<![CDATA[ <= ]]>#{lastTime})
AND cct.ctime<![CDATA[ >= ]]>CURDATE()

在配置高的生产环境（8核16G内存4M带宽）并未发现验证问题，在测试环境（2核4G内存2M带宽）影响到数据分发的SQL查询语句吃CPU占到195%

2.2、版本V2

UPDATE cookie_clean_t cct
INNER JOIN cookie_short_succ_t csst ON cct.uuid=csst.uuid 
SET cct.state=1
WHERE cct.state=0 AND cct.ctime>=CURDATE() AND csst.ctime<![CDATA[ <= ]]>#{lastTime}

优化成表关联批量更新后索引生效，CPU占用不足2%

分析SQL语句例子：

EXPLAIN
UPDATE cookie_clean_t cct SET cct.state=1 WHERE cct.state=0
AND cct.uuid IN(SELECT csst.uuid FROM cookie_short_succ_t csst WHERE csst.ctime<='2019-09-10 09:00:00')
AND cct.ctime>=CURDATE()

1 UPDATE cct index idx_state,idx_ctime PRIMARY 8 502077 100 Using where
2 DEPENDENT SUBQUERY csst ALL 1378 3.33 Using where

 

EXPLAIN
UPDATE cookie_clean_t cct
INNER JOIN cookie_short_succ_t csst ON cct.uuid=csst.uuid
SET cct.state=1
WHERE cct.state=0 AND cct.ctime>=CURDATE() AND csst.ctime<='2019-09-10 09:00:00';

1 SIMPLE csst ALL 284 33.33 Using where
1 UPDATE cct eq_ref PRIMARY,idx_state,idx_ctime,idx_uuid PRIMARY 8 db_wb3.csst.uuid 1 25 Using where

 

show full processlist;
kill id;

 

转载于:https://www.cnblogs.com/xx0829/p/11496558.html