有趣的地方

有趣的地方

有大批量的数据导入到数据库,规则是数据库有相应主键的就update没有就insert怎么做效率快

理此类具有条件的 “upsert”(更新或插入)操作时,您想要最小化对数据库的访问次数并使用高效的SQL逻辑。以下是几个数据库平台通常使用的方法:

MySQL:

在 MySQL 中,可以使用 INSERT ... ON DUPLICATE KEY UPDATE 语句。

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

如果主键或唯一索引触发了重复冲突,则执行 UPDATE

PostgreSQL:

PostgreSQL 可以使用 ON CONFLICT 子句进行类似操作。

sql INSERT INTO table_name (a, b, c) VALUES (1, 2, 3) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c; 

如果字段 a 出现冲突,则会进行更新。 ### SQL Server: SQL Server 使用 MERGE 语句来整合逻辑。

MERGE INTO table_name USING ( VALUES (1, 2, 3) ) AS incoming (a, b, c) ON table_name.a = incoming.a WHEN MATCHED THEN UPDATE SET b = incoming.b, c = incoming.c WHEN NOT MATCHED THEN INSERT (a, b, c) VALUES (incoming.a, incoming.b, incoming.c); 

这将根据是否存在匹配的 a 值来选择更新现有行或插入新行。

Oracle:

Oracle 使用 MERGE 类似 SQL Server。

MERGE INTO table_name USING dual ON (table_name.a = 1) WHEN MATCHED THEN UPDATE SET b = 2, c = 3 WHEN NOT MATCHED THEN INSERT (a, b, c) VALUES (1, 2, 3); 

针对大批量数据,您可以使用上面的语句模式结合批量操作技术,例如先前讨论的 MyBatis 配置批量操作或将数据分割为批次进行处理。在 MyBatis 中使用 foreach 批量操作时,可以在单个 insert 语句内嵌入相应的“upsert”逻辑。

性能优化:

  • 先批量查询:首先对所有导入数据执行一次查询匹配主键,将匹配的数据和未匹配的数据分开,分别进行批量的update和insert。
  • 使用缓存或临时表:根据数据量大小,考虑使用缓存技术(如 Redis)或临时表来批量处理数据,减少对主表的操作负担。
  • 索引优化:确保数据库表上的主键或唯一索引是针对此类 “upsert” 操作优化的,以缩短搜索时间。
  • 减少事务提交的频率:在上文所提的 MyBatis 执行器类型设置为 BATCH 的情况下,尽量在合适的量级后提交事务。 每次操作都应根据数据库服务器的实际情况和硬件能力来调整,实现最佳性能。同时也要注意适当的错误处理机制,确保数据的一致性和完整性。

使用临时表怎么实现

使用临时表来进行大批量的 “upsert” 操作(更新已存在的记录或插入新记录)可以分解成以下步骤:

  1. 创建临时表: 创建一个临时表,其结构与目标表相同或包含你想要插入/更新的字段。
CREATE TEMPORARY TABLE temp_table_name LIKE target_table_name; 
  1. 批量插入数据到临时表: 将所有要导入的数据批量插入到创建的临时表中。
INSERT INTO temp_table_name (col1, col2, ...) VALUES (val1a, val2a, ...), (val1b, val2b, ...), ...; 
  1. 锁定目标表(可选): 如果数据的一致性非常关键,并且可以承受锁定表的短暂时期,考虑在进行操作前锁定目标表。
LOCK TABLES target_table_name WRITE;
  1. 更新目标表中匹配临时表的行: 将目标表中那些在临时表中有匹配的记录进行更新。
UPDATE target_table_name INNER JOIN temp_table_name ON target_table_name.primary_key = temp_table_name.primary_key SET target_table_name.col1 = temp_table_name.col1, target_table_name.col2 = temp_table_name.col2, ...; 
  1. 将不存在于目标表中的记录插入到目标表: 将临时表中的新记录插入到目标表中。
INSERT INTO target_table_name (primary_key, col1, col2, ...) SELECT primary_key, col1, col2, ... FROM temp_table_name WHERE NOT EXISTS (SELECT 1 FROM target_table_name WHERE target_table_name.primary_key = temp_table_name.primary_key); 
  1. 释放表锁(如果前面对表进行了锁定):
UNLOCK TABLES; 
  1. 删除或清空临时表(如果是持久的临时表):
DROP TEMPORARY TABLE IF EXISTS temp_table_name; -- 或者 TRUNCATE TABLE temp_table_name; 

这种方法首先同步已存在的记录,然后添加新记录,这样可以避免主键或唯一约束冲突。但是,这个过程可能会根据数据库的负载和表的大小而导致性能下降,特别是如果涉及到大量数据时。因此,该操作应在系统负载较低的时候执行,并且需要适当的错误处理和事务管理以确保数据一致性。 使用临时表策略可以有效地将 “upsert” 操作的资源消耗分散开来,并且通过先处理数据然后一次性操作,减少了数据库的I/O开销。它还节省了在每条记录检查时索引查找的成本,因为它将这些操作汇总到大批处理中。不过,请根据实际情况及数据库特性来处理,可能需要调整语句以适应不同的数据库系统。

发表评论:

Powered By Z-BlogPHP 1.7.3

© 2018-2020 有趣的地方 粤ICP备18140861号-1 网站地图