PostgreSQL数据分页技术概述

1、PostgreSQL 千万级以上的数据 模糊查询、自动补全、分页浏览技术模糊查询、分页是开发中是使用比较高频的技术。本文介绍在大数据量时如何高效的使用模糊查询和数据分页浏览，同时根据后台设计降低用户界面的使用复杂程度。数据分页浏览技术是开发中基本都会用到的技术,常用的分页方法有以下4种：1.将全部数据读取到内存,然后再内存中分页.稍有经验的人都不会使用；2.使用limit offset效果不是很好,翻页越到后面速度越慢；3.使用“select * from t where id(传入上一页最后一个id) order by id limit 20”.效率比较好,适合手机屏幕向下滑动分页取数据的方

2、 式.因为只能逐页浏览而不能跳页,因此不适合应用系统；4.去除历史数据,使用较少的较新的数据来实现,采用比较多的方式.基于PostgreSQL数据库 自主研发的分页插件介绍使用简单,只要创建普通的常规表,无需对表做特别的优化(无需分库分表),1千万级数据量时全表分页(不带条件)响应 时间5秒,带条件时500ms,关键字全表检索时= 0 每页显示的记录数,值范围10-1000 排序,升序或降序$5:$6:$7:$8:最大页数,设置为0表示不限制,读取所有记录,否则只返回指定的页数返回值:只有一行三列的记录集1列:指定页的id范围数组2列:符合条件的页数3列:符合条件记录数,是准确的记录数,不是评

3、估记录数1 /*2 *3 *4 *5 *6 *7 *8 *9 *10 *11 *12 *13 *14 *15 *16 *17 *18 *19 *20 *21 *22 *23 */24 create function paging_calculate_v2(252627 )28in text,in text,in text,in anyelement,in integer,in integer,in boolean,in integer default 0,out page_ids bigint, out page_count bigint,out row_count bigint return

4、s setof recordas pg_kmcb, paging_calculate_v229 language C;使用方法1.计算指定页的所有ID、页数、符合条件的记录数2.输出指定页的数据select * from enterprises where objected=any(page_ids) order by objectid创建数据库建议#数据库数据根目录.这个目录如果能直接mount比较好,和操作系统分开,这样比较安全.操作系统出问题后重新mount这个目录就可以快速恢复.#Windows双击热备也是这个原理,数据库数据根目录直接放到活动硬盘上/data#postgresql系统

5、目录,也是initdb时的目录.PostgreSQL的系统配置如postgresql.conf,pg_hba.conf等都在这个目录中/data/pgdata#WAL归档目录/data/archivedir#数据库test的表空间目录/data/test#数据库test的索引表空间目录.索引表空间目录一般建议设置,因为当使用SSD和机械硬盘混合时,可以直接修改索引表空间的目录指至SSD/data/idxtest#为每个数据库单独创建用户而不是使用postgres用户,同时在每个数据库上回收public权限revoke all on database test from public;pg_hb

6、a.conf配置，配置完成后无需重启数据库，reload即可。1 #本机允许所有2 hostallall127.0.0.1/32md5#其它数据库只允许指定的用户登录指定的数据库,再把密码设置复杂一点就可以了,怎么攻都没用hostdbnamedbuser0.0.0.0/0md5创建数据表（1）drop index if exists idx_enterprises_keys;drop table if exists enterprises;create table enterprises(-唯一编号objectid bigserial not null, name text not null,

7、bank text, registered text, number text, address text, post text,legal text, contact text, tel text,fax text, mail text, other jsonb,-企业名称-开户银行-纳税人登记号-账号-企业地址-企业邮编-法人代表-联系人-联系电话-传真-邮箱-企业备注或其它(至少包含other-备注)4567891011121314151617constraint pk_enterprisess_objectid primary key (objectid) with (fillfact

8、or=80) using index tablespace idxtest18 )with (fillfactor=80,192021autovacuum_enabled=true,toast.autovacuum_enabled=true, autovacuum_vacuum_threshold=500,autovacuum_analyze_threshold=1000, toast.autovacuum_vacuum_threshold=500);-为了便于查看验证,id从1开始select setval(pg_get_serial_sequence(enterprises,objecti

9、d), 1, false);创建查询条件表（2）1 create schema cond;2 create table cond.enterprises(objectid bigint not null, divid bigint not null, keys tsvector not null,-唯一编号,外键enterprises- objectid-行政区唯一代码,外键divisions-objectid,divisions由程序员维护，因此不需要外键-关键字(name,legal,contact,tel)34567constraint pk_cond_enterprises_objec

10、tid primary key (objectid) with (fillfactor=80) using index tablespace idxtest, constraint fk_cond_enterprises_objectid foreign key(objectid) references enterprises(objectid) on delete cascade8 ) with (fillfactor=80,91011autovacuum_enabled=true,toast.autovacuum_enabled=true, autovacuum_vacuum_thresh

11、old=500,autovacuum_analyze_threshold=1000, toast.autovacuum_vacuum_threshold=500);-创建索引-之所以没用rum是因为rum写太实在是太慢了,相对来说rum在大多数应用查询速度不是那么明显create index idx_cond_enterprises_keys on cond.enterprises using gin(keys tsvector_ops) tablespace idxtest; 分为数据表和查询条件表，keys关键字包含的“企业名称法定代表人联系人电话号码”分词 后的内容，分开的目的是更新主表

