DB2,PostgreSQL & MySQL体系结构概述和对比(转)

特性

MySQL

PostgreSQL

DB2

体系结构概述和对比

http://blog.chinaunix.net/u/17076/showart.php?id=174108#fig1

在 DB2 、MySQL 和 PostgreSQL 之间有许多差异。我们先看看这三种数据库 服务器在基本体系结构方面的一些差异和相似之处。

• 体系结构模型

• MySQL 数据库服务器使用一种基于专用服务器线程的体系结构。

• PostgreSQL 和 DB2 使用一种专用服务器进程模型体系结构。

• 存储引擎

• MySQL 数据库使用可插入的存储引擎体系结构。

• PostgreSQL 和 DB2 使用专用的存储引擎。

• 表空间模型

• MySQL 对于 InnoDB 和 NDB 存储引擎使用表空间。

• PostgreSQL 表空间可以跨越多个数据库。

• DB2 表空间特定于一个数据库。

• 模式支持

• PostgreSQL 和 DB2 具有真正的模式支持。

• MySQL 不具有真正的模式支持。MySQL 中的模式可以被看作 MySQL 数据库。

• 数据库对象 名是否是大小写敏感的

• Linux 上的 MySQL 和 PostgreSQL 使用大小写敏感的数据库名、表名和列名。

• 所有平台上的 DB2 都使用大小写不敏感的名称。但是，DB2 只按照大写存储名称。

• 数组列

• MySQL 和 DB2 不支持数组列。

• PostgreSQL 支持数组列。

• 身份验证

• DB2 使用各种外部安全机制来执行身份验证，比如操作系统、PAM 、Kerberos 、Active Directory 、LDAP 等等。它还允许插入第三方安全模块。

• PostgreSQL 按照许多不同方式之一执行身份验证。它可以使用数据库用户/ 角色、操作系统、PAM 、Kerberos 等等，这取决于主机配置文件（pg_hba.conf ）中的设置。

• MySQL 在数据库级实现身份验证并对密码进行加密。

• 实例体系结构

• DB2 实例管理不同的数据库，在同一台机器上可以有许多 DB2 实例。

• PostgreSQL 的实例概念与数据库集群相似。

• MySQL 实例与 DB2 数据库相似。

MySQL 和 PostgreSQL 数据库可以几乎每周都进行特性修改，但是 DB2 中的特性实现和修改要经过非常仔细的计划，这是因为有众多的业务依赖于使用 DB2 产品。请注意，在本文中我们使用 MySQL 5.1 、PostgreSQL 8.0.3 和 DB2 V8.2 进行比较，所以在阅读本文时请考虑到这一点。

图 1 、图 2 和 图 3 是 MySQL 、PostgreSQL 和 DB2 的体系结构图。我们在阅读一些文档之后竭尽我们的能力绘制出 MySQL 和 PostgreSQL 的体系结构图。如果您发现不符合实际情况的地方，请告诉我们，我们会进行纠正。

MySQL

MySQL 使用一种基于线程的体系结构，而 PostgreSQL 和 DB2 采用基于进程的体系结构。正如在 图 1 中看到的，一个 MySQL 实例可以管理许多数据库。一个实例中的所有 MySQL 数据库共享一个公用的系统编目，INFORMATION_SCHEMA 。

DB2 中的数据库是一个单独的实体，有自己的系统编目、表空间、缓冲池等等。DB2 实例管理不同的数据库，但是数据库并不共享表空间、日志 、系统编目或临时表空间。

具有许多数据库的一个 MySQL 实例可以看作 DB2 中的一个数据库，而每个 MySQL 数据库相当于 DB2 中的一个模式。如果服务器上运行几个 MySQL 实例，每个实例管理几个数据库，那么可以采用以下迁移方式之一：

• 将每个 MySQL 实例迁移为同一 DB2 实例下的一个 DB2 数据库。

• 将每个 MySQL 实例迁移为只包含一个 DB2 数据库的 DB2 实例，并为每个 MySQL 数据库分配不同的模式。

注意： 当我们提到基于进程的体系结构时，指的是 UNIX® 平台，因为 Windows 上的模型是基于线程的体系结构。DB2 和 PostgreSQL 都是这样的。

在一个 DB2 连接的范围内只能访问一个数据库资源，而 MySQL 允许在同一个连接的范围内访问多个数据库资源。

MySQL 最有意思的特性之一是可插入的存储引擎。可以选择 MyISAM 、InnoDB 、Archive 、Federated 、Memory 、Merge 、Cluster 、NDB 或 Custom 存储引擎。每个存储引擎具有不同的性质，可以根据自己的特定需求选择某一存储引擎。对于比较，我们发现 InnoDB 最接近于关系数据库。

图 1. MySQL 体系结构和进程概况

