前言
目前,由于对数据库产品的高安全性、高可用性要求,银行业在现有核心业务系统中普遍采用国际大厂商的成熟产品,如IBM的DB2、Oracle的Oracle、微软的SQL Server等。随着业务的不断发展,银行业对数据库产品的需求逐渐多元化:一方面要能够满足业务系统的基本需求,另一方面对数据安全和自主可控的要求越来越高。越来越高。鉴于此,不少银行业开始了自身的转型尝试,并取得了一定的成效。
MySQL作为目前最流行的开源数据库,已经被互联网公司广泛使用。基于数据库安全可控的考虑,银行业也在大规模推广替代传统数据库产品。
我行在更换、使用、改造过程中遇到了很多问题。下面总结了开发、运维最常问的问题。我们给出了最简洁的解答,希望对大家有所帮助。
01
为什么MySQL数据库首选作为替代Oracle的数据库产品?
MySQL近年来的蓬勃发展以及在互联网行业的丰富实践,使其替代商业数据库成为可能。特别是阿里巴巴等行业巨头已成功使用MySQL替代Oracle并支持庞大的业务。 MySQL作为全球最流行的数据库,还具有以下优势:
1)丰富的文档、大量的从业者和蓬勃发展的生态系统使MySQL成为首选。
2)支持行锁和事务的Innodb存储引擎在官方的大力支持下越来越强大,对于高并发下的OLTP优势明显。
3)灵活的逻辑复制构建主从,在架构设计上留有更多的空间。组复制(MGR)技术可以保证数据强一致性,突破了最后的技术障碍,满足了金融等领域对数据强一致性的要求。
4)当单机成为性能瓶颈时,丰富的开源中间件结合MySQL进行数据拆分,实现分布式数据库改造方案,可以提供更高的业务需求。
02
MySQL相对于Oracle有哪些语法、数据类型、对象类型兼容性问题?
MySQL 支持Oracle 的大部分基本SQL 语法、数据类型和对象类型。一些不受支持的如下:
1)在数据类型方面,MySQL不支持序列、自定义类型、XML数据类型和伪列。
2) MySQL不支持包括物化视图、包管理和同义词在内的对象。
3)索引方面,MySQL不支持位图索引、位图连接索引、函数索引和在线索引重建。
4)触发器方面,MySQL不支持DDL事件触发器、系统事件触发器、时间触发器。
5)高级功能方面,MySQL不支持外部数据库链接、面向对象、闪回查询等。
6)函数方面,MySQL不支持COSH(x)、CHR(n1)、LAG()、RANK()等函数。
03
使用中间件+MySQL开发和直接MySQL开发有什么局限性?
应用连接池配置部分与直接连接MySQL相同。对于应用来说,中间件的引入屏蔽了后端的拆分细节。可以理解为中间件就是数据库。使用中间件方式的具体限制如下:
1)有一些语法限制,包括DDL、DML和管理语句。例如create table.like.INSERT.SELECT.等语法都不支持。
2)性能方面,查询及相关查询需要基于相同分片规则的分片键进行。
3) 不支持外键关联、临时表、触发器、分布式存储过程和自定义函数。
04
如何将数据从Oracle迁移到MySQL?
从Oracle迁移到MySQL是异构迁移,需要依赖第三方开源工具或商业工具进行迁移。数据量和业务停机时间决定迁移方式。
1)当数据量较小且停机时间可以完全完成时,可以使用直接文本导出和导入操作。该方法简单、高效。
2)当停机时间要求特别短时,我们会使用OGG(Oracle Golden Gate)或者类似的工具来维护Oracle到MySQL的全量+增量实时同步。当业务停止且交换机准备就绪时,停止Oracle到MySQL的同步。验证数据无误后,业务代码连接MySQL数据库,完成数据迁移过程。
05
为什么MySQL不建议创建存储过程、触发器、自定义函数等对象?
对于数据库的使用,我们强烈建议您只参与数据访问,不参与业务逻辑。具体原因如下:
1)业务逻辑的实现完全放在代码中,方便集中维护和调试。
2)触发器的嵌套,如果涉及到多个存储过程、事务控制等,很容易出现死锁。
3)基于中间件实现的分布式数据库对存储过程、触发器、自定义函数的支持有限。
4)保护DB,减轻数据库压力。
5)异构数据库可移植性差,增加开发成本。
06
什么情况下应该使用分片?数据库拆分有哪些方法?如何选择拆分方式?
对于MySQL来说,当数据量太大、QPS或者TPS太高,或者单机硬件资源(CPU、磁盘、内存、IO等)存在性能瓶颈时,是没有办法的。如果单方面增加硬件资源来满足要求,就需要考虑这样做。分库、分表。一般单表大小超过2000万,数据库大小超过100G就需要考虑,具体取决于实际应用场景。
数据库拆分方式分为水平拆分和垂直拆分。
垂直拆分是指按照功能模块和关系紧密程度拆分成不同的表或库。垂直拆分比较简单,不同业务访问自己的库和表即可实现。
水平拆分是指将表数据按照一定的规则进行划分,存储到结构相同的不同表中。水平分割相对复杂。它需要对表中的数据进行物理拆分。拆分时,必须根据数据的增长情况预测拆分的粒度,尽可能保证数据和负载的平均。
在选择拆分方法时,需要评估瓶颈的原因。如果由于数据库表太多导致数据量太大,并且数据库中的业务逻辑清晰,那么选择垂直拆分。如果单表数据量比较大,应该选择水平拆分。
07
有哪些MySQL管理工具可用?
常用的MySQL管理工具有:Navicat for MySQL、SQLyog、PhpMyAdmin、MySQLWorkBench等。
推荐开源工具是MySQLWorkBench,推荐付费工具:SQLyog。
08
修改大型MySQL 表有哪些风险和性能?
修改MySQL大表会导致死锁,所以一般采用以下两种方法进行修改:
1)非高峰期停止服务后直接修改ALTER。这种方式安全性较高,但每次修改都需要停止业务,这对于一些核心业务系统来说是无法接受的。而且,对于比较大的表,停机时间会更长,成本也会更高。
2)使用第三方工具pt-online-schema-change。这个工具可以直接修改。它的运行原理是:首先锁定表(此时表是只读的),然后复制原表的物理结构创建中间表,然后修改中间表的物理结构,然后复制原始表数据导入中间表。数据同步完成后,锁定中间表并删除原表。接下来,将中间表重命名为原始表。最后刷新数据字典并释放锁。本工具修改过程中修改的表必须有主键,不能是联合主键。同时,也存在一定的风险。该工具在进行更改修改时不会提示错误,但结果会是部分数据丢失。在性能方面也存在一定的瓶颈:比如并发比较高的时候,会对业务的访问速度产生一定的影响。
基于分布式数据库中间件产品,目前不支持pt-osc、gh-ost等第三方工具。建议使用MySQL 5.7以上版本,并依赖原生Online DDL来更改表结构。
09
MySQL分区表的使用原则是什么?
MySQL实现分区表的方式是对底层表进行封装,也就是说索引也是根据分区的子表来定义的,没有全局索引。这与Oracle不同,Oracle可以更灵活地定义索引和表是否分区。
使用MySQL分区表时,常规的CRUD操作和返回结果与普通表没有什么区别。 MySQL的分区表主要包括四种类型:RANGE、LIST、HASH、KEY。不支持自行创建分区。
在一些特定场景下可以考虑分区表,比如历史数据有明确的分区范围、不跨分区的访问、很少的变更操作、查询语句逻辑简单、无性能瓶颈等。
对于Oracle等商业数据库来说,由于商业许可,水平扩展的成本较高,而且分区表功能稳定,因此可以通过硬件扩展和分区来承担大数据量带来的负载。对于MySQL开源数据库,企业有资源、有能力将很多需求迁移到数据库之外,通过代码逻辑或者其他替代方式来实现。因此他们在MySQL的使用上追求简单、稳定、可靠,通过增加服务器和分库,Tables能够更好的处理数据量爆发式增长带来的性能问题。因此,不建议大量使用MySQL分区表,尤其是重要业务。
10
如何选择MySQL架构?
您可以参考下表:
系统级别高系统级别中系统级别数据量小集中式+三中心架构集中式+两中心架构集中式+单中心架构数据量大分布式+三中心架构分布式+两中心架构分布式+单中心架构笔记:
数据大小依据:单表2000万以内,单库100G以内。具体可根据实际情况确定。
集中式:直接连接MySQL独立数据库。
分布式:通过中间件+MySQL进行数据拆分。
三中心结构:同城双中心+异地中心。
二中心架构:本地单中心+远程中心。
单中心架构:本地单中心。
11
MySQL如何保证数据的一致性?
独立:
通过设置双1参数,在日志写入磁盘后强制提交事务。
复制:
1)主从:主从是通过增强半同步实现的:主库提交事务,从库需要接收主库的日志并写入中继日志。将ack消息返回给主库后,主库就可以提交了。基于这个原则,可以最大程度保证从库数据不丢失,并且可以保证主从数据的一致性,但在极端情况下,可能会出现丢失。
2)MGR:MySQL组复制由若干个成员组成一个复制组。一笔交易的提交必须得到群内多数成员(N/2+1)的确认后才能解决并提交。与传统的主从复制相比,增加了一致性协议层和冲突认证,这是保证数据一致性和多主复制的关键。组复制解决了主从复制极端情况下数据丢失和不一致的问题,保证了数据的强一致性。
12
如何降低MySQL主从延迟?
主从延迟直接决定了RTO时间,因此低延迟对于数据库切换和恢复时间非常重要。具体实现方法如下:
1)适当增加从库的配置,使其大于或等于主库的配置。
2)使用较高的数据库版本,MySQL5.7启用并行复制。
3)设计表结构时,一定要有主键,而且主键一定要短。
4)使用新硬件:PCI-E SSD类型设备。
5)应用端适当使用缓存,减轻数据库的压力。
6)尽量避免大额交易。建议在业务低峰时段进行批量DML操作,并小批量多次执行操作。
13
Oracle和MySQL如何实现互提取?
1)可以通过程序实现双向提取。通过JDBC 建立与Oracle 和MySQL 数据库的连接。在源数据库上执行查询以返回ResultSet 对象。然后通过ResultSet.next()方法一一获取数据后,在目标数据库中使用。连接一条一条插入数据或者批量缓存N行数据。 Oracle数据库查询的内存消耗是单行或N行数据的大小。 MySQL数据库查询的内存消耗是结果集的大小。因此建议采用分页查询处理。
2)MySQL转Oracle:通过工具或select* from table_name into outfile '文件路径将MySQL数据导出为符合MySQL语法的SQL文件、CSV格式文件、数据文本文件,然后使用Oracle的sqlldr或其他工具导入。
3)Oracle转MySQL:可以使用开源工具sqluldr2,它可以将Oracle中的数据导出为符合MySQL语法的SQL文本,然后倒入MySQL数据库中。
4)此外,还可以使用第三方ETL工具或OGG软件来实现。具体实现原理本文不再详细描述。
14
单独MySQL、中间件+MySQL、Oracle 的性能对比如何?
下面以我行单表数据量9000万的业务场景为例(共三张不同的业务表)。
单表是指某个表,双表是指两个表。
场景耗时(秒) Oracle 配置:物理机32C+64G+SSD 文本导入9000 万数据1517 单表计数3.35 单表点查询0.01 两表关联(有条件点查询使用索引,关联字段,查询条件为Sharding key ) 0.01 两表关联(全表) 38 三表关联(有条件点查询、索引、关联字段,查询条件为分片键) 0.01 三表关联(全表) 721) 中间件+MySQL 的文本导入进行了拆分,其性能明显优于其他单机数据。
2)单表计数MySQL8.0已经进行了优化,性能比Oracle好,但多表关联比5.7稍差。
3)单机MySQL无论是5.7还是8.0,在关系查询方面的性能还是比Oracle差很多。 8.0虽然支持hash join,但是也有一定的限制。例如,相关字段无法建立索引,并且必须具有等效条件。
4)条件关联查询的性能是一样的。需要注意的是,这里关联的字段必须是分片键,查询条件也必须是分片键。只有这样,中间件+MySQL的优势才能体现出来。
5)中间件+MySQL8分片使用虚拟机(8c16G)和单数据库Oracle物理机(32C64G)。性能基本相同,但对语句要求严格,必须结合分片键创建关联过滤条件。
15
如何申请MySQL主机资源以及MySQL主从数量?
资源申请:
单机数据库和中间件配置不小于16C32G,分片数据单节点不小于8C16G,根据实际情况而定。
主从数量:
单中心部署:1主2从。
双中心部署:1主3从。
三中心部署:1主4从。
总结
以上是我行使用过程中最常遇到的15个问题。部分方案、参数选择和设置与我行实际应用相关,可能并不适合每个人各自的场景。但他山之石可以攻玉。希望我们的解决方案能够拓展大家的思维,让我们在数据库转型的道路上共同进步!
上述任何一个问题都可以作为一个主题来研究。您可以关注我们办公室的公众号,里面有相关问题的详细解答。