数据库基础知识

POSTGRESQL基础部分

1.1 SQL语法

SQL是一种声明式语言，和命令式编程语言不通，声明式编程语言是描述用户需要做什么，需要得到什么结果，而不是像命名式编程语言给出过程怎么做，过程是什么。类比join，就能反映出来，join实际上是将不同的行拼凑为了新的结果，并最终执行检索操作。

SQL分为三种分类

• DQL
• DML
  • 插入
  • 更新
• DDL
  • 建表
  • drop表

2.1 psql工具

psql是postgresql默认的交互工具

常用的指令（需要先登录进psql，走交互模式）

• \l，用于查看有哪些数据库
• \c，后面跟数据库名称，用于连接到具体的数据库
• \d [pattern]+ ，用于描述匹配pattern（表，视图，索引，序列）的信息。如果不追加pattern，会列出数据库当中所有的表。这个命令可以加正则描述符。此外可以用\d+来显示更详细的信息
• \h，用于查询具体的命令，比方说\h create user

3.1 数据类型

• 布尔类型，一字节存储空间

  • postgresql的bool实际上是三种类型，true、false、null。null表示未知

• 数值类型

  • samllint，两字节；
  • int，四字节；
  • bigint，八字节；相比于int类型，bigint慢很多
  • 精确类型小数，numeric(别名decimal，用时一般加上一个（），里面指名精度)；一般
  • 非精确类型浮点小数，real，double precision。如果要求精确地表示计算，应该使用numeric；如果做重要的计算，包括对无穷，下溢做计算，那么应该评估要不要用这种类型；另外两个此类型数字做比较的结果是不一定的
  • 八字节货币，money

• 字符类型：postgresql的字符串支持多种函数，比方说二进制编解码为base64，使用md5对字符串进行操作

  • varcher(n)，变长，存储空间为4+实际的长度。最大的长度为1GB。实际上存储可能是一个指针，指向真正字符串的地址。
  • char（n)，定长，如果长度不足那么char会自动地用空格补足长度
  • char(n)，text。varchar最长可以1GB

• 二进制类型：

  • bytea：二进制串可以用来存储不可见字符，即可以存储图片这种类型；交互需要使用转义字符
  • 位串：

• 日期类型：

  • 	空间		最小值	最大值	分辨率
    Timestamp(p)	8字节		4713BC	5874897AD	1m s/14位
    Timestamp(p)	8字节	日期+时间，带时区	4713BC	5874897AD	1m s/14位
    Interval(p)	12字节		-178000000年	178000000年	1m s/14位
    Date	4字节	日期	4713bc	5874897AD	1天
    Time(p)	8字节	仅一天，不带时区	00:00:00	24:00:00	1m s/14位
    Time(p)	12字节	一天，带时区	00:00:00+1459	24:00:00-1459	1m s/14位

    时间类型有一些问题需要注意，输入的格式可能不是默认的，有时候输入的明明以为是月份的位置，最终可能变成日，因此建议指定输入的格式。另外请注意，输入时区的时候不要用简写，比方说CST就是好几种不同的时区，比方说Cuba ST，或者Chain ST啥的。

    另外一点需要注意，对于同一个事务而言，获取时间的函数都会返回一个相同的值

• 数组类型

• 枚举类型

  • postgresql的枚举类型，需要先创建，使用create type来创建
  • postgresql的枚举类型的大小顺序是按照创建时候的顺序确定的

• xml类型：postgresql支持xml类型，xml类型的数据如果用字符串存储，那么需要用户保证正确性。相反，如果让postgresql启用xml支持，它就可以省事的做校验了

• json/jsonb类型

  • jsonb以二进制形式存储，因此会被转换为postgresql内部的数据类型，可能损失精度/增加精度。jsonb可以创建索引，而json不能创建索引，这是两者的区别
  • postgresql支持对json操作的多种函数，比方说提取key，提取value，返回指定key的value（返回值为text）

3.1 postgresql逻辑结构管理

postgresql的组织结构可以分为如下三种：

• 数据库
• 表，索引：postgresql称为Relation，其他数据库称为Table
• 数据行：在postgresql称为Tuple，其他称为Rows

3.1.1 数据库基本操作

数据库基本操作：

