postgressql数据源怎么配置

PostgresSQL提供了许多数据库配置参数，本章将介绍每个参数的作用和如何配置每一个参数。

创新互联-专业网站定制、快速模板网站建设、高性价比玉龙网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式玉龙网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖玉龙地区。费用合理售后完善，十载实体公司更值得信赖。

10.1 如何设置数据库参数

所有的参数的名称都是不区分大小写的。每个参数的取值是布尔型、整型、浮点型和字符串型这四种类型中的一个，分别用boolean

、integer、 floating point和string表示。布尔型的值可以写成ON、OFF、 TRUE、 FALSE、 YES、 NO、 1和 0，而且不区分大小

写。

有些参数用来配置内存大小和时间值。内存大小的单位可以是KB、MB和GB。时间的单位可以是毫秒、秒、分钟、小时和天。用ms表示

毫秒，用s表示秒，用 min表示分钟，用h表示小时，用d表示天。表示内存大小和时间值的参数参数都有一个默认的单位，如果用户

在设置参数的值时没有指定单位，则以参数默认的 单位为准。例如，参数shared_buffers表示数据缓冲区的大小，它的默认单位是

数据块的个数，如果把它的值设成8，因为每个数据块的大小是 8KB，则数据缓冲区的大小是8*8=64KB，如果将它的值设成128MB，

则数据缓冲区的大小是128MB。参数vacuum_cost_delay 的默认单位是毫秒，如果把它的值设成10，则它的值是10毫秒，如果把它的

值设成100s，则它的值是100秒。

所有的参数都放在文件 postgresql.conf中，下面是一个文件实例:

#这是注释

log_connections = yes

log_destination = 'syslog'

search_path = '"$user", public'

每一行只能指定一个参数，空格和空白行都会被忽略。“ #”表示注释，注释信息不用单独占一行，可以出现在配置文件的任何地方

。如果参数的值不是简单的标识符和数字，应该用单引号引起来。如果参数的值中有单引号，应该写两个单引号，或者在单引号前面

加一个反斜杠。

一个配置文件也可以包含其它配置文件，使用include指令能够达到这个目的，例如，假设postgresql.conf文件中有下面一行：

include ‘my.confg’

文件my.config中的配置信息也会被数据库读入。include指令指定的配置文件也可以用include指令再包含其它配置文件。如果

include指令中指定的文件名不是绝对路径，数据库会在postgresql.conf文件所在的目录下查找这个文件。

用户也可以在数据库启动以后修改postgresql.conf配置文件，使用命令pg_ctl reload来通知数据库重新读取配置文件。注意，有些

参数在数据库启动以后，不能被修改，只有重新启动数据库以后，新的参数值才能生效。另外一些参数可 以在数据库运行过程中被

修改而且新的值可以立即生效。所以数据库在运行过程中重新读取参数配置文件以后，不是所有的参数都会被赋给新的值。

用户可以在自己建立的会话中执行命令SET修改某些配置参数的值（注意不是全部参数），例如：

SET ENABLE_SEQSCAN TO OFF;

另外，有些参数只有数据库超级用户才能使用SET命令修改它们。用户可以在psql中执行命令show来查看所有的数据库参数的当前值

。例如：

（1）show all; --查看所有数据库参数的值

（2）show search_path; --查看参数search_path的值

10.2 连接与认证

10.2.1 连接设置

listen_addresses (string)

这个参数只有在启动数据库时，才能被设置。它指定数据库用来监听客户端连接的TCP/IP地址。默认是值是* ，表示数据库在启动以

后将在运行数据的机器上的所有的IP地址上监听用户请求（如果机器只有一个网卡，只有一个IP地址，有多个网卡的机器有多个 IP

地址）。可以写成机器的名字，也可以写成IP地址，不同的值用逗号分开，例如，’server01’, ’140.87.171.49, 140.87.171.21

’。如果被设成localhost，表示数据库只能接受本地的客户端连接请求，不能接受远程的客户端连接请求。

port (integer)

这个参数只有在启动数据库时，才能被设置。它指定数据库监听户端连接的TCP端口。默认值是5432。

max_connections (integer)

这个参数只有在启动数据库时，才能被设置。它决定数据库可以同时建立的最大的客户端连接的数目。默认值是100。

superuser_reserved_connections (integer)

这个参数只有在启动数据库时，才能被设置。它表示预留给超级用户的数据库连接数目。它的值必须小于max_connections。 普通用

