阅读量:0
到目前为止，索引是每个通用关系数据库提供的性能相关最多的功能。没有索引，就没有高效的搜索操作，没有唯一约束，也没有主键。因此，在没有索引的情况下，在合理数量的数据上实现良好的性能是完全不可能的。
生成样本数据
现在的主要问题是：在索引创建过程中会发生什么，我们如何加快这一过程？在数十亿行上创建索引的成本非常高，因为必须对数据进行排序并将其转换为有效的搜索结构。为了演示如何快速创建索引，我创建了一组简单的演示数据：
blog=# CREATE TABLE t_data AS SELECT id::int4, (random() * 1000000000)::int4 AS i, (random() * 1000000000) AS r, (random() * 1000000000)::numeric AS n FROM generate_series(1, 1000000000) AS id; Time: 1569002.499 ms (26:09.002) 此示例数据具有几个有趣的属性：“id”列是一个升序数字。在索引创建过程中，这会产生巨大的差异。第二列包含一个随机值乘以行数作为整数值。第三列包含一个“双精度”数字，最后，在最后，我们将类似的数据存储为“数值”，这是一个浮点数，在内部不使用 FPU（CPU 的浮点单元）。
一旦创建了这十亿行，我们就可以使用以下命令轻松检查表的大小：
blog=# SELECT pg_size_pretty(pg_relation_size('t_data')); pg_size_pretty ---------------- 56 GB (1 row) 一旦创建了测试数据，在PostgreSQL中设置一个名为“提示位”的东西是有意义的，这将使我们能够在各种运行之间进行公平的比较。如果您对提示位及其与 VACUUM 的关系感兴趣，请考虑查看我们关于此主题的博客文章。
PgSQL blog=# VACUUM ANALYZE; VACUUM Time: 91293.971 ms (01:31.294) 当 VACUUM 运行时，我们可以检查此操作的进度。如果 VACUUM 操作需要很长时间，这可以为我们提供一些有趣的见解，了解您的数据库内部正在发生的事情：
PgSQL demo=# SELECT * FROM pg_stat_progress_vacuum; -[ RECORD 1 ]------+-------------- pid | 5945 datid | 1515520 datname | blog relid | 1515537 phase | scanning heap heap_blks_total | 7352960 heap_blks_scanned | 212599 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 291 num_dead_tuples | 0 创建数据集后，我们可以继续前进，在表上创建简单的索引：
blog=# \d t_data Table "public.t_data" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- id | integer | | | i | integer | | | r | double precision | | | n | numeric | | | 创建简单索引
以下列表显示了如何为每列编制索引，以及执行此操作所需的时间。请注意，所有这些操作都是使用 PostgreSQL 默认设置进行的，正如我们稍后将看到的那样，这远非理想。所有测试均在“AMD Ryzen Threadripper 2950X 16-Core”处理器上执行：
blog=# CREATE INDEX ON t_data (id); CREATE INDEX Time: 291700.318 ms (04:51.700) 正如我们所看到的，索引创建需要将近 5 分钟。请记住，我们正在处理 10 亿行，我们甚至还没有开始调整数据库。有趣的是 PostgreSQL 在创建索引时做了什么。我们可以检查系统视图以提供一些线索：
PgSQL blog=# SELECT * FROM pg_stat_progress_create_index; -[ RECORD 1 ]------+------------------------------- pid | 5945 datid | 1515520 datname | blog relid | 1515537 index_relid | 0 command | CREATE INDEX phase | building index: scanning table lockers_total | 0 lockers_done | 0 current_locker_pid | 0 blocks_total | 7352960 blocks_done | 703280 tuples_total | 0 tuples_done | 0 partitions_total | 0 partitions_done | 0 在第一步中，PostgreSQL将扫描表并为接下来的排序操作准备数据：
PgSQL -[ RECORD 1 ]------+------------------------------------ pid | 5945 datid | 1515520 datname | blog relid | 1515537 index_relid | 0 command | CREATE INDEX phase | building index: sorting live tuples lockers_total | 0 lockers_done | 0 current_locker_pid | 0 blocks_total | 7352960 blocks_done | 7352960 tuples_total | 0 tuples_done | 0 partitions_total | 0 partitions_done | 0 排序可能非常耗时，因此正确调整此过程以实现良好的性能非常重要。在排序过程之后，PostgreSQL 会将这些排序的元组添加到索引中，如下一列表中所示：
PgSQL -[ RECORD 1 ]------+--------------------------------------- pid | 5945 datid | 1515520 datname | blog relid | 1515537 index_relid | 0 command | CREATE INDEX phase | building index: loading tuples in tree lockers_total | 0 lockers_done | 0 current_locker_pid | 0 blocks_total | 0 blocks_done | 0 tuples_total | 1000000000 tuples_done | 263748386 partitions_total | 0 partitions_done | 0 创建第一个索引后，我们可以查看第二个索引列。请注意，数据类型和数据量是相同的。尽管如此，我们还是可以观察到，该过程需要更长的时间，因为数据是以随机顺序提供给索引创建的，这在大多数情况下可能会产生巨大的差异：
PgSQL blog=# CREATE INDEX ON t_data (i); CREATE INDEX Time: 401634.592 ms (06:41.635) 哇，索引创建需要将近 2 分钟的时间，这真的很相关 - 我们谈论的操作比以前要长得多。
然而，数据的物理顺序并不是唯一可以产生巨大差异的东西。对于我们想要索引的数据类型也是如此。只需看一下，看看运行时将如何根据数据类型而变化：
PgSQL blog=# CREATE INDEX ON t_data (r); CREATE INDEX Time: 441994.372 ms (07:21.994) “双精度”列再次慢 40 秒。必须记住，“双精度”值也大于整数值，这肯定会造成这些运行时差异。
最后要看的是索引“数字”，它的行为方式与以前完全不同：
PgSQL blog=# CREATE INDEX ON t_data (n); CREATE INDEX Time: 799659.658 ms (13:19.660) 这里需要注意的是，有两件事真的很重要：
数据是否已排序？
使用哪种数据类型？
这两个方面往往都被完全低估了，因为大多数人只考虑“更多的RAM”和更多的CPU（好像其他什么都不重要）。
尝试许多性能改进
人们经常做的是将硬件和配置参数扔给问题。
通常，以下参数会更改：
PgSQL blog=# ALTER SYSTEM SET max_wal_size TO '100 GB'; ALTER SYSTEM blog=# ALTER SYSTEM SET max_parallel_maintenance_workers TO 10; ALTER SYSTEM blog=# ALTER SYSTEM SET maintenance_work_mem TO '16 GB'; ALTER SYSTEM blog=# ALTER SYSTEM SET shared_buffers TO '64 GB'; ALTER SYSTEM 完成此操作后，我们可以重新启动数据库。请注意，在不更改的情况下，shared_buffers“SELECT pg_reload_conf（）”就完全足够了。
让我们一次一个地讨论这些参数：
- max_wal_size： 这控制了检查点之间的距离和 WAL 的大小。它对减少 I/O 量和总体上加快 I/O 有很大帮助。
- max_parallel_maintenance_workers： 此变量控制允许 PostgreSQL
启动多少个工作进程来构建索引。它定义了工人的上限。 - maintenance_work_mem： 这定义了每个操作在内存中可以发生多少。
- shared_buffers：I/O 缓存的大小
让我们再次运行索引创建：
blog=# CREATE INDEX ON t_data (n); CREATE INDEX Time: 474907.805 ms (07:54.908) 这很有趣：我们设法将时间从大约 13 分 19 秒减少到近 8 秒。请注意，这甚至不是速度的两倍。但是，CPU怎么说？
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8773 hs 20 0 67.8g 2.2g 196256 R 99.0 1.8 1:05.88 postgres 8824 hs 20 0 66.6g 968576 170576 R 99.0 0.7 0:18.96 postgres 8825 hs 20 0 66.6g 1.0g 170796 R 99.0 0.8 0:18.98 postgres 8826 hs 20 0 67.4g 1.8g 170796 R 99.0 1.4 0:18.97 postgres 8827 hs 20 0 67.4g 1.8g 170796 R 99.0 1.4 0:18.95 postgres 8829 hs 20 0 66.6g 990.8m 170796 R 99.0 0.8 0:18.93 postgres 8823 hs 20 0 67.4g 1.8g 170796 R 98.7 1.4 0:18.95 postgres 8828 hs 20 0 66.7g 1.0g 170376 R 98.7 0.8 0:18.95 postgres 这很有趣：我们在对数据进行排序时抓住了 CPU。现在这是相关的，因为排序阶段可以以一种很好的方式并行完成 - 许多其他阶段不能在RAM中发生，或者不能同时发生。这就是为什么即使有无数的 CPU 内核，我们也只能设法加快速度如此之快的原因。在这里绝对限制我们的是本地SSD的容量，在排序过程中产生大约600 MB / sec。但是，更值得一提的是，使用许多更高的参数设置，对整数变量进行排序的默认配置实际上比完全并行的索引创建更快。
数据类型确实很重要这一事实告诉我们，调整配置参数绝对不是获得启蒙的唯一途径。确定正确的数据类型也会产生巨大的差异。
#PG培训#PG考试#postgresql培训#postgresql考试#postgresql认证
