良好的逻辑设计和物理设计事高性能的基石,在进行数据库设计时,我们应该要考虑到未来将会执行的查询语句,这就需要对各种因素进行权衡。本文将会聊一聊数据库(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 喜欢简单,希望使用数据库的你也会喜欢简单。