PostgreSQL VS MySQL's Storage EngineSss..

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
初次接触MySQL,简单的和PostgreSQL做一个对比,主要是存储引擎这块。(个人认为虽然MySQL支持存储引擎的热插拔,并且存储引擎五花八门,MySQL的说法是适应不同的应用场景,非常抱歉的是现在的应用都是比较综合型的,MySQL的大多数引擎已经不能适合现在的应用,而PostgreSQL应对现在的高并发业务非常适合,对比如下)
参考《High Performance MySQL》一书,书是2008年6月份出版的(因此可能以下的对比拿到现在会有些出入).
首先简单的介绍一下MySQL的架构,如图:
PostgreSQL VS MySQLs Storage EngineSss.. - 德哥@Digoal - The Heart,The World.
 
第一层主要负责处理连接请求,认证,安全。
第二层主要功能包括SQL解析,分析,优化器,查询缓存,以及所有的内建函数(如:date,times,math,encryption等),任何与跨存储引擎的功能都在这一层如存储过程,触发器,视图等。
第三层存储引擎层,负责数据存取,每个存储引擎有各自的特性,就好似UNIX操作系统的各种文件系统。Server 通过存储引擎API与存储引擎交互,API包括一些底层功能,如开始一个事务。存储引擎不干解析SQL的事情,引擎之间也不能相互通信(必须通过Server交换)。只负责相应Server端请求。这里有个例外,因为目前MySQL Server不处理FK关系,而InnoDB是支持FK的,因此在InnoDB中是存储引擎负责解析FK定义的。
存储引擎过多,一个是造成维护的麻烦,开发也会更加复杂,如事务型和非事务型表在同一个事务中的处理,还有引擎间表的互转移,权限和FK丢失等等一系列问题。
下面简单介绍一下MySQL的存储引擎,
查看存储引擎:
mysql> SHOW TABLE STATUS LIKE 'user' \G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 59
    Data_length: 356
Max_data_length: 4294967295
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2002-01-24 18:07:17
    Update_time: 2002-01-24 21:56:29
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges
1 row in set (0.00 sec)