• 创建数据库
• 修改数据库：事务块里面可以删除数据库
• 删除数据库：注意，事务块里面不能删除数据库

3.1.2 模式基本操作

postgresql里面有个模式的概念，模式（schema）是数据库里面的一个概念，理解为命名空间或目录，不同的模式下可以有相同名称的表，函数等对象而不会产生冲突。这里面模式大部分情况需要用户执行授权

在postgresql里面一个数据库包含一个或者多个模式，模式又包含表，函数。postgresql不允许同事访问不同数据库的对象，模式则没有限制。从这个特性来说，postgresql的模式和mysql的database概念是对应的。oracle数据库里面，一个用户对应一个schema。总之模式存在的主要原因：

• 允许多个用户使用同一个数据库而用户不会相互干扰
• 把数据库对象放在不同模式组织成逻辑组，方便数据库对象管理
• 第三方应用可以放在不同的模式中，这样就不会和其他对象冲突

如何创建模式呢？

create schema schemaname [authorization username] schema_element

可能的问题点，因为postgresql不能垮数据库访问，因此如果做移植操作，那么mysql中的三个数据库，在postgresql里面最好是对应三个模式

3.1.3 表

创建表的命令如下，如果希望加一下特定的检查，那么这个检查也可以用类似constraint的方式加在末尾,default用于表示默认值

# 单个列作为主键
create table table_name (col_name col_type primary key...)
# 多个列作为联合主键
create table table_name (col_name_1 col_type_1 [default xxx], col_name_2 col_type_2 [default xxx], col_name_3 col_type_3, col_name_4 col_type_4, col_name_5 col_type_5... CONSTRAINT common_constraint key(col_name_x, col_name_y, ...))

可以将其它表作为模板来创建，语法如下

这里的继承只会有属性，不会有约束，如果希望有约束可以用下面的指令

下面介绍TOAST的概念

临时表，postgresql支持两种临时表

• 会话级的临时表：会话结束的时候，表才会消失，数据会一直保存在会话的生命周期里。因此不同的session的临时表实际上是不一样的
• 事务级的临时表：事务结束，就会丢失

语法如下

create temporary table

unlogged表

约束

• 检查约束
• 非空约束
• 唯一约束
• 主键
• 外键约束：用于约束本表中的一个或多个字段的树枝必须出现在另一个表的一个或多个字段里面。也被称为两个相关表之间的参照完整性约束

修改表，alter table

表继承：注意事项有点多，需要多注意

表继承用于实现分区表，流程如下

约束排除

3.1.4 触发器

触发器可以帮用户实现很多自由自在的功能，比方说记录执行日志

创建触发器的语法为，一般就是先创建一个执行函数，此函数的返回值为触发器类型；然后创建对应的触发器，用下面的语法

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }  
    ON table_name  
    [ FROM referenced_table_name ]  
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }  
    [ FOR [ EACH ] { ROW | STATEMENT } ]  
    [ WHEN ( condition ) ]  
    EXECUTE PROCEDURE function_name ( arguments )  

where event can be one of:  

    INSERT  
    UPDATE [ OF column_name [, ... ] ]  
    DELETE  
    TRUNCATE  

语句级触发器和行级触发器，触发器可以在语法或者行的层次执行，语句级别只执行一次，而行层次会多次执行；语义级别的触发器即使影响的行很多，也只会执行一次。

Before触发器和After触发器

• before和after可以针对行和语句，其中语句的before最先，语句的after最后
• 对于行级触发器，before和instead of如果返回null，就啥也不做。对于insert和update，如果返回非null的行，那么就会执行在新的行里面里面的内容，after的返回值会被忽略
• 如果有多个触发器，如果是before/instead of的触发器，每个返回的行（可能已经被修改）会成为下一个触发器的输入
• 触发器里面的变量
  • NEW
  • OLD
  • TG_NAME
  • TG_WHEN
  • TG_LEVEL

事件触发器

• DDL_COMMAND_START
• DDL_COMMAND_END
• SQL_DROP

3.1.5 表空间

表空间实际上就是指定表的存储目录，创建数据库的时候就可以指定默认的空间。不过更改表空间的语义是有副作用的，这个需要注意

3.1.6 视图和索引

视图是查询语句定义的虚拟表，可以用来组合多种数据。不过需要知道视图实际上是可读的，可以通过触发器把对视图的操作转换为对表的操作

