pgsql索引(pg索引深度分析)

admin 2023年12月17日 14:01 279 0

老铁们，大家好，相信还有很多朋友对于pgsql索引和pg索引深度分析的相关问题不太懂，没关系，今天就由我来为大家分享分享pgsql索引以及pg索引深度分析的问题，文章篇幅可能偏长，希望可以帮助到大家，下面一起来看看吧！

一、postgresql+大于等于不走索引

1、在 PostgreSQL中，查询优化器的选择操作是基于所用表中的统计信息。如果表的统计信息不准确或者没有统计信息，可能会导致查询优化器选择不正确的计划。如果使用大于等于（>=）操作符查询一个列时，如果该列上存在索引且查询返回的数据行数占比较小，那么查询优化器可能会选择使用索引进行查询。但如果数据行数比较大，优化器可能会选择全表扫描。

2、需要注意的是，虽然使用索引进行查询可以提高查询效率，但并不是所有查询都适合使用索引。具体需要根据查询语句、数据量以及表结构等多个因素进行综合考虑和优化。

二、pgsql与mysql有什么区别

pgsql与mysql的区别有：在pgsql上的功能实现比mysql严谨，对表的连接支持更加完整；pgsql的优化器功能完整，支持索引类型多，而mysql的优化器比较简单，适用于简单的查询操作

1、在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；

2、存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；

3、对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；

4、PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。

5、PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。

6、MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而PG不存在。

1、innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀；

2、MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；

3、MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；

4、MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。

5、MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

总的来说，开源数据库都不是很完善，商业数据库oracle在架构和功能方面都还是完善很多的。从应用场景来说，PG更加适合严格的企业应用场景（比如金融、电信、ERP、CRM），而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景（比如google、facebook、alibaba）。

三、pgsql的主键存储方式

PostgreSQL的稳定性极强，Innodb等索引在崩溃，断电之类的灾难场景下抗击打能力有了长足进步，然而很多 MqSQL用户都遇到过 Server级的数据库丢失的场景-- MySQL系统库是 MyISAM，相比之下，PG数据库这方面要更好一些。

任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以位置双曲线甚至对数曲线，到顶峰之后不在下降，而MySQL明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。

PG多年来在 GIS（地理信息）领域处于优势地位，因为它有丰富的几何类型，PG有大量字典，数组，bitmap等数据类型，相比之下 MySQL就差很多， Instagram就是因为 PG的空间数据库扩展 POSTGIS远远强于 MySQL的 my spatial而采用 PgSQL的。

PG的“无锁定”特性非常突出，甚至包括 vacuum这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。

PG可以使用函数和条件索引，这使得 PG数据库的调优非常灵活， MySQL就没有这个功能，条件索引在 web应用中很重要。

PG有极其强悍的 SQL编程能力（9.x图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（Oracle的叫法，PG里叫Window函数），还可以用多种语言来写存储过程，对于 R的支持也很好。这一点MySQL就差很多，很多分析功能都不支持，腾讯内部的存储主要是 MySQL，但是数据分析主要是 Hadoop+ PgSQL。

PG的有多种集群架构可以选择，plproxy可以之hi语句级的镜像或分片，slony可以进行字段级的同步配置，standby可以构建 WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便。

一般关系型数据库字符串有长度限制 8k左右，无限长 TEXT类型的功能受限，只能作为外部大数据访问。而 PG的 TEXT类型可以直接访问且无长度限制， SQL语法内置正则表达式，可以索引，还可以全文检索，或使用 xml xpath。用 PG的话，文档数据库都可以省了。

PgSQL对于 numa架构的支持比 MySQL强一些，比 MySQL对于读的性能更好一些， PgSQL提交可以完全异步提交，而 MySQL的内存表不够实用（因为表锁的原因）。

pgsql除了存储正常的数据类型外，还支持存储

array，不管是一维数组还是多维数组均支持。

json和jsonb，相比使用 text存储要高效很多。

json和 jsonb在更高的层面上看起来几乎是一样的，但是存储实现上是不同的。

