UNVDB基础之索引

索引可以让您快速的访问数据表中的信息，尤其在一个大数据表中。本章节介绍了 unvdb 索引，包括：唯一索引，复合索引，创建索引，删除索引等。

索引的使用及解释

索引类型

本文介绍在 unvdb中的索引类型以及如何正确的使用它们。

unvdb 支持几种不同类型的索引：B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN。每种索引类型使用不同的存储结构和算法来应对不同类型的查询。不同的数据可能要采用不同的索引类型。

unvdb 默认使用 B-tree 索引类型，因为它最适合最常见的查询。你在使用 CREATE INDEX 语句创建索引时，如果不知道索引类型，默认采用 B-tree。

B-tree 索引

B-tree 是一种自平衡树，它维护已排序的数据。

每当索引列涉及使用以下运算符之一的比较时，unvdb 查询规划器将考虑使用 B-tree 索引：

<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL

此外，如果模式是一个常量并且在模式的开头是锚点，查询规划器可以对涉及模式匹配运算符 LIKE 和 ~ 的查询使用 B-tree 索引，例如：

column_name LIKE 'foo%'
column_name LKE 'bar%'
column_name  ~ '^foo'

而 col LIKE '%bar' 则不会使用 B-tree 索引。

对于模式匹配运算符 ILIKE 和 ~*，如果模式以以非字母字符(不受大小写影响的字符)开头，查询规划器将考虑使用 B-tree 索引。

如果您已经开始使用索引来优化 unvdb 数据库，那么 B-tree 可能就是您想要的。

哈希索引

哈希索引只能处理简单的相等比较 (=)。这意味着每当索引列使用 = 运算符进行比较时，查询计划器将考虑使用哈希索引。

要创建哈希索引，请在子句中使用 CREATE INDEX 带有 HASH 索引类型的 USING 语句，如下所示：

CREATE INDEX index_name
ON table_name USING HASH (indexed_column);

GIN 索引

GIN 索引是“倒排索引”。它很适合索引那些复杂的值(比如 array, hstore， json 和 range)。

倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。

当您将多个值存储在单个列中时，GIN 索引最有用。

BRIN 索引

BRIN 表示块范围索引（Block Range Indexes），存储有关存放在一个表的连续物理块范围上的值摘要信息。与 B-tree 索引相比，BRIN 更小，维护成本更低。

BRIN 允许在非常大的表上使用索引，这在以前使用没有水平分区的 B-tree 是不切实际的。

BRIN 常用于具有线性排序顺序的列，例如，销售订单表的创建日期列。

GiST 索引

GiST 索引不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。

GiST 代表广义搜索树。GiST 索引允许构建通用的树结构。GiST 索引可用于索引几何数据类型和全文搜索。

SP-GiST 索引

SP-GiST 代表空间分区的 GiST。SP-GiST 支持分区搜索树，有助于开发各种不同的非平衡数据结构。

SP-GiST 索引最适用于具有自然聚类元素但也不是均衡树的数据，例如 GIS、多媒体、电话路由和 IP 路由。

创建索引

您可以使用 CREATE INDEX 向一个表中添加索引。

什么是索引

简单类比，索引可以看作是一个字典的目录。通过目录，您可以更快的查找词语。通过索引，您可以更快的从表中检索数据。索引是一种有序的数据结构，需要额外的空间存储。

`CREATE INDEX` 语法

以下是 unvdb CREATE INDEX 语句的简单语法：

CREATE [ UNIQUE ] INDEX [ [ IF NOT EXISTS ] name ]
    ON table_name [ USING method ]
