基于主键的索引扫描，Postgres 和 Oracle 一定比 MySQL 差吗？-51CTO.COM

介绍

在文章索引扫描类型：位图、索引和只用索引中，我们回顾了 PostgreSQL 使用索引尽快检索数据的三种方式。在同一篇文章中，我们看到了最快、最有效的方法是 Index-Only Scan，因为它通过避免对堆（表页）的第二次读取，并仅从对索引的单次读取，获取查询请求的所有数据来节省时间。

根据定义，利用只用索引的扫描的查询，表现比其他方法更快，但在关联到事务量时，其优势更加明显。您的系统产生的查询工作负载越多，在进行只用索引的扫描时获得的好处就越多。

业务负载测试

为了更清楚地看到这一点，我们继续使用文章中的同一个表，这次有 500K 行。我们将执行以下查询：

SELECT age, register_date, is_active
FROM person
WHERE age = <int>;

我们将首先使用 age 列上的常规索引运行它，它必须执行索引和表读取，以检索 register_date 和 is_active 列的数据。然后，我们将使用覆盖索引进行再次练习，该索引是在 age 列上声明的，但包括了其他列。

第一个索引如下所示：

CREATE INDEX idx_person_age ON person(age);

第二个索引（覆盖索引）定义如下：

CREATE INDEX idx_person_age_cover ON person(age) INCLUDE (register_date,is_active);

为了验证结果的一致性，我们将使用不同数量的并发用户：20、100 和 200，来运行负载。结果绘制在下图中：

图片

我们在所有三种情况下都得到了一致的结果。在所有这些结果中，利用只用索引的扫描的业务负载每秒执行的事务数（TPS），大约是使用索引扫描的业务负载执行的 2.25 倍。

只用索引扫描的要求

对大多数业务负载使用覆盖索引或复合索引，看起来是个好主意。但是，正如我们在文章中所看到的，必须满足一些条件才能获得这些好处：

• 索引类型必须支持只用索引的扫描方法。B 树索引总是支持的；GiST 和 SP-GiST 支持对某些运算符进行只用索引的扫描操作，但不是全部，并且其他任何索引类型都不支持只用索引的扫描。
• 查询必须只获取索引覆盖的列。在前面的测试中，查询要求提供 age、register_date 和 is_active 列，这些列包含在覆盖索引中。如果 SELECT 语句中有任何其他列，则规划器不会选择只用索引的扫描。
• PostgreSQL 扫描必须确保检索到的行对查询的快照是“可见的”。

可见性条件

前两个条件很容易理解；如果我们使用支持 Index-Only Scan 功能的索引类型，并且我们的查询只获取索引覆盖的列，那我们就已经准备好了。但是，第三个条件需要格外小心，以确保我们能获得好处。让我们展开来看下第三个条件。

只用索引的扫描的目标是，避免对表页（堆）的第二次读取访问，以节省该访问时间，并直接从索引中检索数据。尽管如此，PostgreSQL 必须确保索引数据与堆中的数据保持同步。

可见性信息不存储在索引中；它属于表页，因此乍一看表读取是不可避免的。但是，有一个解决方法：可见性映射表。如果给定页面中的所有行都足够老，对当前和未来的事务都可见，则会在可见性映射表中标记该页面的状态位。

从可见性映射表来验证行页面的可见性，比读取堆表的成本要低得多，因为它很小并且大部分时间都在缓存中。

换句话说，表中的数据更改越多，它在映射表中的可见性比率就越低。因此，为了确保我们能从只用索引的扫描功能中受益，我们需要验证该条件。

如何检查表的页面可见性

如 PostgreSQL 文档中所述，可见性映射表为每个表页仅存储两个位，一个用于指示可见性，另一个用于标记行是否已冻结。此文件不能作为一个传统文件进行检查；幸运的是，我们可以依靠 pg_visibility 扩展来实现此目的。

该扩展随任何基本 PostgreSQL 安装的 contrib 模块一起提供，因此要将其添加到数据库，直接执行下面语句即可：

CREATE EXTENSION pg_visibility;

使用与前面示例相同的表，我们可以按如下方式，来检查表 person 的页面：

SELECT * FROM pg_visibility_map('person');
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | t
     1 | t           | t
     2 | t           | t
[truncated…]
 10202 | t           | t
 10203 | t           | t
 10204 | t           | t
(10205 rows)

因此，我们可以看到为每个表页面的两个位设置的值，可以是 false 或 true。

一旦表开始在其数据中执行更新，相应页面的可见性状态位就会发生变化。例如，通过以下内容，我们可以知道 id 为 10000 的行所在的页面（blkno）：

