MySQL表的物理设计

在设计好表结构之后, 就需要进行物理设计, 将实体及属性映射到具体表和列. 而合理选择存储引擎和列类型也是数据库设计十分重要的一个环节.

物理设计包括, 命名规范, 存储引擎, 列字段选择, 主键设计以及主键生成算法.

一. 命名规范

首先在定义数据库,表,字段时一定要公司的命名规范;

二. 存储引擎

其次需要根据业务情况选择合适的存储引擎

三. 列字段类型选择

在一个列可以选择多种数据类型时, 应该优先使用数字类型, 其次是日期或二进制类型, 最后是字符类型.

如果是相同数据类型有多种选择时, 应该优先选择占用空间少的数据类型.

简单解释下:

1.在数据比较, 关联匹配以及排序时, 字符类型与字符集排序有关; 而数字和二进制是只与二进制值相关, 相对效率也就更高一些;

2.数据处理是以页为单位, 列的长度越小, 一页能容纳的数据就越多. 在加载同样数据时, 需要加载的数据页也就越少, 也就能减少磁盘IO, 提高数据库性能.

3.1 整数类型

根据业务情况, 选择合适类型

3.2 实数类型

选择合适的精确类型;

如存储金钱相关数据, 那最好是decimal类型

3.3 字符串类型

MySQL中字符串类型主要有两种varchar 和 char, 如果是非常大的文本可以酌情选择text类型.

在设置时, 尽量设置为符合需求的最小长度, 这样多次更新不会产生页分裂问题, 提高IO效率.

varchar变长字符串, 只占用必要字符空间, 宽度单位为字符;

列最大长度小于等于255时, 需额外占用1字节记录字符串长度;

列最大长度大于255时, 需额外占用2字节记录字符串长度;

varchar的长度范围为0-65535个字节, 如果编码为utf8, 每个字符最多占3个字节, 最大字符长度为21845.

char型适合存储长度近似的短字符串;

因为是定长, 更新时不会产生页分裂问题, 适合存储经常更新的字符串列;

3.4 日期类型

日期类型有两种: datetime和timestamp

datetime

与时区无关, 占用8个字节.

存储格式:

datetime(6)=YYYY-MM-DD HH:MM:SS.fraction

时间范围:

0000-00-00 00:00:00 - 9999-12-31 23:59:59

timestamp

依赖所在时区, 占用4个字节

存储格式:

timestamp=YYYY-MM-DD HH:MM:SS

时间范围:

1970-01-01 00:00:00 - 2038-01-19 11:14:07

两种类型都有数据更新时自动更新时间戳功能.

update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP

四. 主键选择

应选择尽可能小且顺序增长的数字类型, 并且表主键可以与业务主键不同.

原因是表的主键上, 会自动建立主键索引(B+树), 如果是自增的数字类型, 只需要在索引尾部添加数据即可, 性能开销很小;

当主键采用字符串类型时, 占用的空间会远大于数字类型.

如果是UUID, 不仅占用空间较大, 而且因为新生成的值, 未必会比原来的大, 引起索引调整, 降低性能.

4.1 分布式主键

分布式主键的生成方式有很多种, 这里介绍两种比较通用且性能较高的生成方式.

Redis自增

这是非常简单的一种方式. 在数据增长量不是很快情况下, 也是一个不错的选择.

雪花算法(snowflake)

snowflake是Twitter开源的分布式ID生成算法, 结果是一个long型的ID.

其核心思想是：使用41bit作为毫秒数, 10bit作为机器的ID(5个bit是数据中心, 5个bit的机器ID), 12bit作为毫秒内的流水号(意味着每个节点在每毫秒可以产生 4096 个 ID), 最后还有1个符号位, 永远是0.

也可以根据自己的业务情况自定义每段的bit数.

snowflake因为严格主机时间, 所以对时钟同步要求很高, 但总体来说是一个灵活方便, 高性能, 递增的分布式ID生成算法.