Scheme与数据类型优化

39 阅读9分钟

Schema与数据类型优化

选择优化的数据类型

  • 一般情况下,应该尽量使用可存储数据的最小数据类型。
  • 选择简单的数据类型来存储数据,因为简单数据类型通常需要更少的CPU周期。如用mysql内置数据类型或时间戳来存储日期和时间;用整形来存储IP地址等。
  • 通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。特别是计划在列上建索引,就应该尽量避免设计成可为NULL的列。

整数类型

  • 可以使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别使用8、16、24、32、64位存储空间。他们可以存储的值的范围从-2^(N-1)到2^(N-1) - 1,其中N是存储空间的位数。

  • 整数类型可选UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。

  • INT(11),指的是指定整数类型宽度,它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。

实数类型

  • 实数类型一般用DECIMAL来存储,例如,DECIMAL(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节。
  • 因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL—例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL将需要存储的货币单位根据小数的位数乘以相应的倍数即可

字符串类型

  • VARCHAR:用于存储可变长字符串它比定长类型更节省空间,因为它仅使用必要的空间(例如:越短的字符串使用越少的空间)。VARCHAR需要使用1或2个额外字节记录字符串长度:如列最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。假设采用latin1字符集,VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节,因为需要2个字节存储长度信息。
    • 但是如果update时使行变得比原来更长,并且页内没有更多的空间可以存储,在这种情况下,InnoDB会分裂页来使行可以放进页内。
    • **使用场景:**字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
  • CHAR:用于存储定长字符串,用CHAR存储值时,MySQL会删除所有的末尾空格
    • **使用场景:**①适合存储很短的字符串,或者所有值都接近同一个长度。如存储MD5值;②对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片;③对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率,如CHAR(1)来存储Y和N的值,如果采用单字节字符集只需要一个字节,但是VARCHAR(1)却需要两个字节,因为还有一个记录长度的额外字节。

1675167615166.png

  • 填充和截取空格的行为在不同存储引擎都是一样的,因为这是在MySQL服务层进行处理的。

  • BLOB和TEXT:BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符串方式存储。

    • 字符类型是:TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT
    • 二进制类型是:TINYBLOB, SMALLBLOB,BLOB,MEDIUMBOLB,LONGBLOB

与其他类型不同,MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时,InnoDB会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要1—4个字节存储一个指针,然后在外部存储区域存储实际的值。

使用枚举(ENUM)代替字符串类型

枚举最不好的地方是,字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE。因此对于一系列未来可能会改变的字符串,使用枚举不是一个好主意,除非能接受只在列表末尾添加元素,这样在MySQL5.1中就可以不用重建整个表来完成修改。

1675234848730.png

日常开发中,我们一般直接使用整形来存储对应的枚举值。

日期和时间类型

  • DATETIME:能保存大范围的值,从1001年到9999年,精确度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。

  • TIMESTAMP:保存了从1970.1.1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间。因此他的范围比DATETIME小得多:只能表示从1970年到2038年

    • TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统,以及客户端连接都有时区设置。因此,存储值为0的TIMESTAMP在美国东部时区显示为“1969-12-31 19:00:00”,与格林尼治时间差5个小时,这也体现了TIMESTAMP提供的值与时区有关系。

    • TIMESTAMP如果插入时没有指定值,MySQL则设置这个列的值为当前时间。最后,TIMESTAMP列默认为NOT NULL,这也和其他的数据类型不一样。

    • 除了特殊行为之外,通常也应该尽量使用TIMESTAMP,因为它比DATETIME空间效率更高。有时候人们会将UNIX时间存储为整数值,但这不会带来任何收益。用整数保存时间的格式通常不方便处理,所以不推荐这样做。

    • 如果需要存储比秒更小粒度的日期和时间值怎么办?通常可以使用BIGINT类型存储毫秒级别的时间戳

建表选择合适的数据类型

  • 通过列关联的表,在为列选择数据类型时,应该选择跟关联表中的对应列一样的数据类型(因为不同数据类型的列关联会影像性能)
  • 选择列的数据类型时,不仅仅要考虑存储类型,还要考虑MySQL对这种类型怎么执行计算和比较。例如:MySQL在内部使用整数存储ENUM和SET类型,然后在做比较操作时转换为字符串。
  • 在可以满足值的范围的需求,并且预留未来增长空间的前提下,应该选择最小的数据类型。例如:用state_id存储中国各州的名字,就不需要成千上万个值,所以不需要使用INT,TINYINT足够存储。
  • 如果可能,应避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢。
    • 对于完全随机的字符串也需要多加注意,例如MD5()、SHA1()或者UUID(),应尽量避免使用。原因如下:
      • ①因为插入会随机写到索引的不同位置,所以使得insert语句更慢。会导致随机IO,以及产生内存碎片
      • ②select语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方
      • 随机值导致缓存对所有类型的查询语句效果都很差,因为使缓存l赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”,那么缓存任何一部分特定数据到内存都没有好处;如果工作集比内存大,缓存将会有很多刷新和不命中

MySQL schema设计中的陷阱

  • 太多的列
  • 太多的关联(其实现在都不推荐使用外键关联了),人为知道两个表的列存储关联就行
  • 不轻易使用枚举(枚举后期修改需要alter table)
  • 变相的枚举(枚举用set类型代替)
  • 列的值不轻易设置为null,要给出默认值

范式和反范式

对于任何给定的数据通常都有多种表示方法,从我完全的范式化到完全的反范式化,以及两者的折中,在范式化的数据库中,每个事实数据会出现并且只出现一次。相反,在反范式化的数据库中,信息是冗余的,可能会存储在多个地方。

  • 范式优缺点

    • 优点:

      • 范式化的更新操作通常比反范式化要快。
      • 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据
      • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
      • 还少有多余的数据意味着检索列表数据时更少需要distinct或者group by语句。
    • 缺点:

      • 范式化设计的schema的缺点是通常需要关联。稍微负责的语句可能需要关联多次,这不断代价昂贵,也可能使一些索引策略无效。
  • 反范式优缺点

    • 反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。(因为关联太多的索引会导致效率降低)