许多年前，我曾在一家远程信息处理公司工作，该公司负责接收来自全球数十万台设备的数据。在公司飞速发展的某个阶段，我们迎来了一位客户，为我们带来了大量新设备，并大幅提升了收入。这对公司的发展轨迹来说是一个重要时刻，但随之而来的数据处理量激增，也暴露了我们系统中的一个重大缺陷。

数据历史表（记录所有设备上发生的每一个事件）的 ID 列使用了 INT 数据类型创建，很快就将耗尽空间。

这个问题并非立即显现，但一旦该列空间耗尽，我们的整个系统就会陷入瘫痪。有趣的是，我们为此快速构建了一个名为“末日时钟”的工具，用来粗略计算这一问题发生的日期。如果我们当时预见到了这一点，就会在设计数据库时选择一种不同的类型，以便更轻松地应对这样的增长，但当然，我们决策的结果总是在事后才显得格外明显。

让我们来看看这个问题，以及在设置数据库时其他一些常见的数据库设计错误。

次优的数据类型

本文开头描述的场景凸显了选择一种足够大的数据类型以容纳现有数据以及可能经历的任何增长的重要性。

不过，这不仅仅适用于数值类型。例如，如果你尝试将一个包含 300 个字符的字符串写入 VARCHAR(255) 列，MySQL 在严格模式下（MySQL 的默认设置）会返回错误并拒绝写入。如果 MySQL 未处于严格模式，尝试将超出列长度的字符串数据插入该列会导致数据被截断，从而丢失可能重要的数据。

如果你想了解更多关于 MySQL 中不同字符串类型的信息，请查看我们详细解析可用文本选项的文章。

反过来，你也可能选择存储过多数据的列。虽然这不会像空间不足那样产生严重的负面影响，但过度配置列会带来存储和性能方面的影响。假设你有一个存储美国邮政编码的列，通常为五位数。你可能会默认使用 INT 作为列类型（存储 32 位整数），但这样会分配远超所需的存储空间。使用 SMALLINT 会是更好的选择，因为它存储 16 位整数，足以存储邮政编码。

这些只是为列选择不恰当数据类型的几个小例子。

缺失或冗余的索引

MySQL 中的索引通过构建一个单独的结构来加速数据访问，该结构经过优化，可以在查询条件与索引配置匹配时返回数据。

在设计快速数据库时，索引非常重要。当索引未被利用时，任何不使用分页或提供 LIMIT 的 SQL 查询都将对该表执行扫描。扫描时，MySQL 将从第一行开始读取，直到找到所有符合条件的行。如果你在一个特别大的表上有一个频繁使用的查询，反复扫描该表可能会对性能产生巨大的负面影响。

要了解缺失索引的实际影响，我们自己的 Aaron Francis 拯救了我们一位客户的 SaaS，并在 YouTube 上记录了他的过程！

另一方面，你也可能拥有过多的索引。

创建的每个索引都会占用额外的存储空间，因此拥有未使用或重复的索引会直接影响你为该存储支付的费用。每当数据被更新或插入到带有索引的表中时，MySQL 都需要更新这些索引（以及它们相关的统计信息），以确保它们无论是否被使用都是准确的。这可能是一项耗时的操作，并可能导致糟糕的用户体验。

如果你想了解更多关于如何有效使用索引的信息，我们在 MySQL for Developers 课程中有一整节内容专门介绍它们。

半结构化数据存储不当

在过去的 20 年里，利用 NoSQL 存储半结构化数据已经受到需要非常快速地处理大量数据的公司的青睐。

市场上有大量专用解决方案可用于存储这类数据。然而，MySQL 在这方面实际上也非常有能力。数据库中存储的大多数半结构化数据都表示为 JSON。最明显的存储方式是将字符串存储在 TEXT 列中，但这绝对不是最优的方式。

MySQL 有一种专用的 JSON 列类型，旨在以高效的二进制存储格式存储 JSON。

使用 JSON 而不是 TEXT 有几个关键优势。首先，InnoDB（最常用的 MySQL 数据库引擎）原生支持基于列中存储的 JSON 对象内的数据进行查询和过滤，从而无需在结果返回到应用程序代码后手动过滤。MySQL 还支持基于 JSON 内的数据构建索引。这可以实现快速搜索，使你能够更快地根据查询返回行。

如果您想了解更多关于 JSON 数据类型的信息，我们的博客上有几篇相关文章，包括 JSON 类型基础知识和如何为 JSON 列创建索引。

结论

那个空间即将耗尽的 ID 列后来怎么样了？幸运的是，该列类型是有符号整数，这意味着我们可以将其重新播种为 -2,147,483,648。通过将负数分配为 ID 并向 0 递增，这实际上将我们的 ID 容量翻了一番。这不是最优雅的解决方案，但它确实帮助我们避免了因更新数据库中几乎所有表的 schema 而需要的大量停机时间。

为增长设计数据库绝非易事，事情可能很快就会失控。我们在这里只讨论了一些潜在的数据库设计错误，但每个 MySQL 用例都是独特的，都有其自身的挑战。

转载声明：本文为转载整理，原文版权归原作者及原站点所有。

原文来源：PlanetScale Blog

原创地址：https://planetscale.com/blog/three-common-mysql-database-design-mistakes