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

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

3、记录数,是准确的记录数,不是评估记录数23*/24 create function paging_calculate_v2(25 in text,in text,in text,in anyelement,in integer,in integer,in boolean,in integer default 0,26 out page_ids bigint,out page_count bigint,out row_count bigint27)returns setof record28 as pg_kmcb,paging_calculate_v229 language C;select*f

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

5、个目录中 5/data/pgdata 6#WAL归档目录 7/data/archivedir 8#数据库test的表空间目录 9/data/test10#数据库test的索引表空间目录.索引表空间目录一般建议设置,因为当使用SSD和机械硬盘混合时,可以直接修改索引表空间的目录指至SSD11/data/idxtest 12#为每个数据库单独创建用户而不是使用postgres用户,同时在每个数据库上回收public权限13 revoke all on database test from public;pg_hba.conf配置，配置完成后无需重启数据库，reload即可。1#本机允许所有 2 h

6、ost all all 127.0.0.1/32 md5 3#其它数据库只允许指定的用户登录指定的数据库,再把密码设置复杂一点就可以了,怎么攻都没用 4 host dbname dbuser 0.0.0.0/0 md5创建数据表（1）1 drop index if exists idx_enterprises_keys;2 drop table if exists enterprises;3 create table enterprises(4 objectid bigserial not null,-唯一编号 5 name text not null,-企业名称 6 bank text,-开

7、户银行 7 registered text,-纳税人登记号 8 number text,-账号 9 address text,-企业地址10 post text,-企业邮编11 legal text,-法人代表12 contact text,-联系人13 tel text,-联系电话14 fax text,-传真15 mail text,-邮箱16 other jsonb,-企业备注或其它(至少包含other-备注)17 constraint pk_enterprisess_objectid primary key(objectid)with(fillfactor=80)using index

8、 tablespace idxtest18)with(fillfactor=80,19 autovacuum_enabled=true,toast.autovacuum_enabled=true,20 autovacuum_vacuum_threshold=500,autovacuum_analyze_threshold=1000,21 toast.autovacuum_vacuum_threshold=500);22-为了便于查看验证,id从1开始23 select setval(pg_get_serial_sequence(enterprises,objectid),1,false);创建

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

10、y(objectid)with(fillfactor=80)using index tablespace idxtest,7 constraint fk_cond_enterprises_objectid foreign key(objectid)references enterprises(objectid)on delete cascade 8)with(fillfactor=80,9 autovacuum_enabled=true,toast.autovacuum_enabled=true,10 autovacuum_vacuum_threshold=500,autovacuum_ana

11、lyze_threshold=1000,11 toast.autovacuum_vacuum_threshold=500);12-创建索引13-之所以没用rum是因为rum写太实在是太慢了,相对来说rum在大多数应用查询速度不是那么明显14 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参数，当你使用一段时间后修改这个参数对历史数据是无效的。如果需要对历史数据也生效的话，你不得不做VACU

13、UMFULL，FULL会获取表上的独占锁，阻止所有操作(包括SELECT)，FULL实际上创建了一个表的副本(也就是重新复制了一个表)，如果历史数据比较多的话复制表非常慢（可能是几小时或几天）。同时同时fillfactorfillfactor结合结合autovacuumautovacuum可以最大程度可以最大程度上上避免表膨胀、提升查询效率避免表膨胀、提升查询效率。表中包含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位的数字组成关键字关键字:

