mysql 批量更新及效率对比.docx

MySQL 批量更新及效率对比 MySQL 批量更新是指在 MySQL 数据库中对大量数据进行更新的操作。这种操作对数据库的性能有着很大的影响，特别是在大规模数据更新时。下面我们将讨论 MySQL 批量更新的几种方法，并对其性能进行对比。 方法一：批量 Update 这种方法是最原始的批量更新方法，即对每一条记录进行单独的更新操作。例如： `update test_tbl set dr='2' where id=1;` 这种方法的性能非常差，因为它需要对每一条记录进行单独的更新操作，从而导致大量的 I/O 操作和锁的竞争。 方法二：Replace Into 或者 Insert Into ... On Duplicate Key Update 这种方法使用 Replace Into 或者 Insert Into 语句来实现批量更新。 Replace Into 语句是将新的记录插入到表中，如果记录已经存在，则更新该记录。例如： `replace into test_tbl (id, dr) values (1, '2'), (2, '3'), ... (x, 'y');` Insert Into 语句类似，但是在更新记录时，只更新重复的记录，不会改变其他字段。例如： `insert into test_tbl (id, dr) values (1, '2'), (2, '3'), ... (x, 'y') on duplicate key update dr = values(dr);` 方法三：创建临时表，先更新临时表，然后从临时表中更新 这种方法首先创建一个临时表，然后将数据插入到临时表中，最后从临时表中更新到目标表。例如： `create temporary table tmp (id int(4) primary key, dr varchar(50));` `insert into tmp values (0, 'gone'), (1, 'xx'), ... (m, 'yy');` `update test_tbl, tmp set test_tbl.dr = tmp.dr where test_tbl.id = tmp.id;` 这种方法需要用户有 temporary 表的 create 权限。 性能测试结果 我们对上述三种方法进行了性能测试，测试结果如下： * 逐条 Update：real 0m15.557s, user 0m1.684s, sys 0m1.372s * Replace Into：real 0m1.394s, user 0m0.060s, sys 0m0.012s * Insert Into On Duplicate Key Update：real 0m1.474s, user 0m0.052s, sys 0m0.008s * 创建临时表，先更新临时表，然后从临时表中更新：real 0m0.643s, user 0m0.064s, sys 0m0.004s 从测试结果可以看出，使用 Replace Into 方法的性能最好。 Replace Into 和 Insert Into On Duplicate Key Update 的区别 Replace Into 和 Insert Into On Duplicate Key Update 两种方法都可以实现批量更新，但它们之间有着很大的区别。 Replace Into 操作本质是对重复的记录先 delete 后 insert，如果更新的字段不全会将缺失的字段置为缺省值。Insert Into 则是只 update 重复记录，不会改变其他字段。 使用详解 Insert Into 语句可以使用 ON DUPLICATE KEY UPDATE 子句来实现批量更新。例如： `INSERT INTO tbl_name (col_name1, col_name2, ...) VALUES (col_value1, col_value2, ...), (col_value1, col_value2, ...) ON DUPLICATE KEY UPDATE userName = VALUES(userName), userID = VALUES(userID);` 在上面的示例中，我们将 id、keyword、userName 和 userID 四个字段插入到 keywordtable 表中，如果记录已经存在，则更新对应的字段。 需要注意的是，Insert Into 语句要求有主键或唯一键，否则将无法使用 ON DUPLICATE KEY UPDATE 子句。