Postgresql 百万查询优化案例学习

原文：https://www.cdsy.xyz/computer/soft/database/postgresql/230425/cd43192.html

通过学习别人的优化思路，有助于在以后碰到类似问题的时候可以回忆这些内容，当然最佳学习和实践是看完本文把这些优化手段用到过去的SQL中进行优化。

本文的所有优化手段只针对Postgresql数据库。

前置准备

BRIN索引 和 Gin索引

PostgreSql 不只有btree索引，还有brin索引和Gin索引 ：

BRIN索引：存储关于存储在表的连续物理块范围内的值的摘要，可以使用BRIN索引的特定操作符根据索引策略而变化

Gin索引：Gin索引是用来加快全文搜索的，适合做模糊查询和正则查询。

BRIN 和 BTree 比较

1. 空间上：BRIN相比BTREE索引在空间占用上小很多（数据量千万级时有几千倍之差），在存储空间占用上具备巨大优势。对于数据仓库或者VLDB应用可以节省大量的存储成本。
2. 查询上：对于等值的唯一查询，BTREE在性能上有显著优势。但在不同数据分布情况下，BTREE在性能上的领先优势差别非常大，从不到1倍到265倍。
3. 在选择创建BRIN还是BTREE索引时，需要权衡性能和空间使用两方面的影响。根据数据量、数据分布和SQL选择最适合的索引类型。在性能相差不大的情况下，选择BRIN可能是更加经济的选择。总体来说，当实际匹配数据量较少时，BTREE索引更加适合；反之，BRIN更加适合。
4. 由于BRIN索引的 lossy（有损） 特性，需要消耗较多的CPU时间用于精确匹配。

思考更多方案

1. 根据一定的条件进行分区（年月日等）
2. 增加内存大小
3. 根据不同的条件合理的时候相应的索引结构。
4. 硬件上的优化支持

Postgresql 的with语法

with的场景主要如下：

1. 小的业务场景，可以用作判断数据是新增还是修改，通过SQL的原子性操作一次性判断完成。
2. 大的业务场景当中，可以用作“辅助”，类似查询过程的“临时表”概念。

使用：在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

关联：[[【Postgresql】Update a PostgreSQL table using a WITH query]]

禁用顺序扫描

通过禁用顺序扫描强制走索引的方式提速：

SET enable_seqscan = OFF;

最终效果 - 执行计划查找

从结果来看，查询几乎全部走了索引，效率和性能也有了明显提升：

问题

sql 中用到了表连接，group by ，count以及sum 函数，这些十分消耗数据库性能的操作占用绝大多数的查询时间，但是根据作者描述，数据会不断有增量，到了200万的时候查询时间到了40S。

索引设计情况

因为是从Mysql转到Postgresql 使用，字段使用的都是Btree索引，这也是大部分情况下比较常见的选择，当然也是大部分人想得到的优化（包括我）。

CREATE INDEX indexname ON tablename (code)CREATE INDEX indexname ON tablename (code)

分析过程

优化前后SQL对比

具体分析之前先看下优化的SQL对比，整个SQL优化前后的写法差距还是挺大的，建议拿对比工具左右对比两个SQL的差别：

优化前的代码

select
  count(retrival.id) retrivalNum,
  retrival.index_code,
  retrival.camera_name,
  province."name" place,
  sum(end_time-start_time) retrivalDuration,
  retrival.province_code
from
   retrival
left join
   resource
on
  retrival.index_code = resource.index_code
left join
  province
on
  resource.province_code  = province.code    
 group by
retrival.index_code,resource.province_code,retrival.camera_name

WITH tm AS (
SELECT 
    retrival.index_code,
    COUNT
    ( retrival.index_code ) retrivalNum,
    SUM ( end_time - start_time ) retrivalDuration
FROM
    retrival
    JOIN province ON retrival.province_code = province.code 
WHERE
    retrival.start_time >= '' 
    AND retrival.end_time <= ''
    AND POSITION ( '100000' IN province.PATH ) > 0 
    GROUP BY
        retrival.index_code
    ORDER BY retrival.index_code
    LIMIT 25 OFFSET 0

)
SELECT 
    distinct retrival.index_code,
    retrival.province_code ,
    retrival.camera_name,
    province."name" place,
    t.retrivalNum, t.retrivalDuration
FROM  retrival
    JOIN tm t ON retrival.index_code = t.index_code
    JOIN  province ON retrival.province_code = province.code
    ORDER BY retrival.index_code
    LIMIT 25 OFFSET 0

增加关联字段

首先是简化表关联，resouce表后续被关联字段替代，从改动后结构猜测应该是在表retrival当中。

select
  count(retrival.id) retrivalNum,
  retrival.index_code,
  retrival.camera_name,
  province."name" place,
  sum(end_time-start_time) retrivalDuration,
  retrival.province_code
from 
  retrival
left join
   province
on
  retrival.province_code  = province.code
group by
retrival.index_code,retrival.province_code,retrival.camera_name,province.name

这部分实践起来比较简单，属于比较容易想到的优化手段，这里就不过多分析了。

