vlambda博客
学习文章列表

数据库的优化对性能的提高是绝对的，几倍甚至十几倍，下面总结一些优化技巧，也许对你会有帮助，欢迎一起探讨。

一、Scheme设计与数据类型优化

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。

   
   

2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用16为存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

   
   

3. UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 – 255。

   
   

4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

   
   

5. TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 – 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

   
   

6. 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

   
   

7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

   
   

8. 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇淫技巧可以解决这个问题，有兴趣可自行查阅。

二、优化COUNT()查询

COUNT()可能是被大家误解最多的函数了，它有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。统计列值时，要求列值是非空的，它不会统计NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。但实际并非这样，如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。

有时候某些业务场景并不需要完全精确的COUNT值，可以用近似值来代替，EXPLAIN出来的行数就是一个不错的近似值，而且执行EXPLAIN并不需要真正地去执行查询，所以成本非常低。通常来说，执行COUNT()都需要扫描大量的行才能获取到精确的数据，因此很难优化，MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题，只有从架构层面解决了，比如添加汇总表，或者使用redis这样的外部缓存系统。

三、优化关联查询

在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：

确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。

确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。

太抽象了？以上面的示例来说明，比如有这样的一个查询：

SELECT A.xx,B.yy 

FROM A INNER JOIN B USING(c)

WHERE A.xx IN (5,6)

假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);

outer_row = outer_iterator.next;

while(outer_row) {

    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;

    inner_row = inner_iterator.next;

    while(inner_row) {

        output[inner_row.yy,outer_row.xx];

        inner_row = inner_iterator.next;

    }

    outer_row = outer_iterator.next;

}

可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

四、优化LIMIT分页

当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。

优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果这张表非常大，那么这个查询最好改成下面的样子：

SELECT film.film_id,film.description

FROM film INNER JOIN (

    SELECT film_id FROM film ORDER BY title LIMIT 50,5

) AS tmp USING(film_id);

这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。

有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：

SELECT id FROM t LIMIT 10000, 10;

改为：

SELECT id FROM t WHERE id > 10000 LIMIT 10;

其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。

五、优化UNION

MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。

除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。

一、MySQL数据库开发规范

数据库规范到底有多重要？有过初创公司经历的朋友应该都深有体会。规范是数据库运维的一个基石，能有效地减少数据库出问题的概率，保障数据库schema的合理设计并方便后续自动化的管理。

曾经我们花了大半年时间来做数据库规范化的工作，例如制定数据库开发指南、给程序员做培训等，推进的时候也会遇到一些阻力。但规范之后运维质量会有一个质的提升，也增进了DBA的工作效率。

在开发规范方面，我们划分为开发规范和运维规范两部分。

1、开发规范

表设计的规范：

• 字段数量建议不超过20-50个

• 做好数据评估，建议纯INT不超过1500万，含有CHAR的不要超过1000万。字段类型在满足需求条件下越小越好，尽量使用UNSIGNED存储非负整数，因为实际使用时候存储负数的场景不多。

• 所有字段均定义为NOT NULL，除非你真的想存储null。

索引设计的规范：

1）所有表必须有显式主键

• InnoDB表是以主键排序存储的IOT表

• 尽量使用短、自增的列做索引

• 复制结构使用row格式，如果表有主键可以加速复制

• UNSIGNED INT自增列，也可以考虑BIGINT

• TINYINT做主键可能导致MySQL Crash

• 类型转换会导致查询效率很低

• 可用uuid_short()代替uuid()，转成BIGINT存储

2）合理地建立索引

• 选择区分度高的列作为索引

• 单个索引字段数不超过5，单表索引数量不超过5，避免冗余索引

• 建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾

• 复合索引排序问题，多用explain去确认

SQL编写规范：

1）避免在数据库中进行大量计算任务

• 大事务拆成多个事务，分批多次操作

• 慎用text、blob大型字段，如要用考虑好拆分方案

• 频繁查询的字典表考虑用Cache抗

2）优化join

