设计表时，如何选择正确的数据类型

前言

假设现在有一个需求，需要创建一张orders表来存储客户的订单信息。假设表结构如下:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY, -- 订单ID，主键，自增
  customer_id INT NOT NULL,                -- 客户ID，假设已在其他表中定义
  order_date DATETIME NOT NULL,            -- 订单日期和时间
  total_amount DECIMAL(10, 2) NOT NULL,    -- 订单总金额，保留两位小数
);

这里需要设计一个status 字段，用来表示订单的当前状态。订单状态可以是以下几种：待支付、已支付、发货中、已完成、已取消。

这个status字段在业务逻辑中非常重要，因为它会频繁地用于查询，更新等，方便用户查看自己处于不同状态的订单。

此时我们应该好好设计该字段，在保证其满足基本业务需求的同时，性能和扩展性这些方面也要充分考虑，避免之后较大的维护成本以及性能开销。

回到这个字段的设计来说，现在我们就有几种不同的数据类型选择来存储这个status字段，而且也能够基本得满足业务要求，比如VARCHAR 类型，ENUM 类型，TINYINT 类型，具体设计如下:

VARCHAR

我们可以选择使用字符串类型VARCHAR来直接存储状态文本（如"待支付"、"已支付"等）。

ENUM

我们可以使用枚举类型ENUM('待支付', '已支付', '发货中', '已完成', '已取消')来存储这些状态。

TINYINT

