mysql专题12 数据库表物理设计系列问题

文章目录

1. 1. 物理设计
2. 2. 表及字段的命令规则
3. 3. 字段类型选择原则
4. 4. 数据库如何具体选择字段类型
   1. 4.1. char与varchar的选择
   2. 4.2. decimal与float的选择
   3. 4.3. 时间类型
   4. 4.4. 如何选择主键
5. 5. 数据库设计注意事项
   1. 5.1. 避免使用外键约束
   2. 5.2. 避免使用触发器
   3. 5.3. 预留字段
6. 6. 反范式化表设计
   1. 6.1. 知识回顾
   2. 6.2. 实例说明
7. 7. 总结

数据库需求分析及逻辑设计仅仅是将数据及数据实体之间的关系理清楚了, 最终目的是建立合适的数据库表结构; 在数据库设计环节中的物理设计的重要工作就是建立数据库表结构;

物理设计

• 选择合适的数据库管理系统(Mysql、Oracle, PgSQL等);
• 定义数据库, 表及字段的命名规范;
• 根据所选的DBMS系统选择合适的字段类型;
• 反范式化设计(实际过程中可能会设计冗余数据来换效率, 即用空间换时间)

选择合适的数据库也是至关重要的， 一般而言可以从成本, 功能，场景等方面进行考虑

• 成本
  商业数据库(Oracle, SQLServer)需要支持商业成本; 而开源数据库(MySQL, PgSQL) 只要符合社区协议则可免费使用;

• 功能
  如果需要经常进行比较大的事务操作 则使用Oracle更合适, 因为Oracle相比其他数据库,其事务执行开销成本要低;

• 场景
  互联网项目一般会选择开源数据库(MySQl, PgSQL), 而企业级项目一般倾向于商业数据库(Oracle, SQLServer)

表及字段的命令规则

所有对象命名应该遵循以下原则:

1. 可读性原则
   使用大写和小写来格式化的库对象名字以获得良好的可读性;例如使用CustAddress而不是custaddress来提高可读性;

2. 表意性原则
   对象的名字应该能描述它所标识的对象; 例如对于表, 表的名称应该能体现表中存储的数据内容; 对于存储过程, 存储过程名称应该能够体现存储过程的功能;

3. 长名原则
   尽可能少使用或者不使用缩写; 因为缩写有可能存在歧义;

字段类型选择原则

列的数据类型一方面影响数据存储空间的开销, 另一方面也会影响数据查询性能; 当一个列可以选择多种数据类型时, 应该优先考虑数字类型, 其次是日期或二进制类型, 最后是字符类型; 对于相同级别的数据类型, 应该优先选择占用空间小的数据类型;

示例

birthday 字段可选择如下四种类型,

Int: 257529600 # 时间戳
Datetime: 1978-03-01 #时间类型
varchar(20): '1978-03-01' 变长字符类型
char(10) '1978-03-01' 固定长度字符类型

显然优先选择顺序依次是 时间戳类型 > 时间类型 > 固定字符类型 > 变长字符类型;

因为birthday 类型长度比较固定, 且char(10) 比varchar(10)占用空间小;

列类型	存储空间(单位:字节)
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8
DATE	3
DATETIME	8
TIMESTAMP	4
CHAR(M)	1<=M<=255
VARCHAR(M)	L+1, (其中 L<=M, 1<=M<=255)

字段类型选择原则主要考虑了如下两方面,

在对数据进行比较(查询条件,JOIN条件及排序)操作时,

同样的数据, 字符处理往往比数字处理慢; (字符串需要参考字典进行排序)

在数据库中, 数据处理以页为单位, 列的长度越小, 利于性能提升; (InnoDB下默认页长度为16k)

数据库如何具体选择字段类型

char与varchar的选择

选择原则,

1. 如果列中要存储的数据长度差不多是一致的, 则应该考虑用char; 否则应该考虑用varchar;

2. 如果列中的最大数据长度小于50Byte, 则一般也考虑用char (如果这个列很少用, 则基于节省空间和减少IO的考虑, 还是可以选择用varchar)

3. 一般不宜定义大于50Byte的char类型列;

decimal与float的选择

选择原则,

1. decimal 用于存储精确数据, 而float 只能用于存储非精确数据; 故精确数据只能选择用decimal类型;

2. 由于float的存储空间开销一般比decimal小(精确到7位小数只需要4个字节, 而精确到15位小数需要8个字节) 故非精确数据优先选择float类型;

时间类型

选择原则,

1. 使用int类存储时间字段的优缺点,
   优点: 字段长度比datetime小;
   缺点: 使用不方便, 要进行函数转换;
   限制: 只能存储到2038-1-19 11:14.07 即2³²为2147483648

2.需要存储的时间粒度
年月日 时分秒周

如何选择主键

1. 区分业务主键和数据库主键
   业务主键用于标识业务数据, 进行表与表之间的关联;
   数据库主键为了优化数据存储(InnoDB会生成6个字节的隐含主键)

2. 根据数据库的类型, 考虑主键是否要顺序增长
   有些数据库是按主键的顺序逻辑存储的(如InnoDB);

