选择优化的数据类型

  • 更小的通常更好

尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期也更少

  • 简单就好

简单数据类型的操作通常需要更少的CPU周期。例如，整形比字符操作代价更低，因为字符集和校对规则使字符比较比整形比较更麻烦

  • 尽量避免使用null

通常情况下最好指定列为not null，除非真的需要存储null值。
因为查询中包含可为null的列， 对mysql更难优化，可为null的列使得索引，索引统计和值比较都更复杂，可为null的列会使用更多的存储空间，在mysql里也需要特殊处理，当可为null的列被索引时，每个索引的记录需要一个额外的字节。如果计划在列上建索引，就应该避免设计成可为null的列。

整数类型

整数类型: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, 分别使用8，16， 24， 32, 64位存储空间， 可以存储的值从【-2的（N-1）次方】到【2的（N-1）次方-1】，其中N是存储空间的位数；整数类型决定mysql是如何在内存和磁盘中保存数据的，整数计算一般用64位的BIGINT整数（一些聚合函数使用DECIMAL或DOUBLE例外）

整数类型可选的有UNSIGNED（无符号）属性，表示不允许负值，这大致可以使正数的上限提高一倍，比如TINYINT的存储范围是-128-127，TINYINT UNSIGNED的存储范围是0-255；有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型

mysql可以为整数类型指定宽度，比如INT(11), 对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了mysql的一些交互工具（比如命令行客户端）用来显示字符的个数，对于存储和计算来说，INT(1)和INT(20)是相同的

与java中数据类型对应(个人总结)
image.png
因此通用设计为TINYINT(1)存储布尔, TINYINT(3)存储枚举, INT(11)存储Integer类型, BIGINT(20)存储Long类型.

实数类型

实数是带有小数部分的数字, 也可以用DECIMAL存储比BIGINT还大的整数.

FLOAT和DOUBLE类型支持使用标准的浮点类型进行近似运算, DECIMAL类型用于存储精确的小数, 在mysql5.0和更高版本, DECIMAL类型支持精确计算.

浮点和DECIMAL类型都可以指定精度, 对于DECIMAL列, 可以指定小数点前后允许的最大位数, MYSQL5.0和更高版本将数字打包保存到一个二进制字符串中(每个字节存9个数字). 例如DECIMAL(18,9)小数点两边各存9个数字, 占9个字节, 小数点本身占一个字节

浮点类型在存储同样范围的值时, 通常比DECIMAL使用更少的空间. FLOAT使用4个字节, DOUBLE使用8个字节, 相比FLOAT具有更高的精度和更大的范围. 和整数类型一样, 能选择的只是存储类型, MYSQL使用DOUBLE作为内部浮点计算的类型.

因为需要额外的空间和计算开销, 所以应该尽量只在对小数进行精确计算时才使用DECIMAL, 例如存储财务数据, 但在数据量比较大的时候, 可以考虑使用BIGINT代替DECIMAL, 例如要存储财务数据精确到万分之一分, 则可以把所有金额乘以一百万, 然后将结果存在BIGINT里, 这样可以同时避免浮点数存储计算不精确和DICEMAL精确计算代价高的问题

字符串类型

  • VARCHAR类型用于存储可变长字符串, 比定长更节省空间, 因为它仅使用必要的空间.

适合使用VARCHAR的情况: 字符串列的最大长度比平均长度大很多, 列的更新很少, 所以碎片不是问题; 使用了像UTF-8这样复杂的字符集, 每个字符都使用不同的字节数进行存储

  • CHAR类型是定长的, MYSQL总根据定义的长度分配足够的空间

适合存储很短的字符串, 或者所有值都接近同一个长度, 例如, CHAR非常适合存储密码的MD5值, 对于经常变更的数据, CHAR也比VARCHAR更好, 因为定长的CHAR类型不容易产生碎片, 对于非常短的列, CHAR也比VARCHAR在存储空间上更有效率
image.png

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

日期和时间类型

保存日期和时间时, 使用DATETIME还是TIMESTAMP

  • DATETIME

