如何生成 MySQL 测试数据

KRISTIAN KÖHNTOPP 【hudson译】

2022年9月26号

在我工作的地方，有一个关于测试数据生成的持续讨论。目前，我们没有替换或更改任何用于测试环境的生产数据，也没有生成测试数据。

这是安全的也是合法的，因为生产数据是标记化的。也就是说，PII和PCI数据正被占位符令牌所取代，应用程序可以使用这些令牌通过特殊保护的访问服务访问实际受保护的数据。只有非常有限的人在处理受保护服务背后的数据。

在测试数据库中使用生产数据也很快，因为我们可以并行复制数据，或者我们使用写时重定向（SSD时代的“写时复制”）技术使可写快照可用。

假设我们想暂时改变这一点，然后

将生产数据复制到测试数据库时屏蔽数据
减少测试数据库中使用的数据量，同时保持引用完整性
生成任意大小的测试数据集，而不是使用生产数据，保持参照完整性和一些基线统计属性

屏蔽数据

假设我们将生产数据库复制到测试数据库中。然后，我们通过更改要屏蔽的每个数据项来准备该副本，例如，将其替换为sha（“secret”+original value）或其他替换标记。

kris@localhost [kris]> select sha(concat("secret", "Kristian Köhntopp"));
+---------------------------------------------+
| sha(concat("secret", "Kristian Köhntopp"))  |
+---------------------------------------------+
| 9697c8770a3def7475069f3be8b6f2a8e4c7ebf4    |
+---------------------------------------------+
1 row in set (0.00 sec)

为什么我们要使用哈希函数或本质上等效函数来实现这一点？当然，我们要保持引用的完整性。因此，每次出现“Kristian Köhntopp”都将替换为“9697c8770a3def7475069f3be8b6f2a8e4c7ebf4”，这是一个可预测且稳定的值，而不是第一次出现的随机数“17”，下一次出现的是另一个随机数“25342”。

就数据库工作而言，这意味着在复制数据后，我们将对每一行运行“update”，为每个更改创建一个binlog条目。如果我们更改的列被索引，则需要更新包含该列的索引。如果我们更改的列是主键，那么表中记录的物理位置也会更改，因为InnoDB将数据聚集在主键上。

简而言之，虽然复制数据很快，但屏蔽数据的成本要高得多，而且在任何硬件上都无法以线速运行(at line speed) 。

减少数据量

在公司历史的早期，大约15年前，一位同事通过选择生产数据的子集来创建较小的测试数据库，同时保持引用完整性不变。他失败了。

许多其他人后来也尝试过，我们每3到5年就有一个项目尝试这样做。他们也失败了。

每次尝试总是选择一个空数据库或生产中的所有数据。这是为什么？

让我们尝试一个简单的模型：我们有用户，我们有酒店，他们有n:m关系，即住宿关系。

“克里斯”住在“卡萨”酒店。我们从生产中选择Kris作为测试数据集，也选择“Casa”。其他人住在Casa，所以他们也被导入到测试集中，但他们也住在其他酒店，所以这些酒店也被导入，等等，回到两张表之间。经过3次反射、6次转换后，我们将整个生产数据库选择到测试集中。

我们的生产数据是相互关联的，保持生产数据的引用完整性意味着选择一个最终将选择全部。

限制时间可以有所帮助：我们只选择过去一周的数据或其他数据。但这也有其他含义，例如对数据分布的影响。此外，我们的生产数据在可用性运营方面存在严重的时间偏差 —— 上周发生了大量预订。

生成测试数据

生成垃圾数据很快，但仍比制作副本慢。这是因为从生产机器复制数据可以复制带有预先构建的现有索引的二进制文件，而生成垃圾数据等同于导入“mysqldump”。数据需要解析，更糟糕的是，需要构建所有索引。

虽然我们可以以每秒数百MB的速度复制数据，直到达到GB/s的范围，但导入数据的速度是一位数MB/s，低至几十MB/s。很大程度上取决于RAM的可用性、磁盘的速度、索引的数量以及输入数据是否按主键排序。

