八月 29, 2024
摘要:在本教程中,您将学习如何在 PostgreSQL 中利用只用索引的扫描。
目录
介绍
在文章索引扫描类型:位图、索引和只用索引中,我们回顾了 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 节奏或业务负载类型进行一些调整。