看那头大象

业界有三只有名的大象,Hadoop, PHP和本回要说的PostgreSQL

本文起因是一个数据聚合的问题。在说它之前先看一些基本的东西。

P.S. 后期修补很多,导致内容看起来没Postgres什么事了ಠ_ಠ
跳转到第二部分

范式

提起关系数据库,必谈范式,也就是Normal Form,简称NF。常规情况下分1NF,2NF,3NF直到BCNF。这些概念有点被玄化,我试试通俗解释。

首先所有高级范式的成立必须先满足其低级范式。

1NF

常规关系数据库,只要成功建表就基本符合1NF:

  1. 表头字段是原子的。不能出现嵌套,或重复的表头。
  2. 每一列只存放单一值。Postgres的数组列就不符合第一范式。
  3. *单张表的每一行,又称元组tuple),不能有数据重复。流行的偷懒技巧是设主键

如果万物只是一对一关系,就没有后面什么事了。我觉得高级的范式解决的问题,第一是一致性,第二是冗余。也就是说,只有形式上是一对多或多对多才有高级范式的存在价值。

2NF

菜鸟遇到多对多关系,最常见的思维模式就是Alter表头,看这个身份信息与存货的表头。

|ID|region|tax|stock1|stock2|stock3|

这其实已经违反了1NF,而且没考虑存货的属性。稍有经验会这么做:

|ID|region|tax|stock_code|stock_name|industry|

但这个还是有明显问题。2NF保证:

  1. 单张表的所有数据和主键或候选键之间必须是完全依赖的关系。表二实际上符合直觉,它作为试图来看是成立的。从关系上看就不对了。我们設ID+存货代码为主键,如果此时多人持有同一存货,局部数据可能出现重复,重复往往代表冗余和可能的不一致。存货信息并不依赖主键,只依赖存货代码(部分主键)。反过来一人持有多种货物也是同样的问题。
  2. 解决方法就是拆成两张表:
identity:   |ID|region|tax|stock_code
stock:  |code|name|industry|

但是,这么做好像还缺点什么。现在身份表的主键是ID+存货代码。地域和税收还是依赖部分主键。所以再拆:

identity: |ID|region|tax|
stock: |code|name|industry|
id_stock_rel: |uid|stock_code|

啊,这不是就是最普通的多对多关系么?

P.S. 2NF另一中翻译是没有任何非主属性依赖任何候选键的任何真子集。这样解释我觉得更加容易串联3NF。

P.S.2 候选键指组成属性最少的超键,超键指任何可以作为主键的属性组合成的主键的集合。主属性指组成候选键的属性。真子集指一个不等于父集的子集。

3NF

我觉得3NF其实更简单,但是非常严格。通俗的定义:

  • 单个表中任何表头只和候选键相关,而不是非键表头。
  • 另一种解释,非键表头不能间接依赖候选键。

上述第三张图的个人信息表,我们发现税收是和地域相关而不是主键ID,地域依赖iD,税收就是间接依赖主键那么他就不符合3NF,可以接着拆:

|ID|region|
|region|tax

另一种违反规则情况就是帐目表最后一列的总计或总额。所以日常大多情景都达不到3NF。

这里大致可以总结为2NF解决了非主属性对候选键的部分依赖问题,3NF解决了非主属性对候选键的间接依赖问题。

BCNF

BCNF出现的场景就非常苛刻了。基于3NF,并且任何候选键都不依赖其他候选键。有个经典例子,有表(仓库,管理员,存货,数量),每个仓库和管理员是一对一关系,货物随机仓库存放。这里涉及到一个问题,这里主键可以是(仓库,存货)或者(管理员,存货),无论如何都有一方违反BCNF,仓库和管理员是部分依赖。另外按照上文的一种思路,(仓库,管理员)可能会有重复数据,重复就是冗余,就是有可能不一致。所以需要拆分(仓库,管理员),(仓库,存货,数量)。

BCNF解决的是多候选键之间的部分依赖和间接依赖问题。

日常操作,即使不懂范式也知道一对多,多对多几种拆表法,无非是建外键。这实际是从形式入手。而范式是从逻辑上指导何时该拆,拆什么。

PostgreSQL

PostgreSQL号称世界上最先进的开源数据库。事实上我一个接触的数据库就是它,当时我的认知还在DB就是另一种excel。写本文时postgres已经10.3了,版本的进化伴随着大量特性。某种程度上我也是菜鸟,下文从安装配置开始,找一点有趣的事。