户可以在数据库中建立的最大的并发连接的数目是max_connections- superuser_reserved_connections， 默认值是3。

unix_socket_group (string)

这个参数只有在启动数据库时，才能被设置。设置Unix-domain socket所在的操作系统用户组。默认值是空串，用启动数据库的操作

系统用户所在的组作为Unix-domain socket的用户组。

unix_socket_permissions (integer)

这个参数只有在启动数据库时，才能被设置。它设置Unix-domain socket的访问权限，格式与操作系统的文件访问权限是一样的。默

认值是0770，表示任何操作系统用户都能访问Unix-domain socket。可以设为0770（所有Unix-domain socket文件的所有者所在的组

包含的用户都能访问）和0700(只有Unix-domain socket文件的所有者才能访问)。对于Unix-domain socket，只有写权限才有意义，

读和执行权限是没有意义的。

tcp_keepalives_idle (integer)

这个参数可以在任何时候被设置。默认值是0，意思是使用操作系统的默认值。它设置TCP套接字的TCP_KEEPIDLE属性。这个参数对于

通过Unix-domain socket建立的数据库连接没有任何影响。

tcp_keepalives_interval (integer)

这个参数可以在任何时候被设置。默认值是0，意思是使用操作系统的默认值。它设置TCP套接字的TCP_KEEPINTVL属性。这个参数对

于通过Unix-domain socket建立的数据库连接没有任何影响。

tcp_keepalives_count (integer)

这个参数可以在任何时候被设置。默认值是0，意思是使用操作系统的默认值。它设置TCP套接字的TCP_KEEPCNT属性。这个参数对于

通过Unix-domain socket建立的数据库连接没有任何影响。

10.2.2. 安全与认证

authentication_timeout (integer)

这个参数只能在postgresql.conf文件中被设置，它指定一个时间长度，在这个时间长度内，必须完成客户端认证操作，否则客户端

连接请求将被拒绝。它可以阻止某些客户端进行认证时长时间占用数据库连接。单位是秒，默认值是60。

ssl (boolean)

这个参数只有在启动数据库时，才能被设置。决定数据库是否接受SSL连接。默认值是off。

ssl_ciphers (string)

指定可以使用的SSL加密算法。查看操作系统关于openssl的用户手册可以得到完整的加密算法列表（执行命令openssl ciphers –v

也可以得到）。

10.3 资源消耗

10.3.1 内存

shared_buffers (integer)

这个参数只有在启动数据库时，才能被设置。它表示数据缓冲区中的数据块的个数，每个数据块的大小是8KB。数据缓冲区位于数据

库的共享内存中，它越大越好，不能小于128KB。默认值是1024。

temp_buffers (integer)

这个参数可以在任何时候被设置。默认值是8MB。它决定存放临时表的数据缓冲区中的数据块的个数，每个数据块的大小是8KB。临时

表缓冲区存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。默认值是1024。

max_prepared_transactions (integer)

这个参数只有在启动数据库时，才能被设置。它决定能够同时处于prepared状态的事务的最大数目（参考PREPARE TRANSACTION命令

）。如果它的值被设为0。则将数据库将关闭prepared事务的特性。它的值通常应该和max_connections的值 一样大。默认值是5。

work_mem (integer)

这个参数可以在任何时候被设置。它决定数据库的排序操作和哈希表使用的内存缓冲区的大小。如何work_mem指定的内存被耗尽，数

据库将使用磁盘文件进 行完成操作，速度会慢很多。ORDER BY、DISTINCT和merge连接会使用排序操作。哈希表在Hash连接、hash聚

集函数和用哈希表来处理IN谓词中的子查询中被使用。单位是 KB，默认值是1024。

maintenance_work_mem (integer)

这个参数可以在任何时候被设置。它决定数据库的维护操作使用的内存空间的大小。数据库的维护操作包括VACUUM、CREATE INDEX和

ALTER TABLE ADD FOREIGN KEY等操作。 maintenance_work_mem的值如果比较大，通常可以缩短VACUUM数据库和从dump文件中恢复数

据库需要的时间。 maintenance_work_mem存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。单位是KB，默

认值是16384。

max_stack_depth (integer)

这个参数可以在任何时候被设置，但只有数据库超级用户才能修改它。它决定一个数据库进程在运行时的STACK所占的空间的最大值

。数据库进程在运行时，会 自动检查自己的STACK大小是否超过max_stack_depth，如果超过，会自动终止当前事务。这个值应该比

