MySQL : Table optimization word 'guest' or memberid in the column

Question

这个问题是针对 MySQL 的(它允许列中有许多 NULL，这是唯一的，所以我的问题的解决方案可能会略有不同)。

有两个表:members 和 Table2。表成员有:memberid char(20), 这是一个主键。 (请不要推荐使用 int(11) 而不是 char(20) 作为 memberid，我无法更改它，它正好包含 20 个符号)。

表 2 有:

CREATE TABLE IF NOT EXISTS `Table2`
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    memberid varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status tinyint(4) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

Table2.memberid 是一个单词“guest”(可以重复多次)或来自 members.memberid 的值(也可以重复多次)。 Table2.memberid 列中的任何值(如果不是“guest”)都存在于 members.memberid 列中。同样，members.memberid 是唯一的。 Table2.memberid，即使排除'guest'字也不唯一。

因此，Table2.memberid 列如下所示:'客人''lkjhasd3lkjhlkjg8sd9''kjhgbkhgboi7sauyg674''客人''客人''客人''lkjhasd3lkjhlkjg8sd9'

Table2 只有 INSERTS 和 UPDATES。它只更新状态。更新状态的条件:设置 status=0 WHERE memberid='' 和 status=1。因此，它可以更新一次或根本不更新。因此，UPDATES 的数量小于或等于(根据统计，它是 INSERTS 数量的两倍)。

问题只是关于优化。问题可以拆分为:

1) 您是否强烈建议将“guest”一词替换为 NULL 或特殊的“xxxxxyyyyyzzzzz00000”(20 个符号，如“非常特殊和保留”的字符串)以便您可以使用字符(20) 对于Table2.memberid，因为所有的值都是char(20)？

2) 使用外键怎么样？由于“客人”的值(value)，我不能使用它。该值不能在 members.memberid 列中。

换句话说，我需要一些帮助来决定:

• 是否可以使用“guest”(我喜欢这个词)-vs- 选择 20-char-reserved-string 这样我就可以使用 char(20) 而不是 varchar(20 ) -vs- 保留 NULL 而不是“guest”，

• 除“guest”外的所有值实际上都是外键。有没有可能使用此信息来提高性能的方法？

• 该表经常使用，所以我必须尽可能好地构建 Table2。非常感谢任何想法。

谢谢。

添加:嗯...我想我找到了一个很好的解决方案，它允许我将 memberid 视为外键。

Answer 1

1) Do you HIGHLY recommend to replace the word 'guest' to NULL or to a special 'xxxxxyyyyyzzzzz00000' (20 symbols like a 'very special and reserved' string) so you can use chars(20) for Table2.memberid, because all values are char(20)?

混合来自不同域的值总是会引起麻烦。最好的办法是解决潜在的结构问题。糟糕的设计解决起来成本非常高，修复起来也非常昂贵。

这里简单介绍一下这个问题。此类问题最简单的数据完整性约束是外键约束。您不能使用一个，因为“guest”不是 memberid。 (成员 ID 来自一个域；“访客”不是该域的一部分；您混合了来自两个域的值。)使用 NULL 来标识访客没有多大帮助；您无法区分 guest 和缺少 memberid 的成员(member)。 (使用 NULL 来标识任何东西通常不是一个好主意。)

如果您可以使用一个特殊的 20 个字符的成员(member) ID 来识别所有客人，那么这样做可能是明智的。你可能很幸运，因为那个“客人”是五个字母。如果您可以为 guest 使用“guestguestguestguest”而不会完全破坏您的应用程序逻辑，我真的会首先考虑。 (但是，您说这似乎将 guest 视为已登录用户，我认为这会使事情破裂。)

我认为，改造“用户”父类(super class)型是可能的，并且这可能被证明是最好的整体解决方案。父类(super class)型会让您有时将成员和 guest 视为相同的(因为它们并非完全不同)，而在其他时候则不同(因为它们并不完全相同)。父类(super class)型还允许个人(成员)和聚合用户(客人都集中在一起)而不会过度紧张。它将统一两个域，因此您可以对成员使用外键约束。但这需要更改程序逻辑。

在表 2 中(请找到一个比它更好的名称)，memberid 上的索引或 memberid 和 status 上的复合索引的性能将与您预期的一样好。我不确定复合索引是否有帮助； “status”只有两个值，选择性不强。

all values, except 'guest' are actually foreign keys. Is there any possible way to use this information for increasing the performance?

不，它们不是外键。 (见上文。)真正的外键有助于数据完整性，但不利于 SELECT 性能。

“提高性能”几乎毫无意义。性能是一种平衡行为。如果要提高性能，则需要指定要改进的部分。如果您想要更快的插入，请删除索引和完整性约束。 (不要那样做。)如果你想要更快的 SELECT 语句，建立更多的索引。 (但是更多索引会减慢插入速度。)

您可以通过迁移到可加速所有数据库性能的硬件来加速所有数据库性能。 (咳咳)更快的处理器，更快的磁盘，更快的磁盘子系统，更多的内存(通常)。将关键表或索引移动到固态磁盘可能会让您大吃一惊。

调整您的服务器会有所帮助。但要注意整体表现。不要太专注于加速一个查询而不是降低所有其他查询的性能。理想情况下，编写一个测试套件并在开始测试之前确定什么速度足够好。例如，假设您有一个查询需要 30 秒。什么是可接受的改进？ 20秒？ 15秒？ 2 毫秒听起来不错，但对于需要 30 秒的查询来说不太可能成为目标。 (虽然我已经看到通过迁移到更好的表和索引结构来提高性能。)