首页

表、约束和索引

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

本课件完全基于参考文献: Regina Obe Leo Hsu 著, 丁奇鹏 译《PostgreSQL即学即用》

目录

表

表

表是关系型数据库存储体系的基本单元. 设计好结构化的表并且定义表与表之间的关联关系是关系型数据库的核心设计思想.

在 PostgreSQL 中, 约束定义了表与表之间的关系.

约束可以保证数据库的记录不会违反我们制定的规则.

与以堆结构存储的记录相比, 表的优势在于有索引.

PostgreSQL 中的表

除了普通的表以外, PostgreSQL 还提供了许多不常见的表. 具体包括:

• 临时表
• 无日志表
• 继承表
• 基于复合类型的表
• 外部表

基本的建表操作

在所有支持 SQL 的数据库中建表语法都是类似的. 下面我们在 tutorial 数据库中建立一张名为 logs 的表.

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
);

postgres@localhost:5436 tutorial# CREATE TABLE logs(
tutorial(#   log_id serial PRIMARY KEY,
tutorial(#   user_name varchar(50),
tutorial(#   description text,
tutorial(#   log_ts timestamp with time zone NOT NULL DEFAULT current_timestamp
tutorial(# );
CREATE TABLE
时间：56.047 ms

postgres@localhost:5436 tutorial# \d logs
                                        数据表 "public.logs"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设
-------------+--------------------------+----------+----------+--------------------------------------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |          |          |
 description | text                     |          |          |
 log_ts      | timestamp with time zone |          | not null | now()
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)

然后在其中的 log_ts 上使用 btree 数据结构建立名为 idx_logs_log_ts 的索引.

CREATE INDEX idx_logs_log_ts ON logs USING btree (log_ts);

postgres@localhost:5436 tutorial# CREATE INDEX idx_logs_log_ts ON logs USING btree (log_ts);
CREATE INDEX
时间：15.377 ms
postgres@localhost:5436 tutorial# \d logs
                                        数据表 "public.logs"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设
-------------+--------------------------+----------+----------+--------------------------------------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |          |          |
 description | text                     |          |          |
 log_ts      | timestamp with time zone |          | not null | now()
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_log_ts" btree (log_ts)

继承表

继承表

PostgreSQL 是(目前2016年)唯一提供表继承功能的数据库.

如果创建一张表（子表）时指定为继承自另一张表（视为父表）, 则建好的子表除了含有自己定义的字段外还会包含父表中的所有字段.

PostgreSQL 会记录下这个继承关系, 这样一旦父表的结构发生了变化, 子表的结构也会自动跟着变化.

这种父子继承结构的表可以完美地适用于需要数据分区的场景. 当查询父表时, PostgreSQL 会自动把子表的记录也取出来.

值得注意的是, 并不是所有父表的特征都会被子表继承下来, 比如主表的主键约束、唯一性约束、以及索引就不会被继承. Check 约束会被继承, 但子表还可以另建新的自己的Check约束.

例

我们将创建一张继承自 logs 的表. 先查看一下 logs 表的结构.

postgres@localhost:5436 tutorial# \d logs
                                        数据表 "public.logs"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设
-------------+--------------------------+----------+----------+--------------------------------------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |          |          |
 description | text                     |          |          |
 log_ts      | timestamp with time zone |          | not null | now()
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_log_ts" btree (log_ts)

输入下面的 CREATE TABLE 语句, 创建继承自 logs 的表 logs_2025.

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

postgres@localhost:5436 tutorial# CREATE TABLE logs_2025(PRIMARY KEY(log_id)) INHERITS(logs);
CREATE TABLE
时间：108.749 ms
postgres@localhost:5436 tutorial# \d logs_2025
                                      数据表 "public.logs_2025"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设
-------------+--------------------------+----------+----------+--------------------------------------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |          |          |
 description | text                     |          |          |
 log_ts      | timestamp with time zone |          | not null | now()
索引：
    "logs_2025_pkey" PRIMARY KEY, btree (log_id)
继承: logs

在 log_ts 上创建索引.

CREATE INDEX idx_logs_2025_log_ts ON logs USING btree(log_ts);

postgres@localhost:5436 tutorial# CREATE INDEX idx_logs_2025_log_ts ON logs USING btree(log_ts);
CREATE INDEX
时间：49.247 ms
postgres@localhost:5436 tutorial# \d logs
                                        数据表 "public.logs"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设
-------------+--------------------------+----------+----------+--------------------------------------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |          |          |
 description | text                     |          |          |
 log_ts      | timestamp with time zone |          | not null | now()
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_2025_log_ts" btree (log_ts)
    "idx_logs_log_ts" btree (log_ts)
子表的数量：1(可以使用 \d+ 来列出它们)

postgres@localhost:5436 tutorial# \d+ logs
                                                       数据表 "public.logs"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设                 |   存储   | 统计目标 | 描述
-------------+--------------------------+----------+----------+--------------------------------------+----------+----------+------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass) | plain    |          |
 user_name   | character varying(50)    |          |          |                                      | extended |          |
 description | text                     |          |          |                                      | extended |          |
 log_ts      | timestamp with time zone |          | not null | now()                                | plain    |          |
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_2025_log_ts" btree (log_ts)
    "idx_logs_log_ts" btree (log_ts)
子表: logs_2025

在子表 logs_2025 上定义一个名为 chk_y2025 的约束, 使其只能录入 2025 年的数据.

ALTER TABLE logs_2025 ADD CONSTRAINT chk_y2025
  CHECK (log_ts>='2025-1-1'::timestamptz AND log_ts<'2026-1-1'::timestamptz);

postgres@localhost:5436 tutorial# ALTER TABLE logs_2025 ADD CONSTRAINT chk_y2025
tutorial-#   CHECK (log_ts>='2025-1-1'::timestamptz AND log_ts<'2026-1-1'::timestamptz);
ALTER TABLE
时间：45.543 ms

postgres@localhost:5436 tutorial# \d logs_2025
                                      数据表 "public.logs_2025"
    栏位     |           类型           | 校对规则 |  可空的  |                 预设
-------------+--------------------------+----------+----------+--------------------------------------
 log_id      | integer                  |          | not null | nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |          |          |
 description | text                     |          |          |
 log_ts      | timestamp with time zone |          | not null | now()
索引：
    "logs_2025_pkey" PRIMARY KEY, btree (log_id)
检查约束限制
    "chk_y2025" CHECK (log_ts >= '2025-01-01 00:00:00+08'::timestamp with time zone AND log_ts < '2026-01-01 00:00:00+08'::timestamp with time zone)
继承: logs

该 check 约束告诉查询规划器在查询父表时跳过条件不满足的子表.

无日志表

无日志表

对于发生磁盘故障或者系统崩溃后可以被重建的临时数据来说, 其操作速度比可靠性更重要.

PostgreSQL 从 9.1 版开始支持 UNLOGGED 修饰符, 使用该修饰符可以创建无日志的表.

所谓的无日志表是指系统不会为这种表记录任何事务日志(一般也称为 WAL (write-ahead log)).

创建无日志表

CREATE UNLOGGED TABLE web_sessions(
session_id text PRIMARY KEY,
add_ts timestamptz,
upd_ts timestamptz,
session_state xml
);

postgres@localhost:5436 tutorial# CREATE UNLOGGED TABLE web_sessions(
tutorial(# session_id text PRIMARY KEY,
tutorial(# add_ts timestamptz,
tutorial(# upd_ts timestamptz,
tutorial(# session_state xml
tutorial(# );
CREATE TABLE
时间：85.789 ms

postgres@localhost:5436 tutorial# \d web_sessions
                 不记录日志的表 "public.web_sessions"
     栏位      |           类型           | 校对规则 |  可空的  | 预设
---------------+--------------------------+----------+----------+------
 session_id    | text                     |          | not null |
 add_ts        | timestamp with time zone |          |          |
 upd_ts        | timestamp with time zone |          |          |
 session_state | xml                      |          |          |
索引：
    "web_sessions_pkey" PRIMARY KEY, btree (session_id)

无日志表的优点

无日志表的一大优势就是对其写入数据要远远快于往普通表中写数据. 一般要快大约15倍.

无日志表的缺点

• 如果数据库服务器崩溃, PostgreSQL 将截断所有无日志表（截断的意思是擦除所有行）.
• 无日志表不支持 GiST 索引, 因此它就不适用于依赖 GiST 索引的数据类型.

其他

• pg_dump 命令中有一个选项可以设置在备份时跳过无日志表. 这个选项是 --no-unlogged-table-data, 意思是不转储没有日志的表数据.
• 无日志表上可以建常用的 B-树索引和 GIN 索引.

TYPE OF

TYPE OF

前面说过, PostgreSQL 在创建一张表时, 会自动在后台创建一个结构完全相同的复合数据类型.

在 9.0 版, 你可以使用一个复合数据类型来作为建表的模板.

示例

首先创建一个复合数据类型.

CREATE TYPE basic_user AS (user_name varchar(50), pwd varchar(10));

postgres@localhost:5436 tutorial# CREATE TYPE basic_user AS (user_name varchar(50), pwd varchar(10));
CREATE TYPE
时间：40.950 ms
postgres@localhost:5436 tutorial# \dT
           数据类型列表
 架构模式 |      名称      | 描述
----------+----------------+------
 public   | basic_user     | NULL
 public   | complex_number | NULL
(2 行记录)

然后我们可以使用 OF 语法来创建一张表.

CREATE TABLE super_user OF basic_user (
  CONSTRAINT pk_su PRIMARY KEY (user_name)
);

postgres@localhost:5436 tutorial# CREATE TABLE super_user OF basic_user (
tutorial(#   CONSTRAINT pk_su PRIMARY KEY (user_name)
tutorial(# );
CREATE TABLE
时间：56.231 ms
postgres@localhost:5436 tutorial# \d super_user
                   数据表 "public.super_user"
   栏位    |         类型          | 校对规则 |  可空的  | 预设
-----------+-----------------------+----------+----------+------
 user_name | character varying(50) |          | not null |
 pwd       | character varying(10) |          |          |
索引：
    "pk_su" PRIMARY KEY, btree (user_name)
类型的已确定类型表(typed table):basic_user

注意, 当基于数据类型来创建表时, 你不能指定表字段的定义, 一切以数据类型本身的定义为准.

为复合数据类型新增或者移除字段时, PostgreSQL 会自动修改相应的表结构. 这种机制的优点是, 如果你的系统中有很多结构相同的表, 而你可能会需要同时对所有表结构进行相同的修改, 那么此时只需要修改此基础数据类型即可.

下面为将 super_users 表增加一个电话号码字段, 修改表所使用的数据类型 basic_user.

ALTER TYPE basic_user ADD ATTRIBUTE phone varchar(10) CASCADE;

通常, 如果表依赖于某个类型, 那么你就不能更改该类型的定义. CASCADE 修饰符凌驾于此限制之上, 对所有相关表应用相同的更改.

postgres@localhost:5436 tutorial# ALTER TYPE basic_user ADD ATTRIBUTE phone varchar(10);
ERROR:  cannot alter type "basic_user" because it is the type of a typed table
提示:  Use ALTER ... CASCADE to alter the typed tables too.
时间：27.780 ms

postgres@localhost:5436 tutorial# ALTER TYPE basic_user ADD ATTRIBUTE phone varchar(10) CASCADE;
ALTER TYPE
时间：21.936 ms

postgres@localhost:5436 tutorial# \d super_user
         资料表 "public.super_user"
   栏位    |         型别          | 修饰词
-----------+-----------------------+--------
 user_name | character varying(50) | 非空
 pwd       | character varying(10) |
 phone     | character varying(10) |
索引：
    "pk_su" PRIMARY KEY, btree (user_name)
类型的已确定类型表(typed table):basic_user

postgres@localhost:5436 tutorial# \dST basic_user
        合成型别 "public.basic_user"
   栏位    |         型别          | 修饰词
-----------+-----------------------+--------
 user_name | character varying(50) |
 pwd       | character varying(10) |
 phone     | character varying(10) |

约束机制

约束机制

用户可以在创建约束时定制其各方面的属性, 包括:

• 约束的名称
• 如何处理现有数据
• 级联生效条件选项
• 如何执行匹配算法
• 使用哪些索引以及在何种情况下约束可以不生效

外键约束

与大多数支持引用完整性的数据库一样, PostgreSQL 遵循与其相同的约定. 你可以指定级联更新和删除规则以避免出现孤立记录.

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)
ON UPDATE CASCADE ON DELETE RESTRICT;
CREATE INDEX fki_facts_1 ON facts (fact_type_id);

set search_path=census, public; 是设置搜索路径, 这样就可以直接写表的名称, PostgreSQL 会先从 census 架构中查找这个表. 为做实验, 先连接到 postgresql_book 数据库.

postgres@localhost:5436 tutorial# \c postgresql_book
您现在已经连线到数据库 "postgresql_book",用户 "postgres".
postgres@localhost:5436 postgresql_book# \d
                  关联列表
 架构模式 |    名称     |  型别  |  拥有者
----------+-------------+--------+----------
 public   | test_scores | 资料表 | postgres
(1 行记录)

postgres@localhost:5436 postgresql_book# \dn
    架构模式列表
  名称   |  拥有者
---------+----------
 census  | postgres
 public  | postgres
 staging | postgres
(3 行记录)

postgres@localhost:5436 postgresql_book# \dt census.*
                   关联列表
 架构模式 |     名称      |  型别  |  拥有者
----------+---------------+--------+----------
 census   | facts         | 资料表 | postgres
 census   | hisp_pop      | 资料表 | postgres
 census   | lu_fact_types | 资料表 | postgres
 census   | lu_tracts     | 资料表 | postgres
(4 行记录)

ALTER TABLE facts ADD CONSTRAINT fk_facts_1 FOREIGN KEY (fact_type_id)
REFERENCES lu_fact_types (fact_type_id)
ON UPDATE CASCADE ON DELETE RESTRICT;

该语句建立了从 facts 表（称为从表）到 lu_fact_types 表（称为主表）的一个外键引用, 起名为 fk_facts_1 (fk 是 foreign key 的缩写). 令 facts 表的 fact_type_id 字段指向 lu_fact_types 表的 fact_type_id. 并且, 更新时为级联更新, 删除时限制.

• 级联(CASCADE)的意思是, 若主表 lu_fact_types 的 fact_type_id 字段值发生了变化, 则从表 facts 中相应记录的 fact_type_id 字段值会自动进行相应修改, 以维持外键引用关系不变.
• 如果从表 facts 中还存在某 fact_type_id 字段值的记录, 那么主表 lu_fact_types 中相同 fact_type_id 字段值的记录就不允许被删除. ON DELETE RESTRICT 是默认行为模式, 即可以不写.

唯一性约束

主键字段的值是唯一的, 但每张表只能定义一个主键, 因此如果你需要保证别的字段值唯一, 那么必须在该字段上建立唯一性约束或者说唯一性索引.

例. 对数据库 tutorial 中的表 logs_2025, 在 user_name 和 log_ts 字段上添加唯一性约束.

ALTER TABLE logs_2025 ADD CONSTRAINT uq UNIQUE (user_name, log_ts);

postgres@localhost:5436 tutorial# \d logs_2025
                                资料表 "public.logs_2025"
    栏位     |           型别           |                     修饰词
-------------+--------------------------+------------------------------------------------
 log_id      | integer                  | 非空 缺省 nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |
 description | text                     |
 log_ts      | timestamp with time zone | 非空 缺省 now()
索引：
    "logs_2025_pkey" PRIMARY KEY, btree (log_id)
检查约束限制
    "chk_y2025" CHECK (log_ts >= '2025-01-01 00:00:00+08'::timestamp with time zone AND log_ts < '2026-01-01 00:00:00+08'::timestamp with time zone)
继承: logs

postgres@localhost:5436 tutorial# ALTER TABLE logs_2025 ADD CONSTRAINT uq UNIQUE (user_name, log_ts);
ALTER TABLE
时间：45.859 ms
postgres@localhost:5436 tutorial# \d logs_2025
                                资料表 "public.logs_2025"
    栏位     |           型别           |                     修饰词
-------------+--------------------------+------------------------------------------------
 log_id      | integer                  | 非空 缺省 nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |
 description | text                     |
 log_ts      | timestamp with time zone | 非空 缺省 now()
索引：
    "logs_2025_pkey" PRIMARY KEY, btree (log_id)
    "uq" UNIQUE CONSTRAINT, btree (user_name, log_ts)
检查约束限制
    "chk_y2025" CHECK (log_ts >= '2025-01-01 00:00:00+08'::timestamp with time zone AND log_ts < '2026-01-01 00:00:00+08'::timestamp with time zone)
继承: logs

注意

建立唯一性约束时, PostgreSQL 会自动在后台创建一个相应的唯一索引. 使用 \di 命令(display indexes)查看索引列表. (并尝试 \diS, \diS+)

postgres@localhost:5436 tutorial# \di
                                关联列表
 架构模式 |            名称            | 型别 |  拥有者  |    资料表
----------+----------------------------+------+----------+--------------
 public   | circuits_pkey              | 索引 | postgres | circuits
 public   | employment_pkey            | 索引 | postgres | employment
 public   | families_b_pkey            | 索引 | postgres | families_b
 public   | families_j_pkey            | 索引 | postgres | families_j
 public   | families_pkey              | 索引 | postgres | families
 public   | idx_employment_period      | 索引 | postgres | employment
 public   | idx_familes_jb_profile_gin | 索引 | postgres | families_b
 public   | idx_logs_2025_log_ts       | 索引 | postgres | logs
 public   | idx_logs_log_ts            | 索引 | postgres | logs
 public   | logs_2025_pkey             | 索引 | postgres | logs_2025
 public   | logs_pkey                  | 索引 | postgres | logs
 public   | pk_su                      | 索引 | postgres | super_user
 public   | uq                         | 索引 | postgres | logs_2025
 public   | web_sessions_pkey          | 索引 | postgres | web_sessions
(14 行记录)

• 建立唯一性约束时, PostgreSQL 会自动在后台创建一个相应的唯一索引.
• 与主键字段类似, 建立了唯一性约束的字段不允许为空, 并且可以作为外键(被引用)字段被别的表引用.
• 建立了唯一索引但却没有唯一性约束的字段是可以输入空值的.

check 约束

check 约束能够对表的一个或者多个字段加上一个条件, 表中每一行记录必须满足此条件.

查询规划器也会利用 check 约束来优化执行速度, 比如有些查询附带的条件与待查询表的check约束无交集, 那么规划器会立即认定该查询未命中目标并返回.

例. 以下 check 约束可以限制 logs 表中所有用户名必须都小写:

ALTER TABLE logs ADD CONSTRAINT chk CHECK(user_name=lower(user_name));

check 约束支持基于函数和布尔表达式的条件.

postgres@localhost:5436 tutorial# \d logs
                                  资料表 "public.logs"
    栏位     |           型别           |                     修饰词
-------------+--------------------------+------------------------------------------------
 log_id      | integer                  | 非空 缺省 nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |
 description | text                     |
 log_ts      | timestamp with time zone | 非空 缺省 now()
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_2025_log_ts" btree (log_ts)
    "idx_logs_log_ts" btree (log_ts)
子表的数量：1(可以使用 \d+ 来列出它们)

postgres@localhost:5436 tutorial# ALTER TABLE logs ADD CONSTRAINT chk CHECK(user_name=lower(user_name));
ALTER TABLE
时间：33.332 ms

postgres@localhost:5436 tutorial# \d+ logs
                                                 资料表 "public.logs"
    栏位     |           型别           |                     修饰词                     |   存储   | 统计目标 | 描述
-------------+--------------------------+------------------------------------------------+----------+----------+------
 log_id      | integer                  | 非空 缺省 nextval('logs_log_id_seq'::regclass) | plain    |          |
 user_name   | character varying(50)    |                                                | extended |          |
 description | text                     |                                                | extended |          |
 log_ts      | timestamp with time zone | 非空 缺省 now()                                | plain    |          |
索引：
    "logs_pkey" PRIMARY KEY, btree (log_id)
    "idx_logs_2025_log_ts" btree (log_ts)
    "idx_logs_log_ts" btree (log_ts)
检查约束限制
    "chk" CHECK (user_name::text = lower(user_name::text))
子表: logs_2025

子表 logs_2025 也继承了父表 logs 的约束.

postgres@localhost:5436 tutorial# \d logs_2025
                                资料表 "public.logs_2025"
    栏位     |           型别           |                     修饰词
-------------+--------------------------+------------------------------------------------
 log_id      | integer                  | 非空 缺省 nextval('logs_log_id_seq'::regclass)
 user_name   | character varying(50)    |
 description | text                     |
 log_ts      | timestamp with time zone | 非空 缺省 now()
索引：
    "logs_2025_pkey" PRIMARY KEY, btree (log_id)
    "uq" UNIQUE CONSTRAINT, btree (user_name, log_ts)
检查约束限制
    "chk" CHECK (user_name::text = lower(user_name::text))
    "chk_y2025" CHECK (log_ts >= '2025-01-01 00:00:00+08'::timestamp with time zone AND log_ts < '2026-01-01 00:00:00+08'::timestamp with time zone)
继承: logs

从上面的例子可以看到, 当表间存在继承关系时, 子表会继承父表的 check 约束, 但主键、外键、唯一性这三种约束却不会继承.

排他性约束

传统的唯一性约束在比较算法中仅使用了“等于”运算符, 即保证了指定字段的值在本表的任意两行记录中都不相等. 而 9.0 版中引入的排他性约束机制拓展了唯一性比较算法机制, 可以使用更多的运算符来进行比较, 该类约束特别适用于解决有关时间安排的问题.

PostgreSQL 9.2 版中引入了区间数据类型, 该类型特别适合使用排他性约束.

排他性约束使用关键字 EXCLUDE, 一般是基于 GiST(Generalized Search Tree)类型的索引来实现, 使用基于 B-树算法的 GiST 多列复合索引也是可以的. 不过需要先安装 btree_gist 扩展包才能建立这种索引.

多列排他性约束的一个经典应用场景就是用于安排资源.

例

下面是一个使用排他约束的例子. 假设你的办公场所有固定数量的会议室, 各项目组在使用会议室前必须预定.

CREATE TABLE schedules(
  id serial PRIMARY KEY,
  room smallint,
  time_slot tstzrange
);

ALTER TABLE schedules ADD CONSTRAINT ex_schedules
EXCLUDE USING gist (room WITH =, time_slot WITH &&);

上面使用了 && 运算符来判定时间区间是否重叠, 还使用了 = 运算符来判定会议室房间号是否重复.

同唯一性约束一样, PostgreSQL 会自动为排他性约束中涉及的字段建立索引.

postgres@localhost:5436 tutorial# CREATE TABLE schedules(
tutorial(#   id serial PRIMARY KEY,
tutorial(#   room smallint,
tutorial(#   time_slot tstzrange
tutorial(# );
CREATE TABLE
时间：43.026 ms

postgres@localhost:5436 tutorial# ALTER TABLE schedules ADD CONSTRAINT ex_schedules
tutorial-# EXCLUDE USING gist (room WITH =, time_slot WITH &&);
ERROR:  data type smallint has no default operator class for access method "gist"
提示:  You must specify an operator class for the index or define a default operator class for the data type.
时间：7.125 ms

出现这个错误的原因是未安装 btree_gist 扩展包. 解决办法是输入下面的语句创建扩展包.

CREATE EXTENSION btree_gist;

postgres@localhost:5436 tutorial# CREATE EXTENSION btree_gist;
CREATE EXTENSION
时间：165.659 ms

使用 \dx 命令查看安装的扩展.

postgres@localhost:5436 tutorial# \dx
                                 已安装扩展列表
    名称    | 版本 |  架构模式  |                     描述
------------+------+------------+-----------------------------------------------
 btree_gist | 1.0  | public     | support for indexing common datatypes in GiST
 plpgsql    | 1.0  | pg_catalog | PL/pgSQL procedural language
(2 行记录)

然后, 重新添加排他性约束.

postgres@localhost:5436 tutorial# ALTER TABLE schedules ADD CONSTRAINT ex_schedules
tutorial-# EXCLUDE USING gist (room WITH =, time_slot WITH &&);
ALTER TABLE
时间：54.125 ms

索引

索引

PostgreSQL 的索引机制功能强大、特性丰富. PostgreSQL 至少支持四种类型的索引. 还允许用户为这几种索引类型自定义新的索引运算符和修饰符以作为其功能补充. PostgreSQL 还允许用户创建自己的索引类型.

PostgreSQL 支持在同一张表中混合搭配不同的索引类型, 且预计规划器将综合考虑所有的索引.

PostgreSQL 原生支持的索引类型

PostgreSQL 支持 B-树索引、GiST索引、GIN索引、SP-GiST索引、哈希索引、基于B-树算法的GiST和GIN索引.

B-树索引

B-树是关系型数据库中常见的一种通用索引类型.

在创建主键或唯一性约束时, PostgreSQL 会自动创建索引, 且索引类型是 B-树索引.

如果你自己创建索引时未指定索引类型, 则默认也会创建 B-树类型的索引.

GiST 索引

GiST 的全称是 Generalized Search Tree, 意即通用查找树或通用搜索树.

GiST 索引主要的适用场景包括全文搜索以及空间数据、科学数据、非结构化数据和层次化数据的搜索.

该类索引不能用于保障字段的唯一性, 也就是说建立了该类型索引的字段上可插入重复值, 但如果把该类型索引用于排他性约束就可以实现唯一性保障.

GiST 是一种有损索引, 也就是说它不存储被索引字段的值, 而仅仅存储字段值的一个取样, 这种取样是失真的. 这意味着需要一个额外的查找步骤以获得真正记录的值.

GIN 索引

GIN 的全称是 Generalized Inverted Index, 即通用逆序索引.

它主要适用于 PostgreSQL 内置的全文搜索引擎以及 jsonb 数据类型. 其他有一些扩展包比如 hstore 和 pg_trgm 也会适用这种索引.

GIN 其实是从 GiST 派生出来的一种索引类型, 但它是无损的, 也就是说索引中会包含有被索引字段的值.

如果你需要查询的字段都以被索引, 那么只读取索引即可获取查询结果, 这种情况下 GIN 的查询速度是快于 GiST 的. 然而, 由于 GIN 比 GiST 在更新操作时要多出一个字段值复制动作, 因此此时是 GiST 索引更快一些.

另外, GIN 的索引树内部每一个索引行的长度是有限制的, 所以它不能用于对 hstore 文档或者 text 等大对象类型进行索引. (hstore 类型是用于存储键值对的数据类型, 其中键和值都是字符串.)

SP-GiST 索引

SP-GiST 是指基于空间分区树 (Space-Partitioning Trees)算法的 GiST 索引. 该类型的索引从 9.2 版开始引入, 与 GiST 索引适用领域相同, 但对于某些特定领域的数据算法, 其效率会更高一些.

PostgreSQL 的 point 和 box 等原生几何类型以及 text 类型是最先支持该类索引的数据类型. 从 9.3 版开始, 区间类型也开始支持此类型的索引.

哈希索引

哈希索引在 GiST 和 GIN 索引出现前就已经得到了广泛使用. 业界普遍认为 GiST 和 GIN 索引在性能和事务安全性方面要胜过哈希索引.

PostgreSQL 已将哈希索引列为不推荐使用状态. 在 PostgreSQL 中最好避免使用.

基于 B-树算法的 GiST 和 GIN 索引

基于 B-树算法的 GiST 和 GIN 索引都以扩展包形式存在. 这两类混合算法索引的优势在于, 它们一方面能够支持 GiST 和 GIN 索引特有的运算符, 同时又具有 B-树索引对于“等于”运算符的良好支持.

有时我们会需要建立这样的多列复合索引: 索引字段中既有像 character varying 或 number 这样的数据类型, 又有层次化的 ltree 类型或者用于全文搜索的 vector 类型. 前两种数据类型一般会使用“等于”运算符来进行操作, 后两种一般使用 GIN/GiST 索引提供的运算符进行操作. 此时你会发现要建立这种索引必须使用基于 B-树算法的 GiST 索引或者基于 B-树算法 GIN 索引.

运算符类

各种数据类型均有其自身特点, 因此适用的索引类型不同, 会用到的比较运算符也不同.

例如, 对于基于区间类型(range)的索引来说, 最常用的运算符是重叠运算符(&&). 然而该运算符对于快速文本搜索领域来说却毫无意义.

对于中文这类表意文字来说, 建立的索引基本上不会用到“不等于”运算符; 而对英文这类表音文字建立索引时, 字母 A 到 Z 的排序操作是不可或缺的.

基于以上特点, PostgreSQL 把一类应用领域相近的运算符以及这些运算符适用的数据类型组合在一起成为一个运算符类（简称 opclass）.

例如, int4_ops 运算符类包含适用于 int4 类型的 = <> > < 运算符.

PostgreSQL 提供了一张叫作 pg_class 的系统表, 从中可以查到完整的运算符类列表, 其中既包含了系统原生支持的类, 也包含了通过扩展包机制添加的类.

一种类型的索引会使用特定的若干种运算符类.

完整的运算符列表可以从 pgAdmin 界面上的运算符类目下看到, 也可以根据 system catalog 在下面的示例中执行查询得到.

SELECT am.amname AS index_method, opc.opcname AS opclass_name,
       opc.opcintype::regtype AS indexed_type, opc.opcdefault AS is_default
FROM pg_am am INNER JOIN pg_opclass opc ON opc.opcmethod=am.oid
WHERE am.amname='btree'
ORDER BY index_method, indexed_type, opclass_name;

postgres@localhost:5436 tutorial# SELECT am.amname AS index_method, opc.opcname AS opclass_name,
tutorial-#        opc.opcintype::regtype AS indexed_type, opc.opcdefault AS is_default
tutorial-# FROM pg_am am INNER JOIN pg_opclass opc ON opc.opcmethod=am.oid
tutorial-# WHERE am.amname='btree'
tutorial-# ORDER BY index_method, indexed_type, opclass_name;
 index_method |    opclass_name     |        indexed_type         | is_default
--------------+---------------------+-----------------------------+------------
 btree        | bool_ops            | boolean                     | t
 btree        | bytea_ops           | bytea                       | t
 btree        | char_ops            | "char"                      | t
 btree        | name_ops            | name                        | t
 btree        | int8_ops            | bigint                      | t
 btree        | int2_ops            | smallint                    | t
 btree        | int4_ops            | integer                     | t
 btree        | text_ops            | text                        | t
 btree        | text_pattern_ops    | text                        | f
 btree        | varchar_ops         | text                        | f
 btree        | varchar_pattern_ops | text                        | f
 btree        | oid_ops             | oid                         | t
 btree        | tid_ops             | tid                         | t
 btree        | oidvector_ops       | oidvector                   | t
 btree        | float4_ops          | real                        | t
 btree        | float8_ops          | double precision            | t
 btree        | abstime_ops         | abstime                     | t
 btree        | reltime_ops         | reltime                     | t
 btree        | tinterval_ops       | tinterval                   | t
 btree        | money_ops           | money                       | t
 btree        | macaddr_ops         | macaddr                     | t
 btree        | cidr_ops            | inet                        | f
 btree        | inet_ops            | inet                        | t
 btree        | bpchar_ops          | character                   | t
 btree        | bpchar_pattern_ops  | character                   | f
 btree        | date_ops            | date                        | t
 btree        | time_ops            | time without time zone      | t
 btree        | timestamp_ops       | timestamp without time zone | t
 btree        | timestamptz_ops     | timestamp with time zone    | t
 btree        | interval_ops        | interval                    | t
 btree        | timetz_ops          | time with time zone         | t
 btree        | bit_ops             | bit                         | t
 btree        | varbit_ops          | bit varying                 | t
 btree        | numeric_ops         | numeric                     | t
 btree        | record_image_ops    | record                      | f
 btree        | record_ops          | record                      | t
 btree        | array_ops           | anyarray                    | t
 btree        | uuid_ops            | uuid                        | t
 btree        | pg_lsn_ops          | pg_lsn                      | t
 btree        | enum_ops            | anyenum                     | t
 btree        | tsvector_ops        | tsvector                    | t
 btree        | tsquery_ops         | tsquery                     | t
 btree        | jsonb_ops           | jsonb                       | t
 btree        | range_ops           | anyrange                    | t
(44 行记录)

上面的例子仅查询了 B-树的相关数据.

请注意, 每类索引都会有多个运算符类, 而其中仅有一个会被标记为默认运算符类.

如果建立索引时未指定使用哪个运算符类, 那么 PostgreSQL 默认会使用默认运算符类. 绝大多数情况下这么做是没什么问题的, 但并非绝对如此.

例如, B-树索引默认的 text_ops 运算符类(又名 varchar_ops)中并不支持 ~~/j<> 运算符(即 LIKE 运算符). 所以, 如果建 B-树索引时选择了该运算符类, 那么所有使用 LIKE 的查询都无法在 text_ops 运算符类中使用索引. 因此, 如果你的业务使用场景需要对 varchar 或者 text 类型进行大量 LIKE 模糊查询, 那么建立索引时最好是显示指定使用 text_pattern_ops 或者 varchar_pattern_ops 这两个运算符类.

指定运算符类的语法很简单, 只需要在建索引时加在被索引字段名的后面即可. 例如:

CREATE INDEX idx1 ON census.lu_tracts USING btree (tract_name text_pattern_ops);

注意 text_pattern_ops 直接跟在被索引字段 tract_name 的后面, 中间没有逗号, 否则会将 text_pattern_ops 误认为是表中的字段.

postgres@localhost:5436 postgresql_book# \d census.lu_tracts;
            资料表 "census.lu_tracts"
     栏位      |          型别          | 修饰词
---------------+------------------------+--------
 tract_id      | character varying(11)  | 非空
 tract_long_id | character varying(25)  |
 tract_name    | character varying(150) |
索引：
    "pk_lu_tracts" PRIMARY KEY, btree (tract_id)

postgres@localhost:5436 postgresql_book# CREATE INDEX idx1 ON census.lu_tracts USING btree (tract_name, text_pattern_ops);
ERROR:  column "text_pattern_ops" does not exist
时间：20.415 ms

将逗号去掉.

postgres@localhost:5436 postgresql_book# CREATE INDEX idx1 ON census.lu_tracts USING btree (tract_name text_pattern_ops);
CREATE INDEX
时间：72.683 ms

postgres@localhost:5436 postgresql_book# \d census.lu_tracts
            资料表 "census.lu_tracts"
     栏位      |          型别          | 修饰词
---------------+------------------------+--------
 tract_id      | character varying(11)  | 非空
 tract_long_id | character varying(25)  |
 tract_name    | character varying(150) |
索引：
    "pk_lu_tracts" PRIMARY KEY, btree (tract_id)
    "idx1" btree (tract_name text_pattern_ops)

注意

varchar_ops 和 varchar_pattern_ops 实质上就是 text_ops 和 text_pattern_ops 的别名.

你创建的每一个索引都只会使用一个运算符类. 如果希望一个字段上的索引使用多个运算符类, 那么请创建多个索引.

要将默认索引 text_ops 添加到表中, 请运行下面的代码:

CREATE INDEX idx2 ON census.lu_tracts USING btree (tract_name);

postgres@localhost:5436 postgresql_book# CREATE INDEX idx2 ON census.lu_tracts USING btree (tract_name);
CREATE INDEX
时间：55.236 ms
postgres@localhost:5436 postgresql_book# \d census.lu_tracts
            资料表 "census.lu_tracts"
     栏位      |          型别          | 修饰词
---------------+------------------------+--------
 tract_id      | character varying(11)  | 非空
 tract_long_id | character varying(25)  |
 tract_name    | character varying(150) |
索引：
    "pk_lu_tracts" PRIMARY KEY, btree (tract_id)
    "idx1" btree (tract_name text_pattern_ops)
    "idx2" btree (tract_name)

单个字段上可建立索引的个数是没有限制的.

规划器处理等值查询时会使用 idx2, 处理 like 模糊查询时会使用 idx1

函数索引

PostgreSQL 的函数索引功能可以基于字段值的函数运算结果建立索引.

函数索引的用途也是很广泛的, 例如可用于对大小写混杂的文本数据建立索引. PostgreSQL 是一个区分大小写的数据库, 如果要实现不区分大小写的查询, 那么可以借助如下的函数索引:

CREATE INDEX fidx ON featnames_short
USING btree (upper(fullname) varchar_pattern_ops);

建立了该索引之后, 类似

SELECT fullname FROM featnames_short WHERE upper(fullname) LIKE 'S%';

这种查询就可以用上索引了. 不过要注意, 查询语句中使用的函数要与建函数索引时使用的函数完全一致, 这样才能保证用上索引.

PostgreSQL 和 Oracle 都支持函数索引. MySQL 和 SQL Server 不直接支持函数索引, 但提供了自动计算字段并且可以对该类字段建立索引, 总的来说其效果和函数索引是类似的.

从 9.3 版开始, PostgreSQL 开始支持对物化视图建立索引.

基于部分记录的索引

基于部分记录的索引（有时也称为已筛选索引）是一种仅针对表中部分记录的索引, 而且这部分记录需要满足 WHERE 语句设置的筛选条件.

例如, 假设某表中有 100,0000 条记录, 但你只会查询其中的一个记录数为 1,0000的子集, 那么该场景就非常适合使用基于部分记录的索引.

这种索引比全量索引要快, 因为其体积小, 所以可以把更多索引数据缓存到内存中, 另外该类索引占用的磁盘空间也会更小.

基于部分记录的索引能够实现仅针对部分记录的唯一性约束.

举个例子, 假设你手上有一家报纸在过去十年间的订阅用户数据, 现在需要确保还在订阅的用户们不会每天多拿一份报纸（？）由于人们对纸质媒体的兴趣下降, 因此这十年间的全量订阅用户中仅有 5% 的人还在坚持订阅. 所以, 很显然你不需要关注那些已经退订的用户, 因为他们的姓名早已从报纸递送员手上的递送名单中剔除. 表的结构如下:

CREATE TABLE subscribers(
  id serial PRIMARY KEY,
  name varchar(50) NOT NULL,
  type varchar(50),
  is_active boolean
);

postgres@localhost:5436 tutorial# CREATE TABLE subscribers(
tutorial(#   id serial PRIMARY KEY,
tutorial(#   name varchar(50) NOT NULL,
tutorial(#   type varchar(50),
tutorial(#   is_active boolean
tutorial(# );
CREATE TABLE
时间：145.903 ms

postgres@localhost:5436 tutorial# \d subscribers
                              资料表 "public.subscribers"
   栏位    |         型别          |                      修饰词
-----------+-----------------------+---------------------------------------------------
 id        | integer               | 非空 缺省 nextval('subscribers_id_seq'::regclass)
 name      | character varying(50) | 非空
 type      | character varying(50) |
 is_active | boolean               |
索引：
    "subscribers_pkey" PRIMARY KEY, btree (id)

我们建立一个基于当前活跃用户的部分记录索引即可.

CREATE UNIQUE INDEX uq ON subscribers USING btree (lower(name)) WHERE is_active;

postgres@localhost:5436 tutorial# CREATE UNIQUE INDEX uq ON subscribers USING btree (lower(name)) WHERE is_active;
ERROR:  relation "uq" already exists
时间：55.543 ms

前面已经创建了一个索引, 因此需要重新给个名称. 建议对约束、索引等命名时遵循一定的规范. 比如外键命名使用 fk_tblname_col_1, 索引命名使用 idx_tblname_col_1 等. 由于索引有多种, 在 idx 后跟一些字符表明是哪一种索引. 例如 idx_uq_tblname_col_1.

CREATE UNIQUE INDEX idx_uq_subscribers_name ON subscribers USING btree (lower(name)) WHERE is_active;

postgres@localhost:5436 tutorial# CREATE UNIQUE INDEX idx_uq_subscribers_name ON subscribers USING btree (lower(name)) WHERE is_active;
CREATE INDEX
时间：51.337 ms

postgres@localhost:5436 tutorial# \di
                                关联列表
 架构模式 |            名称            | 型别 |  拥有者  |    资料表
----------+----------------------------+------+----------+--------------
 public   | circuits_pkey              | 索引 | postgres | circuits
 public   | employment_pkey            | 索引 | postgres | employment
 public   | ex_schedules               | 索引 | postgres | schedules
 public   | families_b_pkey            | 索引 | postgres | families_b
 public   | families_j_pkey            | 索引 | postgres | families_j
 public   | families_pkey              | 索引 | postgres | families
 public   | idx_employment_period      | 索引 | postgres | employment
 public   | idx_familes_jb_profile_gin | 索引 | postgres | families_b
 public   | idx_logs_2025_log_ts       | 索引 | postgres | logs
 public   | idx_logs_log_ts            | 索引 | postgres | logs
 public   | idx_uq_subscribers_name    | 索引 | postgres | subscribers
 public   | logs_2025_pkey             | 索引 | postgres | logs_2025
 public   | logs_pkey                  | 索引 | postgres | logs
 public   | pk_su                      | 索引 | postgres | super_user
 public   | schedules_pkey             | 索引 | postgres | schedules
 public   | subscribers_pkey           | 索引 | postgres | subscribers
 public   | uq                         | 索引 | postgres | logs_2025
 public   | web_sessions_pkey          | 索引 | postgres | web_sessions
(18 行记录)

注意

索引的 WHERE 条件中使用的函数必须是确定性函数, 即固定的输入一定能够得到固定输出的函数.

这意味着有几类函数是不能用作筛选条件的:

• CURRENT_DATE 这种输出结果不停在变的函数;
• 依赖于其他表数据进行运算的函数, 其输出结果受其他表数据的影响, 因此输出也是不固定的;
• 依赖当前表中的其他记录行进行运算的函数, 其输出也不是受控的.

我们需要特别强调的一点是, 当使用 SELECT 语句查询数据时, 创建索引时所使用的条件必须是该 SELECT 语句中 WHERE 条件的子集. 这看起来比较麻烦也容易出错, 那么有一个办法可以让事情变得简单一些, 那就是建一个视图, 视图条件就是建索引的条件, 那么针对此视图进行查询就永远不会漏掉条件了.

还是以前述报纸订阅用户数据为例, 建立如下视图:

CREATE OR REPLACE VIEW vw_subscribers_current AS
SELECT id, lower(name) As name FROM subscribers WHERE is_active=true;

postgres@localhost:5436 tutorial# CREATE OR REPLACE VIEW vw_subscribers_current AS
tutorial-# SELECT id, lower(name) As name FROM subscribers WHERE is_active=true;
CREATE VIEW
时间：65.972 ms

使用 \d 查看, vw_subscribers_current 的型别是“视观表”.

postgres@localhost:5436 tutorial# \d
                        关联列表
 架构模式 |          名称           |  型别  |  拥有者
----------+-------------------------+--------+----------
 public   | circuits                | 资料表 | postgres
 public   | circuits_circuit_id_seq | 序列数 | postgres
 public   | employment              | 资料表 | postgres
 public   | employment_id_seq       | 序列数 | postgres
 public   | families                | 资料表 | postgres
 public   | families_b              | 资料表 | postgres
 public   | families_b_id_seq       | 序列数 | postgres
 public   | families_id_seq         | 序列数 | postgres
 public   | families_j              | 资料表 | postgres
 public   | families_j_id_seq       | 序列数 | postgres
 public   | logs                    | 资料表 | postgres
 public   | logs_2025               | 资料表 | postgres
 public   | logs_log_id_seq         | 序列数 | postgres
 public   | operators               | 资料表 | postgres
 public   | schedules               | 资料表 | postgres
 public   | schedules_id_seq        | 序列数 | postgres
 public   | subscribers             | 资料表 | postgres
 public   | subscribers_id_seq      | 序列数 | postgres
 public   | super_user              | 资料表 | postgres
 public   | t1                      | 资料表 | postgres
 public   | tmp12                   | 资料表 | postgres
 public   | vw_subscribers_current  | 视观表 | postgres
 public   | web_sessions            | 资料表 | postgres
(23 行记录)

或者使用 \dv 列出当前数据库下所有视图.

postgres@localhost:5436 tutorial# \dv
                       关联列表
 架构模式 |          名称          |  型别  |  拥有者
----------+------------------------+--------+----------
 public   | vw_subscribers_current | 视观表 | postgres
(1 行记录)

然后针对原表的查询都改为针对此视图的查询. (有一种比较激进的观点认为此种情况下永远都不应该直接查询原表.)

SELECT * FROM vw_subscribers_current WHERE user_name='sandy';

这里会返回错误,

ERROR:  column "user_name" does not exist
LINE 1: SELECT * FROM vw_subscribers_current WHERE user_name='sandy'...
                                                   ^
********** 错误 **********

ERROR: column "user_name" does not exist
SQL 状态: 42703
字符:44

因此使用 ALTER VIEW 更改 vw_subscribers_current 的定义.

你可以查看规划器输出的执行计划来检查你的索引是否被用上了.

多列索引

End

Thanks very much!