【SQL管理】-Flyway数据库版本管理利器从入门到入味_flyway checksum

Flyway是什么?

---

Flyway是独立于数据库的应用、管理并跟踪数据库变更的数据库版本管理工具.用通俗的讲,Flyway可以像Git管理不同人的代码那样,管理不同人的sql脚本,整个过程自动化,可回溯,安全可靠,易用高效,且对代码零侵入,非常友好,而且Flyway支持的数据库很多，主流的数据库都能够完美支持 .

当你决定接入Flyway时,不妨花一点点时间阅读本篇,本篇文档经过本人近一年的生产环境实践总结,覆盖了我们在Flyway使用中可能出现的各种问题及解决方案,也提供了相对完善的最佳实践,通过本篇文档,可以使接入者尽量少踩坑,节省大量时间和精力去专注业务本身.

 Flyaway可以解决哪些问题?

---

在项目或产品研发过程中，很难一开始就把业务理清楚，把代码逻辑和数据库表设计好，因此代码和数据表也会在迭代周期内不断迭代。我们都习惯 使用SVN或者Git来对代码进行版本管理，主要是为了解决多人开发代码冲突和版本回退的问题。其实，数据库的变更也需要版本控制，在日常开发和环境部署中，我们经常会遇到下面的问题：

• 在开发环境部署程序发现报错，定位是自己写的SQL忘了在当前环境执行导致；

从Git上新down下来的代码运行报错，定位发现是其他同事修改了的SQL没有在当前环境执行导致；

每次发布包都需要发布SQL文件包和应用程序的版本包；

线上环境部署报错，发现是运维没有按照你投产文档里面说明的SQL执行顺序操作执行导致；

流水线可以自动化部署程序的，但是SQL文件还需要手动处理或者流水线执行SQL比较繁琐；其他场景....

有了Flyway,这些问题都可以轻松的解决。Flyway可以对数据库进行版本管理，可以自动执行SQL，能快速有效地用于迭代数据库表结构，并保证部署到测试环境或生产环境时，数据表都是保持一致的.

Flyway主要工作流程

---

1. 项目启动，应用程序完成数据库连接池的建立后,Flyway自动运行.
2. 初次使用时,Flyway会创建一个flyway_schema_history表，用于记录sql执行记录.
3. Flyway会扫描项目指定路径下(默认是classpath:db/migration)的所有sql脚本,与flyway_schema_history表脚本记录进行比对。如果数据库记录执行过的脚本记录,与项目中的sql脚本不一致,Flyway会报错并停止项目执行.
4. 如果校验通过,则根据表中的sql记录最大版本号,忽略所有版本号不大于该版本的脚本.再按照版本号从小到大,逐个执行其余脚本.

Springboot项目接入Flyway

---

这里以maven管理的Springboot项目集成为例,其它类型项目,可参考网上教程.

步骤一:引入核心依赖

<!--引入flyway-->
  <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
        <version>${version}</version>
  </dependency>        

 步骤二:建立sql脚本存放脚本

新建文件夹,用于存放sql文件,官方推荐是放在classpath下的db.migration目录下.

步骤三:添加配置信息

 添加springboot配置,在application.yml/application.properties配置文件中加入:

spring:
  # flyway配置
  flyway:
    # 是否启用flyway
    enabled: true
    # 编码格式，默认UTF-8
encoding: UTF-8  
    # 迁移sql脚本文件存放路径，官方默认db/migration 
    locations: classpath:db/migration
    # 迁移sql脚本文件名称的前缀，默认V
    sql-migration-prefix: V 
    # 迁移sql脚本文件名称的分隔符，默认2个下划线__ 
    sql-migration-separator: __ 
    # 迁移sql脚本文件名称的后缀
    sql-migration-suffixes: .sql  
# 迁移时是否进行校验，默认true  
    validate-on-migrate: true
    # 当迁移发现数据库非空且存在没有元数据的表时，自动执行基准迁移，新建schema_version表
    baseline-on-migrate: true
    # 是否关闭要清除已有库下的表功能,生产环境必须为true,否则会删库
    clean-disabled: true 
 
