数据库

十分钟了结MySQL information_schema

nformation_schema数据库是MySQL系统自带的数据库,它提供了数据库元数据的访问方式。感觉information_schema就像是MySQL实例的一个百科全书,记录了数据库当中大部分我们需要了结的信息,比如字符集,权限相关,数据库实体对象信息,外检约束,分区,压缩表,表信息,索引信息,参数,优化,锁和事物等等。通过information_schema我们可以窥透整个MySQL实例的运行情况,可以了结MySQL实例的基本信息,甚至优化调优,维护数据库等,可以说是真正的一部百科全书啊哈哈。

关于字符集和排序规则相关的系统表

CHARACTER_SETS :存储数据库相关字符集信息(memory存储引擎)

COLLATIONS :字符集对应的排序规则

COLLATION_CHARACTER_SET_APPLICABILITY:就是一个字符集和连线校对的一个对应关系而已

下面我们说一下character sets和collations的区别:

字符集(character sets)存储字符串,是指人类语言中最小的表义符号。例如’A'、’B'等;

排序规则(collations)规则比较字符串,collations是指在同一字符集内字符之间的比较规则

每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation)

 MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的

看一下有关于字符集和校对相关的MySQL变量:

character_set_server:默认的内部操作字符集

character_set_client:客户端来源数据使用的字符集

character_set_connection:连接层字符集

character_set_results:查询结果字符集

character_set_database:当前选中数据库的默认字符集

character_set_system:系统元数据(字段名等)字符集

再看一下MySQL中的字符集转换过程:

(1). MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;

(2). 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:

使用每个数据字段的CHARACTER SET设定值;

若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);

若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;

若上述值不存在,则使用character_set_server设定值。

(3). 将操作结果从内部操作字符集转换为character_set_results。

 

权限相关的一些表:

SCHEMA_PRIVILEGES:提供了数据库的相关权限,这个表是内存表是从mysql.db中拉去出来的。

TABLE_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.tables_priv 表中加载的

COLUMN_PRIVILEGES :这个表可以清楚就能看到表授权的用户的对象,那张表那个库以及授予的是什么权限,如果授权的时候加上with grant option的话,我们可以看得到PRIVILEGE_TYPE这个值必须是YES。

USER_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.user 表中加载的

通过表我们可以很清晰看得到MySQL授权的层次,SCHEMA,TABLE,COLUMN级别,当然这些都是基于用户来授予的。可以看得到MySQL的授权也是相当的细密的,可以具体到列,这在某一些应用场景下还是很有用的,比如审计等。

 

存储数据库系统的实体对象的一些表:

COLUMNS:存储表的字段信息,所有的存储引擎

INNODB_SYS_COLUMNS :存放的是INNODB的元数据, 他是依赖于SYS_COLUMNS这个统计表而存在的。

ENGINES :引擎类型,是否支持这个引擎,描述,是否支持事物,是否支持分布式事务,是否能够支持事物的回滚点

EVENTS :记录MySQL中的事件,类似于定时作业

FILES :这张表提供了有关在MySQL的表空间中的数据存储的文件的信息,文件存储的位置,这个表的数据是从InnoDB in-memory中拉取出来的,所以说这张表本身也是一个内存表,每次重启重新进行拉取。也就是我们下面要说的INNODB_SYS_DATAFILES这张表。还要注意一点的是这张表包含有临时表的信息,所以说和SYS_DATAFILES 这张表是不能够对等的,还是要从INNODB_SYS_DATAFILES看。如果undo表空间也配置是InnoDB 的话,那么也是会被记录下来的。

PARAMETERS :参数表存储了一些存储过程和方法的参数,以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。

PLUGINS :基本上是MySQL的插件信息,是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取道德数据

ROUTINES:关于存储过程和方法function的一些信息,不过这个信息是不包括用户自定义的,只是系统的一些信息。

SCHEMATA:这个表提供了实例下有多少个数据库,而且还有数据库默认的字符集

TRIGGERS :这个表记录的就是触发器的信息,包括所有的相关的信息。系统的和自己用户创建的触发器。

VIEWS :视图的信息,也是系统的和用户的基本视图信息。

这些表存储的都是一些数据库的实体对象,方便我们进行查询和管理,对于一个DBA来说,这些表能够大大方便我们的工作,更快更方便的了结和查询数据库的相关信息。

 

约束外键等相关的一些表:

REFERENTIAL_CONSTRAINTS:这个表提供的外键相关的信息,而且只提供外键相关信息

