自从DBA老哥跑路后，数据库中间件大数据的活就落到我的头上了，领导的理由是我之前干过一段时间运维开发，对SQL比较熟！这理由我竟无法反驳，毕竟刚来不久。兼职DBA一个多月，什么稀奇古怪的需求都有。今天来了个有意思的活，有个开发老哥私聊，要update线上一个表，“大概影响15万条数据，可能很久，工单不好提，sql 全表扫，需要dba这边分批执行”，让他在yearing上分批提sql，他觉得没法提要写n条sql没法玩，得给我原始sql，让我分批执行。
这一下把锅和工作量就扔过来了。关键是他sql也没写，啥也没测，张口就来，真是可以的。出于职业操守，还是得给他弄，毕竟单子双方领导都批了~，来看看大佬给的原始sql：
UPDATE t_chegva_com SET status=2 WHERE purpose=1 AND id NOT IN( 191661, 191660, 191659, 191658, 191657, 191656, 191562, 191555, 191554, 191522, 191521, 162099, 125372, 125131, 125125, 125106, 124854, 124852, 56683, 56527, 56442, 56129, 55889, 55888, 55575, 54744, 54218, 53664, 50074, 50061, 50035, 49204, 9, 7);
将近15万条数据，直接梭哈感觉问题不大，但是他要求分批执行，线上出问题背不起这锅，那就拆开执行呗，就当学习啦。于是拆成了12条sql，这样还可以灰度执行验证，把线上表备份了下，然后在本机测了下，让大佬看下sql，大佬说挺巧妙的，执行一条让他验证一下，颇有种菜鸡互啄的感觉，不过这仪式感十足，对线上得有敬畏之心。
explain update t_chegva_com a,(select id from t_chegva_com where id between 1 and 10000 and purpose=1 and id not in (9, 7)) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 10000 and 40000 and purpose=1) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 40000 and 50000 and purpose=1 and id <>49204) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 50000 and 60000 and purpose=1 and id not in (56683, 56527, 56442, 56129, 55889, 55888, 55575, 54744, 54218, 53664, 50074, 50061, 50035)) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 60000 and 80000 and purpose=1) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 80000 and 100000 and purpose=1) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 100000 and 120000 and purpose=1) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 120000 and 130000 and purpose=1 and id not in (125372, 125131, 125125, 125106, 124854, 124852)) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 130000 and 160000 and purpose=1) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 160000 and 170000 and purpose=1 and id <> 162099) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id between 170000 and 190000 and purpose=1) b set status=2 where a.id=b.id; explain update t_chegva_com a,(select id from t_chegva_com where id >=190000 and purpose=1 id not in (191661, 191660, 191659, 191658, 191657, 191656, 191562, 191555, 191554, 191522, 191521)) b set status=2 where a.id=b.id;
其实在本地一把梭也就200多ms，后边又用另外两种写法试了下，对比了下效果，发现直接梭哈是最快的，哈哈
# 1、原生SQL explain update t_chegv_com set status=2 where purpose=1 and id not in (191661, 191660, 191659, 191658, 191657, 191656, 191562, 191555, 191554, 191522, 191521, 162099, 125372, 125131, 125125, 125106, 124854, 124852, 56683, 56527, 56442, 56129, 55889, 55888, 55575, 54744, 54218, 53664, 50074, 50061, 50035, 49204, 9, 7); +----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | UPDATE | t_chegv_com | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 145905 | 100.00 | Using where | +----+-------------+-------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ # 2、子查询（1） explain update t_chegv_com a,(select id from t_chegv_com where purpose=1 and id not in (191661, 191660, 191659, 191658, 191657, 191656, 191562, 191555, 191554, 191522, 191521, 162099, 125372, 125131, 125125, 125106, 124854, 124852, 56683, 56527, 56442, 56129, 55889, 55888, 55575, 54744, 54218, 53664, 50074, 50061, 50035, 49204, 9, 7)) b set status=2 where a.id=b.id; +----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+----------+-------------+ | 1 | SIMPLE | t_chegv_com | NULL | ALL | PRIMARY | NULL | NULL | NULL | 145905 | 10.00 | Using where | | 1 | UPDATE | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | azh_highing.t_chegv_com.id | 1 | 100.00 | NULL | +----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+----------+-------------+ # 3、子查询（2） explain update t_chegv_com a, t_chegv_com b set a.status=2 where a.purpose=1 and b.id not in (191661, 191660, 191659, 191658, 191657, 191656, 191562, 191555, 191554, 191522, 191521, 162099, 125372, 125131, 125125, 125106, 124854, 124852, 56683, 56527, 56442, 56129, 55889, 55888, 55575, 54744, 54218, 53664, 50074, 50061, 50035, 49204, 9, 7) and a.id=b.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+--------+----------+-------------+ | 1 | UPDATE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 145905 | 10.00 | Using where | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 8 | azh_highing.a.id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+--------+----------+-------------+ # 4、使用JOIN explain update t_chegv_com a JOIN (select b.id from t_chegv_com b where b.purpose=1 and b.id not in (191661, 191660, 191659, 191658, 191657, 191656, 191562, 191555, 191554, 191522, 191521, 162099, 125372, 125131, 125125, 125106, 124854, 124852, 56683, 56527, 56442, 56129, 55889, 55888, 55575, 54744, 54218, 53664, 50074, 50061, 50035, 49204, 9, 7)) b on a.id = b.id set a.status=2; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+--------+----------+-------------+ | 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 145905 | 10.00 | Using where | | 1 | UPDATE | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | azh_highing.b.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+--------+----------+-------------+
explain执行计划和实际执行效果：
参考：
换作我直接巴掌就过去了
@bboysoul 打输住院，打赢坐牢
，哈哈