PostgreSQL-表、约束、索引

PostgreSQL / 2024-02-27

基本操作

  1. 创建表
    create table logs(log_id serial PRIMARY KEY, user_name varchar(50),description text,log_ts timestamp with time zone NOT NULL DEFAULT current_timestamp);
    create INDEX idx_logs_log_ts ON logs USING btree (log_ts)
    2.继承表
    pgsql 是唯一提供表继承功能的数据库。
    所谓继承：顾名思义。创建一张子表A时指定为继承自另外一张表B
    ，则建好的新表A除了含有自己的字段外还会含有附表的所有字段。

    CREATE TABLE logs_2011 (PRIMARY KEY(log_id)) INHERITS(logs);

  2. 无日志表
    pgsq从9.1开始支持UNLOGGED修饰符，创建无日志的表。系统不会为这种表记录任何事务日志，掉电重启，无日志表中的数据会在事务回滚过程中被全部清除掉。

    create unlogged table web_sessions(session id text primary key)

    优点：
    对其写入数据远远快于往普通表快，大概快15倍
    缺点：
    数据库服务器崩溃，pqsq截断所有无日志表（擦除所有行）
    无日志表不支持GiST索引，不适合依赖GiST索引的数据类型，可以建立B-tree索引和GIN索引

  3. TYPE OF
    创建一张表时候，会自动在后台创建一个结构完全相同的符合数据类型，反之则不会。

    创建复合数据类型
