随笔（三）

写在前面：慕课网，数据库设计那些事（每个小视频5-6分钟，加起来差不多两个小时），网址

第一章——需求分析

1.为了能对数据进行有效的存储和高效的访问，所以需要数据库设计。

2.四个步骤：需求分析、逻辑设计、物理设计、维护优化，不要一开始就建表。

第二章——逻辑设计

1.关系集的目的主要是把多对多的关系转变成一对多的关系。

2.要服从数据库范式要求，避免数据操作异常（插入异常、更新异常、删除异常，不太清楚，待学习）和数据冗余。

3.第一范式要求数据库中的表都是二维表，所有字段都是单一属性（基本的数据类型，如整数），不可再分。

4.所有单关键字段的表都符合第二范式，拥有组合关键字段的表不一定符合第二范式：如果组合关键字（多个属性构成的关键字）中的某一关键字决定非关键字（部分函数依赖关系），则这个表不符合第二范式；如果不存在这种情况，则表符合第二范式。

5.第三范式是在第二范式的基础上定义的，若数据表中不存在非关键字段对任意候选关键字段的传递函数依赖（如：商品名称（关键字段）->分类->分类描述，后两者都是非关键字段，商品名称和分类描述之间存在传递函数依赖）则符合第三范式。

6.BC范式（对第三范式的扩展，Boyce.Codd范式，BCNF），数据库表中如果不存在任何字段（不管是非关键字段还是关键字段）对任一候选关键字段的传递函数依赖（稍作修改，如供应商->供应商联系人，供应商联系人->供应商，供应商和供应商联系人之间的关系属于传递函数依赖，不太清楚，待学习）则符合BC范式。

注：详细补充可以参考链接：数据库第一范式、第二范式、第三范式、BC范式

第三章——物理设计

1.实际开发中可能会出现以空间换时间的情况（反范式化设计）

2.选择DBMS（数据库管理系统）时，考虑以下问题：

（1）成本问题（Oracle、SQLServer属于商业数据库，成本大，MySQL和PgSQL是开源数据库）；

（2）性能问题（Oracle适合大的事务性操作，其他的适合小的事务性操作）；

（3）操作系统问题（SQLServer只支持在windows上运行，其他可以在windows和linux下运行）；

（4）开发语言问题（如果使用.net作为开发语言，SQLServer更适合这种情况，java或者php做开发语言时其他三种都可以）；

（5）应用场景问题（开源数据库适用于互联网项目，商业数据库适合企业级项目，如电信或金融类等适合用Oracle，中小企业适合用SQLServer做企业级数据库，如ERP系统）。

3.MySQL常用的存储引擎：

（1）MyISAM：MySQL5.5前默认的引擎，不支持事务，适用于写操作很少，读操作很多时，不适用读写操作频繁的情况。

（2）MRG_MYISAM：不支持事务。分段归档，数据仓库。不适用于全局查找过多的情况。

（3）Innodb：MySQL5.5后默认的引擎，支持事务，不存在表级锁，只存在行级锁，阻塞更少，无忌用场景。

（4）Archive：不支持事务。只支持insert和select操作，不支持更新和删除，趋向于应用于日志记录。在相同数量级的存储情况下，使用该引擎所需的存储容量相对更少。

（5）Ndb cluster：支持事务，行级锁，使用mysql集群（内存型集群）时才能使用该引擎，如果数量过大超过内存大小，这时最好不用该集群，也不能使用该引擎。

4.字段类型的选择原则：当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型，对于相同级别的数据类型，应该优先选择占用空间小的数据类型，如char和varchar类型中应该选择char类型。mysql的时间戳类型TIMESTAMP只能存储到2037年？实际存储方式是使用INT存储的。

5.在对数据进行比较操作（查询条件、JOIN条件及排序）时，在处理同样的数据方面，字符处理往往比数字处理（INT或二进制类型）慢；在数据库中，数据处理以页为单位（mysql使用Innodb引擎时默认16k一个页），列的长度越小，在一个页中存储的数据量也越多，越利于I/O性能提升。

6.char和varchar如何选择：如果列中要存储的数据长度差不多一致（如出生日期、电话等），应该用char，如果不是，则用varchar；如果列中最大数据长度小于50Byte，则一般也考虑用char，但如果该列很少使用，则为了节省空间、减少I/O，应该用varchar；一般不宜定义大于50Byte的char类型列。utf-8的每个字符占用三个字节（Byte）。

7.decimal和float如何选择：（1）decimal只用于存储精确数据，float只能用于存储非精确数据；（2）由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，精确到15位小数只需要8个字节），所以非精确数据优先选择float类型。

