PostgreSQL 千万级以上的数据模糊查询、自动补全、分页浏览技术-企业应用专场（30页）.pdf

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,-开