就像这个世界上的所有事物一样，微服务架构也有其优缺点。使用它的某些过程变得更容易，而其他过程则更加复杂。为了改变的速度和更好的可伸缩性，必须做出牺牲。其中之一是分析的复杂性。如果在一个整体中所有操作分析都可以简化为用于分析副本的SQL查询，那么在多服务体系结构中，每个服务都有其自己的基础，并且似乎无法放弃一个查询（或者可以放弃它吗？）。对于那些对我们如何解决公司的运营分析问题以及我们如何学会使用该解决方案感兴趣的人，欢迎您。我的名字叫Pavel Sivash，在DomKlik中，我在一个团队中负责维护分析数据仓库。按照惯例，我们的活动可以归因于工程设计的日期，但实际上，任务的范围要广泛得多。有ETL / ELT标准用于日期工程，工具的支持和改编，以进行数据分析和开发自己的工具。特别是，对于运营报告，我们决定“假装”拥有完整的资料，并为分析师提供一个基础，使他们可以拥有所需的所有数据。通常，我们考虑了不同的选择。可以构建一个完整的存储库-我们甚至尝试过，但是老实说，我们未能在建立和修改存储库的过程中让朋友在逻辑上进行足够频繁的更改（如果有人成功了，请在注释中写下如何做）。可以告诉分析人员：“人，学习python并获得分析提示”，但这是招聘人员的另一项要求，并且似乎应该避免这种情况。我们决定尝试使用FDW（外部数据包装器）技术：实际上，这是标准dblink，它在SQL标准中，但具有更方便的接口。在此基础上，我们做出了决定，最终定居下来，我们停止了。它的详细信息是另一篇文章的主题，或者可能不是，因为我想谈很多事情：从数据库方案的同步到访问控制和个人数据的匿名化。您还需要保留一点，即此解决方案不能替代实际的分析数据库和存储库，它只能解决特定的问题。顶级看起来像这样：有一个PostgreSQL数据库，用户可以在其中存储其工作数据，最重要的是，所有服务的分析副本都通过FDW连接到该数据库。这样就可以将查询写到多个数据库，无论它是什么：PostgreSQL，MySQL，MongoDB或其他（文件，API，如果突然没有合适的包装器，则可以编写自己的查询）。好吧，一切似乎都很棒！我们不同意吗？如果一切都如此迅速而简单地结束，那么可能就不会有文章了。重要的是清楚地了解postgres如何处理对远程服务器的请求。这似乎是合乎逻辑的，但通常他们并不注意它：postgres将请求分为独立的部分，这些部分在远程服务器上独立执行，收集数据，最后的计算由自己完成，因此请求的速度将很大程度上取决于请求的编写方式。还应注意：当数据来自远程服务器时，它们不再具有索引，没有什么可以帮助调度程序，因此，只有我们可以提供帮助和建议。我想告诉您更多有关此的信息。简单的请求和计划
为了展示postgres如何在远程服务器上的600万行表上执行查询，让我们看一个简单的计划。explain analyze verbose
SELECT count(1)
FROM fdw_schema.table;
Aggregate (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms
使用VERBOSE指令，您可以查看将发送到远程服务器的请求以及我们将收到的结果以进行进一步处理（RemoteSQL行）。让我们更进一步，为查询添加几个过滤器：一个过滤器是布尔字段，一个过滤器是时间间隔中的时间戳，一个过滤器是jsonb。explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';
Aggregate (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 5046843
Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms
这是您编写查询时需要注意的时刻。过滤器没有转移到远程服务器，这意味着要执行该过滤器，postgres扩展了全部600万行，只是稍后在本地过滤（过滤器行）并执行聚合。成功的关键是编写一个请求，以便将筛选器传输到远程计算机，并且我们仅接收和汇总必要的行。那是一些布尔值
使用布尔字段，一切都很简单。在原始请求中，问题出在is语句。如果将其替换为=，那么将得到以下结果：explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';
Aggregate (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 3567989
Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms
如您所见，过滤器飞到了远程服务器，运行时间从27秒减少到19秒。值得注意的是，is运算符与=运算符的不同之处在于它可以使用Null值。这意味着在过滤器中不为True将保留False和Null，而！= True将仅保留False。因此，当替换is not运算符时，应使用OR运算符将两个条件传递给过滤器，例如WHERE（col！= True）OR（col为null）。布尔值整理出来后，继续前进。同时，将布尔值过滤器返回其原始形式，以独立考虑其他更改的影响。时间戳？赫兹
通常，通常必须尝试如何编写涉及远程服务器的查询，然后才寻找导致这种情况发生的原因的解释。关于此的很少信息可以在Internet上找到。因此，在实验中，我们发现带有固定日期的过滤器会突然跳到远程服务器，但是当我们想动态设置日期时，例如now（）或CURRENT_DATE，则不会发生这种情况。在我们的示例中，我们添加了一个过滤器，以便created_at列包含过去1个月内的确切数据（BETWEEN CURRENT_DATE-INTERVAL'7 month'AND CURRENT_DATE-INTERVAL'6 month'）。在这种情况下我们做了什么？explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month')
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';
Aggregate (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
Output: count(1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
-> Foreign Scan on fdw_schema."table" (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
Rows Removed by Filter: 76934
Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms
我们提示调度程序提前在子查询中计算日期，然后将现成的变量传递给过滤器。这个提示给我们带来了出色的结果，查询速度几乎快了6倍！同样，在这里要小心一点很重要：子查询中的数据类型必须与我们要过滤的字段相同，否则调度程序将决定由于类型不同而必须首先获取所有数据并在本地对其进行过滤。按日期将过滤器恢复为其原始值。弗雷迪与 杰森
通常，布尔字段和日期已经加快了我们的查询速度，但是还有另外一种数据类型。坦白说，尽管取得了成功，但对其进行过滤的斗争仍未结束。因此，这就是我们设法通过jsonb字段将过滤器传递给远程服务器的方法。explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 619961
Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms
代替筛选运算符，必须使用在另一个jsonb中包含一个jsonb的运算符。7秒，而不是最初的29秒。到目前为止，这是将过滤器通过jsonb传输到远程服务器的唯一成功选择，但是重要的是要考虑到一个限制：我们使用数据库版本9.6，但是我们计划在4月底完成最新的测试并移至版本12。随着我们的更新，我们将写出它的影响方式，因为有许多希望带来的变化：json_path，新的CTE行为，下推（版本10中已有）。我想尽快尝试。解决他
我们检查了每个更改如何分别影响请求的速度。现在，让我们看看正确编写所有三个过滤器时会发生什么。explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month')
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
Output: count(1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
-> Foreign Scan on fdw_schema."table" (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms
是的，请求看起来更复杂，是强制执行的，但是执行速度为2秒，快10倍以上！我们正在谈论一个相对较小的数据集的简单查询。根据实际要求，我们获得了多达数百倍的增长。总结一下：如果将PostgreSQL与FDW一起使用，请始终检查所有过滤器是否都已发送到远程服务器，您会很高兴的……至少直到从不同服务器进入表之间的联接为止。但这是另一篇文章的故事。感谢您的关注！我很高兴听到问题，评论以及关于您在评论中的经历的故事。