操作系统设置的进程STACK的大小 的上限小1MB。使用操作系统命令“ulimit –s“可以得到操作系统设置的进程STACK的最大值。单

位是KB，默认值是100。

10.3.2 Free Space Map

数据库的所有可用空间信息都存放在一个叫free space map (FSM)的结构中，它记载数据文件中每个数据块的可用空间的大小。FSM

中没有记录的数据块，即使有可用空间，也不会系统使用。系统如果需要新的物理存 储空间，会首先在FSM中查找，如果FSM中没有

一个数据页有足够的可用空间，系统就会自动扩展数据文件。所以，FSM如果太小，会导致系统频繁地扩展数 据文件，浪费物理存储

空间。命令VACUUM VERBOSE在执行结束以后，会提示当前的FSM设置是否满足需要，如果FSM的参数值太小，它会提示增大参数。

FSM存放在数据库的共享内存中，由于物理内存的限制，FSM不可能跟踪数据库的所有的数据文件的所有数据块的可用空间信息，只能

跟踪一部分数据块的可用空间信息。

max_fsm_relations (integer)

这个参数只有在启动数据库时，才能被设置。默认值是1000。它决定FSM跟踪的表和索引的个数的上限。每个表和索引在FSM中占7个

字节的存储空间。

max_fsm_pages (integer)

这个参数只有在启动数据库时，才能被设置。它决定FSM中跟踪的数据块的个数的上限。initdb在创建数据库集群时会根据物理内存

的大小决定它的值。每 个数据块在fsm中占6个字节的存储空间。它的大小不能小于16 * max_fsm_relations。默认值是20000。

10.3.3 内核资源

max_files_per_process (integer)

这个参数只有在启动数据库时，才能被设置。他设定每个数据库进程能够打开的文件的数目。默认值是1000。

shared_preload_libraries (string)

这个参数只有在启动数据库时，才能被设置。它设置数据库在启动时要加载的操作系统共享库文件。如果有多个库文件，名字用逗号

分开。如果数据库在启动时未找到shared_preload_libraries指定的某个库文件，数据库将无法启动。默认值为空串。

10.3.4 垃圾收集

执行VACUUM 和ANALYZE命令时，因为它们会消耗大量的CPU与IO资源，而且执行一次要花很长时间，这样会干扰系统执行应用程序发

出的SQL命令。为了解决这个 问题，VACUUM 和ANALYZE命令执行一段时间后，系统会暂时终止它们的运行，过一段时间后再继续执行

这两个命令。这个特性在默认的情况下是关闭的。将参数 vacuum_cost_delay设为一个非零的正整数就可以打开这个特性。

用户通常只需要设置参数vacuum_cost_delay和vacuum_cost_limit，其它的参数使用默认值即可。VACUUM 和ANALYZE命令在执行过程

中，系统会计算它们执行消耗的资源，资源的数量用一个正整数表示，如果资源的数量超过 vacuum_cost_limit，则执行命令的进程

会进入睡眠状态，睡眠的时间长度是是vacuum_cost_delay。 vacuum_cost_limit的值越大，VACUUM 和ANALYZE命令在执行的过程中

，睡眠的次数就越少，反之，vacuum_cost_limit的值越小，VACUUM 和ANALYZE命令在执行的过程中，睡眠的次数就越多。

vacuum_cost_delay (integer)

这个参数可以在任何时候被设置。默认值是0。它决定执行VACUUM 和ANALYZE命令的进程的睡眠时间。单位是微秒。它的值最好是10

的整数，如果不是10的整数，系统会自动将它设为比该值大的并且最接近该值的是10 的倍数的整数。如果值是0，VACUUM 和ANALYZE

命令在执行过程中不会主动进入睡眠状态，会一直执行下去直到结束。

vacuum_cost_page_hit (integer)

这个参数可以在任何时候被设置。默认值是1。

vacuum_cost_page_miss (integer)

这个参数可以在任何时候被设置。默认值是10。

vacuum_cost_page_dirty (integer)

这个参数可以在任何时候被设置。默认值是20。

vacuum_cost_limit (integer)

这个参数可以在任何时候被设置。默认值是200。

10.3.5 后台写数据库进程

后台写数据库进程负责将数据缓冲区中的被修改的数据块（又叫脏数据块）写回到数据库物理文件中。

bgwriter_delay (integer)

这个参数只能在文件postgresql.conf中设置。它决定后台写数据库进程的睡眠时间。后台写数据库进程每次完成写数据到物理文件

