数据库:mysql基础(八)--数据库的设计和优化

本文链接：https://blog.youkuaiyun.com/XiaodunLP/article/details/83038741

数据库的设计
- 数据库设计是基础，数据库优化是建立在设计基础之上的。好的数据库一定拥有好的设计。
- 数据库设计的目标是为用户和各种应用系统提供一个信息基础设施和高效的运行环境。
- 数据库的三大范式
  - 第一范式1NF:所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
  - 第二范式2Nf:第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  - 第三范式3Nf:所有字段必须与主键直接相关，而不是间接相关。也可以理解为字段不要和其他非主键字段相关.
  - 注意:这三个范式尽可能去遵守,不是一定要墨守成规.这只是让我们设计的表的时候,越靠近这些范式,可以使字段尽量的减小冗余.但是有时候也可以根据实际需要小小的违背一下.但是第三范式违反一下还可以接受,但是第一范式别违反.
数据库设计的步骤
- 需求分析阶段
  - 准确了解与分析用户需求（包括数据与处理）。是整个设计过程的基础，是最困难、最耗费时间的一步。
- 概念结构设计阶段
  - 是整个数据库设计的关键--设计数据库的E-R模型图，确认需求信息的正确和完整
  - Entity – Relationship---实体之间的关系
    - 一对一
    - 一对多
    - 多对一
- 逻辑设计阶段
  - 将E-R图转换为逻辑模型(逻辑模型为关系模型则体现为多张表)
    
    应用数据库设计的三大范式进行审核
    
    设计外模式，建立视图
- 物理设计阶段
  - 确定存取方法（例如索引的设定）
    
    确定存储结构（文件存放的位置等），并编写代码实现前端应用
- 数据库实施阶段
  - 将数据载入，并对数据库进行调试。
- 运行和维护阶段
  - 使用和维护数据库。
E-R图也称实体-联系图(Entity Relationship Diagram)
- 提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。
- 在ER图中有如下四个成分：
  - 矩形框：表示实体，在框中记入实体名。
  - 菱形框：表示联系，在框中记入联系名。
  - 椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
  - 实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1；对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)
数据库的优化
- a)SELECT子句中避免使用(*),ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
- b)索引失效的情况:
  - ① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。则会是索引失效。
  - ② 索引列上不要使用函数,
         SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = ‘ABC’（不）
         SELECT Col FROM tbl WHERE name LIKE ‘%ABC%’ （不）
         SELECT Col FROM tbl WHERE name LIKE ‘ABC%’ （使用）。
  - ③ 索引列上不能进行计算
    SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效
    应该改成SELECT Col FROM tbl WHERE col > 10 * 10
  - ④ 索引列上不要使用NOT （ != 、 <> ）
    如:SELECT Col FROM tbl WHERE col ! = 10 应该改成：union。
- c)用UNION替换OR(适用于索引列)
  - union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。由于是追加操作，需要两个结果集的列数应该是相关的，并且相应列的数据类型也应该相当的。 union 返回两个结果集，同时将两个结果集重复的项进行消除。如果不进行消除，用UNOIN ALL.
  - 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描
  - 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
- d)用EXISTS替代IN、用NOT EXISTS替代NOT IN
  - 在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
  - 在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).
  - 为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
    - 高效: SELECT * FROM EMP (基础表) WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
    - 低效: SELECT * FROM EMP (基础表) WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
  - 索引失效的原因简单记忆就是以下的四点:
    - 索引列进行计算
    - 索引列使用函数(模糊查询:包含)
    - 索引列使用not != < > > < null not null
    - 索引列使用or(可以使用union代替)
备份和还原
- Mysqldump -uroot -proot dbname > d:/t.sql; #对数据库进行备份的语法
- mysql -uroot -p < C:\backup.sql #还原