• 避免大表与大表之间的join，考虑让小表去驱动大表join

• 最多允许三表join，最好控制成两表

• 控制join后面where选择的行数

3）注重where条件，多用EXPLAIN确认

• where条件的字段，尽量用区别度高的字段，这样走索引的性能更好

• 出现子查询的SQL，先确认MySQL版本，利用explain确认执行计划

• 进行分页优化;DML时候多个value合并

Schema Review：

1）字符集问题

表字符集选择UTF8 ，如果需要存储emoj表情，就改成UTF8mb4

2）Schema设计原则

• 核心表字段数量尽可能地少，有大字段要考虑拆分

• 适当考虑一些反范式的表设计，增加冗余字段，减少JOIN

• 资金字段考虑统一*100处理成整型，避免使用decimal浮点类型存储

• 日志类型的表可以考虑按创建时间水平切割，定期归档历史数据

3）Schema设计目标

• 快速实现功能为主，保证节省资源

• 平衡业务技术各个方面，做好取舍

• 不要在DB里进行大计算，减少复杂操作

整体来说，这部分规范还是很容易遵守的，实现起来也没有什么难度，就能取得很好的效果。

2、运维规范

（1）SQL审核

SQL评审这部分工作相信让很多的DBA同学都叫苦不迭，人肉审核不仅效率低下，容易出错，对DBA的自身发展也非常不利，难道我们来上班就是为了审核SQL的吗？在经过了一段痛苦的人肉审核之后，我们接入了去哪儿网开源的Inception，并根据自身的业务特点做了一些调整。当然现在开源的SQL评审软件已经很多了，大家可以自由选择，也可以自行开发。

在审核与执行上线DDL语句的时候，要注意MySQL官方原生Online DDL和Percona公司的pt-osc之间的一些差异，例如pt-osc在执行时每次都要copy全表，相对来说比较慢，好处是不锁表，并且有完善的条件检测和延时负载策略控制。官方Online DDL虽然官方也一直在改进，但生产环境使用还不是很完美，尤其要注意执行过程中容易导致MDL锁。官方Online DDL也有优于pt-osc的地方，比如增删索引，重命名列等，如下图所示。

（2）权限控制

MySQL从5.6开始，逐步完善了权限系统，比如MySQL5.6可以安装检查密码强度的插件，5.7开始增加了密码过期机制、账户锁定等功能，对SSL这一块也做了一些优化，8.0版本增加了角色的功能，权限系统已经逐步在向Oracle数据库靠拢了。在日常运维中，也可以使用pt-show-grants工具提高权限审查的力度。应用程序账号应只赋予SELECT、INSERT、UPDATE权限，DELETE的逻辑改用UPDATE实现，并启用sql_safe_updates选项。

另一个有效控制权限的方法就是SQL堡垒机，早期我们通过改造MyWebSQL实现，在Web版客户端的基础上加入了一些资源控制策略、审计、语法校验等功能。后续又使用Python开发了功能更完备的SQL堡垒机，同时支持MySQL、Oracle、Greenplum等数据库。

SQL堡垒机不仅可控制公司内部人员的数据库权限，追溯各类人员对数据库的操作，也能避免大查询或全表更新的情况发生，支持审计需求，整体运维质量提升了一个台阶。

（3）MySQL版本选择

• MySQL社区版，用户群体最大

• MySQL企业版，收费

• Percona Server版，新特性多，和MySQL社区版最接近

• MariaDB版，国内用户暂时不多

• 选择优先级：MySQL社区版> Percona Server > MariaDB > MySQL 企业版

对于版本选择这件事，建议大家还是跟进官方社区版比较好，目前比较稳定的版本是MySQL5.6，推荐大家使用。有特殊需求的话再选择MySQL5.7、PXC、TiDB、TokuDB等数据库。

二、MySQL高可用架构选型

MySQL高可用方面，目前业界主流依然是基于异步复制的技术，例如Keepalived、MHA、ZooKeeper等，要求数据强一致的场景逐步开始使用分布式协议，这方面的典型代表有PXC、Group Replication、TiDB。下面我们就重点来说说keepalived、MHA和PXC这几种大家用得比较多的架构。