对连接表关联到的字段加索引

胡乱加索引可能会有反效果（就像本文作者自己的捣鼓），所以加完索引之后不一定会改善。查询时间还是30s左右

其他零碎改动

对数据进行分页也是没有效果的，利用业务代码处理一部分SQL逻辑，这种处理方式同样不一定会提升效率，反而因为更多的查询拖慢业务处理速度。

关键改动

1. 联合索引优化掉单个索引。
2. 对于不同的字段类型应用不同的索引类型
   1. brin索引：主要用于比较操作
   2. gin索引：用于应对非前缀的模糊查询
3. 为了应用bri和grin，需要先安装扩展

安装扩展

create extension pg_trgm; 
create extension btree_gin; （必须管理员权限执行）

添加不同类型索引

create index indexname on tablename using gin (...) 

create index indexname on tablename using brin (...) with (pages_per_range=1)

个人顺带做了一下删除已有索引和添加grin索引的笔记：

DROP INDEX if exists "index_date" ;

CREATE INDEX if not exists index_date ON table USING brin (date);

索引分类和调整查询顺序之后，查询时间在15s左右。但是发现下面部分的查询依然很耗时：

group by
retrival.index_code,retrival.province_code,retrival.camera_name,province.name

关键改动：with 优化

在with 中只根据count里面的字段进行优化，消耗数据库性能的操作放到with中完成，其他字段根据with生成的“临时表”查询即可，最终通过合理的索引设计和查询字段顺序，实现分页查询执行时间只需要几百毫秒。

WITH tm AS (
    SELECT 
    retrival.index_code,
    COUNT
    ( retrival.index_code ) retrivalNum,
    SUM ( end_time - start_time ) retrivalDuration
FROM
    retrival
    JOIN province ON retrival.province_code = province.code 
WHERE
    retrival.start_time >= '' 
    AND retrival.end_time <= ''
--  AND retrival.camera_name LIKE'%研%' 
    AND POSITION ( '100000' IN province.PATH ) > 0 
GROUP BY
    retrival.index_code
ORDER BY retrival.index_code
LIMIT 25 OFFSET 0

    )
    SELECT 
    distinct retrival.index_code,
    retrival.province_code ,
    retrival.camera_name,
    province."name" place,
    t.retrivalNum, t.retrivalDuration
    FROM  retrival
    JOIN tm t ON retrival.index_code = t.index_code
    JOIN  province ON retrival.province_code = province.code
    ORDER BY retrival.index_code
    LIMIT 25 OFFSET 0

作者的原话：

导师提出了pgsql 的with 语句，此处，将group by 拆分，在with 中只根据count里面的字段进行统计，此处with 相当于将结果放入内存作为一个临时表。然后再从内存中关联其他需要的字段。这样优化之后分页查询执行时间只需要几百毫秒。

这里的思路挺好理解的，就是把所有设计函数的操作和计算放到with当中执行，为了提高处理速度同时也加了分页操作。即把一个一次性统计变为每次统计一小部分，最后合并统计结果的思路。

然而对比SQL会发现这里的group by去掉了三个条件：

retrival.province_code,retrival.camera_name,province.name

对应了“将group by 拆分，在with 中只根据count里面的字段进行统计” 这句话，少了三个列的group，去重操作用distinct替代，实际上这种写法在有索引的情况下并没有区别，分页查询数据使用distinct去重的开销也可以接受。

with之后的结果只有很少的一部分数据，查询也根据with的结果作为主表查询，最终结果同样进行分页，这时候优化其他字段就简单很多了（也就是开头作者所说的where字段添加索引即可），此外发现调整SQL之后分页也确实有了显著的提升查询速度的效果。

虽然加了with语句，但是整个语句可读性并没有复杂多少，还是比较好理解的。大部分SQL内容变化不大就不再介绍了。

distinct效率更高还是group by效率更高？

大致的结论是：

在语义相同，有索引的情况下： group by和distinct都能使用索引，效率相同。

在语义相同，无索引的情况下：distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发filesort，导致sql执行效率低下。

总结和收获

总结：

1. Btree 索引在Postgresql中并不是万金油，虽然是大部分通用业务选择，但是遇到特殊的SQL要选择合适的索引
2. grin索引适合非前缀索引查询，也就是“全文索引查询”。
3. brin 索引适合比较大小等的范围操作优化。
4. 多用联合索引+ 调整字段查询顺序优化查询。
5. with 是Postgresql当中非常强大的语法，分解复杂的语句的同时，具备“临时表”的效果。

收获：

• distinct 在某些场景要优于group，具体要看索引设计。
• with 划分出比较耗性能的函数和聚合操作，将耗时操作结果做成结果表，再根据结果表联表获取其他字段。
• 耗时的大量数据操作改为分页多次操作，数据统计查询更是如此。
• SQL写的较差的时候limit数据其实没啥卵用。
• 合理设计索引，Postgresql提供丰富的索引针对不同的查询条件做不同设计。

写在最后

Postgresql 的优化案例并不多，在加上Postgresql国内确实实践的少，中文文档也基本是机翻，难顶。