mysql批量更新方法

目录

方法一 replace into 批量更新

方法二 insert into 批量更新

方法三 临时表 批量更新

方法四 case when 批量更新

方法一 replace into 批量更新

   原理：replace into table (col1,col2) values (x1,x2), 操作本质是对重复的记录先delete 后insert

 缺点：1、如果更新的字段不全会将缺失的字段置为缺省值

            2、如果表中存在多个唯一键约束，那么会删除多行，造成数据丢失

            3、当有并发场景时，容易造成锁表，replace into流程死锁分析：http://mysql.taobao.org/monthly/2015/03/01/

字段缺失实验：

1.  
   CREATE TABLE `t_insert` (
2.  
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3.  
     `i1` int(11) NOT NULL DEFAULT '0',
4.  
     `i2` int(11) NOT NULL DEFAULT '0',
5.  
     PRIMARY KEY (`id`),
6.  
     UNIQUE KEY `idx_i1` (`i1`),
7.  
     UNIQUE KEY `idx_i2` (`i2`) USING BTREE
8.  
   ) ENGINE=InnoDB AUTO_INCREMENT=1;
9.  
    
10.  
    mysql> select * from t_insert limit 4;
11.  
    ----- ----- -----
12.  
    | id | i1 | i2 |
13.  
    ----- ----- -----
14.  
    | 107 | 102 | 211 |
15.  
    | 109 | 103 | 203 |
16.  
    | 111 | 10 | 99 |
17.  
    | 113 | 105 | 205 |
18.  
    ----- ----- -----
19.  
     
20.  
    4 rows in set (0.00 sec)
21.  
     
22.  
    没有设置i2的值
23.  
    mysql> replace into t_insert(id,i1) values (107,109);
24.  
    Query OK, 2 rows affected (0.04 sec)
25.  
     
26.  
    mysql> select * from t_insert limit 4;
27.  
    ----- ----- -----
28.  
    | id | i1 | i2 |
29.  
    ----- ----- -----
30.  
    | 107 | 109 | 0 | i2默认值为0
31.  
    | 109 | 103 | 203 |
32.  
    | 111 | 10 | 99 |
33.  
    | 113 | 105 | 205 |
34.  
    ----- ----- -----
35.  
    4 rows in set (0.00 sec)

 丢失数据实验：

1.  
   mysql> describe t_insert;
2.  
   ------- ------------------ ------ ----- --------- ----------------
3.  
   | Field | Type | Null | Key | Default | Extra |
4.  
   ------- ------------------ ------ ----- --------- ----------------
5.  
   | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
6.  
   | i1 | int(11) | NO | UNI | 0 | |
7.  
   | i2 | int(11) | NO | UNI | 0 | |
8.  
   ------- ------------------ ------ ----- --------- ----------------
9.  
   3 rows in set (0.12 sec)
10.  
     
11.  
    mysql> select * from t_insert;
12.  
    ----- ----- -----
13.  
    | id | i1 | i2 |
14.  
    ----- ----- -----
15.  
    | 107 | 109 | 0 |
16.  
    | 109 | 103 | 203 |
17.  
    | 111 | 10 | 99 |
18.  
    | 113 | 105 | 205 |
19.  
    ----- ----- -----
20.  
    4 rows in set (0.00 sec)
21.  
     
22.  
    mysql> replace into t_insert(id,i1,i2) values (107,103,205);
23.  
    Query OK, 4 rows affected (0.03 sec)
24.  
     
25.  
    插入的数据存在3个冲突键，删掉3行，插入一行。造成数据丢失
26.  
    mysql> select * from t_insert;
27.  
    ----- ----- -----
28.  
    | id | i1 | i2 |
29.  
    ----- ----- -----
30.  
    | 107 | 103 | 205 |
31.  
    | 111 | 10 | 99 |
32.  
    ----- ----- -----
33.  
    2 rows in set (0.00 sec)

不指定id，无论是否有唯一键重复自增值就会自增实验：

1.  
   mysql> select * from t_insert;
2.  
   ---- ---- ----
3.  
   | id | i1 | i2 |
4.  
   ---- ---- ----
5.  
   | 4 | 1 | 1 |
6.  
   ---- ---- ----
7.  
   1 row in set (0.01 sec)
8.  
    
9.  
   mysql> SELECT Auto_increment FROM information_schema.TABLES WHERE Table_Schema='hi' AND table_name='t_insert';
10.  
    ----------------
11.  
    | Auto_increment |
12.  
    ----------------
13.  
    | 5 |
14.  
    ----------------
15.  
    1 row in set (0.00 sec)
16.  
     
17.  
    插入重复键1，1
18.  
    mysql> replace into t_insert(i1,i2) values(1,1) ;
19.  
    Query OK, 2 rows affected (0.01 sec)
20.  
     
21.  
    mysql> select * from t_insert;
22.  
    ---- ---- ----
23.  
    | id | i1 | i2 |
24.  
    ---- ---- ----
25.  
    | 5 | 1 | 1 |
26.  
    ---- ---- ----
27.  
    1 rows in set (0.01 sec)
28.  
     
