数据库设计：用时间戳列替换布尔列？

Question

早些时候，我以这种方式创建了表：

create table workflow (
    id number primary key,
    name varchar2(100 char) not null,
    is_finished number(1) default 0 not null,
    date_finished date
);

列 is_finished 指示工作流程是否完成。 date_finished 列是工作流程完成的时间。

然后我有了一个想法“我不需要 is_finished 因为我可以说：其中 data_finished 不为空”，并且我设计时没有 is_finished 列：（

create table workflow (
    id number primary key,
    name varchar2(100 char) not null,
    date_finished date
);

我们使用 Oracle 10）

这是一个好主意还是坏主意？我听说不能在具有 NULL 值的列上建立索引，因此 where data_finished is not null 在大表上会非常慢。

Answer 1

这是好主意还是坏主意？

好主意。

您已经消除了冗余列占用的空间； DATE 列有双重作用——您知道工作何时完成以及何时。

我听说不能在具有 NULL 值的列上建立索引，因此“where data_finished is not null”在大表上会非常慢。

这是不正确的。 Oracle 索引忽略 NULL 值。

您可以创建基于函数的索引，以避免未索引的 NULL 值，但我遇到的大多数 DBA 确实不喜欢他们，所以要做好战斗的准备。

Answer 2

有一种正确的方法来索引空值，并且它不使用 FBI。 Oracle将索引空值，但不会索引树中的空LEAF值。因此，您可以消除列is_finished并创建这样的索引。

CREATE INDEX ON workflow (date_finished, 1);

然后，如果您检查此查询的解释计划：

SELECT count(*) FROM workflow WHERE date_finished is null;

您可能会看到正在使用的索引（如果优化器满意）。

回到最初的问题：看看这里的各种答案，我认为没有正确答案。我个人可能倾向于在不必要的情况下删除列，但我也不喜欢过多地解释列的含义。这里有两个概念：

1. 记录完成。 is_finished
2. 记录在特定日期完成。 date_finished

也许您需要将它们分开，也许您不需要。当我考虑删除 is_finished 列时，我很烦恼。接下来，可能会出现记录完成的情况，但您不知道确切的时间。也许您必须从​​其他来源导入数据并且日期未知。当然，这不属于现在的业务需求，但情况发生了变化。那你怎么办？好吧，您必须在 date_finished 列中放入一些虚拟值，现在您已经对数据进行了一些妥协。并不可怕，但那里有一个摩擦。当我做那样的事情时，我脑子里的小声音在喊你做错了。

我的建议是，将其分开。您正在谈论一个很小的列和一个非常瘦的索引。存储在这里不应该成为问题。

表示规则：折叠知识
转化为数据，以便程序逻辑可以
愚蠢而坚强。

-埃里克·S·雷蒙德

Answer 3

对于所有认为该专栏浪费空间的人：

双重职责在数据库中并不是一件好事。您的主要目标应该是清晰。许多系统、工具、人们都会使用你的数据。如果您通过将含义隐藏在其他列中来掩饰值，那么您就是在乞求其他系统或用户弄错。

任何认为它可以节省空间的人都是完全错误的。

您将需要该日期列上的两个索引...一个将是基于函数的，正如 OMG 建议的那样。它看起来像这样：

NVL(Date_finished, TO_DATE('01-JAN-9999'))

因此，要查找未完成的作业，您必须确保正确编写 where 子句

它看起来像这样：

WHERE
NVL(Date_finished, TO_DATE('01-JAN-9999')) = TO_DATE('01-JAN-9999')

是的。那是那么清楚。 好

完全比WHERE
IS_Unfinished = 'YES'

您希望在同一列上有第二个索引的原因是对于该日期的所有其他查询...您不会希望使用该索引按日期查找工作。

那么让我们看看您根据 OMG 的建议等取得了哪些成果。

您使用了更多的空间，您混淆了数据的含义，您更有可能犯错误......胜利者！

有时，程序员似乎还生活在 70 年代，当时 1MB 的硬盘空间就是一套房子的首付。

您可以在不放弃很多清晰度的情况下节省空间。将 Is_unfinished 设置为 Y 或 NULL...如果您将仅使用该列来查找“待办事项”。这将使该索引保持紧凑。它只会与未完成的行一样大（通过这种方式，您可以利用未索引的空值，而不是被它搞砸）。你在桌子上放了一点空间，但总的来说，它比联邦调查局要少。该列需要 1 个字节，并且只需对未完成的行进行索引，这样，这只是工作的一小部分，并且可能保持相当恒定。无论您是否试图找到它们，FBI 的每一行都需要 7 个字节。该索引将与表的大小保持同步，而不仅仅是与未完成作业的大小保持同步。

回复 OMG 的评论

在他/她的评论中，他/她指出要找到未完成的工作，您只需使用

WHERE date_finished IS NULL

但在他的回答中他说

您可以创建基于函数的索引，以避免未索引的 NULL 值