(
    column_name [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
    [, ...]
);

name 索引的名称。它是可选的。如果您不指定索引名称，unvdb 将会自动生成一个。
table_name 是要为其创建索引的表名。
method 是索引方法的名称。包括 btree, hash, gist, spgist, gin, 和 brin。 btree 是默认的方法。您可以查看索引类型以了解更多。
column_name 是要创建索引的列名。
[ ASC | DESC ] 指定排序是顺序还是逆序。它是可选的， ASC 是默认值。
NULLS FIRST 或 NULLS LAST 指定排序时空值在非空值之前或之后。当指定 DESC 时, NULLS FIRST 是默认的，否则 NULLS LAST 是默认的。
UNIQUE 指示创建一个唯一索引。
IF NOT EXISTS 指示只有指定的索引名称不存在时才创建索引。

unvdb 会对主键列自动创建索引。

要检查查询是否使用索引，请使用该 EXPLAIN 语句。

如果您在一个索引中使用了多个列，那么此索引被称为多列索引或复合索引。

`CREATE INDEX` 示例

我们将使用 address表进行演示。

以下查询查找邮编为 x 的地址：

SELECT * FROM address WHERE postal_code = 'x';

由于该 postal_code 列没有可用的索引， unvdb 不得不进行全表扫描。进可以通过查看查询计划来验证这一点。

要显示执行计划，请使用 EXPLAIN 如下语句：

EXPLAIN SELECT * FROM address WHERE postal_code = 'x';
                        
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on address  (cost=0.00..13.54 rows=1 width=161)
   Filter: ((postal_code)::text = 'x'::text)

要为 address 表 postal_code 列中的值创建索引，请使用以下语句：

CREATE INDEX ON address (postal_code);

这里没有指定索引名称，unvdb 将会自动生成一个索引名称： address_postal_code_idx。格式为表名加列名再加 idx 后缀。

现在，再次查看执行计划，您会发现数据库引擎使用索引进行查找：

EXPLAIN SELECT * FROM address WHERE postal_code = 'x';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using address_postal_code_idx on address  (cost=0.28..8.29 rows=1 width=161)
   Index Cond: ((postal_code)::text = 'x'::text)

这里， using address_postal_code_idx 说明了 unvdb 使用了索引进行查找。

删除索引

您可以使用 DROP INDEX 从一个表中删除现有的索引。

`DROP INDEX` 语法

有时，您可能希望从数据库系统中删除现有索引。为此，您可以使用以下 DROP INDEX 语句：

DROP INDEX  [ CONCURRENTLY ]
[ IF EXISTS ]  name
[ CASCADE | RESTRICT ];

name 是要删除的索引的名称。
IF EXISTS 指示如果指定的索引不存在将不会给出错误， unvdb 将会发出一个通知。
CASCADE 指示 unvdb 自动删除那些依赖于此索引的对象。
RESTRICT 指示如果有其他对象依赖于此索引，则拒绝删除。这是默认的行为。
删除索引时， unvdb 默认会获取该表的排他锁并阻止任何其他的访问，知道索引删除完成。您可以使用 CONCURRENTLY 选项改变这一行为。

注意，当使用 CASCADE 选项时，不支持 CONCURRENTLY。

一个简单的删除索引的语句如下：

DROP INDEX name;

您可以使用一个语句同时删除多个索引，如下：

DROP INDEX name1, name2,... ;

`DROP INDEX` 实例

我们将使用 actor 表进行演示。

以下语句为 actor 表的 first_name 列创建索引：

CREATE INDEX idx_actor_first_name
ON actor (first_name);

有时，查询优化器不使用索引。例如，以下语句查找名为 John 的 actor：

SELECT * FROM actor
WHERE first_name = 'John';

该查询未使用 idx_actor_first_name 之前定义的索引，如以下 EXPLAIN 语句中所述：

EXPLAIN
SELECT * FROM actor
WHERE first_name = 'John';
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
   Filter: ((first_name)::text = 'John'::text)

这是因为查询优化器认为只扫描整个表来定位行更为优化。因此，在这种情况下没有用 idx_actor_first_name，我们需要删除它：

DROP INDEX idx_actor_first_name;

查看索引

您有两种方法查看表的索引：

在 ud_sql 工具中使用 \d 命令查看表的索引。
在 pg_indexes 视图中检索索引信息。

使用 ud_sql 命令列出 unvdb 索引

如果你使用 ud_sql 工具管理 unvdb 数据库，您可以使用 \d 命令( 查看指定表中的索引：

\d table_name

这个 \d 命令将返回表的所有信息，包括表的结构、索引、约束和触发器。

例如，以下语句返回 customer 表的详细信息：

\d customer
                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)
Foreign-key constraints:
    "customer_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "customer_store_id_fkey" FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "payment" CONSTRAINT "payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "payment_p2007_01" CONSTRAINT "payment_p2007_01_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_02" CONSTRAINT "payment_p2007_02_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_03" CONSTRAINT "payment_p2007_03_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_04" CONSTRAINT "payment_p2007_04_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_05" CONSTRAINT "payment_p2007_05_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_06" CONSTRAINT "payment_p2007_06_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "rental" CONSTRAINT "rental_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON customer FOR EACH ROW EXECUTE FUNCTION last_updated()

其中， Indexes 部分就是表中的所有的索引。

使用 `pg_indexes` 视图列出 unvdb 索引

unvdb 内置的 pg_indexes 视图允许您访问有关 unvdb数据库中每个索引的有用信息。该 pg_indexes 视图由五列组成：

schemaname: 存储包含表和索引的模式的名称。
tablename: 存放索引所属表的名称。
indexname: 存储索引的名称。
tablespace: 存储包含索引的表空间的名称。
indexdef: 以 CREATE INDEX 语句的形式存储索引定义命令。

例如，要列出 customer 表的所有索引，请使用以下语句：

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'customer';
     indexname     |                                    indexdef
-------------------+--------------------------------------------------------------------------------
 customer_pkey     | CREATE UNIQUE INDEX customer_pkey ON public.customer USING btree (customer_id)
 idx_fk_address_id | CREATE INDEX idx_fk_address_id ON public.customer USING btree (address_id)
 idx_fk_store_id   | CREATE INDEX idx_fk_store_id ON public.customer USING btree (store_id)
 idx_last_name     | CREATE INDEX idx_last_name ON public.customer USING btree (last_name)
(4 rows)

如果您想获取 unvdb 数据库中的所有的索引，您可以不使用 WHERE子句。

多列索引

顾名思义，多列索引是在表的多个列上定义的索引。多列索引，又称为复合索引或组合索引。

在 unvdb 中，只有 B-tree、GIST、GIN 和 BRIN 索引类型支持多列索引。一个多列索引最多支持 32 个列。

多列索引规则

定义多列索引时，应将 WHERE子句中常用的列放在列列表的开头，将不常用的列放在后面的条件中。否则， unvdb 优化器可能不会使用索引。

例如，以下语句在 a, b 和 c 列上定义上了索引：

CREATE INDEX index_name ON table_name(a, b, c);

上述语法中，unvdb 优化器在以下情况下会考虑使用索引：

WHERE a = v1 and b = v2 and c = v3;

或者

WHERE a = v1 and b = v2;

或者

WHERE a = v1;

但是，在以下情况下不会考虑使用索引：

WHERE  c = v3;

或者

WHERE b = v2 and c = v3;

多列索引示例

让我们在customer 表中演示 unvdb多列索引。

通过下面的语句查看 customer 表的信息：

\d customer
                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)
