MySQL 过渡 PostgreSQL 经验分享

·  阅读 461
原文链接: mp.weixin.qq.com
This browser does not support music or audio playback. Please play it in WeChat or another browser. Wolfs Mount Dreams - Début Album

声明

  • 本文思路从一开始就定位为基于云数据库来部署,不考虑生产环境自己搭建、维护、备份

  • 本文只为:sculptor-boot-generator 代码生成器体系服务 Github Gitee

  • 感谢群里老鐡童鞋审稿!

PostgreSQL 资料打包

  • 关注公众号:cd-k8s,聊天窗口输入: postgresql

  • 可以得到 1 套视频、9 本 PDF 资料

总结

  • MySQL 的用户群体性好于 PostgreSQL,特别是国内,更加容易上手。但是 PostgreSQL 从体验上最大优势就是插件以及带来的各种可能。

  • 两者的基准压力测试工具不同,很难说测试数据对比是公平的,如果是通过 Java 代码测试同配置的 CURD,非极限情况下,两者使用感受上差距不大。

  • PostgreSQL 在做统计分析上可以借助各种函数、语法进行支持,所以在数据分析上有优势

  • 借用知乎上的一句总结 PostgreSQL 我觉得很合适:PostgreSQL 是一专多长的全栈数据库:在可观的规模内,都能做到一招鲜吃遍天

    • 所以,作为中小企业我觉得完全可以依赖 PostgreSQL,特别是求活阶段的企业

本文大纲

  • PostgreSQL 官网资料介绍

  • PostgreSQL 和 MySQL 语法差异点

  • PostgreSQL 在阿里云的情况

  • PostgreSQL 的 Docker 本地部署

  • PostgreSQL 基准测试

  • PostgreSQL 特有索引介绍

  • PostgreSQL json 和 jsonb 类型

  • PostgreSQL 执行过程分析

  • PostgreSQL 中文分词、全文检索、自定义中文词库

  • PostgreSQL 日常维护

  • PostgreSQL 插件推荐

  • PostgreSQL 衍生生态介绍

PostgreSQL 官网资料介绍

GUI Tool

  • 我不推荐官方的 pgAdmin,只是因为有更好的产品替换:Navicat Premium

  • Navicat Premium 支持跨平台,支持各类关系型、非关系型数据库,支持各类云平台

  • 界面体验比 DBeaver 更加友好,支持模型与 SQL 转换

  • 支持各种场景的导入导出

  • 官网介绍:www.navicat.com.cn/products/na…

  • 但是,pgAdmin 也有一些优点,比如有 Dashboard、Statistics 可以直观查看服务器的一些统计信息。

PostgreSQL 和 MySQL 语法差异点

PostgreSQL 与 MySQL 常用共同数据类型

  • 如果考虑同时兼容多个数据库,最好在设计之初就考虑差异点

  • 数值类型

    • smallint

    • integer

    • bigint

    • double

    • decimal

  • 字符类型

    • char(100)

    • varchar(100)

    • text

  • 日期/时间类型(我个人喜欢存储时间戳,所以用 bigint)

    • timestamp

    • date

    • time

  • JSON 类型(PostgreSQL 推荐使用 jsonb,因为它支持 GIN 索引)

    • json

PostgreSQL 与 MySQL 语法共同点

  • 插入数据

INSERT INTO sys_demo(id, demo_name, demo_num, demo_type_enum, ranking, description, state_enum, delete_enum, create_date, create_user_id, update_date, update_user_id, delete_date, delete_user_id)VALUES (111111111111111101, '演示次数01', '101', 1, 100, '演示描述01', 1, 1, 1574663780828, 111111111111111111, 1574663780828, 111111111111111111, NULL, NULL);复制代码
  • 插入外键

alter table sys_login_log add constraint FK_ID1 foreign key (user_id) REFERENCES sys_user (id);复制代码
  • 插入索引

CREATE INDEX rel_permission_role_index_most ON rel_permission_role (role_id, permission_id);CREATE UNIQUE INDEX unique_username ON sys_user (username);复制代码

