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

Question

我有一个DB列(类型为varchar(255))，它存储由SecureRandom.urlsafe_base64生成的URL安全基64字符串。方法调用使用默认值，因此结果应该是 in length。

base64值用于在用户访问站点时查找记录，以屏蔽DB ID。由于这种查找，我需要为该列建立索引，但是我不想对整个列进行索引，因为这可能会导致存储效率低下。

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

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

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

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

1. 一个样本的100 k记录是否足以选择一个好的前缀大小？
2. 如果我不使用前缀而应用索引，我是否怀疑这在存储方面是错误的？
3. 有多少合理的记录需要直接查询表，并且仍然可以从索引中获益？

Notes

• 我的数据库是MySQL (实际上是Percona)
• SecureRandom来自Ruby
• 我假设SecureRandom的URL安全特性并没有改变基本64输出的唯一性特征。

Answer 1

结果

我决定按照问题中概述的步骤去做。因此，我得到了选择索引前缀所需的信息。

这一试验产生了两个变化：

1. 我将列的大小从255缩减为22 ( @Rick也建议)。请参阅问题中的详细信息，以了解为什么数据不超过22个字符。
2. 我在列中添加了一个前缀为8个字符的索引。字符集是utf8mb4，所以键大小与前缀中的字符数不成正比。

实验细节

我测试了长度为1、2、4、8和16个字符的前缀，并使用EXPLAIN查看列上的查找功能。我还测试了没有索引作为基线。我的实验是基于一套100 K的记录。我本可以用更多的记录进行测试，但就我的目的而言，没有必要得到更精确的测试。

下面是这个实验的一些亮点：

• 在没有索引的情况下，SecureRandom列上的查询会执行表扫描。
• 使用1字符前缀，MySQL必须扫描大约3200条100 K的记录。
• 使用2个字符的前缀，MySQL必须扫描79条记录。
• 使用4个字符前缀，MySQL立即获得准确的记录，而无需对表进行查找。

除了4个字符前缀之外，数据集为100 k，基于SecureRandom.urlsafe_base64的特性，我无法获得更高的精度。根据生产中表的当前大小和对其增长率的粗略认识，我们决定，在节省磁盘和内存使用的同时，8个字符的前缀将在很大程度上为我们服务。

备选方案

通过这一探索，我还了解了MySQL的索引选择性特性。这将允许使用相同的100 k记录样本集来测试为每个前缀长度检索的记录数，而不必在每次测试之前运行迁移来添加/删除不同的索引。例如，要测试4个字符前缀的选择性：

SELECT count(*) AS count, LEFT(uuid_column, 4) AS prefix
  FROM string_prefix_test GROUP BY count DESC LIMIT 10;

更多关于指数选择性的细节可以在2012年版的高性能MySQL (P 160 )中找到。

未来工作

接下来，我将研究varchar列索引的前缀，其数据不具有SecureRandom.urlsafe_base64的唯一性特征。这将是一个更大的挑战，因为获得一个有代表性的数据集更难。

Answer 2

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

不要使用前缀；虽然它会缩小索引的大小，但在许多情况下，它将使索引的使用无效。是的，22个字节比一个8字符字符串或一个4字节INT长.但未能使用该指数的负面影响更为严重。

默认的16 (22)足够使随机字符串足够唯一以避免意外碰撞。

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

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

如果要对这些项中的10亿项进行索引，那么就会出现严重的性能问题，正如http://mysql.rjweb.org/doc.php/uuid所讨论的那样(尽管是在不同的上下文中)。一百万行可能不是一个问题--它取决于索引何时变得比buffer_pool中的内存中缓存的要大。

(如果我没记错的话，对于你所描述的8个字符，在300 K的索引中，有300 K条目的索引就有一个重复的可能。但这不是问题所在。)