TABLE_CONSTRAINTS :这个表提供的是 相关的约束信息

INNODB_SYS_FOREIGN_COLS :这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的

INNODB_SYS_FOREIGN :存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的,只不过是单独对于INNODB来说的

KEY_COLUMN_USAGE:数据库中所有有约束的列都会存下下来,也会记录下约束的名字和类别

为什么要把外键和约束单列出来呢,因为感觉这是一块独立的东西,虽然我们的生产环境大部分都不会使用外键,因为这会降低性能,但是合理的利用约束还是一个不错的选择,比如唯一约束。

 

关于管理的一些的一些表:

GLOBAL_STATUS ,GLOBAL_VARIABLES,SESSION_STATUS,SESSION_VARIABLES:这四张表分别记录了系统的变量,状态(全局和会话的信息),作为DBA相信大家也都比较熟悉了,而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。

PARTITIONS :MySQL分区表相关的信息,通过这张表我们可以查询到分区的相关信息(数据库中已分区的表,以及分区表的分区和每个分区的数据信息)

PROCESSLIST:show processlist其实就是从这个表拉取数据,PROCESSLIST的数据是他的基础。由于是一个内存表,所以我们相当于在内存中查询一样,这些操作都是很快的。

INNODB_CMP_PER_INDEX,INNODB_CMP_PER_INDEX_RESET:这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.我们知道对于一个INNODB压缩表来说,不管是数据还是二级索引都是会被压缩的,因为数据本身也可以看作是一个聚集索引。关于压缩表在information_schema系列十一有些许简单的介绍。

INNODB_CMPMEM ,INNODB_CMPMEM_RESET:这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息,但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。如果要打开这个功能的话我们要设置innodb_cmp_per_index_enabled参数为ON状态。

INNODB_BUFFER_POOL_STATS :表提供有关INNODB 的buffer pool相关信息,和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。

INNODB_BUFFER_PAGE_LRU,INNODB_BUFFER_PAGE :维护了INNODB LRU LIST的相关信息。

INNODB_BUFFER_PAGE :这个表就比较屌了,存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响,千万不要再我们自己的生产库上面执行这个语句,除非你能接受服务短暂的停顿。

INNODB_SYS_DATAFILES :这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)

INNODB_TEMP_TABLE_INFO :这个表惠记录所有的INNODB的所有用户使用到的信息,但是只能记录在内存中和没有持久化的信息。

INNODB_METRICS :提供INNODB的各种的性能指数,是对INFORMATION_SCHEMA的补充,收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的:innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。

INNODB_SYS_VIRTUAL :表存储的是INNODB表的虚拟列的信息,当然这个还是比较简单的,在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与实际存储一列数据相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

INNODB_CMP,INNODB_CMP_RESET:存储的是关于压缩INNODB信息表的时候的相关信息。

为什么把这些表列为管理相关的表呢,因为我感觉像连接,分区,压缩表,innodb buffer pool等表,我们通过这些表都能很清晰的看到自己数据库的相关功能的状态,特别是我们通过一些变量更容易窥透MySQL的运行状态,方便我们进行管理。

 

关于表信息和索引信息的一些表

TABLES,TABLESPACES,INNODB_SYS_TABLES ,INNODB_SYS_TABLESPACES :

TABLES这张表毫无疑问了,就是记录的数据库中表的信息,其中包括系统数据库和用户创建的数据库。show table status like 'test1'\G的来源就是这个表;

TABLESPACES 却是标注的活跃表空间。 这个表是不提供关于innodb的表空间信息的,对于我们来说并没有太大作用,因为我们生产库是强制INNODB的;

INNODB_SYS_TABLES 这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用)

提供的是关于INNODB的表空间信息,其实和SYS_TABLESPACES 中的INNODB信息是一致的。

STATISTICS:这个表提供的是关于表的索引信息,所有索引的相关信息。

INNODB_SYS_INDEXES:提供相关INNODB表的索引的相关信息,和SYS_INDEXES 这个表存储的信息基本是一样的,只不过后者提供的是所有存储引擎的索引信息,后者只提供INNODB表的索引信息。

INNODB_SYS_TABLESTATS:

这个表就比较重要了,记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息,其实就是MySQL数据库的统计信息

这个表的记录是记录在内存当中的,是一个内存表,每次重启后就会重新记录,所以只能记录从上次重启后的数据库统计信息。有了这个表,我们对于索引的维护就更加方便了,我们可以查询索引的使用次数,方便清理删除不常用的索引,提高表的更新插入等效率,节省磁盘空间。