...

在 customer 表中已经存在两个索引，为了演示多列索引，我们先删掉 idx_last_name 索引：

DROP INDEX idx_last_name;

使用下面的语句在 last_name 和 first_name 列上创建索引：

CREATE INDEX ON customer (last_name, first_name);

再次通过下面的语句查看 customer 表的信息：

\d customer
                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "customer_last_name_first_name_idx" btree (last_name, first_name)
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)

我们发现在在 last_name 和 first_name 列上定义了 customer_last_name_first_name_idx 索引。

通过 EXPLAIN 语句查看以下语句是否使用了索引：

EXPLAIN SELECT * FROM customer WHERE last_name = 'A' AND first_name = 'B';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using customer_last_name_first_name_idx on customer  (cost=0.28..8.29 rows=1 width=70)
   Index Cond: (((last_name)::text = 'A'::text) AND ((first_name)::text = 'B'::text))
(2 rows)

这里，当 SELECT 语句的 WHERE 条件中同时具有 last_name 和 first_name 时， unvdb 优化器选择使用索引。

通过 EXPLAIN 语句查看以下语句是否使用了索引：

EXPLAIN SELECT * FROM customer WHERE last_name = 'A';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using customer_last_name_first_name_idx on customer  (cost=0.28..8.29 rows=1 width=70)
   Index Cond: ((last_name)::text = 'A'::text)
(2 rows)

这里，当 SELECT 语句的 WHERE 条件中只有 last_name 时，unvdb 优化器选择使用索引。这是因为 last_name 列是索引中的第一列。

通过 EXPLAIN 语句查看以下语句是否使用了索引：

EXPLAIN SELECT * FROM customer WHERE first_name = 'B';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..15.49 rows=1 width=70)
   Filter: ((first_name)::text = 'B'::text)
