mysql - 在 MySQL 中存储包含 2 个固定字符和 4 个数字的数据的性能方式？

Question

我们必须在一个数百万行的表中存储一个“文件 ID”信息。格式是巴西州 ID 缩写(即:PA 表示 PARA，BA 表示巴伊亚，SP 表示圣保罗，RJ 表示里约热内卢等)和一个“范围”信息，由短格式年份构成，即:19 2019 年和月份，即“PA1908”格式。

如前所述，该表有数百万行，每个月我们都必须将其数据与外部数据源进行比较，如果外部数据源比我们的表最新，我们必须替换整个 STATE-YEAR-MONTH记录，因此文件 ID 的存在只是为了成为查询的 where 子句中的参数，以便选择要删除的行。

在第一个建模版本中，我将文件 ID 分成两列，fileid_state 作为 Char(2) 数据类型，使用哈希索引，fileid_scope 作为 smallint 数据类型，但我不确定这是归档可接受性能的唯一方法，可能只使用一个名为 file_id 的列，具有 Char(6) 数据类型和散列索引可以作为第一个版本执行。有什么建议最好的两种方法，或另一种存储文件 ID 的方法，以便尽快选择要删除的行？

请记住，我很难对这些方法进行基准测试，因为我们在有限的硬件中有近 10 亿行。

Answer 1

Q1:数据类型:首先问问自己将对字符串做什么:

• 您是否需要只查看“状态”部分？ “年”部分？ “月”部分？如果您对其中任何一个回答"is"，那么您应该可能将这些部分存储在 2 或 3 列中。 声明 CHAR(2) CHARACTER SET ascii，然后使用 TINYINT UNSIGNED 或 SMALLINT UNSIGNED 作为数字部分。
• 如果不是，只需执行 CHAR(6) CHARACTER SET ascii。如果需要，这可以INDEXed，可以单独使用，也可以与“复合”索引中的其他列一起使用。请提供可能需要该索引的UPDATE和SELECT语句；我们会批评。

没有“哈希”索引，只有 BTree。

“尽可能快地选择要删除的行”——将删除表的多少百分比？例如，如果您将DELETE FROM tbl WHERE sym = 'PA1908'，并且它只是表的一小部分，那么INDEX(sym) 以最佳方式工作。

我说“ascii”是为了避免 utf8 等所需的空间/处理。

Q2:“比我们的表更新得最多，我们必须替换整个 STATE-YEAR-MONTH 记录”——请详细说明这里发生了什么。