create type basic_user as (user_name varchar(50), pwd varchar(10));
以复合数据类型为模板来创建一张表
create table super_users OF basic_user(CONSTRAINT pk_su primary key(user_name));

    当基于数据类型来创建表时，你不能指定表字段的定义，一切以数据类型本身的定义为准。然而，为复合数据类型新增或者移除字段时，pgsq会自动修改相应的表结构。

    场景：系统中有很多结构相同的表，如果需要对所有表结构进行相同的修改只需修改此基础数据类型即可。

    增加一个phone 字段
    alter type basic_user add attribute phone varchar(10) cascade;

  4. 约束
    1 外键约束
    2 唯一约束
    3 check约束

    5.1 外键约束

    建立外键约束和相应的索引
    set search_path=census,public;
    alter table facts add constraint fk_facts_1 foreign key (fact_type_id)
    references lu_fact_types (fact_type_id) //主表lu_fact_types中不存在某fact_type_id的记录，那么从表fact中就能插入该fact_type_id的记录。
    on update cascade on DELETE RESTRICT;//级联规则，主表的fact_type_id字段值会自动进行相应的修改，以维持外键引用关系不变。如果从表fact中还存在某fact_type_id，那么主表lu_fact_type中相同fact_type_id字段值的记录就不允许被删除。
    create INDEX fki_facts_1 on facts(fact_type_id);

    5.2 唯一性约束
    主键字段的值是唯一的，但每张表只能定义一个主键，因此如果你需要保证别的字段值唯一，那么必须在该字段上建立唯一性约束或者说唯一索引。建立唯一性约束的时会自动在后台创建一个相应的唯一索引。与主键字段类似，建立了唯一性约束的字段不允许为空，并且可以作为外键字段被别的表引用。建立了唯一索引但没有唯一约束的可以输入空值。
    ALTER TABLE logs_2011 ADD CONSTRAINT uq UNIQUE (user_name,log_ts)
    5.3 check约束
    check约束能够对标的一个或者多个字段加上一个条件，表中每一行记录必须满足此条件。查询规划器也会利用check约束来优化执行速度，有些查询附带的条件与待查询表的check约束无交集，那么规划期会立即认定该查询未命中目标并返回。
    限制logs表中的所有用户名小写
    ALTER TABLE logs ADD CONTRAINT chk CHECK（user_name=lower(user_name));
    特别值得注意的一点是，当表间存在继承关系时，子表会继承父表的check约束，但主键、外键、唯一性这三种约束却不会继承。
    5.4 排他性约束
    解决时间安排的类似场景。用字段的值在任意两行都不相等的算法来排他。一般是基于GiST类型的索引来实现，使用基于B-tree算法的的GiST多列符合索引也是可以的，不过需要先安装btree_gist扩展包才能建立这种索引
    场景：预定会议室冲突
    create table schedules（id serial primary key，room smallint，time_slot tstzrange）;
    alter table schedules add contraint ex_schedules exclude using gist(room with=,time_slot with &&)
    用=号来判断会议室房间号是否重复，&&运算符来判定时间区段是否重叠。
    6.索引
    pgsq索引机制功能强大，特性丰富。
    1 支持在同一个表中混合搭配不同的索引类型，且预计规划器将综合考虑所有的索引。
    2 同一个表上的索引名不允许重复
    3 原生索引
    3.1 b-tree索引是默认索引
    3.2 GiST索引
    Generalized Search Tree 搜索树，主要用于空间数据，科学数据、非结构化数据和层次化数据的搜索。
    缺点：不能保证字段唯一性；有损索引，它不存储被索引字段的值，而仅仅存储字段值的一个取样，这种取样是失真的。
    3.3 GIN索引
    GIN的全称是Generalized Inverted Index（GIN）通用逆序索引
    适合场景：
    pgsq内置全文搜索引擎以及jsonb数据类型。
    从GiST派生的，但是无损的，索引中包含有被索引字段的值。查询字段都已被索引，那么只读取索引可以获得查询结果，这种情况下GIN查询速度是快于GiST的。GIN在更新操作时比GiST多出一个字段值复制动作。
    GIN的索引数内部每一个索引行的长度是有限制的，不能对于text等大对象类型进行索引。
    3.4 SP-GiST索引
    SP-GiST 是指基于空间分区树（Space-Partitioning Trees）算法的GiST索引，该类型的索引从9.2开始引入，与GiST索引适用领域相同，但对于某些特定领域的数据算法，效率会高一些。
    3.5 哈希索引
    哈希索引在GiST和GIN索引出现前就已经得到了广泛使用。但是事务安全性和索引性能方面不如其余两位索引。pgsq中最好避免使用。
    4 运算符类
    pgsq把一类应用领域相近的运算符以及这些运算符适用的数据类型组合在一起称为一个运算符类（opclass）。
    int4_ops 运算符类包含适用于int4类型的= < > > <运算符
    指定运算符类的语法
    create index idx1 ON census.lu_tracts USING btree(tract_name text_pattern_ops)
    注意：每一个索引都只会使用一个运算符类。如果希望一个字段上的索引适用多个运算符类，请创建多个索引。
    单个字段上可以建立索引的个数是没有限制的。
    5 函数索引
    MySQL不直接支持函数索引，pgsq和oracle都支持函数索引。
    基于字段值的函数运算结果建立索引。
    create index fidx on featnames_short using btree (upper(fullname) varchar_pattern_ops);
    建立了该索引之后，类似
    select fullname from featnames_short where upper(fullname) like "s%" 这种sql就能用上索引。
    查询语句中使用的函数要与减建函数索引时使用的函数完全一致，才能保证用上索引。
    6 基于部分记录的索引
    仅针对表中部分记录的索引，而且这部分记录需要满足where 语句设置的筛选条件。假设有100万条记录，只查询1万条的子集，这种场景比较适合。
    此时比全量索引快，因为体积小，把更多索引数据缓存到内存中，另外该类索引占用的磁盘空间也会更小。
    此类索引能够实现仅针对部分记录的唯一性约束。
    create table subscribers(id serial primary key,name varchar(50) not null,type varchar(50),is_active boolean);
    我们建立一个基于当前活跃用户的部分记录索引即可
    create unique index uq ON subscribers using btree(lower(name)) where is_active;
    7 复合索引
    create index idx on subscribers using btree(type, upper(name) varchar_pattern_ops)
    pgsq的规划器在语句执行过程中会自动使用一种被称为“位图索引扫描”的策略来同时使用多个索引。
    索引中包含的字段越多也就意味着索引占用的空间会越大，能在内存中缓存的索引条目就越少。请不要滥用复合索引。

相关文章

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注