如何将一个复杂PostgreSQL数据库查询提升 20倍

最近在项目里面处理数据库查询缓慢的问题，期间也查阅过一些资料，现在得空将这些资料和处理案例整理出来分享给大家。

在开始之前，我们先来了解下项目情况。在配置方面，该项目使用的是12.2版本的自建 PostgresSQL 数据库，服务器配置为2核4G。在存储内容方面，该项目每个表数据量都比较大，有百万数据级的，也有千万数据级的。未做任何优化之前，一条简单的计数查询都可能花上几十秒，这是不能容忍的。查询优化刻不容缓，接下来让我们来看下可以从哪些方面入手吧。

一、优化方向思考

首先从大的方面来看，可以从业务调整、数据库配置调整、SQL调优(重构查询语句、添加索引)、硬件优化这四个方面入手。首先在业务调整方面，该业务属于是属于硬核需求，所以只能乖乖的用后面的几种方式来进行优化。

二、优化方式

1、数据库配置调整

1.1 修改 work_mem 和 shared_buffer 配置

work_mem 限制了查询语句在执行排序和哈希操作时所能使用的内存大小，当实际内存不足时，会在磁盘创建临时文件来存储操作结果。可以通过EXPLAIN命令查看查询数据使用的内存数，如果发现使用内存数超过配置值，则可以适当调高配置值。
shared_buffers 是数据库缓存大小，缓存越大的话，可以存放的数据就越多，那么命中缓存的次数也就越多，读取磁盘的次数就越少，这个配置一般设置为内存数量的25%。

1.2 vacuum命令

在PostgresSQL的多版本并行控制机制中，那些被UPDATE或者DELETE后的行会被标记为已过时，而不会立马从它们所属的表中物理删除，而 vacuum 命令可以回收这些无效行数据占据的存储空间，同时维护表的可见性映射信息。在仅索引扫描路径(Only Index Scan)中，会先检查数据可见性映射信息，如果它了解到在该页面上的所有元组都是可见的，那么就会避开堆获取，减少磁盘访问。

以下是查询某个月的订单数，从图中可以看到读取了堆数据122957次，执行时间用了45s。

执行SQL语句 vacuum orders; 之后同样的查询，获取堆数据减少了，执行时间只需要1.8s，缩短了很多。
这里用了不同的时间范围，避免缓存影响前后对比。

既然这个操作这么有效，那么我们很有必要来了解一下数据库是否有自动 vaccum 的操作，又是在什么时候触发 autovacuum。很容易就能查到，PostgreSQL是支持 autovaccum 的，触发 autovaccum 的默认阈值为表行数的 20%+50。这里就存在一个问题，当表数据比较大时，比如有一个100万条数据的表，那么只有当修改了500050条数据时才会触发 autovaccum，这就会导致上面例子中提到的情况，由于Heap Fetches 过多导致查询缓慢。 这个时候我们可以自己来修改触发阈值，比如以下是将订单的阈值改成 行数的 0%+5000（即5000）。

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 5000);

注意在执行 vaccum 操作时，会导致锁定表或表的部分，阻止其他事务读取或写入这些数据，所以阈值也不能设置得过小，否则频繁的 vaccum 操作也会影响到读写性能。

1.3 Analyze命令

analyze 收集数据库中表内容的统计信息，然后把结果保存在系统表 pg_statistic 里。 随后，查询规划器就可以使用这些统计更好判断查询的最佳规划。

触发 autoanalyze 的默认阈值为表行数的 10%+50，跟 autovacuum 一样，我们可以自己配置阈值，比如以下将订单的阈值改成 行数的 0%+5000（即5000）。

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE orders SET (autovacuum_analyze_threshold = 5000);

2、SQL调优

2.1 EXPLAIN 命令

要从SQL层面进行调优，首先我们得知道查询慢在哪里，此时就到了EXPLAIN命令登场的时候了。在SQL语句前面加上EXPLAIN关键字，并用上恰当的参数，可以输出详细的执行计划，从执行计划中就可以看出哪个步骤执行得比较慢。

首先，我们了解下执行计划的阅读原则（即执行顺序）：层级越深越优先、同一层级由上到下。
如下图所示，该执行计划的执行顺序用序号标出来，先遍历orders，然后遍历order_items，最后对两个过滤之后的结果集进行嵌套循环连接操作。

