接着上一篇，和同事共同翻译这篇 PostgreSQL 推广文章的第二部分。

【译】PostgreSQL优于其他开源数据库的特性：Part II

译者

朱智武 浙江移动DCOS工程师
谭峰（francs） 浙江移动PG数据库专家

作者

原文作者：Lisa Smith
作者博客：https://www.compose.io/articles/author/lisa-smith/

PostgreSQL的宣传口号声称它是“全世界最先进的开源数据库”。在本系列Part I我们介绍了存储数据，包括数据模型、数据结构、类型、大小限制，给出了一些PostgreSQL为何如此声称的理由。在Part II，我们将介绍数据操作和检索，包括索引、虚拟表特性和查询能力。

索引

PostgreSQL提供其他开源数据库所不具备的索引功能。PostgreSQL除了标准索引类型之外，还支持局部、表达式、GiST、GIN索引。我们来看上述这些特殊索引。

局部索引

当你仅仅想为一张表的子集添加索引就可以创建局部索引（Partial Indexes），比如某列的值符合一个特定条件的所有行。这个有利特性让你保持合理的索引大小，并达成提高性能和减少磁盘空间的目标。局部索引的一个关键是被索引的列可以与提供子集约束条件的列不同。比如，你可能只想索引那些支付客户的帐号而不包括为内部测试而创建的帐号。

说明重要的一点，有时候MySQL的局部索引（Partial Indexes有时也被翻译为部分索引）术语用来指截取被索引的列值至一定数量的字节数，而不是基于一个条件去限制被索引行的数量。我们这里描述的局部索引MySQL不支持。

表达式索引

创建表达式索引用来索引通过函数预计算得到的一个列。这些新值在查询时被索引和对待如同常量，而不是查询每次运行时需要重新计算。举一个例子，如果你有一个网页点击日志，采集他们接收的任何格式URL点击，你可能想创建一个基于小写的标准URL的索引（PostgreSQL是大小写敏感的，compose.io和Compose.io会被认为是不同的结果）：

GIST和GIN

GiST（Generalized Search Tree）允许联合B树、R树和用户自定义索引类型来创建拥有先进查询能力的定制索引。GiST在PostGIS（从2015年1月以来我们所有PostgreSQL部署的标配）和OpenFTS（一个开源全文搜索引擎）中使用。PostgreSQL也支持SP-GiST，它允许使数据检索异常快速的分区查找索引的创建。

GIN（Generalized Inverted Index）可以索引复杂数据类型。复杂数据类型允许你以不同方式联合其他数据类型来创建完全定制化的数据类型。查看本系列的Part I以概览复杂数据类型。
创建GiST和GIN索引的语法是，CREATE INDEX .. ON .. USING GIST|GIN ..。简单！
在PostgreSQL 9.5（译者注：目前处于beta 2），BRIN（Block Range Index）将被支持。BRIN允许基于被索引的列将大表打散为一系列范围。这意味着查询计划只需要扫描查询所限定的某一个范围。此外，范围索引所需要的磁盘空间大小比标准B树索引要小很多。

对比

我们关注的其他SQL数据库在表达式索引上正在缩小差距。在MySQL 5.7.6，生成列（Generated Column）开始被支持，可以用作表达式索引。对于MariaDB，虚拟列（Virtual Column，也成为生成列或计算列）在版本5.2中开始支持，但仅支持使用内置函数创建列（无法使用用户自定义函数）。Firebird的2.0版本，使用计算列（Computed Column）的表达式索引开始被支持。然而，这些数据库不支持局部、GiST或GIN索引。

当创建索引并希望去分析它们的性能时，别忘记去阅读mySidewalk的Matt Barr书写的技术文章Simple Index Checking with PostgreSQL。

虚拟表特性

虚拟表在很多查询中是必需的。我们对比过的所有SQL数据库提供一些虚拟表功能，PostgreSQL提供了更多。

通用表表达式和递归

PostgreSQL通过WITH子句支持通用表表达式（Common Table Expression，CTE）。我们在技术文章PostgreSQL – Series Random and With中展示过该特性。通用表表达式使你在查询语句以内联方式创建虚拟表，逻辑上表达一系列操作的顺序，这相比在其他地方使用子查询创建虚拟表更容易阅读和保证质量。PostgreSQL中的通用表表达式可以递归使用。这个方便的功能使你单步遍历一个层次结构，语句重复自我引用直到没有数据被返回。这是一个递归通用表表达式的例子，在一个话题分类中标识了层级、话题、父子关系：

MySQL和MariaDB不使用WITH子句，所以，并不正式支持通用表表达式。这些数据库中可以使用子查询创建衍生表，然而它们并不允许递归。Firebird这方面比MySQL和MariaDB好，与PostgreSQL一样支持使用WITH子句的通用表表达式并提供递归功能。

物化视图

物化视图是另一项PostgreSQL支持的实用的虚拟表特性。物化视图就像普通视图那样代表一个经常使用的查询结果集，只是结果集像一个普通表那样存储在磁盘上。物化视图也可以添加索引，不像普通视图每次请求时重新生成，物化视图是及时的快照。它们只在特定时刻刷新。这可以极大地加快使用物化视图的查询的执行速度。无需在查询中使用普通视图或做复杂表关联或运行聚合函数，使用一个包含所需数据在磁盘的物化视图可以提高效率。当你在一个物化视图中更新数据，可以按需使用REFRESH命令。这是一个物化视图的例子，生成聚合收益数据：

