【赠书】从商用到开源：15个维度，全面剖析DB2与MySQL数据库的差异

云和恩墨为某证券公司进行了从DB2到MySQL数据库系统的迁移论证、验证，对两类数据库展开全方位多角度的对比分析，并根据用户的业务现状进行了相关架构、性能、备份恢复及高可用验证。本系列将带领大家全面学习DB2迁移至MySQL的实践。

前文回顾：

从商用到开源：DB2迁移至MySQL的最佳实践

今天一起来学习DB2与MySQL数据库的特征对比。快上车！

一、隔离级别

标准的隔离级别共四个，分别是：

未授权读取

也称为读未提交（ReadUncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

• 授权读取

也称为读提交（ReadCommitted）：允许 不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

• 可重复读取（Repeatable Read）

可重复读取（RepeatableRead）：禁止 不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

• 序列化（Serializable）

序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把 数据库系统的隔离级别设为 ReadCommitted。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致 不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

MySQL支持标准的四个隔离级别。在可重读级别通过多版本控制解决了幻读问题，默认隔离级别是REPEATABLE-READ。

DB2也支持标准的四个隔离级别。不过，标准隔离级别的序列化和db2的可重复读对等；标准隔离级别的可重复读和db2的读稳定性对等；标准隔离级别的读已提交和db2的游标稳定对等；标准隔离级别的读未提交和db2的读未提交对等。

具体如下表：

例1 在MySQL配置文件中设置隔离级别。

[mysqld]
transaction-isolation =REPEATABLE-READ

例2 在MySQL执行过程中设置隔离级别。

SELECT@@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBALtx_isolation='REPEATABLE-READ';
SET SESSIONtx_isolation='SERIALIZABLE';

二、数据库约束

约束的简介

数据的完整性是指数据的正确性和一致性，可以通过定义表时定义完整性约束，也可以通过规则，索引，触发器等。约束分为两类：行级和表级，处理机制是一样的。行级约束放在列后，表级约束放在表后，多个列共用的约束放在表后。

约束类型

常用的几种约束：唯一约束，非空约束，外键约束，检查约束。 其中MySQL完全支持唯一约束、非空约束、外键约束，但有限支持检查约束，通过enum实现。DB2支持所有约束。

如下表：

三、序列（Sequence）

Sequence是数据中一个特殊存放等差数列的表，该表受数据库系统控制，任何时候数据库系统都可以根据当前记录数大小加上步长来获取到该表下一条记录应该是多少，这个表没有实际意义，常常用来做主键用。不过各个数据库厂商没有一个统一的标准，各有各的一套对Sequence的定义和操作。

Sequence主要有三个特性：自增列、独立序列、独立序列用于自增列。其中MySQL仅支持自增列，其他两个独立序列和独立序列用于自增列均不支持。DB2对三个特性都支持。

如下表：

例1 在MySQL中使用自增列：

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

四、表空间

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在特定的表空间中。

表空间是数据库系统中数据库逻辑结构与操作系统物理结构之间建立映射的重要存储结构，它作为数据库与实际存放数据的容器之间的中间层，用于指明数据库中数据的物理位置。任何数据库的创建都必须显式或隐式的为其指定表空间，且数据库中的所有数据都位于表空间中。

用户可以根据硬件环境以及成本等需求，通过指定建立在不同容器上的表空间来自由选择数据的物理存储位置。同时由于备份和恢复可以在表空间级别执行，用户能够进行更多粒度的备份恢复控制。

理解表空间前先理解容器。

容器（Container）

容器是物理存储设备，可以通过目录名，设备名或文件名进行标识。事实上，这也正是三种容器类型。设备容器（如磁带等）和文件容器被同等看待，通常直接将其理解为数据文件（磁盘存储内部结构中有介绍，数据文件中包含若干Extent。）。系统管理表空间只能使用目录容器，数据库管理表空间只能使用设备容器和文件容器。

容器被分配给某个表空间，单个表空间可以使用多个容器，但容器只能属于一个表空间。容器通常位于本地磁盘上，某些远程网络设备或文件也能作为表空间的容器，但由于网络延迟与可靠性方面的原因，远程容器对数据库安全与性能会造成影响，因此不建议使用远程容器。

DB2数据库系统的表空间有三种管理类型：

系统管理表空间（SMS，System-Managed Space）

