小探知识库

# Pgsql与MySQL区别

# 数据类型区别

pgsql 和 mysql数据类型的区别：

  • mysql：mysql中支持int 1,2,3,4,8 字节，同时支持有符号，无符号。并且mysql中支持在数值列中指定zerofill，用来将存储的数值通过填充0的方式达到指定数据类型的长度(mysql8开始不建议使用ZEROFILL属性，并且在将来的MySQL版本中将不再支持该属性)。

  • pg：pg支持 int 2,4,8 字节，且数值都是有符号的。

# 索引区别

  • mysql 索引的数据结构是B+树。
  • pgsql 索引的数据结构是B树。

B+树和B树的区别在于，B+树的叶子节点存储了所有的数据，而B树的叶子节点只存储了索引，数据存储在非叶子节点中。

因此，B+树的叶子节点是顺序存储的，可以更方便地进行范围查询，而B树的叶子节点是无序的，无法进行范围查询。

另外，B+树的叶子节点之间有指针连接，可以更方便地进行顺序遍历，而B树的叶子节点之间没有指针连接，无法进行顺序遍历。

因此，pgsql 的索引在范围查询和顺序遍历方面性能更好，而mysql 的索引在等值查询方面性能更好。

# 模式

pgsql可以创建模式，mysql不支持创建模式。

模式(schema)是对数据库(database)逻辑分割。在数据库创建的同时，就已经默认为数据库创建了一个模式--public，这也是该数据库的默认模式。所有为此数据库创建的对象(表、函数、试图、索引、序列等)都是创建在这个模式中的

# 语法区别

# 分页查询区别

LIMIT子句差异

  • 在MySQL中，LIMIT子句用于限制结果集中返回的记录数，语法为 LIMIT offset, count，offset为起始位置，count为返回记录数。
  • 在PostgreSQL中，LIMIT子句用于限制结果集中返回的记录数，语法为LIMIT count OFFSET offset，count为返回记录数，offset为起始位置。

示例：

-- MySQL
SELECT * FROM table_name LIMIT 1 20;

-- PostgreSQL
SELECT * FROM table_name LIMIT 20 OFFSET 1;

# 日期函数

  • mysql：DATE_FORMAT(date, format)函数用于格式化日期，其中date为日期，format为格式化字符串。
  • pgsql：TO_CHAR(date, format)函数用于格式化日期，其中date为日期，format为格式化字符串。

示例：

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- PostgreSQL
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

# 字符串

在MySQL中，可以使用+或CONCAT函数来连接字符串，而在PostgreSQL中，可以使用||来连接字符串。

# NULL值处理

  • 在MySQL中，使用= NULL或IS NULL来检查NULL值，使用IS NOT NULL来检查非NULL值。
  • 在PostgreSQL中，使用IS NULL来检查NULL值，使用IS NOT NULL来检查非NULL值

示例：

-- MySQL
SELECT * FROM table_name WHERE column_name IS NULL;

-- PostgreSQL
SELECT * FROM table_name WHERE column_name IS NULL;

# 性能区别

  1. PostgreSQL相对于MySQL的优势
  • PostgreSQL在性能上远远好于MYSQL，通过上面的压测数据即可体现，无论是在耗时，还是在整体吞吐量上，有显著优势。
  • PostgreSQL在单行更新上有明显优势，尤其是启用了HOT UPDATE后，性能比MYSQL高了一个数量级。
  • 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨，比较学院化。
  • PostgreSQL主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
  • PostgreSQL的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
  • MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新，得加锁才可以，但悲观锁会影响性能，手动实现乐观锁又复杂。而 Postgre SQL 的列里有隐藏的乐观锁 version 字段，默认的 repeatable read 级别就能保证并发更新的正确性，并且又有乐观锁的性能。
  1. PostgreSQL之于MySQL相对劣势
  • PostgreSQL系统表设计相对复杂， 在进行一些系统表的统计、操作等方面比较复杂。
  • PostgreSQL 的索引选择方面，选错的概率稍高一些（实测），而且不能跟mysql 一样方便地使用force_index。
  • PostgreSQL 存在vacuum，需要结合具体使用场景，来调整vacuum的参数。

# 场景的使用

  1. MySQL适用的场景

MySQL适用于简单的应用场景，如电子商务、博客、网站等，大中小型系统均可以使用MySQL，它最高支持千万级别到数亿级别的数据量，但是在高性能要求的情况下，比如较快的响应和较高的吞吐量的时候，MYSQL的性能稍微捉襟见肘。另外，在查询条件比较复杂、业务吞吐量要求不高，响应时长无要求的时候，可以选择MYSQL。

  1. PostgreSQL适用的场景

总的来说， PostgreSQL更适合复杂的数据结构、高级应用和大规模数据集。当然如果数据规模比较小，也可以选择PostgreSQL，无论是什么场景，如果你想用PostgreSQL，总可以找到对应的解决方法。有且仅有在查询条件比较复杂的时候不太适用，因为根据我们实际线上的业务表现是PostgreSQL可能会选错索引。

←

Pgsql与MySQL区别