MySQL与DB2数据库差异化分析研究

  MySQL数据库目前在联盟的使用场景越来越广泛且应用场景也越来越重要。但是目前我们在使用和维护MySQL数据库上还缺乏积累和沉淀。目前MySQL已经被Oracle收购且MySQL在很多方面的设计思想与oracle类似，所以本文将不再讨论Oracle与MySQL的差别，主要探讨一下联盟在使用和维护DB2、MySQL数据库中需要关注的点及功能上存在的差异。整理本文档的目的旨在比较分析数据库MySQL与联盟目前广泛使用的DB2数据库在功能及使用等方面存在的差别。为联盟今后如何正确的使用MySQL数据库方面提供一些指导。希望本文档能够为从事MySQL数据库开发、运维方面的同事提供一些有价值的信息。
一、具体差异点
1.账号管理方式

   MySQL账号管理的方式与Oracle类似，由MySQL自己管理，账号存储在MySQL数据库的user表中，其账号的组成是有ip地址+用户名组成，而DB2没有自己的用户体系，其认证完全依赖于操作系统的账号。
2.权限管理方式

   MySQL的权限管理方式与DB2权限管理方式上相同的地方就是都可以对单独的对象进行授权，都可以做到细粒度的授权。不同之处就是MySQL在账号安全上更胜一筹，其账号组成是由用户名加ip地址构成，也就是说其鉴权需要同时验证用户登录的ip和用户名的合法性，而db2只对连接的账号进行鉴权。MySQL的另一个优点就是对最小单元的对象可以批量进行授权，DB2则不能，相对比较繁琐，这也是DB2需要改进的地方之一。
3.日志管理方式

   MySQL数据库使用日志双写的方式来保证数据的完整性与可恢复性，MySQL的事务日志与归档日志两个独立的对象，没有任何因果关系。DB2数据库的归档日志是有事务日志产生的。所以在UDI操作上MySQL数据库的性能要比DB2差一些。
4.锁的管理方式

  MySQL使用MVCC模型实现锁的并发控制，DB2使用内存模型实现了锁的并发控制，在并发处理、处理资源冲突上讲MySQL的并发处理能力、锁冲突的方式上要优于DB2。
5.schema的管理方式

  MySQL数据库严格意义上讲没有schema的概念，其每个schema相当于独立的一个数据库。db2数据库可以在一个数据库能创建多个schema。这也是MySQL在schema方面存在的不足及需要改进的地方。
6.表空间的管理方式

  MySQL数据库在5.6之后才有了表空间的概念，但其在表空间的使用方式上还是与企业级数据库相差很大。MySQL数据库在表空间上支持相对较弱，存在很多的局限性，在条带化及空间的管理上存在软肋。DB2在这些方面做得都十分完善，而且非常健壮易于维护，这些都是MySQL需要加强向企业数据库学习的地方。
7.事物行为的处理方式

  MySQL数据库默认在处理事务相关的操作时只是回退上一条语句的状态，而整个事务实际上没有完成(提交或者回滚)，而是交由应用程序在检测到这个错误时，选择是提交或者回滚整个事务。DB2数据库对事务的处理方式是当DB2数据库在因锁超时或者其他问题导致异常后，对整个事务进行回滚，而不是回滚到上一个保存点，这是需要特别关注的地方。
8.数据备份的管理方式

  MySQL的备份方式很多，但是开源社区备份工具在支持在线备份功能上和易用性上非常差。DB2数据库在这方面做得非常完善、严谨且易用，这也是MySQL需要向DB2数据库学习的地方。
9.数据恢复的管理方式

  MySQL数据库恢复的方式十分特别，可以在恢复或者前滚的过程中打开数据，检查数据，若需要继续前滚可以做到继续执行前滚动作，这是MySQL数据库的一个优点也是其在其他商用数据库厂商看来做的很屌丝的地方。DB2数据库在数据库的恢复管理方式上是非常严谨的，在前滚或者恢复的过程中为了保证数据的完整性与一致性不被破坏，不能够打开数据库。在笔者看来这是一个优点也是一个缺点，优点是入门简单，缺点是灵活性差，无法知道我们要恢复到时刻点，是否有我们需要的数据。
