数据类型选择与优化

由于觉得挺常见的，单独把数据类型从Schema的其他内容中拎出来讲了。

数据库的基础设计是很重要的。因为，有误差或者错误的设计是会使维护成本骤增的，并可能影响数据库性能。

一般情况有几条准则：

  1. 应该尽量选用可以正确存储数据的最小数据类型。

    因为，这会占用更少的空间（无论是内存、缓存、磁盘），并会减少处理时占用的CPU周期。

  2. 选用简单的。

    简单的数据类型处理时通常需要更少的处理周期（有时甚至能减少空间）。比如：整型不用字符串类型的来存储。这是很明显的，整型的比较仅仅是转换成二进制的简单比较，而字符串的比较呢，则涉及到对字符集及其内在规则等等。

  3. 尽量避免用NULL。

    也就是说在声明字段时声明为NOT NULL。因为，在书写SQL时有时会让我们不得不对NULL作额外的考虑。而且，索引会为NULL额外在每个记录下多加一个字节。当然，这更多只是一个优秀的习惯，将NULL转变成NOT NULL对性能的提升较小，在解决性能问题时，也不该是优先考虑优化的地方。

整数类型

整数类型可为：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别为8、16、24、32、64位存储空间。

那么它们的取值范围：

−2n−1≤i≤2n−1−1-2 ^{n-1} \leq i \leq 2^{n-1}-1

注：在表结构声明中，形如：int(8)、bigint(15)等是基本没有任何作用，如此声明既不影响存储的长度，也不能作用于查询输出。

浮点类型

DECIMAL、FLOAT、DOUBLE。

FLOAT和DOUBLE的长度，截取文档的说明：

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

DECIMAL:

MySQL stores DECIMAL values in binary format. See Section 12.23, “Precision Math”.

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

  • M is the maximum number of digits (the precision). It has a range of 1 to 65.

  • D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

For a full explanation of the internal format of DECIMAL values, see the file strings/decimal.c in a MySQL source distribution.

这三者都可指定长度，但是通常应使用FLOAT或DOUBLE，因为，

  • DECIMAL是存储为binary format的，在计算中会转换为DOUBLE。需要额外的开销。

  • DECIMAL更占位置。

  • 但是，DECIMAL在一些十分需要精度的场合可以应用。

字符串类型

这里主讲一下VARCHAR。VARCHAR是较常见的字符串存储形式。VARCHAR作为变长的字符串存储类型，能节省空间。其实没啥好说的。。

需要注意的点是：

  1. VARCHAR需要1个或两个字节存储长度信息。

  2. VARCHAR在例如VARCHAR(5)和VARCHAR(50)这两种不长度的声明中，性能是有差异的。通常，声明短的更好。因为，MySQL在一些内部值，如临时表，会分配固定大小的块来保存值。

枚举类型

通常，将例如：性别类等一些不同值的字符串值，使用枚举来存储会是一个不错的选择。

要注意，枚举实际存储的是一个整数值而非字符串。当用枚举类型来关联字符串查询，对性能有负面影响。

时间类型

主要讨论点，TIMESTAMP和DATETIME。除去特殊需要外，通常应尽量使用TIMESTAMP，因为它更节省空间。

其实，在我工作中有如下经验，假如你的程序是要求高性能运行时，应该使用TIMESTAMP，因为，TIMESTAMP更节省带宽，不仅是在与数据库集群进行大量数据传输，还在于和客户端进行传输，假如你的程序是持续、多节点、长期、高访问量下运行时，其所节约网络IO时间是可观的。而且，在我合作过的前端工程师中，大多更喜欢我返回TIMESTAMP类型。

其他类型就不叙述。

标识符列

标识符列，选用啥数据类型呢？

建议使用整数类型而不是UUID搭配字符串类型存储。

理由：

  1. 字符串耗空间。经测试，插入约千万数据后的，两种类型的主键所占磁盘空间整数类型比UUID字符串少一半。有兴趣的朋油可以自行测试。

  2. 计算查询效率。请阅读本文开头准则中的举例，不赘叙。

  3. UUID字符串如果作为缓存会使缓存的访问局部性原理失效。

说几点零碎的：

  • FLAG类型的列不太建议用bit类型。

  • 某些特殊数据，比如：IP地址，其实可用整数类型存储。又比如特定的业务场景：某些时候的权限标识可用数字存储方便运算、又比如打卡记录可利用INT是2的32的特性等等。这些都是需要平时自己留意的。

PreviousSchema与数据类型优化NextSchema设计相关优化

Last updated