开源中国

我们不支持 IE 10 及以下版本浏览器

It appears you’re using an unsupported browser

为了获得更好的浏览体验,我们强烈建议您使用较新版本的 Chrome、 Firefox、 Safari 等,或者升级到最新版本的IE浏览器。 如果您使用的是 IE 11 或以上版本,请关闭“兼容性视图”。
提升 PostgreSQL 中 Count 的速度 - 技术翻译 - 开源中国社区

提升 PostgreSQL 中 Count 的速度 【已翻译100%】

标签: PostgreSQL
oschina 推荐于 1个月前 (共 21 段, 翻译完成于 08-30) 评论 2
收藏  
34
推荐标签: PostgreSQL 待读

分析 PostgreSQL 中可用于不同情况的优化技术,并了解如何在分布式数据库中的进行并行计数。

人人都进行优化计数 - 但并不总是很快。 本文将仔细研究 PostgreSQL 如何优化计数。 如果你知道这些技巧,那么有一些方法进行计数可以比你更快速。

问题实际上还没被识别 - 这有几种计数方式,每种都有自己的方法。 首先,你要考虑是否需要确切的计数,或者估算计数。然后,检查是计数重复数据或仅计数不同的值? 最后,你想要整个表的一个块数,还是想只计算符合额外条件的那些行?

我们将分析每种情况的可用技术,并比较其速度和资源消耗。 在学习单个数据库的技术后,我们将使用 Citus 来演示如何在分布式数据库中的并行计数。

亚林瓜子
 翻译得不错哦!

准备用于测试的数据库

下面的部分会使用这个表作为测试基准。

-- create a million random numbers and strings
CREATE TABLE items AS
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM
    generate_series(1,1000000);
-- inform planner of big table size change
VACUUM ANALYZE;

包含重复数据的计数

我们来看看精确计数和估计计数。

精确计数

我们从头开始。精确计算允许表中有部分重复甚至全都重复 —— 是好的老办法 count(*)。测量运行此命令的时间,用作其它类型的计数速度的参照。

pgbench 提供了一个便捷的方法来重复执行一个查询并统计性能。

# Tests in this article were run against PostgreSQL 9.5.4
echo "SELECT count(*) FROM items;" | pgbench -d count -t 50 -P 1 -f -
# average  84.915 ms
# stddev    5.251 ms

关于 count(1) 和 count(*) 比较的说明:有人会认为 count(1) 会更快,因为 count(*) 看起来遍历了整行。然而实事却并非如此。星号在这里没有意义,这与 SELECT * 中的星号不同。PostgreSQL 将 count(*) 表达式解析为特殊情况,表示没有参数。(历史上这个表达式应该被定义为 count())。另一方面,count(1) 有一个参数,PostgreSQL 不会用这个参数去检查每一行,1,实际表示非空(NULL)。

边城
 翻译得不错哦!

用 count(1) 运行上面的测试基准,结果是:

# average  98.896 ms
# stddev    7.280 ms

不过,count(1) 和 count(*) 这两个形式从根本上来说都很慢。PostgreSQL 使用多版本并发控制 (MVCC) 来保证并发事务之间的一致性。这就是说,每个事务可以看到表中 —— 不同的行 —— 以及不同的行数 。数据库不能缓存单一的普通行计数,所以它必须遍历检查所有行来统计有多少行可见。精确统计性能[译者注:这里指耗时]与表尺寸增长成线性关系。

EXPLAIN SELECT count(*) FROM items;
Aggregate  (cost=20834.00..20834.01 rows=1 width=0)
  ->  Seq Scan on items  (cost=0.00..18334.00 rows=1000000 width=0)

扫描消耗占总消耗的 88%。如果我们的表尺寸翻倍,查询时间大约增加一倍,扫描和聚合消耗都根据对方按比例增长。

                行数                 平均时间
                1M                 85ms
                2M                 161ms
                4M                 343ms

我们要怎么才能提高速度呢?这必须要有所付出。我们可以采用估算计数而不是精确计数,或者通过手工递增/递减计数并缓存起来。然而,对于第二种情况,我们必须为每个表和每个 where 子句保存计数器,以便后面进行快速计数。

边城
 翻译得不错哦!

这里有一个关于计数器方法的示例,它应用于整个 items 表。下面基于触发器的解决方案改编自 A. Elein Mustain。PostgreSQL 的 MVCC 会维护 items 表和保存行数的表之间的一致性。

