跳到主要内容

数据表的索引

在介绍索引之前，我们需要先对数据库提取数据的逻辑，建立一个基本的了解，这样才可以帮助我们了解到，索引究竟是解决了什么方面的问题，以及如何安排索引。

PostgreSQL 提取数据的逻辑：由数据表构造数据表​

经过前面部分的学习，我们已经对关系型数据库的理论基础与基本实践，建立了一个基本的了解，可以看到，PostgreSQL 习惯于将数据组织成一张张形式各异的数据表加以管理，并且根据我们的查询请求，提取对应的数据行，并加以返回，整体的逻辑可以描述为如下的图片：

select

（从某种意义上面而言，这是一个使用既有数据表数据组合新数据表的过程）

举例来说，假定存在数据表 AtomGit_Issues 用于描述我们提报给 AtomGit 的 Issue 数据，如下所示：

CREATE TABLE "AtomGit_Issues" (
    title TEXT, /* 用于描述 Issue 的标题 */
    content TEXT, /* 用于存储 Issue 的内容 */
    author TEXT, /* 用于存储创建用户的名称 */
    labels TEXT[], /* 用于存储 Issue 所对应的标签  */
    status INTEGER, /* 用于描述 Issue 的状态（开启/关闭）  */
    id INTEGER, /* 用于存储 Issue 的 ID */
    add_time DATE DEFAULT CURRENT_DATE /* 存储创建 Issue 的时间 */
);
提示

可以使用数组在同一列中存储多个元素
对于类似于 "#C语言 #PostgreSQL #SQL" 这样的一个个标签形式的数据，PostgreSQL 提供了数组，以便我们将它们存储在同一个数据列之中。

提示

对于存在大写字母的数据表名称等，对名称加上 "" 更为合适
在没有书写 "" 包裹字符串时，PostgreSQL 会将 CREATE TABLE 的数据表名中的大写字母转换为小写，如我们书写 CREATE TABLE Books 时，将会与 CREATE TABLE books 等价（但是如果我们打上 CREATE TABLE "Books"，将不会存在这个问题）。
在不注意到这个特性的情况下，将会造成许多奇怪的情况，因此必须留心。

之后，我们可能会按照某种条件，搜索这张数据表中的数据，如图所示：

search

可以发现，最终呈现的结果数据，从某种意义上来说，实际上就是 Issue 数据表的一个子集，因此，如果我们可以设法提升构造结果表的速度的话（提升查询的速度），我们自然而然地也就能够缩短整体流程的时间，进而能够带来更好的用户体验。

索引就是我们可以选择采取的一种手段，它会按照我们的要求，采取一定的策略，将整体的数据表中的数据，提炼成更小的数据片段，进而提升查询的速率，最后缩短流程所需要的时间（小数据片段的读写效率往往高于大数据片段）。

index

（索引从某种意义上面看，就是被精心挑选出来的数据列，用以在某种途径上面帮助我们更快地找到我们所需要的信息）

创建索引以优化性能​

现在，我们对于索引的基本原理已经有了一定的了解，现在，我们需要将目光聚焦于 PostgreSQL 的查询计划上面来，他代表着 PostgreSQL 访问数据表数据的方法，对于尽量优化我们查询的性能，具有良好的指导意义。

现在，让我们从一个简单的案例开始，设立一张简单的 number 表用于存储数字：

CREATE TABLE number (
    number INTEGER
);
/* 使用 generate_series 函数向其中插入900万条记录 */
INSERT INTO number SELECT * FROM generate_series(1, 9000000);
提示

使用 generate_series 函数构建数据序列
函数承担着某种功能职责，能够根据我们所输入的数据展开一定的工作，而 PostgreSQL 内置的 generate_series 函数就可以构建数据序列来，如在这里，我们使用 generate_series 函数构建了 900 万条记录（generate_series 函数将会返回从1到9000000的每一个数字，自小到大返回）。
我们同样可以指示每一次累加的数字，如 SELECT generate_series(1, 6, 2); 将会返回 1, 3, 5 这组序列来。 注意：PostgreSQL 函数的返回最终都将呈现为关系型数据，这是 PostgreSQL 本身所依托的关系型数据理论使然。

之后，我们可以尝试使用在这张数据表中查询某个特定的数字，如 4000000（四百万）：

