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

多列索引

​专栏内容：

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

  个人主页：我的主页

  管理社区：开源数据库

  座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

  文章目录

  • 多列索引
  • 概述
  • 多列索引创建
  • • 创建语法
    • 创建说明
    • 案例分析
    • • 创建数据
      • 创建索引
      • 带首列查询
      • 不带首列查询
      • 总结
      • 结尾

        概述

        ---

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

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

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

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

        多列索引创建

        ---

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

        创建语法

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

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

              创建数据

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

              CREATE TABLE test1 (
                  major int,
                  minor int,
                  last  int,
                  name varchar
                );
              

              往表中插入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
              

              创建索引

              在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)
              

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

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

              带首列查询

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

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

                postgres=> explain select * from test1 where major = 1005 and minor > 5000 and last  5000) AND (last  
                可以看到使用了刚才创建的索引 idx_test1，那么我们将条件中各列的顺序进行调换，再来看看。
                 
                postgres=> explain select * from test1 where last  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  
                同样也使用了索引，同时很惊奇的发现， Index Cond: ((major = 1005) AND (minor > 5000) AND (last  
                这一变动，其实由查询优化器来做的，它为什么这么做呢？ 哎，看下面的案例分析就明白了。
                 
                

                • 带major，另外两列之一；

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

                  postgres=> explain select * from test1 where last  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)
                  

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

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

                  不带首列查询

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

                  postgres=> explain select * from test1 where last  5000 ;
                                           QUERY PLAN
                  ------------------------------------------------------------
                   Seq Scan on test1  (cost=0.00..2137.00 rows=7814 width=21)
                     Filter: ((last  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  
                  带有第二列，第三列，或者它们两者独立作为条件，执行计划中都没有使用到索引。
                   
                  总结 
                   
                  

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

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

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

                    结尾

                    ---

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

                    作者邮箱：study@senllang.onaliyun.com

                    如有错误或者疏漏欢迎指出，互相学习。

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