中的任务以后， 就会睡眠bgwriter_delay指定的时间。 bgwriter_delay的值应该是10的倍数，如果用户设定的值不是10的倍数，数

据库会自动将参数的值设为比用户指定的值大的最接近用户指定的值 的同时是10的倍数的值。单位是毫秒，默认值是200。

bgwriter_lru_maxpages (integer)

这个参数只能在文件postgresql.conf中设置。默认值是100。后台写数据库进程每次写脏数据块时，写到外部文件中的脏数据块的个

数不能超过 bgwriter_lru_maxpages指定的值。例如，如果它的值是500，则后台写数据库进程每次写到物理文件的数据页的个数不

能超过500，若 超过，进程将进入睡眠状态，等下次醒来再执行写物理文件的任务。如果它的值被设为0, 后台写数据库进程将不会

写任何物理文件（但还会执行检查点操作）。

bgwriter_lru_multiplier (floating point)

这个参数只能在文件postgresql.conf中设置。默认值是2.0。它决定后台写数据库进程每次写物理文件时，写到外部文件中的脏数据

块的个数 （不能超过bgwriter_lru_maxpages指定的值）。一般使用默认值即可，不需要修改这个参数。这个参数的值越大，后台写

数据库进程每次写 的脏数据块的个数就越多。

10.4 事务日志

full_page_writes (boolean)

这个参数只能在postgresql.conf文件中被设置。默认值是on。打开这个参数，可以提高数据库的可靠性，减少数据丢失的概率，但

是会产生过多的事务日志，降低数据库的性能。

wal_buffers (integer)

这个参数只有在启动数据库时，才能被设置。默认值是8。它指定事务日志缓冲区中包含的数据块的个数，每个数据块的大小是8KB，

所以默认的事务日志缓冲区的大小是8*8=64KB。事务日志缓冲区位于数据库的共享内存中。

wal_writer_delay (integer)

这个参数只能在postgresql.conf文件中被设置。它决定写事务日志进程的睡眠时间。WAL进程每次在完成写事务日志的任务后，就会

睡眠 wal_writer_delay指定的时间，然后醒来，继续将新产生的事务日志从缓冲区写到WAL文件中。单位是毫秒（millisecond），

默认 值是200。

commit_delay (integer)

这个参数可以在任何时候被设置。它设定事务在发出提交命令以后的睡眠时间，只有在睡眠了commit_delay指定的时间以后，事务产

生的事务日志才会 被写到事务日志文件中，事务才能真正地提交。增大这个参数会增加用户的等待时间，但是可以让多个事务被同

时提交，提高系统的性能。如果数据库中的负载比较 高，而且大部分事务都是更新类型的事务，可以考虑增大这个参数的值。下面

的参数commit_siblings会影响commit_delay是否生效。 默认值是0，单位是微秒（microsecond）。

commit_siblings (integer)

这个参数可以在任何时候被设置。这个参数的值决定参数commit_delay是否生效。假设commit_siblings的值是5，如果一个事务发出

一个提交请求，此时，如果数据库中正在执行的事务的个数大于或等于5，那么该事务将睡眠commit_delay指定的时间。如果数据库

中正在执行的事务 的个数小于5，这个事务将直接提交。默认值是5。

10.5 检查点

checkpoint_segments (integer)

这个参数只能在postgresql.conf文件中被设置。默认值是3。它影响系统何时启动一个检查点操作。如果上次检查点操作结束以后，

系统产生的事 务日志文件的个数超过checkpoint_segments的值，系统就会自动启动一个检查点操作。增大这个参数会增加数据库崩

溃以后恢复操作需要的时 间。

checkpoint_timeout (integer)

这个参数只能在postgresql.conf文件中被设置。单位是秒，默认值是300。它影响系统何时启动一个检查点操作。如果现在的时间减

去上次检查 点操作结束的时间超过了checkpoint_timeout的值，系统就会自动启动一个检查点操作。增大这个参数会增加数据库崩

溃以后恢复操作需要的时 间。

checkpoint_completion_target (floating point)

这个参数控制检查点操作的执行时间。合法的取值在0到1之间，默认值是0.5。不要轻易地改变这个参数的值，使用默认值即可。 这

个参数只能在postgresql.conf文件中被设置。

10.6 归档模式

archive_mode (boolean)

这个参数只有在启动数据库时，才能被设置。默认值是off。它决定数据库是否打开归档模式。

archive_dir (string)

