删除数据库表中重复数据的方法

时间:2022-09-30 16:10:07

一直使用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就完成了,删除了许多重复数据。

参考文献

https://blog.csdn.net/wendred/article/details/84704042