PostgreSQL – “多态表”vs 3表

概述我正在使用PostgreSQL 9.5(但升级可以说是9.6). 我有权限表： CREATE TABLE public.permissions( id integer NOT NULL DEFAULT nextval('permissions_id_seq'::regclass), item_id integer NOT NULL, item_type character varyi 我正在使用Postgresql 9.5(但升级可以说是9.6).

我有权限表：

CREATE table public.permissions(  ID integer NOT NulL DEFAulT nextval('permissions_ID_seq'::regclass),item_ID integer NOT NulL,item_type character varying NOT NulL,created_at timestamp without time zone NOT NulL,updated_at timestamp without time zone NOT NulL,CONSTRAINT permissions_pkey PRIMARY KEY (ID))-- skipPing indices declaration,but they would be present-- on item_ID,item_type

以及3对多对多关联的表

-companIEs_permissions(索引声明)

CREATE table public.companIEs_permissions(  ID integer NOT NulL DEFAulT nextval('companIEs_permissions_ID_seq'::regclass),company_ID integer,permission_ID integer,CONSTRAINT companIEs_permissions_pkey PRIMARY KEY (ID),CONSTRAINT fk_rails_462a923fa2 FOREIGN KEY (company_ID)      REFERENCES public.companIEs (ID) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_rails_9dd0d015b9 FOREIGN KEY (permission_ID)      REFERENCES public.permissions (ID) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION)CREATE INDEX index_companIEs_permissions_on_company_ID  ON public.companIEs_permissions  USING btree  (company_ID);CREATE INDEX index_companIEs_permissions_on_permission_ID  ON public.companIEs_permissions  USING btree  (permission_ID);CREATE UNIQUE INDEX index_companIEs_permissions_on_permission_ID_and_company_ID  ON public.companIEs_permissions  USING btree  (permission_ID,company_ID);

-permissions_user_groups(索引声明)

CREATE table public.permissions_user_groups(  ID integer NOT NulL DEFAulT nextval('permissions_user_groups_ID_seq'::regclass),user_group_ID integer,CONSTRAINT permissions_user_groups_pkey PRIMARY KEY (ID),CONSTRAINT fk_rails_c1743245ea FOREIGN KEY (permission_ID)      REFERENCES public.permissions (ID) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_rails_e966751863 FOREIGN KEY (user_group_ID)      REFERENCES public.user_groups (ID) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION)CREATE UNIQUE INDEX index_permissions_user_groups_on_permission_and_user_group  ON public.permissions_user_groups  USING btree  (permission_ID,user_group_ID);CREATE INDEX index_permissions_user_groups_on_permission_ID  ON public.permissions_user_groups  USING btree  (permission_ID);CREATE INDEX index_permissions_user_groups_on_user_group_ID  ON public.permissions_user_groups  USING btree  (user_group_ID);

-permissions_users(索引声明)

CREATE table public.permissions_users(  ID integer NOT NulL DEFAulT nextval('permissions_users_ID_seq'::regclass),user_ID integer,CONSTRAINT permissions_users_pkey PRIMARY KEY (ID),CONSTRAINT fk_rails_26289d56f4 FOREIGN KEY (user_ID)      REFERENCES public.users (ID) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_rails_7ac7e9f5ad FOREIGN KEY (permission_ID)      REFERENCES public.permissions (ID) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION)CREATE INDEX index_permissions_users_on_permission_ID  ON public.permissions_users  USING btree  (permission_ID);CREATE UNIQUE INDEX index_permissions_users_on_permission_ID_and_user_ID  ON public.permissions_users  USING btree  (permission_ID,user_ID);CREATE INDEX index_permissions_users_on_user_ID  ON public.permissions_users  USING btree  (user_ID);

我将不得不像这样运行SQL查询很多次：

SELECT"permissions".*,"permissions_users".*,"companIEs_permissions".*,"permissions_user_groups".* FROM "permissions"left OUTER JOIN  "permissions_users" ON "permissions_users"."permission_ID" = "permissions"."ID"left OUTER JOIN  "companIEs_permissions" ON "companIEs_permissions"."permission_ID" = "permissions"."ID"left OUTER JOIN  "permissions_user_groups" ON "permissions_user_groups"."permission_ID" = "permissions"."ID"WHERE  (companIEs_permissions.company_ID = <company_ID> OR  permissions_users.user_ID in (<user_IDs> OR NulL) OR  permissions_user_groups.user_group_ID IN (<user_group_IDs> OR NulL)) ANDpermissions.item_type = 'topic'

假设我们在其他表中有大约10000个权限和类似数量的记录.

我需要担心性能吗？

我的意思是…我有4个外部连接,它应该返回结果相当快(说< 200ms). 我正在考虑声明1个“多态”表,类似于：

CREATE table public.permissables(  ID integer NOT NulL DEFAulT nextval('permissables_ID_seq'::regclass),resource_ID integer NOT NulL,resource_type character varying NOT NulL,CONSTRAINT permissables_pkey PRIMARY KEY (ID))-- skipPing indices declaration,but they would be present

然后我可以运行这样的查询：

SELECT  permissions.*,permissables.*FROM permissionsleft OUTER JOIN  permissables ON permissables.permission_ID = permissions.IDWHERE  permissions.item_type = 'topic' AND  (permissables.owner_ID IN (<user_IDs>) AND permissables.owner_type = 'User') OR  (permissables.owner_ID = <company_ID> AND permissables.owner_type = 'Company') OR  (permissables.owner_ID IN (<user_groups_IDs>) AND permissables.owner_type = 'UserGroup')

问题：

哪些选项更好/更快？也许有更好的办法呢？

a)4个表(permissions,companIEs_permissions,user_groups_permissions,users_permissions)
b)2表(权限,允许)

