2亿数据量PostgreSQL 10.4查询调优思路分享

最新推荐文章于 2023-02-02 14:55:59 发布

色诺芬0x7C8

最新推荐文章于 2023-02-02 14:55:59 发布

阅读量1.3w

点赞数 6

分类专栏： PostgreSQL 文章标签： PostgreSQL 数据库性能调优 GIN GIST

本文链接：https://blog.csdn.net/jui121314/article/details/84584848

版权

本文分享了在PostgreSQL 10.4上处理2亿数据量的查询优化经验，包括背景、硬件升级、内存使用、索引优化（如启用GIN和GIST索引）以及业务和配置调整等策略，旨在解决非前缀模糊查询的性能瓶颈。

摘要由CSDN通过智能技术生成

●启用Gin (Generalized Inverted Index)索引（仅适用于非前缀模糊查询）

●启用Gist(Generalized Search Tree)索引（仅适用于非前缀模糊查询）

●小结

●背景

最近工作中遇到了数据库的查询瓶颈，自己研究后，准备写一篇博文记录一下。先交代一下背景，这次调优针对的是PostgreSQL 10.4，安装环境为CentOS Linux release 7.2.1511（位于虚拟机），分配CPU为Intel Xeon Silver 4114的4个内核，64GB内存，以及400GB的SSD空间。表单包含158列，按月进行分区，采用PostgreSQL 10.4的partition by range分了一年的分区。并且对主键和待查询的字段“identity_no”建立的Btree索引。这里的identity_no用来区分一个地理位置，由省份简称+市区邮编+4位数字英文混合编号组成，大家只需知道它是一个含中文汉字+数字+英文的varchar格式的字段即可，它并不是一个唯一的值，表单中存在重复值。

CREATE INDEX "idx_info_201903_1" ON "public"."info_201903" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);

CREATE INDEX "idx_info_201903_2" ON "public"."info_201903" USING btree (
  "identity_no" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

测试的SQL很简单，结合业务采用分页的方式，先测总的记录条数，再取每页的结果集。测试精确查询、带前后缀的模糊查询：

SELECT count(*) FROM info WHERE identity_no = '沪200000C544' ;

SELECT * FROM info WHERE identity_no= '沪200000C544' LIMIT 30 OFFSET 50000;

SELECT count(*) FROM info WHERE identity_no like '京102%' ;

SELECT * FROM info WHERE identity_no like '京102%' LIMIT 30 OFFSET 50000;

SELECT count(*) FROM info WHERE identity_nolike '%S888' ;

SELECT * FROM info WHERE identity_no like '%S888' LIMIT 30 OFFSET 50000;

整个测试环境下，单表分12个区，共计2亿条数据。实际测试中，该字段进行精确查询时，平均耗时在10-200ms这个范围内；但模糊查询时，特别是无前缀的情况下，耗时均超过5分钟，导致业务严重不可用。即使是带前缀的查询，通过执行计划(explain)查看，虽然使用了索引，但由于数据量实在过于庞大，查询依旧很慢。因为分区也做了、索引也用了、SQL也已经简单到不能继续优化的地步，考虑了如下的调优思路。

●使用物理服务器

测试过程中使用的是虚拟机作为服务器，性能势必没有直接使用物理服务器高，实际生产过程中建议使用物理服务器作为数据库服务器。

●增加内存大小

通过Linux命令top查看，目前PostgreSQL已经申请了64GB内存的近62GB作为缓存使用，而且在执行计划中可以看到有不少查询会进行Bitmap Heap Scan节点操作，该节点会将索引查出的数据一次取出放入内存进行排序，如果取出的数据过多，内存放不上，势必会分步操作，将部分数据先存放磁盘，再加载进内存，如此往复，势必没有直接一次放入内存的效果好。

●使用NVMe协议的固态硬盘

通过Linux命令iostat查看，最大的瓶颈来源于磁盘I/O，已经处于满负荷运转了。虚拟机使用的是固态硬盘，该硬盘使用AHCI协议，SATA3.0接口，理论顺序读取速度仅500MB/s，实际使用中因其他进程等影响，读取速度会更慢。若更换为NVMe协议的固态硬盘，PCI-E或U.2的接口，理论带宽32Gbps，能极大提高磁盘I/O速率。

●将数据库安装在内存

PostgreSQL不能直接装在内存，只能将高频数据加载进内存。若想把程序和所有数据全部安装在内存可以考虑使用曲线救国的方案。即将内存虚拟为硬盘，把内存当硬盘使用，但由于内存的读写效率是远高于机械硬盘，甚至固态硬盘的，因此这种情况下I/O效率会非常高。对于Linux系统，可以采用挂载分区的方式，将内存等同于硬盘逻辑分区使用；对于Windows系统，可以采用Primo Ramdisk软件将内存虚拟为硬盘。这种方案风险很高，即使使用UPS进行不间断供电，解决断电时数据擦除问题，也无法保证内存硬件故障时数据丢失；同时，这种方案的经济成本也会很高。

●业务调整

此条方案适合特定的查询。例如在2亿量级数据查询时，用户即使只选择某几天时间段的数据，因为数量实在太多(数十万甚至上百万条)，按原本分页需求，需要先查询总记录条数，再查询所选页的记录数。通过执行计划可以看到，耗时慢查询主要是前者，后者返回相对较快。即使总记录条数可以使用异步的方式返回给前端页面，用户依旧需要等两三分钟才能看到共多少条记录以及共多少页。与其这样，不如设定一个阈值，例如5秒内未查询出记录条数，直接异步通知前端页面条数10000+，同时不再去执行count(*)的操作，减少系统I/O开销。这种非精确的计数业务上并不会造成不好的用户体验，即使百度采用搜索引擎来做查询，返回的总数也是用“约”进行修饰的。

用户可以正常翻页，如果其输入的页码过大，导致查询不到数据，页面只需做一个友好跳转进行提示即可。因此没必要在此业务环境去浪费过多的I/O资源。需要查询某时间段内数据总数的统计分析业务环境再单独去进行完整的count(*)操作，或者使用一些预加载的技术。

●修改默认配置项

修改postgresql.conf中的默认配置项，根据网上的一些案例实际测试修改过random_page_cost = 1；fsync = off；parallel_tuple_cost = 0.1；max_parallel_workers_per_gather = 4。通过观察发现实际收效很低，几乎无明显变化&#x