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

比眉伴天荒 2024-03-17 21:09 106阅读 0赞

一直使用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][https_blog.csdn.net_wendred_article_details_84704042]  
[https://www.cnblogs.com/podolski/p/16745080.html][https_www.cnblogs.com_podolski_p_16745080.html]

[https_blog.csdn.net_wendred_article_details_84704042]: https://blog.csdn.net/wendred/article/details/84704042
[https_www.cnblogs.com_podolski_p_16745080.html]: https://www.cnblogs.com/podolski/p/16745080.html