连MySQL表的DDL怎么改都不知道。转载本文请联系三太子敖丙公众号。前言随着业务的发展，用户对系统的要求越来越高，这就要求系统能够快速更新迭代以满足业务需求。通常，在系统版本发布时，首先要进行数据库的DDL变更，包括创建表、添加字段、添加索引、修改字段属性等。无论数据量大或小，DDL的执行速度很快，基本对业务没有影响。但是在数据量很大的情况下，而我们的业务已经实现了读写分离和实时数仓对接，此时DDL的变化是一个需要各方业务综合考虑的难题。下面说说MySQL中DDL执行方式的变化，如何选择大表DDL，以及我们公司这些年对DDL执行过程的监控。MySQL中的DDLDDL概述MySQL中的DDL语句有多种形式。总结起来有以下几类：CREATE、ALTER、DROP、RENAME、TRUNCATE。这些操作是隐式提交的并且是原子的，要么成功要么失败。在MySQL8.0之前，不记录DDL操作。今天说说系统版本发布相关的数据库结构变化，主要是ALTERTABLE变化。DDL更改过程与普通DML更改类似，如下图。注：这里涉及到MySQL的基础知识，不懂的朋友。只需下载我的MySQL基础章节。在早期的MySQL版本中，DDL变更会导致整张表被锁住，阻塞对表的DML操作，影响业务的正常运行。好在随着MySQL版本的迭代，DDL的执行方式也在发生变化。元数据元数据MySQL的元数据（MetaData）与其他RDBMS数据库一样。描述对象的结构信息存储在information_schema架构下，如常见的TABLES、COLUMNS等。下面的例子是创建一张表crm_users，MySQL会自动将数据插入到相关的数据字典表如Information_schema.tables和列。这些数据称为元数据，通常是静态的。只有当表发生DDL操作时，才会实时更新。MetaDataLockMySQL使用MetaDataLock来管理对象访问并确保数据一致性。对于一些核心业务表，对表的DML操作比较频繁，此时添加字段可能会触发MetaDataLock。可以看到Waitingfortablemetadatalock等待事件，155号线程正在执行altertable等待154号线程执行select释放锁，因为DML在执行过程中会持有SHARED_READ锁，获取SHARED_UPGRADABLE（共享可升级锁，简称）时执行DDLForSU，允许并发更新和读同一张表）锁成功，但是获取EXCLUSIVEMetaDataLock锁失败，处于pendingPENDING状态。DDL的执行方式可以参考MySQL官方文档。ALTERTABLE的选项有很多，与性能相关的选项主要有ALGORITHM和LOCK。ALGORITHMOPTIONDESCRIPTIONCOPYMySQL早期的修改方式需要先创建修改后的临时表，然后将原表数据按数据行复制到临时表中，重命名完成创建。在此期间，不允许并发DML操作。原始表是可读的，不可写的，并且需要两倍的磁盘空间。INPLACE直接修改原表，不创建临时表复制数据和重命名。原始表将持有排他元数据锁，这通常允许并发DML操作。MySQL5.8支持INSTANT。只修改数据字典中的元数据，不影响表数据。执行时没有ExclusiveMetadataLock，允许并发DML操作。从这张表可以看出，MySQL一直在优化DDL的执行方式，目的是提高DDL执行效率，减少锁等待，在不影响表数据的情况下，同时不影响正常的DML操作。LOCK选项LOCKOPTiONDESCRIPTIONDEFAULT默认模式：MySQL根据运行情况自动选择在尽可能不锁表的情况下使用LOCK模式。NONE无锁：OnlineDDL期间允许并发读写操作。如果OnlineDDL操作不支持对表进行并发DML操作，则DDL操作失败，表修改无效。SHARED共享锁：OnlineDDL操作时读不受影响，写被阻塞。EXCLUSIVE排他锁：OnlineDDL操作期间不允许对锁表进行任何操作。下面举例说明这些方法的执行过程。首先，创建一个测试表并创建一些数据。COPYCOPY方法的变更过程如下：根据业务需要，需要在crm_users中增加一个字段user_type，使用COPY方法进行变更。从执行过程和profile可以看出，会通过COPY方法创建临时表#sql-564_85，获取SystemLock，将数据复制到临时表，最后rename表名被切换释放Lock资源。执行操作期间不支持并发DML。INPLACEINPLACE方法是直接修改原表。对于添加索引、增删列、修改字段NULL/NOTNULL属性等操作，需要修改MySQL内部数据记录，重建表（RebuildTable）。从执行过程可以看出，执行过程中需要获取ExclusiveMetadataLock，修改表数据，释放Lock，支持并发DML操作。INSTANTMySQL5.8推出的方式，DDL只修改数据字典中的元数据，不影响表数据。没有独占元数据锁，允许并发DML操作。受支持的DDL更改是有限的。目前主要包括添加字段、添加/删除生成列、修改ENUM或SET列、改变索引类型、重命名表。三种方式执行效率对比执行方式/项目数据量(w)执行时间(s)重建表修改元数据修改数据允许并发DMLCOPY65029.89YESNoYesNoINPLACE65010.56YESNoYesYesINSTANT6500.19NoYes否是ONLINEDDL最高支持MySQL8.0。OnLineDDL有三种方法：COPY、INPLACE和INSTANT。MySQL会根据执行的DDL自动选择使用哪种方法。通常，首选INSTANT方法。如果不支持，请选择INPLANCE方法。如果不支持，只能选择COPY方式。MySQL官方文档还提供了OnlineDDL的支持矩阵，其中列出了常用的DDL操作。比较项主要包括是否重建表、允许并发DML操作、只修改元数据。表数据不受影响。操作InstantInPlaceCopyRebuildsTablePermitsConcurrentDMLOnlyModifiesMetadata添加列YesYes*YesNo*Yes*Yes删除列NoYesYesYesYesNo重命名列NoYesYesNoYesYes是否是是删除列默认值是是是否是是更改自动增量值NoYesYesNoYesNo使列为NULLNoYesYesYes*YesNo使列NOTNULLNoYesYesYes*YesNo添加主键NoYes*YesYes*YesNo删除主键NoNoYesYesNo否创建或添加二级索引否是是否是否删除索引否是是否是是重命名索引否是是否否否添加一个FULLTEXT索引否是*是否*否大表DDL解决方案实际业务系统中，业务发展比较快，表的数据量比较大，业务层分离了读取和写。MySQL数据实时同步到数仓（包括实时数仓和离线数仓）。实际的数据库结构如下。假设这是一个交易系统数据库，订单表booking有8000w条数据，并且连接了实时和离线仓库。根据业务需要，在订单表booking中增加一个字段。MySQL5.7之前增加字段属于高危操作，需要充分考虑对业务的影响。主要存在两个方面：读写分离场景下，主从同步延迟导致业务实时数据不一致仓库ADB不允许源端MySQL表重命名。如果表名会被COPY或者pt-osc、gh-ost等工具重命名，那么需要将表从数据仓库中删除，需要重新配置同步（full+incremental）。会影响数据仓库业务的ONLINEDDL方式对于MySQL5.6到5.7，可以使用OnLineDDL方式进行更改。对于大表，执行时间会很长。好处是不会影响Master上的DML操作，但会造成Master-slave延迟。如果Master上添加字段需要20分钟，对应的Slave也会执行20分钟。在此期间，Slave一直处于延迟状态，会造成业务数据不一致。比如用户在Master上成功下单，Slave会因为延迟查询不到订单信息，用户误认为是网络原因导致下单未成功，重新下单，导致重复下单。这种方式会造成主从延迟，但不会影响实时数仓的业务。根据业务情况，只能在业务淡季执行。为了解决DDL变更带来的主从延迟对业务的影响，pt-osc工具会想到使用pt-osc（pt-online-schema-change）或者gh-ost工具进行大表变更。这两个工具执行过程和原理类似，更改过程如下（不考虑外键，根据MySQL规范不允许使用外键）：新建一张修改数据表结构的表，其中用于从源数据表导入新表数据。在源表上创建触发器，记录复制数据后对源数据表的数据修改操作，并在数据复制完成后执行这些操作，保证数据不会丢失。复制数据，将源数据表中的数据复制到新表中。修改外键关联的子表，根据修改后的数据修改外键关联的子表。源数据表重命名为旧表，新表重命名为源表名，删除旧表。删除触发器。执行pt-osc时，还需要获得ExclusiveMetadataLock。如果在此期间有对表的DML操作，pt-osc操作会一直处于pendingPENDING状态。这时候，正常的对表的DML操作就会被阻塞。MySQL活跃连接数瞬间暴涨，CPU使用率100%。依赖于该表的接口将报告错误。所以需要选择在业务的非高峰期执行，同时做好MetaDataLock的监控工作，才不会影响业务。我们来看一个例子：D=trade,t=booking：数据库trade，表名booking。--chunk-size=1000：每次复制的数据行数。--max-log=1：保证从库延时不超过1s，超过则停止复制数据。--check-interval=2：表示等待2s后继续复制数据。--recursion-method="hosts":如果不使用默认的3306端口，使用hosts方法找从库比较靠谱。一般MySQL的binlog格式是ROW，pt-osc在复制数据的过程中也会产生大量的binlog，也可能造成主从延迟。有必要控制每个数据副本的大小和频率。在执行过程中，也会降低DML的Concurrency。MySQL8.0变更方法用过Oracle的都知道，DDL变更就是修改元数据，Oracle中上亿表的DDL变更是瞬间完成的。令人兴奋的是，MySQL8.0还引入了INSTANT方法，只修改MetaData，不影响表数据，因此其执行效率几乎与表的大小无关。新系统上线建议尽量使用MySQL8.0，老数据库也可以升级到MySQL8.0以获得更好的性能。官方文档解释INSTANT：INSTANT：Operationsonlymodifymetadatainthedatadictionary。在准备和执行期间，表上没有独占元数据锁，表数据不受影响，使操作即时进行。允许并发DML。（MySQL8.0.12引入）不仅要解决主从同步问题，还要解决数据仓库不同步重命名问题。目前只有INSTANT方式可以满足需求。监控DDL执行进度当对一个大表执行DDL变更时，你很关心它的执行进度。MySQL5.7之前，没有很好的监控工具，基本只能等。在MySQL8.0中，可以通过打开performance_schema和打开events_stages_current事件进行监控。总结一下，DDL在业务系统版本迭代过程中必不可少。如何在不影响业务和外围系统的情况下实现DDL的平滑变更，需要综合考虑系统特性，评估重要性和优先级。同时我们也要掌握不同MySQL版本的DDL执行方式，这样才能做出更好的选择。比如上面提到的，我目前在大数据团队，我们的业务已经和读写分离了。同时连接实时数仓。数据仓库不支持重命名操作。这时候可以选择在业务淡季使用ONLINEDDL。这样执行，对业务系统的影响很小，同时不影响数据仓库。
