Original link: https://chegva.com/5744.html
Since the DBA brother ran away, the work of database middleware and big data has fallen on my head. The reason for the leadership is that I have worked in operation and maintenance development for a period of time before, and I am familiar with SQL! I can’t refute this reason, after all, I just came here not long ago. Part-time DBA for more than a month, there are all kinds of weird needs. I had an interesting event today. I had a private chat with a development brother and wanted to update a table online. “About 150,000 pieces of data will be affected, and it may take a long time. The work order is not easy to mention. SQL full table scan needs to be distributed by the dba.” Batch execution “, let him mention the sql in batches on the yearing, he thinks it is impossible to write n sql to play, he has to give me the original sql, let me execute in batches.
This time the pot and the workload were thrown over. The key is that he didn’t write sql, didn’t test anything, and just opened his mouth, which is really possible. Out of professional ethics, I still have to get it for him. After all, the leaders of both sides of the list have approved it~ Let’s take a look at the original sql given by the boss:
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);
There are nearly 150,000 pieces of data, and it is not a big problem to stud directly, but he requires batch execution. If there is a problem online, you can’t afford it, so you can take it apart and execute it, just as a study. So it was disassembled into 12 sql, so that the verification can be performed in grayscale, backed up the online table, and then tested it on the local machine, let the big guy look at the sql, the big guy said it was very clever, and let him execute one Check it out, it feels like chickens are pecking at each other, but this is full of ritual, and you have to be in awe of the line.
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, 19 1554, 191522, 191521)) b set status= 2 where a.id=b.id;
In fact, a local shuttle only takes more than 200 ms. Later, I tried the other two writing methods and compared the effects. I found that the direct stud is the fastest, haha 
# 1. Native 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, 19152 1, 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. Subquery (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, 1915 22, 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 | +----+-------------+-------------+------------+--- -----+---------------+---------+---------+------- --------------------+--------+----------+--------- ----+ # 3. Subquery (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,500 74, 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. Use 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, 19155 4, 191522, 191521, 162099, 125372,125131,125125,125106,124854,124852,56683,56527,56442,56129,55889,55888,55575,54744,54218,53664,500 74, 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 | +----+-------------+-------+------------+-------+ ---------------+---------+---------+-------------- ----+--------+----------+-------------+
explain execution plan and actual execution effect:
refer to:
This article is transferred from: https://chegva.com/5744.html
This site is only for collection, and the copyright belongs to the original author.