INNODB_SYS_FIELDS :这个表记录的是INNODB的表索引字段信息,以及字段的排名

INNODB_FT_CONFIG :这张表存的是全文索引的信息

INNODB_FT_DEFAULT_STOPWORD:这个表存放的是stopword 的信息,是和全文索引匹配起来使用的,和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的,这个STOPWORD必须是在创建索引之前创建,而且必须指定字段为varchar。stopword 也就是我们所说的停止词,全文检索时,停止词列表将会被读取和检索,在不同的字符集和排序方式下,会造成命中失败或者找不到此数据,这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。

INNODB_FT_INDEX_TABLE:这个表存储的是关于INNODB表有全文索引的索引使用信息的,同样这个表也是要设置innodb_ft_aux_table以后才能够使用的,一般情况下是空的

INNODB_FT_INDEX_CACHE :这张表存放的是插入前的记录信息,也是为了避免DML时候昂贵的索引重组

 

关于MySQL优化相关的一些表

OPTIMIZER_TRACE :提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试,MySQL追踪优化器小试

PROFILING:SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能,不能查看和剖析其他连接的语句,以及剖析时所引起的消耗。

SHOW PROFILES显示最近发给服务器的多条语句,条数根据会话变量profiling_history_size定义,默认是15,最大值为100。设为0等价于关闭分析功能。

INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。

 

关于MySQL事物和锁的相关的一些表

INNODB_LOCKS:现在获取的锁,但是不含没有获取的锁,而且只是针对INNODB的。

INNODB_LOCK_WAITS:系统锁等待相关信息,包含了阻塞的一行或者多行的记录,而且还有锁请求和被阻塞改请求的锁信息等。

INNODB_TRX:包含了所有正在执行的的事物相关信息(INNODB),而且包含了事物是否被阻塞或者请求锁。

我们通过这些表就能够很方便的查询出来未结束的事物和被阻塞的进程,这是不是更方便了.

MySQL 8.0.2复制新特性 MYSQL8

MySQL 8.0.2 对MySQL 组复制进行了加强,主要有以下几个方面:
  1. 不允许对离开组的成员进行更改:每当组成员离开群组,离开的成员将会自动设置super_read_only,这可以防止DBA,用户或路由器/代理/负载平衡等带来的的意外更改。除了默认离开组复制的成员不能够进行修改以外,也可以从刚加入开始就开始禁止写入,我们也可以在服务器启动时设置super_read_only参数并启动组复制插件。一旦组复制动成功,他会自动调整super_read_only的值。在多主模式下,所有的节点都将不会设置super_read_only参数 ;在单主的模式下,除了主节点以外,其他的节点都会设置super_read_only为ON 。如果很不幸,你的组复制启动失败了的话,super_read_only参数将不会被设置,将不能进行任何写入操作。这些最新的变化同样适用于MySQL 5.7.19和MySQL 8.0.2。
  2. 可以在Performance Schema 中查看更多信息:在Performance Schema现存的表中,对相关的统计信息的可读性进行了加强。“replication_group_members” 和 “replication_group_member_stats” 表也做了相关拓展,现在可以清楚的看到组成员的角色信息,组成员版本和事物计数器(本地/远程)
  3. 通过分配权重来指定主库的选举:用户可以通过指定组成员的权重来控制主库的选举,当现有的主节点退出组复制,权重最高的节点就会被提升为主节点。
  4. 流量控制机制加了一些微调项:用户现在可以更精细的调节流量控制组件。可以定义每个成员的最小配额,整个组的最小提交配额,流程控制窗口等等。

 

MySQL 8.0.1 已经在MySQL复制核心框架添加了很多引人注目的功能。而MySQL 8.0.2在此基础上又有很大的提升,主要如下:
  1. 增强对接收器(IO)线程的管理,即使磁盘已满:此功能提高了接收器和其他线程之间的内部协调效率,减少彼此的争用。对于终端用户来说,这意味着在磁盘变满并且接收器线程阻塞的情况下,它不再阻塞监视操作,例如SHOW SLAVE STATUS。它还引入了一个新的线程状态(接收器线程正在等待磁盘空间资源),此外,当磁盘已满的时候,而且你并不能通过释放磁盘空间使接收器线程继续没有完成的工作,这个时候你可以手动停掉它,一般情况下不会有什么问题。但是如果当时有一个写入的事物被清除掉了,而且relay log 也不是在一致的状态下,当接收器线程轮询relay log并且在等待磁盘空间可用时,你就要特别的留心。
  2. binary log中记录更多的元数据信息:将事物长度添加到全局事务日志事件。这可以对我们未来的优化工作有很大的帮助,而且也提高了binary log的可读性。

 

