数据库设计

2022-10-18 11:49:04

概念数据模型设计

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可能成为单点故障
管理难度加大

码农公寓

概念数据模型设计

物理数据模型设计

分区、分库、分表

相关文章