10W資料匯入該如何與庫中資料去重？

救苦救难韩天尊發表於2024-08-16

使用的是PostgreSQL

在做大資料量（十萬級）匯入時，某些欄位和資料庫表裡資料（千萬級）重複的需要排除掉，把表資料查詢出來用程式的方式判斷去重效率很低，於是考慮用臨時表。

  1. 先把新資料插入到臨時表裡，臨時表結構和原始表一致。
  2. 用SQL的方式把不重複的資料DataA查詢出來。
  3. 把DataA插入到原始表裡。

因為不重複的資料我還要做一些其他的處理，所以查出來DataA，若不需做特殊處理可直接使用 insert into select 的方式將第2步的資料插入到原始表，然後清空臨時表

第2步有兩種方式，一種是用 not exists 的方式，如

SELECT
	* 
FROM
	tableTemp t1 
WHERE
	NOT EXISTS ( SELECT 1 FROM tableName WHERE columnA = t1.columnA AND columnB = t1.columnB )

第二種方式是用 left join

SELECT
	* 
FROM
	tableTemp t1
	LEFT JOIN tableName t2 ON t2.columnA = t1.columnA AND t2.columnB = t2.columnB 
WHERE
	t2.columnA IS NULL

經測試（每個人的表結構和索引各有不同，我這裡僅僅提供參考）

臨時表資料量少時， not exists 用時較少，隨著資料量越多用時越久。當資料達到10w時，用時25s。

臨時表資料量少時，left join 用時30s，隨著資料量越多變化不大，當資料達到10w時，用時40s。

結論1：單表去重時，只要匯入的資料量不是特別特別大（20w級以上），優先使用 not exists 做去重。

但還有一種情況，就是需要對兩個表做去重。

例如

SELECT
	* 
FROM
	tableTemp t1 
WHERE
	NOT EXISTS ( 
	SELECT 1 FROM tableNameA WHERE columnA = t1.columnA AND columnB = t1.columnB 
	union all
	select 1 from tableNameB WHERE columnA = t1.columnA AND columnB = t1.columnB 
	);

SELECT
	* 
FROM
	tableTemp t1
	LEFT JOIN tableNameA t2 ON t2.columnA = t1.columnA AND t2.columnB = t1.columnB 
	LEFT JOIN tableNameB t3 ON t3.columnA = t1.columnA AND t3.columnB = t1.columnB 
WHERE
	t2.columnA IS NULL
	AND t3.columnA IS NULL

這種情況下，

臨時表資料少時，not exists 用時較少，隨著資料量越多用時越久。當資料達到10w時，用時150s！！！。

臨時表資料少時，left join 用時仍然是30s，隨著資料量越多用時越久。當資料達到10w時，用時仍然是40s。

兩者在資料量為3w時，用時不相上下

結論2：雙表去重時，當匯入的資料在3w以下時，用 not exists，在3w以上時，用 left join。

相關文章