>我需要声明不同的索引比btree在permissions.item_type？
>我需要每天运行几次真空分析表,使索引工作(两个选项)？

EDIT1：

sqlfiddle示例：

> wildplasser建议(来自评论),不工作：http://sqlfiddle.com/#!15/9723f8/1
>原始查询(4表)：http://sqlfiddle.com/#!15/9723f8/2

{我也删除了错误的地方谢谢@wildplasser}

我建议将对您的权限系统的所有访问抽象到几个模型类.不幸的是,我发现这样的权限系统有时最终会成为性能瓶颈,而且我发现有时需要对数据表示进行重构.
所以,我的建议是尝试将权限相关的查询隔离在几个类中,并尝试保持与这些类的接口,而不依赖于系统的其余部分.

这里的好方法的例子就是你以上所说的.你实际上并不参与主题表;当您构建权限时,您已经拥有您关心的主题ID.

不良接口的示例将是类接口,可以轻松将权限表连接到任意其他sql.

我明白你使用sql而不是sql上的特定框架提出了这个问题,但是从rails约束名称看来,您正在使用这样的框架,我认为利用它将对您未来的代码有用可维护性.

在10,000行的情况下,我认为这两种方法都可以正常工作.
我实际上并不确定这些做法会有所不同.如果您考虑生成的查询计划,假设您从表中获取少量行,则可以使用与每个表相对应的循环处理连接,其方式与查询可能处理相同,假设索引很可能返回少量行.
我没有给Postgres提供一个合理的数据集,以确定这是真实数据集的真实数据.如果这样做是有道理的,那么Postgres有足够的聪明才能做到这一点.

多态方法确实给你一个更多的控制,如果你遇到性能问题,你可能想检查是否移动它将有所帮助.
如果选择多态方法,我建议您编写代码以进行检查,以确保您的数据是一致的.也就是说,确保resource_type和resource_ID对应于系统中存在的实际资源.
在任何情况下,如果应用程序的问题迫使您对数据进行非规范化,以便数据库约束不足以强制执行一致性,我将会做出这一建议.

如果您开始遇到性能问题,以下是您将来可能需要做的各种事情：

>在应用程序中创建一个缓存,将对象(如主题)映射到这些对象的一组权限.
>在应用程序中创建一个缓存,缓存给定用户拥有的所有权限(包括他们所属的组),用于应用程序中的对象.
>实现用户组权限.这是创建一个物化视图,将user_group权限与用户权限和用户组成员资格相结合.

在我的经验中,真正杀死许可系统性能的事情是当你添加一些东西,如允许一个组成为另一个组的成员.在这一点上,您很快就能到达需要缓存或物化视图的位置.

不幸的是,很难给出更具体的建议,而不用实际掌握数据并查看真正的查询计划和实际的性能.我认为,如果你为将来的改变做好准备,那么你会很好.

总结

以上是内存溢出为你收集整理的PostgreSQL – “多态表”vs 3表全部内容，希望文章能够帮你解决PostgreSQL – “多态表”vs 3表所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。