/* 在 psql 中，使用 \timing 可以打开 SQL 查询执行时间统计 */
SELECT * FROM number WHERE number = 4000000;

psql

可以发现，相较于之前实验我们所做成的迅速创建成功，这一次的查询，无论是数据的插入还是数据的检索，都消耗了不少的时间，但是倘若我们为数据表创建一份索引：

/* CREATE INDEX on 数据表 (数据列) */
CREATE INDEX on number (number);
提示

数据表索引的数据与数据表数据是分离存储的
在 PostgreSQL 中，出于灵活性的目的，索引的数据与数据表数据在物理层面上面是分离存储的（这种隔离性提供了自由施展的空间），索引根据对应的场景（如某种操作符号，某种组合条件）针对特定的数据列（这里请回顾主键的有关知识）展开辅助查询的工作，进而促进查询效率的提升（这就和那些为航船指路的明灯一样）。
但也正是因此，索引也带来了额外的数据存储空间消耗，以及更为高昂的数据维护成本（表数据的更新往往也意味着索引数据的更新），这点在数据规模庞大的情况下更为明显。
因此是否使用索引，使用哪种类型的索引都是一门需要琢磨的学问，都需要不断积累并展开细致的分析。

并再一次执行同样的查询，就会发现，查询的效率得到了一个明显的提升：

index-result

（查询的时间由 4310.888 毫秒降低到 3.187 毫秒，后者的查询时间仅仅为前者的7.39%，可见这项工作的成效）

不过，我们这里所讨论的索引创建，依旧只是整个庞大体系中的冰山一角，现在，我们将就如何直观了解这背后的工作，做一个讨论，即如何去学习 PostgreSQL 的查询计划。

PostgreSQL 查询计划简介：SQL 查询展开工作的总布局​

想要理解 PostgreSQL 的查询计划，我们就应当对 PostgreSQL 所依托的查询优化思想，乃至于 SQL 语言本身的一些指导思路，建立一个更为深刻的了解。

提示

SQL 是一门声明式编程语言
声明式语言的特点在于关注执行的结果而不关心执行的过程，具体的体现就是，我们所书写的 SQL 代码，都是某种对于数据表的操作以及对于查询结果的一种指导，而具体的实践过程，如物理文件的读写，都交由 PostgreSQL 负责而不需要我们过多干涉。
这种声明式语言的好处在于极度简化了查询数据表的流程，坏处则在于查询效率的高低，以及很多操作的细节部分完全取决于数据库内核实现的好坏（这就是为什么对于同样的 SQL 代码，不同的数据库所需要的执行时间，占用的资源均存在一定的差异）。
而查询优化所关注的重点，就在于促进数据库内核，使其能够在尽量短的时间内把全部工作完成（注意：可能会采取以空间换时间的指导策略），由此也产生了许多经典的模型（PostgreSQL 就采用了火山模型）。

在文章 《The Volcano Optimizer Generator: Extensibility and Efficient Search》 中，提出了一种为查询优化设计出来的模型，即后面广为人知的 Volcano（火山）模型，其基本的流程如下所示：

volcano-model

在这种模型的指导下，数据库的操作自总体而看，被划分为了逻辑层与物理层，分别对应特定的算法与具体的安排（如在内存中排序数据以及读写数据文件，实际上就分属于不同的层次），每一种操作，又分别对应不同的“操作符”（如我们可以为全表扫描安排一个对应的操作符），而我们目前所关注的重点，就围绕在逻辑的层面上，即数据表究竟是按照怎样的逻辑被提取出数据，并构建最终的结果的（实际上真实的模型更为复杂，涉及到代价的评估，选择率等一系列操作，这里，出于简化的目的，我们省去相关的讨论）。

这就需要依托 EXPLAIN 指令，现在让我们执行如下的代码：

/* 让 PostgreSQL 给出对应查询的查询计划  */
EXPLAIN SELECT * FROM number WHERE number = 4000000;

explain