1、keepalived高可用架构

业内使用非常普遍，它部署容易、方便维护，还节省服务器资源。这种架构的一个好处就是在发生切换后，原Master只需重新拉起来即可恢复高可用，不需要过多干预。扩展起来也方便，可以任意挂载只读库和灾备库。但它存在的问题也很明显，比如Keepalived的检测机制不完善、有脑裂隐患、数据一致性较弱等等。

还需要注意主从拓扑的设计。如下图，只读库挂到哪个Master比较合适？显然是M2，其它两种拓扑在发生切换后都会影响到只读库的访问。

2、MHA

MHA自诞生以来，就得到了业内的广泛关注，并迅速流行开来。与keepalived相比，MHA最大的优点就是在发生故障切换之后，能自动补齐binlog，最大程度保证数据一致性。从服务器能自动切换，无需人工干预，能非常好的工作在读写分离的环境下。基于Perl语言的脚本也非常方便进行二次开发。MHA非常适合读写压力比较大的应用。

但由于MHA在工作时需要配置SSH互信，因此选择这种架构时内网安全一定要做到位。另外也可以搭配Binlog Server使用。

3、PXC

PXC全称是Percona XtraDB Cluster，是Percona公司基于Galera协议开发的一个产品。PXC牺牲了CAP里面的P（Partition Tolerance），保留了C(Consistency )和A(Availability )。这种结构非常适合电商、金融类业务，自PXC和Group Replication出现以后，MySQL彻底扫清了进入金融行业的障碍。

PXC的优势：

• 同步复制，解决了传统架构复制延迟和脑裂的问题

• 数据强一致

• 多主复制，每个节点都可以读写数据

• 并行复制，多个事务可以并行推送到其他节点

• 高可用，单点故障不影响集群可用性

• 新节点自动部署

• 与传统MySQL几乎完全兼容

使用PXC要注意的问题：

• 不要有大事务

• 木桶效应，集群性能取决于性能最差的那个节点

• 并发效率有损失

• 网络要求较高，建议万兆网络

• 多点并发写时锁冲突、死锁问题多

• 写无法扩展，无法解决热点更新问题

除此之外，还有一类采用DNS/ZooKeeper的高可用架构，这种架构通常都需要自行开发，无通用的方案，比较适合大规模集群的高可用，这里我们不过多赘述。

下面简单回顾一下上述几种高可用架构：

1. 双Master架构：非常成熟，使用很普遍，要注意延迟和数据的一致性。

2. PXC: 分布式协议，数据强一致性，并发效率略低，可用性好

3. MHA：各项指标介于M-M和PXC之间，性能无损失，适合读写分离架构。

总而言之，没有最完美的架构，只有最适合的架构。选择适合自己业务的即可。

三、MySQL sharding拆分

接下来是第三个议题，MySQL拆分原则和分库分表设计。

首先先提一个问题，为什么要拆，不拆不行吗？按照我们的经验来看，当数据和业务到了一定的规模，都不可避免的要面临分库分表的问题。这就好像汽车的发动机一样，要达到更高的性能，4缸6缸明显是不够用的，V8、V12才是王道。

拆分能解决如下几个问题：

• 单库并发较大

• 单库物理文件太大

• 单表过大，DDL无法接受

• 防止出现性能瓶颈，提升性能

• 防止出现抖动不稳定现象

确定要进行数据库的拆分了，应该怎么拆呢？

垂直拆分

优点：

• 拆分简单明了，拆分规则明确

• 应用程序模块清晰，整合容易

• 数据维护方便易行，容易定位

缺点：

• 表关联需要改到程序中完成

• 事务处理变的复杂

• 热点表还有可能存在性能瓶颈

• 过度拆分会造成管理复杂

水平拆分

优点：

• 不会影响表关联、事务操作

• 超大规模的表和高负载的表可以打散

• 应用程序端改动比较小