这个参数只有在启动数据库时，才能被设置。默认值是空串。它设定存放归档事务日志文件的目录。

archive_timeout (integer)

这个参数只能在postgresql.conf文件中被设置。默认值是0。单位是秒。如果archive_timeout的值不是0，而且当前时间减去数 据

库上次进行事务日志文件切换的时间大于archive_timeout的值，数据库将进行一次事务日志文件切换。一般情况下，数据库只有在

一个事务日志 文件写满以后，才会切换到下一个事务日志文件，设定这个参数可以让数据库在一个事务日志文件尚未写满的情况下

切换到下一个事务日志文件。

10.7 优化器参数

10.7.1 存取方法参数

下列参数控制查询优化器是否使用特定的存取方法。除非对优化器特别了解，一般情况下，使用它们默认值即可。

enable_bitmapscan (boolean)

打开或者关闭bitmap-scan 。默认值是 on。

enable_hashagg (boolean)

打开或者关闭hashed aggregation。默认值是 on。

enable_hashjoin (boolean)

打开或者关闭hash-join。默认值是 on。

enable_indexscan (boolean)

打开或者关闭index-scan。默认值是 on。

enable_mergejoin (boolean)

打开或者关闭merge-join。默认值是 on。

enable_nestloop (boolean)

打开或者关闭nested-loop join。默认值是 on。不可能完全不使用nested-loop join，关闭这个参数会让系统在有其它存取方法可

用的情况下，不使用nested-loop join。

enable_seqscan (boolean)

打开或者关闭sequential scan。默认值是 on。不可能完全不使用sequential scan，关闭这个参数会让系统在有其它存取方法可用

的情况下，不使用sequential scan。

postgresql怎么截取日期到月份

1、首先需要找到一个带日期字段的数据表。

2、接下来向表中插入日期值。

3、然后通过month函数获取月份即可，注意month里添加的是列名。

4、运行SQL语句以后发现日期中的月份已经被提取出来了。

5、另外还可以在Month中直接加GETDATE函数获取当前时间的月份。

示例讲解PostgreSQL表分区的三种方式

表分区是解决一些因单表过大引用的性能问题的方式，比如某张表过大就会造成查询变慢，可能分区是一种解决方案。一般建议当单表大小超过内存就可以考虑表分区了。PostgreSQL的表分区有三种方式：

本文通过示例讲解如何进行这三种方式的分区。

为方便，我们通过Docker的方式启动一个PostgreSQL，可参考：《Docker启动PostgreSQL并推荐几款连接工具》。我们要选择较高的版本，否则不支持Hash分区，命令如下：

先创建一张表带有年龄，然后我们根据年龄分段来进行分区，创建表语句如下：

这个语句已经指定了按age字段来分区了，接着创建分区表：

这里创建了四张分区表，分别对应年龄是0到10岁、11到20岁、21到30岁、30岁以上。

接着我们插入一些数据：

可以看到这里的表名还是 pkslow_person_r ，而不是具体的分区表，说明对于客户端是无感知的。

我们查询也一样的：

但实际上是有分区表存在的：

而且分区表与主表的字段是一致的。

查询分区表，就只能查到那个特定分区的数据了：

类似的，列表分区是按特定的值来分区，比较某个城市的数据放在一个分区里。这里不再给出每一步的讲解，代码如下：

当我们查询第一个分区的时候，只有广州的数据：

哈希分区是指按字段取哈希值后再分区。具体的语句如下：

可以看到创建分区表的时候，我们用了取模的方式，所以如果要创建N个分区表，就要取N取模。

随便查询一张分区表如下：

可以看到同是SZ的哈希值是一样的，肯定会分在同一个分区，而BJ的哈希值取模后也属于同一个分区。

本文讲解了PostgreSQL分区的三种方式。

代码请查看：

分库分表 VS newsql数据库

最近与同行 科技 交流，经常被问到分库分表与分布式数据库如何选择，网上也有很多关于中间件+传统关系数据库（分库分表）与NewSQL分布式数据库的文章，但有些观点与判断是我觉得是偏激的，脱离环境去评价方案好坏其实有失公允。

本文通过对两种模式关键特性实现原理对比，希望可以尽可能客观、中立的阐明各自真实的优缺点以及适用场景。

首先关于“中间件+关系数据库分库分表”算不算NewSQL分布式数据库问题，国外有篇论文pavlo-newsql-sigmodrec，如果根据该文中的分类，Spanner、TiDB、OB算是第一种新架构型，Sharding-Sphere、Mycat、DRDS等中间件方案算是第二种（文中还有第三种云数据库，本文暂不详细介绍）。