可以发现，PostgreSQL 反馈出了对应的查询计划（Query Plan），而想要理解清楚对应的信息，在之前的模型之上，我们还需要了解如下的概念：

  • 代价（Cost）
    就像我们使用货币统一衡量各种商品的价值一样，在火山模型中，代价被应用于衡量各种操作所占用的资源（想想看为什么不直接使用时间单位？因为不同的操作所涉及到的硬件资源不一样，如对于索引的扫描，可能就需要更多考虑到 CPU 资源，如对于整张表即全表数据的提取，I/O 资源就要考虑得更多一些），而 PostgreSQL 应当尽量构建出代价较低的查询计划来。
  • 节点（Node）
    在 PostgreSQL 中，采用“节点”对各种不同的操作进行统一的管理，同时各个节点会按照执行的先后顺序，组合形成为一棵树（PostgreSQL 中的原始语法树，查询解析树，查询计划等，均是按照“树”的思想进行组织与管理）。
    每一种节点代表了一种对于数据的处理方式与管理方式，种类繁多，因此常常需要我们具体情况具体分析。
  • 扫描（Scan）
    扫描即代表着逐行自数据表中提取数据，而提取数据所选择的数据源，读取数据表的顺序，乃至于其他的细节，取决于具体选择的节点（Nodes）。

之后，我们就可以正式对其中的内容展开解读了，可以看到，查询计划指出：

  1. 采行 Index Only Scan（仅索引扫描），借助（using）关系 number 上面的 number_number_idex 索引对数据表展开扫描（对应上我们所书写的 SELECT, FROM 等子句）
  2. 扫描的限制条件（Cond，即条件 condition 的缩写），是 number = 4000000（对应我们所书写的 WHERE）
  3. PostgreSQL 对 “Index Only Scan” 的总体评估是，起步代价是 0.43 （即事先准备工作），总体代价是 4.45，而最终所提取得到的数据行为 1 行，而输出本数据行所需要的最小字符数为 4（不过从我的实际经历来看，这个数字往往不需要关心，因为不是很准确）

而如果我们希望看到更加直观的信息的话（以及更为直观的查询计划的结构的话），可以改变 EXPLAIN 输出的格式，如 Yaml，参考如下：

EXPLAIN (FORMAT YAML) SELECT * FROM number WHERE number = 4000000;

explain-yaml

可以发现，内容明显比默认的输出格式更加容易理解（同理，你可以尝试调整输出格式为 Json）

现在，相信读者已经对存在索引情况下面的扫描，有了一个基本的了解，让我们做一个比较，首先，删除 number 表上面的索引：

/* DROP INDEX 索引名称 */
DROP INDEX number_number_idx;
提示

在 psql 中，使用 \d 了解一个数据表上所存在的索引
一般而言，我们并不会刻意去记住各个索引的名称，因此即插即用就成为了一种好的选择，使用 \d + 数据表 可以很快的帮助我们了解到一个数据表的结构以及其配套的索引，如在这里的案例之中，可以使用 \d number 展开相对应的工作。

psql_number

可以发现，psql 在告诉我们索引名称以及对应数据列的情况下，还提示了我们索引的类型，这是我们后续将要介绍的内容。

言归正传，让我们再一次使用 EXPLAIN 对应的查询计划：

EXPLAIN SELECT * FROM number WHERE number = 4000000;

explain-select-after-drop

可以发现，节点的类型调整为了 “Gather”，代表采用了并行查询方式进行管理（即同时使用多个进程处理数据），工作进程被规划为2个，每一个都对数据表 number 采用了 “并行序列扫描” 方式（对应 SELECT），过滤条件被设定为 numbe = 4000000（再一次对应之前的 WHERE）

提示

善用 PostgreSQL 文档用以了解节点的工作方式
PostgreSQL Node 的类型多种多样，往往需要我们去在 PostgreSQL 文档中搜索以了解其具体的用途，如我们可以尝试搜索 Gather 节点：

search-node

可以发现，文档当中对于节点的描述往往是嵌入在某些讨论某项技术操作的文章之中，而且根据版本的不同指向对应的链接（注意到链接/docs/后面所跟的版本号了吗？），这就需要我们结合具体的场景，选择阅读哪一个版本的文档。

同时，我们可以注意到，这一次查询所需要的启动代价被大幅度提升到了 1000，而总体代价则提升到了87699（可以使用 YAML 格式，让结果更为直观），查询所需要的时间大幅度上升，可以发现，对于同样查询所构建出的不同查询计划，直接就影响到了最终的用户体验，学习如何编写合适的 SQL 语句，优化数据表的结构，设计索引，都很大程度上考验着我们对于 PostgreSQL 的理解与积累。

提示

