数据库设计

概念数据模型设计

1.遵守范式

(1)第一范式

可以理解为属性不可分割性范式,即表中的每一个属性,都不能进一步分割为更小的单元。

反例:

  • 属性多值:比如人员表Person中,电话号码可以有多个,使用字符串数组或者逗号分隔符保存。

  • 属性多子属性:比如数据库表的某个属性设计为json字符串格式。当子属性不固定且为动态时,尤其容易出现此种设计,这种设计使得对该字段的检索变得异常复杂。

需要根据具体情况,进行子表拆分,或者使用索引化存储(如Elasticsearch)。当然,如果不涉及到上述负面效果,而仅用于展现时,可以根据实际情况酌情考虑。

(2)第二范式

可以理解为非主属性应当完全依赖于主键范式,即表中的每一个非主属性,都应当完全依赖该表的主键,而不应当是主键的一部分属性。该范式解决了非主属性对主键的部分依赖问题。

反例:

数据冗余-单值代码:Artery单值代码表存在联合主键c_pid+c_code,唯一确定了代码值。此时如果我将主表中代码类型名称冗余进单值代码表中,则会违反第二范式。因为代码类型名称依赖了联合主键c_pid+c_code的一部分属性c_pid,即c_pid可以唯一确定代码类型名称,这产生了部分依赖问题。

(3)第三范式

可以理解为非主属性应当不传递依赖非主键

反例:数据冗余

比如人员表Person,为了便于根据组织机构检索其所属单位,将单位名称冗余进来。由于单位名称依赖于单位id,而单位id依赖于人员主键,违反了第三范式。由于单位名称传递依赖于人员主键,因此不违反第二范式。

(4)BC范式

第一、二、三范式对非主属性进行了约束,当对主属性也进行类似于第三范式的约束时,即为BC范式:主属性不应当依赖于主属性

一般数据库设计,都要求我们使用业务无关的唯一列作为主键,在这种情况下如果满足第三范式,则自然会满足BC范式。

3NF优点:

  • 减少数据冗余,减少数据不一致

  • p数据表小,检索快

缺点:

  • 需要进行表关联,可能影响性能

2.一对多设计

n1..1,1..0设计

  • 1440列的表

  • 用一个表还是用多个表?

n1..n

设计为子表

nm..n

设计为关系表:如人员权限表

物理数据模型设计

JSON

  • 用于信息的展示,而不是检索

  • ABase的JSONB可以用于检索

多值代码

  • 用数组是更好的设计

  • 缺点:依赖于ABase数据库

冗余

  • 一般不建议冗余代码名称信息

  • 不建议冗余父代码信息

  • 应冗余子表关键信息

    • 主表冗余:p如办案人员代码、办案人员名称、当事人名称、案由的拼串等需要频繁展示的信息

    • 访问越频繁,对性能的要求越高

分区、分库、分表

分区(postgresql10以下)

(1)创建主表:

CREATE TABLE almart  
(  
    date_key date,  
    hour_key smallint,  
    client_key integer,  
    item_key integer,  
    account integer,  
    expense numeric  
);  

(2)创建分区:

CREATE TABLE almart_2015_12_10 () inherits (almart);  
CREATE TABLE almart_2015_12_11 () inherits (almart);  

(3)为分区表添加限制:

ALTER TABLE almart_2015_12_10  
ADD CONSTRAINT almart_2015_12_10_check_date_key  
CHECK (date_Key = '2015-12-10'::date);  
ALTER TABLE almart_2015_12_11  
ADD CONSTRAINT almart_2015_12_10_check_date_key  
CHECK (date_Key = '2015-12-11'::date);

(4)插入时处理数据插入哪个分区表

对于开发人员来说,希望数据库是透明的,只管 insert into tbl_partition。对于数据插向哪个分区,则希望由DB决定。这点,ORACLE实现了,但是PG不行,需要前期人工处理下

--创建分区函数  
CREATE OR REPLACE FUNCTION almart_partition_trigger()  
RETURNS TRIGGER AS $$  
BEGIN  
    IF NEW.date_key = DATE '2015-12-10'  
    THEN  
        INSERT INTO almart_2015_12_10 VALUES (NEW.*);  
    ELSIF NEW.date_key = DATE '2015-12-11'  
    THEN  
        INSERT INTO almart_2015_12_11 VALUES (NEW.*);  
    ELSIF NEW.date_key = DATE '2015-12-12'  
    THEN  
        INSERT INTO almart_2015_12_12 VALUES (NEW.*);  
    ELSIF NEW.date_key = DATE '2015-12-13'  
    THEN  
        INSERT INTO almart_2015_12_13 VALUES (NEW.*);  
    ELSIF NEW.date_key = DATE '2015-12-14'  
    THEN  
        INSERT INTO almart_2015_12_14 VALUES (NEW.*);  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;  
--挂载分区Trigger  
CREATE TRIGGER insert_almart_partition_trigger  
BEFORE INSERT ON almart  
FOR EACH ROW EXECUTE PROCEDURE almart_partition_trigger();  

(5)为分区键创建索引

​
CREATE INDEX almart_date_key_2015_12_10  
ON almart_2015_12_10 (date_key);  
CREATE INDEX almart_date_key_2015_12_11  
ON almart_2015_12_11 (date_key);

分区(postgresql10及以上)

(1)创建表

这里设置的是根据year列进行数据分表;创建后使用navicat是看不到的;

create table fenbiao(
id int,
year varchar
) partition by list(year)
-- ) partition by range(year)

(2)创建分表

这样数据会依靠规则插入到不同分表中,如果插入一条不符合规则的数据,则会报错误:no partition of relation "fenbiao" found for row.

create table fenbiao_2017 partition of fenbiao for values in ('2017')
create table fenbiao_2018 partition of fenbiao for values in ('2018')
​
create table fenbiao2_2018_2020 partition of fenbiao2 for values from ('2018') to ('2020')

注意事项:

  • 分区字段:经常出现在WHERE子句中的列(或多个列)适合。这样可以快速排除掉不相关的分区。此外,将分区作为整体进行删除非常快,所以如果有能一起移除的数据可以放在同一个分区(如业务数据按时间分区)。

  • 分区数量:分区数量过多或过少都会影响性能。分区数量过多会导致planning time变长(可用explain命令查看)、内存开销变大。建议为2的倍数,这样未来如果有分区过大需要进一步分区时,可以直接分裂分区,而不需要将数据跨分区迁移。

优点:

  • 根据分区条件检索性能提升明显

  • 索引体积小,数据更新更快

  • 便于维护

缺点:SQL的where条件中有分区键性能才有提升

分库、分表

必要性需要论证

  • 没有好机器

  • 各种调优手段用到了极限

  • 数据量非常大

  • 并发访问压力大

  • 单库无法满足性能需求

带来的问题:

  • 变复杂

  • 可能需要多库之间的数据冗余

  • 如果跨库操作,可能需要分布式事务支持

  • 多库之间数据可能不一致

  • sharding可能成为单点故障

  • 管理难度加大

上一篇:函数调用时栈在做什么?


下一篇:2015 偶数求和(杭电)