10.SQL功能比较

   MySQL对复杂的SQL支持性较差，很多jion方式支持上做的不够好，DB2在复杂SQL支持方面十分强大，而且提供了多种jion方式来保证和提升数据的存取效率。这一点是MySQL需要向DB2学习的地方。
11.DDL操作比较

   MySQL支持在任意位置加列，以适应业务需求；同时也支持在线的DDL以保证业务的连续性。以上这些功能DB2都不支持。
12.语法差异

   MySQL默认使用大小写敏感的数据库名、表名和列名（可以通过lower_case_table_names参数控制是否大小写敏感），DB2数据库对大小写不敏感。

虽然MySQL与DB2都遵循并符合SQL92标准且大多数SQL相互兼容，但是在一些细节的实现上有一些不同的地方。比如：MySQL取符合条件的前几行数据上使用limit语法，DB2则使用fetch语法等。
13.高可用与容灾设计

   MySQL在高可用及容灾方面有非常多的方案，MySQL原生支持多种架构、复制方案来完成据库的高可用及容灾需求如MHA、PXC及MMM等，而且非常灵活、可根据业务需求进行个性化定制、二次开发，几乎零成本。DB2在这方面只有HADR方案且成本高昂。
14.可扩展性

    MySQL在集群方面也有非常多的解决方案，如Cobar、Atlas、Fabric、Amoeba、TDDL、Mycat而且可以进行定制或者进行二次开发，可根据业务需求进行灵活扩展。DB2在这方面目前只有PureScale一种解决方案，灵活性较差，这也是DB2需要想MySQL学习的地方。

二、MySQL与DB2在使用与功能上的差异总结如下表格所示：

编号	功能	MySQL	DB2
1	账号管理	数据库用户名+IP地址	操作系统用户
2	权限管理	可以批量grant与revoke	只能单独授权包括最小单元
3	日志管理	归档日志与事务日志没有关系	归档日志由事务日志产生
4	锁的管理	MVCC实现锁的并发控制	内存模型实现锁的并发控制
5	schema的管理	每个schema为一个独立的DB	同一个DB内可以有多个schema
6	表空间管理	无法条带化、可管理、维护性较弱	管理方便、功能强大
7	数据备份	开源在线的备份工具少，传统的备份只支持温备份	备份方式严谨、功能强大
8	事务行为	回滚到上一个保存点，不会回滚整个事务	整个事务进行回滚
9	数据恢复	支持在恢复时打开数据库，灵活	不支持在恢复或前滚时打开数据库
10	SQL支持	对复杂SQL支持较弱，在运行时可能会存在性能问题	支持各种复杂SQL，多种jion方式
11	Package支持	不支持	支持
12	语法差异	遵守SQL92标准，但细节上有差异、默认大小写敏感	遵守SQL92标准，大小写不敏感
13	容灾与高可用	可选择方案较多、灵活、可二次开发	只有HADR
14	可扩展性	可选择方案较多、灵活、可定制、可开发	只有PureScale
15	DDL功能	支持指定位置加列、在线DDL	不支持

 三、使用MySQL过程中需要注意的地方
1.Binlog日志

   MySQL binlog日志目前有三种格式，分别为statement、mixed,以及row格式。这三种格式各自有自己的使用场景。staement格式只记录执行的语句，记录的日志较少，对性能影响较小，但是其缺点也很明显，比如一些函数语句，在我们设置主从复制或者完整性恢复时会导致数据不一致的问题。row模式会记录那一条记录被修改成了什么，所以其对数据库的完整性恢复及主从设置的支持是最好的，但是其带来的问题就是会对数据库的性能产生较大的负面影响。mixed模式采用了折中的方法，在需要记录结果的时候使用row模式，在不发生歧义的情况下使用statement模式，集statement和row模式的优点于一身。因此不建议在需要保证数据库的可恢复性或者集群数据库下使用statement模式。

