mysql大表操作DDL方式-lifecodes

随着业务的快速增长，用户中心的用户表user单表数据量越来越大，此时，如果我们业务调整，想给test_user表添加索引，便于提升性能。

或者，通过慢查询日志发现了一条慢SQL，相关业务表随着数据增加已达千万级，需要加索引进行优化查询，想给test_user表添加索引，便于提升性能。

实际上，直接给大表加索引、加字段属于DDL（数据定义语言）操作，很可能会引起锁表，报错Waiting for meta data lock，造成业务崩溃。

两种索引构建的方式

在线模式（Online DDL）：
这种模式允许在构建索引的同时，数据库可以继续进行读写操作，对业务的影响较小。
但不是所有的存储引擎和数据库版本都支持这种方式。
如果支持，例如在较新的 InnoDB 存储引擎版本中，可以通过设置参数来使用在线模式构建索引。
不过，在线模式可能会消耗更多的系统资源。
离线模式(Offline)：
在构建索引时，会对表进行锁定，禁止其他读写操作，直到索引构建完成。
这种方式比较简单直接，但会对业务产生较大的影响。

离线模式

方式1：copy table 方式

创建与原表相同的临时表，并在临时表上执行DDL语句
锁原表，不允许DML（数据操作语言），允许查询
将原表中数据逐行拷贝至临时表（过程没有排序）
原表升级锁，禁止读写，即原表暂停服务
rename操作，将临时表重命名原表

假设我们有一个名为orders的表，想要添加一个名为idx_order_date的索引到order_date列。

代码如下：

ALTER TABLE orders ADD INDEX idx_order_date (order_date), ALGORITHM=COPY;

在这个例子中，ALGORITHM=COPY指定了使用copy table方式来执行DDL操作。

数据库系统会创建一个新的临时表，添加索引，然后将旧表的数据复制到新表中。

完成后，旧表会被重命名为临时名称，新表会重命名为旧表的名称，完成DDL操作。

方式2：inplace 方式（fast index creation，仅支持索引的创建跟删除）

创建frm（表结构定义文件）临时文件
锁原表，不允许DML（数据操作语言），允许查询
根据聚集索引顺序构建新的索引项，按照顺序插入新的索引页
原表升级锁，禁止读写，即原表暂停服务
rename操作，替换原表的frm文件

继续使用上面的orders表，如果我们想要以最小的业务影响添加索引，我们可以使用inplace方式：


ALTER TABLE orders ADD INDEX idx_order_date (order_date), ALGORITHM=INPLACE;

在这个例子中，ALGORITHM=INPLACE指定了使用inplace方式来执行DDL操作。

数据库系统会在原表上直接添加索引，这种方式通常更快，因为它避免了数据的复制过程。

在线模式（MySQL5.6.7 之前）

方式一：“影子策略”

在MySQL 5.6.7及之前的版本中，由于DDL（Data Definition Language）操作的实现机制存在局限性，常常需要使用“影子策略”来执行DDL操作，以保证DML（Data Manipulation Language）操作的在线进行。

影子策略的核心思想是在不影响原始数据库性能的情况下，创建一个或多个与原始表结构和数据完全一致的数据表副本，这些副本被称为影子表。影子表可以用于备份、测试、分析或灾难恢复。

“影子策略”具体实践案例,大致如下：

创建一张与原表结构相同的新表（例如tb_new）。
在新表上创建索引。
重命名原表为其他表名（例如tb重命名为tb_tmp），新表重命名为原表名（tb_new重命名为tb）。
为原表（tb_tmp）新增索引。
交换表，新表改回最初的名称（tb），原表改回最初的名称（tb_tmp）。
把新表数据导入原表（即把新表承担业务期间产生的数据导入到原表中）。

“影子策略” 的优点

这种方法可以减少DDL操作对业务的影响，新增索引期间，原表可以正常的 DML 数据增删改的操作，不影响业务处理。

属于在线模式。

“影子策略” 的缺点

在新表新增索引期间，旧表业务增删改操作，期间可能产生的数据（更新和删除）丢失问题，也就是数据一致性的问题。

方案二：pt-online-schema-change 工具

