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

多列索引

​专栏内容：

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

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

文章目录

概述

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

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

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

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

多列索引创建

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

创建语法

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

CREATEINDEX index_name
ON table_name(column1, column2,...);

基本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类型的多列索引进行分析，看看多列索引的运行机制。

创建数据

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

CREATETABLE test1 (
    major int,
    minor int,lastint,
    name varchar);

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

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

创建索引

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

postgres=>createindex idx_test1 ON test1 (major ,minor,last);CREATEINDEX
postgres=> \d test1
                   Table"senlleng.test1"Column|Type| Collation | Nullable |Default--------+-------------------+-----------+----------+---------
 major  |integer|||
 minor  |integer|||last|integer|||
 name   |charactervarying|||
Indexes:
    "idx_test1"btree(major, minor,last)

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

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

带首列查询

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

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

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

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

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

同样也使用了索引，同时很惊奇的发现，

 Index Cond: ((major = 1005) AND (minor > 5000) AND (last < 8000))

索引条件居然与我们

where

子句中的相反。

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

• 带major，另外两列之一；

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

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

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

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

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

不带首列查询

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

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

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

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

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

总结

• 在多列上创建索引时，必须把使用最频繁的列放在索引列的最前面；
• 通过案例分析，可以看到只有在查询条件中带有第一列时，查询计划中才会用到索引，即使将条件中各列的顺序打乱，优化器也会按索引中的列的顺序进行查找路径。
• 在使用多列索引时，避免出现案例中索引失效的场景。

结尾

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

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

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