mysql - 通常/大多数情况下将 NULL 列存储在一个表中或创建 1 :1 relationship?

Question

运行的Mysql服务器版本:5.7.27-0ubuntu0.18.04.1

我正在创建一个网站/应用程序，其中用户“提交”可以是以下之一:

1. 文字评论
2. 图片/文件上传
3. 视频/文件上传(技术上与 #2 大致相同，只是 MIME 类型不同)

我无法在两种设计之间做出决定(为简洁起见，已缩短)...

CREATE TABLE submissions
(
    submissionID            INT,
    userID                  INT,
    submissionComments      TEXT,
    fileDirectory           VARCHAR2(32), -- starting here these are only used 20% of time
    fileName                VARCHAR2(128)
    fileMimeType            VARCHAR2(128),
    fileSize                INT,
    originalFileName        VARCHAR2(64)
)

-或-

CREATE TABLE submissions
(
    submissionID            INT,
    userID                  INT,
    submissionComments      TEXT
)

CREATE TABLE submissionFiles
(
    submissionFileID        INT,
    submissionID            INT, -- FK to submissions table
    fileDirectory           VARCHAR2(32),
    fileName                VARCHAR2(128),
    fileMimeType            VARCHAR2(128),
    fileSize                INT,
    originalFileName        VARCHAR2(64)    
)

我假设文本评论可能占提交内容的 70-80%。

因此，问题是，使用单个表并在 fileDirectory/fileName/fileMimeType/fileSize/originalFileName 中包含一堆 NULL 值是否更好？或者说，是不是在文件上传的时候，有1:1的关系来支持比较好。在这种情况下，我将创建提交和submissionFiles 记录。显然，大多数查询都需要连接两个表。

这本质上归结为没有很好地理解 VARCHAR(和 1 INT)列在大多数为 NULL 的表中的影响。考虑到这是一个全新的网站/应用程序，我可能会在这里进行一些预先优化，但我正在尝试提前计划。

晚加的第二个问题(当我输入这个问题时)，我看到 TEXT 能够处理:65,535 个字符或 64 KB。对于典型用户提交的内容来说，这似乎很多(可能少于 500 个字符)。它会很快耗尽存储空间。将submissionComments 放入VARCHAR(500) 而不是TEXT 会有影响吗？我假设如果有的话，除了能够存储“更少”之外，没有任何负面的权衡。

谢谢!

编辑:正如 madhur 指出的那样，关于“设计模式”也有类似的问题/好的答案。我更关心表现。大量 varchar 的存在是否会对数据存储/检索产生负面影响(通过扰乱 mysql 实现页面/范围/等的方式)？

Answer 1

我已经以两种方式构建了模式。在某种程度上，这并不重要。但您可能会发现某些查询以某种方式(或另一种方式)更快。磁盘使用情况大致相同。

您的第二个选项允许(因此意味着)每个“提交”有多个"file"。对于这样的“多:1”关系，您必须使用 2 个表。

另一方面，如果每个“提交”只能有一个"file"，则不需要 submissionFileID (我认为它应该是 PRIMARY KEY ??) 相反，请对第二个表使用 PRIMARY KEY(submissionID)。

如果您想进一步讨论，请提供完整的CREATE TABLE，包括NULL或NOT NULL、PRIMARY KEY每个表的 以及任何二级索引。

submissionComments into VARCHAR(500) instead of TEXT?

• 没有存储差异。
• 没有速度差异。
• 前者会在 500 个字符处截断，并给出警告或错误；后者将截断为 65535 字节。我会简单地使用TEXT。

回到主要问题。您的示例有几个列要么全部为 NULL，要么全部填充。因此，我倾向于 2 个表。