ToB企服应用市场:ToB评测及商务社交产业平台

标题: 【postgresql低级使用】部门索引 partial index，索引覆盖表的部门数据，建 [打印本页]
作者: 怀念夏天    时间: 2024-6-15 03:00
标题: 【postgresql低级使用】部门索引 partial index，索引覆盖表的部门数据，建
部门索引

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

在通常创建的索引中，会覆盖一列的全部数据，而有些时间，访列的一部门数据经常被用到，而另一些数据几乎不会用到，假如全部数据都被索引引用，会造成索引数据占用空间比较大。
本文就来先容postgresql 中的部门索引(partial index)，使用它可以让自己关心的数据，大概热门数据独立创建索引，同时通过几个案例，来分享它的几种独特的用法。
部门索引的使用

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

部门索引的创建SQL与普通索引类似，也可以使用using子句选择索引范例，下面是创建部门索引的语法SQL。
  1. CREATE INDEX index_name
  2. ON tablename(column1, column2, ...)
  3. WHERE qual;
复制代码
与普通索引差别的是，在索引界说最后增加where子句，用于过滤创建索引的数据。

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

下面来演示一下创建部门索引。
照旧用前面一节用的表布局，它的界说如下：
  1. -- 创建产品表  
  2. CREATE TABLE products (  
  3.     product_id INT PRIMARY KEY,  
  4.     product_name VARCHAR(255) NOT NULL,  
  5.     price DECIMAL(10, 2) NOT NULL,  
  6.     category VARCHAR(255)  
  7. );  
  8.   
复制代码
数据的批量天生与插入，参见前面章节《物化视图》中有详细说明，这里不再赘述。
我们创建此中一类商品的索引，因为这类商品是主销商品。
  1. postgres=> create index pidx_cate6 ON products (category ) where category='Category6';
  2. CREATE INDEX
  3. Time: 17.965 ms
复制代码
此时创建了关于6号范例的商品的索引，下面我们看一下索引的使用环境，查看方法可以参考前一章节《索引使用》。
  1. postgres=> select * from products where category ='Category6' and product_name='pxyiitdjjo';
  2. product_id | product_name | price  | category
  3. ------------+--------------+--------+-----------
  4.          24 | pxyiitdjjo   | 411.59 | Category6
  5. (1 row)
  7. Time: 1.964 ms
  8. postgres=> explain select * from products where category ='Category6' and product_name='pxyiitdjjo';
  9.                                  QUERY PLAN
  10. -----------------------------------------------------------------------------
  11. Bitmap Heap Scan on products  (cost=93.05..981.35 rows=1 width=31)
  12.    Recheck Cond: ((category)::text = 'Category6'::text)
  13.    Filter: ((product_name)::text = 'pxyiitdjjo'::text)
  14.    ->  Bitmap Index Scan on pidx_cate6  (cost=0.00..93.05 rows=9753 width=0)
  15. (4 rows)
  17. Time: 0.259 ms
复制代码
查询一个范例6下面的商品，通过explain查看它的实行计划，可以看到通过pidx_cate6索此进行了最内层的过滤Bitmap Index Scan on pidx_cate6，用到了我们刚才创建的部门索引。
那我们再来看一下，假如查询一个范例7的商品，会不会使用索引呢？
  1. postgres=> explain select * from products where category ='Category7' and product_name='pxyiitdjjo';
  2.                                              QUERY PLAN
  3. ----------------------------------------------------------------------------------------------------
  4. Seq Scan on products  (cost=0.00..2242.00 rows=1 width=31)
  5.    Filter: (((category)::text = 'Category7'::text) AND ((product_name)::text = 'pxyiitdjjo'::text))
  6. (2 rows)
  8. Time: 0.246 ms
复制代码
正如我们所料，查询范例7中的商品，使用了顺序查找的方法，没有索引可用。
使用场景

部门索引在现实项目中照旧非常有用的，下面我们来分享几种具体的应用场景。
用于有用数据

在一些管理体系中，经常会积累大量的历史数据，但是经常使用的都是最新数据。
比如员工信息管理体系中，颠末几年的使用，此中员工信息表中有一部门离职员的信息，它不会从数据库中删除，但在业务流中几乎不再访问。随着企业的发展，这类数据占比会逐步提高，严峻影响对员工信息表的使用服从。
对于类似场景，就可以创建部门数据索引，只对有用数据，大概是关心的数据创建索引，可以大大提升数据使用的性能。
下面以员工信息来举例。
员工信息表界说

  1. CREATE TABLE employee (  
  2.     eid int primary key,
  3.     ename VARCHAR(50) NOT NULL,  
  4.     birth_date DATE,  
  5.     position VARCHAR(100),  
  6.     department VARCHAR(100) ,  
  7.     email VARCHAR(100) ,  
  8.     phone_number VARCHAR(20),  
  9.     hire_date date ,  
  10.     address varchar,  
  11.     emergency_contact VARCHAR(100),
  12.     isLeave boolean
  13. );
复制代码
此中 isLeave字段表现是否在职。
员工信息数据

  1. 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);
  2. INSERT 0 2
  3. Time: 8.032 ms
  4. postgres=> select * from employee ;
  5. eid |  ename   | birth_date | position |     department      | email | phone_number | hire_date  | address | emergency_contact | isleave
  6. -----+----------+------------+----------+---------------------+-------+--------------+------------+---------+-------------------+---------
  7.    1 | lihua    | 2010-10-01 | jiangsu  | sales department    | abc   | 123          | 2015-04-22 | jiangsu | 34                | f
  8.    2 | zhanglei | 2000-10-01 | jiangsu  | software department | df    | 444          | 2015-01-22 | jiangsu | 65645             | t
  9. (2 rows)
  11. Time: 0.172 ms
复制代码
插入了两行数据，此中一人已经离职。
创建在职人员的索引

  1. postgres=> create index pidx_isleave on employee (isleave ) where isLeave=false;
  2. CREATE INDEX
  3. Time: 8.087 ms
复制代码
如许在查询在职人员时就可以使用部门索引进行优化，而不是全量的数据中查询。
用于唯一性索引

案例三，设置一个部门唯一性索引
对于一个效果表来说，成功分支只有一个，产生的效果最多也只有一个；还有一个部门的组织架构中，manager职位也是最多只有一个。
这些都可以使用部门索引特性，只在这些值的数据上创建唯一性索引，来约束它们，如许在数据的最底层就可以控制，而不是通过编写程序来校验。
下面我们来举一个效果表的例子。
效果表界说

  1. CREATE TABLE tests (
  2. subject text,
  3. target text,
  4. success boolean
  5. );
复制代码
此中效果字段success对应的内容为 subject和target。
在整个测试中，成功的分支只有一个，也就是对应的效果success=true只有一行数据，对应的subject和target也是唯一的。
创建唯一性索引

要包管唯一的条件时，当success为true时，subject与target也不能重复。
可以创建下面的唯一性索引来约束。
  1. CREATE UNIQUE INDEX tests_success_constraint ON tests (subject,
  2. target)
  3. WHERE success;
复制代码
当subject和target相同时，算作一个测试分支，此测试分支中效果为真数据最多只有一个。
总结

在本节中，重要测试了postgresql 中部门索引partial index的语法和创建，并演示了SQL的使用。
同时也枚举了一些部门索引的使用场景和案例，能帮助我们在数据库应用计划中，简化模型和提升性能。
结尾

   非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，假如觉得值得鼓励，请点赞，收藏，我会更加努力！
  作者邮箱：study@senllang.onaliyun.com
如有错误大概疏漏接待指出，相互学习。
注：未经同意，不得转载！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4