BEGIN;
CREATE TABLE row_counts (
  relname   text PRIMARY KEY,
  reltuples bigint
);
-- establish initial count
INSERT INTO row_counts (relname, reltuples)
  VALUES ('items', (SELECT count(*) from items));
CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$
   DECLARE
   BEGIN
   IF TG_OP = 'INSERT' THEN
      EXECUTE 'UPDATE row_counts set reltuples=reltuples +1 where relname = ''' || TG_RELNAME || '''';
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      EXECUTE 'UPDATE row_counts set reltuples=reltuples -1 where relname = ''' || TG_RELNAME || '''';
      RETURN OLD;
   END IF;
   END;
$
LANGUAGE 'plpgsql';
CREATE TRIGGER items_count BEFORE INSERT OR DELETE ON items
  FOR EACH ROW EXECUTE PROCEDURE adjust_count();
COMMIT;

读取和更新缓存值的速度不受表尺寸影响,而且读取会非常快。不过,这个技术会把开销转嫁到插入 (insert) 和删除 (delete) 操作。没有触发器的情况下,下面的语句大约需要 4.7 秒,而使用了触发器的插入操作会慢50倍

INSERT INTO items (n, s)
  SELECT
    (random()*1000000)::integer AS n,
    md5(random()::text) AS s
  FROM generate_series(1,1000000);
边城
 翻译得不错哦!

估算计数

我们来看看完整的表估算和筛选过的表估算。

完整表估算

前面缓存“计数器”的方法会让插入变慢。如果我们可以接受估算计数而不是精确计数,我们就可以在不减弱插入的情况下像计数器一样迅速获取计数值。我们可以依靠来自 PostgreSQL 子系统的估算聚集。两个来源分别是统计采集器自动清理守护

这里是获取估算的两种选择:

-- Asking the stats collector
SELECT n_live_tup
  FROM pg_stat_all_tables
 WHERE relname = 'items';
-- Updated by VACUUM and ANALYZE
SELECT reltuples
  FROM pg_class
 WHERE relname = 'items';

然而,越准确的来源越不可能是陈旧的。Andrew Gierth (RhodiumToad) 建议:

记住 reltuples 并不是估计实际使用的计划器;计划器使用 reltuples/relpages 乘以当前页数。

这是直觉。随着表中数据样本的增加,物理页面中拟合的平均行数可能比总行数的变化更为彻底。我们可以通过用每页的平均行数乘以当前页数的最新信息,以获得更精确的估算当前行数。

-- pg_relation_size and block size have fresh info so combine them with
-- the estimation of tuples per page
SELECT
  (reltuples/relpages) * (
    pg_relation_size('items') /
    (current_setting('block_size')::integer)
  )
  FROM pg_class where relname = 'items';
边城
 翻译得不错哦!

过滤表评估

上一节讲了整表行数评估,但是有没有一种方法来获得带条件的行数? Michael Fuhr 想出了一个聪明的技巧来运行 EXPLAIN 进行查询并解析其输出行数。

CREATE FUNCTION count_estimate(query text) RETURNS integer AS $
DECLARE
  rec   record;
  rows  integer;
BEGIN
  FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
    rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
    EXIT WHEN rows IS NOT NULL;
  END LOOP;
  RETURN rows;
END;
$ LANGUAGE plpgsql VOLATILE STRICT;

我们可以使用这样的函数:

SELECT count_estimate('SELECT 1 FROM items WHERE n < 1000');

该方法的准确性依赖于使用几种技术来估计 where 子句的选择性,并从中返回行数。

总长
 翻译得不错哦!

去重计数(无重复)

我们来看看准确的计数和预估的计数。

精确计数

我们来看看低内存,自定义聚合,HashAggregate 和仅索引扫描下的默认行为。

低内存下的默认行为

重复计数可能很慢,但去重计数明显更糟。由于工作内存有限,没有索引,PostgreSQL 无法优化。在其库存配置中,PostgreSQL 为每个并发查询(work_mem)指定了一个低内存限制。在我的开发机器上,默认是四兆字节。

使用默认设置,这里是处理一百万行的性能情况。

echo "SELECT count(DISTINCT n) FROM items;" | pgbench -d count -t 50 -P 1 -f -
# average  742.855 ms
# stddev    21.907 ms
echo "SELECT count(DISTINCT s) FROM items;" | pgbench -d count -t 5 -P 1 -f -
# average  31747.337 ms
# stddev     267.183 ms

运行 EXPLAIN 显示大量查询发生在聚合中,并且在字符串列上运行计数的时间长于整数列:

-- plan for the integer column, n
Aggregate  (cost=20834.00..20834.01 rows=1 width=4) (actual time=860.620..860.620 rows=1 loops=1)
  Output: count(DISTINCT n)
  Buffers: shared hit=3904 read=4430, temp read=1467 written=1467
  ->  Seq Scan on public.items  (cost=0.00..18334.00 rows=1000000 width=4) (actual time=0.005..107.702 rows=1000000 loops=1)
        Output: n, s
        Buffers: shared hit=3904 read=4430
-- plan for the text column, s
Aggregate  (cost=20834.00..20834.01 rows=1 width=33) (actual time=31172.340..31172.340 rows=1 loops=1)
  Output: count(DISTINCT s)
  Buffers: shared hit=3936 read=4398, temp read=5111 written=5111
  ->  Seq Scan on public.items  (cost=0.00..18334.00 rows=1000000 width=33) (actual time=0.005..142.276 rows=1000000 loops=1)
        Output: n, s
        Buffers: shared hit=3936 read=4398
亚林瓜子
 翻译得不错哦!

在聚集内部发生了什么?在输出中的描述信息是含糊的。我们可以通过分析相关查询来获得更多细节,选择使用 distinct 而不是 count distinct。

EXPLAIN (ANALYZE, VERBOSE) SELECT DISTINCT n FROM items;
Unique  (cost=131666.34..136666.34 rows=498824 width=4) (actual time=766.775..1229.040 rows=631846 loops=1)
  Output: n
  ->  Sort  (cost=131666.34..134166.34 rows=1000000 width=4) (actual time=766.774..1075.712 rows=1000000 loops=1)
        Output: n
        Sort Key: items.n
        Sort Method: external merge  Disk: 13632kB
        ->  Seq Scan on public.items  (cost=0.00..18334.00 rows=1000000 width=4) (actual time=0.006..178.153 rows=1000000 loops=1)
              Output: n

无需更多的 work_mem 或像索引这样的外部数据结构,PostgreSQL 合并-排序保存在内存和磁盘中的表,然后迭代结果并删除重复的内容,这很像经典的 Unix 命令组合 sort | uniq

排序会占用大部分查询时间,特别是如果我们选择字符串列 s 而不是整数列 n 的情况下。在这两种情况下,唯一的过滤器会以相同的速度执行。

Tocy
 翻译得不错哦!

自定义聚合(Custom Aggregate)

Thomas Vondra 创建了一个自定义聚合,用于计数在固定长度类型的列中的不同值(另外,类型必须最多有 64 位)。无需任何额外的工作内存或索引,它会使用默认的基于排序的计数。安装步骤:

  1. 克隆该项目,tvondra/count_distinct

  2. 运行 make install

  3. 在你的数据库中执行:CREATE EXTENSION count_distinct;

Thomas 在此博客中解释了该聚合是如何工作的,但其简短的描述就是它在内存中构建了一个唯一元素的排序数组,并在运行时将其压缩。

echo "SELECT COUNT_DISTINCT(n) FROM items;" | pgbench -d count -t 50 -P 1 -f -
# average  434.726 ms
# stddev    19.955 ms

这击败了对唯一聚聚合的标准计数方法,其在我们的数据集上平均运行时间为 742 ms。 请注意,用 C 编写的自定义扩展(如 count_distinct)不受 work_mem 的值约束。在这个扩展中构造的数组可能会超过你对内存使用的预期。

Tocy
 翻译得不错哦!

Hash 聚合(HashAggregate)

当要计数的所有行可以放到 work_mem 中时,PostgreSQL 使用一个哈希表来获取唯一值:

SET work_mem='1GB';
EXPLAIN SELECT DISTINCT n FROM items;
HashAggregate  (cost=20834.00..25822.24 rows=498824 width=4)
  Group Key: n
  ->  Seq Scan on items  (cost=0.00..18334.00 rows=1000000 width=4)

到目前为止,这是最快的方式来获得唯一值的。对于 n,平均需要 372 ms,s 为 23 ms。查询选择唯一的 n 并且 select count(distinct n)大致使用相同的时间量运行,这表明计数唯一值的聚合在内部也使用了一个 HashAggregate。

注意 - 设置足够高的内存容量来激活此方法可能是不可取的。请记住,work_mem 单独应用于所有并发查询,因此它可以累加。相信我们可以做得更好。

Tocy
 翻译得不错哦!
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们
评论(2)
Ctrl/CMD+Enter

66
第一段翻译说实话,还不如百度翻译的好。
顶部