2亿数据量PostgreSQL 10.4查询调优思路分享_postgres 亿级统计如何做

目录

 

●背景

●使用物理服务器

●增加内存大小

●使用NVMe协议的固态硬盘

●将数据库安装在内存

●业务调整

●修改默认配置项

●启用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也已经简单到不能继续优化的地步，考虑了如下的调优思路。