• 拆分能提升性能，也比较易扩展

缺点：

• 数据分散，影响聚集函数的使用

• 切分规则复杂，维护难度增加

• 后期迁移较复杂

要先分库还是先分表？

• 分库的优点：实现简单，库与库之间界限分明，便于维护，缺点是不利于频繁跨库操作，单表数据量大的问题解决不了。

• 分表的优点：能解决分库的不足点，但是缺点恰恰是分库的优点，分表实现起来比较复杂，特别是分表规则的划分，程序的编写，以及后期的数据库拆分移植维护。

一巴掌拍板直接选分库或分表都是不可取的，主要是看需要达到什么样的扩展方式，才能决定先分库还是先分表，根据具体的场景决定。分库分表的最终目的还是为了扩展，而且要看拆分的规划设计是针对哪一层。

上述问题都解决了，该考虑如何实现了，到底是在应用程序中实现，还是使用中间件？个人建议如果是小规模的拆分，直接在程序逻辑中实现即可，大规模的拆分再考虑使用各种中间件。

目前业内已经开源了很多的MySQL中间件产品，例如Atlas、DBProxy、MyCAT、OneProxy、DRDS、Vitess等等，每个中间件都有自己的特点，个别不太成熟的可能会存在一些Bug，选用之前要做好相关的调研与测试工作，上线使用一定要保证自己能hold住。如果要完全贴合自身业务，并且掌控得较好的还是要自行开发。

下面说说我们的拆分经验。

首先我们先在压力比较大的数据库上做垂直拆分，剥离出活动、后台统计等业务。这一步也是最容易实现的。

接下来，如果是消息类的数据，就基于时间维度进行拆分，单表控制在5-10G，行数控制到500-1000w这个样子。这个时候我们发现数据库的性能是比较好的，而且比较好维护。如果是用户类的数据，就按照Hash或Range进行拆分。这种情况下用这种方法拆分会拆的比较均匀一些。

并发仍然比较高怎么办？可以在时间维度拆分的基础上再按Range或Hash进行拆分。

最后要注意的就是不要过度的拆分，会造成复杂度的上升。Schema设计合理的情况下，10亿的数据量也能跑的好好的。个别不关键的应用，例如日志、监控数据等，使用分区表、TokuDB也能抗。拆分对应用层总是有损的。

网上已经有很多拿PostgreSQL与MySQL比较的文章了，这篇文章只是对一些重要的信息进行下梳理。在开始分析前，先来看下这两张图：

MySQL

MySQL声称自己是最流行的开源数据库。LAMP中的M指的就是MySQL。构建在LAMP上的应用都会使用MySQL，如WordPress、Drupal等大多数php开源程序。MySQL最初是由MySQL AB开发的，然后在2008年以10亿美金的价格卖给了Sun公司，Sun公司又在2010年被Oracle收购。Oracle支持MySQL的多个版本：Standard、Enterprise、Classic、Cluster、Embedded与Community。其中有一些是免费下载的，另外一些则是收费的。其核心代码基于GPL许可，由于MySQL被控制在Oracle，社区担心会对MySQL的开源会有影响，所以开发了一些分支，比如： MariaDB和Percona。

PostgreSQL

PostgreSQL标榜自己是世界上最先进的开源数据库。PostgreSQL的一些粉丝说它能与Oracle相媲美，而且没有那么昂贵的价格和傲慢的客服。最初是1985年在加利福尼亚大学伯克利分校开发的，作为Ingres数据库的后继。PostgreSQL是完全由社区驱动的开源项目。它提供了单个完整功能的版本，而不像MySQL那样提供了多个不同的社区版、商业版与企业版。PostgreSQL基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，只需要提供一个版权声明即可。

MySQL与PostgreSQL的对比

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

PostgreSQL相对于MySQL的优势

1）不仅仅是关系型数据库

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

• array，不管是一位数组还是多为数组均支持

• json（hStore）和jsonb，相比使用text存储接送要高效很多

json和jsonb之间的区别

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

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

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

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

