Mysql replace 与 insert on duplicate效率分析

导读

我们在向数据库里批量插入数据的时候，会遇到要将原有主键或者unique索引所在记录更新的情况，而如果没有主键或者unique索引冲突的时候，直接执行插入操作。

这种情况下，有三种方式执行：

直接

直接每条select, 判断，　然后insert，毫无疑问，这是最笨的方法了，不断的查询判断，有主键或索引冲突，执行update,否则执行insert. 数据量稍微大一点这种方式就不行了。

稍微高级一些的方式。

replace

这是mysql自身的一个语法，使用 replace 的时候。其语法为：

replace into tablename (f1, f2, f3) values(vf1, vf2, vf3),(vvf1, vvf2, vvf3)

这中语法会自动查询主键或索引冲突，如有冲突，他会先删除原有的数据记录，然后执行插入新的数据。

insert on duplicate key.

这也是一种方式，mysql的insert操作中也给了一种方式，语法如下：

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

在insert时判断是否已有主键或索引重复，如果有，一句update后面的表达式执行更新，否则，执行插入。

第一种方式不说了，replace和insert　on duplicate key这两种方式，哪中效率更高一些呢，毕竟，我们的执行sql，追求的就是高效。

分析

在最终实践结果中,得到接过如下：
在数据库数据量很少的时候，　这两种方式都很快，无论是直接的插入还是有冲突时的更新，都不错，但在数据库表的内容数量比较大(如百万级)的时候，两种方式就不太一样了，

首先是直接的插入操作，两种的插入效率都略低，　比如直接向表里插入１０００条数据(百万级的表(innodb引擎))，二者都差不多需要５，６甚至十几秒。究其原因，我的主机性能是一方面，但在向大数据表批量插入数据的时候，每次的插入都要维护索引的，　索引固然可以提高查询的效率，但在更新表尤其是大表的时候，索引就成了一个不得不考虑的问题了。

其次是更新表，这里的更新的时候是带主键值的(因为我是从另一个表获取数据再插入，要求主键不能变)　同样直接更新1000条数据，　replace的操作要比insert on duplicate的操作低太多太多，　当insert瞬间完成(感觉)的时候，replace要７，８s,　replace慢的原因我是知道的,在更新数据的时候，要先删除旧的，然后插入新的，在这个过程中，还要重新维护索引，所以速度慢,但为何insert　on duplicate的更新却那么快呢。　在向老大请教后，终于知道，insert on duplicate 的更新操作虽然也会更新数据，但其对主键的索引却不会有改变，也就是说，insert　on duplicate　更新对主键索引没有影响.因此对索引的维护成本就低了一些(如果更新的字段不包括主键，那就要另说了)。

题外话：

在向数据量大的表里批量插入更新数据的时候，随着插入的数量越来越多，会导致越来越慢，这种情况下,因为我们用的innodb表，可以开启事务， 每次批量执行一批数据更新后提交， 再重新开事务处理下批数据，这样会有效增加效率

还有说明一下：　当我们执行数据库的插入和更新操作很慢的时候，不仅仅是语句，主机性能也很重要，　比如内存和cpu，　如果是虚拟机要相应适当调整，　如果在各种优化了之后效率还是很低，　但cpu和内存的占用却不高，那么就很可能是磁盘的IO性能了，这也会导致数据的更新速度慢。

PS: 数据库菜鸟一个，　所知有限，　如有纰漏，　还请指教