29.  
    mysql> SELECT Auto_increment FROM information_schema.TABLES WHERE Table_Schema='hi' AND table_name='t_insert';
30.  
    ----------------
31.  
    | Auto_increment |
32.  
    ----------------
33.  
    | 6 |
34.  
    ----------------
35.  
    1 row in set (0.00 sec)
36.  
     
37.  
    插入新值
38.  
    mysql> replace into t_insert(i1,i2) values(2,2) ;
39.  
    Query OK, 1 row affected (0.01 sec)
40.  
     
41.  
    mysql> SELECT Auto_increment FROM information_schema.TABLES WHERE Table_Schema='hi' AND table_name='t_insert';
42.  
    ----------------
43.  
    | Auto_increment |
44.  
    ----------------
45.  
    | 7 |
46.  
    ----------------
47.  
    1 row in set (0.00 sec)
48.  
     
49.  
    mysql> replace into t_insert(i1,i2) values (110,220);
50.  
    Query OK, 2 rows affected (0.13 sec)

分析死锁前先介绍一些Mysql锁相关知识

死锁实验：

本地实验批量更新数据为1k,1w,10w下的更新时间

1k

1w

10w

方法二 insert into 批量更新

   原理：insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

  缺点：  1、注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个

缺点1实验：

1.  
   mysql> select * from t_insert;
2.  
   ----- ----- -----
3.  
   | id | i1 | i2 |
4.  
   ----- ----- -----
5.  
   | 105 | 101 | 209 |
6.  
   | 107 | 102 | 223 |
7.  
   | 123 | 103 | 227 |
8.  
   | 129 | 104 | 226 |
9.  
   ----- ----- -----
10.  
    4 rows in set (0.00 sec)
11.  
     
12.  
    插入130，102，202 但是存在102冲突，所以这里只更新107这一行
13.  
    mysql> INSERT INTO t_insert ( id, i1, i2 ) VALUES (130,102,202) ON DUPLICATE KEY UPDATE i1 = VALUES ( i1 ), i2 = VALUES ( i2 );
14.  
    Query OK, 2 rows affected (0.09 sec)
15.  
     
16.  
    mysql> select * from t_insert;
17.  
    ----- ----- -----
18.  
    | id | i1 | i2 |
19.  
    ----- ----- -----
20.  
    | 105 | 101 | 209 |
21.  
    | 107 | 102 | 202 |
22.  
    | 123 | 103 | 227 |
23.  
    | 129 | 104 | 226 |
24.  
    ----- ----- -----
25.  
    4 rows in set (0.00 sec)

 死锁分析：

步骤一：设置Mysql参数，开启加锁输出

               set GLOBAL innodb_status_output_locks=ON;
               set GLOBAL innodb_status_output=ON;

步骤二：依次执行以下session中sql

session1:

1.  
   session1:
2.  
   mysql> CREATE TABLE `test` (
3.  
   `id` int(11) NOT NULL AUTO_INCREMENT,
4.  
   `alias` int(11) NOT NULL,
5.  
   `age` int(11) NOT NULL DEFAULT '0',
6.  
   PRIMARY KEY (`id`),
7.  
   UNIQUE KEY `u_idx_alias` (`alias`) USING BTREE
8.  
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
9.  
   mysql> insert into test(alias,age) values(1,1),(7,7),(15,15);
10.  
    mysql> begin;insert into test(alias,age) value(12,12) on duplicate key update age=9;
11.  
     
12.  
    等session2,3执行完后，再执行
13.  
    mysql> commit;
14.  
     
15.  
    session2:
16.  
    mysql>begin;insert into test(alias,age) value(13,13) on duplicate key update age=11;
17.  
     
18.  
    session3:
19.  
    mysql> begin;insert into test(alias,age) value(14,14) on duplicate key update age=11;
20.  
    Query OK, 0 rows affected (0.01 sec)
21.  
    session1 commit后：
22.  
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

本地实验批量更新数据为1k,1w,10w下的更新时间

1k

1w

10w

方法三 临时表 批量更新

原理：创建临时表，先更新临时表，然后从临时表中update

临时表生命周期只存在当前连接，连接关闭临时表会自动清除,不用临时表的时候最好显示删除掉，因为关闭连接/连接池的时候SQL Server才帮我们清除临时表了，但事实上连接池只有当程序重启的时候才清掉。在 binlog_format='row’的时候，临时表的操作不记录到 binlog

缺点：1、需要用户有临时表操作权限

           2、不显示清除掉则可能导致tempdb占用磁盘空间非常大

          3、设置 innodb_temp_data_file_path 选项，最好设定文件最大上限，超过上限时，需要生成临时表的SQL无法被执行

本地实验批量更新数据为1k,1w,10w下的更新时间

1k

1w

10w

方法四 case when 批量更新

本地实验批量更新数据为1k,1w,10w下的更新时间

1k

1w

10w

总结：

参考文章：40 | insert语句的锁为什么这么多？-极客时间  

                  https://blog.csdn.net/rlnLo2pNEfx9c/article/details/121326354

                 解决死锁之路 - 常见 SQL 语句的加锁分析 - aneasystone's blog

                  REPLACE语句死锁与官方修复剖析 - 知乎