存储过程插入、更新百万级数据如何优化

现在数据库中有200W的用户数据，来自第三方的用户数据700W（存在一张临时表里面），怎样快速的将这700W的数据同步到数据库：即如果用户已存在，则用第三方的用户信息更新数据里的相应的用户信息，如果不存在这直接插入数据库

用户信息通过phoneNo来标识

1、通过什么方式能快速查询出第三方用户数据在数据库中已经存在和不存在的记录？


2、怎样通过 存储过程快速的将这700W的数据同步到数据库？

22 个解决方案

#1

1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

#2

1、建议使用 merge into，可以加hint /*+append*/
即：merge /*+append*/ into ..
(不过据说加hint有bug，可以一试)。

2、如果是定期执行，可以将其放在数据库负载较低的晚上进行。

3、存储过程主要使用merge into就可以完成你的逻辑了。

#3

引用 1 楼 java3344520 的回复:

1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

现在用户的phoneNo（号码）是分号段的，
在95106010000-95106988888之间

怎样通过分批执行的方式用merge into 进行匹配更新和插入？

#4

引用 3 楼 lgq_0714 的回复:

引用 1 楼 java3344520 的回复:

1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行


现在用户的phoneNo（号码）是分号段的，
在95106010000-95106988888之间

怎样通过分批执行的方式用merge into 进行匹配更新……

是周期性同步吗？其实，一般数据库负载小的时段不用分批的。
分批只是为了减小事务的大小，减小undo段资源消耗，但是并不一定省时和提高效率。
如果资源充分的话，一次执行更快！

#5

引用 2 楼 tangren 的回复:

1、建议使用merge into，可以加hint /*+append*/
即：merge /*+append*/ into ..
(不过据说加hint有bug，可以一试)。

2、如果是定期执行，可以将其放在数据库负载较低的晚上进行。

3、存储过程主要使用merge into就可以完成你的逻辑了。

目前数据库中有100W的数据，第三方提供的数据是700W，

merge into能达到在20几分钟内完成这个数量级的数据的更新、插入吗？


谢谢！

#6

看看你的数据硬件性能，一般来说，如果索引恰当，完全是没有问题的。
你可以建立一个测试表试一试。

#7

引用 4 楼 tangren 的回复:

引用 3 楼 lgq_0714 的回复:

引用 1 楼 java3344520 的回复:

1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行


现在用户的phoneNo（号码）是分号段的，
在95106010000-95106988888之间

怎样通过分……

不是周期性同步，是一次性同步，要在半个小时之内完成数据的同步

#8

引用 6 楼 tangren 的回复:

看看你的数据硬件性能，一般来说，如果索引恰当，完全是没有问题的。
你可以建立一个测试表试一试。

服务器是单板的，硬件应该没什么问题

#9

感谢楼主分享

#10

这个跟机器硬件性能有关的。什么配置？小型机？普通PC服务器？
一次性同步更没有关系了，在业务少的时段更新就行了。

#11

服务器是单板的

700w的数据同步插入、更新也没问题？

ps:用户表有45个字段，会不会有什么影响？

#12

引用 1 楼 java3344520 的回复:

1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

不是周期性同步，是一次性同步，要在半个小时之内完成数据的同步
700万的数据，两张表的表结构一样不呢?
如果一样的话，直接使用empdp/impdp导过来。。。那效率不晓得有多高。。还搞什么临时表，存储过程呢？
如果表结构不一样，想办法把两变的表搞成一致的，再用empdp/impdp

#13

学习。。希望看到解决办法。谢谢各位了

#14

一個比較笨的方法，思路感覺還比較清晰，大致為：
1. 通過Full Join得到現有數據庫和第三方資料表中的所有數據插入到臨時表中
2. 對此臨時表操作，得到相當於Merge后的結果
    2.1. 更新已經存在的記錄為新的記錄
    2.2. 如果用戶不存在，用第三方數據填充
3. Truncate 現有數據庫中的所有記錄
5. 將第二步的結果插入到現有數據庫中

CREATE TABLE tbl_Test1
(
co11 INTEGER,
col2 INTEGER
);

CREATE TABLE tbl_Test2
(
co11 INTEGER,
col2 INTEGER
);

INSERT INTO tbl_test1 VALUES(1,11);
INSERT INTO tbl_test1 VALUES(2,12);
INSERT INTO tbl_test1 VALUES(3,13);