#(默认项目中已存在已存在数据源配置,jdbc,mysql driver等,且数据源连接正常,如果没有,请先配置数据源)

步骤四:按命名规范置入SQL脚本,启动项目

将变更的SQL脚本按照V版本号_序号__xxx.sql的形式(推荐,关于命名后面详讲),置入db/migration目录下,启动项目;项目启动后系统会自动创建flyway_schema_history表,执行SQL变更脚本,并插入脚本执行记录至该表,如图:

 

SQL脚本命名(重点)

 

Flyway对数据库的所有更改都称为迁移；版本迁移(Versioned Migrations)以V开头，只会执行一次；回退迁移(Undo Migrations)以U开头，执行一旦发生破坏性更改，就会很麻烦，项目中一般不用；可重复执行迁移(Repeatable Migrations)以R开头，每次修改后都会重新执行。

总结如下：

1. 仅需要被执行一次的SQL命名以大写的"V"开头,后面跟上"0~9"数字的组合,数字之间可以用“.”或者下划线"_"分割开，然后再以两个下划线分割，其后跟文件名称,最后以.sql结尾.比如，V2020.00.000_1__create_table.sql，V202001.00.000_2__insertTable.sql，V2.1.5__create_table.sql。
2. 可重复运行的SQL，则以大写的“R”开头,后面再以两个下划线分割,其后跟文件名称,最后以.sql结尾.比如,R__addTable.sql,R__update_user.sql.
3. 版本号需要唯一,否则Flyway执行会报错;如果V__脚本.sql,已经执行过了,不能修改里面的内容,再次执行Flyway就会报错。
4. R——脚本.sql,允许脚本内容的修改,如有变化可以执行多次。
5. V开头的SQL执行优先级要比R开头的SQL优先级高。

生产级规约

---

随着Flyway的接入,我们有必要指定一套统一的规范,让团队内开发者遵循,否则任由开发者自由命名的话,后续脚本管理将会变得混乱,无规矩不成方圆,这里我拟定了一套类似《阿里巴巴代码规范》的规约,并在生产环境得到了长期验证,也受到了大家的一致认可,现在分享给大家,可供参考.

Tips:其中标红内容为十分重要,必须强制遵循.

1. 【强制】flyway的执行的SQL脚本仅适合数据库表结构变更,以及创建/删除表,少量的数据变更也可以使用,但数据量大的情况下建议不要通过flyway进行数据变更,应由运维通过其它工具如navicat等进行变更,用flyway执行size大的脚本会有性能问题和历史包袱,在文末更多章节中有详细介绍和实验.

2.【强制】sql脚本文件,统一放在当前项目classpath下的db.migration目录下,方便管理.

3.【强制】encoding: UTF-8 配置文件中的编码格式强制要求使用UTF-8,避免未来碰到编码问题.

4.【强制】validate-on-migrate: true, 迁移时强制进行校验,以保证数据安全.

5.【强制】clean-disabled: true 强制关闭清除已有库下的表功能,生产环境必须为true,否则已有数据会被清空.

6.【强制】数据库脚本的命名V+当前项目版本号,如果当前版本下涉及多个数据库变更脚本,则可以用下划线+数字来区分,然后用双下滑线与脚本名称隔开,脚本名称采用小驼峰命名,示例:

为了让大家更容易理解,我们先来看下Flyway强制要求的脚本命名规范

如果脚本不按照该命名规范,就不能被Flyway认可,脚本无法执行.

结合目前的项目版本命名及实际使用的角度,拟定出了下面这种命名方式:

以我在做的项目为例,项目英文名为mengnan,在当前版本号(202101.02.000)下有两个sql脚本: mengnan_user.sql(新增的表),mengnan_user_data_init.sql(为新增表初始化数据),按照当前约定的强制命名规范,应该命名为:

V202101.02.000_1__createUser.sql和V202101.02.000_2__initUser.sql

