数据库设计

一、什么是数据库设计？
    简单来说，数据库设计就是根据业务系统的具体需要，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据存储模型。并建立好数据库中的表的结构以及表与表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。

二、为什么要进行数据库设计？

    糟糕的设计
        1.存在大量的数据冗余
        2.存在数据插入，更新，删除异常
        3.浪费大量的存储空间
        4.访问数据低效
    优良的设计
        1.减少数据冗余
        2.避免数据维护异常
        3.节约存储空间
        4.高效的访问
三、数据库设计流程
    需求分析 ——> 逻辑设计——> 物理设计 ——>维护优化

    需求分析
        数据库需求的作用点：
            1.数据是什么
            2.数据有哪些属性
            3.数据和属性各自的特点有哪些
        为什么要进行需求分析
            1.了解系统中所要存储的数据
            2.了解数据库的存储特点
            3.了解数据的生命周期
        需要弄清的问题
            1.实体与实体之间的关系（1对1,1对多，多对多）
            2.实体所包含的属性有什么？
            3.哪些属性或属性的组合可以唯一标识一个实体
        逻辑设计
            1.将需求转化为数据库的逻辑模型
            2.通过E-R图的形式对逻辑模型进行展示
            3.同所选用的具体的DBMS系统无关
            数据库设计范式
                常见的数据库设计范式包括：
                    第一范式，第二范式，第三范式以及BC范式
                    当然还有第四以及第五范式，不过目前我们大多数数据库设计所要遵循的范式为前三个范式。
                为什么要遵循数据库设计范式
                    如果不遵循数据库设计范式，会出现数据操作的异常以及数据冗余
                    操作异常
                        1.插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常。
                        2.更新异常：如果更改表对应的某个实体实例属性时，
                        需要将多行更新，那么就说这个表存在更新异常。
                        3.删除异常：如果删除表的某一行来反应某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表就存在删除异常。
                    数据冗余
                        是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，这样就说表中存在数据冗余。
             第一范式
                定义：数据库中表的所有字段都是单一属性，不可再分的。
                这个单一属性是由基本的数据类型所构成的，如整数，浮点数，字符串，等。

                换句话说就是第一范式要求数据表中的表都是二维表。

             第二范式
                定义：数据库的表中不存在非关键字段对任一候选字段的部分函数依赖。
                部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况。

                换句话说所有单关键字段的表都符合每二范式。

             第三范式
                定义：第三范式是在第二范式的基础之上定义的，如果数据表中不存在非关键字段对任意候选字段的传递函数依赖则符合第三范式。
            Boyce.Codd范式，简称BC范式
                定义：在第三范式的基础之上，数据库表中如果不存在任何字段对任意候选关键字段的传递函数依赖符合BC范式。
                也就是说如果是复合关键字，则复合关键字段之间也不能存在函数依赖关系。
        物理设计
            做什么
                1.选择合适的数据库管理系统（商业数据库：Oracle、SQLServer 开源数据库：MySQL、PgSQL）
                2.定义数据库表及字段的命名规范。
                3.根据所选的DBMS系统选择合适的字段类型。
                4.反范式化设计
            表及字段的命名规则
                所有对象命名应该遵循下述原则：
                    1.可读性原则
                        使用大写和小写来格式换的库对象名字以获得良好的可读性。
                        例如：使用UserAddress而不是useraddress来提高可读性。
                        （这里要注意有些DBMS系统对表名的大小写是敏感的）
                    2.表意性原则
                        对象的名字应该能够描述它所标识的对象。
                        例如，对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能。
                    3.长名原则
                        尽可能少使用或者不使用缩写，适用于数据库（DATABASE）名之外的任意对象。
            字段类型的选择原则


            主要从下面两个角度考虑：
                1.在对数据进行比较（查询条件、join条件及排序）操作时：同样的数据，字符处理往往比数字处理慢。
                2.在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。
                char与varchar如何选择
                原则：
                    1.如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar.
                    2.如果列中的最大数据长度小于50Byte，则一般考虑用char。
                    3.一般不宜定义大于50Byte的char类型列。
                decimal与float如何选择
                    原则：
                        1.decimal用于存储精确数据，而float只能用于存储非精确数据，故精确数据只能选择用decimal类型。
                        2.由于float的存储空间开销一般比decimal小，故非精确数据优先选择float类型。
                时间类型如何存储
                    1.使用int来存储时间字段的优缺点
                        优点：字段长度比datetime小
                        缺点：使用不方便，要进行函数转换
                        限制：只能存储到2038-1-19 11:14:07 即2^32为2147483648
                    2.需要存储的时间粒度

                        年 月 日 小时 分 秒 周

维护优化

             如何选择主键
                1.区分业务主键和数据主键
                    业务主键用于标识业务数据，进行表与表之间的关联；
                    数据库主键为了优化数据存储
                2.根据数据库的类型，考虑主键是否要顺序增长
                    有些数据库是按主键的顺序逻辑存储的
                3.主键的字段类型所占的空间尽可能的小
                    对于使用聚集索引方式存储的表，每个索引后都会附加主键信息
            避免使用外键约束
                1.降低数据导入的效率
                2.增加维护成本
                3.虽然不建议使用外键约束，但是相关联的列上一定要建立索引
            避免使用触发器
                1.降低数据导入的效率
                2.可能会出现意想不到的数据异常
                3.使业务逻辑变的复杂
            关于预留字段
                1.无法准确的指导预留字段的类型
                2.无法准确知道预留字段中所存储的内容
                3.后期维护预留字段所要的成本同增加以一个字段所需要的成本是相同的
                4.严禁使用预留字段
        什么是反范式化
            定义：反范式化是针对范式化而言的，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，换句话说反范式化就是使用空间来换取时间。
        为什么反范式化
            1.减少表的关联
            2.增加数据的读取效率
            3.反范式化一定要适度
        维护和优化中要做什么
            1.维护数据字典
            2.维护索引
            3.维护表结构
            4.在适当的时候对表进行水平拆分或者垂直拆分
        如何维护索引
            1.在出现where从句、group by 从句、order by 从句中的列
            2.可选择性高的列要放到索引的前面
            3.索引中不要包括太长的数据类型
            注意事项：
                1.索引不是越多越好，过多的索引不但会降低读效率还会降低写效率
                2.定期维护索引碎片
                3.在SQL语句中不要使用强制索引关键字
        如何维护表结构
            1.使用在线变更表结构的工具
            2.同时对数据字典进行维护
            3.控制表的宽度和大小
        数据库中适当的操作
            1.批量操作VS逐条操作
            2.禁止使用Select* 这样的查询
            3.控制使用用户自定义函数
            4.不要使用数据库中的全文索引
        表的垂直拆分
            1.经常一起查询的列放到一起
        表的水平拆分