json存储完的文本，json列会每次都解析存储的值，它不支持索引，但可以为创建表达式索引。

jsonb存储的二进制格式，避免了重新解析数据结构。它支持索引，这意味着可以不使用指定索引就能查询任何路径。

当我们比较写入数据速度时，由于数据存储的方式的原因，jsonb会比 json稍微的慢一点。json列会每次都解析存储的值，这意味着键的顺序要和输入的时候一样。但是 jsonb不同，以二进制格式存储且不保证键的顺序。因此如果有软件需要依赖键的顺序，jsonb可能不是最佳选择。使用 jsonb的优势还在于可以轻易的整合关系型数据和非关系型数据，PostgreSQL对于 mongodb这类数据库是一个不小的威胁，毕竟如果一个表中只有一列数据的类型是半结构化的，没有必要为了迁就它而整个表的设计都采用 schemaless的结构。

没有CPU核心数限制，有多少CPU核就用多少

一共有255个参数，用到的大概是80个，参数比较稳定，用上个大版本配置文件也可以启动当前大版本数据库

一共有707个参数，用到的大概是180个，参数不断增加，就算小版本也会增加参数，大版本之间会有部分参数不兼容情况

只有高可用集群需要依靠第三方中间件，例如：patroni+etcd、repmgr

大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，学习成本高，高可用集群也需要第三方中间件，官方MGR集群还没成熟

物理流复制，属于物理复制，跟SQL Server镜像/AlwaysOn一样，严格一致，没有任何可能导致不一致，性能和可靠性上，物理复制完胜逻辑复制，维护简单

主从复制，属于逻辑复制，（sql_log_bin、binlog_format等参数设置不正确都会导致主从不一致）

大事务并行复制效率低，对于重要业务，需要依赖 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比较和修复主从一致

主从复制出错严重时候需要重搭主从

MySQL的逻辑复制并不阻止两个不一致的数据库建立复制关系

系统自动设置从库默认只读，不需要人工介入，维护简单

从库需要手动设置参数super_read_only=on，让从库设置为只读，super_read_only参数有bug，链接：https://baijiahao.baidu.com/s?id=1636644783594388753&wfr=spider&for=pc

只有社区版，没有其他任何分支版本，PGSQL官方统一开发，统一维护，社区版有所有功能，不像SQL Server和MySQL有标准版、企业版、经典版、社区版、开发版、web版之分

国内外还有一些基于PGSQL做二次开发的数据库厂商，例如：Enterprise DB、瀚高数据库等等，当然这些只是二次开发并不算独立分支

由于历史原因，分裂为三个分支版本，MariaDB分支、Percona分支、Oracle官方分支，发展到目前为止各个分支基本互相不兼容

Oracle官方分支还有版本之分，分为标准版、企业版、经典版、社区版

SQL特性支持情况支持94种，SQL语法支持最完善，例如：支持公用表表达式（WITH查询）

SQL特性支持情况支持36种，SQL语法支持比较弱，例如：不支持公用表表达式（WITH查询）

关于SQL特性支持情况的对比，可以参考：http://www.sql-workbench.net/dbms_comparison.html

同步流复制、强同步（remote apply)、高安全，不会丢数据

PGSQL同步流复制：所有从库宕机，主库会罢工，主库无法自动切换为异步流复制（异步模式），需要通过增加从库数量来解决，一般生产环境至少有两个从库

手动解决：在PG主库修改参数synchronous_standby_names=''，并执行命令： pgctl reload，把主库切换为异步模式

主从数据完全一致是高可用切换的第一前提，所以PGSQL选择主库罢工也是可以理解

增强半同步复制，mysql5.7版本增强半同步才能保证主从复制时候不丢数据

参数rpl_semi_sync_master_wait_for_slave_count等待至少多少个从库接收到binlog，主库才提交事务，一般设置为1，性能最高

参数rpl_semi_sync_master_timeout等待多少毫秒，从库无回应自动切换为异步模式，一般设置为无限大，不让主库自动切换为异步模式

