result = select * from table;
if result = 0
insert the record into table;
else
update the record;
这样的操作乍一看没有什么问题,但是仔细分析分析,还是有些瓶颈的,目前来看,我能分析到的瓶颈有两个,
其一:
每次要执行2个SQL,效率比较差;
其二:
当我们在高并发的情况下跑这条语句,如果程序崩溃,不能保证操作的原子性。
基于这个问题,同事提出了insert...on duplicate key update这个办法,而我的第一反应是replace方法,因为我之前从来没用过insert...on duplicate key这个语法,所以专门把这两个语法研究了一下,下面简单分析下这两个语法:
01
insert...on duplicate key方法
首先我们看看这个语法的具体写法,我简单写一个例子,内容大致如下:
1、首先创建一个包含id,name,age的表,其中id是主键;
2、在这个表中插入一条id=1的记录;
3、使用insert...on duplicate key update语法插入一条id=2的记录;
4、使用同样的语法更新id=1的记录;
mysql 23:12:32>>create table test_1(
-> id int primary key auto_increment,
-> name varchar(20),
-> age int not null
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.08 sec)
mysql 23:13:26>>insert into test_1 values (1,'yyz',16);
Query OK, 1 row affected (0.01 sec)
mysql 23:13:58>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | yyz | 16 |
+----+------+-----+
1 row in set (0.00 sec)
mysql 23:14:36>>insert into test_1 (id,name,age) values (2,'yyz',18) on duplicate key update age=18;
Query OK, 1 row affected (0.01 sec)
mysql 23:15:08>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | yyz | 16 |
| 2 | yyz | 18 |
+----+------+-----+
2 rows in set (0.00 sec)
mysql 23:15:17>>insert into test_1 (id,name,age) values (1,'yyz',18) on duplicate key update age=18;
Query OK, 2 rows affected (0.00 sec)
mysql 23:15:28>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | yyz | 18 |
| 2 | yyz | 18 |
+----+------+-----+
2 rows in set (0.00 sec)
我们可以看到,当主键存在的时候,相当对id=1的那条记录执行了:
update test_1 set age=18 where id=1;
这就是insert...on duplicate key update语法的作用,可以分析到,当发生主键冲突的时候,可以直接进行update操作,这个update操作里面可以更新任意想要更新的列;而没有主键冲突的时候,相当于对这个表进行了一次插入操作。
使用这个方法可以替代业务方的那种2个SQL的写法,也能够解决数据的原子性问题。
需要注意的是,这个语法不是SQL的通用语法,而是MySQL特有的一种语法。还有一点需要注意,当表中存在两个列都冲突的情况下(例如age也是一个unique索引,id和age同时冲突的情况),只会更新其中的一条记录,而且,当出现多个unique索引的时候,对于SQL语法where条件的先后顺序会变得很敏感,不同的顺序修改的记录行也不相同(这需要了解索引部分的知识)。
简单的分析这个语句,个人认为,在多个客户端同时执行这个SQL的时候,也就是高并发情况下,虽然解决了数据的原子性问题,但是这个语句会带来另外一个问题,那就是死锁,当我们执行这个语句的时候,这个语句会先在表test_1中找到是否存在id=1的行,因为id是主键,所以很快就定位到这一行上面。在对该现有的行加上S(共享锁)锁,然后返回这行数据给mysql,mysql执行duplicate后的update操作,先对该记录加上X(排他锁),接下来需要执行update操作。由于大量的连接都在执行这个操作,因此在抢夺行锁上产生了大量的竞争,因为行锁的分配也涉及了自旋锁。很多连接就卡在了自旋锁上面,白白的消耗cpu资源(这个我还没测试,但是想想应该也是这样)。
02
Replace操作
Replace语句。使用Replace插入一条记录时,如果不重复,Replace就和Insert的功能一样,如果有重复记录,Replace就使用新记录的值来替换原来的记录值。
使用REPLACE的最大好处就是可以将Delete和Insert合二为一,形成一个原子操作。这样就可以不必考虑在同时使用Delete和Insert时添加事务等复杂操作了。
在使用Replace时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则Replace就和Insert完全一样的。
在执行Replace后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了Delete删除这条记录,然后再记录用Insert来插入这条记录。
03
不同之处
有了上面的知识储备,这两条命令的不同之处就显而易见了,replace是删除记录,然后再重新insert,而insert...on duplicate key update是直接在该条记录上修改,所以二者的差别主要有以下两处:
1、当表中存在自增值的时候,如果表中存在某条记录,replace语法会导致自增值+1,而insert...on duplicate key update语法不会;
2、当表中的某些字段中包含默认值的时候,replace操作插入不完全字段的记录,会导致其他字段直接使用默认值,而insert...on duplicate key update操作会保留该条记录的原有值。