位置: 编程技术 - 正文

PostgreSQL教程(八):索引详解

编辑:rootadmin

推荐整理分享PostgreSQL教程(八):索引详解,希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:,内容如对您有帮助,希望把文章链接给更多的朋友!

一、索引的类型:

PostgreSQL提供了多  种索引类型:B-Tree、Hash、GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引。 1. B-Tree: B-Tree索引主要用于等于和范围查询,特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时,PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中,PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询,如LIKE、ILIKE、~和 ~*,仅当模式存在一个常量,且该常量位于模式字符串的开头时,如col LIKE 'foo%'或col ~ '^foo',索引才会生效,否则将会执行全表扫描,如:col LIKE '%bar'。 2. Hash: 散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时,查询规划器会考虑使用散列索引。 这里需要额外说明的是,PostgreSQL散列索引的性能不比B-Tree索引强,但是散列索引的尺寸和构造时间则更差。另外,由于散列索引操作目前没有记录WAL日志,因此一旦发生了数据库崩溃,我们将不得不用REINDEX重建散列索引。 3. GiST: GiST索引不是一种单独的索引类型,而是一种架构,可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略,而使用特定的操作符类型。 4. GIN: GIN索引是反转索引,它可以处理包含多个键的值(比如数组)。与GiST类似,GIN同样支持用户定义的索引策略,从而可以使GIN索引根据不同的索引策略,而使用特定的操作符类型。作为示例,PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型,如:<@、@>、=、&&等。

二、复合索引:

PostgreSQL中的索引可以定义在数据表的多个字段上,如: 在当前的版本中,只有B-tree、GiST和GIN支持复合索引,其中最多可以声明个字段。 1. B-Tree类型的复合索引: 在B-Tree类型的复合索引中,该索引字段的任意子集均可用于查询条件,不过,只有当复合索引中的第一个索引字段(最左边)被包含其中时,才可以获得最高效率。 2. GiST类型的复合索引: 在GiST类型的复合索引中,只有当第一个索引字段被包含在查询条件中时,才能决定该查询会扫描多少索引数据,而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值,那么此时应用GiST索引就会比较低效。

3. GIN类型的复合索引: 与B-Tree和GiST索引不同的是,GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响,无论是哪种组合,都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下,单一字段上的索引就已经足够了,并且还节约时间和空间。除非表的使用模式非常固定,否则超过三个字段的索引几乎没什么用处。

三、组合多个索引:

PostgreSQL教程(八):索引详解

PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用),来处理单个索引扫描不能实现的场合。与此同时,系统还可以在多个索引扫描之间组成AND和OR的条件。比如,一个类似WHERE x = OR x = OR x = OR x = 的查询,可以被分解成四个独立的基于x字段索引的扫描,每个扫描使用一个查询子句,之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是,如果我们在x和y上分别存在独立的索引,那么一个类似WHERE x = 5 AND y = 6的查询,就会分别基于这两个字段的索引进行扫描,之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引,系统扫描每个需要的索引,然后在内存里组织一个BITMAP,它将给出索引扫描出的数据在数据表中的物理位置。然后,再根据查询的需要,把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后,检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的,因为这是位图的布局,这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句,那么还将会有一个额外的排序步骤。因为这个原因,以及每个额外的索引扫描都会增加额外的时间,这样规划器有时候就会选择使用简单的索引扫描,即使有多个索引可用也会如此。

四、唯一索引:

目前,只有B-Tree索引可以被声明为唯一索引。 如果索引声明为唯一索引,那么就不允许出现多个索引值相同的行。我们认为NULL值相互间不相等。 五、表达式索引:

表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况,如: 此时,如果我们仅仅是在col1字段上建立索引,那么该查询在执行时一定不会使用该索引,而是直接进行全表扫描。如果该表的数据量较大,那么执行该查询也将会需要很长时间。解决该问题的办法非常简单,在test1表上建立基于col1字段的表达式索引,如: CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1)); 如果我们把该索引声明为UNIQUE,那么它会禁止创建那种col1数值只是大小写有区别的数据行,以及col1数值完全相同的数据行。因此,在表达式上的索引可以用于强制那些无法定义为简单唯一约束的约束。现在让我们再看一个应用表达式索引的例子。 和上面的例子一样,尽管我们可能会为first_name和last_name分别创建独立索引,或者是基于这两个字段的复合索引,在执行该查询语句时,这些索引均不会被使用,该查询能够使用的索引只有我们下面创建的表达式索引。 CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧,就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用,那么可以省略,就像我们在第一个例子里显示的那样。

从索引维护的角度来看,索引表达式要相对低效一些,因为在插入数据或者更新数据的时候,都必须为该行计算表达式的结果,并将该结果直接存储到索引里。然而在查询时,PostgreSQL就会把它们看做WHERE idxcol = 'constant',因此搜索的速度等效于基于简单索引的查询。通常而言,我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。 六、部分索引:

部分索引(partial index)是建立在一个表的子集上的索引,而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。 由于不是在所有的情况下都需要更新索引,因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小,因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例: 1. 索引字段和谓词条件字段一致: 下面的查询将会用到该部分索引: 下面的查询将不会用该部分索引: 一个不能使用这个索引的查询可以是∶ 2. 索引字段和谓词条件字段不一致: PostgreSQL支持带任意谓词的部分索引,唯一的约束是谓词的字段也要来自于同样的数据表。注意,如果你希望你的查询语句能够用到部分索引,那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说,只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时,这个部分索引才能被用于该查询。 下面的查询一定会用到该部分索引: 那么对于如下查询呢? 这个查询将不像上面那个查询这么高效,毕竟查询的条件语句中没有用到索引字段,然而查询条件"billed is not true"却和部分索引的谓词完全匹配,因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下,该查询才能更有效一些。