如果你在研究MySQL复制的内部机制与原理,基础组件添加了一个有趣的服务:
  1. 组成员事件可以传播到内部其他组件。通过利用新的基础服务架构,组复制插件现在可以通知服务器中的其他组件关于成员关联的事件。例如,通知组成员的角色改变仲裁丢失等。其他的组件可以对这个信息作出反馈,并且用户也可以自己开发组件用来记录和检测这些事件。
  2. 从XCom(标准的Paxos实现,能严格保证正确性)的内部结构中删除节点上的冗余信息:我们在XCom的结构中删除了一些冗余信息,这使它变得更加简单,更少的出现错误,更容易监控那些节点加入或者离开集群,同时它会在系统中保留以前的信息。
  3. 对XCom核心和新编码风格进行了几项改进:我们已经修复了XCom的几个BUG,重新格式化了代码,使它符合Google的编码准则,如果你恰巧是一个开发人员,并且再看Paxos实现的源代码,你会发现改版后的代码将会更加容易阅读和理解。
  4. 移除了一些老旧版本binary log转换的源代码:这个清理工作我们清除了一些老版本My数据库产的的binary logs转化为新版本能够识别的一些代码(现在仅支持MySQL5.0以及以上版本)。

 

还有一件有意思的事情,MySQL 8.0.2中更改了以下复制默认值:
  1. 复制的元数据信息默认以INNODB系统表来存储:这将使MySQL复制功能变得更加强大,在复制崩溃并且自动恢复时候能够使用INNODB事物的特性来保证恢复到指定位置的正确性。此外,新功能还要求将元数据以表的形式存储(比如组复制和多源复制),它与MySQL 8的新的数据字典保持一致。
  2. 基于行数据的哈希扫描被默认开启:这也许并不是一个被广泛认同的做法,但是当从库有一些没有主键约束的表的时候性能会有提高。在这种情况下,使用基于行的复制时,此更改会最大程度降低性能损失,因为它会减少更新所有行所需的表扫描数(slave_rows_search_algorithms参数默认TABLE_SCAN,INDEX_SCAN,HASH_SCAN)。
  3. transaction-write-set-extraction参数会默认开启:使用写集提取,为用户启动组复制或在主服务器上使用基于WRITESET的依赖关系对master进行跟踪。
  4. 默认开启Binary log 过期时间:expire-logs-days默认设置为30(30天)

PostgreSQL 与 MySQL 相比,优势何在?

Pg 没有 MySQL 的各种坑

MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小.

按照 SQL 标准, 做 null 判断不能用 = null, 只能用 is null
the result of any arithmetic comparison with NULL is also NULL

但 pg 可以设置 transform_null_equals 把 = null 翻译成 is null 避免踩坑

不少人应该遇到过 MySQL 里需要 utf8mb4 才能显示 emoji 的坑, Pg 就没这个坑.

MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 Pg 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能. 附带一个各数据库对隔离级别的行为差异比较调查: cs.umb.edu/~poneil/iso.

MySQL 不支持多个表从同一个序列中取 id, 而 Pg 可以.

MySQL 不支持 OVER 子句, 而 Pg 支持. OVER 子句能简单的解决 "每组取 top 5" 的这类问题.

几乎任何数据库的子查询 (subquery) 性能都比 MySQL 好.

更多的坑:blog.ionelmc.ro/2014/12

不少人踩完坑了, 以为换个数据库还得踩一次, 所以很抗拒, 事实上不是!!!

Pg 不仅仅是 SQL 数据库

它可以存储 array 和 json, 可以在 array 和 json 上建索引, 甚至还能用表达式索引. 为了实现文档数据库的功能, 设计了 jsonb 的存储结构. 有人会说为什么不用 Mongodb 的 BSON 呢? Pg 的开发团队曾经考虑过, 但是他们看到 BSON 把 ["a", "b", "c"] 存成 {0: "a", 1: "b", 2: "c"} 的时候就决定要重新做一个 jsonb 了... 现在 jsonb 的性能已经优于 BSON.

现在往前端偏移的开发环境里, 用 Pg + PostgREST 直接生成后端 API 是非常快速高效的办法:
begriffs/postgrest · GitHub
postgREST 的性能非常强悍, 一个原因就是 Pg 可以直接组织返回 json 的结果.

