mysql怎么在海量数据上ddl_一种支持MySQL数据库在线DDL操作的方法与流程

本发明涉及数据库

技术领域：

：，特别涉及一种支持mysql数据库在线ddl操作的方法。

背景技术：

：：在mysql数据库的日常运维工作中，经常需要用到ddl(datadefinitionlanguage)操作，比如对表添加索引，删除列，修改字段类型等。在早期的mysql版本中进行ddl操作，首先需要创建一张和原始表结构一样的临时表，然后将基表数据全部拷贝到临时表，最后再重命名表。但是整个创建过程中基表是不可写的，会阻塞所有的dml操作，并且会消耗一倍的存储空间。mysql5.6版本之后开始支持onlineddl操作，在执行创建或者删除操作的同时，将dml操作日志写入到缓存中，待完成后再将缓存操作应用到表上，以此达到数据的一致性。原生online方式支持了大部分的ddl在线操作，但在涉及到修改字段数据类型、添加全文索引时等操作时，仍然采用了之前的创建临时表并复制数据的办法。这种代价昂贵的操作仅支持并发读，阻塞了所有的写操作，会影响到业务系统的性能和可用性。技术实现要素：本发明的目的是克服上述

背景技术：

：中不足，提供一种支持mysql数据库在线ddl操作的方法，可实现在所有的ddl操作过程中不会造成读写阻塞，由此避免了ddl操作过程中系统的不可写状态。为了达到上述的技术效果，本发明采取以下技术方案：一种支持mysql数据库在线ddl操作的方法，包括以下步骤：a.检查并设置数据库环境，创建基表，并通过python程序不断往基表里做添加、修改和删除的操作；b.创建一个与基表相同表结构的临时表并在临时表上执行ddl操作；如果在基表上直接做ddl操作，一方面基表正在为业务提供服务，直接操作势必会影响到现有的业务；另一方面基表在使用过程中已经累计了一定的数据量，在操作过程中会比较耗时；因此本方案中采取重新建立一个与基表相同表结构的新的临时表，在基于临时表的基础上做ddl操作；同时，新建的临时表不对外提供业务因此没有任何数据，所以在此基础上执行ddl操作不会对现有的业务有任何影响，并且速度非常快，在新建的临时表上完成计划的ddl操作之后，即可计划下一步的在基表和临时表之间的数据迁移；c.在所述基表上添加基于dml操作的触发器，所述触发器的内容逻辑只保证在基表上进行的任何dml操作且在事务提交成功时，也必须同时保证在临时表上做同样的操作；则通过触发器可实现在基表所有新进来的dml操作都同步到临时表中；d.将基表内的数据拷贝迁移到临时表中；上一步的触发器保证了新来的dml操作会同步数据到临时表中，这一步即是把基表中触发器之前的基础数据全部拷贝到临时表中；在拷贝的过程中在基表上进行的dml操作同样会同步到临时表的数据中，由这两步操作来保证基表和临时表之间的数据一致性；e.将基表重命名，再将临时表重命名为原基表名称并将基表及基表上的触发器删除；先把基表重命名，然后把临时表改成基表名；临时表是已经完成了ddl操作的新的表结构，并且数据和基表保持一致；重命名完成之后，临时表就作为新表用新的表结构对外提供服务了；在最后删除基表和基表上的触发器，清理线上环境。进一步地，所述步骤a中检查并设置数据库环境时，包括设置数据库事务等待行锁的超时时间参数、设置获取元数据锁超时参数、设置交互超时时间参数及进行使用用户权限验证和数据库连通性验证。进一步地，所述步骤c中具体是添加三个触发器，分别是基于delete、update、insert的aftertrigger。进一步地，所述步骤d中进行数据拷贝迁移时是用insertasselect的方式把基表中的初始数据插入到临时表中。进一步地，所述步骤d中还包括在插入数据的过程中给临时表加上共享锁，并通过提示forceindex强制基表在查询数据的过程中使用主键，同时基表也必须要满足有主键约束。本发明与现有技术相比，具有以下的有益效果：本发明的支持mysql数据库在线ddl操作的方法，可实现在对表结构进行ddl操作时，避免了对操作表的读写阻塞，由此对数据库运维操作提供了便利性，也提高了业务系统的可用性。附图说明图1是本发明的支持mysql数据库在线ddl操作的方法的流程示意图。图2是mysql原生的onlineddl结构简图。具体实施方式下面结合本发明的实施例对本发明作进一步的阐述和说明。实施例：实施例一：如图1所示，一种支持mysql数据库在线ddl操作的方法，包括以下步骤：第一步.检查并设置数据库环境，创建基表origin_table，并通过python程序不断往基表里做添加、修改和删除的操作。具体的，检查并设置数据库环境时，包括设置数据库事务等待行锁的超时时间参数、设置获取元数据锁超时参数、设置交互超时时间参数及进行使用用户权限验证和数据库连通性验证。本实施例中，具体将数据库事务等待行锁的超时时间参数innodb_lock_wait_timeout设置为1秒，获取元数据锁超时参数lock_wait_timeout设置为60秒，交互超时时间参数wait_timeout设置为1000秒。第二步.创建一个与基表相同表结构的临时表并在临时表上执行ddl操作。如果在基表上直接做ddl操作，一方面基表正在为业务提供服务，直接操作势必会影响到现有的业务；另一方面基表在使用过程中已经累计了一定的数据量，在操作过程中会比较耗时；因此本方案中采取重新建立一个与基表相同表结构的新的临时表，在基于临时表的基础上做ddl操作；同时，新建的临时表不对外提供业务因此没有任何数据，所以在此基础上执行ddl操作不会对现有的业务有任何影响，并且速度非常快，在新建的临时表上完成计划的ddl操作之后，即可计划下一步的在基表和临时表之间的数据迁移。具体的，本实施例中，首先创建一张临时表作为新表并定义为new_table，按照具体的需求给新表增加一个字段v_name。由此得到一个已经完成ddl操作的新表结构new_table，新表里没有数据，因此ddl操作过程很快，且对当前的python程序没有任何影响。而对比原生的onlineddl操作，具体结构如图2所示，在进行如更改列数据类型等操作时，仍然会进行copyoffline的现象，即在拷贝数据过程中，基表会出现不可写的状态。第三步.在所述基表上添加基于dml操作的触发器，所述触发器的内容逻辑只保证在基表上进行的任何dml操作且在事务提交成功时，也必须同时保证在临时表上做同样的操作；则通过触发器可实现在基表所有新进来的dml操作都同步到临时表中。本实施例中，具体是在基表origin_table上，分别添加基于delete,update,insert的aftertrigger，三个触发器的主要目的都是把基表的新数据同步到新表中；针对python程序对基表做的添加、修改和删除操作，在触发器的逻辑中获取到操作行主键，并把行数据以同样的操作回放到新表中。第四步.将基表内的数据拷贝迁移到临时表中；上一步的触发器保证了新来的dml操作会同步数据到临时表中，这一步即是把基表中触发器之前的基础数据全部拷贝到临时表中；在拷贝的过程中在基表上进行的dml操作同样会同步到临时表的数据中，由这两步操作来保证基表和临时表之间的数据一致性。具体的，本实施例中，数据拷贝迁移时是用insertasselect的方式把基表中的初始数据插入到新表中。在插入数据的过程中给新表加上共享锁lockinsharemode，并通过提示forceindex强制基表在查询数据的过程中使用主键，同时基表也必须要满足有主键约束。第五步.将基表重命名，再将临时表重命名为原基表名称并将基表及基表上的触发器删除；先把基表重命名，然后把临时表改成基表名；临时表是已经完成了ddl操作的新的表结构，并且数据和基表保持一致；重命名完成之后，临时表就作为新表用新的表结构对外提供服务了；在最后删除基表和基表上的触发器，清理线上环境。本实施例中具体为：首先需要把基表origin_table改名为origin_table_tmp，并且把新表new_table重命名为origin_table，整个操作是保证原子性的，最后把旧表origin_table_tmp直接删除，包括表上创建的三个触发器。此时拥有新的表结构的origin_table即可以对外提供服务。可以理解的是，以上实施方式仅仅是为了说明本发明的原理而采用的示例性实施方式，然而本发明并不局限于此。对于本领域内的普通技术人员而言，在不脱离本发明的精神和实质的情况下，可以做出各种变型和改进，这些变型和改进也视为本发明的保护范围。当前第1页1&nbsp2&nbsp3&nbsp当前第1页1&nbsp2&nbsp3&nbsp