下面的查询将不会用到部分索引。 3. 数据表子集的唯一性约束: 该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中,如果成功的数据较少,而不成功的数据较多时,该实现方法将会非常高效。 七、检查索引的使用:

见以下四条建议: 1. 总是先运行ANALYZE。 该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息,而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息,那么就会使用一些缺省数值,这样肯定是不准确的。因此,如果还没有运行ANALYZE就检查一个索引的使用状况,那将会是一次失败的检查。 2. 使用真实的数据做实验。 用测试数据填充数据表,那么该表的索引将只会基于测试数据来评估该如何使用索引,而不是对所有的数据都如此使用。比如从行中选行,规划器可能会考虑使用索引,那么如果从行中选1行就很难说也会使用索引了。因为行的数据很可能是存储在一个磁盘页面中,然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时,在模拟测试数据时也要注意,如果这些数据是非常相似的数据、完全随机的数据,或按照排序顺序插入的数据,都会令统计信息偏离实际数据应该具有的特征。 3. 如果索引没有得到使用,那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。 4. 强制使用索引用法将会导致两种可能:一是系统选择是正确的,使用索引实际上并不合适,二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时,这个时候EXPLAIN ANALYZE命令就很有用了。

PostgreSQL教程(十一):服务器配置 一、服务器进程的启动和关闭:下面是pg_ctl命令的使用方法和常用选项,需要指出的是,该命令是postgres命令的封装体,因此在使用上比直接使用postgres

PostgreSQL教程(十):性能提升技巧 一、使用EXPLAIN:PostgreSQL为每个查询都生成一个查询规划,因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用

PostgreSQL教程(九):事物隔离介绍 在SQL的标准中事物隔离级别分为以下四种:1.读未提交(Readuncommitted)2.读已提交(Readcommitted)3.可重复读(Repeatableread)4.可串行化(Serializable)然而PostgreSQL在9.1之

标签: PostgreSQL教程(八):索引详解

本文链接地址:https://www.jiuchutong.com/biancheng/319456.html 转载请保留说明!

上一篇:PostgreSQL教程(七):函数和操作符详解(3)

下一篇:PostgreSQL教程(十一):服务器配置

  • 契税减免税特殊规定有哪些?
  • 其他应收款期末余额为负数是什么意思
  • 小规模未达到起征点申报表怎么填
  • 本年利润借方余额意味什么
  • 财政补助资金是什么意思
  • 个人所得税累计收入
  • 作为一个财务 月末要做什么
  • 当月少计提工资下月怎么处理?
  • 收到现金货款可以直接用吗
  • 订单式生产管理
  • 库存现金日记账和库存现金总账都应逐日逐笔
  • 去国外参加展览的英文
  • 企业增加实收资本
  • 扶持资金属于政府补助吗
  • 商业保险税前扣除限额
  • 月末计提税金数据从哪得来的
  • 购进固定资产抵扣时咋填报增值税
  • 高新技术企业所得税税率
  • 质押贷款属于什么贷款
  • 实收资本记错账如何调整账面价值
  • 非房地产企业转让旧房土地增值税
  • 商业承兑汇票到期日付款人账户不足付款时
  • 典当行的账务处理会计分录大全
  • 电子商务平台怎么下载中标通知书
  • 结转出租设备的会计分录
  • 鸿蒙密码锁设置密码的方法
  • linux swap 0
  • 土地增值税怎么计算举例说明
  • 未办理装修手续
  • shwiconem.exe - shwiconem是什么进程 有什么用
  • linux怎样使用
  • logd是什么进程
  • 计提公积金账务处理需要什么凭证
  • 现金折扣税务处理shi
  • 开发票的时候补差价怎么处理?
  • 生活补助费能不能退回来
  • 资产评估增值是什么意思
  • php进行批量任务分类
  • 精读论文分析
  • 人工智能机器人保姆什么时候实现
  • 自动驾驶感知算法
  • php websocket教程
  • wordpress mobile themes
  • vue3父子组件通信
  • 结转各项成本的会计分录
  • 汇兑损益的种类
  • 营改增之前的工程还能开票吗
  • 企业收到海河工厂发运的乙材料,并验收入库
  • 企业消费税应计入资产成本的有哪些
  • 贷款用途不符合规定有啥危害
  • 企业间拆借资金是否合法
  • 企业债务重组业务
  • 商业承兑汇票托收什么意思
  • 增值税免征印花税怎么算
  • 进口关税,增值税是进口设备重置成本中的从属费用
  • 支持疫情的句子 关于抗击疫情的句子
  • 迟到扣发工资
  • 长期待摊费用的最新账务处理
  • 劳动关系与劳务关系的联系与区别
  • 城镇土地使用税减免税政策
  • 已经支付的预付款 银行能更改为货到付款吗
  • 启动mysql1067错误
  • windows下命令
  • windows7更新80072efe
  • 苹果电脑mac系统怎么升级
  • mac如何中文输入法
  • win7怎样解除无线连接限制
  • windows中复制文件的几种方法
  • win10累积更新卡在正在下载
  • jquery 动态添加列表元素
  • opencv是干嘛用的
  • android真机调试闪退
  • node .js
  • 配置命令提示符怎么打开
  • Javascript Throttle & Debounce应用介绍
  • jquery的选择器有哪几种类型
  • 国家税务局吉林省税务局官网app
  • 广西地方税务局2017年公务员拟录用
  • 百望税控盘电子发票开好了如何打印及导出发票
  • 姓名验证不正确
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设