使用 EXPLAIN ANALYZE 查看真实执行的情况
EXPLAIN 所展现出来的查询计划，依旧是一个模拟的查询计划，同真实的执行而比，依旧是存在一定的误差，而如果我们想要了解真实环境的执行的话，可以加上 ANALYZE 使得 PostgreSQL 真实地执行对应的查询（与之对应的，INSERT，DELETE，UPDATE 将会真实地改变数据表的数据，这点尤其需要注意）。
出于实验的目的，一种好的做法就是将 EXPLAIN ANALYZE 包裹在一个 BEGIN...ROLLBACK 语句块里面，如：

BEGIN;
EXPLAIN ANALYZE SELECT * FROM number;
ROLLBACK;

进而使得 EXPLAIN ANALYZE 所造成的影响可以被撤销，保持真实性与所造成影响的一个平衡。

PostgreSQL 索引类型简介​

在前文中，我们了解到，默认情况下面，使用 CREATE INDEX 将会为数据表的数据列创建 Btree 的索引，这种类型的索引，将会把参考使用的索引数据组织为 B-Tree 的形式。

假定我们此刻的数据表如下：

 number 
--------
     20
     30
     40
     50
     60
     70
     80
(7 rows)

而按照顺序扫描的办法搜索数字 80 的话， 我们往往就需要诸行搜索，不幸的是，80 在整张数据表的最后面，由此就需要经过7次比较。

采用 Btree 索引的话，则数据将会被组织为如下的形式（图中展现了一种可能的情况，具体则取决于实现）：

Btree

而再一次展开搜索的话，我们就可以使用二分方法，扫描 50 （发现 80 > 50，选择右子树），扫描 70，发现（80 > 70，选择右子树），比较返回 80。可以发现，扫描次数下降到了3次，时间开销明显下降。

而其他的索引类型，往往会将数据组织为其它的形式以供参考，Btree 索引非常适合于比较数字大小等场景，但是倘若我们期待稳定的搜索效率（即时间不因数据在索引的位置而发生巨大的改变）的话，Hash 索引显然是一种更好的选择，它会按照一定的算法，将数据表中的数据映射到一张名为 “Hash Table”（哈希表）的数据结构上面来，这样在搜索的时候，只需要依照同样的流程，就可以直接在哈希表中“对号入座”式地提取到对应的数据，进而快速搜索到对应的数据，但是也正是因此，哈希索引无法对涉及排序的场景，提供良好的支持。

hash

让我们继续沿用先前的案例，这一次，我们再一次创建 number 表，同时再一次插入 900 万条数据，并展开查询解析的工作：

DROP TABLE number;
CREATE TABLE number (
    number INTEGER
);
INSERT INTO number SELECT * FROM generate_series(1, 9000000);
EXPLAIN ANALYZE SELECT number FROM number WHERE number = 4000000;

在笔者的电脑上，PostgreSQL 反馈如下：

explain-no-index

之后，我们为 number 属性列加上 Btree 索引：

/* 使用 USING 指定对应的索引类型 */
CREATE INDEX ON number USING BTREE (number);
/* 再一次展开搜索 */
EXPLAIN ANALYZE SELECT number FROM number WHERE number = 4000000;

可以发现，搜索时间明显缩短：

explain-btree-index

而现在，让我们删除既有 Btree 索引，改为 Hash 索引：

DROP INDEX number_number_idx;
CREATE INDEX ON number USING HASH (number);
EXPLAIN ANALYZE SELECT number FROM number WHERE number = 4000000;

可以发现，搜索时间相较于 Btree，变得更短，体现出 Hash 索引在 = 为代表的搜索上能力强于 Btree 的特性：

explain-hash-index

但如果我们让其执行搜索操作，可以发现，扫描的方式再一次回归到了顺序扫描，而没有借助索引，时间显著变长：

explain-hash-index-2

所以这就充分提醒了我们，每一种类型的索引都有自己的特点与应用场景，需要多做了解，才可以进行采用，不然只会是徒增存储空间（想一想如果这张表非常庞大，数据量达数百G的话，配套的 Hash 数据将会达到多少？而因为涉及比较大小的场景 Hash 索引无能为力，这就是巨大的资源浪费）。

PostgreSQL 其它类型的索引可以参考对应的文档部分 《Index Types》，我们非常欢迎读者依靠自己的主动学习来对丰富多样的索引类型建立理解。