2）支持地理信息处理扩展

PostGIS 为PostgreSQL提供了存储空间地理数据的支持，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。在功能上，和MYSQL对比，PostGIS具有下列优势：

O2O业务场景中的LBS业务使用PostgreSQL + PostGIS有无法比拟的优势。

3）可以快速构建REST API

PostgREST 可以方便的为任何 PostgreSQL 数据库提供完全的 RESTful API 服务。

4）支持树状结构

支持R-trees这样可扩展的索引类型，可以更方便地处理一些特殊数据。MySQL 处理树状的设计会很复杂, 而且需要写很多代码, 而 PostgreSQL 可以高效处理树结构。

5）有极其强悍的 SQL 编程能力

支持递归，有非常丰富的统计函数和统计语法支持。

• MySQL：支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。

• PostgreSQL：没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

6）外部数据源支持

可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop …) 当成自己数据库中的表来查询。Postgres有一个针对这一难题的解决方案：一个名为“外部数据封装器（Foreign Data Wrapper，FDW）”的特性。该特性最初由PostgreSQL社区领袖Dave Page四年前根据SQL标准SQL/MED（SQL Management of External Data）开发。FDW提供了一个SQL接口，用于访问远程数据存储中的远程大数据对象，使DBA可以整合来自不相关数据源的数据，将它们存入Postgres数据库中的一个公共模型。这样，DBA就可以访问和操作其它系统管理的数据，就像在本地Postgres表中一样。例如，使用FDW for MongoDB，数据库管理员可以查询来自文档数据库的数据，并使用SQL将它与来自本地Postgres表的数据相关联。借助这种方法，用户可以将数据作为行、列或JSON文档进行查看、排序和分组。他们甚至可以直接从Postgres向源文档数据库写入（插入、更细或删除）数据，就像一个一体的无缝部署。也可以对Hadoop集群或MySQL部署做同样的事。FDW使Postgres可以充当企业的中央联合数据库或“Hub”。

7）没有字符串长度限制

一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而PostgreSQL的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。MySQL 的各种text字段有不同的限制，要手动区分 small text, middle text, large text… PostgreSQL 没有这个限制，text 能支持各种大小。

8）支持图结构数据存储

没有具体使用过，具体可以自己搜索下。参考链接：https://mp.weixin.qq.com/s/cjor82wgDu5gzDvTYpLDWw

9）支持窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着OVER子句的聚集函数，作为窗口函数；否则它们作为常规的聚合函数。窗口也是一种分组，但和 group by 的分组不同。窗口，可以提供分组之外，还可以执行对每个窗口进行计算。可以相像成是group by 后，然后对每个分组进行计算，而不像Group by ，只是单纯地分组。MySQL 不支持 OVER 子句, 而PostgreSQL支持。OVER 子句能简单的解决 “每组取 top 5” 的这类问题。MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。

10）对索引的支持更强

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

• MySQL：取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。

• PostgreSQL：支持 B-树、哈希、R-树和 Gist 索引。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。PostgreSQL不存在这个问题。

索引类型方面，MySQL取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。PostgreSQL支持 B-树、哈希、R-树和 Gist 索引。

11）集群支持更好

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。复制(Replication)功能是异步的并且有很大的局限性。例如，它是单线程的(single-threaded)，因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master。

PostgreSQL有丰富的开源cluster软件支持。plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

另外，PostgreSQL的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

12）事务隔离做的更好

MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 PostgreSQL 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能。

13）对于字符支持更好一些

MySQL 里需要 utf8mb4 才能显示 emoji 的坑, PostgreSQL 没这个坑。

14）对表连接支持较完整

对表连接支持较完整，MySQL只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。PostgreSQL都支持。

15）存储方式支持更大的数据量

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

16）时间精度更高

MySQL对于时间、日期、间隔等时间类型没有秒以下级别的存储类型，而PostgreSQL可以精确到秒以下。

17）优化器的功能较完整

MySQL对复杂查询的处理较弱，查询优化器不够成熟，explain看执行计划的结果简单。性能优化工具与度量信息不足。