3. 主键的字段类型所占空间要尽可能的小
   对于使用聚集索引方式存储的表, 每个索引后都会附加主键信息;
   数据库是按页查询的, 所以一页中存在的数据也多约便于查询;

数据库设计注意事项

避免使用外键约束

1. 降低数据导入的效率; (高并发时 建议不使用外键约束)

2. 增加维护成本;

3. 虽然不建议使用外键约束, 但是相关联的列上一定要建立索引;

避免使用触发器

1. 降低数据导入的效率; (有些存储引擎对触发器使用的总数是有限的)

2. 可能会出现意想不到的数据异常; (当逻辑变更后, 新逻辑的修订可能会忽略同步更新触发器设定而带来数据异常问题);

3. 使业务逻辑变得复杂;

预留字段

1. 无法准确的知道预留字段的类型;

2. 无法准确的知道预留字段中所存储的内容;

3. 后期维护预留字段所要的成本, 同增加一个字段所需要的成本是相同的;

4. 严禁 使用预留字段;

反范式化表设计

知识回顾

反范式化是针对范式化而言, 有时为了性能和读取效率的考虑而适当的对第三范式的要求进行违反, 而充许存在少量的数据冗余, 换句话来说反范式化就是使用空间来换取时间;

实例说明

符合范式化设计的表

表名	表字段
用户表	用户ID, 姓名, 电话, 地址, 邮编
订单表	订单ID, 用户ID, 下单时间, 支付类型, 订单状态
订单商品表	订单ID, 商品ID, 商品数量, 商品价格
商品表	商品ID, 名称, 描述, 过期时间

问题一,

查询订单信息

SELECT b.用户名, b.电话, b.地址, a.订单ID, SUM(c.商品价格*c.商品数量) as 订单价格
FROM `订单表` a
JOIN `用户表` b ON a.用户ID = b.用户ID
JOIN `订单商品表` c ON c.订单ID = b.订单ID
GROUP BY b,用户名, b.电话, b.地址, a.订单ID

查询订单详情信息

SELECT b.用户名, b.电话, b.地址, a.订单ID, SUM(c.商品价格*c.商品数量) as 订单价格, d.名称 as 商品名称, c.商品价格
FROM `订单表` a
JOIN `用户表` b ON a.用户ID = b.用户ID
JOIN `订单商品表` c ON c.订单ID = b.订单ID
JOIN `商品表` d on d.商品ID = c.商品ID

反范式化的冗余设计

表名	表字段	冗余字段
用户表	用户ID, 姓名, 电话, 地址, 邮编
订单表	订单ID, 用户ID, 下单时间, 支付类型, 订单状态	订单价格, 姓名, 地址, 电话
订单商品表	订单ID, 商品ID, 商品数量, 商品价格	商品名称, 过期时间
商品表	商品ID, 名称, 描述, 过期时间

查询订单信息

SELECT a.姓名, a.电话, a.地址, a.订单ID, a.订单价格
FROM `订单表` a

查询订单详情信息

SELECT b.用户名, b.电话, b.地址, a.订单ID, SUM(c.商品价格*c.商品数量) as 订单价格, c.商品名称, c.商品价格
FROM `订单表` a
JOIN `用户表` b ON a.用户ID = b.用户ID
JOIN `订单商品表` c ON c.订单ID = b.订单ID

一般情况下 读写比率为3:1, 少量的写冗余可以换取大量的读取效率;

为什么要进行反范式化设计

1. 减少表的关联数量; (减少表关联数量 意味着减少了数据库对磁盘的IO操作)

2. 增加数据的读取效率;

3. 反范式化一定要适度;

总结

• 物理设计过程中需要注意数据库/表/字段命名规范; 字段类型选择原则等问题;
• 如果设计字符长度小于50Byte, 与varchar相比, 建议优先选择char字符类型;
• decimal 用于精确数据场景, 而float用于非精确数据场景, 考虑的点在于非精确float只需要占用4字节, 而精确的decimal字段需要用到8字节;
• 时间类型选择, 读占比更多时,且对格式化有要求的 优先建议使用时间类型, 如果仅做判断或对时间格式没有太多要求时 建议使用int存储，但是要注意int只能存储的最大年限时间, 如果对时间有特殊的格式化要求时可以考虑字符串类型, 当格式化长度相对固定,且小于50Byte时, 优先选择char字符类型;
• 注意区分数据库主键与业务主键, 数据库主键主要服务与数据库自身检索查询用, 而业务主键主要用于关联业务数据表使用;
• 尽量避免使用外键约束, 因为导入外键约束耗时长, 此外会增加维护成本, 虽然不建议使用约束,但相应的列上也应建立合适的索引, 便于快速检索数据;
• 避免使用触发器, 一因为一些存储引擎如InnoDB对触发器的使用数量是有限的; 而新需求更新时可能忘记同步更新触发器而导致数据异常问题; 同时过多的使用触发器会使得业务逻辑变得复杂;
• 严禁使用预留字段;
• 为了性能及读取效率, 在数据库表物理设计时可以适当进行一些反范式化设计, 其本质为增加数据冗余, 通过空间换时间, 因为大多数场景下读占比会较高于写, 所以少量的写冗余能换来较高的读取效率是值得的;