若当前项目只有一个sql变更脚本,则可直接缩写为V202101.02.000__createUser.sql,可省略中间的数字.

如果存在多个sql脚本时,不加中间的数字加以区分,会被Flyway认为存在同一个版本号的多个sql脚本,会导致项目无法正常启动.

8.【强制】sql-migration-suffixes: .sql 迁移sql脚本文件名称的后缀统一使用.sql结尾,避免出现各种格式的sql脚本.

9.【推荐】sql-migration-prefix: V 为了保证生产环境数据安全,配置文件中迁移sql脚本文件名称的前缀,推荐使用V打头,避免使用R,U开头,可确保脚本只被成功执行一次,也可避免脚本被篡改,可以减少很多不必要的麻烦,当然如确有需要,可采用R,U开头.

10.【推荐】sql-migration-separator: __ 迁移sql脚本文件名称的分隔符，默认2个下划线__ 值得注意的是:配置文件中的分隔符需要与脚本命名中用的分隔符保持一致

11. 【参考】baseline-on-migrate: true 当迁移发现数据库非空且存在没有元数据的表时，自动执行基准迁移，新建schema_version表.

FAQ

---

1.删除之前已经执行过的某个SQL脚本会怎样?

        a.只删除SQL脚本

如果只删除SQL脚本,项目无法启动,启动时会报错,Flyway会感知到你把这条SQL脚本给删了,所以不允许项目启动

        b.只删除历史记录

删除了历史记录,但SQL脚本还在,于是在下次重启项目的时候,该记录对应的SQL脚本会被重新执行.

        c.SQL脚本和SQL执行历史记录表里对应的数据一起删除

如果SQL脚本和SQL执行历史记录一起删除,则项目可以正常启动,无影响,但这样做就相当于放弃了Flyway的版本管理功能,而仅仅把它当成了一个不用手动执行SQL脚本的工具人.

综上所述,不建议随意删除SQL脚本和已经执行的SQL历史记录,都是危险操作,虽然脚本都由Git管理,都能找回来,但这样的删除没有任何意义.

删除的本意是想提高项目启动的速度,避免经过多年的迭代,SQL脚本堆积如山,影响项目启动速度,但实际上,已经执行过的SQL脚本对项目启动的速度影响微乎其微,我尝试放了11条已经执行过的SQL脚本,启动项目时的速度仅比只有1条已执行过的SQL脚本速度慢1秒左右,Flyway在启动时只是对当前所有SQL脚本依次做个MD5值的计算和对比,并不会耗很多时间,所以不必担心. 按照我们正常每个迭代0-5个SQL脚本,迭代周期15天计算,一年下来也就0-120个SQL脚本,对Flyway执行效率没多少影响.

 

2. 版本回退

尽管Flyway支持回滚（仅作为商业功能），但不鼓励使用它,如果一定要回退,可采用备份的方式进行回退,原因如下(引自Flyway官网):

尽管撤消迁移的想法很好，但不幸的是，有时它在实践中会崩溃。一旦您进行了破坏性的更改（删除，删除，截断……），便开始遇到麻烦。即使不这样做，您最终也将创建用于还原备份的自制替代方法，这些替代方法也需要进行适当的测试。

撤消迁移假定整个迁移成功，现在应该撤消。对于没有DDL事务的数据库，如果版本迁移失败，这将无济于事。为什么？迁移随时可能失败。如果您有10条语句，则第1，第5，第7或第10条可能会失败。根本没有事先知道的方法。相反，撤消迁移被编写为撤消整个版本的迁移，在这种情况下将无济于事。

我们发现更可取的另一种方法是保持数据库与当前在生产环境中部署的所有版本的代码之间的向后兼容性。这样，失败的迁移就不会成为灾难。该应用程序的旧版本仍与数据库兼容，因此您可以简单地回滚应用程序代码，进行调查并采取纠正措施。