(2 rows)

这里，当 SELECT 语句的 WHERE 条件中只有 first_name 时， unvdb 优化器选择不使用索引。这是因为 first_name 列不是索引的第一列。

当您定义多列索引时，您应该始终考虑业务上下文以确定哪些列经常用于查找，并在定义索引时将这些列放在列列表的开头。



唯一索引

UNIQUE 索引用于强制一列或多列中值的唯一性。

`UNIQUE` 索引介绍

只有索引类型为 B-Tree 的索引可以声明为唯一索引。要创建 UNIQUE 索引，您可以使用以下语法：

CREATE UNIQUE INDEX index_name ON table_name(column_name, [...]);

UNIQUE 关键字用于将索引声明为唯一索引。

如果一个列被定义为唯一索引，那么该列不能存储具有相同的值。

如果两列或更多列被定义为唯一索引，则这些列中的组合值不能重复。

但是，您可以在具有 UNIQUE 索引的列中使用多个 NULL 值。

当您为表定义主键或唯一约束时，unvdb 会自动创建相应的 UNIQUE 索引。

`UNIQUE` 索引示例

以下语句创建一个名为 staff 的表：

CREATE TABLE staff (id SERIAL PRIMARY KEY,first_name VARCHAR(255) NOT NULL,last_name VARCHAR(255) NOT NULL,email VARCHAR(255) UNIQUE);

在这个语句中， id 是主键列， email 列有唯一约束，因此，unvdb 创建了两个 UNIQUE 索引，每个列一个。

要显示 staff 表的索引，请使用以下语句：

SELECT tablename,indexname,indexdef FROM pg_indexes WHERE tablename = 'staff';
 tablename |    indexname    |                                indexdef
-----------+-----------------+-------------------------------------------------------------------------
 staff     | staff_pkey      | CREATE UNIQUE INDEX staff_pkey ON public.staff USING btree (id)
 staff     | staff_email_key | CREATE UNIQUE INDEX staff_email_key ON public.staff USING btree (email)
(2 rows)

`UNIQUE` 索引 – 单列示例

以下语句将 mobile_phone 列添加到 staff 表中：

ALTER TABLE staff
ADD mobile_phone VARCHAR(20);

为确保所有员工的手机号码都是不同的，您可以为该 mobile_phone 列定义一个 UNIQUE 索引，如下所示：

CREATE UNIQUE INDEX idx_staff_mobile_phone
ON staff(mobile_phone);

让我们来测试一下。

首先，在 staff 表中插入一个新行：

INSERT INTO staff(first_name, last_name, email, mobile_phone) VALUES ('Adam','Z','adam@sqliz.com', '13333333333');

其次，尝试插入具有相同电话号码的另一行：

INSERT INTO staff(first_name, last_name, email, mobile_phone) VALUES ('Jack','W','jack@sqliz.com', '13333333333');

由于手机号重复，unvdb 出现如下错误：

ERROR:  duplicate key value violates unique constraint "idx_staff_mobile_phone"
DETAIL:  Key (mobile_phone)=(13333333333) already exists.

`UNIQUE` 索引 – 多列示例

以下语句将添加两个新列 work_phone 和 extension 到 staff 表中：

ALTER TABLE staff ADD work_phone VARCHAR(20),ADD extension VARCHAR(5);

多个员工可以共享同一个工作电话号码。但是，它们不能具有相同的分机号码。要强制执行此规则，您可以在 work_phone 和 extension 列上定义 UNIQUE 索引：

CREATE UNIQUE INDEX idx_staff_workphone ON staff(work_phone, extension);

要测试这个索引，首先在 staff 表中插入一行：

INSERT INTO staff(first_name, last_name, work_phone, extension) VALUES('Lily', 'Bush', '1234567','3564');

其次，插入另一个具有相同工作电话号码但分机号不同的员工：

INSERT INTO staff(first_name, last_name, work_phone, extension) VALUES('Joan', 'Doe', '1234567','3565');

该语句有效，因为 work_phone 和 extension 列中的值组合是唯一的。

第三，尝试在 staff 表中的 work_phone 和 extension 列中插入具有相同值的行：

INSERT INTO staff(first_name, last_name, work_phone, extension)
VALUES('Tommy', 'Stark', '1234567','3565');

unvdb 发出以下错误：

