数据类型选择与优化

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

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

一般情况有几条准则:

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

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

  2. 选用简单的。

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

  3. 尽量避免用NULL。

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

整数类型

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

那么它们的取值范围:

注:在表结构声明中,形如: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的特性等等。这些都是需要平时自己留意的。

Last updated