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

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

体系结构模型

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

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

存储引擎

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

DB2 使用专用的存储引擎。

表空间模型

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

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

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

模式支持

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 数据库相似。

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

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

MysqL

MysqL 使用一种基于线程的体系结构，而 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 数据库。这两种数据库都支持模式对象类型。不能从命名的连接访问其他 数据库。

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

2. Postgresql 体系结构和进程概况 Postgresql 会话由几个主进程组成：

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

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

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

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

DB2

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

3. DB2 体系结构和进程概况 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 对比了 DB2 特性。这不是一个完整的列表，但是对比了最常用的特性。

表 1. MysqL 、 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 和密码。使用角色管理数据库中的用户和用户组。

客户机使用 ID 和密码。使用 *** 作系统命令在数据库外创建用户和用户组。

身份验证

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

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

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

加密

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

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

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

审计

可以对 querylog 执行 grep 。

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

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

查询解释

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

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

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 （专用的过程语言）、C 编写。

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

触发器

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

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

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

系统配置文件

my.conf

Postgresql.conf

Database Manager Configuration

数据库配置

Database Configuration

客户机连接文件

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 语句级或使用数据库配置参数在数据库级设置隔离级别。

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

DB2 之间的数据类型对比

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

表 2 列出最常用的 DB2 数据类型。我们在后面的小节中提供 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)

存储长度可变的字符数据，长度最大为 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)

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

GRAPHIC(n)

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

INT
INTEGER

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

REAL

存储浮点数，使用 SMALliNT

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

TIME

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

TIMESTAMP

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

VARCHAR(n)
CHAR varying(n)
CHaraCTER varying(n)

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

VARCHAR(n) FOR BIT DATA

存储长度可变的二进制数据。使用 VArgraphIC(n)

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

DB2

MysqL 可以使用 SERIAL 别名作为数据类型，这相当于 BIGINT UNSIGNED NOT NulL auto_INCREMENT UNIQUE 。

BOol 或 BOolEAN 是 tinyint(1) 的同义词。在 MysqL 中，DECIMAL 的最大位数是 65 ，支持的最大小数位是 30 。如果为 DECIMAL 指定 UNSIGNED ，那么不允许负数。

时间戳列不支持毫秒。

3. MysqL 数据类型

BIT

固定长度的位串。

BOolEAN

存储逻辑布尔值（true/false/unkNown ），可以是 TRUE 、true 和 1 ；FALSE 、false 和 0 。

TINYBLOB

用于存储二进制对象（比如图形）的原始二进制数据，最大 255 字节。

BLOB

用于存储二进制对象（比如图形）的原始二进制数据，最大 65,535 字节。

MEDIUMBLOB

用于存储二进制对象（比如图形）的原始二进制数据，最大

LONGBLOB

用于存储二进制对象（比如图形）的原始二进制数据，最大 4GB 。

CHAR(n)
CHaraCTER(n)

包含固定长度的字符串，用空格填充到长度 n 。

DATE

用 3 字节的存储空间存储日历日期（年、月、日）。

DATETIME

用 8 字节的存储空间存储日历日期和天内的时间。

YEAR

用 1 字节的存储空间存储两位或四位格式的年份。

DECIMAL(p,s)

存储精确的数值，精度（p ）最高为 65 ，刻度（s ）为 30 或更高。

float

存储浮点数，限制由硬件决定。单精度浮点数精确到大约 7 位小数。UNSIGNED 属性不允许负数。

DOUBLE
REAL

存储双精度浮点数，限制由硬件决定。双精度浮点数精确到大约 15 位小数。tinyint

存储有符号或无符号 1 字节整数。

SMALliNT

存储有符号或无符号 2 字节整数。

MEDIUMINT

存储有符号或无符号 3 字节整数。

INTEGER

存储有符号或无符号 4 字节整数。

BIGINT

存储有符号或无符号 8 字节整数。

TINYTEXT

用于存储最多 255 字节的字符串数据。

TEXT

用于存储最多

MEDIUMTEXT

用于存储最多

LONGTEXT

用于存储最多 4GB 的字符串数据。

TIME

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

TIMESTAMP

用 4 字节的存储空间存储日期和时间。如果没有提供有效值的话，TIMESTAMP 列会自动设置为最近 *** 作的日期和时间。

VARCHAR(n)
CHaraCTER varying(n)
CHaraCTER varying

存储长度可变的字符串，最大长度由 n 指定。末尾的空格不存储。

ENUM

一种串对象，它的值只能是从值列表 ‘value1’,‘value2’,...,NulL 中选择的一个值。

SET

一种串对象，它可以具有零个或更多的值，这些值必须从值列表 ‘

BINARY

与 CHAR 类型相似，但是存储二进制字节串而不是字符串。

VARBINARY

与 VARCHAR 类型相似，但是存储二进制字节串而不是字符串。

表 4. MysqL 数据类型到 DB2 的映射

BIT

CHAR(n) FOR BIT DATA

关于用来简化迁移的 UDF 的细节，请参阅 参考资料 。

BOolEAN

SMALliNT 或 CHAR(1)

使用检查约束来实施规则。

TINYBLOB

VARCHAR(255) FOR BIT DATA

可以使用 BLOB(255) 或 VARCHAR(255) FOR BIT DATA 。在这种情况下，使用 VARCHAR 效率比较高。

BLOB

BLOB(64K)

如果长度小于 32K ，那么考虑使用 VARCHAR(n) FOR BIT DATA 。

MEDIUMBLOB

BLOB(16M)

可以使用 NOT LOGGED 改进性能。

LONGBLOB

BLOB(2G)

支持的 BLOB 最大长度是 2GB 。

CHAR(n)
CHaraCTER(n)

CHAR(n)
CHaraCTER(n)

在 DB2 中，‘n’ 的最大值为 254 。

DATE

DATE

-

DATETIME

TIMESTAMP

可以使用特殊寄存器 CURRENT TIMEZONE 对日期进行转换。

YEAR

SMALliNT

可以使用检查约束实施 YEAR 规则。

如果 p 大于 31 ，那么使用 DOUBLE 。

float

REAL

_

DOUBLE
REAL

DOUBLE

SMALliNT

SMALliNT

使用检查约束限制值小于 256 。

SMALliNT

MEDIUMINT

INTEGER

如果需要，使用检查约束限制最大长度。

INTEGER

INTEGER
INT

BIGINT

BIGINT

TINYTEXT

VARCHAR(255)

对于少于 32K 的数据，使用 VARCHAR 比较高效。

TEXT

CLOB(64K)

DB2 允许为 CLOB 或 BLOB 指定长度参数。指定需要的长度，而不要使用 TINY 、MEDIUM 或 LONG CLOB 。

MEDIUMTEXT

CLOB(16M)

LONGTEXT

CLOB(2G)

最大长度是 2GB 。如果使用 LOGGED ，那么 BLOB 或 CLOB 的最大长度为 1GB 。使用 NOT LOGGED 选项可以提高性能。

TIME

TIME

TIMESTAMP

TIMESTAMP

VARCHAR(n)
CHaraCTER varying(n)

VARCHAR(n)
CHaraCTER varying(n)

如果长度小于 32K ，那么使用 VARCHAR 。

ENUM

VARCHAR(n)

使用检查约束来实施规则。