数据库设计
数据库设计
数据库设计术语
1）属性（attribute）：列的名字
2）依赖（relation）：列属性间存在的某种联系
3）元组（tuple）：每一行的数据就是一个元组
4）表（table）：由多个属性，以及众多元组所表示的各个实例组成
5）模式（schema）：schema就是数据库对象的集合，所谓的数据库对象也就是常说的表，索引，视图，存储过程等。可以给不同的用户分配不同的schema权限，即管理不同的数据库。
6）域（domain）：数据类型，如string、integer等，每一个属性都有它的数据类型
7）键（key）：又称为关键字
8）主键（primary key）：主键就是在一张数据表中能够唯一确定一条记录的属性/字段
9）候选键（candidate key）：一张表中通常都会含有多个属性/字段。能够用来唯一确定一条记录的属性/字段就是候选键，所以候选键的数量可以为一个或多个。候选键就是具备成为主键的资格，但是目前还不是主键的属性/字段。
10）超键（super key）：在关系中能唯一标识元组的属性集称为关系模式的超键
11）外键（foreign key）：如果某一个关系A中的一个（组）属性是另一个关系B的键，则该（组）属性在A中称为外键
12）主属性（prime attribute）：所有候选键所包含的属性都是主属性
13）投影（projection）：选取特定的列，如将学生信息投影为学号、姓名即得到仅包含学号、姓名的列，即查询指定的列
14）选择（selection）：按照一定条件选取特定元组，如选择分数>80的元组
15）笛卡儿积（交叉连接Cross join）：第一个关系每一行分别与第二个关系的每一行组合
16）自然连接（natural join）：第一个关系中每一行与第二个关系的每一行进行匹配，如果得到有交叉部分则合并，若无交叉部分则舍弃。
17）连接（theta join）：即加上约束条件的笛卡儿积，先得到笛卡儿积，然后根据约束条件删除不满足的元组
18）外连接（outer join）：执行自然连接后，将舍弃的部分也加入，并且匹配失败处的属性用NULL代替
19）除法运算（division）：关系R除以关系S的结果为T，则T包含所有在R但不在S中的属性，且T的元组与S的元组的所有组合在R中
数据库范式设计
第一范式：关系模式的每一个分量是不可再分的数据项，数据库表的字段无法再细分。即对属性的原子性，要求属性具有原子性，不可再分解。
第二范式：消除非主属性对码的部分函数依赖，主键是该条数据唯一代表。
第三范式：消除非主属性对码的传递函数依赖，该表依赖的外键应该是外键表数据的唯一代表，如外键的主键。对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。
反范式：范式设计可以消除数据库的数据冗余及插入异常，但是过分的范式设计可能导致数据查询时需要关联多张表，导致数据库查询效率下降，因此在实际工作中，需要适当的反范式设计。反范式设计需要跟具具体业务实际决定，将经常使用的业务表进行合理的反范式设计可以提高数据库的查询效率。在实际的业务工作中，一般不使用外键，因为外键会在每次数据插入时进行合法性校验，这样会极大的增加数据库的插入效率。
规范设计完整流程
流程说明：
参考链接：https://pdai.tech/md/db/sql/sql-db-theory-design.html
一、需求分析（常用自顶向下）：分析用户的需求，包括数据、功能和性能需求
1）进行数据库设计首先必须准确了解和分析用户需求（包括数据与处理）。需求分析是整个设计过程的基础，也是最困难，最耗时的一步。需求分析是否做得充分和准确，决定了在其上构建数据库大厦的速度与质量。需求分析做的不好，会导致整个数据库设计返工重做。
2）需求分析的任务，是通过详细调查现实世界要处理的对象，充分了解原系统工作概况，明确用户的各种需求，然后在此基础上确定新的系统功能，新系统还得充分考虑今后可能的扩充与改变，不仅仅能够按当前应用需求来设计。调查的重点是，数据与处理。达到信息要求，处理要求，安全性和完整性要求。
3）分析方法常用SA（Structured Analysis）结构化分析方法，SA方法从最上层的系统组织结构入手，采用自顶向下，逐层分解的方式分析系统。
4）数据流图表达了数据和处理过程的关系，在SA方法中，处理过程的处理逻辑常常借助判定表或判定树来描述。在处理功能逐步分解的同时，系统中的数据也逐级分解，形成若干层次的数据流图。系统中的数据则借助数据字典（data dictionary，DD）来描述。数据字典是系统中各类数据描述的集合，数据字典通常包括数据项，数据结构，数据流，数据存储，和处理过程5个阶段。
二、概念结构设计（常用自底向上）：主要采用E-R模型进行设计，包括画E-R图
概念结构设计是整个数据库设计的关键，它通过对用户需求进行综合，归纳与抽象，形成了一个独立于具体DBMS的概念模型。设计概念结构通常有四类方法：
1）自顶向下。即首先定义全局概念结构的框架，再逐步细化。
2）自底向上。即首先定义各局部应用的概念结构，然后再将他们集成起来，得到全局概念结构。
3）逐步扩张。首先定义最重要的核心概念结构，然后向外扩张，以滚雪球的方式逐步生成其他的概念结构，直至总体概念结构。
4）混合策略。即自顶向下和自底向上相结合。
三、逻辑结构设计（E-R图）：通过将E-R图转换成表，实现从E-R模型到关系模型的转换
逻辑结构设计是将概念结构转换为某个DBMS所支持的数据模型，并将进行优化。在这阶段，E-R图显得异常重要。各分E-R图之间的冲突主要有三类：属性冲突，命名冲突和结构冲突。E-R图向关系模型的转换，要解决的问题是如何将实体性和实体间的联系转换为关系模式，如何确定这些关系模式的属性和码。
四、数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径
物理设计是为逻辑数据结构模型选取一个最适合应用环境的物理结构，包括存储结构和存取方法。首先要对运行的事务详细分析，获得选择物理数据库设计所需要的参数，其次，要充分了解所用的RDBMS的内部特征，特别是系统提供的存取方法和存储结构。常用的存取方法有三类：
1）索引方法，目前主要是B+树索引方法。
2）聚簇方法（Clustering）方法。
3）是HASH方法。
五、数据库的实施：包括编程、测试和试运行
数据库实施阶段，设计人员运营DBMS提供的数据库语言（如sql）及其宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制和调试应用程序，组织数据入库，并进行试运行。
六、数据库运行与维护：系统的运行与数据库的日常维护
数据库应用系统经过试运行后，即可投入正式运行，在数据库系统运行过程中必须不断地对其进行评价，调整，修改。
数据库表设计
参考链接：https://mp.weixin.qq.com/s?__biz=MzkyMzU5Mzk1NQ==&mid=2247506621&idx=1&sn=afca898cb461827054d706a92f9b9250&source=41#wechat_redirect
============================命名规范
数据库表名、字段名、索引名等都需要命名规范，可读性高(一般要求用英文)，让别人一看命名，就知道这个字段表示什么意思。比如一个表的账号字段，反例如下：
acc_no,1_acc_no,zhanghao
正例：
account_no,account_number
表名、字段名必须使用小写字母或者数字，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。
============================选择合适的字段类型
设计表时，我们需要选择合适的字段类型，比如：
尽可能选择存储空间小的字段类型，就好像数字类型的，从tinyint、smallint、int、bigint从左往右开始选择。
小数类型如金额，则选择decimal，禁止使用float和double。
如果存储的字符串长度几乎相等，使用char定长字符串类型。
varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。
如果存储的值太大，建议字段类型修改为text，同时抽出单独一张表，用主键与之对应。
同一表中，所有varchar字段的长度加起来，不能大于65535。如果有这样的需求，请使用TEXT/LONGTEXT类型。
============================主键设计要合理
主键设计的话，最好不要与业务逻辑有所关联。有些业务上的字段，比如身份证，虽然是唯一的，一些开发者喜欢用它来做主键，但是不是很建议。主键最好是毫无意义的一串独立不重复的数字，比如UUID，又或者Auto_increment自增的主键，或者是雪花算法生成的主键等等。
============================选择合适的字段长度
在mysql中，varchar和char类型表示字符长度，而其他类型表示的长度都表示字节长度。比如char(10)表示字符长度是10，而bigint(4)表示显示长度是4个字节，但是因为bigint实际长度是8个字节，所以bigint(4)的实际长度就是8个字节。我们在设计表的时候，需要充分考虑一个字段的长度，比如一个用户名字段（它的长度5~20个字符），你觉得应该设置多长呢？可以考虑设置为 username varchar(32)。字段长度一般设置为2的幂（也就是2的n次方）。
============================优先考虑逻辑删除，而不是物理删除
物理删除：把数据从硬盘中删除，可释放存储空间。
逻辑删除：给数据添加一个字段，比如is_deleted，以标记该数据已经逻辑删除。
物理删除就是执行delete语句，如删除account_no =‘666’的账户信息SQL如下：
delete from account_info_tab whereaccount_no ='666';
逻辑删除呢，就是这样：
update account_info_tab set is_deleted = 1 where account_no ='666';
为什么不推荐使用物理删除，因为恢复数据很困难。
物理删除会使自增主键不再连续。
核心业务表的数据不建议做物理删除，只适合做状态变更。
============================每个表都需要添加这几个通用字段如主键、create_time、modifed_time等
表必备一般来说，或具备这几个字段：
id：主键，一个表必须得有主键，必须
create_time：创建时间，必须
modifed_time/update_time: 修改时间，必须，更新记录时，需要更新它
version : 数据记录的版本号，用于乐观锁，非必须
remark ：数据记录备注，非必须
modified_by :修改人，非必须
creator ：创建人，非必须
============================一张表的字段不宜过多
我们建表的时候，要牢记，一张表的字段不宜过多，一般尽量不要超过20个字段哈。如果一张表的字段过多，表中保存的数据可能就会很大，查询效率就会很低。因此，一张表不要设计太多字段，如果业务需求，实在需要很多字段，可以把一张大的表，拆成多张小的表，它们的主键相同即可。当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表 (主要是为了性能考虑)。
============================尽可能使用not null定义字段
如果没有特殊的理由，一般都建议将字段定义为NOT NULL。首先，NOT NULL可以防止出现空指针问题。其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。NULL值有可能会导致索引失效。如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑，那就可以将这个字段设置为NOT NULL。
============================设计表时，评估哪些字段需要加索引
首先，评估你的表数据量。如果你的表数据量只有一百几十行，就没有必要加索引。否则设计表的时候，如果有查询条件的字段，一般就需要建立索引。但是索引也不能滥用。索引也不要建得太多，一般单表索引个数不要超过5个。因为创建过多的索引，会降低写得速度。
区分度不高的字段，不能加索引，如性别等。
索引创建完后，还是要注意避免索引失效的情况，如使用mysql的内置函数，会导致索引失效的。
索引过多的话，可以通过联合索引的话方式来优化。然后的话，索引还有一些规则，如覆盖索引，最左匹配原则等等。
============================不需要严格遵守3NF，通过业务字段冗余来减少表关联
============================避免使用MySQL保留字（对象映射需要加反引号区分）
如果库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号来引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。因此，我们一般避免使用MySQL保留字，如select、interval、desc等等。
============================不搞外键关联，一般都在代码维护
使用外键存在性能问题、并发死锁问题、使用起来不方便等等。每次做DELETE或者UPDATE都必须考虑外键约束，会导致开发的时候很难受，测试数据造数据也不方便。还有一个场景不能使用外键，就是分库分表。
============================一般都选择INNODB存储引擎（支持事务）
============================选择合适统一的字符集
数据库库、表、开发程序等都需要统一字符集，通常中英文环境用utf8。
MySQL支持的字符集有utf8、utf8mb4、GBK、latin1等。
utf8：支持中英文混合场景，国际通过，3个字节长度
utf8mb4: 完全兼容utf8，4个字节长度，一般存储emoji表情需要用到它。
GBK ：支持中文，但是不支持国际通用字符集，2个字节长度
latin1：MySQL默认字符集，1个字节长度
============================如果你的数据库字段是枚举类型的，需要在comment注释清楚（所有属性都应该添加注释，方便维护）
============================时间的类型选择
我们设计表的时候，一般都需要加通用时间的字段，如create_time、modified_time等等。那对于时间的类型，我们该如何选择呢？
对于MySQL来说，主要有date、datetime、time、timestamp 和 year。
date：表示的日期值, 格式yyyy-mm-dd,范围1000-01-01到 9999-12-31，3字节
time：表示的时间值，格式hh:mm:ss，范围-838:59:59到838:59:59，3字节
datetime：表示的日期时间值，格式yyyy-mm-dd hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59，8字节，跟时区无关
timestamp：表示的时间戳值，格式为yyyymmddhhmmss，范围1970-01-01 00:00:01到2038-01-19 03:14:07，4字节，跟时区有关
year：年份值，格式为yyyy。范围1901到2155，1字节
推荐优先使用datetime类型来保存日期和时间，因为存储范围更大，且跟时区无关。
============================不建议使用Stored procedure（包括存储过程，触发器）
对于MYSQL来说，存储过程、触发器等还不是很成熟，并没有完善的出错记录处理，难以维护，不建议使用。
============================1:N、N:N关系的设计
学生表是多（N）的一方，会有个字段class_id保存班级表的主键。当然，一班不加外键约束，只是单纯保存这个关系而已。有时候两张表存在N:N关系时，我们应该消除这种关系。通过增加第三张表，把N:N修改为两个1:N。
============================大字段
设计表的时候，我们尤其需要关注一些大字段，即占用较多存储空间的字段。比如用来记录用户评论的字段，又或者记录博客内容的字段，又或者保存合同数据的字段。如果直接把表字段设计成text类型的话，就会浪费存储空间，查询效率也不好。在MySQL中，这种方式保存的设计方案，其实是不太合理的。这种非常大的数据，可以保存到mongodb中，然后，在业务表保存对应mongodb的id即可。这种设计思想类似于，我们表字段保存图片时，为什么不是保存图片内容，而是直接保存图片url即可。
============================考虑是否需要分库分表
我们在设计表的时候，其实可以提前估算一下，是否需要做分库分表。比如一些用户信息，未来可能数据量到达百万设置千万的话，就可以提前考虑分库分表。为什么需要分库分表: 数据量太大的话，SQL的查询就会变慢。如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库。即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢啦。
