Postgresql刪除數據庫表中重復數據的幾種方法詳解

一直使用Postgresql數據庫,有一張表是這樣的:

DROP TABLE IF EXISTS "public"."devicedata";
CREATE TABLE "public"."devicedata" (
  "Id" varchar(200) COLLATE "pg_catalog"."default" NOT NULL,
  "DeviceId" varchar(200) COLLATE "pg_catalog"."default",
  "Timestamp" int8,
  "DataArray" float4[]
)

CREATE INDEX "timeIndex" ON "public"."devicedata" USING btree (
  "Timestamp" "pg_catalog"."int8_ops" DESC NULLS LAST,
  "DeviceId" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

ALTER TABLE "public"."devicedata" ADD CONSTRAINT "devicedata_pkey" PRIMARY KEY ("Id");

主鍵為Id,是通過程序生成的GUID,隨著數據表的越來越大(70w),即便我建立瞭索引,查詢效率依然不樂觀。

使用GUID作為數據庫的主鍵對分佈式應用比較友好,但是不利於數據的插入,可以使用類似ABP的方法生成連續的GUID解決這個問題。

為瞭進行優化,計劃使用DeviceId與Timestamp作為主鍵,由於主鍵會自動建立索引,使用這兩個字段查詢的時候,查詢效率可以有很大的提升。不過,由於數據庫的插入瞭很多的重復數據,直接切換主鍵不可行,需要先剔除重復數據。

使用group by

數據量小的時候適用。對於我這個70w的數據,查詢運行瞭半個多小時也無法完成。

DELETE FROM "DeviceData"
WHERE "Id"
NOT IN (
SELECT max("Id")
FROM "DeviceData_temp"
GROUP BY "DeviceId", "Timestamp"
);

使用DISTINCT

建立一張新表然後插入數據,或者使用select into語句。

SELECT DISTINCT "Timestamp", "DeviceId"
INTO "DeviceData_temp"
FROM "DeviceData";
-- 刪除原表
DROP TABLE "DeviceData";
-- 將新表重命名
ALTER TABLE "DeviceData_temp" RENAME TO "DeviceData";

不過這個問題也非常大,很明顯,未來的表,是不需要Id列的,但是DataArray也沒有瞭,沒有意義。

如果SELECT DISTINCT "Timestamp", "DeviceId", "DataArray",那麼可能出現"Timestamp", "DeviceId"重復的現象。

使用ON CONFLICT

如果我們直接建立新表格,設置好新的主鍵,然後插入數據,如果重復瞭就跳過不就行瞭?但是使用select into是不行瞭,重復的數據會導致語句執行中斷。需要借助upsert(on conflict)方法。

INSERT INTO "DeviceData_temp"
SELECT * FROM "DeviceData"
on conflict("DeviceId", "Timestamp") DO NOTHING;
-- 刪除原表
DROP TABLE "DeviceData";
-- 將新表重命名
ALTER TABLE "DeviceData_temp" RENAME TO "DeviceData";

執行不到100s就完成瞭,刪除瞭許多重復數據。

以上就是這篇文章的全部內容瞭,希望本文的內容對大傢的學習或者工作具有一定的參考學習價值,謝謝大傢對WalkonNet的支持。如果你想瞭解更多相關內容請查看下面相關鏈接

推薦閱讀: