无损DDL

无损DDL

一、什么是DDL，DML，DCL

1.1 DDL

DDL（Data Definition Language，数据定义语言）：用于定义数据的结构，比如 创建、修改或者删除数据库对象。它包含三个主要的关键字：create、drop、alter。

1.2 DML

DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。DML包括

• SELECT：用于检索数据；
• INSERT：用于增加数据到数据库；
• UPDATE：用于从数据库中修改现存的数据

• DELETE：用于从数据库中删除数据。

  1.3 DCL

  DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。DCL包括：

• ALTER PASSWORD

• GRANT
• REVOKE
• CREATE SYNONYM

二、表级锁和元数据锁

MySQL的表级锁有两种，一种是表锁，一种是元数据锁MDL。

2.1 表锁

表锁的语法： lock tables … read/write
释放时机：通过unlock tables 主动释放，当客户端断开时也会自动释放。
例如：线程A执行： lock tables t1 read, t2 write; 那其他线程写t1和读写t2时都会被阻塞, 而且线程A在unlock tables 之前，也只能执行读t1，读写t2，它自己也不被允许写t1。

2.2 MDL

元数据锁也是一种表级锁：metadata lock。
作用：不需要显示的使用，当访问一个表的时候，会被自动加上。MDL锁的作用是保证读写的正确性。也就是实现：当有用户对表执行DML相关操作时，其他线程不能把表结构给改了（想改表结构也可以，等排在它前面的DML全部执行完）。反之，当有线程在更改表结构时，其他线程需要执行的DML也会被阻塞住。
特性：

1. 系统默认添加。
2. 读锁之间不互斥。
3. 读写锁之间互斥。

三、什么是无损DDL？

需求：一般对公司对业务线来说，总是难免遇到需要修改线上表结构的需求。比如搞个活动，结果发现：现有的表中的列不够用了，那么就需要对现有的表进行无损DDL操作，添加一列。

3.1 有损DDL

为什么直接执行alter table add column有损？
alter table时是需要获取元数据锁的写锁的，而所有的DML操作又会被默认的加上元数据读锁。如果所有的语句都是DML语句那皆大欢喜，大家都是读锁彼此不影响。
但是当突然插入一个alter语句，一旦他持有写锁后，执行DDL语句时期间，所有的DML语句全部被阻塞，这种情况对业务来说是有损的。

3.2 无损DDL

无损是相对于业务来说的，如果能做到执行DDL的过程中，对业务无影响，那么这种DDL就是无损的。

四、重建表

4.1 什么是重建表？为什么要重建表？

4.1.1 造成空洞-删除数据流程

InnoDB 里的数据都是用 B+ 树的结构组织的。

B+ 树索引示意图
假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。
InnoDB 的数据是按页存储的，如果我们删掉了一个数据页上的所有记录，整个数据页就可以被复用了。如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另一个数据页就被标记为可复用。
如果我们用 delete 命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上的文件不会变小。delete 命令其实只是把记录的位置，或数据页标记为了“可复用”，但磁盘文件的大小不会变。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

4.1.2 造成空洞-插入数据流程

实际上，不止是删除数据会造成空洞，插入数据也会。
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

插入数据导致页分裂
可以看到，由于 page A 满了，再插入一个 ID 是 550 的数据时，就不得不再申请一个新的页面 page B 来保存数据了。页分裂完成后，page A 的末尾就留下了空洞。
更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

4.2 重建表

4.2.1 改锁表DDL-有损

MySQL 5.5 之前重建表，mysql执行alter table A engine=InnoDB的流程如下图：

在上面的过程中，MySQL会自动的为我们创建临时表，拷贝数据，交换表名，以及删除旧表。
特点：

1. 这个过程并不是安全的。因为在往tmp_table中写数据的过程中，如果有业务流量写入表A，而且写入的位置是不久前完成往tmp_table中拷贝的位置，就会导致数据的丢失。
2. 即使是MySQL会我们自动的创建临时表，数据拷贝的过程依然是在Server层面做的。

   alter table t engine=innodb,ALGORITHM=copy; 当你使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的流程就是图改锁表 DDL 的操作。

4.2.2 Online DDL-无损

MySQL 5.6 版本开始引入的 Online DDL，重建表的大概流程如下：

1. 创建一个临时文件, 扫描表A主键的所有数据页。
2. 使用数据页中的记录生成B+树，存储临时文件中。这一步是针对数据文件的操作,由innodb直接完成。
3. 在生成转存B+数的过程中，将针对A的写操作记录在row_log日志中，对应图中state 2状态。
4. 完成了B+树的转存后，将row_log中记录的日志在tmp_file中回放。
5. 使用临时文件替换A中的数据文件。

可以看到，与改锁表 DDL过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。
局限性：这种DDL本质上是在替换表空间中的数据文件，仅仅是用于对原表进行无损DDL瘦身。而不是解决动态无损加列的情况。

上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用 GitHub 开源的 gh-ost 来做。

五、gh-ost在线DDL

gh-ost基于 golang 语言，是 github 开源的一个 DDL 工具，是 GitHub’s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy 的缩写，即 GitHub 的在线表定义转换器。
其源码解读见
ghost工具源码梳理

5.1 命令参数

Usage of gh-ost:
  --aliyun-rds:是否在阿里云数据库上执行。true
  --allow-master-master:是否允许gh-ost运行在双主复制架构中，一般与-assume-master-host参数一起使用
  --allow-nullable-unique-key:允许gh-ost在数据迁移依赖的唯一键可以为NULL，默认为不允许为NULL的唯一键。如果数据迁移(migrate)依赖的唯一键允许NULL值，则可能造成数据不正确，请谨慎使用。
  --allow-on-master:允许gh-ost直接运行在主库上。默认gh-ost连接的从库。此外，单实例上DDL，单个实例相当于主库，需要开启--allow-on-master参数和ROW模式。
  --alter string:DDL语句
  --approve-renamed-columns ALTER:如果你修改一个列的名字，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的，除非提供-approve-renamed-columns ALTER。
  --ask-pass:MySQL密码
  --assume-master-host string:为gh-ost指定一个主库，格式为”ip:port”或者”hostname:port”。在这主主架构里比较有用，或则在gh-ost发现不到主的时候有用。
  --assume-rbr:确认gh-ost连接的数据库实例的binlog_format=ROW的情况下，可以指定-assume-rbr，这样可以禁止从库上运行stop slave,start slave,执行gh-ost用户也不需要SUPER权限。
  --check-flag
  --chunk-size int:在每次迭代中处理的行数量(允许范围：100-100000)，默认值为1000。
  --concurrent-rowcount:该参数如果为True(默认值)，则进行row-copy之后，估算统计行数(使用explain select count(*)方式)，并调整ETA时间，否则，gh-ost首先预估统计行数，然后开始row-copy。
  --conf string:gh-ost的配置文件路径。
  --critical-load string:一系列逗号分隔的status-name=values组成，当MySQL中status超过对应的values，gh-ost将会退出。-critical-load Threads_connected=20,Connections=1500，指的是当MySQL中的状态值Threads_connected>20,Connections>1500的时候，gh-ost将会由于该数据库严重负载而停止并退出。
        Comma delimited status-name=threshold, same format as --max-load. When status exceeds threshold, app panics and quits
  --critical-load-hibernate-seconds int :负载达到critical-load时，gh-ost在指定的时间内进入休眠状态。 它不会读/写任何来自任何服务器的任何内容。
  --critical-load-interval-millis int:当值为0时，当达到-critical-load，gh-ost立即退出。当值不为0时，当达到-critical-load，gh-ost会在-critical-load-interval-millis秒数后，再次进行检查，再次检查依旧达到-critical-load，gh-ost将会退出。
  --cut-over string:选择cut-over类型:atomic/two-step，atomic(默认)类型的cut-over是github的算法，two-step采用的是facebook-OSC的算法。
  --cut-over-exponential-backoff
  --cut-over-lock-timeout-seconds int:gh-ost在cut-over阶段最大的锁等待时间，当锁超时时，gh-ost的cut-over将重试。(默认值：3)
  --database string:数据库名称。
  --debug:debug模式。
  --default-retries int:各种操作在panick前重试次数。(默认为60)
  --discard-foreign-keys:该参数针对一个有外键的表，在gh-ost创建ghost表时，并不会为ghost表创建外键。该参数很适合用于删除外键，除此之外，请谨慎使用。
  --dml-batch-size int:在单个事务中应用DML事件的批量大小（范围1-100）（默认值为10）
  --exact-rowcount:准确统计表行数(使用select count(*)的方式)，得到更准确的预估时间。
  --execute:实际执行alter&migrate表，默认为noop，不执行，仅仅做测试并退出，如果想要ALTER TABLE语句真正落实到数据库中去，需要明确指定-execute
  --exponential-backoff-max-interval int
  --force-named-cut-over:如果为true，则'unpostpone | cut-over'交互式命令必须命名迁移的表
  --force-table-names string:在临时表上使用的表名前缀
  --heartbeat-interval-millis int:gh-ost心跳频率值，默认为500
  --help
  --hooks-hint string:任意消息通过GH_OST_HOOKS_HINT注入到钩子
  --hooks-path string:hook文件存放目录(默认为empty，即禁用hook)。hook会在这个目录下寻找符合约定命名的hook文件来执行。
  --host string :MySQL IP/hostname
  --initially-drop-ghost-table:gh-ost操作之前，检查并删除已经存在的ghost表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
  --initially-drop-old-table:gh-ost操作之前，检查并删除已经存在的旧表。该参数不建议使用，请手动处理原来存在的ghost表。默认不启用该参数，gh-ost直接退出操作。
  --initially-drop-socket-file:gh-ost强制删除已经存在的socket文件。该参数不建议使用，可能会删除一个正在运行的gh-ost程序，导致DDL失败。
  --master-password string :MySQL 主密码
  --master-user string:MysQL主账号
  --max-lag-millis int:主从复制最大延迟时间，当主从复制延迟时间超过该值后，gh-ost将采取节流(throttle)措施，默认值：1500s。
  --max-load string:逗号分隔状态名称=阈值，如：'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes
  --migrate-on-replica:gh-ost的数据迁移(migrate)运行在从库上，而不是主库上。
  --nice-ratio float:每次chunk时间段的休眠时间，范围[0.0…100.0]。0：每个chunk时间段不休眠，即一个chunk接着一个chunk执行；1：每row-copy 1毫秒，则另外休眠1毫秒；0.7：每row-copy 10毫秒，则另外休眠7毫秒。
  --ok-to-drop-table:gh-ost操作结束后，删除旧表，默认状态是不删除旧表，会存在_tablename_del表。
  --panic-flag-file string:当这个文件被创建，gh-ost将会立即退出。
  --password string :MySQL密码
 --port int ：MySQL端口，最好用从库
  --postpone-cut-over-flag-file string：当这个文件存在的时候，gh-ost的cut-over阶段将会被推迟，数据仍然在复制，直到该文件被删除。
  --quiet：静默模式。
  --replica-server-id uint : gh-ost的server_id
  --replication-lag-query string:弃用
  --serve-socket-file string：gh-ost的socket文件绝对路径。
  --serve-tcp-port int:gh-ost使用端口，默认为关闭端口。
  --skip-foreign-key-checks:确定你的表上没有外键时，设置为'true'，并且希望跳过gh-ost验证的时间-skip-renamed-columns ALTER
  --skip-renamed-columns ALTER：如果你修改一个列的名字(如change column)，gh-ost将会识别到并且需要提供重命名列名的原因，默认情况下gh-ost是不继续执行的。该参数告诉gh-ost跳该列的数据迁移，让gh-ost把重命名列作为无关紧要的列。该操作很危险，你会损失该列的所有值。
  --stack:添加错误堆栈追踪。
  --switch-to-rbr:让gh-ost自动将从库的binlog_format转换为ROW格式。
  --table string:表名
  --test-on-replica：在从库上测试gh-ost，包括在从库上数据迁移(migration)，数据迁移完成后stop slave，原表和ghost表立刻交换而后立刻交换回来。继续保持stop slave，使你可以对比两张表。
  --test-on-replica-skip-replica-stop:当-test-on-replica执行时，该参数表示该过程中不用stop slave。
  --throttle-additional-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数可以用在多个gh-ost同时操作的时候，创建一个文件，让所有的gh-ost操作停止，或者删除这个文件，让所有的gh-ost操作恢复。
  --throttle-control-replicas string:列出所有需要被检查主从复制延迟的从库。
  --throttle-flag-file string:当该文件被创建后，gh-ost操作立即停止。该参数适合控制单个gh-ost操作。-throttle-additional-flag-file string适合控制多个gh-ost操作。
  --throttle-http string
  --throttle-query string:节流查询。每秒钟执行一次。当返回值=0时不需要节流，当返回值>0时，需要执行节流操作。该查询会在数据迁移(migrated)服务器上操作，所以请确保该查询是轻量级的。
  --timestamp-old-table:在旧表名中使用时间戳。 这会使旧表名称具有唯一且无冲突的交叉迁移
  --tungsten：告诉gh-ost你正在运行的是一个tungsten-replication拓扑结构。
  --user string :MYSQL用户
  --verbose
  --version

5.2 操作模式

gh-ost 工作时可以连上多个 MySQL 实例，同时也把自己以从库的方式连上其中一个实例来获取二进制日志事件。根据你的配置、数据库集群架构和你想在哪里执行修改操作，可以有许多种不同的工作模式。

5.2.1连上从库，在主库修改

gh-ost 默认的工作模式，它会查看从库情况，找到集群的主库并且连接上去。修改操作的具体步骤是：

• ghost会连接上主库；
• 在主库上读写行数据：从主库中读取数据rowCopy主库上的影子表中；
• 添加对从库的binlog的监听，在从库上读取二进制日志事件，将变更应用到主库的影子表上；
  • 因为ghost会解析binlog，所以要求从库的binlog格式必须上row格式。不对主库的binlog格式有要求。
• 在从库上查看表格式、字段、主键、总行数等；
• 在从库上读取 gh-ost 内部事件日志（比如心跳）；
• 在主库上完成表切换（cutOver）；

如果主库的二进制日志格式是 Statement，就可以使用这种模式。但从库就必须配成启用二进制日志（log_bin, log_slave_updates），还要设成 Row 格式（binlog_format=ROW）。

使用示例：

# gh-ost --initially-drop-old-table --initially-drop-ghost-table --user="ghost" --password="ghost" --host=110.119.120.230 --port=3306 --database="test" --table="t1" --verbose --alter="ADD COLUMN y1 varchar(10),add column y2 int not null default 0 comment 'test' " --assume-rbr --execute