PERCONA提供若干维护MySQL的小工具，其中 pt-online-schema-change（简称pt-osc）便可用来相对安全地对大表进行DDL操作。

pt-online-schema-change 方案利用三个触发器（DELETE\UPDATE\INSERT触发器）解决了“影子策略”存在的问题，让新老表数据同步时发生的数据变动也能得到同步。

pt-online-schema-change 工作原理

创建一张与原表结构相同的新表
对新表进行DDL操作（如加索引）
然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
将原表数据以数据块（chunk）的形式复制到新表
表交换，原表重命名为old表，新表重命名原表名
删除旧表，删除触发器

pt-online-schema-change 的优点

这种方法可以减少DDL操作对业务的影响，新增索引期间，旧表可以正常的 DML 数据增删改的操作，不影响业务处理。属于在线模式。

同时，在新表新增索引期间，旧表业务增删改操作，通过触发器同步到了新表，不产生的数据（更新和删除）丢失问题，实现了新表老表的数据一致性。

pt-online-schema-change 的问题:

表要有主键，否则会报错
表不能有trigger
尽管它是尽量减少对业务的影响，但在数据复制和同步阶段仍然会消耗一定的系统资源，包括 CPU、磁盘 I/O 和内存。对于大型表，这个过程可能会比较耗时，并且可能会对数据库的性能产生一定的影响。因此，最好在数据库负载较低的时候使用这个工具。

pt-online-schema-change 的方案，其实也是属于 “影子策略” 的一个方案变种，是一个保证了原表和影子表之间的数据一致性的 “影子策略” 方案。

ONLINE DDL（MySQL5.6.7 之后）

MySQL5.6.7 之前的“影子策略” 包括（pt-online-schema-change 方案），属于外部干预的 ONLINE DDL 方案。

在 MySQL5.6.7 版本中新推出了内部的 Online DDL 特性，支持“无锁”DDL。

5.7版本已趋于成熟，所以在5.7之后可以直接利用 ONLINE DDL特性。

MySQL5.6.7 Online DDL 的三个阶段

大致可分为三个阶段：

Prepare 阶段
执行
提交

MySQL 5.6.7 版本中 Online DDL 的执行主要分为以下三个阶段：

Prepare 阶段：
- 在这个阶段，MySQL 会创建新的临时 frm 文件（与 InnoDB 无关）。
- 持有 MDL（metadata lock）写锁，禁止读写操作（禁止 DML 和 DDL）。
- 根据 ALTER TABLE 类型，确定执行方式（copy, online-rebuild, online-no-rebuild）。对于 InnoDB 存储引擎，如果增加的是辅助索引（非主键索引），并且表没有外键约束，MySQL 可以使用 Online-Rebuild 算法。这种方式不需要复制整个表，而是在原表上重建索引，同时允许 DML 操作继续进行
- 更新数据字典的内存对象。
- 分配 row_log 对象记录增量DML log（仅 rebuild 类型需要）。
- 生成新的临时 ibd 文件（仅 rebuild 类型需要）。
DDL 执行阶段：
- 降级MDL（metadata lock）写锁成为 MDL读锁，允许读写操作（允许 DML，禁止 DDL）。
- 为了保证数据一致性，记录 DDL 执行过程中产生的增量DML log 到 row_log。在这个阶段，与此同时，原表表的所有DML操作日志写入row_log。
- 扫描原表的聚集索引每一条记录。
- 遍历新表的聚集索引和二级索引，逐一处理。
- 根据记录构造对应的索引项。
- 将构造索引项插入 sort_buffer 块排序。
- 将 sort_buffer 块更新到新索引树上。
- 重放 row_log 中的操作到新索引上，重放该阶段产生的 Row Log日志到新索引树。
Commit 阶段：
- 当前 Block 为 row_log 最后一个时，禁止读写，升级到MDL（metadata lock）写锁。
- 重做 row_log 中最后一部分增量。
- 更新 InnoDB 的数据字典表。
- 提交事务（刷事务的 redo 日志）。
- 修改统计信息。
- rename 临时 ibd 文件，frm 文件。
- 变更完成，释放MDL（metadata lock）写锁。

这三个阶段共同确保了 Online DDL 操作能够在不影响现有 DML 操作的情况下执行，从而提高了大型数据库操作的可用性和并发性。