SMS表空间由操作系统的文件系统管理器分配并管理。在这种表空间中，数据存储空间完全由操作系统管理，SMS表空间能够使用的唯一容器是目录容器，SMS表空间可以定义多个容器，目录容器可以根据需要增加大小，因此SMS表空间的大小是可以动态增加的。但是一旦SMS表空间创建，就不能再为表空间增加或删除容器了。SMS表空间中通常包含多个文件，这些文件代表了存储在文件系统空间中的表对象，比如表数据，表索引，表大对象都是单独占用一个或若干个文件的。一旦为表指定了SMS表空间，那么表中的数据就不允许分开存储，即表的常规数据，索引，大对象数据不能位于不同的表空间中。在DB2 V9之前的数据库版本中，创建数据库（创建数据库时如果不指定表空间则会默认创建3个表空间）或表空间的默认类型就是SMS表空间。另外，只有DB2数据库允许有系统管理表空间，Oracle数据库的表空间都是数据库管理的，不存在系统管理表空间。

数据库管理表空间（DMS，Database-Managed Space）

DMS表空间由数据库管理系统（DBMS）自己管理控制，本质上讲，这种类型的表空间是为了最大程度满足数据库管理器的需要而设计并实现的一种特定目的的文件系统。DMS表空间是由有限数量的容器所组成的，DMS表空间可以使用的容器有设备容器和文件容器，这些容器的空间都是预先分配的且不允许修改大小的，但是与SMS表空间不同的是，DMS表空间允许添加容器。也就是说，SMS表空间和DMS表空间分别通过扩大容器大小和增加容器数量的方式实现表空间大小的增加。DMS表空间创建时需要手动指定一个或多个容器。以文件为容器的表空间创建完以后就是一个单独的文件。使用DMS表空间的表的数据可以分开存储，即为常规数据，索引和大对象数据指定不同的DMS表空间。

DMS自动存储表空间（Automatic Storage DMS）

自动存储表空间不是真正意义上的独立类型的表空间。它是DMS存储的另外一种处理方法。DMS需要很多的维护操作，而自动存储器则是作为一种简化的空间管理手段，能够自动进行表空间的管理维护，它是DB2 V8.8.2中引入的概念，目前取代SMS成为默认的表空间类型。

这部分我们主要从三方面对比：是否支持表空间、是否支持索引表空间、是否支持大字段表空间。

其中MySQL仅支持表空间，但不支持索引表空间和大字段表空间。对于MySQL的innodb存储引擎，同一张表的索引和数据是放在同一个表空间，无法分离。DB2 支持表空间、支持索引表空间、支持大字段表空间。

如下表：

例1 MySQL创建表空间语法：

CREATE TABLESPACEtablespace_name
  InnoDB and NDB:
    ADD DATAFILE 'file_name'
  InnoDB only:
    [FILE_BLOCK_SIZE = value]
  NDB only:
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    [AUTOEXTEND_SIZE [=] autoextend_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] comment_text]
  InnoDB and NDB:
    [ENGINE [=] engine_name]

五、XML

XML即可扩展标记语言（eXtensible Markup Language）。标记是指计算机所能理解的信息符号，通过此种标记，计算机之间可以处理包含各种信息的文章等。如何定义这些标记，既可以选择国际通用的标记语言，比如HTML，也可以使用象XML这样由相关人士自由决定的标记语言，这就是语言的可扩展性。XML是从SGML中简化修改出来的。它主要用到的有XML、XSL和XPath等。

MySQL仅支持对xml的存取，不支持xml相关函数。DB2支持对xml的存取，也支持xml相关函数。

例1 将MySQL查询结果导出为xml格式。

shell> mysql --xml -e'SELECT * FROM mydb.mytable' > file.xml

例2 MySQL load xml语法。

LOAD XML [LOW_PRIORITY |CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE [db_name.]tbl_name
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<tagname>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var,...)]
[SET col_name = expr,...]
例3MySQL load xml示例。
mysql> LOAD XML LOCALINFILE 'person.xml'
    ->  INTO TABLE person
    ->  ROWS IDENTIFIED BY'<person>';
Query OK, 8 rows affected(0.00 sec)
Records: 8  Deleted: 0 Skipped: 0  Warnings: 0

六、数据库锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

对于该问题，我们的关注点有三个：是否支持行锁、是否存在行锁升级、是否支持MVCC。

MySQL的innodb存储引擎支持行锁，不需要行锁升级，支持MVCC。

DB2支持行锁，存在行锁升级，部分支持MVCC。

如下表：

例1MySQL lock表和unlock表语法。

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
UNLOCK TABLES

七、数据导入

在数据库的日常运维中，我们进程面临需要将一个格式化的数据文本文件或sql文件导入数据库。我们的关注点主要有三方面：是否支持CSV文件、是否支持SQL、是否支持nolog。

MySQL支持CSV和SQL，但不建议使用nolog方式导入，因为MySQL有些功能必须依赖binlog方能实现，如果采用nolog方式，可能会对数据产生影响。DB2支持CSV、SQL、nolog导入。

如下表：

 

例1MySQL load语法：