基于中间件（包括SDK和Proxy两种形式）+传统关系数据库（分库分表）模式是不是分布式架构？我觉得是的，因为存储确实也分布式了，也能实现横向扩展。但是不是"伪"分布式数据库？从架构先进性来看，这么说也有一定道理。"伪"主要体现在中间件层与底层DB重复的SQL解析与执行计划生成、存储引擎基于B+Tree等，这在分布式数据库架构中实际上冗余低效的。为了避免引起真伪分布式数据库的口水战，本文中NewSQL数据库特指这种新架构NewSQL数据库。

NewSQL数据库相比中间件+分库分表的先进在哪儿？画一个简单的架构对比图：

这些大多也是NewSQL数据库产品主要宣传的点，不过这些看起来很美好的功能是否真的如此？接下来针对以上几点分别阐述下的我的理解。

这是把双刃剑。

CAP限制

想想更早些出现的NoSQL数据库为何不支持分布式事务（最新版的mongoDB等也开始支持了），是缺乏理论与实践支撑吗？并不是，原因是CAP定理依然是分布式数据库头上的颈箍咒，在保证强一致的同时必然会牺牲可用性A或分区容忍性P。为什么大部分NoSQL不提供分布式事务？

那么NewSQL数据库突破CAP定理限制了吗？并没有。NewSQL数据库的鼻主Google Spanner（目前绝大部分分布式数据库都是按照Spanner架构设计的）提供了一致性和大于5个9的可用性，宣称是一个“实际上是CA”的，其真正的含义是 系统处于 CA 状态的概率非常高，由于网络分区导致的服务停用的概率非常小 ，究其真正原因是其打造私有全球网保证了不会出现网络中断引发的网络分区，另外就是其高效的运维队伍,这也是cloud spanner的卖点。详细可见CAP提出者Eric Brewer写的《Spanner, TrueTime 和CAP理论》。

完备性 ：

两阶段提交协议是否严格支持ACID，各种异常场景是不是都可以覆盖？

2PC在commit阶段发送异常，其实跟最大努力一阶段提交类似也会有部分可见问题，严格讲一段时间内并不能保证A原子性和C一致性（待故障恢复后recovery机制可以保证最终的A和C）。完备的分布式事务支持并不是一件简单的事情，需要可以应对网络以及各种硬件包括网卡、磁盘、CPU、内存、电源等各类异常，通过严格的测试。之前跟某友商交流，他们甚至说目前已知的NewSQL在分布式事务支持上都是不完整的，他们都有案例跑不过，圈内人士这么笃定，也说明了 分布式事务的支持完整程度其实是层次不齐的。

但分布式事务又是这些NewSQL数据库的一个非常重要的底层机制，跨资源的DML、DDL等都依赖其实现，如果这块的性能、完备性打折扣，上层跨分片SQL执行的正确性会受到很大影响。

性能

传统关系数据库也支持分布式事务XA，但为何很少有高并发场景下用呢？ 因为XA的基础两阶段提交协议存在网络开销大，阻塞时间长、死锁等问题，这也导致了其实际上很少大规模用在基于传统关系数据库的OLTP系统中。

NewSQL数据库的分布式事务实现也仍然多基于两阶段提交协议，例如google percolator分布式事务模型，

采用原子钟+MVCC+ Snapshot Isolation（SI），这种方式通过TSO(Timestamp Oracle)保证了全局一致性，通过MVCC避免了锁，另外通过primary lock和secondary lock将提交的一部分转为异步，相比XA确实提高了分布式事务的性能。

但不管如何优化，相比于1PC，2PC多出来的GID获取、网络开销、prepare日志持久化还是会带来很大的性能损失，尤其是跨节点的数量比较多时会更加显著，例如在银行场景做个批量扣款，一个文件可能上W个账户，这样的场景无论怎么做还是吞吐都不会很高。

虽然NewSQL分布式数据库产品都宣传完备支持分布式事务，但这并不是说应用可以完全不用关心数据拆分，这些数据库的最佳实践中仍然会写到，应用的大部分场景尽可能避免分布式事务。