我们可以选择使用较小的整数类型TINYINT，并为每种状态分配一个数字代码(如1=待支付，2=已支付等）。

这种情况下，status 字段时应该使用VARCHAR，ENUM 还是TINYINT 类型呢?

在平时开发设计时，我们总是不可避免说会遇到类似这种选择。这种情况下我们应该怎么抉择呢? 能从哪些方面考虑呢？

数据类型选择的原则

在 MySQL 数据表设计时，选择合适的数据类型对于提高数据库的性能是至关重要且基础的。下面介绍一些简单的原则，帮助我们在遇到选择时，能过做出更好的选择。

更小的更好

选择能够在满足需求的前提下，占用最小存储空间的数据类型。

在执行查询和其他操作时会将数据加载到内存中，使用较小的数据类型可以减少内存使用，从而允许更多的数据同时驻留在内存中，提高数据的处理速度。同时它们占用更少的磁盘、CPU缓存，并且处理时需要的CPU周期也更少。

同时，数据类型的大小也会对索引的性能产生影响。较小的数据类型也可以提高索引的效率，字段占据空间越小，该字段对应的索引更小，可以提高索引的查找速度并减少磁盘I/O操作。这对查询性能有显著影响，尤其是对于大量数据和高负载的系统来说。

举例来说，如果你知道一个字段的值不会超过 255，那么使用 TINYINT 而不是 INT，这个不管是在存储空间还是查询效率上来说，都是TINYINT的性能更好。

但是要确保没有低估需要存储的值的范围，因为扩展数据类型的范围是一个非常耗时和痛苦的操作，也降低了系统的可维护性和扩展性，这样子就得不偿失了。

优先使用内建类型

在数据库设计中，应该优先使用数据库的内置类型的表示，而不是一些通用类型。

优先使用数据库内建类型有以下好处:

性能方面，数据库都会对内建数据类型进行了优化，以提供更好的存储和检索性能。例如，内建的数值和日期类型通常比通用的字符串类型在索引、排序和比较操作中表现得更好。

数据完整性方面，内建数据类型通常包括数据验证功能，可以在数据插入或更新时自动进行类型检查。这有助于防止无效数据的输入，从而维护数据的一致性和准确性。举例来说，DATE类型的字段将自动拒绝任何不符合日期格式的数据。

具有更高的存储效率。因为它们是针对所存储数据的性质量身定做的。例如，整数类型（如 INT 或 BIGINT）通常比等效的字符串表示（如数字的文本形式）占用更少的存储空间。

简化查询。内建数据类型可以简化查询语句的编写，因为它们不需要额外的转换或格式化函数。例如，使用 DATE 类型可以直接比较日期，而不需要将字符串转换为日期。

因此，在数据库设计中，我们应该优先使用系统内建的数据类型，只有在内建字段不满足需求的情况下，才考虑一些通用数据类型来表示。

优先使用简单类型

简单和复杂数据类型的界定并没有一个严格的标准，这些术语更多地是相对的，并且取决于上下文。

这里的简单和复杂是相对于操作和处理这些数据类型所需的资源来说的。

当我们说简单类型时，我们通常是指这些类型在数据库中的处理更加直接和高效。而复杂类型则可能需要更多的处理步骤，更多的资源，或者更复杂的内部表示。

当我们在数据库设计时，某个字段类型有多种选择，那么其中需要更多的存储空间，查询排序等操作更为复杂，性能更为低下的，此时该字段类型在该场景下就被界定为复杂类型。

这里举个例子说明一下，假设我们在用户信息表中有一个字段用于存储性别。性别通常只有几个固定的选项，例如“男”、“女”、“未指定”等。

在这种情况下，可以使用整数来表示，约定一个小的整数来表示性别，比如，0：未指定，1：男，2：女。

也可以使用字符串类型 (VARCHAR) 来存储性别信息。这种方式在表达上更直观，但它比简单数据类型更复杂、更耗费资源。

在这个场景下，字符串类型相对于整数类型来说，其需要占用更多的存储空间，查询排序时也需要耗费更多的cpu和内存资源，此时字符串类型将被定义为复杂类型，整数类型相对的就是简单类型了。

在设计数据库时，考虑使用简单数据类型而不是复杂数据类型可以在确保足够表达能力的同时，提高数据库操作的效率。

考虑数据精度

在数据库设计时，选择合适的数据精度是非常重要的。选择过高或过低的精度都会带来问题：过高的精度可能会导致不必要的存储空间浪费和性能下降，而过低的精度则可能导致数据失真，无法满足业务需求。

经常的一个常见误区是数据精度越大越好，但是业务场景并不需要那么精确，此时使用过高的精度意味着需要更多的存储空间，这在大型数据库中尤其成问题，可能导致额外的存储成本。更高的精度可能会导致计算速度变慢，尤其是在进行数学运算、排序和索引操作时。

此时应该是从业务需求出发，选择最为合适的数据精度，既满足了业务要求，也避免了存储空间的浪费和查询性能的下降。

考虑扩展性

数据库设计时，需要考虑到后续需求的变化，随着时间的推移，数据库设计应该能够很好得适应业务的增长和变化，而不需要进行昂贵的重构。

上面提到了数据类型应该越小越好，但是这个需要考虑后续的扩展性。比如，当选择数值类型时，如果预计记录数量会非常大，就应该选择 BIGINT 而不是 INT。对于字符串类型，此时应该分配一个足够容纳未来可能增长的内容长度的大小。

再比如字符串类型的选择，如果字符串长度在当前是不可预见的，此时使用 VARCHAR 类型相对于 CHAR 类型就更为灵活，它可以存储可变长度的字符串，节省空间并且可以容纳未来长度的变化。

总之，当数据库设计时，要充分考虑到后续可能的变化，避免在数据库需要支持更多数据和更复杂查询时进行成本高昂的重构。

选择什么类型呢

回到文章开头的问题，我们就有几种不同的数据类型选择来存储这个status字段，如VARCHAR 类型，ENUM 类型，TINYINT 类型，此时我们应该使用哪个字段类型呢?

这里我们使用上面几个简单原则来帮助我们做出判断，从而帮助我们做出更好的选择。

数据类型/考虑因素	VARCHAR	TINYINT	ENUM
占用空间	由于状态字符串的长度可能不一致，`VARCHAR` 可能会使用更多存储空间。	`TINYINT`需要非常少的存储空间（通常是1个字节）。	`ENUM`类型相对于`VARCHAR`类型更节省空间，因为它内部使用数值来表示每个可能的值。
内建类型	不涉及	不涉及	不涉及
简单类型	字符串索引排序效率相对更慢	查询性能高，存储效率高,相对 `VARCHAR` 为简单类型	底层数据表示上实际上是使用整数索引来存储的。查询性能高，存储效率高，相对`VARCHAR` 为简单类型
数据精度	不涉及	不涉及	不涉及
扩展性	新增一个枚举简单，可扩展性强	可容纳127个状态，满足未来可见的变化，可扩展性强	可扩展性差，每新增一个枚举，都需要重建表

基于以上分析，可以看出来，相比于VARCHAR 和 ENUM 类型，TINYINT 是一个合适的选择，其提供了最好的存储效率，数据处理性能，可扩展性也比较强，完全能够适应未来业务需求的变化。

基于此，status 字段类型最终选择 TINYINT 字段类型。最终的表结构如下:

CREATE TABLE orders (   
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- 订单ID，主键，自增
    customer_id INT NOT NULL,                -- 客户ID，假设已在其他表中定义
    order_date DATETIME NOT NULL,            -- 订单日期和时间
    total_amount DECIMAL(10, 2) NOT NULL,    -- 订单总金额，保留两位小数
    status TINYINT NOT NULL,                 -- 订单状态，使用TINYINT类型
);

总结

MySQL 支持的数据类型非常多，在合适的场景下，选择正确的数据类型对于获得高性能至关重要。

本文讲述了几个简单的原则，如选择最小存储空间的数据类型，使用内建类型，优先使用简单类型等。

这几个原则的核心思想，都是尽量选择更为简单的数据类型，减少磁盘空间或者cpu资源的浪费，从而获得更好的性能。

通过这几个简单的原则，希望能够帮助你在数据库设计时做出更好的选择。

posted @ 2024-03-10 08:09 菜鸟额阅读(535) 评论(0) 编辑收藏举报

刷新页面返回顶部

菜鸟额