数据库设计

2018-08-12 本文已影响14人春泥村雨

1. 数据库选型

1.1 常见 DBMS 系统：

商业数据库（更适合企业级项目）：Oracle、SQLServer
开源数据库（适用于互联网项目）：MySQL、PgSQL

2. MySQL常用存储引擎：Innodb

3. 数据库表及字段的命名规则

可读性原则： 单词首字母大小写，MySQL 大小写敏感（设置）
表意性原则
长名原则： 少用缩写

4. 字段类型的选择原则

优先考虑数字类型，其次是日期或二进制类型，最后是字符类型，对于相同级别的数据类型，优先选择占用空间小的数据类型

以上原则主要从下面两个角度考虑：

在对数据进行比较（查询条件、JOIN 条件及排序）操作时：同样的数据，字符处理往往比数字处理慢；
在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

4.1 char 与 varchar 选择原则？

长度一致用 char；
如果列中的最大数据长度小于 50 Byte，则一般也考虑用 char。（注：如果这个列很少用，则基于节省空间和减少 I/O 的考虑，还是可以选择用 varchar）
一般不宜定义大于 50 Byte 的 char 类型列，大于 50 Byte的选择 varchar 类型。
utf8，每个字符占用 3 个字节

4.2 decimal 与 float 选择原则？

decimal 用于存储精确数据，float 只能用于存储非精确数据；
float 的存储空间开销一般比 decimal 小（精确到 7 位小数需要 4 个字节，精确到 15 位小数需要 8 个字节）

4.3 时间类型存储选择原则

注：根据业务场景来进行选择，如果不经常被用于查询，可以选择 int

使用 int 来存储时间字段的优缺点

优点：字段长度比 datetime 小

缺点：使用时要进行函数转换，只能存储到 2038-1-19 11:14:07，即 2^32

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

5. 如何选择主键

区分业务主键与数据库主键

业务主键用于标识业务数据，进行表与表之间的关联；
数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）

根据数据库的类型，考虑主键是否要顺序增长

有些数据库是按主键的顺序逻辑存储的

主键的字段类型所占空间要尽可能的小

对于使用聚集索引方式存储的表，每个索引都会附加主键信息。

6. 避免使用外键约束

建议外键约束时，虽然可以保护数据的完整性，但数据写入时，会去检测是否符合外键约束，增加了开销。

降低数据导入的效率；
增加维护成本；
虽然不建议使用外键约束，但是相关联的列上一定要建立索引

7. 避免使用触发器

降低数据导入的效率；
可能会出现意想不到的数据异常；
使业务逻辑变的复杂。

8. 严禁使用预留字段

无法准确的知道预留字段的类型；
无法准确的知道预留字段中所存储的内容；
后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的。

9. 反范式化

为了性能和读取效率的考虑而适当对第三范式的要求进行违反，用空间换时间。

10. 维护和优化

10.1 维护数据字典

使用第三方工具对数据字典进行维护，根据数据库选择相应的数据库；

select 
a.table_name,b.TABLE_COMMENT,
a.COLUMN_NAME,a.COLUMN_TYPE,a.COLUMN_COMMENT
FROM
information_schema.COLUMNS a JOIN information_schema.TABLES b
ON a.table_schema=b.table_schema AND
a.table_name=b.table_name
WHERE a.table_name='customer'

利用数据库本身的备注字段来维护数据字典；
导出数据字典。

10.2. 维护索引

出现在 Where 从名，group by 从名，order by 从句中的列；
可选择性高的列要放到索引的前面；
索引中不要包括太长的数据类型。

注意事项

索引并不是越多越好，过多的索引会降低读写效率；
定期维护索引碎片；
在 SQL 语句中不要使用强制索引关键字。

10.3 维护表结构

使用在线变更表结构的工具；
同时对数据字典进行维护；
控制表的宽度和大小。

数据库适合的操作

批量操作 VS 逐条操作；
禁止使用 select * 这样的查询；
控制使用用户自定义函数；
不要使用数据库中的全文索引。

10.4 在适当的时候对表进行水平拆分或垂直拆分

垂直拆分：控制表的宽度

经常一起查询的列放到一起；
text，blob 等大字段拆分出到附加表中。

水平拆分：控制表的大小

把一张表的数据拆分成多张表来存储。