从SQL Server到MySql(2) : MySql 数据类

1. 优化数据结构

• 尽量避免null.
  • 可为NULL的列使索引,索引统计和值比较都更加复杂.
  • 它需要更多的存储空间, 在MySql 里需要特殊的处理.
  • 索引可为NULL的列时,每个索引记录需要一个额外的字节. (InnoDB使用单独的bit来存储NULL值,会稍微好点).
• 更小的通常更好. 它们占用更少的磁盘,内存和缓存, 处理时需要的CPU 周期也更少.
• 简单就好. 简单类型的操作需要更少的CPU周期.
• 两步进行数据类型的选择:
  • 确定合适的大类型: 数字,字符串,时间等.
  • 根据存储的长度和范围, 允许的精度, 需要的物理空间 来选择具体的类型.

2. 整数类型

2.1 整数类型

• 不同的具体类型(包含无符号数)决定的是如何在内存和磁盘中保存数据,并不影响性能,整数计算都使用64位的BIGINT.
• 整型的宽度,例如INT(11)中的11, 只是规定了某些交互工具中, 用来显式字符的个数, 并不影响存储和值范围.

2.2 实数类型

• 使用DOUBLE作为内部浮点类型的计算类型.
• Decimal需要额外的空间和计算开销. 仅在需要对小数进行精确计算时才使用. 当数据量大时可使用BIGINT替代.

3. 字符串类型

• VARChar 需要额外的1或2个字节(根据最大长度的大小)记录长度.
  • 比定长类型更节省空间, 因为仅使用必要的空间.
  • 若表使用Row_Format = Fixed 创建时,每一行都使用定长存储,会浪费空间.
  • Update 时若造成行更长, 可能会导致碎片.
  • 适用场景: 字符串列的最大长度比平均长度大很多. 列更新少(不易产生碎片). 采用的字符集中每个字符都使用不同的字节数进行存储.
• CHAR 适合较短的字符串, 或所有值都接近一个长度.
• Binary,VarBinary 在需要存储二进制数据时, 其比较是按字节逐次比较,更加简单高效.
• Blob和Text 用于存储很大的字符串.
  • 其值会被当成独立的对象处理. 当值很大时,会使用外部空间存储,内部存储指针.
  • 其列排序只对最前max_sort_length字节而非整个字节排序.
  • 不能对列全部长度的字符串进行索引.
  • Memory�引擎不支持Blob和Text. 若查询使用了他们, 会造成磁盘临时表的使用.
  • 应避免使用它们. 如使用Substring将列值转换为字符串.

4. 枚举

• 有时可以使用枚举类代替常用的字符串类型.
• 问题: 列表是固定的,添加或删除必须使用alter table. 对于未来会变更的情况,尽量不使用,除非只在末尾添加元素.
• 存储枚举时非常紧凑, 会根据列表值的数量压缩到1/2字节中. 在内部将值在列表中的位置保存为整数. 并在.frm文件中保存'数字-字符串'映射关系的查找表.
• 尽量避免使用数字作为枚举常量, 这样会有双重性.
• 按照内部存储的整数而不是定义的字符串的值进行排序的(也就是定义值时的顺序).

5. 日期和时间类型

• TimeStamp 保存了从1970 年以来的秒数.
  • 只使用4个字节存储,所以只能到2038 年.
  • 等同于UNIX时间戳.
    • from_unixtime()/unix_timestamp()进行日期和Unix时间戳的转换.
  • 显示值依赖于时区.
  • TimeStamp 列默认为Not Null.
  • 其空间效率更高,所以应尽量使用TimeStamp.
• DateTime
  • 可保存从1001到9999 年, 精度为秒.
  • 将时间和日期封装到格式为YYYYMMDDHHMMSS 的整数中,与时区无关.
  • 使用8个字节存储.

6. 位数据类型

• BIT
  • 被当做字符串类型,而不是数字类型.
  • 检索出的结果是包含0和1的字串.
  • 但在数字上下文中得到的是字串对应的数字值. 所以会产生二义性.
• SET
  • 若需要保存很多true/false 值, 可以合并这些列到一个SET中.
    • 如ACL: SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE').
  • 内部以一系列打包的位的集合来表示,从而有效地利用了存储空间.
  • 问题是改变列定义(交换可读和可写的位置)�时的代价较大, 且无法在SET列上通过索引查找.
• 在整数列上进行按位操作.
  • 一种替代SET的方式是使用一个整数包装一系列的位. 如把8个位包装到一个TINYINT中,并按位操作来使用.
  • 好处是可以不适用Alter Table 改变字段代表的"枚举"值.
  • 缺点是查询语句更难写, 并且难以理解.

7. Schema 的设计陷阱

• 太大的列.
  • 存储引擎API需要在服务器层和存储引擎层通过缓存格式拷贝数据, 然后在服务器层将缓存内容解码成各个列. 从行缓存中将编码过的列转换成行数据结构的代码很高.
  • 定长行结构与服务器层的行结构正好匹配, 所以不需要转换. 而变长结构总是需要昂贵的转换.
• 太多的关联
  • 所谓的"实体-属性-值(EVA)"是一种糟糕的设计模式. 它需要过多的自关联.
• 全能的枚举
  • 类似enum('','0','1',....'31'). 会造成新增值时的alter table.
  • 应该使用整数作为外键关联到字典表或查找表来查找具体值.
• 多数情况下,应该避免使用NULL值,而使用替代(0,特殊值). 但不要极端.
  • MySql会在索引中存储NULL值.