它支持服务器端脚本: TCL, Python, R, Perl, Ruby, MRuby ... 自带 map-reduce 了.

它有地理信息处理扩展 (GIS 扩展不仅限于真实世界, 游戏里的地形什么的也可以), 可以用 Pg 搭寻路服务器和地图服务器:
PostGIS — Spatial and Geographic Objects for PostgreSQL

它自带全文搜索功能 (不用费劲再装一个 elasticsearch 咯):
Full text search in milliseconds with PostgreSQL 不过一些语言相关的支持还不太完善, 有个 bamboo 插件用调教过的 mecab 做中文分词, 如果要求比较高, 还是自己分了词再存到 tsvector 比较好.

它支持 trigram 索引.
trigram 索引可以帮助改进全文搜索的结果: PostgreSQL: Documentation: 9.3: pg_trgm
trigram 还可以实现高效的正则搜索 (原理参考 swtch.com/~rsc/regexp/r )

MySQL 处理树状回复的设计会很复杂, 而且需要写很多代码, 而 Pg 可以高效处理树结构:
Scaling Threaded Comments on Django at Disqus
slideshare.net/quipo/tr

它可以高效处理图结构, 轻松实现 "朋友的朋友的朋友" 这种功能:
slideshare.net/quipo/rd

它可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop ...) 当成自己数据库中的表来查询:
Foreign data wrappers

其它观点
一、 PostgreSQL 的稳定性极强, Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多 MySQL 用户都遇到过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 可以支持语句级的镜像或分片,slony 可以进行字段级的同步设置,standby 可以构建WAL文件级或流式的读写分离集群,同步频率和集群策略调整方便,操作非常简单。
八、一般关系型数据库的字符串有限定长度8k左右,无限长 TEXT 类型的功能受限,只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问,SQL语法内置正则表达式,可以索引,还可以全文检索,或使用xml xpath。用PG的话,文档数据库都可以省了。
九,对于WEB应用来说,复制的特性很重要,mysql到现在也是异步复制,pgsql可以做到同步,异步,半同步复制。还有mysql的同步是基于binlog复制,类似oracle golden gate,是基于stream的复制,做到同步很困难,这种方式更加适合异地复制,pgsql的复制基于wal,可以做到同步复制。同时,pgsql还提供stream复制。
十,pgsql对于numa架构的支持比mysql强一些,比MYSQL对于读的性能更好一些,pgsql提交可以完全异步,而mysql的内存表不够实用(因为表锁的原因)

PG 不如 MySQL 的地方

第一,MySQL有一些实用的运维支持,如 slow-query.log ,这个pg肯定可以定制出来,但是如果可以配置使用就更好了。
第二是mysql的innodb引擎,可以充分优化利用系统所有内存,超大内存下PG对内存使用的不那么充分,
第三点,MySQL的复制可以用多级从库,但是在9.2之前,PGSQL不能用从库带从库。
第四点,从测试结果上看,mysql 5.5的性能提升很大,单机性能强于pgsql,5.6应该会强更多.
第五点,对于web应用来说,mysql 5.6 的内置MC API功能很好用,PGSQL差一些。

另外一些:
pgsql和mysql都是背后有商业公司,而且都不是一个公司。大部分开发者,都是拿工资的。
说mysql的执行速度比pgsql快很多是不对的,速度接近,而且很多时候取决于你的配置。
对于存储过程,函数,视图之类的功能,现在两个数据库都可以支持了。
另外多线程架构和多进程架构之间没有绝对的好坏,oracle在unix上是多进程架构,在windows上是多线程架构。
很多pg应用也是24/7的应用,比如skype. 最近几个版本VACUUM基本不影响PGSQL 运行,8.0之后的PGSQL不需要cygwin就可以在windows上运行。
至于说对于事务的支持,mysql和pgsql都没有问题。
 

上面说的都很齐全了


没有人说FDW(Foreign data wrappers)吗?那我提一下吧。
第一次看到惊为天人:用统一的SQL,去访问其他关系数据库,其他NoSQL数据库,HBase,甚至是各种格式的文件,操作系统信息,在线数据集。

瞬间爆炸好吗,各种数据库大一统万岁!
 