LOAD DATA [LOW_PRIORITY |CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
[SETcol_name = expr,...]

例2 MySQLload示例。
CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE'/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

八、存储引擎

MySQL属于独有的插件式结构，支持多种存储引擎，每种引擎都有自己的特性。可根据实际需要选用不同的存储引擎。

其中常用的几个引擎如下：

• MyISAM  不支持事务，无特殊原因不建议在生产环境使用。
  
• InnoDB   属于当前主流存储引擎，被广泛采用，支持事务。
  
• NDB 分布式数据库
  

DB2 属于单一存储引擎。

MySQL可以在创建表时指定engine选项，例如：

CREATE TABLE `customer` (
  `C_CUSTKEY` int(11) NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` int(11) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL,
  PRIMARY KEY (`C_CUSTKEY`),
) ENGINE=InnoDB DEFAULTCHARSET=utf8

也可以使用alter table语句变更表引擎：

Alter table table_name ENGINE[=] engine_name;

九、数据类型

数据类型方面MySQL主要有以下几方面和DB2不同：

MySQL单行最长65532字节。
MySQL date类型不支持默认值。
MySQL使用ENUM作为check约束。
MySQL的BLOB最大为4G，不支持默认值。

十、DDL

DDL方面MySQL主要有以下几方面和DB2不同：

Mysql仅支持自增ID，且自增列必须为主键。
MySQL表分区支持range, list, hash, key分区方式。
MySQL不支持date默认值为当前。
MySQL无法指定独立的索引表空间。

例1 在MySQL中创建一个含有自增列的表。

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

例2 在MySQL中创建一个分区表。

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE(year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2003),
    PARTITION p4 VALUES LESS THAN (2007)
);

十一、Schema

DB2中的schema是依赖于database存在的，database下面有schema，schema下面有表。

MySQL中的database等同于schema，schema下面直接有表，进入一个schema时执行“use ‘schema_name’”。

例1 在MySQL中创建一个数据库mytest。

mysql> create databasemytest;
Query OK, 1 row affected(0.01 sec)
mysql>

例2 在MySQL中进入mytest数据库。

mysql> use mytest
Database changed
mysql>

十二、DML

DML方面MySQL主要有以下几方面和DB2不同：

MySQL不支持单行附带隔离级别的update。
MySQL使用limit语法代替fetch first语法。
MySQL的limit m,n语法支持翻页。
MySQL不支持窗口函数。

例1 在MySQL查询中使用limit语句。

mysql> SELECT * FROMratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |       1 |    4.5 |
|  5 |       1 |    3.2 |
|  4 |       2 |    3.5 |
|  3 |       2 |    3.7 |
|  6 |       2 |    3.5 |
+----+----------+--------+

十三、存储过程

存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

DB2支持存储过程。

MySQL的存储过程只支持基本特性。

例1 在MySQL中创建并调用procedure。

mysql> delimiter //
mysql> CREATE PROCEDUREsimpleproc (OUT param1 INT)
    -> BEGIN
    ->  SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected(0.00 sec)
mysql> delimiter ;
mysql> CALLsimpleproc(@a);
Query OK, 0 rows affected(0.00 sec)
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

十四、触发器

触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。

触发器方面MySQL主要有以下几方面和DB2不同：

MySQL支持多个event直接设置为单个触发器。

在MySQL5.7之前的版本，单表最多只能有一个触发器。

MySQL创建trigger的语法：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body
trigger_time: { BEFORE |AFTER }
trigger_event: { INSERT |UPDATE | DELETE }
trigger_order: { FOLLOWS |PRECEDES } other_trigger_name

例1 在MySQL中创建一个trigger。

mysql> CREATE TABLEaccount (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected(0.03 sec)
mysql> CREATE TRIGGERins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum +NEW.amount;
Query OK, 0 rows affected(0.01 sec)

十五、安全认证

安全认证方面MySQL主要有以下几方面和DB2不同：

MySQL将user@host作为一个独立用户。
MySQL支持最细粒度为列级别的授权。
MySQL无法对行级别进行授权。
MySQL商业版支持外部方式认证。

MySQL创建用户语法：

例1 在MySQL中创建一个只能从本地登陆的用户。

CREATE USER'jeffrey'@'localhost'
  IDENTIFIED WITH sha256_password BY'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY;

从商用到开源，从DB2到MySQL，从传统业务到互联网架构，一切正在发生。你准备好了吗？

本系列内容包含但不限于以下几个方面：

迁移准备

1. DB2与MySQL数据库对比分析。包含：数据库架构对比，数据类型对比，数据库对象对比，SQL对比等。
   
2. 测试。包含DB2与MySQL兼容性测试，MySQL性能测试，MySQL基于OLPT的测试等等。
   

迁移过程

1. 应用设计与改造。
   
2. MySQL高可用设计与部署
   
3. MySQL备份与恢复设计
   
4. 迁移中的重点问题和注意事项
   

迁移优化

1. 性能测试
   
2. 系统优化