PostgreSQL很强大的查询优化器，支持很复杂的查询处理。explain返回丰富的信息。提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

18)序列支持更好

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

19）对子查询支持更好

对子查询的支持。虽然在很多情况下在SQL语句中使用子查询效率低下，而且绝大多数情况下可以使用带条件的多表连接来替代子查询，但是子查询的存在在很多时候仍然不可避免。而且使用子查询的SQL语句与使用带条件的多表连接相比具有更高的程序可读性。几乎任何数据库的子查询 (subquery) 性能都比 MySQL 好。

20）增加列更加简单

MySQL表增加列，基本上是重建表和索引，会花很长时间。PostgreSQL表增加列，只是在数据字典中增加表定义，不会重建表.

MySQL相对于PostgreSQL的优势

1）MySQL比PostgreSQL更流行

流行对于一个商业软件来说，也是一个很重要的指标，流行意味着更多的用户，意味着经受了更多的考验，意味着更好的商业支持、意味着更多、更完善的文档资料。易用，很容易安装。第三方工具，包括可视化工具，让用户能够很容易入门。

2）回滚实现更优

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

3）在Windows上运行更可靠

与PostgreSQL相比，MySQL更适宜在Windows环境下运行。MySQL作为一个本地的Windows应用程序运行(在 NT/Win2000/WinXP下，是一个服务)，而PostgreSQL是运行在Cygwin模拟环境下。PostgreSQL在Windows下运行没有MySQL稳定，应该是可以想象的。

4）线程模式相比进程模式的优势

MySQL使用了线程，而PostgreSQL使用的是进程。在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多。

• 进程模式对多CPU利用率比较高。进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

• 线程模式对资源消耗比较少。所以MySQL能支持远比PostgreSQL多的更多的连接。但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

5）权限设置上更加完善

MySQL在权限系统上比PostgreSQL某些方面更为完善。PostgreSQL只支持对于每一个用户在一个数据库上或一个数据表上的 INSERT、SELECT和UPDATE/DELETE的授权，而MySQL允许你定义一整套的不同的数据级、表级和列级的权限。对于列级的权限， PostgreSQL可以通过建立视图，并确定视图的权限来弥补。MySQL还允许你指定基于主机的权限，这对于目前的PostgreSQL是无法实现的，但是在很多时候，这是有用的。

6）存储引擎插件化机制

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

7）适应24/7运行

MySQL可以适应24/7运行。在绝大多数情况下，你不需要为MySQL运行任何清除程序。PostgreSQL目前仍不完全适应24/7运行，这是因为你必须每隔一段时间运行一次VACUUM。

8）更加试用于简单的场景

PostgreSQL只支持堆表，不支持索引组织表，Innodb只支持索引组织表。

• 索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。

• 索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。

• 对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。

由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。

总结

MySQL从一开始就没有打算做所有事情，因而它在功能方面有一定的局限性，并不能满足一些先进应用程序的要求。MySQL对某些功能（例如引用、事务、审计等）的实现方式使得它与其他的关系型数据库相比缺少了一些可靠性。对于简单繁重的读取操作，使用PostgreSQL可能有点小题大做，同时性能也比MySQL这样的同类产品要差。除非你需要绝对的数据完整性，ACID遵从性或者设计复杂，否则PostgreSQL对于简单的场景而言有点多余。

如何你确定只在MySQL和PostgreSQL中进行选择，以下规则总是有效的：

• 如果你的操作系统是Windows，你应该使用MySQL。

• 当绝对需要可靠性和数据完整性的时候，PostgreSQL是更好的选择。

• 如果需要数据库执行定制程序，那么可扩展的PostgreSQL是更好的选择。

• 你的应用处理的是地理数据，由于R-TREES的存在，你应该使用PostgreSQL。

• 如果你对数据库并不了十分了解，甚至不知道事务、存储过程等究竟是什么，你应该使用MySQL。

以上内容只是个人观点，如果你有不同的看法可以回复评论。