安装

记得以前配置服务端很麻烦。从官网先导入repo。再执行:

dnf install postgresql10 postgresql10-server

我使用fedora,其他系统可参考官文。

安装套件已经有了postgresql-10-setup,可以一键初始化:

/usr/pgsql-10/bin/postgresql-10-setup initdb

工具自动装在这个位置,很奇怪,然而非常好用,还能在版本更迭时通过upgrade子命令更新数据库。配置文件夹的位置也很奇怪,/var/lib/pgsql/10/。在./initdb.log日志里可以看到:

Success. You can now start the database server using:
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start

这个命令并不好用。不如手动起服务:

service postgresql-10 restart

有兴趣看看官方手册,内容完善,质量么…

建库表

PG有快捷命令,直接使用createdb <DB_NAME>建库。这里可能会遇到:

createdb: could not connect to database postgres: FATAL:  role "joe" does not exist

暂时你得切换postgres用户:

sudo -i -u postgres
createdb mydb
#dropdb mydb

现在还多了一个快捷命令createuser,接着使用普通用户登录:

postgres# createuser fat -sldW #superuser login createdb password
fat# psql mydb

远程登录,需要改动配置文件pg_hba.conf:

# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
  host all      all 127.0.0.1/32 md5
# IPv6 local connections:
  host all      all  ::1/128     md5

这两行Method改为md5,否则服务端不接受密码。现在看来配置PG还是比较麻烦,不过它本身也不是开箱即用的。

基本命令

官方有一些测试数据库。我随便下载了world.sql,进入文件目录,登录PG。导入数据库:

\i world.sql
  • \i 导入数据库
  • \list 查看数据库列表
  • \dt 查看表格列表
  • \password 改密码
  • \q 退出

额,PG是不能show dbs和show tables。

在SQL前加explain analyze,可以出一个简单的分析报告,比如:

explain analyze select 1;

类型

PG的CRUD没什么特别,数据类型倒是很有趣。看一些比较特别的

  • serial,相当于简化的int auto increment。
  • numeric,相当于var-float;
  • money,就是字面上的意思…
  • arrays,这个就又厉害了。它可以简化很多关系数据库,不过容易被滥用。官方提供了一个构造函数 ARRAY[]。查询数组内元素可以 ‘sth’ = any []或 ‘sth’ = all []。
  • JSON,这算是NoSQL的特性了。据说jsonb比bson性能还快一些。有机会单独写博文。

回到问题

电商界很早就在探讨,如何设计商品数据库。这个问题可以分两部分:

  1. SQL数据库如何处理继承问题
  2. SQL数据库如何处理多种分类问题

举个简单例子:

//product
{
  ID,
  price,
  description,
}
//book inherits product
{
  author,
  title,
  ISBN,
}
//clothes inherits product
{
  pattern,
  size,
}

传统SQL建模可能是这样:

//product
{
  ID serial primary key,
  price money,
  description text, 
}
//book
{
  author varchar,
  title varchar,
  ISBN varchar,
  productID int references product(ID),
}
//clothes
{
  pattern varchar;
  size varchar;
productID int references product(ID),
}

首先,这种设计勉强解决了继承问题,插入一条数据要改两张表。如果使用PG的特性可以直接inherits继承:

create table book(author varchar, title varchar, ISBN varchar) 
       inherits (product);
create table book(pattern varchar, size varchar) inherits(product);

其次出现上万中商品,岂不是要上万张表。后人搞出了EAV(Entity Attribute Value),这种设计只是理论可行,实际很麻烦:

entity|attribute|value
book  |author   |fat
book  |title    |math101
book  |ISBN     |00XBS021

这样的结构用NoSQL是很简单的,但是得放弃一致性:

{
 ID: '', 
 price: '', 
 description: '', 
 type: 'book',
 
 author: '', 
 title: '', 
 ISBN: '', 
}
//
{
 ID: '', 
 price: '', 
 description: '', 
 type: 'clothes',
 
 pattern: '',
 size: '',
}

现实中,我遇到了类似的问题,有想过用MongoDB,又听说说PG揉了各种特性进去,就回顾了一下。有缘再谈谈NoSQL。

Leave a Reply

Your email address will not be published. Required fields are marked *