数据类型选择与优化
由于觉得挺常见的,单独把数据类型从Schema的其他内容中拎出来讲了。
Last updated
由于觉得挺常见的,单独把数据类型从Schema的其他内容中拎出来讲了。
Last updated
数据库的基础设计是很重要的。因为,有误差或者错误的设计是会使维护成本骤增的,并可能影响数据库性能。
一般情况有几条准则:
应该尽量选用可以正确存储数据的最小数据类型。
因为,这会占用更少的空间(无论是内存、缓存、磁盘),并会减少处理时占用的CPU周期。
选用简单的。
简单的数据类型处理时通常需要更少的处理周期(有时甚至能减少空间)。比如:整型不用字符串类型的来存储。这是很明显的,整型的比较仅仅是转换成二进制的简单比较,而字符串的比较呢,则涉及到对字符集及其内在规则等等。
尽量避免用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
andDOUBLE
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 isDECIMAL(
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 thanM
.For a full explanation of the internal format of
DECIMAL
values, see the filestrings/decimal.c
in a MySQL source distribution.
这三者都可指定长度,但是通常应使用FLOAT或DOUBLE,因为,
DECIMAL是存储为binary format的,在计算中会转换为DOUBLE。需要额外的开销。
DECIMAL更占位置。
但是,DECIMAL在一些十分需要精度的场合可以应用。
这里主讲一下VARCHAR。VARCHAR是较常见的字符串存储形式。VARCHAR作为变长的字符串存储类型,能节省空间。其实没啥好说的。。
需要注意的点是:
VARCHAR需要1个或两个字节存储长度信息。
VARCHAR在例如VARCHAR(5)和VARCHAR(50)这两种不长度的声明中,性能是有差异的。通常,声明短的更好。因为,MySQL在一些内部值,如临时表,会分配固定大小的块来保存值。
通常,将例如:性别类等一些不同值的字符串值,使用枚举来存储会是一个不错的选择。
要注意,枚举实际存储的是一个整数值而非字符串。当用枚举类型来关联字符串查询,对性能有负面影响。
主要讨论点,TIMESTAMP和DATETIME。除去特殊需要外,通常应尽量使用TIMESTAMP,因为它更节省空间。
其实,在我工作中有如下经验,假如你的程序是要求高性能运行时,应该使用TIMESTAMP,因为,TIMESTAMP更节省带宽,不仅是在与数据库集群进行大量数据传输,还在于和客户端进行传输,假如你的程序是持续、多节点、长期、高访问量下运行时,其所节约网络IO时间是可观的。而且,在我合作过的前端工程师中,大多更喜欢我返回TIMESTAMP类型。
其他类型就不叙述。
标识符列,选用啥数据类型呢?
建议使用整数类型而不是UUID搭配字符串类型存储。
理由:
字符串耗空间。经测试,插入约千万数据后的,两种类型的主键所占磁盘空间整数类型比UUID字符串少一半。有兴趣的朋油可以自行测试。
计算查询效率。请阅读本文开头准则中的举例,不赘叙。
UUID字符串如果作为缓存会使缓存的访问局部性原理失效。
FLAG类型的列不太建议用bit类型。
某些特殊数据,比如:IP地址,其实可用整数类型存储。又比如特定的业务场景:某些时候的权限标识可用数字存储方便运算、又比如打卡记录可利用INT是2的32的特性等等。这些都是需要平时自己留意的。