mysql - 用户数据和更改日志的哪种布局最有效且存储消耗更少？

Question

我的用户可以更新他们的信息，这些信息保存在表中定义数量的列中，例如:user ( id INT, email VARCHAR, phone VARCHAR, address VARCHAR)，例如.

我见过其他实现，例如 Wordpress 的实现，它为用户将此信息存储在一个名为 usermeta 的表中，其布局为 ( umeta_id INT, user_id INT, meta_key VARCHAR,元值 VARCHAR ).

在我想要实现的更改日志中，我正在评估是使用这样的解决方案还是制作(我认为会更好的)布局，例如:userLog ( id INT, date TIMESTAMP, email VARCHAR，电话 VARCHAR，地址 VARCHAR)。
因此，我可以获得任何用户在给定日期拥有的所有信息的历史记录。行将只记录更改，在未更改的列上具有 NULL。

对于第一个问题:除了能够通过插入适当的meta_key来创建新的信息类型之外，这种布局还有什么优势吗？
我有时认为，如果我的环境需要考虑性能，那么这种布局可能不太合适，因为我会为我要存储的每种数据使用 VARCHAR。

对于第二个问题:存储和选择/插入效率真的能影响我正在考虑的两种解决方案吗？
哪个解决方案比另一个解决方案占用空间更少(或更多)和/或选择/插入效率更低(或更高)，为什么？

Answer 1

一些想法，如果不一定是答案:

显然更改日志对您来说是必不可少的，因此每个用户一行的原始结构不适合您。所以我们谈论的是以下选择:

1. 每个用户的整个信息集的每个版本一行；或
2. 每个用户信息项的每个版本一行

解决方案 1 对应于您的

userLog ( id INT, date TIMESTAMP, email VARCHAR, phone VARCHAR, address VARCHAR )

方案二对应Wordpress方案一:

umeta_id INT, user_id INT, meta_key VARCHAR, meta_value VARCHAR

您的问题 1: 我看不出 Solution2 有任何优势，除非您随后决定要捕获用户的(例如)网站 URL 或(例如)最喜欢的颜色作为好吧，你可以通过添加一个 meta_key 来做到这一点。但是您同样可以在 Solution1 下轻松地执行此操作，只需执行一个

ALTER TABLE userlog ADD COLUMN WebSiteURL(etc)

这并不难做到。除非您公司中的 DBA 非常像杜宾犬 (;))。因为您持有更改日志，所有现有用户(在更改时)现在将有一个空白的 WebsiteURL 列；但这正是您想要的:您不知道他们的 WebsiteURL，因为系统之前没有捕获它。当然，新列必须是 NULLABLE - 但无论如何这可能是不可避免的，即使使用“初始”数据，除非您用来捕获用户信息的方法坚持将电子邮件、电话和地址列为必需的列。

对我来说，meta_key 解决方案的缺点大于优点。缺点是:

• 您必须开发一段数据透视代码，将一个用户的用户信息转换为另一个用户
  排。您必须在要在一行中获取用户信息的每个地方调用此代码。在相比之下，Solution1只需要

  SELECT userID,[所有用户信息] FROM userLog INNER JOIN (SELECT userID,MAX(datechanged) AS LatestDAteChanged FROM userlog GROUP BY userID) a ON userlog.userid=a.userID AND userlog.DateChanged=a.LatestDAteChanged

  这比枢轴更有效。使用 UserID、DateChanged 的​​索引，这将奔跑如风。

• 除非您真的想在 userinfo 表(Email、Email、Email、Email、Email)中多次保存 meta_key 值，否则您需要一个额外的 Meta_Key_Lookup 表。

第二个问题:对于最终的空间效率，是的，meta_key Solution2 是最好的。特别是如果您不使用 VARCHAR 元键，而是使用元键 ID 值，并且有一个单独的元键查找表(例如 1=Email，2=Phone 等)。但我认为这不是 meta_key 解决方案 2 的决定性论据，因为存储价格几乎为零，而且该解决方案涉及困难。

(注意/想法:恕我直言，您在解决方案 1 中保留 NULL 值的想法是一条错误的道路。尝试获取最新电子邮件的编码，然后是电话，然后是地址(分别) 对于每个用户来说，这将是一场噩梦:几乎与其他解决方案所需的枢轴一样难以编码/测试 - 以及服务器运行 - 以及存储边际的减少。每次做一件事时只保留整行变化。除非你只是举个例子，真正的用户信息集是 50 列宽...)

恕我直言，存储问题不是决定性的。那么让我们转向 SELECT/INSERT 效率:

在这个问题上，我认为还是Solution1胜出。在 Inserts 上，SOlution1 获胜:仅插入一行，即使用户更改了其信息中的每个字段。在 SELECTS 上，解决方案 1 再次获胜:您只需要查看每个用户的最新信息(上面的代码)，这是 SQL 优化的类型。相比之下，解决方案 2 需要一个支点:SQL 不擅长的东西。