12、非企业名称法定代表人联系人电话号码字段时,无需更新 关键字表,同时便于管理.当然不分也可以.创建表和索引时要设置fillfactor,同时注意指明索引的索引表空间。创建表和索引时的fillfactor参数fillfactor参数是PostgreSQL最重要的参数之一，表fillfactor默认为100%，B-树索引fillfactor默认为90%。我们都知道PostgreSQL最小存储单位为页，每页默认大小为8K（可以在编译时修改）。如果不在 创建表时根据需求（主要是update和delete频繁程度）设置fillfactor参数，当你使用一段时间后 修改这个参数对历史数据是无效的。如果需要对

13、历史数据也生效的话，你不得不做VACUUM FULL， FULL会获取表上的独占锁，阻止 所有操作(包括SELECT)， FULL实际上创建了一个表的副本(也就是重新复制了一个表)，如果历史 数据比较多的话复制表非常慢（可能是几小时或几天）。同时fillfactor结合autovacuum可以最大程度上避免表膨胀、提升查询效率。表中包含char或varchar类型字段（长度较小）时，在创建表完成后注意修改char或varchar的 存储方式（默认为EXTENDED，修改为plain）否则事后修改不得不VACUUM FULL。测试数据组成测试数据全部由数字组成,全由数字组成的测试数据重覆概率比较

14、高,相同的条件值返 回的数据最多,因为这是最恶劣的情况,在此情况测试的结果到生产环境会好很多.唯一编号:自动增长行政区唯一代码:随机在1-17内生成,1-17是行政区表北京市及下级辖区的唯一编号. 企业名称:8-32位随机的数字组成开户银行:8-32位随机位的数字组成纳税人登记号:12-13位随机的数字组成 账号:12位数字组成地址:8-32位随机的数字组成 邮编:6位的数字组成法定代表人:2-5位随机的数字组成 联系人:2-5位随机的数字组成联系电话:11位的数字组成关键字:由“企业名称法定代表人联系人联系电话”切分组成， 每2个字做为一个词处理。当然你也可以 使用分词技术生成关键字(例如结

15、巴).测试时使用数字查询返回的结果最多测试时查询使用非数字没有返回的结果生产环境各种情况都有，返回的结果相 对均衡好差插入测试数据timing on do $declarebeginfor i in 1.1000 loopinsert into enterprises(name,bank,registered,number,address,post,legal,contact,tel,fax) selectgenerate_rand_string(8,32,1) as name, generate_rand_string(8,32,1) as bank, generate_rand_strin

