聊一聊数据库（MySQL）设计中的数据类型优化

良好的逻辑设计和物理设计事高性能的基石，在进行数据库设计时，我们应该要考虑到未来将会执行的查询语句，这就需要对各种因素进行权衡。本文将会聊一聊数据库（MySQL）设计中有关数据类型优化的一些内容。以下内容总结自《高性能 MysQL》。

选择优化的数据类型

原则

我们知道 MySQL 支持多种数据类型，通常情况下，很多数据类型都可以完成相同的工作，选择正确的数据类型对于高性能至关重要。在明确需要优化的数据类型前，我们需要先掌握几个原则，这些原则有助于我们作出更好的选择。

• 选择相对轻量的数据类型

这里的轻量指的是，在一般情况下，应该使用可以正确存储数据的最小数据类型。例如一个列中的最大值为 2020，那就没必要选择 INT 以及更大的数据类型。

• 简单就好

简单的数据类型通常意味着处理时需要更少的 CPU 周期。例如，整型比字符操作代价更低；存储日期/时间应该用内置的数据类型而不是字符串；存储 IP 地址时应该用整型而不是字符串。

• 避免 NULL

通常情况下，应该设置列为 NOT NULL。因为 NULL 会使某个列的索引、值等数据的处理变得复杂。而且可为 NULL 的列会使用更多的存储空间。

针对具体的数据类型

整数类型

对于整数，可选的数据类型有 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。分别使用 8、16，24，32，64 位存储空间。同时可选的属性有UNSIGNED，表示不允许有负值，这可以让正数的上限提高一倍。例如 TINYINT 的存储范围是-128～127，TINYINT UNSIGNED 的存储范围是0~255。

MySQL 可以为整数类型制定宽度，例如 INT(11)，但是并不会限制数值的合法范围，只会控制某些交互工具用来显示字符的个数，对于存储和计算来说，INT(1)和 INT(20)是相同的。即括号中的数字只是用于控制显示的字符数，和实际可以存储的字符数无关。

实数类型

实数是带有小数部分的数字，MySQL 中使用 DECIMAL 类型用于存储精确的小数，但是 CPU 不支持对 DECIMAL 的直接计算，因此 MySQL 服务器自身实现了 DECIMAL 的高精度计算。

与此同时 CPU 支持原生浮点计算，因此浮点数的运算速度相对 DECIMAL 会更快。浮点分为两种：FLOAT 和 DOUBLE。由于 DOUBLE 相对于 FLOAT 有更高的精度和更大的范围，MySQL 使用 DOUBLE 作为内部浮点计算的类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL，例如财务数据。数据量较大时，也可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

常用的是 VARCHAR 和 CHAR

VARCHAR

• 存储可变长字符串

• 比定长类型省空间，越短的字符串使用空间越少

• 使用 1 或 2 个额外字节记录字符串的长度。列的最大长度小于或等于 255 字节，使用 1 个字节表示，否则使用 2 个字节表示

CHAR

• 存储定长字符串

• 对于经常变更的数据，使用 CHAR 存储不易产生碎片

因此二者的使用场景也很明显：

VARCHAR 适合字符串列的最大长度比平均长度大很多，同时列的更新很少，以及列中的字符串使用复杂的字符集，每个字符都使用不同的字节数进行存储（UTF-8）。

CHAR 适合存储很短的字符串，或者所有值都接近同一个长度，例如存储密码的 MD5 值，或者用 CHAR(1)存储只有 Y 和 N 的值，因为 CHAR(1)需要一个字节，VARCHAR(1)需要两个字节（需要一个记录长度的额外字节）。

日期和时间

DATETIME

• 可以保存从 1001 年到 9999 年，精度为秒

• 将日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。

• 使用 8 个字节的存储空间

TIMESTAMP

• 保存了从 1970 年 1 月 1 日午夜以来的秒数

• 只使用 4 个字节存储，因此范围会小很多（最多表示从 1970 年到 2038 年）

• 和时区有关

因为 TIMESTAMP 是用 4 个字节存储，因此最多只能保存到 2038 年，这一点也造成了非常著名的2038 年问题。

选择标识符

标识符是用于标识列与其他值进行比较（例如关联操作中，通过标识列寻找其他列），标识列在选择数据类型时，应该跟关联表中的对应列一样的类型。

整数类型通常是最好的选择，效率高且可以自增长（例如主键）。如果可以，尽量避免使用字符串作为标识列，消耗空间，且查询速度慢。

特殊数据

有些特殊的数据需要用一些数据类型专门存储，例如存储 IP 地址应该用无符号整数，因为 IP 地址本质上是 32 位无符号数，并不是字符串，用小数点将地址分成四段只是方便阅读。

总结

想要提高 MySQL 的效率，可以做的功课非常多，数据库的数据类型优化也只是其中很小的一点，本文也只是挑出了常用的数据类型进行介绍。有兴趣的可以仔细阅读《高性能 MySQL》这本书，你可以在微信公众号「01 二进制」后台回复「高性能 MySQL」获取本书。

大道至简，尽可能将事情保持简单总是好的，MySQL 喜欢简单，希望使用数据库的你也会喜欢简单。