写操作，尽量走主键最小化锁粒度



【v0.1】更新v_TAB.status执行计划
【1】
UPDATE v_TAB SET status=13;
说明：全表数据总数行。
【2】
将oss中https://mybuncket.oss-cn-beijing.aliyuncs.com/gen_TAB/x.mp4存在的视频相应v_TAB.address的数据的status更新为0；
为逐条sql独立的执行，例如：
......
UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160486_5.mp4");
UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160694_5.mp4");
......
sql总条数：约20万。
【3】
将已投放的视频相应v_TAB.address的数据的status更新为1；
影响行数：约2万。




【v1.1】更新v_TAB.status执行计划
【1】
UPDATE v_TAB SET status=13;
说明：全表数据总数行。
【2】
update v_TAB_publish as vp left join v_TAB as v on v.id = vp.TAB_id set v.status = 1
将已投放的视频相应v_TAB.address的数据的status更新为1；
影响行数：约2万。
【3】
将oss中https://g3img-all.oss-cn-beijing.aliyuncs.com/gen_TAB/x.mp4存在的视频相应v_TAB.address的数据的status更新为0；
为逐条sql独立的执行，例如：
......
UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160486_5.mp4");
UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160694_5.mp4");
......
sql总条数：约20万。




【v2.1】更新v_TAB.status执行计划
v1.1-3执行执行时间

EXPLAIN  UPDATE v_TAB SET status=13;

id select_type    table  type   possible_keys  key    key_len    ref    rows   Extra
1  SIMPLE v_TAB  index     PRIMARY    4     549633 Using temporary

EXPLAIN update v_TAB_publish as vp left join v_TAB as v on v.id = vp.TAB_id set v.status = 1

id select_type    table  type   possible_keys  key    key_len    ref    rows   Extra
1  SIMPLE vp index     TAB_id 4     22168  Using index
1  SIMPLE v  eq_ref PRIMARY    PRIMARY    4  TAB.vp.TAB_id  1


[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:21:52 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160486_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:22:41 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160694_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:23:29 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160814_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:24:16 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160831_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:25:03 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35160859_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:25:51 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35161182_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:26:38 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35161262_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:27:25 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35200135_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:28:12 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35200214_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:28:59 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35200523_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]
Wed, 25 Jul 2018 15:29:46 chkMysqlFromOssUdpDB.py[line:101] INFO [START]UPDATE v_TAB SET status=0 WHERE INSTR(address,"gen_TAB/35200676_5.mp4")  AND status=13;
[thread:139756345866048][process:23691]



停止v1.1-3；
遍历v_TAB，去oss查询，用主键更新表;
【【【写操作，尽量走主键】】】


【【走主键不用 加班了 】】

Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232909_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232910_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=35[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232911_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232912_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232913_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232914_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232915_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=40[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232916_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232917_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232919_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232920_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232921_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232922_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=47[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232923_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=48[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232924_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=49[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232925_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232926_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232927_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232928_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=53[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232929_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:196] INFO object not existgen_TAB/new_50232930_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:175] INFO object existgen_TAB/new_50232931_5_fc.mp4[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:178] INFO UPDATE v_TAB SET status=0 WHERE id=56[thread:140010784782144][process:25702]
Wed, 25 Jul 2018 16:01:18 chkMysqlFromOssUdpDB.py[line:185] INFO [UDP-OK][thread:140010784782144][process:25702]



最小化锁粒度

posted @ 2018-07-25 15:27 papering 阅读(239) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

papering

写操作，尽量走主键 最小化锁粒度

写操作，尽量走主键最小化锁粒度