-- SQL: 
EXPLAIN (analyze,buffers,verbose) select orders.id, o_id, status from orders inner join order_items on orders.id = order_items.order_id AND orders.id < 1057250;`

图中第一行Nested Loop表示嵌套循环连接，接下来第一个括号括起来的内容为预估数据，cost表示预估执行时间，rows表示预估返回条数，width表示每行的平均宽度（字节）。

EXPLAIN加上 analyze 参数后才会实际执行SQL语句，并显示实际执行时间 actual time，actual time 单位是ms，等号右边第一个数是指启动当前操作需要的时间，第二个数指启动时间加上执行完当前操作所需的时间。actual time 右边的 rows 和 loops 分别指实际返回行数和循环次数。EXPLAIN 加上 buffers 参数，可以查看缓存命中情况，图中第三行 hit 表示命中缓存块数，read 指读取磁盘块数。 加上 verbose 参数，可以查看更详细的信息，如图中第二行，显示了投影数据（即选择返回的列）。

2.2 扫描路径和连接路径

这一节会简单介绍下常见的扫描路径和连接路径，如果对这一块比较熟悉，可以直接跳到下一节。

常见的扫描路径有顺序扫描、索引扫描、位图扫描、TID扫描、子查询扫描、通用表达式扫描等。

• 顺序扫描（Sequential Scan）：顺序扫描是最简单的扫描路径，它会按照物理存储的顺序，逐行扫描整个表。当表中没有可用的索引，或者查询返回大多数行时，会使用顺序扫描。
• 索引扫描（Index Scan）：索引扫描会利用表的索引来加速查询。它会先扫描索引，找到满足查询条件的索引项，找到对应表行的ctid(行的物理位置)，然后回表访问响应表页以获得整条数据。
  • 有两个原因会导致回表：
    （1）查询可能请求获取比相应索引列更多列
    （2）由于可见性信息不与索引数据一起维护，为了按照隔离级别检查数据的可见性，需要访问表数据。
  • 索引扫描有个问题是会产生随机读，而随机读IO时间代价大约是顺序IO的4倍，所以只有在总体增益超过随机IO开销时，才会选择索引扫描。
• 位图堆扫描（Bitmap Heap Scan）：位图堆扫描是一种结合了索引扫描和顺序扫描的扫描路径。它先通过索引扫描找到满足查询条件的索引项，并生成一个位图，然后根据位图按照顺序扫描表。位图堆扫描比索引扫描更有效率，因为它能避免随机IO，但是需要更多的内存。
• TID扫描（TID Scan）：TID扫描会直接根据表的ctid（行的物理位置）来定位表行。Tid扫描非常高效，但是只有在查询条件包含ctid时，才能使用。
• 子查询扫描（Subquery Scan）：子查询扫描是一种特殊的扫描方式，它会在执行过程中先对子查询进行扫描，然后将子查询的结果作为一个新的临时表，再对这个临时表进行扫描。这种扫描方式在处理复杂查询和包含多个子查询的查询时效率较高。
• 通用表达式扫描（Common Table Expressions Scan, CTE Scan）：通用表达式扫描是对WITH语句（即通用表达式）的查询进行扫描。通用表达式是一个临时的结果集，它在查询的执行过程中被创建，并且在该查询的生命周期内一直存在。通用表达式扫描可以方便地处理复杂的SQL查询，特别是那些包含多次引用同一子查询的查询。

常见的物理连接路径有嵌套循环连接、哈希连接 和 合并连接。

• 嵌套循环连接（Nested Loop Join）：嵌套循环连接是最简单的连接方式，它通过外部循环和内部循环来遍历两个表的所有行。对于在外部表中的每一行，它会扫描内部表并检查连接条件。这种连接方式处理小型表或者大表的小部分数据时效率较高，但是对于大型表数据的全连接，效率较低。示意图如下。

  

• 哈希连接（Hash Join）：哈希连接通过构建哈希表来加速连接操作。它会先扫描一张表并创建一个哈希表，然后扫描另一张表并通过哈希表进行连接。哈希连接在处理大型表数据时效率较高，但是需要更多的内存资源。示意图如下。

  

• 合并连接（Merge Join）：合并连接是一种基于排序的连接方式。它会先对两个表进行排序，然后同时扫描两个表并进行连接。合并连接在处理大型有序数据时效率较高，但是需要预先对数据进行排序。示意图如下。

  

2.3 添加索引

在添加索引时，我们需要注意以下几点：
（1）索引列的顺序：在多列索引中，查询条件中的列顺序和索引中的列顺序不一致可能导致索引失效。
（2）复合索引最左匹配原则：当对多列进行联合索引时，查询时需要从索引的最左列开始，否则索引将无法被有效利用。
（3）覆盖索引：查询语句中的投影（即选择的列）如果都在复合索引中，那么无需回表，这样减少了很多随机IO操作，提高查询性能。
我们来看个例子，以下是查询某些分类商品一个月内的订单数量：

explain analyze SELECT COUNT(DISTINCT "orders"."id")
FROM "orders"
LEFT OUTER JOIN "order_items" ON "order_items"."order_id" = "orders"."id"
LEFT OUTER JOIN "combine_sku_items" ON "combine_sku_items"."combine_sku_id" = "order_items"."jushuitan_sku_id"
WHERE "orders"."status" IN (2, 9) AND
      "orders"."is_delivery_online" = false AND
      "orders"."is_exclude_statistical" = false AND
      "orders"."jushuitan_type" != '换货订单' AND (
        "order_items"."category_id" IN (19,10,3,14,38,15,48,5,1,32,4,17,20,26,6,12,56,2,57,13,46,58,59) OR
        "combine_sku_items"."src_sku_id" IN (
          SELECT "shop_skus"."sku_id"
          FROM "shop_skus"
          WHERE "shop_skus"."is_combine_sku" = false AND (
            EXISTS (
              SELECT 1 FROM "products"
              WHERE "products"."i_id" = "shop_skus"."i_id" AND
                    "products"."category_id" IN (19,10,3,14,38,15,48,5,1,32,4,17,20,26,6,12,56,2,57,13,46,58,59)
            )
          )
        )
      ) AND "orders"."order_date" BETWEEN '2023-01-21 16:00:00' AND '2023-02-22 15:59:59.999999';

order_items 和 oders没有添加联合索引之前，执行时间如下图所示

为 order_items 添加联合索引

CREATE INDEX index_order_items_on_order_id_jushuitan_sku_id_category_id on order_items (order_id,jushuitan_sku_id,category_id);

为 order 添加覆盖索引

CREATE INDEX index_orders_on_filter_columns_and_id on orders (order_date,shop_id,status,jushuitan_type,is_delivery_online,is_exclude_statistical,id);

如下图所示，可以看到 orders 和 order_items 都使用了仅索引扫描(Only Index Scan)，避免了回表操作。

（4）在联合索引中，如果遇到范围查询(>、<、between、like)就会停止后续匹配。比如下面这个例子：

-- 查询衣服分类商品一个月内的销售额
EXPLAIN (ANALYZE, BUFFERS) SELECT SUM("data_statistics_counters"."value")
FROM "data_statistics_counters"
WHERE "data_statistics_counters"."owner_type" = 'ShopSku' AND
"data_statistics_counters"."counter_type" = 'sales_amount' AND
"data_statistics_counters"."time_kind" = 4 AND
"data_statistics_counters"."start_at" BETWEEN '2024-03-22 16:00:00' AND '2024-04-23 15:59:59.999999' AND
"data_statistics_counters"."owner_id" IN (
  SELECT "shop_skus"."id" FROM "shop_skus"
  LEFT OUTER JOIN "products" ON "products"."i_id" = "shop_skus"."i_id"
  INNER JOIN "categories" ON "categories"."id" = "products"."category_id"
  INNER JOIN "categories" "parents_categories" ON "parents_categories"."id" = "categories"."custom_parent_c_id"
  WHERE ("categories"."name" = '衣服' OR "parents_categories"."name" = '衣服')
);

在这条查询语句中，start_at 用了 between 范围查询，所以遇到 start_at 就会停止后面的字段匹配。所以即使下面的联合索引2用到的字段比较多，还是会使用成第一条索引。

data_statistics_counters 表有创建以下条索引：
联合索引1：(owner_type,owner_id,counter_type,time_kind,start_at,end_at)
联合索引2：(start_at,counter_type,time_kind,owner_type,owner_id,value)

联合索引2如果变成 (counter_type,time_kind,owner_type,owner_id,start_at,value)，那么就会使用第二条索引。在这里value字段放在最后面，是因为它不参与查询过滤，它只是一个投影列，为了避免回表加上的。

2.4 SQL重构

2.4.1 在查询语句中使用了函数/表达式、使用不等判断、隐式类型转换、从头开始模糊查询等都可能导致索引失效。有时候当查询语句过于复杂时，查询优化器也可能选择使用顺序扫描。比如下面这个例子，涉及到多个表关联查询（after_sales关联了after_sale_items, after_sale_items关联了skus, skus关联了products, products关联了categories），本来 after_sale_items 是有设置 after_sale_id 索引的，但是看到执行计划实际使用的是顺序扫描，而不是索引扫描。

EXPLAIN ANALYZE  SELECT COUNT(DISTINCT asales.order_id)
FROM after_sales asales
JOIN orders ON orders.id = asales.order_id
    AND orders.status IN (2, 9)
    AND orders.is_delivery_online = FALSE
    AND orders.is_exclude_statistical = FALSE
JOIN after_sale_items asi ON asi.after_sale_id = asales.id
JOIN skus ON skus.id = asi.sku_id
    AND skus.displayed = TRUE
LEFT JOIN products ON products.id = skus.product_id
LEFT JOIN categories ON categories.id = products.category_id
WHERE asales.status = 2
    AND asales.is_exclude_statistical = FALSE
    AND (categories.name IS NULL OR categories.name NOT IN (
        '办公用品', '半成品', '辅料', '童装', '邮费', '福袋', '珠江啤酒', '雨伞', '手机壳', 
        '眼镜', '红包', '大力水手', '领带', '32799', '外贸压模', '天猫半成品', '天猫辅料', '客户辅料', '男装辅料'
    ))
    AND asales.as_date BETWEEN '2024-03-26 00:00:00' AND '2024-04-26 00:00:00';

我们应该尽量简化查询，比如这里可以在 after_sale_items 中存储 category_id，然后把符合条件的 categories 的 id 查询出来，最后 after_sale_items 直接通过 category_id 查询，减少关联层级。下面是重构之后的SQL语句。查看执行计划，可以看到 after_sale_items 用上索引了

EXPLAIN ANALYZE SELECT COUNT(DISTINCT(after_sales.order_id))
FROM "after_sales"
INNER JOIN "orders" ON "orders"."id" = "after_sales"."order_id"
INNER JOIN "after_sale_items" ON "after_sale_items"."after_sale_id" = "after_sales"."id"
WHERE "after_sales"."status" = 2
  AND "after_sales"."is_exclude_statistical" = FALSE
  AND "orders"."status" IN (2, 9)
  AND "orders"."is_delivery_online" = FALSE
  AND "orders"."is_exclude_statistical" = FALSE
  AND ("after_sale_items"."category_id" IN (19,10,3,14,38,15,48,5,1,32,4,17,20,26,6,12,56,2,57,13,46,58,59)
        OR "after_sale_items"."category_id" IS NULL)
  AND "after_sales"."as_date" BETWEEN '2024-03-26 00:00:00' AND '2024-04-26 00:00:00';

2.4.2 有时候子查询并不会优先执行，可以使用WITH CTE（Common Table Expression）通用表表达式语句，提高子查询的执行优先级

-- 查询某个产品首次下单时间
EXPLAIN (ANALYZE, BUFFERS) SELECT  "orders".* FROM "orders" WHERE "orders"."id" IN (
  SELECT "order_items"."order_id"
  FROM "order_items"
  WHERE "order_items"."sku_id" IN (
    SELECT "skus"."id" FROM "skus" WHERE "skus"."product_id" = 14915 ORDER BY "skus"."id" DESC
  )
) ORDER BY "orders"."order_date" ASC LIMIT 1;

对应的执行计划语句如下，可以看到，优化器是先对 orders 进行过滤操作，再对子查询中的 order_items进行过滤操作。

可以使用WITH(CTE)语句，增加子查询的执行优先级。以下使用CTE语句将过滤后的order_items组织成一个临时的结果表，orders过滤时引用。

# Ruby On Rails 中使用activerecord-cte插件
order_ids = OrderItem.with(filtered_order_items: OrderItem.where(sku_id: Product.find(14915).skus.order(id: :desc).select(:id))).
    from("filtered_order_items as order_items").select(:order_id)
Order.where(id: order_ids).order(:order_date).first&.order_date

-- 转成SQL语句，并加上 EXPLAIN
EXPLAIN (ANALYZE) SELECT  "orders".* FROM "orders"
WHERE "orders"."id" IN (
  WITH "filtered_order_items" AS (
    SELECT "order_items"."order_id" FROM "order_items"
    WHERE "order_items"."sku_id" IN (
      SELECT "skus"."id" FROM "skus"
      WHERE "skus"."product_id" = 14915 ORDER BY "skus"."id" DESC
    )
  ) SELECT "order_items"."order_id" FROM filtered_order_items as order_items
) ORDER BY "orders"."order_date" ASC LIMIT 1;

另外在原始的SQL语句，其实可以直接删掉 skus 的排序 (ORDER BY "skus"."id" DESC)，对结果不会产生影响。以下是去掉skus排序之后的执行计划。可以看出，它先对order_items进行过滤，再将过滤出的结果作为orders的过滤条件。

3、硬件优化

本项目一开始为了降低项目成本，使用的是自建数据库，现在迁移到阿里云RDS云服务器上，方便查看日志和监控数据。迁移使用的是DTS迁移方式，其中的全量迁移服务是免费的，只要配置好源数据库和目标数据库连接信息，设置好要迁移的数据，就可以一键启动迁移。该项目有30多G数据，迁移过程不超过1个小时。

通过以上方法优化查询之后，发现一些比较复杂的查询，CPU还是会飙到100%，这种情况明显是因为资源不足导致的，考虑到数据库的维护成本，所以在迁移到RDS时，还升级了CPU配置，内存配置也同步提升。

三、结果对比

最后，我们来总结下优化前后对比情况。