主页 / dba / 问题 / 250518
Accepted
Sebastien Diot
Asked: 2019-10-09 02:00:45 +0800 CST

通过主键查找一行，当你不知道它在哪个表中？

  • 772

我是负责专有 OODBMS 的（非官方）DBA。管理层希望我们转向 Postgres，以降低许可成本。此举应该是渐进的，因此我们也应该保持与 OODBMS 中相同的数据结构。幸运的是，有了对 ARRAY 和表继承的支持，我们可以在 Postgres 中创建一个完全相同的模式。所有表都将使用 bigint 作为主键，并且都（间接）继承自同一个基表。

最大的问题是：我们的 bigint 键在所有表中都是（而且必须是）唯一的，我们必须能够根据主键快速加载一组行，而无需知道它们在哪个表中。这些行将分布在所有表格中。这里主要关注的是速度，而不是磁盘或内存使用。

换句话说，我们需要的是跨所有表的唯一索引。AFAIK，这在 Postgres 中是不可能的。“下一个最佳”选项是什么？我对任何解决方案都持开放态度，包括使用甚至编码一些“Postgres 扩展”。

为了给出一些关于实际数据库的提示，我们讨论的是 300 个表、130M 行和大约 300GB 的大小（OODBMS 大小）。

performance postgresql-performance

1 个回答

  1. Best Answer

    所有表都将使用 bigint 作为主键，并且所有（间接）继承自同一个基表

    我不确定我喜欢这个“所有表都继承自基表”，但考虑到这一点，它听起来对 Postgres 可行：

    要为所有表生成主键，请创建一个序列：

    create sequence one_for_all as bigint;

    使用该序列创建基表以生成值：

    create table base (id integer primary key default nextval('one_for_all'));

    请注意，不会对子表强制执行主键！

    然后创建子表：

    create table t1 (t1_data integer, primary key (id)) inherits (base);
create table t2 (t2_data integer, primary key (id)) inherits (base);

    如果现在插入到子表中，序列将用于生成 ID：

    insert into t1 (t1_data) values (100);
insert into t2 (t2_data) values (200);

    要查找行位于哪个表中，请从基表中选择并包括tableoid列，该列标识行所在的实际表：

    select b.*, tableoid::regclass as actual_table
from base b
where id = 42;

    您仍然需要另一个查询来返回子表的整行。

    在线示例：https ://dbfiddle.uk/?rdbms=postgres_11&fiddle=4305e86b996e7a94c24faed733257232

    另一种不需要继承的方法是生成对表名进行编码的 ID 值（不寒而栗）。

    沿线的东西：

    create sequence one_for_all as bigint;
create table lookup (table_name text primary key, code serial unique);
insert into lookup (table_name) values ('base'), ('t1'), ('t2');

create function get_id(p_tablename text)
  returns bigint
as
$$
  select nextval('one_for_all') * 1000 + code
  from lookup
  where table_name = p_tablename;
$$
language sql;

create function get_tablename(p_id bigint)
  returns text
as
$$
  select table_name
  from lookup
  where code = p_id % 1000;  
$$
language sql;

    通过将序列中的值乘以 1000，我们基本上可以使用较低的 3 位数字为每个表编码一个唯一的数字。为了能够查找这些数字，我们需要那个额外的表。

    请注意，如果查找表不包含所有表，这将失败得很惨！

    然后不使用nextval()use，get_id()函数：

    create table base (id integer primary key default get_id('base'));
create table t1 (id integer primary key default get_id('t1'), some_value integer);
create table t2 (id integer primary key default get_id('t2'), some_data text);

    然后你可以这样做：

    insert into t1 (some_value) values (42);
insert into t2 (some_data) values ('foo');
insert into t1 (some_value) values (117);
insert into t2 (some_data) values ('bar');

    所以 t2 中的行现在的 ID 为 2003 和 4003。该函数get_tablename()可用于检索 ID 所属的表名：

    select get_tablename(2003);

get_tablename
-------------
t2

    这在查找表名方面肯定更快，并且不会带来巨大的继承树的包袱。所以在性能方面它可能更快。然而，这将成为维护的噩梦。

    每当创建或删除表时，都可以通过事件触发器来填充查找表。

    如果您可以更改您的应用程序以支持 varchar 主键，而不是 bigint（并且您可以接受稍高的存储要求），您还可以将 ID 生成为仅包含表名的字符串：

    create function get_id(p_tablename text)
  returns text
as
$$
  select concat(nextval('one_for_all'), '_', p_tablename)
$$
language sql;

    不过，排序和范围查询之类的事情会很复杂。

    • 5

相关问题