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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
22天前
|
关系型数据库 Go 网络安全
go语言中PostgreSQL驱动安装
【11月更文挑战第2天】
60 5
|
2月前
|
SQL Oracle 关系型数据库
SQL语言的主要标准及其应用技巧
SQL(Structured Query Language)是数据库领域的标准语言,广泛应用于各种数据库管理系统(DBMS)中,如MySQL、Oracle、SQL Server等
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
存储 SQL 关系型数据库
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 12 章 全文搜索_12.9. GIN 和 GiST 索引类型
12.9. GIN 和 GiST 索引类型 有两种索引可以被用来加速全文搜索。注意全文搜索并非一定需要索引,但是在一个定期会被搜索的列上,通常需要有一个索引。 CREATE INDEX name ON table USING GIN(column); 创建一个基于 GIN(通用倒排索引)的索引。
1627 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.2. 索引类型
11.2. 索引类型 PostgreSQL提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。
1385 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.18. 对象标识符类型
8.18. 对象标识符类型 对象标识符(OID)被PostgreSQL用来在内部作为多个系统表的主键。OID不会被添加到用户创建的表中,除非在创建表时指定了WITH OIDS或者default_with_oids配置变量被启用。
1330 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.17. 范围类型
8.17. 范围类型 8.17.1. 内建范围类型 8.17.2. 例子 8.17.3. 包含和排除边界 8.17.4. 无限(无界)范围 8.17.5. 范围输入/输出 8.17.6. 构造范围 8.17.7. 离散范围类型 8.17.8. 定义新的范围类型 8.17.9. 索引 8.17.10. 范围上的约束 范围类型是表达某种元素类型(称为范围的subtype)的一个值的范围的数据类型。
1086 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.16. 复合类型
8.16. 复合类型 8.16.1. 复合类型的声明 8.16.2. 构造组合值 8.16.3. 访问复合类型 8.16.4. 修改复合类型 8.16.5. 在查询中使用复合类型 8.16.6. 复合类型输入和输出语法 一个复合类型表示一行或一个记录的结构,它本质上就是一个域名和它们数据类型的列表。
1036 0