4.PostgreSQL索引特性-01.docx

兜灬兜

5页

0次

2024-06-01

50墨值下载

唯一索引

1.创建唯一索引

CREATE UNIQUE INDEX ON t1(id);--单列

CREATE UNIQUE INDEX ON t1(id,name);--多列

用于强迫字段数值的唯一性，或者是多个字段组合值的唯一性，只有 Btree 支持唯一索引。

2.通过约束生成

唯一约束通过唯一索引实现。创建主键、唯一键时会隐含的创建唯一索引。

create table t1(id int primary key,name text,age int) ;

create table t1(id int unique,name text,age int) ;

外键约束依赖于被引用字段上的唯一约束（隐含的需要唯一索引）。

create table class(id int,name text);

create table student(id int,name text,age int,class_id reference class(id));--ERROR

create unique index on class(id);

create table student(id int,name text,age int,class_id reference class(id));--success

表达式索引

PostgreSQL 支持表达式索引。

定义索引表达式后，当定义索引的表达式出现在 SQL 语句的 WHERE 子句或 ORDERBY 子

句中时，PostgreSQL 将考虑使用该索引。

注意：维护表达式的索引非常昂贵，因为每次在插入或更新的时候，都会去评估每一行的表达

式并将其用于索引。如果当检索速度比插入和更新速度更重要时，应在表达式上使用索引。

在做大小写无关比较时，常用的方法是使用 lower 函数：

SELECT * FROM t1 WHERE lower(info) = 'hello world';

但因为使用了函数，无法利用到 info 字段上的普通索引，这时就需要建一个函数索引：

CREATE INDEX ON t1 (lower(info));

表达式上的索引并不是在索引查找时进行表达式的计算，而是在插入或更新数据行时进行计算，

因此在插入或更新时，表达式上的索引会慢一些。

如果把表达式上的索引声明为 UNIQUE，那么它会禁止向 info 列中插入只是大小写有区别而

内容完全相同的数据行。因此，表达式上的索引可以实现简单唯一约束无法实现的一些约束。

CREATE UNIQUE INDEX ON mytest (lower(info));

部分索引

部分索引是对表中的部分数据进行索引，是由一个条件表达式把这部分行筛选出来，该条件

表达式被称为部分索引的谓词。部分索引在一些情况下非常有用，有助于加快查询速度，同时

减小索引的大小。

有时需要仅索引数据的一部分。这通常与高度不均匀的分布有关：通过索引搜索不频繁的值是

有意义的，但通过对表的完全扫描更容易找到频繁值。

定义部分索引的语法：

CREATE INDEX index_name ON table_name(column_list) WHERE condition;

WHERE 子句指定应将哪些行添加到索引。

示例一：设置部分索引以排除普通数值

create table users(id int,name text,age int);

建的部分索引：

create index on user(age) where age>=100;

如下 SQL 命令就可以使用到上面创建的部分索引

select * from user where age>=100;

select * from user where age=120;

而如下 SQL 命令就不能走到此部分索引，因为它的数据不在索引中：

select * from user where age=88;

示例二：设置部分索引以排除不感兴趣的数值

假如有一个表，其中包含已付款和未付款的定单，而未付款的定单占总表的一小部分，并且

是经常使用的部分，那么可以只在未付款定单上创建一个索引来改善查询性能。

表定义如下：

CREATE TABLE orders(order_id int,amount decimal(12,2),status boolean);

创建的索引：

CREATE INDEX ON orders (order_id) WHERE status is not true;

插入测试数据：

insert into orders select n, n*1.1, true from generate_series(1,1000000) n;

insert into orders select n, n*1.1, false from generate_series(1000001,1000020) n;

下面这条 SQL 语句就可以用到这个索引：

SELECT * FROM orders WHERE status is not true AND order_id > 5000000;

看一下执行计划，命令如下：

explain SELECT * FROM orders WHERE status is not true AND order_id > 5000000;