INSERT INTO tbl_test2 VALUES(1,111);
INSERT INTO tbl_test2 VALUES(2,222);
INSERT INTO tbl_test2 VALUES(5,555);


CREATE TABLE tbl_temp AS
SELECT A.Co11 A_Col1,A.Col2 A_Col2,B.Co11 B_Col1,B.Col2 B_Col2 
FROM tbl_test1 A
FULL JOIN tbl_test2 B
ON A.Co11 = B.Co11;

UPDATE tbl_temp
SET A_Col2 = B_Col2
WHERE A_Col1 = B_Col1 ;

UPDATE tbl_temp
SET A_Col1 = B_Col1,A_Col2 = B_Col2
WHERE A_col1 IS NULL; 

TRUNCATE TABLE tbl_test1;

INSERT INTO tbl_test1
SELECT A_COL1,A_COL2 FROM tbl_temp

#15

引用 12 楼 oraclefans_ 的回复:

引用 1 楼 java3344520 的回复:
1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

不是周期性同步，是一次性同步，要在半个小时之内完成数据的同步
700万的数据，两张表的表结构一样不呢?
如果一样的话，直接使用empdp/impdp导过来。。。那效率不晓……

两张表的结构是一样的，但是数据有交叉

#16

引用 14 楼 hanks_gao 的回复:

一個比較笨的方法，思路感覺還比較清晰，大致為：
1. 通過Full Join得到現有數據庫和第三方資料表中的所有數據插入到臨時表中
2. 對此臨時表操作，得到相當於Merge后的結果
    2.1. 更新已經存在的記錄為新的記錄
    2.2. 如果用戶不存在，用第三方數據填充
3. Truncate 現有數據庫中的所有記錄
5. 將第二步的結果插入到現有數據庫中

SQL ……

谢谢hanks_gao，我试试看，希望效率能够达到

#17

引用 15 楼 lgq_0714 的回复:

引用 12 楼 oraclefans_ 的回复:

引用 1 楼 java3344520 的回复:
1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

不是周期性同步，是一次性同步，要在半个小时之内完成数据的同步
700万的数据，两张表的表结构一样不呢?
如果一样的话……

有重复的记录，你的目标表有主键没有呢？如果有的话，直接导入，忽略错误就可以了，
如果没有直接导入进去，然后使用delete删除重复的记录就行了，也可以建立个唯一的索引，
然后再导入操作。。。建立唯一索引比较好。。呵呵。。

#18

引用 17 楼 oraclefans_ 的回复:

引用 15 楼 lgq_0714 的回复:
引用 12 楼 oraclefans_ 的回复:

引用 1 楼 java3344520 的回复:
1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

不是周期性同步，是一次性同步，要在半个小时之内完成数据的同步
700万……

目标表是有主键的，忽略错误之后数据库里面的数据是原有的数据呢还是第三方的数据呢？

如果还是原有的数据没有用第三方给的数据更新掉，这样数据还是有问题的

#19

引用 18 楼 lgq_0714 的回复:

引用 17 楼 oraclefans_ 的回复:

引用 15 楼 lgq_0714 的回复:
引用 12 楼 oraclefans_ 的回复:

引用 1 楼 java3344520 的回复:
1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

不是周期性同步，是……

第三方的数据去掉在你的数据库里重复的记录。

#20

引用 18 楼 lgq_0714 的回复:

引用 17 楼 oraclefans_ 的回复:

引用 15 楼 lgq_0714 的回复:
引用 12 楼 oraclefans_ 的回复:

引用 1 楼 java3344520 的回复:
1:用merge into 进行匹配更新和插入，
2： 开启并行，提高速度
3: 如果可以的话加NOLOGING 不写入日志
4：如果数据有规律的话，分批次执行

不是周期性同步，是……

你导入的时候是这样的，如果使用你第三方的数据导入到你的新表，如果你的库里有的数据，报错，现在你忽略，继续导入，如果没有的记录，导入进去，正好是你的要求

#21

如果更新只是用第三方数据更新现有数据表指定的几个字段应该怎么办呢？

#22

一個比較笨的方法，思路感覺還比較清晰，大致為：
1. 通過Full Join得到現有數據庫和第三方資料表中的所有數據插入到臨時表中
2. 對此臨時表操作，得到相當於Merge后的結果
  2.1. 更新已經存在的記錄為新的記錄
  2.2. 如果用戶不存在，用第三方數據填充
3. Truncate 現有數據庫中的所有記錄
5. 將第二步的結果插入到現有數據庫中