联盟目前大多数系统对数据的要求都是强一致性及可恢复性，因此强烈建议将binlog开启并设置为row模式，这对MySQL数据库发生灾难性故障时的可恢复性至关重要。
2.表空间使用

  使用innodb引擎的时候如果不指定所有的表会用公共表空间ibdata。使用时间长了以后会发现数据量不是很大但是公共表空间很大。因为当drop表或者删除表中的数据后，表空间不会自动收缩，这样就会持续增长。唯一解决办法就是将整库以sql的形式备份出来，重建数据库，导入数据。因此需要将参数innodb_file_per_table设置为on，这样每个表创建时都会新建各自的表空间，当表空间使用过高时，只用重建此表就可以。

当打开binlog日志后，当新建函数或者存储过程时，若不将此参数设置为on，数据库在写binlog时也会报错。
3.SQL使用

  SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量，默认为空。SQL_MODE的设置其实是比较冒险的一种设置，因为在这种设置下可以允许一些非法操作，比如可以将NULL插入NOT NULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式，这样有些问题可以在数据库的设计和开发阶段就能发现，而如果在生产环境下运行数据库后发现这类问题，那么修改的代价将变得十分巨大。此外，正确地设置SQL_MODE还可以做一些约束检查的工作。因此建议将MySQL数据库SQL_MODE设置为严格模式。当在插入数据不符合约束时终止数据的插入并抛出异常。

如果MySQL与其它异构数据库之间有数据移植的需求的话，那么可以使用SQL_MODE设置顺利完成数据的迁移。

下表是通过设置SQL_MODE来完成与异构数据库的兼容性：

数据库	SQL_MODE值
DB2	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
MAXDB	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER
MSSQL	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS
ORACLE	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
POSTGRESQL	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS

4.数据类型
  MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型以及近似数值数据类型。但是在我们使用的过程中有些数据类型存在一些缺陷，建议在设计表时尽量提前避免采用可能会对系统带来问题的数据类型。

类型	范围	需要关注的点
YEAR	1901--2155	该数据类型有效截止到2155年
TIMESTAMP	1970-01-01--2037年某时	该数据类型有效截止到2037年
DECIMAL	DECIMAL(M,D)	如果小数超过2位，需要对数据进行转换，否则会以科学计数法显示
FLOAT	0(1.175 494 351 E-38，3.402 823 466 E+38)	浮点数存在误差，数据失真

5.事物的原子性
  MySQL官方手册对innodb_rollback_on_timeout变量在控制事务的行为有非常明确的说明，这也是开发人员在使用MySQL数据库的过程中需要特别关注的地方。

该变量默认值为OFF，如果事务因为加锁超时，会回滚上一条语句执行的操作。如果设置ON，则整个事务都会回滚。将innodb_rollback_on_timeout为OFF，事务会回滚到上一个保存点，InnoDB在执行每条SQL语句之前，都会创建一个保存点。后续操作由应用来判断是完整回滚还是继续提交没有操作的步骤，这些行为对于MySQL数据库来讲可能是正常的，但对于习惯使用商业数据库的开发人员来讲可能是无法接受的。所以强烈建议将该参数设置为ON。
四、最后的总结

  对于后续我们对数据库的选型上，建议从业务需求出发，对轻量级、高并发的互联网类、非关键应用可以考虑采用MySQL数据库。关键性中后台应用建议使用DB2数据库。当然在选型时还要考虑数据库的团队技术支撑能力。以上内容是我基于对MySQL数据库和DB2数据库的理解进行的一些梳理，希望能够对从使用商用数据库转向开源数据库MySQL开发、维护的同事，提供一些帮助，在使用MySQL的道路上少走一点弯路。