这应该辅之以适当的，经过良好测试的备份和还原策略。它独立于数据库结构，并且一旦经过测试并证明其可以工作，任何迁移脚本都无法破坏它。为了获得最佳性能，并且在基础架构支持的情况下，我们建议使用基础存储解决方案的快照技术。特别是对于较大的数据量，这可能比传统的备份和还原快几个数量级。

3.Flyway任意环境如何迁移

针对新项目,可以直接接入,从第一版开始一直到第N版迭代的SQL保留,可以在任意环境直接迁移.但目前大部分都是一些已经在运行的老项目,此时集成Flyway,需要考虑未来不同环境迁移的问题,项目通过Flyway在不同环境迁移,主要针对以下两种情形分别给出不同的解决方案:

a.新环境只迁移表结构,不迁移数据

针对这种情况,操作比较简易,让运维人员导出一份全量的数据库表结构,统一命名为V日期.00.000__createAll.sql,然后将该sql脚本与本次迭代的sql统一放在db.migration目录下,在该环境下只要项目启动,相关表就会被自动创建完成,迁移完成.

b.新环境表结构和数据都迁移

最初的方案是和上面一样,需要在接入Flyway后,准备发布前,停止生产环境服务,让运维人员提供全量的线数据库表结构及数据的SQL脚本,统一命名为V日期.00.000__initAll.sql,然后将该sql脚本与本次迭代的sql统一放在db.migration目录下,提交发布流程,审批通过后由运维人员部署该项目即可,后续迁移任意环境,只需要启动项目就可自动完成数据库迁移.但实际操作时发现几个比较棘手的问题,所以放弃了该方案,网上也没找到相关的解决方案,所以如需迁移数据+表结构,仍然推荐以前的老方法,运维同学通过SQL工具进行迁移.迁移完成后,新迭代可继续通过Flyway管理sql,无需运维人员介入,毕竟新环境迁移本来就是非常低频的,数据和结构同时迁移更低频.

针对最初方案存在的问题梳理如下:

1. 对于一些数据量比较大的老项目,将包含数据的全量SQL脚本打包进项目,会使项目包非常大,对流水化构建拉取代码以及打包耗时都比较大
2. SQL脚本中存在HTML文档,文档中若存在$占位符的标签,就会导致该SQL脚本无法通过Flyway执行,Flyway会报错,Flway会误认为该占位符对应的字段没有默认值而报错.
3. 对于包含数据超大的SQL脚本,在项目启动时非常容易导致堆空间溢出,即便我指定了很大的堆大小,依旧很容易出现堆空间溢出,300多MB的SQL脚本,5倍的堆空间都容易溢出,考虑到一些老项目的数据可能不止300多MB,服务器内存资源难以支撑这样的场景,所以放弃了此方案.

4.flyway脚本的版本号与执行顺序

flyway支持多种格式的脚本版本号,比如:

1. 001
2. 5.2
3. 1.2.3.4.5.6.7.8.9
4. 205.68
5. 20130115113556
6. 2013.1.15.11.35.56
7. 2013.01.15.11.35.56
8. 2013_1_2
9. …

在各种不同命名规则下的版本号,到底哪一个脚本先被执行,以及版本号是否会影响到脚本执行顺序? 在flyway官网找到了答案,脚本的版本号会影响执行顺序,且顺序与期望的数字排序一致(从小到大),至于采取何种算法,官网并无说明.

  官网没有相关排序计算逻辑,于是我下载了源码,在源码中找到了排序的逻辑,Flyway源码中将版本号读取出来后,通过正则表达式匹配出版本号里的符号,将符号去除后作为数字,然后优先去比较数字大小,从小到大排序.如果有版本号为null,则该脚本排序靠后,这也就正好解释了R开头的脚本为什么执行优先级低于V开头的脚本了,因为R开头的脚本是没有版本号的.如果都没有版本号,则根据描述的大小进行排序,排序规则按照jdk提供的字符串比较器进行排序.

 

5.超大量SQL脚本对项目启动速度的影响

为了验证接入Flyway后项目持续迭代了一两年,积累了上百个sql是否会严重拖慢项目启动的速度?具体拖慢多少秒?这里我进行了一个对照组实验:

