PostgreSQL 多列索引
本文介绍在 PostgreSQL 中如何创建多列索引。
顾名思义，多列索引是在表的多个列上定义的索引。多列索引，又称为复合索引或组合索引。
在 PostgreSQL 中，只有 B-tree、GIST、GIN 和 BRIN 索引类型支持多列索引。一个多列索引最多支持 32 个列。
PostgreSQL 多列索引规则
定义多列索引时，应将 WHERE 子句中常用的列放在列列表的开头，将不常用的列放在后面的条件中。否则， PostgreSQL 优化器可能不会使用索引。
例如，以下语句在 a, b 和 c 列上定义上了索引：
CREATE INDEX index_name
ON table_name(a, b, c);
上述语法中，PostgreSQL 优化器在以下情况下会考虑使用索引：
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;
PostgreSQL 多列索引示例
让我们在 Sakila 示例数据库中的 customer 表中演示 PostgreSQL 多列索引。
通过下面的语句查看 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 时， PostgreSQL 优化器选择使用索引。
通过 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 时， PostgreSQL 优化器选择使用索引。这是因为 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 时， PostgreSQL 优化器选择不使用索引。 这是因为 first_name 列不是索引的第一列。
结论
本文讨论了 PostgreSQL 多列索引以及多列索引的顺序对与 PostgreSQL 优化器的影响。
当您定义多列索引时，您应该始终考虑业务上下文以确定哪些列经常用于查找，并在定义索引时将这些列放在列列表的开头。