我正在设计一个 Web 应用程序,卖家可以在其中提供他们的汽车,银行提供各种融资优惠(例如 36 个月、25% 首付、25% 最终付款)。买家来到这个网络应用程序,并根据各种搜索条件搜索汽车:例如,年龄小于 5 岁,月付款低于 500 美元,红色汽车每月费用低于 350 美元,合同期限为 36 或 48 个月。
在我的系统中,我有列表,每个列表可能最多有 18 个计算。
清单就是一辆汽车。为简洁起见,列表具有以下属性:id、颜色、里程。
计算是融资要约。每个计算都有以下属性:id、listingId、financeProviderId、months、downPayment、finalPayment、monthlyRate。
在数据库中我有两个表:列表和计算。
CREATE TABLE IF NOT EXISTS public.calculation
(
id uuid NOT NULL,
"listingId" uuid NOT NULL,
"financeProviderId" smallint NOT NULL,
"downPayment" numeric(10,2) NOT NULL,
"finalTerm" numeric(10,2) NOT NULL,
rate numeric(10,2),
CONSTRAINT calculation_pkey PRIMARY KEY (id),
CONSTRAINT "calculation_listingId_fkey" FOREIGN KEY ("listingId")
REFERENCES public.listing (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
CREATE INDEX IF NOT EXISTS "calculation_listingId"
ON public.calculation USING btree
("listingId" ASC NULLS LAST)
TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS "calculation_downPayment"
ON public.calculation USING btree
("downPayment" ASC NULLS LAST)
TABLESPACE pg_default;
-- similar indices for all the other fields
CREATE TABLE IF NOT EXISTS public.listing
(
id uuid NOT NULL,
color integer,
mileage integer,
CONSTRAINT listing_pkey PRIMARY KEY (id)
)
CREATE INDEX IF NOT EXISTS listing_mileage
ON public.listing USING btree
(mileage ASC NULLS LAST)
TABLESPACE pg_default;
-- similar indices for constructionYear and other attributes
当用户搜索要购买的汽车时,他们希望看到符合其搜索条件的汽车分页列表以及匹配汽车的总数。
获取列表通常不成问题,因为列表页面最多只显示 20 辆汽车。
但是每个 COUNT 查询都非常慢(2-20 秒),尽管数据库上还没有负载(产品在发布之前)。下面是一个这样的查询,它想要计算颜色 ID 7 且里程数少于 75000 英里、首付 0%、最终付款 25% 且月费低于 350 美元的列表数量。
SELECT COUNT(DISTINCT "l"."id")
FROM "listing" as "l"
INNER JOIN "calculation" as "ca" ON "l"."id" = "ca"."listingId"
WHERE
"l"."color" = 7 AND "mileage" < 75000
AND "ca"."downPayment" = 0 AND "ca"."finalTerm" = 25 AND monthlyRate < 350
在系统中我有大约。30 万条列表和 150 万次计算。(并非每个列表都有所有 18 种可能的计算,例如旧车在 60 或 72 个月内不会收到报价。)
我正在使用 AWS Aurora Postgres Serverless V2。但我猜 COUNT 查询速度慢是 Postgres 的一个普遍问题。另外,我很惊讶如此少量的数据却会导致如此糟糕的性能。
现在我问我能做些什么来加快计数查询。我的目标是让 COUNT 查询的运行时间低于 100 毫秒,但我可以忍受低于 350 毫秒。
在 Postgres 上快速执行 COUNT 次查询有什么秘诀吗?
count()
并不比其他聚合慢。但我不明白人们怎么能认为它很快。如果你的抽屉里有很多袜子的话,数起来也不会很快。请参阅此处了解加快计数的可用选项。不管怎样,每当有人抱怨这样的查询太慢时,我就会得出这样的结论:他们正在计算结果集总数。这始终是一个糟糕的主意,解决方案就是不要这样做。选择可用的替代方案之一:
EXPLAIN
快速获得近似计数我知道加快速度并使其变得更快是可能的。数据量很小,大约。1GB,包含索引和所有内容。只是我的架构不足以满足我需要的查询类型。这是我最终得到的解决方案。
展平矩阵
我
calculation
按以下方式重组了该表,并将其重命名为financialData
并“展平”了计算矩阵。每个计算都只是列中的一个值。表中的
financialData
ID 与列表的 ID 相同。其他列保存给定参数组合的月利率,例如rate_12_10_25
包含 12 个月的月利率,10% 首付 25% 余款。如何查询这个表呢?
这最终比我最初想象的更简单。
最初问题的示例:查找颜色 ID 7 且里程数少于 75000 英里、首付款为 0%、最终付款为 25%、月费低于 350 美元的所有列表,并按月费率订购。(请注意,我对所有
listings
而不是所有可能的财务优惠感兴趣。)相应的
COUNT
查询就更简单了:该解决方案需要应用程序代码具有一定的智能性,以选择要
rate_AA_BB_CC
查询的正确列 ( )。根据用户的搜索条件,rate_AA_BB_CC
查询中会包含动态数量的字段,例如,如果用户仅对 12 个月的合同感兴趣,则仅选择rate_12_00_00
、rate_12_00_25
、rate_12_10_00
、列。rate_12_10_25
重构架构后,大多数查询的执行时间可以达到 100 毫秒以下。
附录 您可能已经注意到,
financialData
我在表中添加了一列offers jsonb
。这仅存储有关财务报价的一些其他详细信息,这些详细信息不应被搜索,但可能需要向用户显示(法律事项、其他费用等)。该字段仅由应用程序代码处理。