Schema与数据类型优化

Schema与数据类型优化

数据库的表设计需要考虑各种因素，反范式可以加快某些查询，比如添加计数表和汇总表是一种很好的优化手段，但是这些表的维护成本会比较高，常规做法是消费binglog，一个好处是异步处理，还有就是不用事务保证数据一致性，用MQ保证。

1 数据类型选择

1.1 整数类型

整数类型有：TINYINT，SMALLINT,MEDIUMINT，INT，BIGINT，分别占用8、16、24、32、64位存储空间。
整数类型可选 UNSIGNED属性，表示不允许为负数，这样做可以使存储上限提高一倍。有符号和无符号具有相同的存储空间，并具有相同的性能。

1.2 实数类型

实数是带有小数部分的数字。然而他们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。

1.3 字符串类型

以下皆以InnoDB存储引擎为例

VARCHAR

varchar 类型用于存储可变字符串，它比定长字符串类型更节省空间，因为仅使用必要的空间。varchar需要额外使用1或2个字节存储长度。

varchar不仅节省了空间，所以对性能也很有帮助。但是由于是变长的，UPDATE时可能会比原来的长度更长，需要一些额外的工作。

以下情况使用varchar是很合适的：字符串列的最大长度比平均长度大很多，列的更新很少，所以碎片不是问题。

使用VARCHAR(5)和VARCHAR(20)存储Hello的空间是一样，但是使用更短的列是更加明智的。因为 mysql 通常会分配固定大小的内存块保存内部值。尤其是使用临时表的时候，所以最好只分配真正需要的空间。

关于NULL值的存储

---

以下是Compact行格式示意图，和5.7版本之后的默认版本格式 Dynamic几乎一样

---

变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的，会在NULL值列表记录下来。所以数据库字段设计时，字段最好设置为非null，或者用空字符串代替。因为如NULL值虽然不占用存储空间，但是需要额外的空间来记录该列是非为NULL，同时也需要费力维护，最重要的是会影响Mysql对索引的选择。反之空字符串就不存在这个问题。

CHAR

char类型是定长的，mysql会一次性分配指定长度的空间。mysql会自动删除末尾的空格。

char适合存储很短的字符串，或者所有的值接近一个长度。char非常适合存储密码的MD5值，因为是定长的，所以对经常变动的列会比varchar更好。对于非常短的列也比varchar更好，因为varchar会有额外字节存储实际使用长度。

1.4 时间类型

DATETIME

这个类型可以保存从 1001年到9999年，精确度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储。

TIMESTAMP

TIMESTAMP类型保存了从1970年1月1号以来的秒数，和UNIX时间戳相同，只使用4个字节的存储空间，因此他的存储范围也比DATETIME小的多，只能表示1970年到2038年。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它的空间利用效率更高。

如果需要存储比秒更小的粒度怎么办？可以使用BIGINT存储微秒级别的时间戳或者使用DOUBLE存储秒之后的小数部分。

1.5 范式和反范式

在日常开发中，多数情况是不需要严格遵守数据库三大范式的，因为在互联网中查询的使用场景和响应时间要求是要远高于更新的。

通过在表中增加冗余字段可以提升响应时间，因为这样可以尽量避免表的关联查询，如果数据量比内存大时，关联查询会产生随机IO，严重影响查询效率。

但是如果发生更新的话，会涉及多个表的一个字段更新和一张表多行的更新，会比遵循范式要慢。

1.6 缓存表和汇总表

有时提示性能最好的办法是在同一张表中保存衍生数据和冗余数据，有时又需要独立出一张缓存表或者汇总表。

例如现在要设计一个论坛系统，用户可以发帖子，也可以评论、点赞和转发帖子或者评论，如果是你你会如何设计这样的系统？如何高效查询每个帖子对于的点赞、评论和转发数量？是我的话我会如下设计：

• t_user(用户信息表)
• t_comment(评论回复表)
• t_u_content(用户发表的内容，帖子和一级评论的id等)
• t_like(点赞表)
• t_relay(转发表)
• t_dw_content(聚合每个帖子和回复的全量表，包括点赞、回复、转发信息)（汇总表）