您好,登录后才能下订单哦!
有时间讨论一下。
http://blog.chinaunix.net/u/17076/showart.php?id=174108#fig1
[@more@]体系结构概述和对比 在 DB2、MySQL 和 PostgreSQL 之间有许多差异。我们先看看这三种数据库服务器在基本体系结构方面的一些差异和相似之处。
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 实例,每个实例管理几个数据库,那么可以采用以下迁移方式之一:
注意: 当我们提到基于进程的体系结构时,指的是 UNIX® 平台,因为 Windows 上的模型是基于线程的体系结构。DB2 和 PostgreSQL 都是这样的。 在一个 DB2 连接的范围内只能访问一个数据库资源,而 MySQL 允许在同一个连接的范围内访问多个数据库资源。 MySQL 最有意思的特性之一是可插入的存储引擎。可以选择 MyISAM、InnoDB、Archive、Federated、Memory、Merge、Cluster、NDB 或 Custom 存储引擎。每个存储引擎具有不同的性质,可以根据自己的特定需求选择某一存储引擎。对于比较,我们发现 InnoDB 最接近于关系数据库。 图 1. MySQL 体系结构和进程概况 MySQL 服务器进程(mysqld)可以创建许多线程:
MySQL 使用数据缓存、记录缓存、键缓存、表缓存、主机名缓存和特权缓存来缓存和检索服务器进程中所有线程所使用的不同类型的数据。 另外,MySQL 主进程(mysqld)具有用来处理数据库管理活动的线程,比如备份、恢复、并发控制等等。 PostgreSQL PostgreSQL 实例(见 图 2)可以管理一个数据库集群。每个数据库有自己的系统编目,INFORMATION_SCHEMA 和 pg_catalog。所有数据库共享 在逻辑上,PostgreSQL 数据库可以迁移到 DB2 数据库。这两种数据库都支持模式对象类型。不能从命名的连接访问其他数据库。 PostgreSQL 和 DB2 之间最显著的差异与表空间相关。PostgreSQL 表空间可以跨越多个数据库,而 DB2 表空间特定于一个数据库。 图 2. PostgreSQL 体系结构和进程概况 PostgreSQL 会话由几个主进程组成:
DB2 图 3 显示 DB2 的体系结构。这张图解释了 DB2 如何使用缓冲池在磁盘之间处理数据(文件、原始设备、目录等等)。DB2 使用一个连接集中器来处理大量连接。DB2 页清理器和预获取器异步地工作,各个进程单独处理重做日志活动。关于 DB2 中锁和进程的工作方式的详细描述,请参见 参考资料。 图 3. DB2 体系结构和进程概况 DB2 会话由几个进程组成:
DB2 服务器进程通过称为数据库管理器内存(Database Manager Memory)和数据库共享内存(Database Shared Memory)的内存区域相互通信,见 图 4。 图 4. DB2 数据库管理器(实例)和数据库共享内存体系结构 特性对比 表 1 对比了 MySQL、PostgreSQL 和 DB2 特性。这不是一个完整的列表,但是对比了最常用的特性。 表 1. MySQL、PostgreSQL 和 DB2 特性对比 | ||||||
特性 | MySQL | PostgreSQL | DB2 | |||
---|---|---|---|---|---|---|
实例 | 通过执行 MySQL 命令(mysqld)启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。 | 通过执行 Postmaster 进程(pg_ctl)启动实例。一个实例可以管理一个或多个数据库,这些数据库组成一个集群。集群是磁盘上的一个区域,这个区域在安装时初始化并由一个目录组成,所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。 | 实例是一个 DB2 安装,它管理一个或多个数据库。在安装期间创建一个默认实例。使用 | |||
数据库 | 数据库是命名的对象集合,是与实例中的其他数据库分离的实体。一个 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 | |||
XML 支持 | 有限的 XML 支持。 | 有限的 XML 支持。 | 为访问 XML 数据提供丰富的支持。DB2 Viper(V9)是第一个以原生形式存储/检索 XML 的混合型数据库。 | |||
数据访问和管理服务器 | OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片 | Vacuum —— 回收未使用的空间 | Reorg —— 用来重新整理数据并消除数据碎片 | |||
并发控制 | 支持表级和行级锁。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 | 存储长度可变的二进制数据,长度最大为 2 GB。超过 1 GB 的长度不进行日志记录。 | |||||||||||||||
CHAR(n) | 存储固定长度的字符数据,长度最大为 254 字节。使用 ‘n’ 字节的存储空间。 | |||||||||||||||
CHAR(n) FOR BIT DATA | 存储固定长度的二进制值。 | |||||||||||||||
CLOB | 存储长度可变的字符数据,长度最大为 2 GB。超过 1 GB 的长度不进行日志记录。 | |||||||||||||||
DATE | 存储日历日期,不包含天内的时间。使用 4 字节的存储空间。 | |||||||||||||||
DEC(p,s) | 采用精度(p)1 到 31 和刻度(s)0 到 31 来存储数值。使用 (p/2) +1 字节的存储空间。 | |||||||||||||||
DOUBLE | 存储浮点数,使用 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 | 存储有符号或无符号整数,使用 4 字节的存储空间。 | |||||||||||||||
REAL | 存储浮点数,使用 4 字节的存储空间。 | |||||||||||||||
SMALLINT | 存储有符号和无符号整数,使用 2 字节的存储空间。 | |||||||||||||||
TIME | 存储天内的时间,使用 3 字节的存储空间。 | |||||||||||||||
TIMESTAMP | 存储日期(年、月、日)和时间(小时、分钟、秒),最大精度 6 毫秒。使用 10 字节的存储空间。 | |||||||||||||||
VARCHAR(n) | 存储长度可变的字符数据,长度最大为 32,672 字节。使用 n+2 字节的存储空间。 | |||||||||||||||
VARCHAR(n) FOR BIT DATA | 存储长度可变的二进制数据。使用 n 字节的存储空间。 | |||||||||||||||
VARGRAPHIC(n) | 存储长度可变的双字节字符数据,长度最大为 16,336 字符。使用 (n*2)+2 字节的存储空间。 | |||||||||||||||
MySQL 和 DB2
MySQL 可以使用 SERIAL 别名作为数据类型,这相当于 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 | 用于存储二进制对象(比如图形)的原始二进制数据,最大 16,777,215 字节。 | |||||||||||||||
LONGBLOB | 用于存储二进制对象(比如图形)的原始二进制数据,最大 4GB。 | |||||||||||||||
CHAR(n) | 包含固定长度的字符串,用空格填充到长度 n。 | |||||||||||||||
DATE | 用 3 字节的存储空间存储日历日期(年、月、日)。 | |||||||||||||||
DATETIME | 用 8 字节的存储空间存储日历日期和天内的时间。 | |||||||||||||||
YEAR | 用 1 字节的存储空间存储两位或四位格式的年份。 | |||||||||||||||
DECIMAL(p,s) | 存储精确的数值,精度(p)最高为 65,刻度(s)为 30 或更高。 | |||||||||||||||
FLOAT | 存储浮点数,限制由硬件决定。单精度浮点数精确到大约 7 位小数。UNSIGNED 属性不允许负数。 | |||||||||||||||
DOUBLE | 存储双精度浮点数,限制由硬件决定。双精度浮点数精确到大约 15 位小数。UNSIGNED 属性不允许负数。 | |||||||||||||||
TINYINT | 存储有符号或无符号 1 字节整数。 | |||||||||||||||
SMALLINT | 存储有符号或无符号 2 字节整数。 | |||||||||||||||
MEDIUMINT | 存储有符号或无符号 3 字节整数。 | |||||||||||||||
INTEGER | 存储有符号或无符号 4 字节整数。 | |||||||||||||||
BIGINT | 存储有符号或无符号 8 字节整数。 | |||||||||||||||
TINYTEXT | 用于存储最多 255 字节的字符串数据。 | |||||||||||||||
TEXT | 用于存储最多 65,535 字节的字符串数据。 | |||||||||||||||
MEDIUMTEXT | 用于存储最多 16,777,215 字节的字符串数据。 | |||||||||||||||
LONGTEXT | 用于存储最多 4GB 的字符串数据。 | |||||||||||||||
TIME | 用 3 字节的存储空间存储天内的时间。 | |||||||||||||||
TIMESTAMP | 用 4 字节的存储空间存储日期和时间。如果没有提供有效值的话,TIMESTAMP 列会自动设置为最近操作的日期和时间。 | |||||||||||||||
VARCHAR(n) | 存储长度可变的字符串,最大长度由 n 指定。末尾的空格不存储。 | |||||||||||||||
ENUM | 一种串对象,它的值只能是从值列表 ‘value1’, ‘value2’, ..., NULL 中选择的一个值。 | |||||||||||||||
SET | 一种串对象,它可以具有零个或更多的值,这些值必须从值列表 ‘value1’, ‘value2’, ... 中选择。 | |||||||||||||||
BINARY | 与 CHAR 类型相似,但是存储二进制字节串而不是字符串。 | |||||||||||||||
VARBINARY | 与 VARCHAR 类型相似,但是存储二进制字节串而不是字符串。 | |||||||||||||||
| ||||||||||||||||
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) | CHAR(n) | 在 DB2 中,‘n’ 的最大值为 254。 | ||||||||||||||
DATE | DATE | - | ||||||||||||||
DATETIME | TIMESTAMP | 可以使用特殊寄存器 CURRENT TIMEZONE 对日期进行转换。 | ||||||||||||||
YEAR | SMALLINT | 可以使用检查约束实施 YEAR 规则。 | ||||||||||||||
DECIMAL(p,s) | DECIMAL(p,s) | 如果 p 大于 31,那么使用 DOUBLE。 | ||||||||||||||
FLOAT | REAL | _ | ||||||||||||||
DOUBLE | DOUBLE | _ | ||||||||||||||
SMALLINT | SMALLINT | 使用检查约束限制值小于 256。 | ||||||||||||||
SMALLINT | SMALLINT | _ | ||||||||||||||
MEDIUMINT | INTEGER | 如果需要,使用检查约束限制最大长度。 | ||||||||||||||
INTEGER | INTEGER | _ | ||||||||||||||
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) | VARCHAR(n) | 如果长度小于 32K,那么使用 VARCHAR。 | ||||||||||||||
ENUM | VARCHAR(n) | 使用检查约束来实施规则。 | ||||||||||||||
SET | VARCHAR(n) | 使用检查约束来实施规则。 | ||||||||||||||
BINARY | CHAR(n) FOR BIT DATA | 如果 n 小于 254,那么使用 CHAR(n) FOR BIT DATA;否则使用 VARCHAR(n) FOR BIT DATA。 | ||||||||||||||
VARBINARY | VARCHAR(n) FOR BIT DATA | 如果 ‘n’ 小于 32K,那么使用 VARCHAR;否则使用 BLOB。 | ||||||||||||||
PostgreSQL 和 DB2
|