PostgreSQL 与 MySQL 语法异同点

  • 符号上

    • PostgreSQL 不支持有 ` 号包裹表名、字段名等情况

  • 命名上

    • 创建索引的时候,MySQL 只要同表中索引名称唯一即可,PostgreSQL 必须整个库唯一

  • 创表:在 sculptor-boot-generator 项目上,有一个策略类:GeneratorPostgreSQL.java 在通过 MySQL 生成的时候可以直接生成 PostgreSQL 的创表语句,但是只针对我们自己的约定的字段类型

  • MySQL 创表

DROP TABLE IF EXISTS sys_user;CREATE TABLE sys_user(    id                   bigint      NOT NULL COMMENT '主键ID',    username             varchar(50) NOT NULL COMMENT '用户账号:oneParam',    real_name            varchar(50) NULL COMMENT '真实姓名',    user_password        varchar(50) NOT NULL COMMENT '登录密码',    password_salt        varchar(10) NOT NULL COMMENT '密码盐:放于密码后面',    user_email           varchar(50) NULL COMMENT '邮箱地址:oneParam',    telephone            varchar(20) NULL COMMENT '固话',    mobile_phone         varchar(20) NULL COMMENT '手机号:oneParam',    gender_enum          tinyint     NOT NULL DEFAULT '1' COMMENT '性别:[1=保密=PRIVACY, 2=男性=MALE, 3=女性=FEMALE, 4=中性=NEUTRAL]max=4',    register_type_enum   tinyint     NOT NULL DEFAULT '1' COMMENT '注册方式:[1=系统预置=SYSTEM_INIT, 2=后台管理系统新增=MANAGEMENT_ADD, 3=主动注册=REGISTER, 4=被邀请注册=INVITE]max=4',    register_origin_enum tinyint     NOT NULL DEFAULT '1' COMMENT '注册来源:[1=WEB方式=WEB, 2=安卓APP=ANDROID, 3=苹果APP=IOS, 4=H5=H5, 5=微信小程序=WECHAT_MINI_PROGRAM, 6=微信公众号=WECHAT_OFFICIAL_ACCOUNT]max=6',    state_enum           tinyint     NOT NULL DEFAULT '1' COMMENT '启用状态:[1=启用=ENABLE, 2=禁用=DISABLE]max=2',    delete_enum          tinyint     NOT NULL DEFAULT '1' COMMENT '删除状态:[1=未删除=NOT_DELETED, 2=已删除=DELETED]max=2',    create_date          bigint      NOT NULL COMMENT '创建时间',    create_user_id       bigint      NOT NULL COMMENT '创建人',    update_date          bigint      NOT NULL COMMENT '更新时间',    update_user_id       bigint      NOT NULL COMMENT '更新人',    delete_date          bigint      NULL COMMENT '删除时间',    delete_user_id       bigint      NULL COMMENT '删除人',    PRIMARY KEY (id)) COMMENT ='用户表';复制代码
  • PostgreSQL 创表

DROP TABLE IF EXISTS sys_user;CREATE TABLE sys_user(    id                   bigint      NOT NULL,    username             varchar(50) NOT NULL,    real_name            varchar(50) NULL,    user_password        varchar(50) NOT NULL,    password_salt        varchar(10) NOT NULL,    user_email           varchar(50) NULL,    telephone            varchar(20) NULL,    mobile_phone         varchar(20) NULL,    gender_enum          smallint    NOT NULL,    register_type_enum   smallint    NOT NULL,    register_origin_enum smallint    NOT NULL,    state_enum           smallint    NOT NULL,    delete_enum          smallint    NOT NULL,    create_date          bigint      NOT NULL,    create_user_id       bigint      NOT NULL,    update_date          bigint      NOT NULL,    update_user_id       bigint      NOT NULL,    delete_date          bigint      NULL,    delete_user_id       bigint      NULL,    PRIMARY KEY (id))WITH (    OIDS = FALSE);COMMENT ON TABLE sys_user IS '用户表';COMMENT ON COLUMN sys_user.id IS '主键ID';COMMENT ON COLUMN sys_user.username IS '用户账号';COMMENT ON COLUMN sys_user.real_name IS '真实姓名';省略其他复制代码

PostgreSQL 与 MySQL 事务差

  • PostgreSQL 除了常规的 DML 的 ROLLBACK 之外,还支持 DDL 的 ROLLBACK

  • 常规脚本操作,稳妥一点的流程基本是:BEGIN > COMMIT / ROLLBACK

  • PostgreSQL 额外支持 DDL 操作,比如 CREATE TABLE,TRUNCATE 等的 ROLLBACK

  • 测试:

select count(*) from sys_user;假设返回有 9 条数据BEGIN;TRUNCATE sys_user;select count(*) from sys_user;返回有 0 条数据ROLLBACK;select count(*) from sys_user;还是返回有 9 条数据复制代码

PostgreSQL 与 MySQL 在 Java 使用

  • 没有太大区别,ORM 框架都帮你解决了(假设使用的都是共有的类型)

  • PostgreSQL 配置信息

spring:  datasource:    driver-class-name: org.postgresql.Driver    url: jdbc:postgresql://postgresql.cdk8s.com:5432/cdk8s_sculptor_boot?useSSL=false    username: root    password: 123456复制代码
  • MySQL 配置信息

spring:  datasource:    driver-class-name: com.mysql.cj.jdbc.Driver    url: jdbc:mysql://mysql.cdk8s.com:3306/cdk8s_sculptor_boot?useSSL=false    username: root    password: 123456复制代码

PostgreSQL 在阿里云的情况

  • 阿里云 PostgreSQL 官网:www.aliyun.com/product/rds…

  • 阿里云 PostgreSQL 帮助文档:help.aliyun.com/document_de…

  • 阿里云 PostgreSQL 各版本对比:help.aliyun.com/document_de…

  • PostgreSQL进阶之路:RDS PG+HDB PG应用和案例集-云栖社区-阿里云:yq.aliyun.com/topic/118

  • 查看阿里云 PostgreSQL 的版本插件支持:www.alibabacloud.com/help/zh/doc…

    • 也可以连上数据库,然后输入:SELECT * FROM pg_available_extensions order by name

  • 2019-12 查询到的阿里云 PostgreSQL 12 支持的插件列表(目前 12 不支持高可用版本)

  • 补充:因为我的工单反馈后就阿里官方补充了文档,但是还是缺部分信息,我自己购买后做了测试,这里有完整的数据

(阿里云记录)
  • 2019-12 查询到的阿里云 PostgreSQL 11 支持的插件列表(目前 11 支持高可用版本)

(阿里云记录)
(阿里云记录)
(阿里云记录)

PostgreSQL 11 带 zhparser 插件的 Docker 部署(非官方)

  • hub.docker.com/r/davidlauh…

  • 字典文件在容器:/usr/share/postgresql/11/tsearch_data

  • 配置文件在容器:/var/lib/postgresql/data/postgresql.conf

mkdir -p /Users/youmeek/docker_data/pgsql11/data /Users/youmeek/docker_data/pgsql11/confsudo chmod -R 777 /Users/youmeek/docker_data/pgsql11先启动一个简单容器拿配置文件:docker run -d --name pgsql11 davidlauhn/postgres-11-with-zhparser复制配置文件出来,后面我就不说这个目录了,改为 /etc 目录下docker cp pgsql11:/var/lib/postgresql/data/postgresql.conf /Users/youmeek/docker_data/pgsql11/conf停止旧容器docker stop pgsql11 && docker rm pgsql11再次赋权sudo chmod -R 777 /Users/youmeek/docker_data/pgsql11启动新容器docker run \	-d \	--name pgsql11 \	-e POSTGRES_PASSWORD=123456 \	-v /Users/youmeek/docker_data/pgsql11/conf/postgresql.conf:/etc/postgresql/postgresql.conf \	-v /Users/youmeek/docker_data/pgsql11/data:/var/lib/postgresql/data \	-p 5432:5432 \	davidlauhn/postgres-11-with-zhparser \	-c 'config_file=/etc/postgresql/postgresql.conf'默认库名:postgres用户名:postgres复制代码

连接配置

  • 这个文件是专门配置登录认证信息:/var/lib/postgresql/data/pg_hba.conf

  • 默认 ip4 下只能是 127.0.0.1 才能登陆,我这里还补上了一个:192.168.0.101,大家可以自己扩展,一行一个配置

  • METHOD 列是配置密码连接加密方式,照抄即可

  • 修改完记得重启服务

  • 还有一个特殊的地方:

    • /etc/postgresql/postgresql.conf 里面有一个参数默认是:listen_addresses = '*'

    • 但是不排除有些版本是 localhost,那就需要改为星号,不然会连不上

    • 建议把连接改为大点:max_connections = 1000

# TYPE  DATABASE        USER            ADDRESS                 METHOD# "local" is for Unix domain socket connections onlylocal   all             all                                     trust# IPv4 local connections:host    all             all             127.0.0.1/32            trusthost    all             all             192.168.0.101/32        trust# IPv6 local connections:host    all             all             ::1/128                 trust# Allow replication connections from localhost, by a user with the# replication privilege.local   replication     all                                     trusthost    replication     all             127.0.0.1/32            trusthost    replication     all             ::1/128                 trusthost all all all md5复制代码

PostgreSQL 的数据库和模式说明(schema)

  • 这里把 schema 翻译成模式,我记得好像是阿里云还是哪里看到的,暂且就这样叫吧。

  • PostgreSQL 相对 MySQL 表特殊的地方在于其数据库下面还有一个东西叫做:schema 模式,默认每个库下都有一个 public 模式

  • PostgreSQL 支持多模式,但是不推荐使用其他。因为 public 是默认模式,所以在写查询 SQL 的时候可以省掉这个,这样方便以后系统做数据库迁移

常用管理 SQL

查看版本select version();查看软件配置select * from pg_config;查看设置参数select * from pg_settings;更多目录参数:https://www.postgresql.org/docs/11/catalogs.htmlhttp://postgres.cn/docs/10/catalogs.html创建用户CREATE USER root WITH PASSWORD '123456';创建库,并且赋权给 rootCREATE DATABASE cdk8s_sculptor_boot OWNER root;将 newDbName 数据库的所有权限都赋予 root:GRANT ALL PRIVILEGES ON DATABASE newDbName TO root;导出数据:docker exec -it 容器ID pg_dump -h localhost -U postgres 数据库名 > /data/backup.sql复制代码

pgbench 基准测试

  • PostgerSQL 自带基准测试工具:pgbench

  • 它可以自定义脚本文件进行测试,但是我们这里不复杂说,只讲内置的脚本进行测试

  • CentOS 7 独立安装:yum install -y postgresql-contrib

  • 查看版本:pgbench -V

初始化数据库

  • 初始化测试数据:pgbench -i -s 2 -F 100 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName

    • -i 是初始化的意思

    • -s 2 表示初始化 2 个商户的数据。默认 1 个商户是 10W 个账号(pgbench_accounts),10 个出纳(pgbench_tellers),我们这里设置 2 就要对应的生成翻倍数据

    • -F 创建表时数据库的填充因子,取值 10 ~ 100,值越小 UPDATE 性能会有一定提升。

NOTICE:  table "pgbench_branches" does not exist, skippingNOTICE:  table "pgbench_tellers" does not exist, skippingNOTICE:  table "pgbench_accounts" does not exist, skippingNOTICE:  table "pgbench_history" does not exist, skippingcreating tables...10000 tuples done.20000 tuples done.30000 tuples done.40000 tuples done.50000 tuples done.60000 tuples done.70000 tuples done.80000 tuples done.90000 tuples done.100000 tuples done.set primary key...vacuum...done.复制代码

测试命令

  • 测试语句参数说明

-c 并发客户端数-j 工作线程数-M 提交查询到服务器使用的协议:simple|extended|prepared-n 运行测试时不执行清理-T 60 执行总时间,单位秒-r 输出每个SQL的平均每语句延迟复制代码
  • 只读测试,通过 -S 参数控制

nohup pgbench -c 5 -j 5 -M prepared -n -S -T 60 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName > /opt/pgbenchtest-readonly.out 2>&1 &复制代码
  • 更新、查询、插入测试

nohup pgbench -c 5 -j 5 -M prepared -n -T 60 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName > /opt/pgbenchtest-all.out 2>&1 &复制代码
  • 不执行更新测试,通过 -N 参数控制

nohup pgbench -c 5 -j 5 -M prepared -n -N -T 60 -h 127.0.0.1 -p 5432 -U myUserName -d myDBName > /opt/pgbenchtest-noupdate.out 2>&1 &复制代码

测试报告

pghost: 127.0.0.1 pgport: 5432 nclients: 5 duration: 10 dbName: myDBNametransaction type: TPC-B (sort of)scaling factor: 2query mode: preparednumber of clients: 5number of threads: 5duration: 60 snumber of transactions actually processed: 7237tps = 723.210820 (including connections establishing)tps = 723.959626 (excluding connections establishing)复制代码
  • 参数解释:

  • number of clients 是测试时指定的客户端数量

  • number of threads 是测试时指定的每个客户端的线程数

  • number of transactions actually processed 是测试结束时实际完成的事务数和计划完成的事务数,

  • tps = 723.210820 (including connections establishing)

    • 包含建立网络连接开销的 TPS 值

  • tps = 723.959626 (excluding connections establishing)

    • 不包含建立网络连接开销的 TPS 值

PostgreSQL 特有索引介绍

  • 索引类型:B-tree,Hash,GiST,SP-GiST,GIN

  • 一般普通表我们用 B-tree

  • 全文检索的用 GIN,又分为两种:

    • tsvector:表示一个被优化的,可以基于搜索的文档

    • tsquery:表示一个文本查询,支持布尔操作 & | !

    • 两者搭配使用:select * from tableName where to_tsvector('zhparser', fieldName) @@ to_tsquery('搜索内容1|其他内容2');

    • GIN 索引支持操作符:@>??&?|

PostgreSQL json 和 jsonb 类型

  • PostgreSQL 的 JSON 存储有两种字段类型:json、jsonb,两者的区别主要是对软件本身而言,一般是说:json 写入快,读取慢,jsonb 写入慢,读取快。因为 jsonb 是二进制存储,json 是文本存储。

  • jsonb 支持 GIN 索引类型,所以实际使用过程我们推荐 jsonb

  • 假设有一段 JSON:

{  "id": 11111111111111,  "username": "admin"}复制代码
  • 给字段创建 GIN 索引:CREATE INDEX idx_name ON tableName USING GIN(fieldName);

  • 查询操作:

select * from tableName where jsonFieldName @> '{"username":"admin"}'复制代码

PostgreSQL 执行过程分析

explain (analyze,verbose,costs,buffers,timing)select * from sys_user where to_tsvector('zhparser', real_name) @@ to_tsquery('李四|admin');复制代码
  • 输出内容格式如下:

Seq Scan on public.sys_user  (cost=0.00..66.38 rows=1 width=692) (actual time=0.837..1.622 rows=10 loops=1)  Output: id, username, real_name, user_password, password_salt, user_email, telephone, mobile_phone, ...省略... delete_date, delete_user_id  Filter: (to_tsvector('zhparser'::regconfig, (sys_user.real_name)::text) @@ to_tsquery('李四|admin'::text))  Rows Removed by Filter: 82  Buffers: shared hit=3Planning Time: 1.640 msExecution Time: 1.863 ms复制代码
  • 上面信息有一个内容叫做:扫描节点,常见的有:

    • Seq Scan,全表顺序扫描

    • Index Scan,基于索引扫描,但不只是返回索引列的值

    • IndexOnly Scan,基于索引扫描,并且只返回索引列的值,简称为覆盖索引

    • BitmapIndex Scan,利用Bitmap 结构扫描

    • BitmapHeap Scan,把BitmapIndex Scan 返回的Bitmap 结构转换为元组结构

    • Tid Scan,用于扫描一个元组TID 数组

    • Subquery Scan,扫描一个子查询

    • Function Scan,处理含有函数的扫描

    • TableFunc Scan,处理tablefunc 相关的扫描

    • Values Scan,用于扫描Values 链表的扫描

    • Cte Scan,用于扫描WITH 字句的结果集

    • NamedTuplestore Scan,用于某些命名的结果集的扫描

    • WorkTable Scan,用于扫描Recursive Union 的中间数据

    • Foreign Scan,用于外键扫描

    • Custom Scan,用于用户自定义的扫描

  • 代价估计信息:(cost=0.00..66.38 rows=1 width=692)

  • 真实执行信息:(actual time=0.837..1.622 rows=10 loops=1)

    • 点点符号之前是节点实际的启动时间,即找到符合该节点条件的第一个结果实际需要的时间

    • 后面是总执行时间

    • 单位都是 ms

    • actual time 执行时间

    • rows 是实际返回的行数值,不是 MySQL 的扫描行数

    • loops 指的是该节点实际的重启次数。如果一个计划节点在运行过程中,它的相关参数值(如绑定变量)发生了变化,就需要重新运行这个计划节点。

  • 除了以上 3 个核心,更多参数说明可以参看:mysql.taobao.org/monthly/201…

PostgreSQL 中文分词、全文检索、自定义中文词库

  • PostgreSQL 全文索引的实现要靠 gin 索引(通用倒排索引)。默认内置了英文、西班牙文等分词,但是没有中文。

  • 我们需要借助插件:zhparser

  • 阿里云帮助文档:help.aliyun.com/knowledge_d…

测试分词效果

数据库级别(不是软件用例级别)CREATE EXTENSION zhparser;我把配置叫做:zhparser,后面的都是采用这个,大家也可以自己改为自己喜欢的名字CREATE TEXT SEARCH CONFIGURATION zhparser (PARSER = zhparser);修改一个全文检索配置的定义映射了名词(n),动词(v),形容词(a),成语(i),叹词(e)和习用语(l)6种。词典使用的是内置的simple词典,即仅做小写转换。这篇文章介绍写得很好:https://cloud.tencent.com/developer/article/1430039ALTER TEXT SEARCH CONFIGURATION zhparser ADD MAPPING FOR n,v,a,i,e,l WITH simple;select ts_debug('zhparser', '白垩纪是地球上海陆分布和生物界急剧变化、火山活动频繁的时代');SELECT * from ts_parse('zhparser', 'hello world! 2010年保障房建设在全国范围内获全面启动,从中央到地方纷纷加大 了保障房的建设和投入力度 。2011年,保障房进入了更大规模的建设阶段。住房城乡建设部党组书记、部长姜伟新去年底在全国住房城乡建设工作会议上表示,要继续推进保障性安居工程建设。');SELECT to_tsvector('zhparser','“今年保障房新开工数量虽然有所下调,但实际的年度在建规模以及竣工规模会超以往年份,相对应的对资金的需求也会创历>史纪录。”陈国强说。在他看来,与2011年相比,2012年的保障房建设在资金配套上的压力将更为严峻。');SELECT to_tsquery('zhparser', '保障房资金压力');SELECT plainto_tsquery('zhparser', '保障房资金压力');复制代码

扩展词库-方法1

  • 自定义词典的优先级高于自带的词典

  • 限制 zhparser 版本为 2.1 或以上

先查看没有扩展之前的效果:保障 房 资金 压力SELECT * FROM ts_parse('zhparser', '保障房资金压力'); 现在把 资金压力 插入到自定义表中insert into zhparser.zhprs_custom_word values('资金压力');同步数据select sync_zhprs_custom_word();断开当前连接,重新建立连接,再查询:资金压力 就为一个词了SELECT * FROM ts_parse('zhparser', '保障房资金压力');特别说明,阿里云的 zhparser 虽然显示是 1.0 版本,但是通过工单沟通后了解到他们改造了这个插件,所以版本没用。他们的自定义词库是这样用的:https://help.aliyun.com/knowledge_detail/44451.html表名和函数都进行了重命名:insert into pg_ts_custom_word values ('保障房资');select zhprs_sync_dict_xdb();SELECT to_tsquery('zhparser', '保障房资金压力');复制代码

扩展词库-方法2

自定义词典的文件必须放在 /usr/share/postgresql/11/tsearch_data 目录中zhparser根据文件扩展名确定词典的格式类型.txt扩展名表示词典是文本格式.xdb扩展名表示这个词典是xdb格式多个文件使用逗号分隔,词典的分词优先级由低到高,如:zhparser.extra_dicts = 'dict_extra.txt,mydict.xdb'该参数添加到:postgresql.conf 里面复制代码
  • dict_extra.txt 内容格式

; dict_extra.txt我是新增词     2.0再试一个       1.0       1.0    @; 以下词为删除项这是删除           1.0      1.0    !复制代码
测试:SELECT * FROM ts_parse('zhparser', '我是新增词,你可以再试一个');结果:114	我118	是118	新增110	词117	,114	你118	可以100	再118	试109	一个复制词典到容器:docker cp /Users/youmeek/dict_extra.txt pgsql11:/usr/share/postgresql/11/tsearch_data重启容器:docker restart pgsql11重新连接测试:SELECT * FROM ts_parse('zhparser', '我是新增词,你可以再试一个');结果:120	我是新增词117	,114	你118	可以120	再试一个复制代码

独立全文检索字段

  • 如果需要用到大量的全文检索,建议有额外的字段专门来存储对应字段的分词内容

create index idx_name on tableName using gin(to_tsvector('zhparser', fieldName));select * from tableName where to_tsvector('zhparser', fieldName) @@ '搜索内容';select * from tableName where to_tsvector('zhparser', fieldName) @@ '搜索内容11 & 搜索内容22';select * from tableName where to_tsvector('zhparser', fieldName) @@ '搜索内容11 | 搜索内容22';select * from tableName where to_tsvector('zhparser', fieldName) @@ to_tsquery('搜索内容1|其他内容2');添加一个分词字段,gin 索引必须是 tsvector 类型ALTER TABLE table ADD COLUMN tsv_columnName tsvector;将字段的分词向量更新到新字段中UPDATE table SET tsv_columnName = to_tsvector('zhparser', coalesce(fieldName,''));在新字段上创建索引CREATE INDEX idx_gin_zhcn ON table USING GIN(tsv_columnName);创建一个更新分词触发器create trigger trigger_name before insert or updateon table for each row execute procedure tsvector_update_trigger(tsv_columnName, 'zhparser', fieldname);复制代码

PostgreSQL 日常维护

开启 pg_stat_statements 模块

  • 该模块可用于收集数据库中的 SQL 运行信息,例如:SQL 的总执行时间、调用次数等,常用语监控 SQL 性能

修改配置文件:/etc/postgresql/postgresql.conf把原来默认值是:shared_preload_libraries = '',改为shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max= 5000pg_stat_statements.track= allpg_stat_statements.track_utility = onpg_stat_statements.save = on重启软件用 postgres 超级用户登录:create extension pg_stat_statements;这时候会生成:1 个 pg_stat_statements 视图2 个函数:pg_stat_statements,pg_stat_statements_reset()统计调用次数最多select * from pg_stat_statements order by calls desc limit 10统计平均用时最多select * from pg_stat_statements order by mean_time desc limit 10清空统计数据select pg_stat_statements_reset();复制代码
  • 核心字段,查询的时候可以根据下面字段来做排序,筛选出对应的一些 SQL 记录

calls:调用次数query:执行 sql 内容total_time:执行的总时间,单位 msmin_time:执行的最小时间,单位 msmax_time:执行的最大时间,单位 msmean_time:执行的平均时间,单位 msrows:影响的数据行树shared_blks_hit:SQL 命中的共享内存数据块数量shared_blks_read:SQL 读取的共享内存数据块数量shared_blks_written:SQL 写入的共享内存数据块数量shared_blks_dirtied:SQL 产生的共享内存数据脏块数量复制代码
  • 如果是本地部署,需要通过 WEB UI 界面查看以上信息,可以借助它:pg_web_stats

  • pg_web_stats:github.com/kirs/pg_web…

日志检查

  • 设置日志输出格式为 csvlog

  • 每天存一份,一份最大 100M

  • 修改 postgresql.conf 文件,添加如下内容(默认配置参数是没有打开注释的)

  • 重启服务器,在 data 目录下可以看到一个新目录:pg_log

log_destination = 'csvlog'              # Valid values are combinations oflogging_collector = on                  # Enable capturing of stderr and csvloglog_directory = 'pg_log'                # directory where log files are written,log_filename = 'postgresql-%d_%H%M%S.log'       # log file name pattern,log_file_mode = 0600                    # creation mode for log files,log_truncate_on_rotation = on           # If on, an existing log file with thelog_rotation_age = 1d                   # Automatic rotation of logfiles willlog_rotation_size = 100MB               # Automatic rotation of logfiles will复制代码
  • csv 可以用 excel 直接打开

  • 如果数据很多,可以看下列头有哪些,然后创建一个表,把 csv 数据导入到表中,然后进行 sql 筛选

  • log 中如果有 error code 可以在这里找到对应说明:www.postgresql.org/docs/11/err…

连接数检查

  • 查看当前连接数的具体信息

SELECT * from  pg_stat_activity;复制代码

容量检查

  • 通过如下 SQL 可以看出当前库的存储大小

  • 如果定期查询,可以归纳出业务发展情况变化,是否有突发性增长,大表等等

  • pg_size_pretty 表示用人类可读的单位显示出来

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;复制代码
  • 查出所有表按大小排序并分离 data 与 index 存储大小

SELECT    table_name,    pg_size_pretty(table_size) AS table_size,    pg_size_pretty(indexes_size) AS indexes_size,    pg_size_pretty(total_size) AS total_sizeFROM (    SELECT        table_name,        pg_table_size(table_name) AS table_size,        pg_indexes_size(table_name) AS indexes_size,        pg_total_relation_size(table_name) AS total_size    FROM (        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name        FROM information_schema.tables    ) AS all_tables    ORDER BY total_size DESC) AS pretty_sizeswhere table_name like '"public"."%'复制代码

查看提交与回滚数量

  • 如果回滚次数 / 提交次数过大,就说明程序肯定哪里有问题,造成经常回滚

select datname,xact_commit,xact_rollback from pg_stat_database;复制代码

定期 vacuum

  • 新版本的 PostgreSQL 中有自动 vacuum 策略,但是如果是大批量的数据IO可能会导致自动执行很慢,需要配合手动执行以及自己的脚本来清理数据库

  • 打开配置文件,找到自动策略开启的配置:autovacuum = on

  • 手动执行,需要用最高权限的 postgres 用户,建议夜间业务低峰时处理

vacuumdb -d dbName -f -z -v复制代码

PostgreSQL 插件推荐

PostGIS 插件

  • 支持空间对象、空间索引、空间操作函数和空间操作符

  • 常见业务场景:

    • 圈地:以当前或者指定中心点,找出周边 1 公里范围内的商家或人

    • 围栏:检测指定点落在哪个地理围栏中,比如共享单车是否停在禁停区,无人机是否在禁飞区

  • 如果需要用到空间数据的话,这个插件就是最好的支持

  • 目前阿里云的 PostgreSQL 11 支持,最新的 12 还没有该插件

  • 推荐空间数据类型统一使用 Geometry 类型

  • PostgreSQL 也内置了几个几何类型,但是跟 PostGIS 的不是同机制,推荐使用以 ST 开头的对象(ST = Spatial Type)

pg_pathman

  • 官网:github.com/postgrespro…

  • 看德哥文章说比内置分区性能来的好,我没试过,大家可以自己试试。阿里云的 PostgreSQL 11 已经内置该插件

pgRouting 插件

  • 基于 PostgreSQL 和 PostGIS 提供了地理位置引路系统

  • 核心功能:

    • 所有最短路径组合(Johnson 算法)

    • 所有最短路径组合(Floyd-Warshall 算法)

    • 最短 A* 路径

    • 双向 Dijkstra 最短路径

    • 双向 A* 最短路径

    • Dijkstra 最短路径

    • 行车距离

    • K-最短路径,多候选路径

    • K-Dijkstra,一对多最短路径

    • 旅行销售人员(Traveling Sales Person)

    • TRSP(Turn Restriction Shortest Path)

TimescaleDB 插件

PASE 插件

其他插件

PostgreSQL 衍生生态介绍

官网之外参考资料


我们会努力的~

(长按识别扫描二维码)

专注互联网中小企业研发方案

免责申明:

本微信部分图片、音频资源来源于互联网及其他公众平台,版权归原作者所有。

如涉及侵权请予以告知,我们会在 24 小时内删除相关内容,感谢!

分类:
后端
标签:
分类:
后端
标签: