数据库设计

数据库设计

物理设计

1. 物理设计要做什么？
   选择合适的数据库管理系统。
   考虑因素：成本，业务场景，开发语言，功能，操作系统等。
   
   2.定义数据库，表及字段，要符合命名规范。

选择存储引擎：这里以MySQL为例

表及字段命名规范

• 可读性：命名可读性强
  举例：列名nickname和nick_name相比，后者更加清晰明了，看起来更加舒服一点。
• 表意性：见名知意
  举例：列名col1和user_name相比，后者更加具有直观性，可以让我们一眼就知道当前列名所代表的意思和可能的数据类型
• 敏感性：不能与数据库专有字段命名冲突
  举例：MySQL中 有user表，所以我们自己创建用户表时，尽量不使用user命名，可以加一个前缀比如数据库缩写_user。

3.根据选择的数据库管理系统选择合适的数据类型

常用数据类型及占用空间
字段类型的选择的原则
1.当一个列可以选择多种数据类型的时候，优先考虑数字类型，其次是日期类型和二进制类型，最后是字符类型。
2.对于相同的数据类型，应考虑占用空间较小的数据类型。
字段类型的选择的原则依据
1.在对数据进行比较（查询条件，JOIN条件及排序）操作时，同样的数据，字符处理往往比数字处理效率要低，因为字符要参考数据字典进行比较，数字就不需要。
2.在数据库中，数据处理以页为单位，列的长度越小，一页中存储的数据就越多，加载相同的数据时的页数就相对较小，速度会更快。

如何具体选择字段类型？
1.char和varchar该如何选择？

• char用于数据长度差不多是一致的，基本都在一个小区间内波动或者列中最大数据长度小于50字节。
• varchar用于数据长度变化较大，不能预知其具体长度的数据。

2.decimal和float该如何选择？

• decimal用于存储精确数据，精度最高，但是占用空间很大。
• float占用空间比decimal小，适用于非精确数据，但会丢失数据精度。

3.时间类型如何存储？

• 使用int：int 是从 1970 年开始累加的，但是 int 支持的范围是 1901-12-13 到 2038-01-19 03:14:07，如果需要更大的范围需要设置为 bigInt。但是这个时间不包含毫秒，如果需要毫秒，还需要定义为浮点数。
• 使用timestamp：记录经常变化的更新 / 创建 / 发布 / 日志时间 / 购买时间 / 登录时间 / 注册时间等，并且是近来的时间，够用，时区自动处理，比如说做海外购或者业务可能拓展到海外。
• 使用datetime：记录固定时间如服务器执行计划任务时间 / 健身锻炼计划时间等，在任何时区都是需要一个固定的时间要做某个事情。

数据库设计的其他注意事项

1.如何选择主键？
(1)区分业务主键和数据库主键：

• 业务主键：用于标识业务数据，进行表与表之间的关联。
• 数据库主键：为了优化数据存储和查找。若没有设置数据库主键，则InnoDB引擎会自动生成6个字节的隐含主键。

(2)考虑主键是否要自动顺序增长：部分数据库是按照主键的顺序逻辑存储的。

(3)主键的字段类型所占用的空间要尽可能小：对于使用聚集索引方式存储的表，每个索引都会附加上主键信息。

2.避免使用外键（避免使用数据库来提供外键约束功能）：限于互联网项目
(1)在高并发业务中，使用外键约束会降低数据导入的效率，增加维护成本。
(2)建议使用逻辑外键，事实上在数据库中并没有设置外键约束，但在项目上都认为这是外键。由程序来维护外键约束，而不是数据库服务器本身来实现该功能。
(3)相关联的列要建立索引，增加查找效率。
(4)该怎么创建表就怎么创建表，只是没有了FOREIGN KEY (user_id) REFERENCES user (id)该条外键约束命令。
例如：使用数据库创建外键约束：

CREATE TABLE `m_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `m_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不使用数据库创建外键约束：

CREATE TABLE `m_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `m_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.避免使用触发器

(1)触发器是一个隐藏的存储过程,因为它不需要参数，不需要显示调用，往往在你不知情的情况下已经做了很多操作,无形中增加了系统的复杂性。
(2)涉及到复杂的逻辑的时候，触发器的嵌套是避免不了的，如果再涉及几个存储过程，再加上事务等等，很容易出现死锁现象。
(3)存储过程的致命伤在于移植性，存储过程不能跨库移植，在后期系统升级维护时难度加大。
4.谨慎使用预留字段

无法准确的知道预留字段的类型。
无法知道预留字段中所存储的内容。
后期维护预留字段的成本高。

建议：
(1)“按需设计”，在经过详细有效的分析之后，在数据表中只放置必要的字段，而不要留出大量的备用字段。
(2)如果数量很少，而且信息的性质与原表密切相关，那么就可以直接在原表上动态增加字段，并将相关的数据更新进去
(3)如果数量较大，或者并非是原表对象至关重要的属性，那么就可以新增一个表，然后通过键值连接起来。

5.反范式化设计。
什么是反范式化？
适当的违反的范式的要求，允许少量的数据冗余，用空间换取时间。
优点：增加查询效率。
三范式核心思想：不要出现冗余，
数据库设计的核心原则：既要考虑数据库存储空间，又要考虑查询速度。

反范式的常用方法：横向切割表；纵向切割表；增加冗余列；增加附加列（计算列）

拓展链接学习：数据库设计14个技巧