MySQL 服务器进程（mysqld ）可以创建许多线程：

• 一个全局线程（每个服务器进程有一个）负责创建和管理每个用户连接线程。

• 为处理每个新的用户连接创建一个线程。

• 每个连接线程还执行身份验证和查询。

• 在 Windows 上，有一个命名管道处理器线程，它针对命名管道连接请求执行与连接线程相同的工作 。

• 一个信号线程处理警报并迫使长时间空闲的连接超时。

• 分配一个线程来处理关闭事件。

• 有一些线程在复制期间处理主服务器和从服务器的同步。

• 使用线程处理表刷新、维护任务等等。

MySQL 使用数据缓存、记录缓存、键缓存、表缓存、主机名缓存和特权缓存来缓存和检索服务器进程中所有线程所使用的不同类型的数据。

另外，MySQL 主进程（mysqld ）具有用来处理数据库管理活动 的线程，比如备份、恢复、并发控制等等。

PostgreSQL

PostgreSQL 实例（见 图 2 ）可以管理一个数据库集群。每个数据库有自己的系统编目，INFORMATION_SCHEMA 和 pg_catalog 。所有数据库共享 pg_databases 作为公用系统表。每个数据库是一个单独的实体，数据库的集合称为集群。一个 PostgreSQL 实例可以管理一个数据库集群。一台服务器可以运行多个实例。

在逻辑上，PostgreSQL 数据库可以迁移到 DB2 数据库。这两种数据库都支持模式对象类型。不能从命名的连接访问其他 数据库。

PostgreSQL 和 DB2 之间最显著的差异与表空间相关。PostgreSQL 表空间可以跨越多个数据库，而 DB2 表空间特定于一个数据库。

图 2. PostgreSQL 体系结构和进程概况

PostgreSQL 会话由几个主进程组成：

• postmaster 进程是一个主管进程，它生成其他进程并监听用户连接。

• 用户进程（比如 psql ）用来处理交互式 SQL 查询。

• postmaster 生成一个或多个名为 postgres 的服务器进程来处理用户的数据请求。

• 服务器进程通过信号量和共享内存来相互通信。

DB2

图 3 显示 DB2 的体系结构。这张图解释了 DB2 如何使用缓冲池在磁盘之间处理数据（文件、原始设备、目录等等）。DB2 使用一个连接集中器来处理大量连接。DB2 页清理器和预获取器异步地工作，各个进程单独处理重做日志活动。关于 DB2 中锁和进程的工作方式的详细描述，请参见 参考资料 。

图 3. DB2 体系结构和进程概况

DB2 会话由几个进程组成：

•  

  	MySQL 、 PostgreSQL 和 DB2 之间有什么差异？ MySQL 使用基于线程的体系结构模型，而 PostgreSQL 和 DB2 使用基于进程的体系结构模型。 DB2 可以在一台服务器上有多个实例。每个实例中可以有许多数据库。每个数据库与其他数据库在物理上和逻辑上隔离。 MySQL 可以在一台服务器上运行多个 mysqld 实例。每个实例可以管理一个或多个 MySQL 数据库。一个实例中的每个数据库并不在物理上或逻辑上隔离。MySQL 中的每个数据库可以被看作 DB2 中的模式。 可以认为 PostgreSQL 数据库集群相当于 DB2 实例。

  db2sysc，主 DB2 系统控制器，即引擎进程。

• 监听器进程，比如 db2tcpcm 和 db2ipccm ，它们监听用户的连接请求。

• 一个或多个代表应用程序工作的代理。代理有两种类型：

• db2agent 代表一个应用程序工作，并使用进程间通信或远程通信协议与其他代理通信。

• db2agntp 用来在打开内部并行的情况下满足对数据库的客户机请求。

• 用户的进程（比如 db2 ）用来处理来自命令行的交互式查询。

• db2bp —— 一个持久的后台进程，用于 DB2 Command Line Processor （CLP ）。

• db2disp —— 一个代理调度器进程，在启用连接集中器的情况下用于将连接分配给可用的协作代理。

• db2fmcd —— 每个服务器的故障监视器协作守护进程。

• db2fmd —— 每个实例的故障监视器守护进程。

• db2resyn —— 一个重新同步管理器进程，用于处理两阶段提交。

• db2dlock —— 一个 DB2 死锁探测器。

• db2loggr —— 数据库日志读取器。

• db2loggw —— 数据库日志写入器。

• db2pclnr —— 缓冲池页清理器。

• db2pfchr —— 缓冲池预获取器。

• db2fmp —— 用于在服务器上 DB2 地址空间之外运行用户代码 。

• 等等

DB2 服务器进程通过称为数据库管理器内存（Database Manager Memory ）和数据库共享内存（Database Shared Memory ）的内存区域相互通信，见 图 4 。

