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列上通过索引查找.
- 若需要保存很多true/false 值, 可以合并这些列到一个SET中.
- 在整数列上进行按位操作.
- 一种替代SET的方式是使用一个整数包装一系列的位. 如把8个位包装到一个TINYINT中,并按位操作来使用.
- 好处是可以不适用Alter Table 改变字段代表的"枚举"值.
- 缺点是查询语句更难写, 并且难以理解.
7. Schema 的设计陷阱
- 太大的列.
- 存储引擎API需要在服务器层和存储引擎层通过缓存格式拷贝数据, 然后在服务器层将缓存内容解码成各个列. 从行缓存中将编码过的列转换成行数据结构的代码很高.
- 定长行结构与服务器层的行结构正好匹配, 所以不需要转换. 而变长结构总是需要昂贵的转换.
- 太多的关联
- 所谓的"实体-属性-值(EVA)"是一种糟糕的设计模式. 它需要过多的自关联.
- 全能的枚举
- 类似enum('','0','1',....'31'). 会造成新增值时的alter table.
- 应该使用整数作为外键关联到字典表或查找表来查找具体值.
- 多数情况下,应该避免使用NULL值,而使用替代(0,特殊值). 但不要极端.
- MySql会在索引中存储NULL值.
