标签归档:PostgreSQL

PostgreSQL 模糊查询

在MySQL中,只要字段对应建立B-Tree索引,那么前缀LIKE就可以走索引。不过在PostgreSQL中的设置就稍微复杂一点。

#建表和建索引
create table test(id int, name varchar(32));
create index idx on test(name);

#对应如下详细SQL语句
CREATE TABLE "public"."test" (
"id" int4,
"name" varchar(32) COLLATE "default"
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."test" OWNER TO "postgres";
CREATE INDEX "idx" ON "public"."test" USING btree ("name" "pg_catalog"."text_ops");

注意,字段“name”的字符检验是“default”,表示继承自数据库的设置。创建的索引类型是btree(默认),未指定字符校验集,类型模式是text_ops(默认)。这个情况下(数据库的校验集不是“C”),直接like ‘xxx%’是无法走索引的。

如果需要走索引,有两种方式:
1 建立索引时,明确指定校验集为“C”,查询时也指定校验集为“C”(如果数据校验集是“C”,不需要指定)

#建索引时指定
create index idx on test(name collate "C"); 

#查询时指定
like 'xxx%' collate "C"

这个索引的搜索是二进制搜索,效率高效。

2 建立索引时,明确指定字段类型对应模式,比如varchar对应varchar_pattern_ops,text对应text_pattern_ops,其它类推

create index idx on test(name varchar_pattern_ops);

使用类型对应的 pattern ops 时,索引搜索不仅支持 LIKE 的写法,还支持规则表达式的写法(name ~ ‘^abcd’)。使用pattern ops将使用字符查询而非binary查询的搜索方式。

一般,第二种方式比较常用(因为默认字符校验一般都不会是“C”), 基于btree的后缀模糊搜索(建索引时调用reverse()函数),跟上面的讨论一致。前后模糊搜索,无法应用到btree索引。

在PostgreSQL中,内置了一般数据库都没有的pg_trgm插件,还内置了表达式索引和GIN索引的功能。PostgreSQL 支持使用 pg_trgm 索引来加速同时包含前模糊和后模糊的查询。为使索引过滤有较好的效果,前模糊查询至少需输入 1 个字符,后模糊查询至少需输入 2 个字符。若要高效支持多字节字符(如中文),数据库的 lc_ctype 不能为 “C”,只有 TOKEN 分割正确才能有较好的效果。

#创建gin索引
create index idx_test on test using gin (c1 gin_trgm_ops);

#查询
select * from test where c1 like 'x%';
select * from test where c1 like '%xx';

注意:对于后默认查询(2个字符以上),并不需要像btree索引那样另外调用翻转函数建立索引。

对于前后模糊搜索,需要至少3个字符:

select * from test where c1 like '%xxx%'

PostgreSQL 入门

2010年9月20日发布了PostgreSQL 9.0
2011年9月12日发布了PostgreSQL 9.1(同步复制)
2012年9月10日发布了PostgreSQL 9.2(级联复制),CentOS 7.x默认YUM源会安装此版本。

大体应该是每年一个大版本:
2013 PostgreSQL 9.3
2014 PostgreSQL 9.4
2015 PostgreSQL 9.5
2016 PostgreSQL 9.6
2017 PostgreSQL 10.0

#CentOS 7中默认安装PostgreSQL 9.2,可以通过安装一个Yum包来获取最新的版本
yum install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

#安装服务端和第三方贡献包和客户端
yum install postgresql96.x86_64 postgresql96-contrib.x86_64 postgresql96-server.x86_64 

#初始化数据库(默认数据目录/var/lib/pgsql/9.6/data/)
/usr/pgsql-9.6/bin/postgresql96-setup initdb

#设置开机启动和启动PostgresSQL
systemctl enable postgresql-9.6.service
systemctl start postgresql-9.6.service

#PostgresSQL安装后就可以启动,安装时自动创建了一个叫postgres的系统用户 和 一个同名的用户名和数据库(postgres)
#设置数据库需要切换到postgres用户,以postgres用户进入PostgreSQL不需要密码
[root@localhost ~]# su postgres
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (9.6.3)
Type "help" for help.

#PostgreSQL默认会创建一个叫postgres的数据库,还有两个模板数据库template0和template1。
#用户新建数据库时默认从模板数据库template1克隆出来(可以定制这个模板库)。
#而template0是一个最简化的模板库,创建数据库时,如果明确指定从此数据库中继承,则创建一个最简化的数据库。
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

############################################################
#使用postgres用户,来生成其他用户和新数据库

\password postgres  #为postgres用户设置密码
CREATE USER dbuser WITH PASSWORD 'password'; #新建立用户和对应的密码
CREATE DATABASE exampledb OWNER dbuser;  # 创建用户的数据库
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;  #为用户授权

############################################################
#一般常用操作
#切换用户
su postgres
#运行psql终端
psql
#查看数据库
\l
#创建数据库
CREATE DATABASE test;
#切换数据库
\c test;
#创建数据库
CREATE TABLE t(id int primry key, name varchar(40));
#查看库中的数据表
\d
#查看具体的数据表结构
\d t
#查看更加详细的表结构
\d+ t

#显示SQL执行的时间
\timing on

postgres=# \q

登录,导入,命令:

#如果当前系统用户是dbuser,则-U dbuser可以省略
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

#如果要导入数据库
psql exampledb < exampledb.sql

一般命令:

\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。

基本配置:
1 监听地址和端口:

cd /var/lib/pgsql/9.6/data/
vi postgresql.conf

#listen_address = 'localhost'
#port = 5432

#############################
#如果修改了远程监听,还需要修改pg_hba.conf,添加一条记录
vi pg_hba.conf
host    all		all		all			trust

注:pg_hba.conf配置文件中的认证METHOD的ident修改为password,可以实现用账户和密码来访问数据库,其中这个认证标示有”trust”, “reject”, “md5”, “password”, “gss”, “sspi”

2 数据库日志

#日志收集
logging_collector = on

#日志目录(默认)
log_directory = 'pg_log'

#日志切换
# 每天生成一个新日志文件
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_ratation = off
log_rotation_age = 1d
log_roration_size = 0

#每当满一定大小则重建
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_ratation = off
log_rotation_age = 0
log_roration_size = 10M

#保留x天日志,循环覆盖
log_filename = 'postgresql-%a.log'
log_truncate_on_ratation = on
log_rotation_age = 1d
log_roration_size = 0

3 内存参数设置
shared_buffers: 共享内存的大小,主要用于共享数据块(缓存)
work_mem: 单个SQL执行时,排序、hash join所使用的内存
根据实际情况,可以适当调大。

————————————————————————————–
数据库逻辑结构
数据库:一个PostgreSQL实例下可以管理多个数据库。
表、索引:一个数据库中有很多表、索引。在PostgreSQL中表的术语为“Relation”(Table)。
数据行:每张表中有很多行数据。在PostgreSQL中行的术语为“Tuple”(Row)。

在PostgreSQL中,一个数据库包含一个或多个模式,模式中有包含了表、函数及操作符等数据库对象(模式可以理解为在数据库和表之间引入了一个命名空间,也即数据库-模式-表)。在PostgreSQL中不能同时访问不同数据库中的对象(需要重新连接到新库,MySQL可以切换库),而模式没有此限制(从这个概念上看,模式类似MySQL中的数据库级别的概念)。

要创建或访问模式中的对象,需要写上模式名(schema_name.table_name),通常创建和访问表时都不用指定模式,实际上这时访问的都是“public”的模式。当登录到该数据库是,如果没有特殊指定,都是以该模式(public)操作各种数据对象的。

默认,用户无法访问模式中不属于他们的对象。如要访问,模式的所有者必须在模式上赋予它们“USAGE”权限。用户也可以在别人的模式里创建对象,则需要被赋予在该模式上的”CREATE”权限。默认情况下每个人在public模式上都有CREATE和USAGE权限。

PostgreSQL支持两类临时表,一种是会话级的临时表,一种是事务级的临时表。

在PostgreSQL中,表空间实际上是为表指定一个存储的目录。在创建数据库时可以为数据库指定默认的表空间。创建表和索引时可以指定表空间,这样表、索引就可以存储到表空间对应的目录。

PostgreSQL使用多进程架构,每个连接会启动一个新的服务进程。不同于多线程方案,多进程架构通常使用共享内存来实现进程间通信,数据共享。PostgreSQL启动后,会生成一块共享内存用做数据库块的缓冲区。

数据目录结构:
使用环境变量PGDATA指向数据目录的根目录。目录的初始化是使用initdb来完成的。完成后这个数据目录下会生成三个配置文件(postgresql.conf实例配置文件,pg_hba.conf认证配置文件,pg_ident.conf认证方式ident的用户映射文件)

服务配置
1 连接配置项
listen_address
port
max_connections
superuser_reserved_connections 为超级用户连接保留的链接数
unix_socket_directory
unix_socket_group
unix_socket_permissions
bonjour
bonjour_name
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count

2 内存配置
shared_buffers 可以是专用内存的25%,比如1G,可以分250M(注意一个单位是8K,32M就是4000个8K,这里设置为4000)。
temp_buffers
work_mem
maintenance_work_mem
max_stack_depth

3 预写式日志配置
4 错误报告和日志项

访问控制配置文件
在PostgreSQL中,运行哪些IP的机器访问数据库服务器是由pg_hba.conf文件控制的。HBA的意思是host-based authentication,也就是基于主机的认证。

格式:

type	database	user	address		method

local	all		all			peer
host	all		all	127.0.0.1/32	ident
host	all		all	all		md5

第一字段是local,这个记录匹配通过UNIX域套接字的链接认证;是host时,这条记录匹配通过TCP/IP进行的连接(包括SSL和非SSL)。

认证方法有:trust、reject、md5和ident。
1 trust
无条件地允许连接。这个方法允许任何可以与PostgreSQL数据库服务器连接的用户以任意PostgresSQL数据库用户身份进行链接,不需要口令或其它任何认证
2 reject
无条件拒绝链接
3 md5
要求客户端提供一个MD5加密的口令进行认证
4 password
要求客户端提供一个未加密的口令进行认证
5 ident
运行客户端上的特定操作系统用户连接到数据库

查看系统信息的常用命令

#查看版本
select version();

#查看数据库启动时间
select pg_postmaster_start_time();

#查看加载配置文时间
select pg_conf_load_time();

#显示当前数据库时区
show timezone;

#查看当前实例有哪些数据库
psql -l #\l

#查看当前用户名
select user;

#查看Session用户
select session_user;