【postgresql初级使用】在表的多个频繁使用列上创建一个索引，多条件查询优化，多场景案例揭示索引失效

多列索引

​专栏内容：

• postgresql使用入门基础
• 手写数据库toadb
• 并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

概述

---

在实际业务的查询中，往往会带有多个过滤条件，涉及多个列，而索引也能够以多列数据构成。

在postgresql 中，一个索引可以由最多32个列来构建，如果业务中有多列查询情况，可以对此进行优化。

当然，多列索引在应用时，复杂度会提升，应用不当反尔性能下降。

本文就来分享一下多列索引的使用，同时通过案例来剖析它的原理，揭示它的优势与不足，避免遇坑。

多列索引创建

---

下面分享多列索引的创建语法，在postgresql中的限制说明。

创建语法

多列索引的创建SQL语法如下：

CREATE INDEX index_name
ON table_name(column1, column2, ...);
1
2

基本SQL形式与普通索引相同，只是在选择表的列时，可以指定为多列，这里最多为32个列。

创建说明

• 支持的索引类型

当然也可以使用using子句指定索引类型，不指定时默认为btree类型。

在postgresql中，多列索引可以使用的索引类型有btree, brin, gin和gist，其它类型不支持。

• 列的顺序

创建索引时，指定列的顺序是有讲究的，不然会事得其反。

在where条件中，使用频繁程度高的列，要放在前面，依次排序。

比如 column1,column2,column3这样的顺序建索引时，那么对于

... where column1 = a;

... where column1 = a and column2 = b;

... where column1 = a and column2 = b and column3 = c;

这三种情况都可以用到该索引，其中column1的使用频率最高，column2次之，最后是column3。

• 列的数量

虽然多列索引可以支持最大32列，通过实践证明，最多不要超过两到三列。

案例分析

---

通过对btree类型的多列索引进行分析，看看多列索引的运行机制。

创建数据

为了演示方便，我们创建如下数据表。

CREATE TABLE test1 (
    major int,
    minor int,
    last  int,
    name varchar
  );
1
2
3
4
5
6

往表中插入10万条测试数据。

postgres=> INSERT INTO test1(major,minor,last,name)
select id, (random() * 100000)::int, (random() * 100000 + 100)::int, 'name' || id::int FROM generate_series(1, 100000) as id;

INSERT 0 100000
1
2
3
4

创建索引

在major, minor, last 三列数据上创建一个索引，操作如下：

postgres=> create index idx_test1 ON test1 (major ,minor, last);
CREATE INDEX
postgres=> \d test1
                   Table "senlleng.test1"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 major  | integer           |           |          |
 minor  | integer           |           |          |
 last   | integer           |           |          |
 name   | character varying |           |          |
Indexes:
    "idx_test1" btree (major, minor, last)
1
2
3
4
5
6
7
8
9
10
11
12

创建索引idx_test1，它是在三列上创建的默认btree索引，查看表定义，可以看到索引已经创建。

下面我们来看看如何使用此索引。

带首列查询

这里先来看看带有索引首列major带的条件查询，它可以分为以下情况：

• 条件带有major, minor, last三列；

postgres=> explain select * from test1 where major = 1005 and minor > 5000 and last < 8000;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_test1 on test1  (cost=0.42..8.44 rows=1 width=21)
   Index Cond: ((major = 1005) AND (minor > 5000) AND (last < 8000))
(2 rows)

1
2
3
4
5
6
7

可以看到使用了刚才创建的索引 idx_test1，那么我们将条件中各列的顺序进行调换，再来看看。

postgres=> explain select * from test1 where last < 8000 and minor > 5000 and major = 1005 ;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_test1 on test1  (cost=0.42..8.44 rows=1 width=21)
   Index Cond: ((major = 1005) AND (minor > 5000) AND (last < 8000))
(2 rows)
1
2
3
4
5
6

同样也使用了索引，同时很惊奇的发现， Index Cond: ((major = 1005) AND (minor > 5000) AND (last < 8000)) 索引条件居然与我们where子句中的相反。

这一变动，其实由查询优化器来做的，它为什么这么做呢？ 哎，看下面的案例分析就明白了。

• 带major，另外两列之一；

如果减少其中一列，还会用到索引路径吗？ 下面我们来看看还有第一列major，但是其它两列任选一列时，会是什么情况发生呢？

postgres=> explain select * from test1 where last < 8000  and major = 1005 ;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_test1 on test1  (cost=0.42..8.44 rows=1 width=21)
   Index Cond: ((major = 1005) AND (last < 8000))
(2 rows)

postgres=> explain select * from test1 where  minor > 5000 and major = 1005 ;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_test1 on test1  (cost=0.42..8.44 rows=1 width=21)
   Index Cond: ((major = 1005) AND (minor > 5000))
(2 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13

可以看到，索引仍然使用到了。

当然单独带有第一列时，也是同样可以使用索引的。

不带首列查询

如果不带第一列major时，又会是什么情况呢？

postgres=> explain select * from test1 where last < 8000 and minor > 5000 ;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on test1  (cost=0.00..2137.00 rows=7814 width=21)
   Filter: ((last < 8000) AND (minor > 5000))
(2 rows)

postgres=> explain select * from test1 where minor > 5000 ;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on test1  (cost=0.00..1887.00 rows=95076 width=21)
   Filter: (minor > 5000)
(2 rows)

postgres=> explain select * from test1 where last < 8000  ;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on test1  (cost=0.00..1887.00 rows=8218 width=21)
   Filter: (last < 8000)
(2 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

带有第二列，第三列，或者它们两者独立作为条件，执行计划中都没有使用到索引。

总结

---

• 在多列上创建索引时，必须把使用最频繁的列放在索引列的最前面；

• 通过案例分析，可以看到只有在查询条件中带有第一列时，查询计划中才会用到索引，即使将条件中各列的顺序打乱，优化器也会按索引中的列的顺序进行查找路径。

• 在使用多列索引时，避免出现案例中索引失效的场景。

结尾

---

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！