部分索引

​专栏内容：

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

个人主页：我的主页

管理社区：开源数据库

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

文章目录

• 部分索引
• 概述
• 部分索引的使用
• • 语法介绍
  • 创建部分索引
  • 使用场景
  • • 用于有效数据
    • • 员工信息表定义
      • 员工信息数据
      • 创建在职人员的索引
      • 用于唯一性索引
      • • 结果表定义
        • 创建唯一性索引
        • 总结
        • 结尾

          概述

          ---

          在通常创建的索引中，会覆盖一列的全部数据，而有些时候，访列的一部分数据经常被用到，而另一些数据几乎不会用到，如果全部数据都被索引引用，会造成索引数据占用空间比较大。

          本文就来介绍postgresql 中的部分索引(partial index)，使用它可以让自己关心的数据，或者热点数据独立创建索引，同时通过几个案例，来分享它的几种独特的用法。

          部分索引的使用

          ---

          本节分享部分索引(partial index)的语法，以及创建部分索引的演示。

          语法介绍

          部分索引的创建SQL与普通索引类似，也可以使用using子句选择索引类型，下面是创建部分索引的语法SQL。

          CREATE INDEX index_name
          ON tablename(column1, column2, ...)
          WHERE qual;
          

          与普通索引不同的是，在索引定义最后增加where子句，用于过滤创建索引的数据。

          • 索引删除

            索引删除语法与普通索引是一样的，可以参考前一章节《索引使用》。

            创建部分索引

            下面来演示一下创建部分索引。

            还是用前面一节用的表结构，它的定义如下：

            -- 创建产品表  
            CREATE TABLE products (  
                product_id INT PRIMARY KEY,  
                product_name VARCHAR(255) NOT NULL,  
                price DECIMAL(10, 2) NOT NULL,  
                category VARCHAR(255)  
            );  
              
            

            数据的批量生成与插入，参见前面章节《物化视图》中有详细说明，这里不再赘述。

            我们创建其中一类商品的索引，因为这类商品是主销商品。

            postgres=> create index pidx_cate6 ON products (category ) where category='Category6';
            CREATE INDEX
            Time: 17.965 ms
            

            此时创建了关于6号类型的商品的索引，下面我们看一下索引的使用情况，查看方法可以参考前一章节《索引使用》。

            postgres=> select * from products where category ='Category6' and product_name='pxyiitdjjo';
             product_id | product_name | price  | category
            ------------+--------------+--------+-----------
                     24 | pxyiitdjjo   | 411.59 | Category6
            (1 row)
            Time: 1.964 ms
            postgres=> explain select * from products where category ='Category6' and product_name='pxyiitdjjo';
                                             QUERY PLAN
            -----------------------------------------------------------------------------
             Bitmap Heap Scan on products  (cost=93.05..981.35 rows=1 width=31)
               Recheck Cond: ((category)::text = 'Category6'::text)
               Filter: ((product_name)::text = 'pxyiitdjjo'::text)
               ->  Bitmap Index Scan on pidx_cate6  (cost=0.00..93.05 rows=9753 width=0)
            (4 rows)
            Time: 0.259 ms
            

            查询一个类型6下面的商品，通过explain查看它的执行计划，可以看到通过pidx_cate6索此进行了最内层的过滤Bitmap Index Scan on pidx_cate6，用到了我们刚才创建的部分索引。

            那我们再来看一下，如果查询一个类型7的商品，会不会使用索引呢？

            postgres=> explain select * from products where category ='Category7' and product_name='pxyiitdjjo';
                                                         QUERY PLAN
            ----------------------------------------------------------------------------------------------------
             Seq Scan on products  (cost=0.00..2242.00 rows=1 width=31)
               Filter: (((category)::text = 'Category7'::text) AND ((product_name)::text = 'pxyiitdjjo'::text))
            (2 rows)
            Time: 0.246 ms
            

            正如我们所料，查询类型7中的商品，使用了顺序查找的方法，没有索引可用。

            使用场景

            ---

            部分索引在实际项目中还是非常有用的，下面我们来分享几种具体的应用场景。

            用于有效数据

            在一些管理系统中，经常会积累大量的历史数据，但是经常使用的都是最新数据。

            比如员工信息管理系统中，经过几年的使用，其中员工信息表中有一部分离职员的信息，它不会从数据库中删除，但在业务流中几乎不再访问。随着企业的成长，这类数据占比会慢慢提高，严重影响对员工信息表的使用效率。

            对于类似场景，就可以建立部分数据索引，只对有效数据，或者是关心的数据建立索引，可以大大提升数据使用的性能。

            下面以员工信息来举例。

            员工信息表定义

            CREATE TABLE employee (  
                eid int primary key,
                ename VARCHAR(50) NOT NULL,  
                birth_date DATE,  
                position VARCHAR(100),  
                department VARCHAR(100) ,  
                email VARCHAR(100) ,  
                phone_number VARCHAR(20),  
                hire_date date ,  
                address varchar,  
                emergency_contact VARCHAR(100),
                isLeave boolean
            );
            

            其中 isLeave字段表示是否在职。

            员工信息数据

            postgres=> insert into employee values(1,'lihua','2010-10-1','jiangsu','sales department','abc','123','2015-4-22','jiangsu','34', false),(2,'zhanglei','2000-10-1','jiangsu','software department','df','444','2015-1-22','jiangsu','65645',true);
            INSERT 0 2
            Time: 8.032 ms
            postgres=> select * from employee ;
             eid |  ename   | birth_date | position |     department      | email | phone_number | hire_date  | address | emergency_contact | isleave
            -----+----------+------------+----------+---------------------+-------+--------------+------------+---------+-------------------+---------
               1 | lihua    | 2010-10-01 | jiangsu  | sales department    | abc   | 123          | 2015-04-22 | jiangsu | 34                | f
               2 | zhanglei | 2000-10-01 | jiangsu  | software department | df    | 444          | 2015-01-22 | jiangsu | 65645             | t
            (2 rows)
            Time: 0.172 ms
            

            插入了两行数据，其中一人已经离职。

            创建在职人员的索引

            postgres=> create index pidx_isleave on employee (isleave ) where isLeave=false;
            CREATE INDEX
            Time: 8.087 ms
            

            这样在查询在职人员时就可以使用部分索引进行优化，而不是全量的数据中查询。

            用于唯一性索引

            案例三，设置一个部分唯一性索引

            对于一个结果表来说，成功分支只有一个，产生的结果最多也只有一个；还有一个部门的组织架构中，manager职位也是最多只有一个。

            这些都可以使用部分索引特性，只在这些值的数据上创建唯一性索引，来约束它们，这样在数据的最底层就可以控制，而不是通过编写程序来校验。

            下面我们来举一个结果表的例子。

            结果表定义

            CREATE TABLE tests (
            subject text,
            target text,
            success boolean
            );
            

            其中结果字段success对应的内容为 subject和target。

            在整个测试中，成功的分支只有一个，也就是对应的结果success=true只有一行数据，对应的subject和target也是唯一的。

            创建唯一性索引

            要保证唯一的条件时，当success为true时，subject与target也不能重复。

            可以创建下面的唯一性索引来约束。

            CREATE UNIQUE INDEX tests_success_constraint ON tests (subject,
            target)
            WHERE success;
            

            当subject和target相同时，算作一个测试分支，此测试分支中结果为真数据最多只有一个。

            总结

            ---

            在本节中，主要测试了postgresql 中部分索引partial index的语法和创建，并演示了SQL的使用。

            同时也列举了一些部分索引的使用场景和案例，能帮助我们在数据库应用设计中，简化模型和提升性能。

            结尾

            ---

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

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

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

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