既然强一致事务付出的性能代价太大，我们可以反思下是否真的需要这种强一致的分布式事务？尤其是在做微服务拆分后，很多系统也不太可能放在一个统一的数据库中。尝试将一致性要求弱化，便是柔性事务，放弃ACID(Atomicity,Consistency, Isolation, Durability)，转投BASE(Basically Available,Soft state,Eventually consistent)，例如Saga、TCC、可靠消息保证最终一致等模型，对于大规模高并发OLTP场景，我个人更建议使用柔性事务而非强一致的分布式事务。关于柔性事务，笔者之前也写过一个技术组件，最近几年也涌现出了一些新的模型与框架（例如阿里刚开源的Fescar），限于篇幅不再赘述，有空再单独写篇文章。

HA与异地多活

主从模式并不是最优的方式，就算是半同步复制，在极端情况下（半同步转异步）也存在丢数问题，目前业界公认更好的方案是基于paxos分布式一致性协议或者其它类paxos如raft方式，Google Spanner、TiDB、cockcoachDB、OB都采用了这种方式，基于Paxos协议的多副本存储，遵循过半写原则，支持自动选主，解决了数据的高可靠，缩短了failover时间，提高了可用性，特别是减少了运维的工作量，这种方案技术上已经很成熟，也是NewSQL数据库底层的标配。

当然这种方式其实也可以用在传统关系数据库，阿里、微信团队等也有将MySQL存储改造支持paxos多副本的，MySQL也推出了官方版MySQL Group Cluster，预计不远的未来主从模式可能就成为 历史 了。

需要注意的是很多NewSQL数据库厂商宣传基于paxos或raft协议可以实现【异地多活】，这个实际上是有前提的，那就是异地之间网络延迟不能太高 。以银行“两地三中心”为例，异地之间多相隔数千里，延时达到数十毫秒，如果要多活，那便需异地副本也参与数据库日志过半确认，这样高的延时几乎没有OLTP系统可以接受的。

数据库层面做异地多活是个美好的愿景，但距离导致的延时目前并没有好的方案。 之前跟蚂蚁团队交流，蚂蚁异地多活的方案是在应用层通过MQ同步双写交易信息，异地DC将交易信息保存在分布式缓存中，一旦发生异地切换，数据库同步中间件会告之数据延迟时间，应用从缓存中读取交易信息，将这段时间内涉及到的业务对象例如用户、账户进行黑名单管理，等数据同步追上之后再将这些业务对象从黑名单中剔除。由于双写的不是所有数据库操作日志而只是交易信息，数据延迟只影响一段时间内数据，这是目前我觉得比较靠谱的异地度多活方案。

另外有些系统进行了单元化改造，这在paxos选主时也要结合考虑进去，这也是目前很多NewSQL数据库欠缺的功能。

Scale横向扩展与分片机制

paxos算法解决了高可用、高可靠问题，并没有解决Scale横向扩展的问题，所以分片是必须支持的。NewSQL数据库都是天生内置分片机制的，而且会根据每个分片的数据负载(磁盘使用率、写入速度等)自动识别热点，然后进行分片的分裂、数据迁移、合并，这些过程应用是无感知的，这省去了DBA的很多运维工作量。以TiDB为例，它将数据切成region，如果region到64M时，数据自动进行迁移。

分库分表模式下需要应用设计之初就要明确各表的拆分键、拆分方式（range、取模、一致性哈希或者自定义路由表）、路由规则、拆分库表数量、扩容方式等。相比NewSQL数据库，这种模式给应用带来了很大侵入和复杂度，这对大多数系统来说也是一大挑战。

这里有个问题是NewSQL数据库统一的内置分片策略（例如tidb基于range）可能并不是最高效的，因为与领域模型中的划分要素并不一致，这导致的后果是很多交易会产生分布式事务。 举个例子，银行核心业务系统是以客户为维度，也就是说客户表、该客户的账户表、流水表在绝大部分场景下是一起写的，但如果按照各表主键range进行分片，这个交易并不能在一个分片上完成，这在高频OLTP系统中会带来性能问题。

分布式SQL支持

常见的单分片SQL，这两者都能很好支持。NewSQL数据库由于定位与目标是一个通用的数据库，所以支持的SQL会更完整，包括跨分片的join、聚合等复杂SQL。中间件模式多面向应用需求设计，不过大部分也支持带拆分键SQL、库表遍历、单库join、聚合、排序、分页等。但对跨库的join以及聚合支持就不够了。

NewSQL数据库一般并不支持存储过程、视图、外键等功能，而中间件模式底层就是传统关系数据库，这些功能如果只是涉及单库是比较容易支持的。