1. MyISAM : 
在MySQL 5.5GA以前,MyISAM一直作为MySQL的默认引擎,拥有支持全文索引,压缩,空间函数等特性,缺点是不支持行锁和事务,因此并发能力极差(insert和select不冲突,其他的写都是排他锁)。
存储:
索引和数据分别存在.MYD和.MYI,文件具有平台无关性,如x86的文件放到PowerPC下使用。
MyISAM可以存储变长记录和固定长度记录,表的定义决定MySQL使用何种方式存放记录,MyISAM可以存放的最大记录数受到文件系统剩余空间
和单个文件大小上限的限制。在建表时可以使用MAX_ROWS 和AVG_ROW_LENGTH指定最大记录数和平均记录的SIZE。在MySQL5.0版本中,默认MyISAM表的限制是256TB,pointer占用6 bytes,早期版本默认pointers占用4bytes,因此表的现在是4GB.pointer最大可以占用8bytes
CREATE TABLE mytable (
   a    INTEGER  NOT NULL PRIMARY KEY,
   b    CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
mysql> SHOW TABLE STATUS LIKE 'mytable' \G
*************************** 1. row ***************************
           Name: mytable
         Engine: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 98784247807
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2002-02-24 17:36:57
    Update_time: 2002-02-24 17:36:57
     Check_time: NULL
 Create_options: max_rows=1000000000 avg_row_length=32
        Comment:
1 row in set (0.05 sec)
特征:
reader获得shared lock.writer获得exclusive锁,因此并发性极差。(insert和select 锁不冲突,这要再冲突的话MyISAM估计活不到现在)
自动修复 或 手工修复数据表
check table , repair table或者使用myisamchk 在数据库关闭时修复。(数据容易被破坏,要不整这玩意干啥)
支持BLOB,TEXT前500字符的索引,(据说是为了防止索引过大,所以整了个前500字符的限制,搞个函数索引不就完了么,整这限制干啥)
delay_key_write : 即索引的更新在内存中暂缓,降低IO开销,听起来不错,可惜的是如果出现故障,索引要完全重建。
compressed MyISAM table :  使用myisampack可以将myisam表压缩,以行为单位压缩,支持索引,遗憾的是压缩表是只读的。

2. MyISAM Merge引擎
类似视图,把多个MyISAM表合并成一个表对外。

3. InnoDB引擎
InnoDB是一个支持事务的引擎。使用MVCC来满足高并发的场景需求。
一个比较致命的问题是InnoDB的表建立在Clustered Index之上.因此所有的索引都会包含PK的内容,PK必须使用精简列来减少索引的空间占用。
任何改变InnoDB表结构的操作都将导致表和所有索引的重建。
InnoDB支持预加载数据,内存哈希索引,插入缓存等特性来提高性能。

4. 内存存储引擎
之前也被称为heap表,内存表重启后数据将丢失,常用于lookup和mapping表的场景。支持HASH索引。
缺点是不支持事务,使用表级锁,只支持定长的字段类型等。
MySQL在临时表的处理方面,如果没有超出参数指定值的话都将使用内存表来处理,超过将自动转换为MyISAM来处理。

5. 归档引擎
只支持insert和 select,不支持索引,支持行级压缩,支持插入缓存。由于不支持索引,所以在查询方面性能不行,但是MySQL提供了复制,并且可以在SLAVE端使用不同的引擎。这就使得SLAVE端可以选用支持索引的引擎来加快扫描速度。

6. CSV引擎
类似外部表,支持CSV格式的文件,不支持索引。

7. Federated引擎
用于操控远程数据库记录,在统计,JOIN等其他方面使用不是太好。一般用于PK来取数据,或INSERT来插入记录。类似DBLINK,但是功能较弱

8. 黑洞引擎
类似/dev/null,往里面写东西 啥都不干。一般用作数据复制的MASTER,由于MASTER啥都不干,没有IO,提高写入效率。

9. NDB Cluster引擎
一个shared nothing的存储引擎,包含数据节点,管理节点,SQL实例节点。每个数据节点存放部分数据,并且数据节点之间相互有对方的数据,确保数据至少有两份在不同的节点上。管理节点监控和配置SQL集群。由于SQL实例和数据节点之间通信频繁,低的延时对NDB 集群非常重要。

10. Falcon引擎
支持MVCC的事务引擎

11. solidDB引擎
支持MVCC的事务引擎,使用集群索引,支持FK,支持在线备份,

12. PBXT引擎
一般用作高并发的写场景,支持MVCC,支持事务,支持FK。使用非集群索引。

13. Maria存储引擎
开发中

PostgreSQL包含了几乎所有MySQL这些存储引擎的优点,支持MVCC,支持事务,支持所有事务隔离级别,支持全文索引,支持自动压缩,支持行锁,支持FK,支持CSV快速导入导出,支持外部表(GreenPlum),支持列存储(GreenPlum),支持资源控制(GreenPlum),支持shared nothing(Plproxy或PGXC,pg-grid,pgpool等都可以做),支持复制(支持master-master ,master-slave),支持写规则(比MYSQL黑洞引擎更加灵活),支持继承(比merge引擎更加灵活),使用非集群索引,支持手工修正数据表存储顺序(类似瞬态cluster索引),支持并行运算(GreenPlum)。等等
还有一些其他更加优异的优点:
如:
支持MMAP,将IO请求高的PAGE放入内存区域。更强大的是可以记录下状态,下次开启数据库直接将之加载至内存.
支持部分索引,索引变小,维护开销降低
支持在线备份,支持PITR
支持日志级复制,表级复制,流复制等等
支持非常强大的数据库DBLINK,甚至异构平台,如到oracle , mysql的 dblink
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
10天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
10天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
16天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
4月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
288 62
|
4月前
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
329 1
|
10月前
|
关系型数据库 MySQL 数据库
市场领先者MySQL的挑战者:PostgreSQL的崛起
PostgreSQL(简称PG)是世界上最先进的开源对象关系型数据库,起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称,支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL,但PG在全球范围内广泛应用,受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
462 4
|
11月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
549 4
|
11月前
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
354 1
|
4月前
|
存储 关系型数据库 测试技术
拯救海量数据:PostgreSQL分区表性能优化实战手册(附压测对比)
本文深入解析PostgreSQL分区表的核心原理与优化策略,涵盖性能痛点、实战案例及压测对比。首先阐述分区表作为继承表+路由规则的逻辑封装,分析分区裁剪失效、全局索引膨胀和VACUUM堆积三大性能杀手,并通过电商订单表崩溃事件说明旧分区维护的重要性。接着提出四维设计法优化分区策略,包括时间范围分区黄金法则与自动化维护体系。同时对比局部索引与全局索引性能,展示后者在特定场景下的优势。进一步探讨并行查询优化、冷热数据分层存储及故障复盘,解决分区锁竞争问题。
439 2
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
514 0