所有从库宕机，主库会罢工，因为无法收到任何从库的应答包

手动解决：在MySQL主库修改参数rpl_semi_sync_master_wait_for_slave_count=0

多种索引类型（btree, hash, gin, gist, sp-gist, brin, bloom, rum, zombodb, bitmap，部分索引，表达式索引）

btree索引，全文索引（低效），表达式索引(需要建虚拟列)，hash索引只在内存表

支持 nested-loop join、hash join、merge join

视图谓词条件下推限制多，子查询上拉限制多

支持 JIT执行计划即时编译，使用LLVM编译器

并行查询（多种并行查询优化方法），并行查询一般多见于商业数据库，是重量级功能

支持插件功能，可以丰富PGSQL的功能，GIS地理插件，时序数据库插件，向量化执行插件等等

不支持check约束，可以写check约束，但存储引擎会忽略它的作用，因此check约束并不起作用（mariadb支持）

数据类型丰富，如 ltree，hstore，数组类型，ip类型，text类型，有了text类型不再需要varchar，text类型字段最大存储1GB

不支持跨库查询，这个跟Oracle 12C以前一样

备份还原非常简单，时点还原操作比SQL Server还要简单，完整备份+wal归档备份（增量）

假如有一个三节点的PGSQL主从集群，可以随便在其中一个节点做完整备份和wal归档备份

备份还原相对不太简单，完整备份+binlog备份（增量）

完整备份需要percona的XtraBackup工具做物理备份，MySQL本身不支持物理备份

需要安装pg_stat_statements插件，pg_stat_statements插件提供了丰富的性能视图：如：等待事件，系统统计信息等

不好的地方是，安装插件需要重启数据库，并且需要收集性能信息的数据库需要执行一个命令：create extension pg_stat_statements命令

否则不会收集任何性能信息，比较麻烦

自带PS库，默认很多功能没有打开，而且打开PS库的性能视图功能对性能有影响（如：内存占用导致OOM bug）

有各个平台的包rpm包，deb包等等，相比MySQL缺少了二进制包，一般用源码编译安装，安装时间会长一些，执行命令多一些

有各个平台的包rpm包，deb包等等，源码编译安装、二进制包安装，一般用二进制包安装，方便快捷

加字段、可变长字段类型长度改大不会锁表，所有的DDL操作都不需要借助第三方工具，并且跟商业数据库一样，DDL操作可以回滚，保证事务一致性

由于大部分DDL操作都会锁表，例如加字段、可变长字段类型长度改大，所以需要借助percona-toolkit里面的pt-online-schema-change工具去完成操作

将影响减少到最低，特别是对大表进行DDL操作

PGSQL每年一个大版本发布，大版本发布的第二年就可以上生产环境，版本迭代速度很快

PGSQL 9.6正式版推出时间：2016年

PGSQL 10正式版推出时间：2017年

PGSQL 11正式版推出时间：2018年

PGSQL 12正式版推出时间：2019年

MySQL的大版本发布一般是2年~3年，一般大版本发布后的第二年才可以上生产环境，避免有坑，版本发布速度比较慢

MySQL5.5正式版推出时间：2010年

MySQL5.6正式版推出时间：2013年

MySQL5.7正式版推出时间：2015年

MySQL8.0正式版推出时间：2018年

支持returning语法，returning clause支持 DML返回 Resultset，减少一次 Client<-> DB Server交互

多进程架构，并发连接数不能太多，跟Oracle一样，既然跟Oracle一样，那么很多优化方法也是相通的，例如：开启大页内存

多线程架构，虽然多线程架构，但是官方有限制连接数，原因是系统的并发度是有限的，线程数太多，反而系统的处理能力下降，随着连接数上升，反而性能下降

一般同时只能处理200~300个数据库连接

不支持聚集索引，PGSQL本身的MVCC的实现机制所导致

通过设置 idle_in_transaction_session_timeout参数来终止空闲事务，比如：应用代码中忘记关闭已开启的事务，PGSQL会自动查杀这种类型的会话事务

