先说一下背景,之前我用的 pg 镜像是 postgres:11.18-bullseye,后来被升级成了 postgres:11.20-alpine3.18,这个造成的其中一个后果简而言之是 pg 对字符串的排序发生了变化,比如原先认为 A > B,现在则变成了 A < B。由此,就有可能破坏数据的唯一性约束。不仅如此,只要索引列包含字符串类型的字段,也会有问题。
为了方便继续描述,假设有个 test 表,结构如下
字段名 | 类型 | 说明 |
name | varchar(128) | 主键 |
age | int |
为了加深理解,假设这个表已经包含了重复数据,重复数据的 name = ‘A’,age 分别是 11 和 21。
思考一下如下这两条 SQL 的结果分别是什么
select name, count(*) from test group by name;
select name, count(*) from test where name = 'A' group by name;
第一条 SQL 语句大概率会返回 ('A', 2),第二条大概率会返回 ('A', 1)。造成这个现象的原因是第一条语句是全表扫描,第二条语句是走的索引。
如何解决
这种情况除了重建整个数据库的索引外,没有其他办法。但是在重建唯一索引之前,需要先删除重复的数据以维护唯一性约束。
这个问题貌似很简单,但要处理如下几个事情:
- 找出重复的数据
- 区分重复的数据
- 确定数据保留标准,这里假设保留新数据。
找出重复的数据已经有办法了,通过 group by 和 count(*) 可以找出哪些数据重复了。但是剩下两个事情呢,考虑下我们上文提的例子,如何为 test 表确定哪个数据更新呢?test 表可没有自增主键ID,也没有相关的时间字段。建表不规范让这个问题更难处理。
pg 的系统字段
让我们先了解下 pg 中的隐藏字段
- ctid。他记录的是行的物理存储信息,即使两条数据完全一样,ctid 也会不一样。但是 ctid 是易变的,比如更新之后,ctid 也会变化,所以需要考虑并发问题。
- xmin。他主要用在 MVCC 中,记录的是行被插入时的事务 ID,pg 通过比较行的事务 ID 和 当前事务 ID 的大小来判断该行对当前事务是否可见。所以事务 ID 一般情况下是递增的。但是也不能完全通过 xmin 的大小判断哪个行是先插入的,原因如下:
- MVCC 机制使得在更新记录的时候,其实是写入行的新版本,新版本行的 xmin 是会变的。
- 事务 ID 是32位的,在数据库长久运行后,事务ID会产生回卷,即从 3 开始继续递增,详见 防止事务ID回卷失败
解决方案
思虑再三,我没有发现一个普适的方案,能够解决如下这个问题,如果有的话,希望有个好心人告知我一下
在某个表已经破坏了唯一性约束后,若这个表没有 自增ID,创建时间,更新时间等可以标志记录写入先后顺序字段的话,很难去删除旧的重复数据
不过还好,我的工作并不是需要去证明什么,有时候也不需要完美的解决方案,在借助上面提到的 ctid 和 xmin 字段,再加上业务背景,我的解决方案是:
- 禁用索引
- 通过 group by 找出重复的记录
- 获取重复记录的 ctid 和 xmin,有些表还有 created_at 字段,一并获取
- 通过 order by created_at desc, xmin desc,获取到第一条记录,并认为它是最新的,保留它,通过 ctid 删除其他所有
话外
这个事情是由于升级 pg 版本导致的,对于升级这个事情,我的观点还是保守的,能不升就不升。不过这里还暴露出来了另外一个事情,建表的规范。如果我们的表都满足如下两个要求,这个事情就会简单很多:
- 使用自增主键。
- 带上 created_at, updated_at 字段
有时候挺奇怪的,可能每个研发,每个团队的关注点不一样,但是像这种建表的规范不应该是最基础的吗?我们的所有工作都是围绕数据存储展开,那最基本的表的结构,索引的创建,是不是也应该受到更多关注,成为各种评审的重中之重。前人挖坑,后人填坑,后人还有后来人。
在思考方案的过程中,我还想到过另外一个方案,简述如下:
- 通过 group by 找到重复的数据
- 通过走对应的唯一索引,查出当前能查到的那条记录。这条记录认为是最新的,删除其他重复记录
但是这个有如下几个问题:
- 如何确保一定走了唯一索引,有两种方式:
- pg 可以通过装个扩展,从而实现类似 MySQL 的索引提示功能
- 每次只查一条记录,这个基本上应该是走的索引,除非你的表数据量足够的小。除此之外,若重复记录较多,则效率也慢
- 即使走了唯一索引,一定会只返回一条记录吗(这个没实验过,等待一个好心人)。之所以有这个顾虑,是考虑到B+树的叶子结点,一般对应的是数据页,数据库页里包含多个数据记录。如果一次查询,返回的数据页里恰好同时包含了重复的记录呢,这个时候不知道是否会返回两条重复的记录。