AA网友第一份工作是在某两家大型游戏公司做数据挖掘的,干了三年,听着说是数据挖掘,其实做的最多的我觉得是项目数据分析以及做游戏的后台报表数据计算框架。大公司因为人才储备和数据量很大的原因,一般是hadoop+mysql的模式,hadoop计算大量原始数据,然后按维度汇总后的展示数据存储在mysql供前段web select出来展示。三年后我离职去了一家小型游戏公司,虽然体量比原来东家小多了,但是游戏也比较受欢迎,数据也很多。由于人才储备及成本的原因,小公司并没有hadoop之类的大数据工具,我这边搭建这些东西还是使用的mysql,每天凌晨1点左右数据导入算前一天数据报表,需要实时展示的一些数据用python写的代码计算。但是mysql做数据报表计算后台最大缺点就是没有grouping sets和一些窗口函数,替代方案很麻烦而且效率低,还有就是做很多统计数据各种表连接、外连接等等一大堆,不同数据库之间数据的利用计算。于是我回去查资料偶然间发现了postgresql,短期用了下简直惊呆了,感觉这数据库都不要钱还开源,简直良心。但后面慢慢使用,也发现了一些postgresql的缺点,当然这些缺点只是我作为使用者的感受去说的,可能有写人不认同。简单总结下:

优点:
1,单纯我使用的感受来说,postgresql的性能应该是远远甩mysql三条街的,而且也稳如死狗,相同的数据量复杂计算,postgresql时间一般只有mysql 1/4左右,当然这个时间只是我这边业务我的使用情况,其他环境可能不一样,我这边是多表join,维度聚合等等比较多。还有将大量log文件导入数据库,也是pgsql快得多。
2,postgresql有grouping sets函数,也是迫使我抛弃mysql第一原因。做报表后台计算,olap/oltp之类的这个函数简直是刚性需求。没有grouping sets函数,我感觉做报表后台计算,简直惨不忍睹。当然pgsql还有挺多很好用的窗口函数之类,用起来真心爽。
3,pgsql对json支持比较好,还有很逆天的fdw功能,就是把别的数据库的表当自己的用,减少了我这边很多工作。

4,pgsql的字段类型支持的多,有很多mysql没有的类型,但是实际中有时候用到。
缺点:

1,pgsql表名就让我这种强迫症的人很难受,pgsql对表名大小写不敏感的。但是很多时候建表用大写字母在我看来是很优雅的一种方式。比如手游行业里面,创建角色的数据库你一般得用CreateRole类似这样的名字吧,不同英文之间大写标识,但是pgsql会直接给你变成小写表名,除非你能忍受每次使用这个表加个双引号,所以我建表就弄成create_role这样子,但是有些含义较多的会导致表名很长,看着不舒服。当然这算不上大事,但是也是不太方便的一个小细节点。
2,pgsql对语法简直严苛到有时候你认为变态的地步,甚至要为它这个严苛的学院派作风做很多代码修改、预处理。举个例子,一款手游每天某个日志会产生几十G的数据,你要把数据导入进去数据仓库,一般就是按照字段名,再指定下分隔符,比如有些公司喜欢用 | 分割字段等之类,还有的打印json格式。但是你要知道实际生产环境中,服务器是有很小的几率(我这边大概是百万分之1左右吧)将日志打印的不规范的,比如简单的举个例子角色登录日志。一般类似这样子 时间|账号ID|角色ID|角色名字|等级|登录IP|机型 。但很小的几率服务器会将一些字段丢掉了,比如这个日志打印成了 时间|账号ID|角色ID|角色名字 这样子,mysql导进去就无所谓,顶多就是这百万分之一中的这条数据某条数据不完整而已,而且丢掉的一些字段也不见得重要,但是pgsql直接导入不进去,会直接报错停掉。每天1000万行数据,就因为一条打印的不完整,呵呵,对不起,他会直接报错一条也导入不进去。有些人会说用awk之类预处理,但是数据量大了之后这种预处理也会拖慢效率。还有一个更是头疼,1000万里面有一行将数字类型的等级打印成了字符串的东西,那么pgsql会非让你找出这一条删掉,然后才能将剩下的数据导入进去。mysql就完全没有这个问题,比如mysql level字段定义的int类型,几千万中有一条数据没注意打印成字符串,mysql会自己给你转成0存储的,不会有任何报错。
3,pgsql的分区简直就是灾难级别的。使用的继承之类的概念。建一个主表,然后建立很多小表,继承到主表,建索引得一个一个在小表上面建立。小表建表的时候指定了分割规则,你导入的时候还是得很傻的按照这个规则去指定那个表导入,pgsql不会对你说,你插入主表,我根据你的建表规则去分配数据。还有它的分表性能差的要死。据说10会内置分表,后面看吧,反正我感觉pgsql的分表惨不忍睹。