使用外部 online 工具 gh-ost 做online DDL

gh-ost 采用 bin-log + 异步迁移 + 分块处理技术，它通过解析二进制日志，将原表的插入、更新和删除操作应用到幽灵表上，从而实现数据的迁移和同步。

gh-ost基本概述

gh-ost是一个用于 MySQL 数据库的无阻塞在线表结构迁移工具，

gh-ost 和pt-online-schema-change工具的功能类似，主要目的是在不中断或尽量少中断数据库服务的情况下进行表结构的变更，它是基于 MySQL 的复制协议（replication protocol）来工作的。

gh-ost 工作原理

创建幽灵表（Ghost Table）
当启动gh-ost工具进行表结构变更时，首先会创建一个幽灵表。
这个幽灵表的结构是按照修改后的表结构定义来创建的，例如，要添加新列或者修改列的数据类型等变更都会体现在幽灵表的结构中。
数据迁移与同步
接着，gh-ost会利用 MySQL 的二进制日志（bin-log）来跟踪原表的更改。
它通过解析二进制日志，将原表的插入、更新和删除操作应用到幽灵表上，从而实现数据的迁移和同步。这是一种异步的过程，原表的操作不会被阻塞，能够正常进行读写操作。
在数据迁移过程中，gh-ost还会对原表进行分块（chunking）处理。
它将原表的数据分成多个小块，逐块地将数据从原表迁移到幽灵表，这种方式有助于控制内存和磁盘 I/O 的使用，避免一次性处理大量数据带来的性能问题。
切换表操作
当幽灵表的数据和原表的数据基本同步完成后，gh-ost会进行一个切换操作。
这个切换操作是通过原子性地重命名表来实现的，将原表重命名为一个中间表，然后将幽灵表重命名为原表的名称。
这样，数据库的读写操作就会自动切换到新的表结构上，完成表结构的在线修改。

gh-ost 优势

高效的数据迁移和同步
利用 MySQL 二进制日志来跟踪和同步数据的方式使得gh-ost能够高效地处理数据迁移。
这种方式对于高并发的数据库环境特别有效，因为原表的读写操作不会被长时间阻塞，数据的更新能够及时地在幽灵表中得到反映。
灵活的分块策略
分块处理数据的策略使得gh-ost能够适应不同大小的表。
对于大型表，它可以通过合理地划分数据块，逐步完成数据迁移，从而减少对系统资源的瞬间冲击。
例如，对于一个有千万行数据的表，可以将其分成若干个较小的数据块，每次迁移一块数据，这样可以在不影响数据库正常运行的情况下完成数据迁移。
对数据库性能影响小
由于采用了异步迁移和分块处理等技术，gh-ost在整个表结构变更过程中对数据库性能的影响相对较小。
在数据迁移阶段，原表可以正常读写，只是在切换表的瞬间可能会有短暂的影响，但这种影响通常是可以接受的，特别是在数据库负载较高的情况下，这种优势更加明显。

gh-ost 局限性和注意事项

对二进制日志的依赖
gh-ost高度依赖 MySQL 的二进制日志来跟踪原表的操作。
如果二进制日志的配置不正确或者出现问题，例如，二进制日志损坏或者没有开启二进制日志，那么gh-ost将无法正常工作。
因此，在使用gh-ost之前，需要确保二进制日志的正确配置和完整性。
复杂环境下的风险
在复杂的数据库环境中，如存在大量的存储过程、视图、外键约束等，gh-ost可能会遇到一些问题。
虽然它在尽力减少对这些复杂结构的影响，但在某些情况下，仍然可能会导致数据不一致或者操作失败。
例如，在处理涉及多个表的外键关系时，需要特别小心，确保表结构变更后外键关系仍然正确。
资源消耗和性能优化
尽管gh-ost对性能的影响相对较小，但在数据迁移过程中仍然会消耗一定的系统资源，包括 CPU、磁盘 I/O 和内存。
对于资源紧张的数据库系统，需要仔细评估和优化gh-ost的使用，例如，合理调整分块大小和迁移速度等参数，以达到最佳的性能和资源利用效果。

目录CONTENT

mysql大表操作DDL方式