生成非垃圾数据也很困难，而且速度很慢。

您需要定义引用完整性约束列表，或者从架构中推断引用完整性。实际工作中，我们的模式很大，比单个数据库或服务大得多——用户服务（测试用户服务，测试用户数据）中的用户需要在预订服务（测试预订服务，测试预订）中引用，引用测试可用性和测试酒店商店中的酒店。

这意味着要么为每个测试创建并维护一致的第二个宇宙，要么跨服务从头开始创建。一个是对生产力的拖累（维护一致的宇宙需要很多工作），另一个是缓慢的。

不过，一致性并不是唯一的要求。如果您想测试验证代码，一致性很好（但我的测试名称不是utf8，而是来自ASCII的十六进制数子集！）。

但如果您想谈论性能，则会出现其他要求：

数据大小。如果生产数据的大小为2 TB，而测试集的大小为200 GB，则速度并不是线性的10倍快。这种关系是非线性的：在给定的硬件上，生产数据可能受到IO限制，因为工作集不适合内存，而测试数据可以将WSS调入内存。生产数据将产生与负载成比例的磁盘读取，测试数据将从内存中运行，并且在预热后没有读取I/O–应用了完全不同的性能模型。测试数据的性能测试对生产性能的预测值为零。
数据分布 生产数据和生产数据的访问受数据访问模式的影响，这些数据访问模式基本上是未知的、未记录在案，并且也在不断变化。一些用户是比正常旅行次数多100倍的鲸鱼。其他用户是经常旅行的人，旅行次数是正常值的10倍。一些用户是一次性的，在数据集中只出现一次。这些集合之间的关系是什么，它们对数据访问有什么影响？

例如：

我输掉了一次数据库基准测试

2006年，德国计算机杂志c’t在DVD租赁商店的web请求访问中描述了一个应用程序基准。参赛者应该使用他们希望使用的任何技术，在要求的输出和测试中他们将面临的URL请求中定义，来编写DVD租赁商店。我曾担任MySQL顾问，希望参与其中，为此，我使用MySQL将提供的模板放入一个web商店，并相应地调整商店和数据库。

我没有进入前十名。

这是因为我使用了一个真实的网络商店，对用户行为进行了真实的假设，包括缓存和其他东西。

使用的测试数据被生成，请求被平均分配：模拟DVD商店中的每张DVD被租用的可能性相等，每个用户租用的DVD数量相同。放入存储中的任何缓存都会溢出，并进入阈值，或者必须有足够的内存才能将整个存储保持在缓存中。

真正的DVD租赁店拥有前100种流行的图书，后面是以长串列表。缓存有帮助，但在测试中，缓存破坏了性能。

我输掉了另一次数据库基准测试

另一家德国计算机杂志有另一个数据库基准测试，它基本上以非常高的负载冲击了测试中的系统。不幸的是，这里的负载并不是均匀分布的，但是有几个键经常被使用，而很多键从未被请求过。实际上，负载生成器有大量线程，每个线程都在数据库中执行“它们的” 键 —— 线程1对表中的id 1，依此类推。

这使用了一定数量的热键，并在一些锁上等待得很快，但实际上并没有准确模拟任何吞吐量限制。如果您使用更多类似生产的负载来运行系统，它的总吞吐量将增加大约100倍。

生成用于测试的屏蔽或模拟数据的计算成本大约是复制生产数据的100倍。如果生产数据已经标记化，与花费的努力相比，其好处也值得怀疑。生成有效的测试数据在计算上非常昂贵，特别是在微服务体系结构中，在该体系结构中要跨服务边界维护引用完整性。

有效的测试数据在测试中不一定有用，尤其是在性能测试中。性能测试还特别依赖于影响锁定时间的数据访问模式、工作集大小和到达率分布。最后，实际的测试环境始终是生产环境，根据我个人的专业经验，使测试更具价值

最后，实际的测试环境始终是生产环境，根据我个人的专业经验，在生产环境中进行安全测试比创建准确的测试环境更有价值。