ERROR:  duplicate key value violates unique constraint "idx_staff_workphone"
DETAIL:  Key (work_phone, extension)=(1234567, 3565) already exists.

表达式索引

除了在列上创建索引， unvdb 允许您在基于表中的一列或者多列的表达上是创建索引，这称为表达式索引。

请注意，表达式上的索引维护起来非常昂贵，因为 unvdb 必须在插入或更新每一行时评估每一行的表达式，并将结果用于索引。因此，只有当检索速度比插入和更新速度更重要时，您才应该在表达式上使用索引。

表达式索引语法

要创建表达式索引，请遵循如下语法：

CREATE INDEX index_name
ON table_name ( (expression) );

解释：

创建表达式索引和创建普通索引的语法基本相同，只是由表达式代替了列名。
表达式一般通过圆括号包含起来。如果表达式只是一个函数调用，则可以省率圆括号。
index_name 为索引名称。您可以省率索引名称，并且 unvdb 会自动生成一个。

一旦定义了索引表达式，当定义索引的表达式出现在 WHERE 子句或 ORDER BY SQL 语句的子句中时，unvdb 将考虑使用该索引。

表达式索引示例

下面通过 customer 表来演示 unvdb 表达式索引。

下面是 customer 表的部分定义：

                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)

...

customer 表中有一个为 last_name 列定义的 B 树索引 idx_last_name。以下 EXPLAIN 语句展示查询查找姓氏为 Adam 的客户时 unvdb 使用 idx_last_name 索引：

EXPLAIN SELECT * FROM customer WHERE last_name = 'Adam';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=70)
   Index Cond: ((last_name)::text = 'Adam'::text)
(2 rows)

但是，当查找姓氏为 adam 小写的客户时，unvdb 无法利用索引进行查找：

EXPLAIN SELECT * FROM customer WHERE LOWER(last_name) = 'adam';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..16.98 rows=3 width=70)
   Filter: (lower((last_name)::text) = 'adam'::text)
(2 rows)

为了提高 LOWER() 函数的查询效率，你可以定义一个这样的索引表达式：

CREATE INDEX ON customer(LOWER(last_name));

现在，再次查看上面语句执行计划，

EXPLAIN SELECT * FROM customer WHERE LOWER(last_name) = 'adam';
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on customer  (cost=4.30..10.83 rows=3 width=70)
   Recheck Cond: (lower((last_name)::text) = 'adam'::text)
   ->  Bitmap Index Scan on customer_lower_idx  (cost=0.00..4.30 rows=3 width=0)
         Index Cond: (lower((last_name)::text) = 'adam'::text)
(4 rows)

您会发现， unvdb 优化器使用了索引。

部分索引

部分索引由助于减少索引大小，因为它没有为列中的所有数据创建索引，它只为符合条件的行中的数据建立索引。

如果您有 WHERE使用常量值的常用条件，则部分索引很有用，如下所示：

SELECT * FROM table_name WHERE column_name = constant_value;

让我们看一下 customer 表：

\d customer
                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "customer_lower_idx" btree (lower(last_name::text))
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)

例如，您通常对不活跃的客户感兴趣，并经常进行一些跟进以让他们回来购买更多东西。

以下查询查找所有不活动的客户：

SELECT * FROM customer WHERE active = 0;

要执行此查询，查询计划器需要扫描 customer 表，如以下 EXPLAIN 语句所示：

EXPLAIN SELECT * FROM customer WHERE active = 0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..15.49 rows=15 width=70)
   Filter: (active = 0)
(2 rows)

您可以通过为 active 列创建索引来优化此查询，如下所示：

CREATE INDEX idx_customer_active
ON customer(active);

该索引实现了其目的，但是，它包含许多从未搜索过的行，即所有活跃客户。

要定义仅包含非活动客户的索引，请使用以下语句：

CREATE INDEX idx_customer_inactive ON customer(active) WHERE active = 0;

该语句定义了一个部分索引 idx_customer_inactive，只有符合 WHERE 条件 active = 0 中的 active 值才会被索引。相比较为所有 active 值建立的索引 idx_customer_active，此索引的大小会很小。

从现在开始，只要该 WHERE 子句出现在查询中，unvdb 就会考虑部分索引：

