mysql - Web 应用程序用户表主键 : surrogate key vs username vs email vs customer Id

Question

我正在尝试在 MySQL 中设计一个电子商务 Web 应用程序，但在为用户表选择正确的主键时遇到了问题。给出的示例只是用于说明的示例示例。



用户表有以下定义

CREATE  TABLE IF NOT EXISTS `mydb`.`user` (
  `id` INT NOT NULL ,
  `username` VARCHAR(25) NOT NULL ,
  `email` VARCHAR(25) NOT NULL ,
  `external_customer_id` INT NOT NULL ,
  `subscription_end_date` DATETIME NULL ,
  `column_1` VARCHAR(45) NULL ,
  `column_2` VARCHAR(45) NULL ,
  `colum_3` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
  UNIQUE INDEX `customer_id_UNIQUE` (`external_customer_id` ASC) )
ENGINE = InnoDB

我在主键候选列方面面临以下问题:

Id 列

优点

无商业意义(稳定主键)

更快的表连接

压实机指数

缺点

不是“自然”键

所有属性表都必须与“主”用户表连接，因此无法进行非连接直接查询

导致较少的“自然”SQL 查询

泄漏信息:如果起始值为0，用户可以计算出注册用户的数量(更改起始值对此进行排序)
ii) 用户在 time_X 注册配置文件为 user_A，一段时间后在 time_Y 注册为 user_B，将能够轻松计算出一段时间内注册用户的数量 ((id for user_B) - (Id for user_A)/(time_Y -时间_X))

邮箱栏

优点

无

缺点

用户应该能够更改电子邮件地址。不适合主键

用户名栏

优点

“自然”主键

少表连接

更简单、更“自然”的查询

缺点

加入表时 varchar 列速度较慢

varchar 列上的索引不如 int 列索引紧凑

由于外键依赖于值，因此很难更改用户名。解决方案:“同步”应用程序上的所有外键或不允许用户更改用户名，例如用户应删除配置文件并注册新

external_customer 列

优点

可以用作客户的外部引用并且不保存任何信息(也许可以使用不可编辑的用户名？)

缺点

如果是自动增量，可能会泄漏信息(如果可能)

如果已在使用自动增量代理 ID，则生成 unqiue 值会出现问题，因为 MySQL innodb 引擎在同一个表中没有多个 auto_increment 列

what are the common practice when choosing user table primary keys for a scalable ecommerce web application? all feedback appreciated

Answer 1

对于你的一些分析，我无话可说。如果我削减了你的一些优点或缺点，那只意味着我认为我没有任何有用的补充。

Id column

Pros

• No business meaning (stable primary key)
• faster table joins
• compacter index

首先，声明为 NOT NULL UNIQUE 的任何列或列集都具有主键的所有属性。您可以使用它们中的任何一个作为外键引用的目标，这就是这一切的真正意义所在。

在您的情况下，您的结构允许 4 列作为外键引用的目标:id、用户名、电子邮件和 external_customer_id。您不必一直使用同一个。将 id 用于 90% 的 FK 引用，并为其中 10% 使用电子邮件可能是有意义的。

稳定性与列是否具有商业意义无关。稳定性与值可能发生变化的频率和情况有关。除非您正在运行 Oracle，否则“稳定”并不意味着“不可变”。 (Oracle 不能执行 ON UPDATE CASCADE。)

根据您的表结构和索引，自然键可能执行得更快。自然键使一些连接变得不必要。在构建生产数据库之前，我进行了测试。可能需要几十年时间才能达到连接 ID 号的性能优于较少连接和自然键的程度。我已经在 SO 或 DBA 上写过这些测试。

您还有其他三个唯一索引。 (对你有好处。我认为至少有 90% 的构建数据库的人没有做到这一点。)因此，不仅仅是 ID 编号的索引比这三个中的任何一个都更紧凑；它也是一个额外的索引。 (在此表中。)

email column

Pros

• None

电子邮件地址可以被认为是稳定且唯一的。您无法阻止人们共享电子邮件地址，无论它是否是外键引用的目标。

但是电子邮件地址可能会“丢失”。在美国，大多数大学生在毕业一年左右就会丢失他们的 *.edu 电子邮件地址。如果您的电子邮件地址来自您正在付费的域，而您停止付款，则该电子邮件地址将消失。我想可以将类似的电子邮件地址提供给新用户。这是否会造成难以承受的负担取决于应用程序。

Cons

• a user should be able to change the email address. Not suitable for primary key

SQL 数据库中的所有值都可以更改。如果您的环境不允许您的 dbms 及时遵守 ON UPDATE CASCADE 声明，则它只是不合适的。我的环境可以。 (但我在体面的、非共享的硬件上运行 PostgreSQL。) YMMV。

username column

Pros

• a "natural" primary key
• Less table joins
• simpler and more "natural" queries

更少的连接是一个重要的点。我一直在咨询演出，在那里我看到无意识地使用 ID 号码使人们编写具有 40 多个连接的查询。明智地使用自然键消除了多达 75% 的自然键。

始终使用代理键作为外键的目标(除非 Oracle)或始终使用自然键作为目标并不重要。思考很重要。

Cons

• varchar column is slower when joining tables
• an index on a varchar column is less compact than int column index

如果不限定该声明，您就不能真正说加入 varchar() 会更慢。事实是，尽管 varchar() 上的大多数连接都比 id 号上的连接慢，但它们不一定慢到您无法使用它们。如果查询需要 4 毫秒的 id​​ 号码，6 毫秒的 varchar()，我认为这不是取消 varchar() 资格的好理由。此外，使用自然键将消除大量连接，因此整体系统响应可能会更快。 (在其他条件相同的情况下，40 个 4ms 连接的性能将低于 10 个 6ms 连接。)

我不记得在我的数据库职业生涯(25 年以上)中索引的宽度是选择外键目标的决定性因素的任何情况。

external_customer column

pros

• can be used as an external reference for a customer and holds no information (maybe non-editable username can be used instead? )

实际上很少有系统可以让我更改用户名。大多数人会让我改变我的真实姓名(我认为)，但不会让我改变我的用户名。我认为不可编辑的用户名是完全合理的。