Case1:只放1个已经被执行过的SQL脚本,反复启动项目 观察启动时间,计算出均值

Case2:只放25个已经被执行过的SQL脚本,反复启动项目 观察启动时间,计算出均值

Case3:只放50个已经被执行过的SQL脚本,反复启动项目 观察启动时间,计算出均值

Case4:放100个已经被执行过的SQL脚本,反复启动项目 观察启动时间, 计算出均值

 

 

 

 

由于日积月累的脚本已经在flyway历史记录表中有记录,所以即便是有几百条SQL脚本,每次项目启动需要执行的脚本也就只有当次迭代的那几个脚本,所以考耗时主要来源于两个方面:

1. 一是flyway依次读取脚本中内容时的IO开销
2. 二是flyway计算checksum值的开销

对于IO开销而言,每个脚本如果不是涉及大量的数据变更,只是表结构的变更,脚本的大小都非常小,可以不考虑.事实上Flyway也不适合大量的数据变更时使用,原因可参考上面[4.Flyway任意环境如何迁移],因此IO开销对启动耗时的增量基本可以忽略.

再来看checksum值的计算,我下载了flyway的源码,并找到了计算的算法.

Flyway计算checksum值采用的是著名的CRC32(循环冗余校验码)算法:

是数据通信领域中最常用的一种查错校验码，其特征是信息字段和校验字段的长度可以任意选定。循环冗余检查（CRC）是一种数据传输检错功能，对数据进行多项式计算，并将得到的结果附在帧的后面，接收设备也执行类似的算法，以保证数据传输的正确性和完整性。

算法是对原数据的每个比特位进行模-2除法的余数,所以一旦有原数据有任何一点改变,计算出来的checksum值就会发生改变,从而可以判定为SQL脚本被篡改.

为了验证Flyway实现的CRC32算法计算效率,我写了个测试类模拟了下:

 Case1: 读取一个超大文件,约1M,并进行计算

 Case2: 读取一个正常大小的SQL脚本,并进行计算

      

由此可见,即便是有上百个正常大小的sql,计算checksum值也不会耗费太多的时间,基本都可以在1秒内完成,所以接入Flyway后也不必担心会有历史包袱.

6. SQL脚本执行报错及回滚

当执行的SQL脚本在生产环境发生错误时,运维同学如何快速定位是Flyway执行的SQL脚本问题导致部署失败?以及如何回滚?

下面这张截图是我在本地跑时报错的截图,项目未成功启动:

 

在接入Flyway部署后,运维同学虽然不需要手动执行SQL脚本,但需要确认本迭代的SQL脚本是否执行失败:

SELECT version,installed_on,

CASE success

WHEN 1 THEN '成功'

WHEN 0 THEN '失败'

END

AS '执行状态'

FROM flyway_schema_history

ORDER BY installed_on DESC

LIMIT 50;

查看是否有执行失败的SQL脚本.

 

如果有,则可以打开浏览器访问项目,如果服务异常,则

如果有,则可以打开浏览器访问项目,如果服务异常,则可进一步验证服务启动失败(此步骤可以省略)打开应用启动日志也可以看到,服务启动失败的日志,里面有Flyway相关异常

 通过这两种方式,来确认Flyway执行脚本是否成功,事实上像这种脚本执行失败是非常小概率的事件,开发同学在上生产前,相关表结构变更的SQL已经至少在测试环境和预发环境执行过了,所以极大概率在生产上也能执行成功,因此对运维而言,每次部署后只需复制上面的SQL看下Flyway的执行是否有失败即可.

如果出现执行失败,并确认是Flyway导致的,运维同学可以回退到上一版本的项目包,然后把Springboot中Flyway的开关关闭,重新启动项目即可完成回退.

其它

---

值得注意的是,在初次接入Flyway时,一定要对生产环境的数据做好备份,以便应从容应对各种突发情况,如果想要了解更多,可以参考Flyway官方文档.

参考文档:Flyway官网 https://flywaydb.org/