图 4. DB2 数据库管理器（实例）和数据库共享内存体系结构

特性对比

表 1 对比了 MySQL 、PostgreSQL 和 DB2 特性。这不是一个完整的列表，但是对比了最常用的特性。

表 1. MySQL 、 PostgreSQL 和 DB2 特性对比

实例

通过执行 MySQL 命令（mysqld ）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl ）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装 时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。

实例是一个 DB2 安装，它管理一个或多个数据库。在安装期间创建一个默认实例。使用 db2start 命令启动实例。还可以使用 db2icrt 命令在同一台机器上创建多个实例。在创建数据库本身之前，并不分配数据存储。数据库可以使用原始设备自己管理存储，或使用操作系统文件系统。环境变量 DB2INSTANCE 决定要连接哪个实例。

数据库

数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。

数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases 。

数据库是命名的对象集合，是与其他数据库分离的实体。数据库是在物理上和逻辑上独立的实体，不与其他数据库共享任何东西。一个 DB2 实例可以管理一个或多个数据库。

数据缓冲区

通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80% 。

Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K 。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

在默认情况下分配一个缓冲池，并可以使用 CREATE BUFFERPOOL 命令添加其他缓冲池。默认的页大小在创建数据库时决定，可以是 4 、8 、16 或 32K 。

数据库连接

客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用操作系统命令在数据库外创建用户和用户组。

身份验证

MySQL 在数据库级管理身份验证。

PostgreSQL 的身份验证取决于主机配置。

DB2 使用 API 通过各种实现（比如 Kerberos 、LDAP 、Active Directory 和 PAM ）在操作系统级对用户进行身份验证，它的可插入身份验证体系结构允许插入第三方模块。

加密

可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。

可以使用 pgcrypto 库中的函数对列进行加密/ 解密。可以通过 SSL 连接实现网络加密。

可以使用 DB2 提供的加密和解密方法 对列数据进行加密/ 解密。如果在实例级选择 DATA_ENCRYPT 身份验证方法，那么可以对客户机和服务器之间的网络通信进行加密。

审计

可以对 querylog 执行 grep 。

可以在表上使用 PL/pgSQL 触发器来进行审计。

DB2 提供的 db2audit 实用程序可以提供详细的审计，而不需要实现基于触发器或日志的审计。

查询解释

使用 EXPLAIN 命令查看查询的解释计划。

使用 EXPLAIN 命令查看查询的解释计划。

DB2 提供的 GUI 和命令行工具可以用来查看查询的解释计划。它还可以从 SQL 缓存捕获查询并生成解释计划。可以使用工具查看所有存储过程中的 SQL 的解释计划。

备份、恢复和日志

InnoDB 使用写前（write-ahead ）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。

在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。

使用写前日志记录。支持完全、增量、delta 和表空间级在线/ 离线备份和恢复。支持崩溃、时间点和事务恢复。

JDBC 驱动程序

可以从 参考资料 下载 JDBC 驱动程序。

可以从 参考资料 下载 JDBC 驱动程序。

支持 Type-2 和 Type-4 （Universal ）驱动程序。JDBC 驱动程序是 DB2 产品的一部分。

表类型

取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。

支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。

支持用户表、临时表、常规表以及范围、哈希和多维簇类型的分区表。

索引类型

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

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

支持 B- 树和位图索引。

约束

支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。

支持主键、外键、惟一、非空和检查约束。

支持主键、外键、惟一、非空和检查约束。

存储过程和用户定义函数

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

虽然使用术语存储过程 ，但是只支持 CREATE FUNCTION 语句。用户定义函数可以用 PL/pgSQL （专用的过程语言）、SQL 和 C 编写。

支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL （SQL PL ）、C 、Java 、COBOL 和 REXX 编写。用户定义函数可以用 SQL （SQL PL ）、C 和 Java 编写。

触发器

支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。

支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。

支持行前触发器、行后和语句触发器、instead of 触发器和包含 SQL PL 复合语句的触发器。可以从触发器调用存储过程。

系统配置文件

my.conf

Postgresql.conf

Database Manager Configuration

数据库配置

my.conf

Postgresql.conf

Database Configuration

客户机连接文件

my.conf

pg_hba.conf

System Database Directory
Node Directory

XML 支持

有限的 XML 支持。

有限的 XML 支持。

为访问 XML 数据提供丰富的支持。DB2 Viper （V9 ）是第一个以原生形式存储/ 检索 XML 的混合型数据库。

数据访问和管理服务器

OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片
myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）
mysql —— 命令行工具
MySQL Administrator —— 客户机 GUI 工具

Vacuum —— 回收未使用的空间
Analyze —— 更新查询优化器所使用的统计数据
psql —— 命令行工具
pgAdmin —— 客户机 GUI 工具