NewSQL数据库往往选择兼容MySQL或者PostgreSQL协议，所以SQL支持仅局限于这两种，中间件例如驱动模式往往只需做简单的SQL解析、计算路由、SQL重写，所以可以支持更多种类的数据库SQL。

SQL支持的差异主要在于分布式SQL执行计划生成器，由于NewSQL数据库具有底层数据的分布、统计信息，因此可以做CBO，生成的执行计划效率更高，而中间件模式下没有这些信息，往往只能基于规则RBO（Rule-Based-Opimization），这也是为什么中间件模式一般并不支持跨库join，因为实现了效率也往往并不高，还不如交给应用去做。

存储引擎

传统关系数据库的存储引擎设计都是面向磁盘的，大多都基于B+树。B+树通过降低树的高度减少随机读、进而减少磁盘寻道次数，提高读的性能，但大量的随机写会导致树的分裂，从而带来随机写，导致写性能下降。NewSQL的底层存储引擎则多采用LSM，相比B+树LSM将对磁盘的随机写变成顺序写，大大提高了写的性能。不过LSM的的读由于需要合并数据性能比B+树差，一般来说LSM更适合应在写大于读的场景。当然这只是单纯数据结构角度的对比，在数据库实际实现时还会通过SSD、缓冲、bloom filter等方式优化读写性能，所以读性能基本不会下降太多。NewSQL数据由于多副本、分布式事务等开销，相比单机关系数据库SQL的响应时间并不占优，但由于集群的弹性扩展，整体QPS提升还是很明显的，这也是NewSQL数据库厂商说分布式数据库更看重的是吞吐，而不是单笔SQL响应时间的原因。

成熟度与生态

分布式数据库是个新型通用底层软件，准确的衡量与评价需要一个多维度的测试模型，需包括发展现状、使用情况、社区生态、监控运维、周边配套工具、功能满足度、DBA人才、SQL兼容性、性能测试、高可用测试、在线扩容、分布式事务、隔离级别、在线DDL等等，虽然NewSQL数据库发展经过了一定时间检验，但多集中在互联网以及传统企业非核心交易系统中，目前还处于快速迭代、规模使用不断优化完善的阶段。

相比而言，传统关系数据库则经过了多年的发展，通过完整的评测，在成熟度、功能、性能、周边生态、风险把控、相关人才积累等多方面都具有明显优势，同时对已建系统的兼容性也更好。

对于互联网公司，数据量的增长压力以及追求新技术的基因会更倾向于尝试NewSQL数据库，不用再考虑库表拆分、应用改造、扩容、事务一致性等问题怎么看都是非常吸引人的方案。

对于传统企业例如银行这种风险意识较高的行业来说，NewSQL数据库则可能在未来一段时间内仍处于 探索 、审慎试点的阶段。基于中间件+分库分表模式架构简单，技术门槛更低，虽然没有NewSQL数据库功能全面，但大部分场景最核心的诉求也就是拆分后SQL的正确路由，而此功能中间件模式应对还是绰绰有余的，可以说在大多数OLTP场景是够用的。

限于篇幅，其它特性例如在线DDL、数据迁移、运维工具等特性就不在本文展开对比。

总结

如果看完以上内容，您还不知道选哪种模式，那么结合以下几个问题，先思考下NewSQL数据库解决的点对于自身是不是真正的痛点：

如果以上有2到3个是肯定的，那么你可以考虑用NewSQL数据库了，虽然前期可能需要一定的学习成本，但它是数据库的发展方向，未来收益也会更高，尤其是互联网行业，随着数据量的突飞猛进，分库分表带来的痛苦会与日俱增。当然选择NewSQL数据库你也要做好承担一定风险的准备。

如果你还未做出抉择，不妨再想想下面几个问题：

如果这些问题有多数是肯定的，那还是分库分表吧。在软件领域很少有完美的解决方案，NewSQL数据库也不是数据分布式架构的银弹。相比而言分库分表是一个代价更低、风险更小的方案，它最大程度复用传统关系数据库生态，通过中间件也可以满足分库分表后的绝大多数功能，定制化能力更强。 在当前NewSQL数据库还未完全成熟的阶段，分库分表可以说是一个上限低但下限高的方案，尤其传统行业的核心系统，如果你仍然打算把数据库当做一个黑盒产品来用，踏踏实实用好分库分表会被认为是个稳妥的选择。

很多时候软件选型取决于领域特征以及架构师风格，限于笔者知识与所属行业特点所限，以上仅为个人粗浅的一些观点，欢迎讨论。