改进数据库表设计

Question

我正在尝试设计一个数据库表，它将由服务a更新。另一方面，服务B将通过过滤一些行来读取数据

以下是当前表格的示例：

^{tb1}$

顺便说一下，以下是表格创建：

create table MyTable
(
    reference_id integer,
    rent_id integer,
    price integer,
    date date not null,
    constraint uniq_reference_id_rent_id_date
        unique (reference_id, rent_id, date)
);

create index ix_MyTable_date
    on MyTable (date);

服务B中的数据如下所示：

 {date_1: [(reference_id_1, rent_id_1), (reference_id_2, rent_id_2), ...]}

因此，有一个约束和一个与性能相关的问题

• 无法修改服务B中的数据
• 获取结果的SQL查询未执行。让我解释一下：

以下是查询：

SELECT * 
FROM MyTable
WHERE date = date_1 AND
         (reference_id = reference_id_1 AND rent_id = rent_id_1) OR
         (reference_id = reference_id_2 AND rent_id = rent_id_2) OR
         (reference_id = reference_id_3 AND rent_id = rent_id_3) OR
         ...

在PostgreSQL 10.14上根本无法执行此查询。而且我们也知道我们正在寻找这对夫妇的那一排（参考号1，租金号1）可能不存在

因此，我正在寻找不同的解决方案来重新设计我的表。一种解决方案是创建一个包含（reference_id_1，rent_id_1）couple的散列值的附加列

最后，我相信您的想法将使我能够改进同事提出的解决方案或找到另一种解决方案

提前谢谢

Answer 1

你的桌子设计很好。您需要一个multicolumn index来帮助您满足这个查询。没有必要为此使用哈希值列

我建议买这个

 CREATE INDEX ref_rent_date
     ON TABLE MyTable
  USING BTREE (reference_id, rent_id, date);

我建议将date列放在索引的最后，这样您就可以使用它来搜索日期范围（WHERE date >= '2021-01-01'）以及您向我们展示的查询模式中的特定日期

Pro-tip：除非您知道您的查询模式需要它们，或者您知道您需要它们来满足唯一约束等要求，否则不要创建大量的单列索引。大多数在WHERE子句中包含多个项的查询，在创建多列索引以匹配查询模式时效果更好

Pro tip 2随着应用程序及其表的增长，通常需要添加新的索引以匹配实际的查询模式。SQL的设计是为了在不影响数据的情况下添加和删除索引

阅读马库斯·温南的https://use-the-index-luke.com