DDL经验总结

Jacob407
166 阅读5分钟

总结

  1. 执行DDL需要注意的点？
  2. 实际执行的过程描述
  3. 对于Inplace的理解
  4. 常见的DDL操作
  5. SQL REVIEW的影响
  6. 问题
  • Mysql 如何处理DDL？
  • Online DDL是怎么实现的？
  • In place 和 copy的区别？
  • DDL锁表吗？
  • DDL执行注意点？
  • DML 读锁 和 写锁的理解？

执行DDL需要注意的点？

  • 【事前测试】提前做好测试，对时间、和空间做好评估，例如DDL的时候可能用到临时文件， 物理空间需要预备2倍以上
    • 【估算时间】DDL 的执行时间，和很多因素相关，如果需要比较精确的时间预估，建议在测试环境提前做测试。
    • 【估算空间】要注意计算而外的空间的占用， 生产的磁盘一般有限， 提前做好测试是比较重要的；
    • 【选择时间点】
      • 规避一些意外情况的发生
      • 选择一个低峰期是DDL的重要考虑点， 例如可能出现一些意外，虽然对数据一致性没影响，但是可能会卡主数据库，例如mysql没有选择合适的锁，导致DML阻塞。又利于因为一些情况需要kill掉ddl，而ddl的kill是有滞后性的等等。
  • 【事前分析优化】
    • 优化什么地方
      • DDL的几个大的开销点就是无法支持并发DML，rebuild table带来的io和空间开销， DDL后的空间碎片等等。
    • 怎么优化
      • 【最后原则】
        • 加列最后加到最后一列
      • 【降锁提速】
        • DDL语句允许我们选择锁类型和DDL类型，给予我们更好的自由度。比如当执行删除列时，MySQL默认使用的是Inplace Rebuild操作，锁级别是None (允许并发读写)。如果业务可以妥协，那么可以将锁级别设置为SHARED (允许并发读但阻塞写)，这样DDL可以更快完成。
        • 不用一味地追求支持并发DML，可以适当地降低锁的粒度，然后提高执行的速度，例如本来锁的级别是NONE， 可以升级为shared，支持读的DML，不支持写的DML，这样的话也会快一点。
      • 【指定算法和锁的原则】
        • 无论你在测试环境多么满意，都一定要指定算法的和锁，这样避免Mysql优化器判断 错误
        • DDL应显式指定ALGORITHM，从低成本(INSTANT)到高成本(COPY)逐一尝试，当不匹配时MySQL会报错。以防我们认为的一个低成本的DDL，因为认为失误而需要重建表，造成运维事故。
      • 【拆分DDL语句】
        • 在以前版本中，MySQL的DDL都需要重建表，所以会建议将一个表的多个变更写在同一句DDL中，用一次重建实施多个变更。
        • 而现在，如果一句DDL中的多个变更 的算法不同，那么会使用其中最高成本的算法。运维中，需要仔细甄别情况，使得一部分变更可以更快完成上线。
      • 【默认值更新法】 有新增字段需要更新2-3个状态数据（例如1，2），那么这个语句插入到最后一列，并且有默认值1，然后再更新2，因为2的列占用小数，update很快。最后再将default值改回来。
  • 【事后清理】
    • 【空间清理】DDL rebuild table的话，可能需要清理数据

实际执行的过程描述

  1. 进行充分的测试
    1. 时间： 控制好执行时间， 以合适的时间锁住资源；
    2. 空间： 部分inplace的操作会使用到中间表， 而copy也会产生临时表；
    3. 是否dml ： 通过指定lock可以确保某条DDL必须只能以“支持并发DML”执行；
    4. 使用等待时间来兜底： 如果需要支持并发DDL,担心执行时间太长的话，可以用等待时间
  2. 在不满意的基础上，进行可能的调优
    1. 能否拆分上线， 例如加索引的操作可以最后执行， 先让数据可用；
    2. 不同的语句使用不同的算法
    3. 增加列最好增加到最后一列， 这样在8.0.12后可以使用“instant”算法；
    4. 使用Lock Clause可以适当降低并发，来提高DDL;
    5. 理论需要结合实践, 以及根据实际情况进行处理
  3. 原则：
    1. 通过制定算法和lock子句来测试是否DDL执行的算法和并发的DML 如我们所预料， 避免执行时间过长，出现灾难；

inplace rebuild 和 copy算法的区别

  • inplace rebuild 和 copy算法的区别
    • copy算法
      • 位置： server层解决
      • 临时： 需要临时表
      • 并发DML： 不支持，直接锁表
    • inplace
      • 位置：innodb
      • 临时： 需要临时文件
      • 并发DML: 大部分可以支持，短期锁表
        • 不是inplace 或者 instant ，肯定不支持DML
  • Inplace 如何确定rebuild 或者 不 rebuild
    • 查官网的表
    • 但凡需要修改row数据格式内容，索引内容等，都需要rebuild
  • 怎么确定用inplace 算法 还是 copy算法

    • 使用sql 指定算法

    • 分析：

问题

  1. 大表的DDL, 你一般的执行顺序是什么?
  2. 如何确保一个ddl是支持并发的?
  3. 大表的DDL可能带来什么问题?
  4. 大表的DDL有什么优化手段?

 

avatar
文章
阅读
粉丝