今天工作中遇到了一个小bug,本来主键id应该会存在,结果却并不存在与数据库里面。debug的时候需要查找多张表,看该id是否存在,由于未来可能还存在这种需求,就写了个小脚本。达到的效果就是给出id,能快速得到该id存在于哪张表的哪个字段

背景

项目中存在很多张表, 几乎重要地方的主键均使用UUID,有关联的地方有的地方使用了外键,有的地方没有

思路

  1. Postgresql分层级db->schema->table->column,先得到所有类型为uuid的字段

    1
    2
    3
    4
    SELECT t.table_schema,c.table_name,c.column_name
    FROM information_schema.columns c
    left join information_schema.tables t on t.table_schema = c.table_schema and t.table_name = c.table_name
    where c.data_type = 'uuid' and t.table_type = 'BASE TABLE'

    需要注意的是查询的时候可以限制一下表类型,对于视图和物化视图可以忽略,一般它们都是基于基础表中得到的数据

  2. 对每一个得到的字段,构建出一个SQL语句得到uuid的值

    select distinct(column) from schema.table
    
  3. 将这些值写入到Redis中

    对于少量数据,可以很容易想到采用uuid->set的方式,记录每一个UUID存在于哪些字段中。但是当uuid的个数达到上千万级别的时候,该方式无疑会极大的浪费内存空间
    此时Bloom Filter数据结构就是一个极佳的形式,它拥有的特性是, 判断值是否已经存在于已经添加的数据中,如果判断结果是不存在,那么它就一定没有被加入,如果结果是存在,那么有一定几率是没有加入的(具体原理可以参照下方文档),查询效率极高,空间占用比上一种要小一些。因此,数据对应关系是这样的:column->uuid set。
    Bloom Filter在Redis免费版中并不是内置的,是作为4.0以上版本中的一个外部模块,需要我们编译后使用loadmodule指令挂载.

  4. 客户端查询

    写入Redis后需要查询的时候先keys得到所有的字段,对于每一个字段执行bf.exists查找该id是否存在于该字段中

小细节

即使单表一亿个字段,单表扫描查询,大概三五十秒也能读取到所有数据, 所以数据库读取性能并不需要太过担心,采用多个goroutine读取会更快,写入Redis就在遍历结果的时候写入就好了
写入cron里面,每天更新一次数据,redis最后使用rename使用新的数据替换掉旧的数据
客户端查询的时候得到所有的key后,也采用多goroutine同时查询.此时采用pipeline的形式应该更好,然而我不怎么会写~~
客户端连接的redis地址已经写死了,我使用了ssh端口转发ssh -NL 6378:127.0.0.1:6379 remote_machine,更近一步,使用tmuxinator管理tmux配置

源码如下 https://github.com/ficapy/pg_uuid_search

参考资料

rebloom-bloom-filter-datatype-redis