SELECT ctid, id FROM person WHERE id = 10000;
  ctid   |  id
---------+-------
 (204,4) | 10000
(1 row)

因此，我们可以检查第 204 页的可见性映射信息，然后更新该行并再次检查，如下：

SELECT * FROM pg_visibility_map('person')
  WHERE blkno = 204;
 blkno | all_visible | all_frozen
-------+-------------+------------
   204 | t           | t
(1 row)

UPDATE person SET register_date = now() WHERE id = 10000;

SELECT * FROM pg_visibility_map('person')
  WHERE blkno = 204;
 blkno | all_visible | all_frozen
-------+-------------+------------
   204 | f           | f
(1 row)

更新 id 为 10000 的行中的值后，可见性映射表中相应页面的 all_visible 列显示为 false。

随着不断针对表进行更新操作，可见性映射表将发生变化，标记为 all_visible 的页面数量将减少。

检查可见性百分比

了解可见性映射表变化的原因以及如何验证其内容后，我们可以检查可见页面的百分比，从而保持只用索引的扫描功能正常工作。

以下查询将显示标记为 all_visible 或 no_visible 的页面的百分比：

SELECT
    x.pages::text,
    round(avg(x.percent), 1)::int AS percentage
FROM (
    SELECT
        CASE WHEN all_visible THEN 'visible'
        WHEN NOT all_visible THEN 'no_visible'
        END pages,
        100 * COUNT(all_visible) OVER (PARTITION BY all_visible) / COUNT(all_visible) OVER () AS percent
    FROM
        pg_visibility_map ('<table>')) x
GROUP BY
    x.pages
UNION ALL
SELECT
    'no_visible'::text,
    0
WHERE NOT EXISTS (SELECT 1 FROM pg_visibility_map ('<table>') WHERE NOT all_visible);

在新加载的没有更新的表中，或者刚刚对表进行过 VACUUM 操作（VACUUM 过程会负责更新可见性映射表），我们可以获得类似于以下内容的输出：

pages    | percentage
------------+------------
 visible    |        100
 no_visible |          0
(2 rows)

当更新发生时，标记为可见的页面的百分比也会发生变化：

UPDATE person SET register_date = now() WHERE age < 15;

SELECT
    x.pages::text,
    round(avg(x.percent), 1)::int AS percentage
FROM (
    SELECT
        CASE WHEN all_visible THEN 'visible'
        WHEN NOT all_visible THEN 'no_visible'
        END pages,
        100 * COUNT(all_visible) OVER (PARTITION BY all_visible) / COUNT(all_visible) OVER () AS percent
    FROM
        pg_visibility_map ('person')) x
GROUP BY
    x.pages
UNION ALL
SELECT
    'no_visible'::text,
    0
WHERE NOT EXISTS (SELECT 1 FROM pg_visibility_map ('person') WHERE NOT all_visible);
   pages    | percentage
------------+------------
 visible    |         46
 no_visible |         53
(2 rows)

PostgreSQL 将继续尝试执行只用索引的扫描，但是当页面在可见性映射表中标记为 “不可见” 时，它将不得不切换成不同的扫描方法，并从堆表中读取，从而失去所有性能提升。而且，标记为“不可见”的页面越多，只用索引的扫描尝试成功的可能性就越小。

在这种情况下，在对表进行 VACUUM 清理后，我们将再次看到所有页面都标记为 “可见的”：

VACUUM person;

SELECT
    x.pages::text,
    round(avg(x.percent), 1)::int AS percentage
FROM (
    SELECT
        CASE WHEN all_visible THEN 'visible'
        WHEN NOT all_visible THEN 'no_visible'
        END pages,
        100 * COUNT(all_visible) OVER (PARTITION BY all_visible) / COUNT(all_visible) OVER () AS percent
    FROM
        pg_visibility_map ('person')) x
GROUP BY
    x.pages
UNION ALL
SELECT
    'no_visible'::text,
    0
WHERE NOT EXISTS (SELECT 1 FROM pg_visibility_map ('person') WHERE NOT all_visible);
   pages    | percentage
------------+------------
 visible    |        100
 no_visible |          0
(2 rows)

结论

只用索引的扫描无疑是为我们的查询获得最佳性能的绝佳方法；但是，在添加复合索引或覆盖索引之前，我们还需要注意表在更新和页面可见性方面的行为。

借助 pg_visibility 扩展和一些查询（如我们在本文中看到的查询），我们可以监控我们的表，并确定它们是否适合使用只用索引的扫描，或者是否可以针对 VACUUM 节奏或业务负载类型进行一些调整。