8.时间类型如何存储：（1）使用int来存储时间字段的优缺点：优点：字段长度比datetime小；缺点：使用不方便，要进行函数转换；限制：只能存储到2038-1-19 11:14:07，也就是2^32。如果经常使用，还是要用datetime类型进行存储，如果很少用到，可以用int进行存储。（2）考虑要存储的时间粒度：年（year类型一个字节？）月日小时分秒周。

9.（1）区别业务主键和数据库主键；（2）根据数据库的类型，考虑主键是否要顺序增长；（3）主键大的字段类型所占空间要尽可能的小。对于使用聚集索引方式存储的表，每个索引后都会附加主键信息。

10.避免使用外键约束：（1）降低数据导入的效率；（2）增加维护成本；（3）虽然建议不适用外键约束，但相关联的列上要建立索引。

11.避免使用触发器：（1）降低数据导入的效率；（2）可能出现意想不到的数据异常；（3）使业务逻辑变得复杂。使用大量的触发器保证业务逻辑时，如果业务逻辑变更，可能会产生影响业务逻辑的情况。

12.关于预留字段：（1）无法准确地知道预留字段的类型；（2）无法准确地知道预留字段中所存储的内容；（3）后期维护预留字段所要的成本同增加一个字段所需要的成本是相同的；（4）严禁使用预留字段。

13.反范式化：针对范式化而言，反范式化就是为了性能和读取效率的考虑而适当地对第三范式的要求进行违反，即允许存在少量的数据冗余，用空间换时间。在互联网项目中，读比写比例大约是3比1。

14。为什么反范式化：（1）减少表的关联数量；（2）增加数据的读取效率；（3）反范式化一定要适度，不要每张表都反范式化操作。

第四章——维护优化

1.维护和优化需要做什么：（1）维护数据字典（如状态字段符表示的含义）；（2）维护索引；（3）维护表结构；（4）在适当的时候对表进行水平拆分或垂直拆分。

2.维护数据字典：（1）针对选用的数据库管理系统选择适合的第三方工具对数据字典进行维护；（2）利用数据库本身的备注字段来维护数据字典，如在mysql中使用COMMENT字段进行备注，然后用sql语句导出。

3.什么是索引？

索引用于快速找出在某个列中有一特定值的行。例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。如果选择Phone列建立索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。MySQL中的索引的存储类型有两种：BTREE、HASH，也就是用树或者Hash值来存储该字段。

经常更新的表要少建立索引，经常查询的可以；数据量小的表尽量不建立索引；要建立索引的列应该有多个不同的值，如果只有两种值（如男、女），最好不要建立索引。

链接：https://www.cnblogs.com/yinqanne/p/9635106.html

4.如何选择合适的列建立索引？

（1）出现在WHERE从句，GROUP BY从句，ORDER BY从句中的列；（2）可选择性高的列（多个列）要放在索引的前面；（3）索引中不要包括太长的数据类型。

5.如何维护索引？（1）索引不是越多越好，过多的索引不但会降低写效率而且会降低读的效率；（2）定期维护索引碎片；（3）在sql语句中不要使用强制索引关键字（oracle中经常使用？）。

6.如何维护表结构？（1）使用在线变更表结构的工具（mysql5.5前可以使用pt-online-schema-change，在使用该工具前表不要有触发器；mysql5.6之后本身支持在线表结构的变更）；（2）同时对数据字典进行维护；（3）控制表的宽度和大小（为了保持表的宽度大小，就需要对表垂直拆分）。

7.数据库中适合的操作：（1）批量操作更适用于在数据库中执行，逐条操作适合在程序中执行；（2）禁止使用select *这样的查询，因为会造成I/O的浪费，如果表结构发生变更，这也可能会导致程序的出错；（3）控制使用用户自定义函数，因为可能会对索引的使用造成影响，因为如果使用了函数，列中的索引就不会起作用；（4）不要使用数据库中的全文索引，因为会另外建立索引文件来对索引进行维护，还有对中文的支持不太好（？），如果要实现这种功能，最好使用专业的搜索引擎工具来实现。

8.为了控制表的宽度可以进行表的垂直拆分：经常一起查询的列放到一起；text.blob等大字段（列）拆分出到附加表中。

9.为了控制表的大小可以进行表的水平拆分：对表进行水平拆分后，每张表的表结构都是相同的。通过Hash Key的方式进行，通过对主键值进行hash操作，将行分开成表结构相同的不同表（待学习）。