总结下来就是,我这边使用下来,pgsql性能、稳定性会远强于mysql,功能上也比mysql强大太多,但是真的太学院派风格了,什么都是感觉在实验室的环境一样,对数据对语法严苛变态的不近人情,估计得让写这个的教授体会下真实的生产环境,他们的产品就不会这么理想化了。

 

MySQL如果只有MyISAM一个引擎的话,那你们黑真的也有道理,但问题是InnoDB现在已经是MySQL默认的引擎,而且这个引擎综合能力很强,能用好这个引擎其实就已经能解决大多数需要数据库的业务逻辑.在MySQL先占领市场的前提下,大多数MySQL用户都是不愿意冒风险切换到另一个数据库的,除非PostgreSQL真的是那个场景上是万金油.

在数据量极大的时候(大于1亿条的级别),InnoDB的B+树性能的缺陷会暴露,这时MySQL的DBA可能会转向TokuDB这个第三方开源的MySQL引擎来处理这些大数据.也就是说,MySQL的小用户,数据量估计连千万都不到,他们是不可能没事折腾换数据库的.MySQL的大用户,他们熟悉MySQL,他们也更愿意使用MySQL,既然TokuDB/Infobright这些第三方引擎能满足他们的某些需求如大数据存储和分析,他们也没有换数据库的动力.除非PostgreSQL在他们需求上对MySQL有绝对压倒性优势.

PostgreSQL真那么强,其实完全可以像TokuTek(已被Percona收购)那样,开发一个第三方MySQL存储引擎在MySQL里挑战InnoDB嘛,至少TokuDB(分形树索引)在某些方面证明了自己比InnoDB(B+树索引)优秀,比如随机插入性能,数据压缩效果,大数据存储和分析.
 
一、PG相对于MySQL的优势:

1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
2、存储过程的功能支持要比MySQL好,具备本地缓存执行计划的能力;
3、对表连接支持较完整,优化器的功能较完整,支持的索引类型很多,复杂查询能力较强;
4、PG主表采用堆表存放,MySQL采用索引组织表,能够支持比MySQL更大的数据量。
5、PG的主备复制属于物理复制,相对于MySQL基于binlog的逻辑复制,数据的一致性更加可靠,复制性能更高,对主机性能的影响也更小。
6、MySQL的存储引擎插件化机制,存在锁机制复杂影响并发的问题,而PG不存在。

二、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)。
 

我们这样的比较不想仅仅成为一份性能测试报告,因为至少从我个人来看,对于一个数据库,稳定性和速度并不能代表一切。对于一个成熟的数据库,稳定性肯定会日益提供。而随着硬件性能的飞速提高,速度也不再是什么太大的问题。

这两个产品都属于开放源码的一员,性能和功能都在高速地提高和增强。MySQL AB的人们和PostgreSQL的开发者们都在尽可能地把各自的数据库改得越来越好,所以对于任何商业数据库使用其中的任何一个都不能算是错误的选择。

MySQL的背后是一个成熟的商业公司,而PostgreSQL的背后是一个庞大的志愿开发组。这使得MySQL的开发过程更为慎重,而PostgreSQL的反应更为迅速。
这样的两种背景直接导致了各自固有的优点和缺点。

1、首先是速度,MySQL通常要比PostgreSQL快得多。MySQL自已也宣称速度是他们追求的主要目标之一,基于这个原因,MySQL在以前的文档中也曾经说过并不准备支持事务和触发器。但是在最新的文档中,我们看到MySQL 4.0.2-alpha已经开始支持事务,而且在MySQL的TODO中,对触发器、约束这样的注定会降低速度的功能也列入了日程。但是,我们仍然有理由相信,MySQL将有可能一直保持速度的优势。
2、MySQL比PostgreSQL更流行,流行对于一个商业软件来说,也是一个很重要的指标,流行意味着更多的用户,意味着经受了更多的考验,意味着更好的商业支持、意味着更多、更完善的文档资料。
3、与PostgreSQL相比,MySQL更适宜在Windows环境下运行。MySQL作为一个本地的Windows应用程序运行(在 NT/Win2000/WinXP下,是一个服务),而PostgreSQL是运行在Cygwin模拟环境下。PostgreSQL在Windows下运行没有MySQL稳定,应该是可以想象的。
4、MySQL使用了线程,而PostgreSQL使用的是进程。在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多。
5、MySQL可以适应24/7运行。在绝大多数情况下,你不需要为MySQL运行任何清除程序。PostgreSQL目前仍不完全适应24/7运行,这是因为你必须每隔一段时间运行一次VACUUM。
6、MySQL在权限系统上比PostgreSQL某些方面更为完善。PostgreSQL只支持对于每一个用户在一个数据库上或一个数据表上的 INSERT、SELECT和UPDATE/DELETE的授权,而MySQL允许你定义一整套的不同的数据级、表级和列级的权限。对于列级的权限, PostgreSQL可以通过建立视图,并确定视图的权限来弥补。MySQL还允许你指定基于主机的权限,这对于目前的PostgreSQL是无法实现的,但是在很多时候,这是有用的。
7、由于MySQL 4.0.2-alpha开始支持事务的概念,因此事务对于MySQL不再仅仅成为劣势。相反,因为MySQL保留无事务的表类型。这就为用户提供了更多的选择。
8、MySQL的MERGE表提供了一个独特管理多个表的方法。
9、MySQL的myisampack可以对只读表进行压缩,此后仍然可以直接访问该表中的行。