不能应付超大数据量，由于PGSQL本身的MVCC设计问题，需要垃圾回收，只能期待后面的大版本做优化

不能应付超大数据量，MySQL自身架构的问题

HTAP数据库：cockroachDB、腾讯Tbase

分片集群： Postgres-XC、Postgres-XL

分片集群：各种各样的中间件，不一一列举

PGSQL在这方面做的比较不好，DBA不能在操作系统层面（停库状态下）看清楚数据库的文件名和命名规律，文件的数量，文件的大小

一旦操作系统发生文件丢失或硬盘损坏，非常不利于恢复，因为连名字都不知道

PGSQL表数据物理文件的命名/存放规律是：在一个表空间下面，如果没有建表空间默认在默认表空间也就是base文件夹下，例如：/data/base/16454/3599

base：默认表空间pg_default所在的物理文件夹

3599：就是表对象的oid，当然，一个表的大小超出1GB之后会再生成多个物理文件，还有表的fsm文件和vm文件，所以一个大表实际会有多个物理文件

由于PGSQL的数据文件布局内容太多，大家可以查阅相关资料

当然这也不能全怪PGSQL，作为一个DBA，时刻做好数据库备份和容灾才是正道，做介质恢复一般是万不得已的情况下才会做

数据库名就是文件夹名，数据库文件夹下就是表数据文件，但是要注意表名和数据库名不能有特殊字符或使用中文名，每个表都有对应的frm文件和ibd文件，存储元数据和表/索引数据，清晰明了，做介质恢复或者表空间传输都很方便

PGSQL在权限设计这块是比较坑爹，抛开实例权限和表空间权限，PGSQL的权限层次有点像SQL Server，db=》schema=》object

要说权限，这里要说一下Oracle，用Oracle来类比

在ORACLE 12C之前，实例与数据库是一对一，也就是说一个实例只能有一个数据库，不像MySQL和SQL Server一个实例可以有多个数据库，并且可以随意跨库查询

而PGSQL不能跨库查询的原因也是这样，PGSQL允许建多个数据库，跟ORACLE类比就是有多个实例（之前说的实例与数据库是一对一）

一个数据库相当于一个实例，因为PGSQL允许有多个实例，所以PGSQL单实例不叫一个实例，叫集簇（cluster），集簇这个概念可以查阅PGSQL的相关资料

PGSQL里面一个实例/数据库下面的schema相当于数据库，所以这个schema的概念对应MySQL的database

注意点：正因为是一个数据库相当于一个实例，PGSQL允许有多个实例/数据库，所以数据库之间是互相逻辑隔离的，导致的问题是，不能一次对一个PGSQL集簇下面的所有数据库做操作

必须要逐个逐个数据库去操作，例如上面说到的安装pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有数据库都做性能收集的话，需要逐个数据库去执行加载命令

又例如跨库查询需要dblink插件或fdw插件，两个数据库之间做查询相当于两个实例之间做查询，已经跨越了实例了，所以需要dblink插件或fdw插件，所以道理非常简单

权限操作也是一样逐个数据库去操作，还有一个就是PGSQL虽然像SQL Server的权限层次结构db=》schema=》object，但是实际会比SQL Server要复杂一些，还有就是新建的表还要另外授权

在PGSQL里面，角色和用户是一样的，对新手用户来说有时候会傻傻分不清，也不知道怎么去用角色，所以PGSQL在权限设计这一块确实比较坑爹

使用mysql库下面的5个权限表去做权限映射，简单清晰，唯一问题是缺少权限角色

多线程架构和多进程架构之间没有绝对的好坏，例如oracle在unix上是多进程架构，在windows上是多线程架构。

MySql对于无事务的MyISAM表，采用表锁定，一个长时间运行的查询很可能会长时间的阻碍，而PostgreSQL不会尊在这种问题。

PostgreSQL支持存储过程，要比MySql好，具备本地缓存执行计划的能力。

高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySql明显出现一个波峰后下滑（5.5版本后Mysql企业版有优化，需要付费）

