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

凌寒ᨐ舞

已于 2024-07-17 15:26:52 修改

阅读量684

点赞数 14

分类专栏：面试文章标签：数据库

于 2024-04-02 22:59:55 首次发布

本文链接：https://blog.csdn.net/qq_37427142/article/details/137296587

版权

面试专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了如何在MySQL、PostgreSQL、SQLServer和Oracle等数据库中执行高效upsert操作，包括使用INSERT...ONDUPLICATEKEYUPDATE、ONCONFLICT、MERGE以及MyBatis的批量处理方法。同时探讨了性能优化策略，如批量查询、缓存使用、索引优化和减少事务提交频率等。

摘要由CSDN通过智能技术生成

理此类具有条件的 “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” 操作（更新已存在的记录或插入新记录）可以分解成以下步骤：

创建临时表：创建一个临时表，其结构与目标表相同或包含你想要插入/更新的字段。

CREATE TEMPORARY TABLE temp_table_name LIKE target_table_name;

批量插入数据到临时表：将所有要导入的数据批量插入到创建的临时表中。

INSERT INTO temp_table_name (col1, col2, ...) VALUES (val1a, val2a, ...), (val1b, val2b, ...), ...;

锁定目标表（可选）：如果数据的一致性非常关键，并且可以承受锁定表的短暂时期，考虑在进行操作前锁定目标表。

LOCK TABLES target_table_name WRITE;

更新目标表中匹配临时表的行：将目标表中那些在临时表中有匹配的记录进行更新。

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, ...;

将不存在于目标表中的记录插入到目标表：将临时表中的新记录插入到目标表中。

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);

释放表锁（如果前面对表进行了锁定）：

UNLOCK TABLES;

删除或清空临时表（如果是持久的临时表）：

DROP TEMPORARY TABLE IF EXISTS temp_table_name; -- 或者 TRUNCATE TABLE temp_table_name;

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