Reorg —— 用来重新整理数据并消除数据碎片
Runstat —— 收集优化器所使用的统计数据
CLP —— 命令行工具
Control Center —— 客户机 GUI 工具

并发控制

支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED 、READ_UNCOMMITTED 、REPEATABLE_READ 和 SERIALIZABLE 。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。

支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed （默认 —— 能看到查询启动时数据库的快照）和 Serialization （与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

支持表级和行级锁以及 4 个隔离级别：RR （可重复读）、RS （读可靠）、CS （默认 —— 游标可靠）和 UR （未提交读）。使用 SET ISOLATION 在会话级、使用 WITH 子句在 SQL 语句级或使用数据库配置参数在数据库级设置隔离级别。

数据类型

说明

到目前为止，我们已经看到了 MySQL 、PostgreSQL 和 DB2 在体系结构和特性方面的一些差异。现在就来研究这些数据库服务器在数据类型方面的差异。

 

 

		回页首

MySQL 、PostgreSQL 和 DB2 之间的数据类型对比

SQL ANSI 标准规定了关系数据库系统中使用的数据类型的规则。但是，并非每种数据库平台都支持标准委员会定义的每个数据类型。而且，特定数据类型的厂商实现可能与标准的规定不同，甚至在所有数据库厂商之间互不相同。因此，尽管许多 MySQL 、PostgreSQL 和 DB2 数据类型在名称和/ 或含义方面是相似的，但是也有许多需要注意的差异。

表 2 列出最常用的 DB2 数据类型。我们在后面的小节中提供 MySQL 和 PostgreSQL 数据类型与 DB2 最接近的匹配。

尽管 DB2 对 SQL 有一些限制（比如对约束名的长度限制、数据类型限制等等），但是各个新版本正在系统化地消除这些限制。

表 2. DB2 数据类型

BIGINT

存储有符号或无符号整数，使用 8 字节的存储空间。

BLOB
BLOB(n)

存储长度可变的二进制数据，长度最大为 2 GB 。超过 1 GB 的长度不进行日志记录。

CHAR(n)
CHARACTER(n)

存储固定长度的字符数据，长度最大为 254 字节。使用 ‘n’ 字节的存储空间。

CHAR(n) FOR BIT DATA

存储固定长度的二进制值。

CLOB
CLOB(n)

存储长度可变的字符数据，长度最大为 2 GB 。超过 1 GB 的长度不进行日志记录。

DATE

存储日历日期，不包含天内的时间。使用 4 字节的存储空间。

DEC(p,s)
DECIMAL(p,s)
NUM(p,s)
NUMERIC(p,s)

采用精度（p ）1 到 31 和刻度（s ）0 到 31 来存储数值。使用 (p/2) +1 字节的存储空间。

DOUBLE
DOUBLE PRECISION
FLOAT

存储浮点数，使用 8 字节的存储空间。

FLOAT(p)

采用精度（p ）1 到 53 来存储数值。如果 p <= 24 ，那么相当于 REAL 。如果 p >= 25 ，那么相当于 DOUBLE PRECISION 。

GRAPHIC(n)

用于 National Language Support （NLS ）和长度固定的字符串（常常是 DBCS ），长度最大为 127 字节。对于双字节字符集，使用 n*2 字节的存储空间；对于单字节字符集，使用 n 字节的存储空间。

INT
INTEGER

存储有符号或无符号整数，使用 4 字节的存储空间。

REAL

存储浮点数，使用 4 字节的存储空间。

SMALLINT

存储有符号和无符号整数，使用 2 字节的存储空间。

TIME

存储天内的时间，使用 3 字节的存储空间。

TIMESTAMP

存储日期（年、月、日）和时间（小时、分钟、秒），最大精度 6 毫秒。使用 10 字节的存储空间。

VARCHAR(n)
CHAR VARYING(n)
CHARACTER VARYING(n)

存储长度可变的字符数据，长度最大为 32,672 字节。使用 n+2 字节的存储空间。

VARCHAR(n) FOR BIT DATA

存储长度可变的二进制数据。使用 n 字节的存储空间。

VARGRAPHIC(n)

存储长度可变的双字节字符数据，长度最大为 16,336 字符。使用 (n*2)+2 字节的存储空间。

MySQL 和 DB2

 

理解 MySQL 和 DB2 之间的数据类型问题 从性能方面考虑，将少于 32K 的 BLOB 和 CLOB 迁移为 VARCHAR(n) WITH BIT DATA 或 VARCHAR(n) 。迁移工具通过研究表中的实际数据处理这一转换。 迁移工具处理 UNSIGNED 数据类型。 将 Boolean 数据类型迁移到 SMALLINT 或 CHAR(1) 。 如果小数精度大于 31 ，那么迁移工具将列转换为