15、由由“企业企业名称名称 法定代表人法定代表人 联系人联系人 联系联系电话电话”切分组成，切分组成，每每2 2个字做为一个个字做为一个词处理。当然你也词处理。当然你也可以可以使用分词技术生成关键字使用分词技术生成关键字(例如结巴例如结巴).).测试时使用数字查询返回的结果最多测试时查询使用非数字没有返回的结果生产环境各种情况都有，返回的结果相对均衡好差插入测试数据timing ondo$declarebeginfor i in 1.1000 loopinsert into enterprises(name,bank,registered,number,address,post,legal,con

16、tact,tel,fax)selectgenerate_rand_string(8,32,1)as name,generate_rand_string(8,32,1)as bank,generate_rand_string(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_str

17、ing(2,5,1)as contact,format(%s%s,1,generate_rand_string(10,10,1)as tel,format(087%s-%s,generate_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(fill

18、factor参数是参数是PostgreSQL最重要的参数，表最重要的参数，表fillfactor默认为默认为100%，B树索引树索引fillfactor默认为默认为90%。电话：。电话： ,(+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 u

19、nnest(case when vals1(+86|86)?(10-910)$then-判断手机号 split_string(vals3,2)-只切11位手机号，忽略区号 when vals4(0-93,4)|(（0-93,4）)|(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

20、-Z0-9_+)$then-判断英文数字下划线 split_string(lower(vals12),2)when vals13(a-zA-Z1,u3007u3400-u4db5u4e00-u9fcbuf900-ufa2d+)$then-判断汉字 split_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,

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

22、秒。createindexidx_cond_enterprises_keysoncond.enterprisesusinggin(keystsvector_ops)tablespaceidxtest;这里特别指出如果用rum索引写入比gin索引速度更慢。关键字自动补全关键字自动补全根据用户输入的关键字显示最新的10条数据.实际上就是执行下面的sqlselect name from enterprises where(name like%任意内容%)or(legal like%任意内容%)or(contact like%任意内容%)or(tel like%任意内容%)order by id de

23、sc limit 10看到这个大家第一反应肯定觉得很简单吧但是系统设计要求在1kw数据量时关键字自动补全响应时间要求100ms,但是limit 10某些关键字会对执行计划有比较大的影响,上面的sql很难达到设计要求的指标。关键字自动补全（使用Limit）输入的值存在且数量较多,查询较快explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(139)order by objectid desc limit 10;输入的值存在返回的数量较少,查询较慢

24、explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises 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;关键字自动补全（不使

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

26、ts,buffers,timing)select objectid from cond.enterprises where keystoTsquery(中国);关键字自动补全（两种方案对比）使用使用limit不使用不使用limitkeys列中包含用户输入的数据且较多时,响应速度非常快。keys列中包含用户输入的数据且较多时,响应速度非常慢。keys列中不包含用户输入的数据或数据较少时，响应非常慢。keys列中不包含用户输入的数据或数据较少时，响应非常快。实测结果包含limit和不包含limit它们完全是相互矛盾的，执行计划也是完全不同的。用户输入的关键字是不可控的我们可能遇到过同一SQL因关键

27、字不同忽快忽慢的问题,可能的原因一是输入的关键字可选择性太差（数量比较多），还有就是limit的问题，因此开发人员在设计时应充分考虑各种可能性，避免发生此类问题。关键字自动补全优化方案一方案一在程序中执行自动补全功能前设置稍微大一点statement_timeout时间,例如设置setstatement_timeoutto100（单位为毫秒）,当超过这个时间时就认为keys列中不包含用户输入的数据.statement_timeout超时后抛出一个SQLstate:57014的异常,忽略这个异常.statement_timeout需要在程序中实现,执行完自动补全功能后需要确保恢复原来设置stat

28、ement_timeout时间.特点：开发简单,但有很大的概率不会返回任何数据，换句说也就是查询成功率非常低。数据量和成功率成反比。关键字自动补全优化方案二方案二在程序中使用多线程同时执行如下二个sql,谁先完成用谁的数据,同时取消执行另一个sql.select objectid from cond.enterprises where keys(13:tsquery)order by objectid desc limit 10;select objectid from cond.enterprises where keys(13:tsquery)order by objectid desc;

29、它们之间的区别是否包含limit,其它一样.缺点：在响应速度和准确性方面均可满足需求,但是程序复杂度呈几何陪数提升。关键字自动补全优化方案二蓝色背景部是由应用程序控制(无需控制或不可控),从白色背景开始由程序员处理.对象池大小和处理进程数量可能不一至,对象池中的对象全部使用时要注意处理.由于自定义对象池中的连接是长连接,在设置数据库连接参数字符串时不要设置连接池,同时为防止PostgreSQL被OOM killer(Out Of Memory killer),有效利用PostgreSQL服务器的内存,自定义对象池中的连接每使用一定的次数时(例如1000次)关闭连接并重新打开.在前面的视频中可以

30、看到，同一关键字第一次搜索时需要20秒左右，但第二次搜索不到200毫秒，这种情况说明了二个问题：1.数据没有加载至shared_buffers中，第一次使用时需要从磁盘中读取这个数据。解决方法是数据库启动时预热数据，同时索引表空间设置至SSD可加快预热速度；2.服务器物理内存太小shared_buffers中的一部份数据一段时间没有使用后被丢弃了，再次使用需要从磁盘读取。解决方法是加大服务器物理内存并合理配置shared_buffers、temp_buffers,work_mem参数可以缓解此问题。关于PostgreSQL开发中其它注意事项在决定使用什么技术之前结合需求测试很重要;如果单表数据没达到一定的规模建议不要分库分区，不正确的使用分区表带来的不是性能提升而是下降，千万级以下的数据量不需要考虑分区；数据库连接数不是越多越好，是和您的服务器硬件密切相关。Web程序一般连接数采用cpu内核数量的2倍或4倍即可(连接数最好为cpu内核数的倍数)，开几百上千的连接性能反而下降；数据库和sql优化请结合需求和具体的执行计划优化。