PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.8. 部分索引

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 11.8. 部分索引 一个部分索引是建立在表的一个子集上,而该子集则由一个条件表达式(被称为部分索引的谓词)定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性,但在很多种情况下它们也很有用。

11.8. 部分索引

一个部分索引是建立在表的一个子集上,而该子集则由一个条件表达式(被称为部分索引的谓词)定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性,但在很多种情况下它们也很有用。

使用部分索引的一个主要原因是避免索引公值。由于搜索一个公值的查询(一个在所有表行中占比查过一定百分比的值)不会使用索引,所以完全没有理由将这些行保留在索引中。这可以减小索引的尺寸,同时也将加速使用索引的查询。它也将加速很多表更新操作,因为这种索引并不需要在所有情况下都被更新。例 11.1展示了一种可能的应用:

例 11.1. 建立一个部分索引来排除公值

假设我们要在一个数据库中保存网页服务器访问日志。大部分访问都来自于我们组织内的IP地址,但是有些来自于其他地方(如使用拨号连接的员工)。如果我们主要通过IP搜索来自于外部的访问,我们就没有必要索引对应于我们组织内网的IP范围。

假设有这样一个表:

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

用以下命令可以创建适用于我们的部分索引:

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

一个使用该索引的典型查询是:

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

一个不能使用该索引的查询:

SELECT *
FROM access_log
WHERE client_ip = inet '192.168.100.23';

可以看到部分索引查询要求公值能被预知,因此部分索引最适合于数据分布不会改变的情况。当然索引也可以偶尔被重建来适应新的数据分布,但是这会增加维护负担。


例 11.2展示了部分索引的另一个可能的用途:从索引中排除那些查询不感兴趣的值。这导致了上述相同的好处,但它防止了通过索引来访问不感兴趣的值,即便在这种情况下一个索引扫描是有益的。显然,为这种场景建立部分索引需要很多考虑和实验。

例 11.2. 建立一个部分索引来排除不感兴趣的值

如果我们有一个表包含已上账和未上账的订单,其中未上账的订单在整个表中占据一小部分且它们是最经常被访问的行。我们可以通过只在未上账的行上创建一个索引来提高性能。创建索引的命令如下:

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

使用该索引的一个可能查询是:

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

然而,索引也可以用于完全不涉及order_nr的查询,例如:

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

这并不如在amount列上部分索引有效,因为系统必须扫描整个索引。然而,如果有相对较少的未上账订单,使用这个部分索引来查找未上账订单将会更好。

注意这个查询将不会使用该索引:

SELECT * FROM orders WHERE order_nr = 3501;

订单3501可能在已上账订单或未上账订单中。


例 11.2也显示索引列和谓词中使用的列并不需要匹配。PostgreSQL支持使用任意谓词的部分索引,只要其中涉及的只有被索引表的列。然而,记住谓词必须匹配在将要受益于索引的查询中使用的条件。更准确地,只有当系统能识别查询的WHERE条件从数学上索引的谓词时,一个部分索引才能被用于一个查询。PostgreSQL并不能给出一个精致的定理证明器来识别写成不同形式在数学上等价的表达式(一方面创建这种证明器极端困难,另一方面即便能创建出来对于实用也过慢)。系统可以识别简单的不等蕴含,例如x < 1蕴含x < 2;否则谓词条件必须准确匹配查询的WHERE条件中的部分,或者索引将不会被识别为可用。匹配发生在查询规划期间而不是运行期间。因此,参数化查询子句无法配合一个部分索引工作。例如,对于参数的所有可能值来说,一个具有参数x < ?的预备查询绝不会蕴含x < 2

部分索引的第三种可能的用途并不要求索引被用于查询。其思想是在一个表的子集上创建一个唯一索引,如例 11.3所示。这对那些满足索引谓词的行强制了唯一性,而对那些不满足的行则没有影响。

例 11.3. 建立一个部分唯一索引

假设我们有一个描述测试结果的表。我们希望保证其中对于一个给定的主题和目标组合只有一个成功项,但其中可能会有任意多个不成功项。实现它的方式是:

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

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

当有少数成功测试和很多不成功测试时这是一种特别有效的方法。


最后,一个部分索引也可以被用来重载系统的查询规划选择。同样,具有特殊分布的数据集可能导致系统在它并不需要索引的时候选择使用索引。在此种情况下可以被建立,这样它将不会被那些无关的查询所用。通常,PostgreSQL会对索引使用做出合理的选择(例如,它会在检索公值时避开索引,这样前面的例子只能节约索引尺寸,它并非是避免索引使用所必需的),非常不正确的规划选择则需要作为故障报告。

记住建立一个部分索引意味着我们知道的至少和查询规划器所知的一样多,尤其是我们知道什么时候一个索引会是有益的。构建这些知识需要经验和对于PostgreSQL中索引工作方式的理解。在大部分情况下,一个部分索引相对于一个普通索引的优势很小。

关于部分索引的更多信息可以在[ston89b][olson93][seshadri95]中找到。

本文转自PostgreSQL中文社区,原文链接:11.8. 部分索引

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
关系型数据库 Go 网络安全
go语言中PostgreSQL驱动安装
【11月更文挑战第2天】
91 5
|
4月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
117 1
|
7月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
3月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
4月前
|
SQL 存储 关系型数据库
PostgreSQL核心之SQL基础学习
PostgreSQL核心之SQL基础学习
61 3
|
4月前
|
SQL 安全 关系型数据库
PostgreSQL SQL注入漏洞(CVE-2018-10915)--处理
【8月更文挑战第8天】漏洞描述:PostgreSQL是一款自由的对象关系型数据库管理系统,支持多种SQL标准及特性。存在SQL注入漏洞,源于应用未有效验证外部输入的SQL语句,允许攻击者执行非法命令。受影响版本包括10.5及更早版本等。解决方法为升级PostgreSQL
318 2
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
511 0
|
4月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
349 0
|
6月前
|
SQL 关系型数据库 数据库
nacos 2.2.3版本 查看配置文件的历史版本的接口 是针对MySQL数据库的sql 改成postgresql后 sql语句报错 该怎么解决
在Nacos 2.2.3中切换到PostgreSQL后,执行配置文件历史版本分页查询出错,因`LIMIT 0, 10`语法不被PostgreSQL支持,需改为`LIMIT 10 OFFSET 0`。仅当存在历史版本时报错。解决方案是调整查询SQL以兼容PostgreSQL语法。
|
6月前
|
SQL 关系型数据库 PostgreSQL
【sql】PostgreSQL物化视图表使用案例
【sql】PostgreSQL物化视图表使用案例
70 0