1、对事务的支持与MySQL相比,经历了更为彻底的测试。对于一个严肃的商业应用来说,事务的支持是不可或缺的。
2、MySQL对于无事务的MyISAM表。采用表锁定,一个长时间运行的查询很可能会长时间地阻碍对表的更新。而PostgreSQL不存在这样的问题。
3、PostgreSQL支持存储过程,而目前MySQL不支持,对于一个严肃的商业应用来说,作为数据库本身,有众多的商业逻辑的存在,此时使用存储过程可以在较少地增加数据库服务器的负担的前提下,对这样的商业逻辑进行封装,并可以利用数据库服务器本身的内在机制对存储过程的执行进行优化。此外存储过程的存在也避免了在网络上大量的原始的SQL语句的传输,这样的优势是显而易见的。
4、对视图的支持,视图的存在同样可以最大限度地利用数据库服务器内在的优化机制。而且对于视图权限的合理使用,事实上可以提供行级别的权限,这是MySQL的权限系统所无法实现的。
5、对触发器的支持,触发器的存在不可避免的会影响数据库运行的效率,但是与此同时,触发器的存在也有利于对商业逻辑的封装,可以减少应用程序中对同一商业逻辑的重复控制。合理地使用触发器也有利于保证数据的完整性。
6、对约束的支持。约束的作用更多地表现在对数据完整性的保证上,合理地使用约束,也可以减少编程的工作量。
7、对子查询的支持。虽然在很多情况下在SQL语句中使用子查询效率低下,而且绝大多数情况下可以使用带条件的多表连接来替代子查询,但是子查询的存在在很多时候仍然不可避免。而且使用子查询的SQL语句与使用带条件的多表连接相比具有更高的程序可读性。
8、支持R-trees这样可扩展的索引类型,可以更方便地处理一些特殊数据。
9、PostgreSQL可以更方便地使用UDF(用户定义函数)进行扩展。

这个问题很难说得清,而
且事实上除了MySQL和PostgreSQL外,使用Oracle、Sybase、Informix等也是明智的选择。如何你确定只在MySQL和PostgreSQL中进行选择,以下规则总是有效的。1、如果你的操作系统是Windows,你应该使用MySQL。
2、如果你对数据库并不了十分了解,甚至不知道事务、存储过程等究竟是什么,你应该使用MySQL。
3、如果你的应用对数据的完整性和严肃性要求不高,但是追求处理的高速度。例如是一个论坛和社区,你应该使用MySQL。
4、你的应用是一个严肃的商业应用,对数据完整性要求很高。而且你希望对一些商业数据逻辑进行很好的封装,例如是一个网上银行,你应该使用PostgreSQL。
5、你的应用处理的是地理数据,由于R-TREES的存在,你应该使用PostgreSQL。
6、你是一个数据库内核的狂热爱好者,你甚至希望拥有你自己版本的数据库,毫无疑问,你必须使用PostgreSQL,没准下一个PostgreSQL版本中某一个模块的作者就是你。

以上只是作者从自己的理解尽量客观公正地评价MySQL和PostgreSQL的优劣。其中的带有倾向性的意见只代表作者个人观点,有关这两个数据库,欢迎广大朋友提出自己的看法。

个人感觉,两者都了解下吧,比如说你想学习数据库,但是你只是专注于了Oracle,Sybase什么的数据库你根本不懂,那对你今后的就业或者搞研究,肯定是有很大的阻碍的,学习数据库我们不能专注于某一个产品,而是关注整个行业,或许这样对未来的发展,对数据库的趋势才能掌握的很清楚。