参数含义：

5.2.2 直接在主库上修改

如果没有从库，或者不想在从库上操作，那直接用主库也是可以的。gh-ost 就会在主库上直接做所有的操作。仍然可以在上面查看主从复制延迟。启动 gh-ost 时用—allow-on-master 来开启这种模式。

• ghost会连接上主库；
• 在主库上读写行数据：从主库中读取数据rowCopy主库上的影子表中。
• 添加对主库binlog的监听：在主库上读取二进制日志事件，将变更应用到主库的影子表上；
  • 要求主库的binlog格式为row格式。
• 在主库上查看表格式、字段、主键、总行数等；
• 在主库上读取 gh-ost 内部事件日志（比如心跳）；
• 在主库上完成表切换（cutOver）。

使用示例：

# gh-ost --initially-drop-old-table --initially-drop-ghost-table --user="ghost" --password="ghost" --host="110.119.120.231"  --port=3306 --database="test" --table="t2" --verbose --alter="add column test_field varchar(256) default '';" --exact-rowcount --serve-socket-file=/tmp/gh-ost.t2.sock --panic-flag-file=/tmp/ghost.panic.t2.flag --postpone-cut-over-flag-file=/tmp/ghost.postpone.t2.flag --allow-on-master --execute

参数含义：

5.1.3 在从库上修改和测试

这种模式会在从库上做修改，所有操作都是在从库上做的，不会对主库产生任何影响。在操作过程中， gh-ost 也会不时地暂停，以便从库的数据可以保持最新。
相关参数：—test-on-replica。表明操作只是为了测试目的。在进行最终的切换操作之前，复制会被停止。原始表和临时表会相互切换，再切换回来，最终相当于原始表没被动过。主从复制暂停的状态下，可以检查和对比这两张表中的数据（若不想stop slave，可添加参数—test-on-replica-skip-replica-stop）。

上面所说的主主、主从、并不是MySQL实例的主从关系。说的是 rowCopy和binlog的同步是在谁身上进行。

使用示例：

# gh-ost --initially-drop-old-table --initially-drop-ghost-table --user="ghost" --password="ghost" --host=110.119.120.230 --port=3306  --database="test" --table="t3" --verbose --alter="ADD COLUMN abc1 varchar(10),add column abc2 int not null default 0 comment 'test' " --test-on-replica --assume-rbr --execute

5.3 rds限制

1. 用户没有 Super 权限，因此使用过程中要加上—assume-rbr，gh-ost 会认为 binlog 本身就是 row 模式，不会再去修改。阿里云RDS上的binlog 默认也是 row 模式，所以不存在问题。
2. 其它权限，主要是REPLICATION SLAVE，REPLICATION CLIENT可以拉取 binlog ,也可以获得。
3. 无法连接到备库拉取binlog。备库通常对用户来说是透明的，所以gh-ost需要直接连接到主库上 去，这可能会增大对主库的负载。使用的时候需要增加—allow-on-master，—assume-master-host。 官方推荐的方式也是连接到其中一个备库，因为会有一些压力较大的SELECT操作，放在备库是最好的。
4. 阿里云数据库上执行，需要增加一个参数—aliyun-rds。 目前用户使用的话，记得加上以下几个参数： —allow-on-master —assume-rbr —assume-master-host —aliyun-rds。

5.4 三种模式应用场景

模式一的缺点，模式一会在从 DB 上面读取 binlog，可能造成数据库主从数据不一致，原因因为是主库的 binlog 没有完全在从库执行。所以个人感觉模式一有丢失数据的风险。
模式二任何操作都会在主库操作，或多或少会对主库负载造成影响，但是可以通过调整一些参数降低和时刻关注这些影响，所以个人推荐使用模式二。
模式三是偏向测试用的，但是模式三里有一个细节，cut-over 阶段有会 stop slave 一个操作,其实这个操作风险特别高，有时 stop slave 时间会很长，务必会对线上数据库使用造成影响，所以如果使用模式三做测试也要在线下数据库。