MySql的InnoDB引擎，可以充分优化利用系统的所有内存，超大内存下PG对内存使用的不那么充分（需要根据内存情况合理分配）。

InnoDB的基于回滚实现的 MVCC机制，对于 PG新老数据一起放的基于 XID的 MVCC机制，是占优的。新老数据一起存放，需要定时触发 VACUUM，会带来多余的 IO和数据库对象加锁开销，引起数据库整理的并发能力下降。而且 VACUUM清理不及时，还可能会引发数据膨胀

Mysql到现在也是异步复制，pgsql可以做到同步、异步、半同步复制。

Mysql同步是基于binlog复制，属于逻辑复制，类似于oracle golden gate，是基于stream的复制，做到同步很困难，这种方式更加适合异步复制；

Pgsql的同是基于wal，属于物理复制，可以做到同步复制。同时，pgsql还提供stream复制。

Mysql的复制可以用多级从库，但是在9.2之前，PgSql不能用从库带从库。

Pgsql的主从复制属于物理复制，相对于Mysql基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。

MySql允许自定义一套不同的数据级、表级和列的权限，运行指定基于主机的权限

Mysql的merge表提供了一个独特管理多个表的方法。myisampack可以对只读表进行压缩，以后仍然可以直接访问该表中的行。

PG有极其强悍的 SQL编程能力（9.x图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，例如分析函数（Oracle的叫法，PG里叫window函数）

支持用多种语言来写存储过程，对于R的支持也很好。这一点上Mysql就差的很远，很多分析功能都不支持。

PgSql对表名大小写的处理，只有在Sql语句中，表明加双引号，才区分大小写。

在Sql的标准实现上要比Mysql完善，而且功能实现比较严谨。

对表连接支持比较完整，优化器的功能比较完整，支持的索引类型很多，复杂查询能力较强。

Mysql采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结果设计存在约束；

Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦数据量大，性能就非常的差。PostgresSQL除了支持 Nest Join和 Sort Merge Join，PostgreSQL还支持正则表达式查询，MySql不支持。

PostgreSQL可以更方便的使用UDF（用户定义函数）进行扩展。

有丰富的几何类型，实际上不止集合类型，PG有大量的字典、数组、bitmap等数据类型，因此PG多年来在 GIS领域处于优势地位。相比之下Mysql就差很多，instagram就是因为PG的空间数据扩展 PostGIS远远强于 MySql的 my spatial而采用 PgSql的。Mysql中的空间数据类型有4种，分别是 CEOMETRY、POINT、LINESTRING、POLYGON，其空间索引只能在存储引擎为 MyiSam的表中创建，用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL。不同的存储亲情有差别。MyISAM和InnoDB都支持 spatial extensions，但差别在于：如果使用MyISAM，可以建立 spatial index，而 InnoDB是不支持的。

pgsql对json支持比较好，还有很逆天的fdw功能，就是把别的数据库中的表当自己的用。

pgsql的字段类型支持的多，有很多mysql没有的类型，但是实际中有时候用到。

一半关系型数据库的字符串长度8k左右，无限长的 TEXT类型的功能受限，只能作为外部带数据访问。而 PG的 TEXT类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用 xml xpath。用 PG的话，文档数据库都可以省了。

postgresql有函数，用于报表、统计很方便

PG支持 R-Trees这样可扩展的索引类型，可以方便的处理一些特殊数据。

PG可以使用函数和条件所以，使得数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。

大批量数据入库，PostgreSql要求所有的数据必须完全满足要求，有一条错误，整个数据入库过程失败。MySql无此问题。

pgsql用继承的方式实现分区表，让分区表的使用不方便且性能差，这点比不上mysql。

pg主表采用堆表存放，MySQL采用索引组织表，能够支持比MySql更大的数据量。

MySql分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到成千上万后的处理性能差异很大。

对于web应用来所，mysql 5.6的内置 MC API功能很好用，PgSQL差一些。

PG的“无锁定”特性非常突出，甚至包括 vacuum这样的整理数据空间的操作，这个和 PGSQL的 MVCC实现有关系。