MySQL中修改表结构时需要注意的一些地方 |
|
本文标签：MySQL,表结构 MySql 在修改表结构的时候可能会中断产品的正常运行影响用户体验，甚至更坏的结果，丢失数据 。不是所有的数据库管理员、程序员、系统管理员都非常了解Mysql能避免这种情况 。DBA会经常碰到这种生产中断的情况，当升级脚本修改了应用层和数据库层，或者缺乏经验的管理员、开发在不是很了解Mysql内部工作机制的情况下修改了规范文件 。 真相是：
Percona MySQL 服务器开发团队鼓励用户在计划或者执行数据库迁移的时候先和我们沟通 。我们的目标是基于用户给出的各种情况给出最佳的方案 。旨在避免锁表当用户对非常大的表执行DDL，以确保应用能像平常一样正常运行，同时也在努力改善响应时间或增加系统功能 。最差的情况是确保那些经不起当机的系统在黄金交易时间正常运行 。 我们使用的大多数安装包仍然小于Mysql5.6，这需要我们不停尝试新的安装环境来把数据库迁移造成的损失降到最低 。这可能需要一个能“在线修改规范定义文件”的工具来升级或者修改规范文件 。Mysql5.6解决这一问题的做法是通过减少重建表和锁表的场景，但这个方法不能覆盖所有的可能的操作，例如当修改一列的数据类型时必然需要全表重构 。Przemys?aw和 Malkowski在去年尽可能详尽的讨论了Mysql5.6运行中修改定义 。
说实话，锁表操作会经常被忽视，在操作30M大小的表时我们更倾向于直接修改，但是30G，300G的表就要考虑一下了 。当使用率不高或者对锁定时间要求不是很高的的系统来说直接操作也许更好 。可是，我们常常会遇到一个需要立即执行的SQL，或者因为性能问题需要紧急增加一个索引来减少加载时间 。
上面提到，在线修改表定义是工作流中的一个模块 。通常是不错的解决方案，但也会遇到不能使用的场合，例如：当某个表使用了触发器 。了解pt-osc在我们项目中的工作过程很重要，让我们来看一下源代码： 复制代码 代码如下: [moore@localhost]$ egrep Step pt-online-schema-change
# 步骤 1: 创建一个新表 # 步骤 2: 修改清空表. 这应该比较快, # Step 3: 创建触发器来捕获原始表的改变 <--(锁定元数据) # Step 4: 复制数据. # Step 5: 重命名表: <--(锁定元数据 # Step 6: 更新外键 如果是子表. # Step 7: 删除旧表. 我把上面第三步到第五步高亮出来，这是锁表可能引起系统停机的时间 。但步骤六设计外键更新是一个循环的操作，是避免在更新关系的时候隐含地重建表 。有很多方法可以确保表的完整性约束，在pt-osc的说明文档中详细说明了，在开始之前预览你的表结构包括约束，并知道怎样把修改表定义所造成的影响降到最低 。
校订：2.2版本的percona工具新增了一个变量–tries 和变量–set-vars 共同被部署，解决了各种pt-osc操作可能会锁表的情况 。pt-osc (–set-vars)默认会设置如下的会话变量当连接到数据库服务器的时候 。
复制代码 代码如下: wait_timeout=10000
innodb_lock_wait_timeout=1 lock_wait_timeout=60
复制代码 代码如下: –tries swap_tables:5:0.5,drop_triggers:5:0.5
说明文档在这里http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries 它阐述了即便使用了诸如pt-osc之类的工具，充分了解你想解决的问题是很重要 。下面的流程图会帮助你当你了解修改了MYSQL数据库的结构的注意事项 。请仔细阅读建议尽管有些图上未标出，例如磁盘空间，IO加载等 。
选择合适的DDL操作 确保能清楚了解在修改表结构对你的系统会产生何种影响，并选择合适的方法来使这种影响降到最低 。有时这意味着需要将改动延期直到系统到了不常使用的时候或者使用能在操作期间不锁表的工具 。当你表中有触发器的时候一般直接修改表结构 。
|
5.5.3版本的说明，当开启一个事务时会锁定它会用到的所有表的数据（不依赖于存储引擎），并在事务提交的时候释放锁 。这样做确保了在开启事务期间不能修改表的定义 。