索引用来快速查找表记录，常见的索引类型如下

• B-tree：最常用的索引
• Hash：针对等值查询
• GiST：一种特定的架构，可以在架构上实现很多的不同的索引策略

3.1.7 用户和权限管理

权限管理可以说是最简单的控制的方法了，可以方便的控制是否可以操作数据库。

创建用户和角色的语法如下：

-- 创建用户
CREATE USER name [[ WITH ] option [...]]

-- 创建角色
CREATE ROLE name [[ WITH ] option [...]]

备注：在PG中，用户与角色是没有区别的，角色默认没有login权限，无法登陆，如果授予login之后，也可以像用户一样登陆。

option常用选项如下：

• SUPERUSER

  NOSUPERUSER：创建出来的用户是否为超级用户

• CREATEDB

  NOCREATEDB：创建出来的用户是否有create database的权限

• CREATEROLE

  NOCREATEROLE：创建出来的用户是否有创建其它角色的权限

• CREATEUSER

  NOCREATEUSER：创建出来的用户是否有创建其它用户的权限

• INHERIT

  NOINHERIT：确定角色是否继承其它角色的权限

• LOGIN

  NOLOGIN：创建出来的角色是否有登录权限

• CONNECTION LIMIT n：创建出来的角色并发连接数限制数量，默认值是“-1”,表示没有限制
• VALID UNTIL ‘timestamp’：密码失效时间

用户的权限分两类，一类是在创建用户时就指定的权限，有：

• 超级用户的权限
• 创建数据库的权限
• 是否允许login的权限
• 更多见\help create role

这些权限是创建用户时指定的，后面可以使用alter role来修改。

另一类是有GRANT和REVOKE命令来管理的，有：

• 在数据库中创建schema的权限
• 在指定的数据库中创建临时表的权限
• 连接某个数据库的权限
• 在某个数据库中创建数据库对象的权限，如表、视图、函数等
• 在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的权限
• 在一张表的列上做 SELECT 、UPDATE、DELETE等操作的权限
• 对序列进行查询(执行序列的currval函数)、使用(执行序列的currval和nextval函数)、更新的权限
• 在表上创建触发器的权限
• 把表、索引创建到指定表空间的权限

总之postgresql中的权限是按照以下几个层次进行管理的：

1.首先管理赋予用户的特殊权限，如超级用户的权限，创建数据库的权限、创建用户的权限、login权限等

2.然后是在数据库中创建schema的权限

3.接着是在schema中创建数据库对象(如表、索引)的权限

4.之后是对表进行操作(insert、update、delete、select)的权限

5.最后是操作(update、delete、select)表中某些字段的权限

3.1.8 事务和锁

3.1.9 执行计划

什么是执行计划？数据库的执行计划通俗点说就是，数据库服务器在执行sql语句的时候，会准备几套方案，最后选择消耗资源最小的那个方案。在postgresql可以估算出来具体执行的代价

语法如下，其中analyze会让语句真正地执行一次

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

这里 option可以是：

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

可以通过执行计划看到postgresql内部的部分优化

3.1.10 postgresql技术内幕

先解释几个名词

• xmin
• xmax
• cmin
• cmax

这些有什么用呢？

• 新插入一行：新插入行的xmin为当前事务ID，xmax为0
• 修改一行，实际上是插入新的一行，元数据行的xmin不变，xmax改为当前事务ID，新的数据行上面的xmin改为当前的事务ID，xmax为0
• 删除一行的时候，把被删除的行上面的xmax填写为当前事务ID

因此，xmin实际上是插入数据行的事务ID，xmax用来标记删除数据行的事务ID（这里面还藏了一个事务ID递增这么一点），postgresql的mvcc就是这么实现的

相比较mysql和oracle数据库的旧版本数据存储在原先的数据块里面，postgresql的好处是

• 事务回滚可以立即完成
• 数据可以进行多更新，不必像oracle和innodb那样需要保证回滚段不会被用完

缺点是

• 旧版本数据需要清理
• 旧版本的数据会导致查询慢

3.1.11 物理存储结构

结尾

唉，尴尬

• Previous
  2023-08-21-工程师修养
• Next
  2024-05-10-suricata