16、g(12,13,1) as registered, generate_rand_string(12,12,1) as number, generate_rand_string(8,32,1) as address, generate_rand_string(6,6,1) as post, generate_rand_string(2,5,1) as legal, generate_rand_string(2,5,1) as contact, format(%s%s,1,generate_rand_string(10,10,1) as tel, format(087%s-%s,generate_

17、rand_string(1,1,1),generate_rand_string(7,7,1) as faxfrom generate_series(1,1000); raise notice %, i;end loop;end;$;开10个进程,每个进程插入100w数据,1千万数据全部插入完成约1分钟。关键字切分示例with cte as(select vals from regexp_matches(fillfactor参数是PostgreSQL最重要的参数，表fillfactor默认为100%，B树索引fillfactor默认为90%。电话0871-6833782

18、2,(+86|86)?(10-910)|(0-93,4)|(（0-93,4）)|(0-94,5-)?(0-97,8)|(-+?0-9*.?0-9+(eE-+?0-9+)?)|(a-zA-Z0-9_3,)|(a-zA-Z1,u3007u3400-u4db5u4e00-u9fcbuf900-ufa2d1,),g) as vals),repeat1 as(select unnest(case when vals1(+86|86)?(10-910)$ then-判断手机号split_string(vals3,2)-只切11位手机号，忽略区号when vals4(0-93,4)|(（0-93,4）)|(

19、0-94,5-)?(0-97,8)$ then -判断固定电话或传真号码split_string(vals9,2)-只切7-8位电话号码，忽略区号when vals10(-+?0-9*.?0-9+(eE-+?0-9+)?)$ then -判断带符号数字整数|浮点数|指数split_string(vals10,2)-只切整数，小数不切 when vals12(a-zA-Z0-9_+)$ then -判断英文数字下划线split_string(lower(vals12),2)-判断汉字when vals13(a-zA-Z1,u3007u3400-u4db5u4e00-u9fcbuf900-ufa2

20、d+)$ thensplit_string(lower(vals13),2)end) as val from cte) select array_agg(val) from (select val from repeat1 where val is not null group by val order by val) as tmp;正则表达式切分后的关键字：00,01,10,13,22,24,33,35,37,46,57,68,78,79,80,82,83,90,92,ac,b树,ct,es,fa,fi,gr,il,lf,ll,l最,or,os,po,ql,re,r参,r默,sq,st,tg

21、,to,参数,的参,默认,认为,数是,树索,索引,要的,重要,最重插入关键字数据关键字用正则提取并切分后，用一个进程写入查询条件表，单进程的目的是评估gin和rum索引的写入速度，所有数据随机分布在北京市或北京市下辖的地区。因为索引和正则提取的原因，在本案例一个进程提取并保存1千万数据的关键字用时为 Time: 3300734.480 ms (55:00.734)，约为3000条/每秒。create index idx_cond_enterprises_keys on cond.enterprises using gin(keys tsvector_ops) tablespace idxtes

22、t;这里特别指出如果用rum索引写入比gin索引速度更慢。关键字自动补全关键字自动补全根据用户输入的关键字显示最新的10条数据.实际上就是执行下面的sqlselect name from enterprises where (name like %任意内容%) or (legal like %任意内 容%) or (contact like %任意内容%) or (tel like %任意内容%) order by id desc limit 10看到这个大家第一反应肯定觉得很简单吧但是系统设计要求在1kw数据量时关键字自动补全响应时间要求100ms,但是limit 10某些关键字 会对执行计

23、划有比较大的影响,上面的sql很难达到设计要求的指标。关键字自动补全（使用Limit）输入的值存在且数量较多,查询较快explain (analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(139) order by objectid desc limit 10;输入的值存在返回的数量较少,查询较慢explain (analyze,verbose,costs,buffers,timing)select objectid from cond.enterprise

24、s where keystoTsquery(YNHX) order by objectid desc limit 10;输入的值不存在,查询较慢explain (analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(中国) order by objectid desc limit 10;关键字自动补全（不使用Limit）输入的值存在且返回的数量较多,查询较慢explain (analyze,verbose,costs,buffers,timing)select

25、 objectid from cond.enterprises where keystoTsquery(139);输入的值存在返回的数量较少,查询较快explain (analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(YNHX);输入的值不存在,查询较快explain (analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery

26、(中国);关键字自动补全（两种方案对比）使用limit不使用limitkeys列中包含用户输入的数据且较多时,响 应速度非常快。keys列中包含用户输入的数据且较多时,响 应速度非常慢。keys列中不包含用户输入的数据或数据较 少时，响应非常慢。keys列中不包含用户输入的数据或数据较 少时，响应非常快。实测结果包含limit和不包含limit它们完全是相互矛盾的，执行计划也是完全不同的。用户输入的关键字是不可控的我们可能遇到过同一SQL因关键字不同忽快忽慢的问题,可能的原因一是输入的关键字可选择性太 差（数量比较多），还有就是limit的问题，因此开发人员在设计时应充分考虑各种可能性，避 免

27、发生此类问题。关键字自动补全优化方案一方案一在程序中执行自动补全功能前设置稍微大一点statement_timeout时间,例如设置set statement_timeout to 100（单位为毫秒）,当超过这个时间时就认为keys列中不包含用 户输入的数据.statement_timeout超时后抛出一个SQL state: 57014的异常,忽略这个异常.statement_timeout需要在程序中实现,执行完自动补全功能后需要确保恢复原来设置statement_timeout时间.特点：开发简单,但有很大的概率不会返回任何数据，换句说也就是查询成功率非常低。数 据量和成功率成反比。关键字自动补全优化方案二方案二在程序中使用多线程同时执行如下二个sql,谁先完成用谁的数据,同时取消执行另一 个sql.select objectid from cond.enterprises where keys(13:tsquery