EXPLAIN SELECT * FROM customer WHERE active = 0;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Index Scan using idx_customer_active on customer  (cost=0.15..11.69 rows=15 width=70)
   Index Cond: (active = 0)
(2 rows)

重建索引

当索引被损坏或者包含错误的数据时，您可以重建索引。unvdb 提供 REINDEX 语句用来重建一个或多个索引。

`REINDEX` 语法

这里是unvdb REINDEX 语句的语法：

REINDEX
  [ ( VERBOSE ) ]
  [ ( CONCURRENTLY [ boolean ] ) ]
  [ ( TABLESPACE new_tablespace ) ]
  { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;

说明：

VERBOSE 关键字是可选的。如果提供此关键字，重建索引时将会显示进度。
CONCURRENTLY 关键是可选的。它指示 unvdb 在重建索引时，不需要阻止任何表上的操作。
TABLESPACE new_tablespace 是可选的。它指示 unvdb 在新的表空间重建索引。
```
{ INDEX | TABLE | SCHEMA | DATABASE | SYSTEM }
```
用来指定重建的索引对象。
- INDEX: 重建指定的索引。
- TABLE: 重建指定的表中的所有的索引。
- SCHEMA: 重建指定的 Schema 中的所有的索引。
- DATABASE: 重建指定的数据库中的所有的索引。
- SYSTEM: 重建指定的数据库的系统目录中的所有的索引。
name 指定对象的名字。

下面是一些具体的用法：

要重建单个索引，请使用 INDEX 关键字并指定索引名称：
```
REINDEX INDEX index_name;
```
要重建一个表中的所有索引，请使用 TABLE 关键字并指定表的名称：
```
REINDEX TABLE table_name;
```
要重建一个架构中的所有索引，请使用 SCHEMA 关键字并指定架构的名称：
```
REINDEX SCHEMA schema_name;
```
要重建一个数据库中的所有索引，请使用 DATABASE 关键字并指定数据库名称：
```
REINDEX DATABASE database_name;
```
要重建一个数据中的系统目录上的所有索引，请使用 SYSTEM 关键字并指定数据库名称：：
```
REINDEX SYSTEM database_name;
```

`REINDEX` 与 `DROP INDEX`&`CREATE INDEX`

重建索引的过程相当于先删掉索引在新建相同定义的索引。也就是说： REINDEX 相当于 DROP INDEX 和 CREATE INDEX 语句的组合。它他们之间也有一些不同之处：

REINDEX 语句：
- 锁定索引所属表的写入但不锁定读取。
- 对正在处理的索引进行排他锁，这会阻止尝试使用该索引的读取。除非您指定 CONCURRENTLY 关键字。
DROP INDEX 和 CREATE INDEX 语句：
- DROP INDEX 通过获取表上的排他锁来锁定索引所属表的写入和读取。
- CREATE INDEX 语句会锁定索引的父表中的写入但不锁定读取。但是，在创建索引期间读取可能会很昂贵。

UNVDB基础之索引

索引的使用及解释

索引类型

B-tree 索引

哈希索引

GIN 索引

BRIN 索引

GiST 索引

SP-GiST 索引

创建索引

什么是索引

CREATE INDEX 语法

CREATE INDEX 示例

删除索引

DROP INDEX 语法

DROP INDEX 实例

查看索引

使用 ud_sql 命令列出 unvdb 索引

使用 pg_indexes 视图列出 unvdb 索引

多列索引

多列索引规则

多列索引示例

当您定义多列索引时，您应该始终考虑业务上下文以确定哪些列经常用于查找，并在定义索引时将这些列放在列列表的开头。



唯一索引

UNIQUE 索引介绍

UNIQUE 索引示例

UNIQUE 索引 – 单列示例

UNIQUE 索引 – 多列示例

表达式索引

表达式索引语法

表达式索引示例

部分索引

重建索引

REINDEX 语法

REINDEX 与 DROP INDEX&CREATE INDEX

`CREATE INDEX` 语法

`CREATE INDEX` 示例

`DROP INDEX` 语法

`DROP INDEX` 实例

使用 `pg_indexes` 视图列出 unvdb 索引

`UNIQUE` 索引介绍

`UNIQUE` 索引示例

`UNIQUE` 索引 – 单列示例

`UNIQUE` 索引 – 多列示例

`REINDEX` 语法

`REINDEX` 与 `DROP INDEX`&`CREATE INDEX`