数据库内容概览

------------恢复内容开始------------

 

1   概述

自顶向下的设计。

 

2   数据库设计

1.理解系统：系统结构图，数据流图。领域驱动模型。 

        2.对象实体划分。E-R关系图。不同的组件不相依赖。减少对象之间的依赖。

        3.满足范式：字段的不可分，表的不可分。

 

3   表设计

3.1 领接表

 

3.2 路径表——树形结构的数据

 

3.3 纵向表——额外属性扩展表

属性表：属性ID，属性名称。

属性信息表：属性ID，所属对象ID，属性值。

3.4 闭包表（字典表）——包含所有对应关系

 

3.5 嵌套表

 

3.6 M：N表

M一张表，N一张表，M：N的对应一张表。

3.7 冗余表

每次往主表录入信息时，进行一定的计算得出其他信息存入冗余表，方便后续的查询和计算。

 

4   字段设计

4.1      原子性，不可拆分

4.2      主键尽量自增长的数字，不要与业务逻辑有关，最好无意义

4.3      尽量不使用NULL，使用默认值0或者特殊字符替代

4.4      尽量使用枚举 而不是字符串

对于经常修改的字段，尽量使用枚举或者独立的字母。

4.5      尽量不使用外键，使用id与其他的表关联

4.6      资源存储

不要直接存储资源，用资源的文件路径代替。程序中访问路径来访问资源。

4.7      增加字段 是否可用

方便表数据整理，剔除。

 

5   应用设计

 

     动静分离。

 

      数据分区。

 

      建立索引。

 

   SQL设计

 

  提前过滤，使用索引，不要全表扫描。like，null，not，not in，索引列上的or操作会造成全表扫描，使用union。

 

5.1 索引设计

 索引——将要索引的列的所有数据复制出来建立一个B树的数据结构。查找次数为logN(底数为树的叉数)。N为总数。所以提高了查询效率。因为更新表，要维护B树，所以影响更新效率。

唯一索引、主索引、聚集索引、非聚集索引。覆盖索引（多个字段的联合非聚集索引）直接通过非聚集索引找到结果，查询速度快。非聚集索引通过聚集索引最终找到数据行。

 

对于经常要查询的条件字段考虑建立非聚集索引。

5.2 数据库选型

事务型和并发型

SQL优化原则

    5.3 表设计

      动静分离。

      数据分区。

      建立索引。

   SQL设计

  提前过滤，使用索引，不要全表扫描。like，null，not，not in，索引列上的or操作会造成全表扫描，使用union。

 

6   SQL常见优化规则

   本质是要减少执行次数。

   

6.1 表连接数

·        连接的表越多，性能越差

·        可能的话，将连接拆分成若干个过程逐一执行

·        优先执行可显著减少数据量的连接，既降低了复杂度，也能够容易按照预期执行

·        如果不可避免多表连接，很可能是设计缺陷

·        外链接效果差，因为必须对左右表进行表扫描

·            尽量使用inner join查询

6.2 使用临时表

如果不可避免，可以考虑使用临时表或表变量存放中间结果。

6.3 少用子查询

6.4 视图嵌套

不要过深,一般视图嵌套不要超过2个为宜。

6.5 SQL编写注意事项

6.5.1 NULL列

Null列使用索引没有意义，任何包含null值的列都不会被包含在索引中。因此where语句中的is null或is not null的语句优化器是不允许使用索引的。

6.5.2 concat或||

concat或||是mysql和oracle的字符串连接操作，如果对列进行该函数操作，那么也开会忽略索引的使用。比较下面的查询语句：

-- 忽律索引

select ... from .. where first_name || '' || last_name = 'bill gates' ;

-- 使用索引

select ... from .. where first_name = 'bill'and last_name = 'bill gates' ;

6.5.3 like

通配符出现在首位，无法使用索引，反之可以。

-- 无法使用索引

select .. from .. wherenamelike'%t%' ;

-- 可以使用索引

select .. from .. wherenamelike't%' ;

6.5.4 order by

order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

6.5.5 Not运算

not运算无法使用索引，可以改成其他能够使用索引的操作。如下：

-- 索引无效

select .. from .. where sal != 3000 ;

-- 索引生效

select .. from .. where sal < 3000  or sal > 3000;

6.5.6 where与having

select .. from .. on .. where .. group by .. having .. order by .. limit ..，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

6.5.7 exists替代in

not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。如下：

-- 正确

SELECT  *

FROM EMP 

WHERE  

    EMPNO > 0

    AND  EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

-- 错误

SELECT  * 

FROM  EMP 

WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

6.5.8 索引

索引的好处可以实现折半查找，时间复杂度是

O(log2n)O(log2n)

，但是也有成本，需要额外的空间存放索引数据，并且每次insert、update和delete都会对索引进行更新，因此会多增加4、5次的磁盘IO。所以给一些不必要使用索引的字段增加索引，会降低系统的性能。对于oracle来讲，SQL语句尽量大写，内部需要向将小写转成大写，再执行。

不要在索引列上使用函数，这样会停止使用索引，进行全表扫描，如下：

-- 错误

SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 

-- 正确

SELECT … FROM  DEPT  WHERE SAL > 25000/12; 

6.5.9 >与>=

-- 直接定位到4的记录(推荐)

select .. from .. where SAL >= 4 ;

-- 先定位到3，再向后找1个(不推荐)

select .. from .. where SAL > 3 ;

6.5.10 union代替or

在索引列上，可以使用union替换or操作。索引列上的or操作会造成全表扫描。

-- 高效: 

SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10

UNION

SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = 'MELBOURNE'

-- 低效: 

SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID=10OR REGION ='MELBOURNE'

6.5.11 is null & is not null

如果列可空，避免使用索引。对于多个列使用的索引，起码保证至少有个列不为空。对于多列索引，只有访问了第一个列才会启用索引，如果访问后面的列则使用的是全表扫描。

-- 低效: (索引失效) 

SELECT .. FROM  DEPARTMENT  WHERE  DEPT_CODE ISNOTNULL; 

-- 高效: (索引有效) 

SELECT .. FROM  DEPARTMENT  WHERE  DEPT_CODE >=0; 

6.5.12 union & union all

union具有去重的操作，增加了计算时间。union all不需要去重，但会包含相同记录。同样功能下，首选union all操作。

 

------------恢复内容结束------------