1. Psql 安装 （CentOS-7.x）
采用yum安装psql是最简洁高效的
# 更新一下yum
yum update -y
# 直接安装
yum install postgresql-server -y
# 初始化数据库
postgresql-setup initdb
# 加入linux的服务管理
systemctl enable postgresql.service
# 启动服务
systemctl start postgresql.service
安装完成之后，我们可能需要配置一下，Psql有两个配置文件
/var/lib/pgsql/data/postgres.conf
如果你需要远程访问，请取消注释，并吧localhost改成*，我本地安装，只为了自己平时使用，所以我就默认监听localhost就可以了
postgres.conf
/var/lib/pgsql/data/pg_hba.conf
Psql 初始化过后，默认提供一个postgres用户和一个postgres数据库，但是我们直接psql -U postgres -d postgres命令是连接不上的，因为在默认配置里，当前登录的账户必须是postgres，才能连接
所以我们改一改，认证方式改成md5或者password（不建议），默认是没有密码的，所以要输入密码的时候，回车就好了
更粗暴的方式是直接把认证方式改为trust，这样凡是登录到这台电脑的用户都被信任，个人电脑可以这么干了
更详细的配置可以去看看官方文档
pg_hba.conf
这个时候，我们重启服务，就可以登录了
systemctl restart postgresql.service
psql -U postgres -h localhost -w
2. Psql 关于全文检索的支持（tsvector和tsquery）
个人觉得，Psql对全文检索还是很友好了，以前还是通过Apache Lucene实现的，最近看看Psql，简单了不少
Psql 提供两个数据类型tsvector，tsquery，并且通过动态检索自然语言文档的集合，定位到最匹配的查询结果
一个
tsvector的值是唯一分词的分类列表，把一话一句词格式化为不同的词条，在进行分词处理的时候，tsvector会自动去掉分词中重复的词条，按照一定的顺序装入，例如
# psql的自动分词（默认是英文分词，中文分词后面讲）
select 'hello world'::tsvector;
# 输出
tsvector
-----------------------------
'hello' 'world'
# 可以设置词条位置常量
select 'hello:3 world:1'::tsvector;
# 输出
tsvector
-----------------------------
'hello':3 'world':1
这个位置信息通常就是当前文档中单词所处的位置，这个位置信息用于关注度的体现。位置信息常量的值的范围为1 到 16383。分词后，会把相同词条的位置记录到一个词条中
tsquery，相当于是查询tsvector的查询条件，并且可以联合使用boolean 操作符来连接, & (AND), | (OR), and ! (NOT). 使用括号(),可以强制分为一组
在全文检索的过程中，tsquery与tsvector之间采用@@操作符
select 'hello world fuck you hahahaha'::tsvector @@ 'hello & you'::tsquery;
?column?
------------------
t
对于英文全文检索应用来说,很多时候的句子是非标准化的,但是tsvector和tsquery是不会知道的,为处理加工的文本应该通过使用
to_tsvector函数
# 采用psql提供的函数帮助我们将文本分词
select to_tsvector('hello world');
to_tsvector
---------------------
'hello':1 'world':2
# 采用psql提供的函数进行全文检索
select to_tsvector('hello world fuck you hahah') @@ to_tsquery('hello & you');
?column?
------------------
t
在Psql中
@@操作符会将两边的文本进行自动的转换
- tsvector @@ tsquery
- text @@ tsquery
- text @@ text
- 后两种,text @@ tsquery 等同于 to_tsvector(x) @@ y.
- 而 text @@ text 等同于 to_tsvector(x) @@ plainto_tsquery(y).
plainto_tsquery() 用于将文本转为tsquery，这样就不用每次都去写 & 条件了
3. Psql 索引类型
我们也要为全文检索建立索引，提升查询效率，我们为全文检索建立的索引是Gin索引
几种基本索引类型
B-Tree
CREATE INDEX test1_id_index ON test1 (id);
B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引(MySQL则不会)。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。
Hash
CREATE INDEX name ON table USING hash (column);
散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。
这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。
GiST
CREATE INDEX name ON table USING gist (column);
GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。
GIN
CREATE INDEX name ON table USING gin (column);
GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：<@、@>、=、&&等。
两种建立索引方式
# 我新建一个posts表，记录我的博客内容，平时需要根据博客名和内容做查询
create table posts (
id integer,
title varchar(50) not null,
content text not null,
primary key(id)
);
- 为tsv函数建索引
create index post_tsvcontent_idx on posts using gin(to_tsvector('english', title || content));
- 新增一个tsv列，为列建立索引（推荐）
alter table posts add_column tsv_content tsvector;
update posts set tsv_content = to_tsvector('english', coalesce(title,'') || coalesce(content,''));
create index post_tsvcontent2_idx on posts using gin(tsv_content);
现在都可以对posts表里的内容进行全文检索了
# 例如
select title from posts where to_tsvector(title || content) @@ plainto_tsquery('hello world');
# 或者
select title from posts where tsv_content @@ plainto_tsquery('hello world');
4. 用触发器更新分词字段
如果我们是单独新建一列去存放tsvector的分词内容，为了简便，我们希望在插入记录过后，自动对需要检索的列生成分词，所以我们就需要触发器
# Psql 提供了两个触发器函数用于实现此功能
tsvector_update_trigger()
tsvector_update_trigger_column()
# 当然，你也可以写自己的触发器
tsvector_update_trigger() 的使用
# 非常简单
CREATE TRIGGER trigger_posts_tsv_content BEFORE INSERT OR UPDATE
ON posts FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv_content, 'english', title, content)
5. 采用zhparser支持中文分词
zhparser是基于Simple Chinese Word Segmentation(SCWS)中文分词库实现的一个PG扩展，作者是 amutu，源码URL为https://github.com/amutu/zhparser
安装SCWS
wget -q -O - http://www.xunsearch.com/scws/down/scws-1.2.2.tar.bz2 | tar xf -
cd scws-1.2.2
./configure
make install
安装zhparser
git clone https://github.com/amutu/zhparser.git
cd zhparser
SCWS_HOME=/usr/local make && make install
配置zhparser扩展
# 连接到目标数据库，创建zhparser解析器
create extension zhparser
# 将zhparser解析器作为全文检索配置项
create text search configuration chinese (PARSER = zhparser);
然后通过\dF和\dFp命令查看配置
demo
指定分词策略
zhparser可以将中文切分成下面26种token
# 连接到目标数据库
select ts_token_type('zhparser');
demo
普遍情况下，我们只需要按照名词(n)，动词(v)，形容词(a)，成语(i),叹词(e)和习用语(l)6种方式对句子进行划分就可以了，词典使用的是内置的simple词典，即仅做小写转换
所以添加如下映射
alter text search configuration chinese add mapping for n,v,a,i,e,l with simple;
最后效果
# 连接到目标数据库
select to_tsvector('chinese', '这周日你有空么');
# 输出
to_tsvector
-------------------------
'么':2 '有空':1
网友评论