何时将主表的列移入辅助表是一个好主意？

Question

假设我有这样一个表：

  create table users (
   user_id int not null auto_increment,
   username varchar,
   joined_at datetime,
   bio text,
   favorite_color varchar,
   favorite_band varchar
   ....
 );

随着时间的推移，越来越多的列，比如favorite_animal、favorite_city等，被添加到这个表中。最终，会有20个或更多的列。

此时，我感觉应该将列移到一个单独的"user_profiles"表中，这样我就可以执行"select * from users"而不返回大量通常无关的列（比如favorite_color）。当我需要按favorite_color查询时，只需像这样操作：

select * from users inner join user_profiles using user_id where
user_profiles.favorite_color = 'red';

将主表中的列移出到“辅助”表中是一个好主意吗？

还是最好将所有列保留在“用户”表中，并始终明确要返回哪些列？例如：

select user_id, username, last_logged_in_at, etc. etc. from users;

这里涉及哪些性能考虑因素？

- dan

---

在select *上阅读：为什么SELECT *被认为是有害的？ - Ocaso Protal

Answer 1

不要使用辅助表，如果它将包含一组没有概念上的凝聚力的杂乱字段。

应该使用一个单独的表，如果你可以想出一个好的概念分组，例如一个地址表。

当然，你的应用程序有自己的性能和规范化需求，你应该根据自己的情况恰当地遵循这个建议。

- ctford

Answer 2

我认为最好的选择是拥有适当规范化的表格，并且仅请求所需的列。如果用户档案表格结构良好，提供数据完整性并且易于后期增强/修改，则可能不是一个坏主意。只有您真正了解您的要求。

- JeremyDWill

Answer 3

没有人提到的一件事是，如果主表的行大小过大，通常建议使用辅助表。在文档中阅读有关您特定数据库的行大小限制。通常创建宽度较小的表并将不经常使用的字段移动到单独的表中会有性能优势。如果选择创建具有一对一关系的辅助表，请确保设置PK / FK关系以维护数据完整性，并在FK字段上设置唯一索引或约束条件以维护一对一关系。

此外，和其他人一样，我强烈反对在生产查询中使用select *。虽然可以节省几秒钟的开发时间，但这会创建性能问题，并使应用程序难以维护（是的，难以维护 - 因为您不能随意返回不想在应用程序中显示但需要在数据库中使用的内容。您将破坏使用select的插入语句，并向用户显示不想让他们看到的内容）。

- HLGEM

Answer 4

适用于此的一般准则（称为规范化）是将表格按照不同的实体/对象/概念进行分组，并且该表中的每个列（字段）应描述该实体的某些方面。

在您的示例中，似乎favorite_color描述（或属于）用户。有时候，将数据移动到第二个表格是一个好主意：当清楚地了解到该数据实际上描述了第二个实体时。例如：您开始收集user_id、name、email和zip_code的数据库。然后在某个时间点，CEO决定他也想收集street_address。此时，已经形成了一个新实体，您可以将数据概念上视为两个表格：

user: userid, name, email
address: steetaddress, city, state, zip, userid(as a foreign key)

所以，总的来说：真正的挑战在于决定哪些数据描述了表的主要实体，以及是否存在其他实体。 这里有一个很好的规范化示例，帮助我更好地理解它。

- Michael Jasper

Answer 5

不要养成使用 SELECT * FROM ... 的习惯。如果你的应用程序变得越来越大，而你在应用程序的不同部分查询 users 表进行不同的操作，那么当你添加 favorite_animal 时，你更可能会破坏一些使用了 SELECT * 的地方。或者至少，那个地方现在正在获得未使用的字段，从而减慢了它的速度。

选择你需要的数据。它可以自我记录下你尝试使用该代码做什么。

- Craig Celeste

Answer 6

除非你有充分的理由，否则不要进行反规范化。

每当用户添加新的收藏时，每隔一天就添加一个收藏列是最麻烦的维护方式。我强烈建议在你的情况下创建一个表来保存收藏值。我相信我不会一直添加新列。

- Kuberchaun

---

+1 for "不要非正常化，除非你有充分的理由。" 我猜性能或空间使用很可能不是在“用户”表中的“好理由”。 - user533832

我所学习并现在生活的方式基本上是，如果您在一个 OLTP 系统中，实际上有充分理由的机会是如此罕见和不频繁，以至于当它们发生时，您可以带领团队出去吃午餐来庆祝。 - Kuberchaun

Answer 7

除非有其他原因（例如数据库的正常形式），否则不应该这样做。你并没有节省任何空间，因为数据仍然必须存储，相反，你浪费了更多的空间，因为你需要另一个索引来访问它们。

- flolo

---

这个答案（并非有意）误导了读者。虽然没有节省空间，但性能可以大幅提升。有许多可能的好处。辅助表可以使主表具有固定的行大小，从而使其非常快速。更多信息请参见http://dev.mysql.com/doc/refman/5.1/en/static-format.html。 - David Snabel-Caunt

Answer 8

只获取你需要的列总是更好的选择（尽管如果模式发生变化可能需要更多的维护）。

这将减少MySQL和客户端应用程序的内存使用，同时由于传输的数据量减少，查询时间也会缩短。无论是否在线上环境中都会有所受益。

- David Snabel-Caunt

Answer 9

这是一个经验法则：如果向现有表添加列需要将其设置为可空（在数据迁移后等），那么最好创建一个新表，其中所有列都是 NOT NULL（当然还要有对原始表的外键引用）。

出于各种原因，您不应该依赖使用 SELECT *（请自行搜索）。

- onedaywhen

---

@JackPDouglas：你读了Chris Date的文章吗？他负责6NF，请向他表达你的不满 :) 太多有经验的SQL编码人员在处理NULL时犯错误，这不能归咎于缺乏“能力”，我个人认为。我认为任何人都不应该需要处理3VL。 - onedaywhen

@JackPDouglas：现在考虑这个：CREATE TABLE T (c INTEGER NOT NULL, CHECK(NULL = NULL));以及INSERT INTO T (c) VALUES (1);不会触发CHECK，我证明了NULL= NULL是真的吗？当然没有。我仅仅表明 NULL = NULL 没有引起 CHECK 的错误。 现在读一下规范：“如果对于表的任何行，指定的搜索条件都不为假，那么表检查约束就得到满足”。因此，我证明了 NULL = NULL 不为假。 - onedaywhen

在Postgres上执行select (null=null) is null;，结果为'true'。在真实的数据库中从未听说过'unknown' - 请命名您的RDBMS。 - user533832

@JackPDouglas：再次提醒，你的SQL语法不符合标准，但是你尝试在PostgreSQL上使用SELECT (NULL=NULL) IS UNKNOWN;了吗？ ;) - onedaywhen

“有效”的SQL语法对我来说并不重要 - 我使用Oracle和Postgres谋生，我只关心它们上面的“有效性”，而不是某个标准。从Postgres文档中可以看到：请注意，IS UNKNOWN和IS NOT UNKNOWN与IS NULL和IS NOT NULL实际上是相同的，唯一的区别在于输入表达式必须是布尔类型。 - user533832