mysql - 索引 SecureRandom 字符串列时使用什么前缀

Question

我有一个数据库列(类型 varchar(255))，它存储由 SecureRandom.urlsafe_base64 生成的 URL 安全的 base 64 字符串。方法调用使用默认值，因此结果应为 16 bytes or 22 characters in length .

base64 值用于在用户访问网站时查找记录，以屏蔽数据库 ID。由于此查找，我需要此列的索引，但我不想为整个列编制索引，因为那样可能在存储方面效率低下。

在这种情况下，确定要使用的最佳索引前缀的最佳方法是什么？我现在的想法是这样的:

1. 创建包含约 100k 条记录的示例数据以模拟生产数据
2. 为带前缀(例如 8 个字符)的 base 64 列添加索引
3. 运行 EXPLAIN 查找 base 64 列以查看有多少需要检查记录
4. 向上或向下调整索引并重新执行第 3 步。
5. 选择一个前缀大小来平衡 (a) 存储要求和 (b) 返回的匹配匹配的记录数。

这里的问题是我知道 SecureRandom 会生成唯一的 base 64 字符串，但我不确定它们有多独特。例如，在 10 万条记录中，如果我使用 8 个字符的前缀，该前缀会被 10 条记录还是 100 条记录共享？

关于我的方法的一些更具体的问题:

1. 10 万条记录是否足以作为样本来选择合适的前缀大小？
2. 如果我确实没有使用前缀应用索引，我怀疑这在存储方面是错误的是否正确？
3. 预计必须直接查询表并仍从索引中受益的合理记录数是多少？

注意事项:

• 我的数据库是 MySQL(实际上是 Percona)
• SecureRandom 来自 Ruby
• 我假设 SecureRandom 的 URL 安全功能不会改变 base 64 输出的唯一性特征。

Answer 1

这只是一个随机数，对吗？不是加密。

不要使用前缀；尽管它会缩小一些索引大小，但在许多情况下会使索引的使用无效。的确，22 个字节比 8 个字符的字符串或 4 个字节的 INT 长。但不使用索引的缺点更糟。

默认值 16 (22) 足以使随机字符串具有足够的唯一性以避免意外冲突。

如果最大值为 22，则不要说 VARCHAR(255)。如果是固定长度，请说 CHAR(22) 或 VARCHAR(22) 如果您允许用户选择最多 16 位的长度。

请为该列说CHARACTER SET ascii COLLATE ascii_bin。这将避免 (1) utf8 的开销和 (2) 大小写折叠的错误。

如果您要为这些项目中的 10 亿个建立索引，那么就会出现严重的性能问题，如前所述here (尽管在不同的上下文中)。一百万行可能不是问题——这取决于索引何时变得大于 buffer_pool 中 RAM 中的缓存。

(如果我没记错公式，对于您描述的 8 个字符，300K 中有一个机会包含 300K 条目的索引将包含重复项。但这不是问题。)