如果您按照他指向的链接操作，使用 NVL 将 null 值替换为其他任意值，那么我不确定还有什么解释。

Answer 4

这是好主意还是坏主意？我听说你不能在具有 NULL 值的列上建立索引，因此“where data_finished is not null”在大表上会非常慢。

Oracle 会索引可空字段，但不会索引NULL值

这意味着您可以在标记为NULL<的字段上创建索引/code>，但该字段中包含 NULL 的记录不会进入索引。

这反过来意味着，如果您将date_finished NULL设置为date_finished NULL，则索引将尺寸减小，因为NULL值不会存储在索引中。

因此，涉及 date_finished 上范围搜索相等的查询实际上会执行得更好。

当然，此解决方案的缺点是涉及 date_finished 的 NULL 值的查询必须恢复为全表扫描。

您可以通过创建两个索引来解决此问题：

CREATE INDEX ON mytable (date_finished)
CREATE INDEX ON mytable (DECODE(date_finished, NULL, 1))

并使用此查询查找未完成的作品：

SELECT  *
FROM    mytable
WHERE   DECODE(date_finished, NULL, 1) = 1

这将表现得像分区索引：完整的作品将由第一个索引索引；不完整的将由第二个索引。

如果您不需要搜索完整或不完整的作品，您始终可以删除适当的索引。

Answer 5

在表设计方面，我认为您删除了 is_finished 列是件好事，正如您所说的那样，它是不必要的（它是多余的）。如果没有必要，就没有必要存储额外的数据，只会浪费空间。就性能而言，我认为这对于 NULL 值来说不是问题。他们应该被忽略。

Answer 6

正如其他答案中已经提到的那样，对于除“WHERE date_finished IS NULL”之外的所有查询（因此这取决于您是否需要使用该查询），我将使用空值作为索引工作。我绝对不会使用像 9999 年这样的异常值，正如答案所建议的：

您还可以使用“虚拟”值（例如 9999 年 12 月 31 日）作为未完成工作流程的 date_finished 值

像 9999 年这样的异常值会影响性能，因为（来自 http://richardfoote.wordpress.com/2007/12/13/outlier-values-an-enemy-of -索引/）：

范围扫描的选择性基本上由 CBO 计算为感兴趣范围内的值的数量除以可能值的全部范围（即. 最大值减去最小值）

如果您使用像9999这样的值，那么数据库会认为存储在该字段中的值的范围是例如2008-9999，而不是实际的2008-2010；因此，任何范围查询（例如“2008 年和 2009 年之间”）似乎只覆盖了可能值范围的一小部分，而实际上覆盖了大约一半的范围。它使用这个统计数据来表示，如果覆盖的可能值的百分比很高，则可能会匹配很多行，然后全表扫描将比索引扫描更快。如果数据中存在异常值，它将无法正确执行此操作。

Answer 7

正如其他人所说，删除可导出值列是个好主意。

另一种想法是，通过删除该列，您将避免需要编写代码的自相矛盾的情况，例如当 is_finished = No 和 finish_date = Saturday 时会发生什么......等。

Answer 8

要解析索引/非索引列，简单地 JOIN 两个表不是更容易吗，如下所示：

-- PostgreSQL
CREATE TABLE workflow(
    id SERIAL PRIMARY KEY
  , name VARCHAR(100) NOT NULL
);

CREATE TABLE workflow_finished(
    id INT NOT NULL PRIMARY KEY REFERENCES workflow
  , date_finished date NOT NULL
);

因此，如果workflow_finished 中存在记录，则此工作流已完成，否则未完成。在我看来，这相当简单。

当查询未完成的工作流程时，查询变为：

-- Only unfinished workflow items
SELECT workflow.id
FROM workflow
WHERE NOT EXISTS(
  SELECT 1
  FROM workflow_finished
  WHERE workflow_finished.id = workflow.id);

也许您想要原始查询？有旗帜和日期吗？然后像这样查询：

-- All items, with the flag and date
SELECT
    workflow.id
  , CASE
    WHEN workflow_finished.id IS NULL THEN 'f'
    ELSE                                   't'
    END AS is_finished
  , workflow_finished.date_finished
FROM
            workflow
  LEFT JOIN workflow_finished USING(id);

对于数据的消费者，可以而且应该根据他们的需要创建视图。

Answer 9

作为基于函数的索引的替代方法，您还可以使用“虚拟”值（例如 9999 年 12 月 31 日，或者最早预期 date_finished 值的前一天）作为未完成工作流的 date_finished 值。

编辑：替代虚拟日期值，以下评论。

Answer 10

我更喜欢单列解决方案。

但是，在我最常使用的数据库中，索引中包含 NULL，因此搜索开放工作流程的常见情况会很快，而在您的情况下会较慢。由于搜索开放工作流程的情况可能是您最常做的事情之一，因此您可能需要冗余列来支持该搜索。

测试性能，看看是否可以使用性能更好的解决方案，然后在必要时回退到不太好的解决方案。