Firebird、MySQL和MariaDB并不支持物化视图，但可以使用一种变通方案，创建一张普通表并使用存储过程或者触发器更新它。

查询能力

PostgreSQL的查询功能是丰富的。前面章节讨论了WITH子句，现在来看SELECT语句中使用的另外两个可选特性。

集合查询

PostgreSQL提供UNION、INTERSECT和EXCEPT子句用于SELECT语句之间的交互。UNION将第二个SELECT语句的结果附加到第一个。INTERSECT返回两个SELECT语句均有的行。EXCEPT返回第一个SELECT语句有而第二个SELECT语句没有的行。我们看一个使用EXCEPT的例子，该语句返回客户联系信息除非客户一周内已经收到并回复邮件。

MySQL、MariaDB和Firebird都支持UNION，但都不支持INTERSECT和EXCEPT。然而，通过查询中的关联以及EXISTS条件，可以获取与PostgreSQL相同的结果集。当然，这会使查询变得更为复杂。

窗口函数

窗口函数基于结果集的部分行（一个子集一个窗口）运行聚合函数，极其有用。实质上，它遍历与当前行有关的分区中的所有行，运行该函数。常用函数包括ROW_NUMBER()、RANK()、DENSE_RANK()和PERCENT_RANK()。关键词OVER，与PARTITION BY和ORDER BY一起，指示使用一个窗口函数。举一个例子，在下面的章节“函数及其他”，我们使用一个窗口函数ROW_NUMBER() OVER来确定一系列数值的中位数。注意WINDOW子句并不是必需的，只是用来创建和命名窗口以帮助保持条理。

Firebird、MySQL和MariaDB现阶段不支持窗口函数，虽然窗口函数几年前就在Firebird 3的支持计划中宣布。

横向子查询（Lateral Subquery）

在FROM子句中关键词LATERAL可以作用于子查询，允许子查询和之前创建的其他表或虚拟表之间做交叉引用。查询语句如此可以更为简化。它的工作方式是每一行与交叉引用的表作衡量，这意味着查询语句执行的速度加快。这里是一个例子，我们想要一个学生列表以了解他们最近是否阅读面向技术的话题：

MySQL、Firebird和MariaDB现阶段不支持横向子查询（Lateral Subquery）。同样地，存在变通方案，但是查询语句将变得更为复杂。

另一件事需要说明，MySQL和MariaDB不支持完全外连接，但一个使用UNION ALL的变通方案可以用来合并两张表的所有行。

函数及其他

PostgreSQL提供健壮的内置操作符和函数，包括那些支持本系列Part I里特定数据类型，但你可以创建自己的操作符和函数（包括聚合函数），如同定制的存储过程和触发器。我们无法提及所有这些细节，因为内容过多，但我们可以看函数相关的两个简单例子。

PostgreSQL支持4种用户自定义函数：查询语言、过程语言、C语言和内部语言。每一种都可以传入和返回基础和复杂类型。注意在PostgreSQL中CREATE FUNCTION命令不仅可以创建函数也可以创建存储过程。

让我们看一个例子，创建一个返回复杂类型的函数：

这是一个实用的定制函数，用来找到一个数值序列中的中位数：

我们用来对比的其他开源SQL数据库也允许创建自己的函数、存储过程和触发器，但它们没有PostgreSQL提供的那么丰富的数据类型和自定义选项。额外的，在PostgreSQL你可以创建自己的操作符。其他数据库并不支持用户自定义操作符。

语言扩展

PostgreSQL拥有大量的语言扩展，一些是发行版的一部分，更多的是第三方。

在Compose，我们仅支持可信任的PostgreSQL语言扩展，以保证你的部署是安全的。我们在二月重新支持PL/Perl，并在八月支持PL/v8，一个基于Javascript的过程语言。这些语言扩展，比基于SQL的PL/pgSQL语言（Compose的部署同样可以使用）拥有更多内置函数，使你可以创建复杂脚本来操作和处理服务器上的数据。

更多

PostgreSQL刚刚宣布了9.5版本的Beta 1发行版（译者注：目前已发布9.5 Beta 2）。我们已经着手于它，学习所有新特性以便使9.5版本一旦稳定就第一时间提供出来。在过去几个月我们查看了9.5带来的几个特性，比如前面提及的BRIN索引。 阅读 PostgreSQL’s Future Is Looking Up-sert和Beyond Upsert – Coming in PostgreSQL 9.5以预览即将到来的9.5。

总结

PostgreSQL有丰富的内置特性和大量的方式可以定制或扩展来满足需求。另外，它是可靠和成熟的，这是一个值得任何企业致力于的数据库解决方案。即便如此，它仍对刚起步的开发项目保持易用性和高效性。

我们仅仅涉及了少数PostgreSQL不同于其他开源SQL数据库的功能，还有更多的其他功能未涉及（在9.5版本还将带来更多）。我们希望这两篇文章能提供一个为什么选择PostgreSQL的坚实概述。

阅读原文

• What PostgreSQL has over other open source SQL databases: Part II