能保存大范围的值, 从1001年到9999年, 精度为秒. 它将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中, 与时区无关. 使用8个字节的存储空间. 默认情况下, MYSQL以一种可排序的无歧义的格式显示DATETIME的值, 比如”2008-01-05 22:12:12”;

  • TIMESTAMP

保存了从1970年1月1日午夜以来的秒数, 它和UNIX时间戳相同. 只使用4个字节的存储空间, 因此存储范围比DATETIME小得多, 只能表示从1970年到2038年.TIMESTAMP显示的值依赖于时区, MYSQL服务器, 操作系统以及客户端连接都有时区设置. 此外, TIMESTAMP列默认为NOT NULL, 插入记录时, MYSQL默认会更新第一个TIMESTAMP列的值.

除了特殊行为之外, 通常应该尽量使用TIMESTAMP, 因为它比DATETIME空间效率更高.

位数据类型

  • BIT, MYSQL将BIT当做字符串处理, 但是在数字上下文的场景中检索时, 却是将位字符串转换成数字. 对于大部分应用, 最好避免使用这种类型
  • SET, 如果要保存很多的true/false值, 可以考虑合并这些列到一个SET数据类型, 它在MYSQL内部是以一系列打包的位的集合来表示的; 另一种替代SET的方式是使用一个整数来包装一系列的位, 例如可以将8个位包装到一个TINYINT中, 并且按位操作来使用

选择标识符

为标识列选择数据类型时, 应该跟关联表中的对应列是一样的类型
对比:

  • 整数, 通常是标识列最好的选择, 因为很快且可以自增
  • 字符串类型, 应该避免, 因为很消耗空间, 而且通常比数字类型慢, 尤其MYISAM默认对字符串使用压缩索引, 会导致查询慢很多, 最多有6倍的性能下降.

对于完全随机的字符串也要多加注意, 例如MD5(),SHA1()或者UUID()产生的字符串, 这些函数生成的新值会任意分布在很大的空间内, 这会导致INSERT或SELECT语句变得很慢

  • 插入会随机写到索引的不同位置, 使得INSERT语句慢, 会导致页分裂, 磁盘随机访问, 以及对于聚簇存储引擎产生聚簇索引碎片
  • SELECT语句更慢, 因为逻辑上相邻的行会分布在磁盘和内存的不同地方
  • 随机值导致缓存对所有类型的查询语句效果都很差, 因为会使得缓存赖以工作的访问局部性原理失效, 如果整个数据集都一样的”热”, 那么缓存任何一部分特定数据到内存都没有好处, 如果工作集比内存大, 缓存将会有很多刷新和不命中

如果存储UUID值, 则应该移除”-“符号, 或者更好的做法是用UNHEX()函数转化UUID值为16字节的数字, 并且存储在一个BINARY(16)的列中, 检索时可以通过HEX()函数来格式化为16进制格式

特殊数据类型

某些类型的数据并不直接与内置类型一致
image.png

MySQL schema 设计中的陷阱

  • 太多的列

MYSQL存储引擎的API在工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据, 然后在服务器层将缓冲内容解码成各个列, 从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的, 依赖于列的数量, 因此太多的列会对性能造成影响

  • 太多的关联

单个查询最好在12个表内做关联

  • 全能的枚举
  • 变相的枚举
  • 非此发明的NULL

非NULL的原则不要走极端

范式和反范式

三大范式:
1.第一范式（1NF）列不可再分
2.第二范式（2NF）属性完全依赖于主键
3.第三范式（3NF）属性不依赖于其它非主属性, 属性直接依赖于主键

范式的优点:

  • 范式化的更新操作通常比反范式要快
  • 当数据被较好的范式化时, 就只有很少或者没有重复数据, 所以只需要修改更少的数据
  • 范式化的表通常更小, 所以放到内存中操作更快
  • 很少有多余的数据意味着很少的DISTINCT或GROUP BY语句

范式的缺点:

  • 稍微复杂一点的查询语句通常都要关联, 不但代价昂贵, 也可能使一些索引策略失效

总结

image.png