本文原文链接

尼恩说在前面

在45岁老架构师尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团、蚂蚁、得物的面试资格，遇到很多很重要的相关面试题：

问题1：在实际生产环境中，如何给大表加索引？

问题2：给大表加索引会影响业务吗？如何解决？

最近有小伙伴面试阿里，都问到了相关的面试题。 小伙伴没有系统的去梳理和总结，所以支支吾吾的说了几句，面试官不满意，面试挂了。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的《尼恩Java面试宝典PDF》V175版本，供后面的小伙伴参考，提升大家的 3高架构、设计、开发水平。

《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取

如何判断什么时候可以给大表加索引？

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

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

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

任何对MySQL大表的DDL操作都值得警惕，那么如何对大表进行加索引操作？

简历神僧，45岁老架构师，给大家来要给彻底的解读。

两种索引构建的方式

例如，MySQL 在构建索引时，可以使用在线（Online）模式或者离线（Offline）模式。

在线模式（Online DDL）：

这种模式允许在构建索引的同时，数据库可以继续进行读写操作，对业务的影响较小。

但不是所有的存储引擎和数据库版本都支持这种方式。

如果支持，例如在较新的 InnoDB 存储引擎版本中，可以通过设置参数来使用在线模式构建索引。

不过，在线模式可能会消耗更多的系统资源。
离线模式(Offline)：

在构建索引时，会对表进行锁定，禁止其他读写操作，直到索引构建完成。

这种方式比较简单直接，但会对业务产生较大的影响。

如果数据库可以承受一段时间的停机或者业务低峰期足够长，离线模式也是一种选择。

关于DDL 和DML ，尼恩给大家说明一下

DDL（Data Definition Language）即数据定义语言，是用于定义、修改和删除数据库对象（如数据库、表、索引、视图、存储过程等）的 SQL 语句集合。它的主要作用是构建和管理数据库的结构，为存储和操作数据提供框架。

DML（Data Manipulation Language）即数据操作语言，用于对数据库中的数据进行操作。它主要包括 INSERT（插入）、UPDATE（更新）、DELETE（删除）操作，这些操作可以让用户在数据库的表中添加新的数据、修改现有数据的值或者删除不需要的数据。

早期DDL操作，属于离线模式(Offline) 类型

如果一张表数据量级是千万级别以上的，那么，给这张表添加索引，你需要怎么做呢？

这个和数据库的版本，有关系。

先看看 MySQL5.6.7之前的早期DDL原理。

早期DDL操作分为copy table和inplace两种方式，属于离线模式(Offline)，会对表进行锁定，禁止其他读写操作（DML），直到索引构建完成。

早期DDL操作分为copy table和inplace两种方式，具体如下：

方式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操作。

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

早期`copy` VS `inplace` 两种方式的对比

inplace 方式相对于 copy 方式来说，inplace 不会生成临时表，不会发生数据拷贝，所以减少了I/O资源占用。

inplace 只适用于索引的创建与删除，不适用于其他类的DDL语句。

但是，不论是早期copy 模式还是早期inplace 模式的DDL，都会进行锁表操作，不允许DML操作，仅允许查询。

所以，在数据库的早期版本中，DDL（Data Definition Language）可以理解离线（Offline）操作，因为这些操作往往会锁定表，阻止其他用户进行数据的插入、更新或删除操作，直到DDL操作完成。

“离线模式” 下，对数据的DML操作（如新增数据/删除数据）很可能会引起锁表，报错Waiting for meta data lock，造成业务崩溃。

总之，无论 copy方式还是 inplace 方式，数据库表在DDL操作期间不可用，因此被称为“离线模式”。

MySQL5.6.7 之前，如何在线模式为大表添加索引？

MySQL5.6.7 之前由于DDL实现机制的局限性，有两种在线模式为大表添加索引的方式：

“影子策略”
pt-online-schema-change 方案

这两种模式，都是从 mysql 外部进行。

方式一： “影子策略”

在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 的方案，其实也是属于 “影子策略” 的一个方案变种，是一个保证了原表和影子表之间的数据一致性的 “影子策略” 方案。

方案三：MySQL5.6.7 之后的内部 ONLINE DDL

MySQL5.6.7 之前由于DDL实现机制的局限性，常用“影子策略”和 pt-online-schema-change 方案进行DDL操作，保证相对安全性。

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 操作的情况下执行，从而提高了大型数据库操作的可用性和并发性。

MySQL5.6.7 Online DDL 如何保证数据一致性

Online DDL 操作过程中，从扫描原表的聚集索引每一条记录，构建索引项刷入到新的索引树上。

这个扫描过程，原表的数据可能会发生 DML 变更，从而导致新的索引树的数据和聚集索引上的数据不一致性。

MySQL5.6.7 Online DDL 如何保证数据一致性？

使用的是 row log 结构。

在数据库执行 DDL（Data Definition Language）操作期间，记录 DML（Data Manipulation Language）操作的 Row Log（行日志）非常重要。

这种日志主要用于在 DDL 操作过程中保证数据的一致性和完整性。

例如，当对一个表进行结构修改（如添加列、修改列的数据类型等）时，同时可能有其他事务在对该表进行 DML 操作（插入、更新或删除数据行）。

记录这些 DML 操作的 Row Log 可以在 DDL 操作完成后，根据日志中的信息来更新受影响的数据行，确保数据的准确性。

DDL 的row log 核心结构row_log_t

在线online处理DDL 的row log 的核心代码在文件row0log.cc中，有兴趣的可以进行详细解读。

online ddl的细节逻辑是：通过一个日志缓存，保留在ddl期间的 dml操作，然后进行缓存日志回复。

online ddl 的原理类似于gh-ost工具，只不过后者采用binlog进行dml操作回放，而mysql内部是单独维护一个核心缓存结构——row_log_t

/** @brief Buffer for logging modifications during online index creation

All modifications to an index that is being created will be logged by
row_log_online_op() to this buffer.

All modifications to a table that is being rebuilt will be logged by
row_log_table_delete(), row_log_table_update(), row_log_table_insert()
to this buffer.

When head.blocks == tail.blocks, the reader will access tail.block
directly. When also head.bytes == tail.bytes, both counts will be
reset to 0 and the file will be truncated. */
struct row_log_t {
   
  int fd;              /*!< file descriptor */
  ib_mutex_t mutex;    /*!< mutex protecting error,
                       max_trx and tail */
  page_no_map *blobs;  /*!< map of page numbers of off-page columns
                       that have been freed during table-rebuilding
                       ALTER TABLE (row_log_table_*); protected by
                       index->lock X-latch only */
  dict_table_t *table; /*!< table that is being rebuilt,
                       or NULL when this is a secondary
                       index that is being created online */
  bool same_pk;        /*!< whether the definition of the PRIMARY KEY
                       has remained the same */
  const dtuple_t *add_cols;
  /*!< default values of added columns, or NULL */
  const ulint *col_map; /*!< mapping of old column numbers to
                        new ones, or NULL if !table */
  dberr_t error;        /*!< error that occurred during online
                        table rebuild */
  trx_id_t max_trx;     /*!< biggest observed trx_id in
                        row_log_online_op();
                        protected by mutex and index->lock S-latch,
                        or by index->lock X-latch only */
  row_log_buf_t tail;   /*!< writer context;
                        protected by mutex and index->lock S-latch,
                        or by index->lock X-latch only */
  row_log_buf_t head;   /*!< reader context; protected by MDL only;
                        modifiable by row_log_apply_ops() */
  ulint n_old_col;
  /*!< number of non-virtual column in
  old table */
  ulint n_old_vcol;
  /*!< number of virtual column in old table */
  const char *path; /*!< where to create temporary file during
                    log operation */
};

从说明可以看出，mysql内部将online ddl分为两类：

一类是增加索引类，即调用row_log_online_op函数来进行dml操作缓存填写；
一类是其他ddl。则调用row_log_table_delete, row_log_table_update, row_log_table_insert进行缓存区填充。

下面说要一下，核心结构体中row_log_t各字段含义：

fd，path ：分别是在ddl操作期间，用于保存dml操作记录的临时文件的句柄和文件名；从源码可以看到该目录为innodb_tmpdir指定，若该值为空，则设置为tmpdir对应目录。其获取临时目录的函数为innobase_mysql_tmpdir()

blobs：记录的写入是按照记录块的方式，该字段表示记录块的数量；

table：不为null表示重建表，为null表示online 添加索引

tail，head：该成员就是记录块，分别用于写入和回放。具体结构 row_log_buf_t 下面会详细说明

DDL 的 Row Log 记录内容

操作类型记录

对于 DML 操作，日志文件会详细记录操作的类型，即插入、更新或删除。例如，使用特定的代码或标识符来表示这三种操作类型。如果是更新操作，还会记录更新的是哪个或哪些列。

比如，记录 “UPDATE 操作，更新了 customer 表中的 customer_name 列和 customer_email 列”。

数据行标识记录

为了能够准确地定位需要处理的数据行，日志文件会记录数据行的关键标识信息。

在有主键的表中，通常会记录主键值。例如，对于 customer 表，若 customer_id 是主键，在记录更新操作的 Row Log 中会包含 customer_id 的值，这样在后续处理时可以快速找到对应的行。

对于没有主键的表，可能会记录其他唯一标识或者数据行在表中的物理位置（如数据页编号和页内偏移量等信息，在 InnoDB 存储引擎中）。

数据值记录

对于插入操作，会记录插入的完整数据行内容。对于更新操作，会记录更新前和更新后的列值。

例如，记录 “更新前 customer_name 的值为 'John'，更新后的值为 'Johnny'”。

对于删除操作，会记录被删除行的数据内容或者关键标识，以便在需要时可以恢复或审计这些数据。

在 DDL 操作中的处理流程

DDL 操作开始前的日志记录开启

当检测到即将进行 DDL 操作时，数据库会开启专门的日志记录机制来捕获 DML 操作的 Row Log。这个机制可能会涉及分配内存缓冲区来暂存日志记录，或者直接将日志记录指向现有的重做日志（redo log）和撤销日志（undo log）系统（如在 InnoDB 存储引擎中）。
DDL 操作期间的日志记录

在 DDL 操作过程中，如修改表结构，所有对该表的 DML 操作的 Row Log 都会被持续记录。例如，在将一个表的列数据类型从整数改为字符串的过程中，如果有新的数据行插入，日志会记录插入操作的详细信息；如果有数据行更新，会记录更新前后的数据值和列信息。这些日志记录是按照时间顺序或者操作顺序进行存储的，以保证可以准确地还原操作过程。
DDL 操作完成后的日志处理

当 DDL 操作完成后，数据库会根据记录的 Row Log 来处理受影响的数据行。如果是添加列的 DDL 操作，会根据日志中的插入和更新操作记录，为新列填充合适的值或者更新数据行的结构。例如，通过日志中记录的插入操作，为新添加列填充默认值；通过更新操作记录，按照新的列结构调整数据行。如果是修改列的数据类型的 DDL 操作，会根据日志中的更新操作记录，对数据行中的列值进行转换或者重新组织，确保数据行与新的表结构相匹配。

与数据恢复和一致性维护的关系

数据恢复场景

在 DDL 操作过程中，如果系统出现故障（如崩溃、断电等），记录 DML 操作的 Row Log 可以用于数据恢复。数据库可以在重启后，根据日志中的信息来恢复在 DDL 操作期间未完成的 DML 操作，确保数据的完整性。例如，在故障发生前，有部分数据行已经完成插入操作但未在 DDL 操作后进行相应的处理，通过日志可以重新执行这些处理步骤。
数据一致性维护

通过记录和处理这些 Row Log，可以避免在 DDL 操作过程中由于数据行的变化而导致的数据不一致问题。例如，在修改表结构的同时，确保新插入的数据行能够符合新的结构要求，更新的数据行能够正确地转换列值。这样，无论是在正常的操作过程中还是在出现异常情况后，都能够维护数据库数据的一致性。

DDL 的 Row Log （DML增量日志）

我们先来看一个结构，它叫DDL 的DML增量 Row Log ，用于在DDL过程中，记录DML操作的日志。

一条 DML增量操作日志，结构为

操作flag + 事务id + 操作记录

下面是一个 log的例子：

 0x61 + 100 + <1000, ‘技术自由圈’, 40>

其中：

0x61代表类型，这是一个插入操作。操作flag包含两种：INSERT和DELETE，
100 表示这个操作的事务id，
<1000, ‘技术自由圈’, 1> 表示操作的记录。

DML增量日志的缓存（写入临时文件）和回放（读取临时文件）时，并不是以log 记录为单位进行的，而是以Block记录块为单位进行的。

log 和 bock 记录块的关系如下：

一个记录块可保存一条或多条DML log 增量日志。
一条增量DML 日志可能跨 2个 bock 记录块。

我以test_user表为例，讲解一下Row Log。如上图中， DML 日志和 block的对应关系如下：

最前面两个Log，存在第一个Block中
第3个Log的前半部分，存在第一个Block中，第3个Log的后半部分，存在第二个Block中
第4个Log，存在第二个Block中
第5个Log的前半部分，存在第二个Block中，第5个Log的后半部分，存在第三个Block中
第6个Log，存在第三个Block中

每个索引在DDL的时候，对应一个 DML增量 Row Log ，如上图为test_user表的索引index_height对应的Row Log。

Row Log的核心结构如下：

Log 日志：

表示DML操作日志，它的结构为操作flag + 事务id + 操作记录 。
head 指针：

head 指针指向第一个没有处理（/没有回放）的Bock 块。

在新的索引树建好了之后，这些增量DML log 都需要进行回放，把数据更新到新的索引树。

head 指针就是指向需要回放的 Bock 块，回放完成一个Bock 块里边的 DML Log后，head指针向后移到下一个Bock 块。

如上图，回放的时候，从Block的head 头部开始扫描，head指针在回放前的第一个Block的位置。
tail 指针：

从名字上看，tail相当于当前日志的尾部，head相当于当前日志的头部。回放时，head的位置不超过tail。

这是用于将DML操作写入一个Block时，用来定位Block中Log插入位置的指针，插入完一个Block，tail指针向后移动到新插入的Block。

如下图，Block的尾部插入Log，所以，在插入后，tail指针处在的最后一个Block位置。

前面讲到：一个记录块可保存一条或多条增量DML日志。一条增量DML日志可能跨2个记录块。

在物理存储上Row Log分为两部分：

内存日志：

内存中会存放一个总大小等于inndob_sort_buffer_size的Block，用于写入DML log 操作
文件日志：

当内存中大小大于innodb_sort_buffer_size，内部中 Block写满，写满的Block会刷到磁盘上，空出内存中的Block给后续的Log写入日志文件中，

所有Block总大小如果超过innodb_online_alter_log_max_size，写入就会报错

bock 记录块的大小由参数innodb_sort_buffer_size指定：

node1-performance_schema>show variables like "%innodb_sort_buffer_size%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
+-------------------------+---------+
1 row in set (0.01 sec)

参数默认为1MB，该参数为只读参数，无法动态调整。

head 、tail 指针的类型：row_log_buf_t 结构

row_log_buf_t是另一个重要类型，定义如下：



/** Log block for modifications during online ALTER TABLE */
struct row_log_buf_t {
  byte *block;            /*!< file block buffer */
  ut_new_pfx_t block_pfx; /*!< opaque descriptor of "block". Set
                       by ut_allocator::allocate_large() and fed to
                       ut_allocator::deallocate_large(). */
  mrec_buf_t buf;         /*!< buffer for accessing a record
                          that spans two blocks */
  ulint blocks;           /*!< current position in blocks */
  ulint bytes;            /*!< current position within block */
  ulonglong total;        /*!< logical position, in bytes from
                          the start of the row_log_table log;
                          0 for row_log_online_op() and
                          row_log_apply(). */
};

根据定义，可以知道，增量DML日志的缓存（写入临时文件）和回放（读取临时文件）时以block记录块为单位进行的，而不是以DML log 记录为单位的。

row_log_buf_t 结构成员详细解释

byte *block：

这是一个指向字节（byte）类型的指针，用于存储文件块的缓冲区。这个缓冲区用于存储在ALTER TABLE操作中被修改的数据块。

block 的大小由参数innodb-sort-buffer-size决定，默认为1MB。

在处理在线ALTER TABLE操作过程中的修改日志时，这个缓冲区可能用于暂存从文件（可能是数据库文件等）读取出来的块数据，或者是准备写入文件的块数据。

ut_new_pfx_t block_pfx：

这是一个不透明的描述符（opaque descriptor），用于描述block。这个描述符的作用是帮助内存分配器正确地管理和释放block所指向的内存块。它可能包含了关于内存块大小、分配来源等信息，使得内存管理系统能够准确地处理这个内存块。具体实现和用途依赖于底层的内存分配器。
mrec_buf_t buf：

这是一个用于访问跨越两个块的记录的缓冲区。

一条记录有可能因为大小原因，跨越了两个物理存储块。这个buf缓冲区就是为了方便处理这种情况而设置的。它可能包含了一些机制，用于正确地拼接和访问跨越两个块的记录数据，例如记录了两个块的位置信息以及如何将它们组合在一起的规则。
ulint blocks

blocks：当block空间使用完，会将block的数据写入临时文件中，再次利用block的空间。blocks字段用于记录当前处理的block的个数。

ulint bytes

bytes: 用于记录当前block内已经使用的字节数。
ulonglong total：

代表逻辑位置，是以字节为单位从row_log_table日志开始计算的。

这个变量提供了一种全局的位置参考，使得在处理日志数据时，可以清楚地知道当前操作的位置相对于整个日志文件的起始位置的偏移量。

如何追加 Row Log （/DDL期间的DML增量日志）?

下面我们再来看下Log是如何追加到Row Log的？

分为两种情况，具体如下：

我以test_user表的index_height索引的Row Log为例来说明。

(1) 全拷贝追加 Row Log

如果内存中 row_log_buffer 没有分配，创建一个innodb_sort_buffer_size（默认1M）大小的Block，tail指针指向这个新的row_log_buffer 。

如果内存中有row_log_buffer ，就判断一下 row_log_buffer 的剩余空间：

剩余空间 =  innodb_sort_buffer_size（默认1M）  -  当前Block中已有Log的总大小

如果剩余空间 > DML操作日志大小，那就就进行全拷贝，将DML操作日志全部拷贝到Block中末尾Log。

如上图，全拷贝最右侧，将DML日志<0x61 + 3355 + <25, ‘技术自由圈’, 1>>完整拷贝到末尾Log。

简历神僧、45岁老架构师尼恩特别提示，以上的图是对 mysql 源码的一大致的梳理，并没有代码级正确，主要目标是为了方便大家理解。

具体的源码细节过于复杂，可能和尼恩的梳理有出入，细节以源码为准。

再啰嗦一下，由于网上其他文章在这个复杂的场景，写的过于复杂，不好理解。尼恩在这里给大家梳理的是一个大致的全拷贝的流程。并不是代码级正确，但是这个不妨碍大家的理解。

如果觉得过于复杂，后面尼恩会在《尼恩Java面试宝典》配套视频中，做出细致解读。

(2) 半拷贝追加 Row Log

上面讲到，将log 复制到block 的时候，如果内存中有row_log_buffer ，判断一下 row_log_buffer 的剩余空间：

剩余空间 =  innodb_sort_buffer_size（默认1M）  -  当前Block中已有Log的总大小

那么，如果剩余空间 < DML操作日志大小 , 这个时候，log放不进去了，怎办？

全拷贝进行不下去了，那就是半拷贝。

什么是半拷贝，就是将DML操作日志分成两半：前半部分、后半部分。当然，两部分并不是平均分配的，大小并不相当。

半拷贝追加 Row Log 大概3步：

一半全部拷贝到row_log_buffer 末尾，
然后把row_log_buffer 里边的内容刷入 row_log_file，清空row_log_buffer
再把另一半拷贝清理后的 row_log_buffer 。

如上图，半拷贝里的第一步，将DML日志<0x61 + 3355 + <25, ‘技术自由圈’, 1>>的前半部分，拷贝到row_log_buffer 的末尾Log，

然后半拷贝里的第二步，将内存中tail 指向的整个Block（row_log_buffer ）写入Row Log日志文件，将 row_log_buffer 清理一下。

然后半拷贝里的第三步，将DML操作的后半部分，拷贝到row_log_buffer （还是tail ）的前面。如上图 , 将DML日志<0x61 + 3355 + <25, ‘技术自由圈’, 1>>的后半部分拷贝到row_log_buffer 的头部。

拷贝完成后， tail.blocks + 1，代表Row Log日志文件中新增了一个Block。

注意，以上的图是tail指针位置画错了， tail 指针应该是下面的位置。

注意上面的 tail 指针的位置。

简历神僧、45岁老架构师尼恩特别提示，以上的图是对 mysql 源码的一大致的梳理，并没有做到代码级正确，主要目标是为了方便大家理解。

再啰嗦一下，由于网上其他文章在这个复杂的场景，写的过于复杂，不好理解。具体的源码细节过于复杂，可能和尼恩的梳理有出入，所以实现的细节以源码为准。

尼恩在这里给大家梳理的是一个大致的半拷贝的流程。并不是代码级正确，但是这个不妨碍大家的理解。如果觉得过于复杂，后面尼恩会在《尼恩Java面试宝典》配套视频中，做出细致解读。

如何回放 Row Log ，把DDL期间的DML增量日志更新到索引树上的?

MySQL将DML日志写到Row Log，只是为了在执行DDL期间，可以并行执行DML，

最后，这些DML日志还是要更新到索引树上的，那么，如何回放 Row Log ？

所以，同样以索引index_height为例，我们再来看下Row Log中的日志是如何更新到索引树的？

下面我们再来看下Log是如何回放到索引树 B+ 的？

分为两种情况，具体如下：

接下来，以test_user表的index_height索引的Row Log 回放为例来说明。

（1）第一种回放：文件扫描/文件回放

先扫描磁盘上的Row Log file 文件，遍历文件中的Block， head指针指向Block的头部Log，从该Log开始，Log一个一个的写入索引树

MySQL先扫描磁盘上的Row Log文件，从head 开始，文件扫描部分为一个Block的遍历：

(1) head指针指向Block的头部Bock，从该Block里边的第一个Log开始，将头部Block里边的Log一个要给，全部写入index_height 索引树。

(2) 头部Block 清空，将head指针移到后面一个Block。文件扫描中的第二块长Block 方框。

(3) 重复(1)和(2)两步，直到head指针移到Block中最后一个Log，然后，将该Log中的记录写入索引树index_height。

如上图，文件扫描中的最后一个Log 的记录，写入索引树index_height的第最后个叶子节点。

重复步骤，完成 Row Log file文件中所有Block内的Log扫描和回放，全部写入索引树index_height，至此，Row Log file文件清空。

head 和tail，都指向了内存的 row_log_buffer ，进入下一个回放的阶段。

（2）第二种回放：内存扫描/内存回放

在追加的场景，大部分Block写入了Row Log文件，但是，还会存在小部分DML日志留存在内存的Block中，所以，接下来，MySQL需要处理这部分留存的row_log_buffer 。

完成了第一阶段的 Row Log file文件中扫描和回放后，此时，head 和tail 都指向了内存的 row_log_buffer ，进入下一个回放的阶段——内存扫描/内存回放。

如上图所示，不断扫描内存Block里边的 DML Log日志，将全部Log写入索引树index_height，内存扫描/内存回放动作结束。

需要注意的是：存扫描/内存回放阶段，需要对数据字典加排它锁，禁止新的DML操作，如果不加锁，会导致内存中Block不断更新，无法判断DML操作何时结束。

row_log_t 结构体的总结

在执行需要重建表结构的DDL操作时，如添加索引或修改列类型等，InnoDB会创建一个row_log_t对象来记录这些变更。这个对象会用于缓存DML操作日志，直到DDL操作完成。

工作流程

日志块分配：

在在线DDL操作期间，InnoDB会为row_log_t分配一个日志块（block），用于存储DML操作的日志记录。
日志记录：

所有对表的DML操作（如INSERT、UPDATE、DELETE）都会被记录在row_log_t的日志块中。这些日志记录了行级别的变更，以确保DDL操作完成后，这些变更能够被应用到新的表结构上。
日志回放：

在DDL操作完成后，row_log_t中的日志会被重放，将DML操作应用到修改后的表结构上。这个过程确保了数据的一致性和完整性。
性能优化：

row_log_t 的使用是InnoDB在线DDL操作性能优化的一部分。通过记录DML操作日志，InnoDB可以减少DDL操作对业务的影响，同时保持数据的完整性。
临时文件管理：

row_log_t中的日志块数据被存储在临时文件中，这个临时文件的大小由参数innodb_online_alter_log_max_size控制，默认值为128M。

如果这个参数设置的值不够大，可能会导致DDL操作失败。

row_log_t结构体是InnoDB在线DDL操作中记录和应用DML操作日志的关键组件，它帮助InnoDB存储引擎在修改表结构的同时，记录和回放对表数据的变更，确保数据的一致性和完整性。

MySQL5.6.7 Online DDL 在线添加索引的案例

了解了 row log 的底层原理之后，再来看一下InnoDB引擎中MySQL添加索引的过程。

以test_user表为例详细讲解一下：

Prepare阶段：

创建新的临时frm文件
持有EXCLUSIVE-MDL锁，禁止读写
根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild), 新增二级索引使用的是online-rebuild,
更新数据字典的内存对象，标记test_user表所有索引online_status为ONLINE_INDEX_CREATION，表示该表索引都处在在线DDL状态。
分配row_log对象记录增量 dml log
生成新的临时ibd文件，根据旧表test_user的ibd文件，创建副本ibd文件

ddl执行阶段：

降级EXCLUSIVE-MDL锁，允许读写
扫描old_table的聚集索引每一条记录 rec，并且遍历新表的聚集索引和二级索引，逐一处理
- 根据rec构造对应的索引项
- 将构造索引项插入sort_buffer块
- 将sort_buffer块进行归并排序，插入新的索引树
处理ddl执行过程中产生的增量 (仅rebuild类型需要) 。在这个阶段，与此同时，test_user表的所有DML操作日志写入Row Log。在第二步完成后，重放该阶段产生的test_user表的Row Log日志到新的索引树中，直到Row Log中的最后一个block，

commit阶段

升级到EXCLUSIVE-MDL锁，禁止读写
重做最后row_log中最后一部分增量
更新innodb的数据字典表
提交事务(刷事务的redo日志)
修改统计信息
rename副本ibd文件和frm文件为旧表名，即原test_user表的frm和ibd文件名
变更完成

1000W级大表内部 Online DDL 的性能问题：

CPU开销大

循环遍历旧表聚簇索引叶子节点的所有记录，如果表记录非常多，非常消耗CPU，

如果DDL长时间占用CPU资源，势必会影响MySQL的连接数，导致MySQL处理DML操作的并发请求数下降
磁盘开销大

归并排序使用的磁盘临时文件做记录排序，

如果文件中的已排序记录集非常多，那么，归并排序过程中产生大量的磁盘IO，

在MySQL处理查询时，如果内存中没有查询的结果，此时，buffer pool又满了，触发刷脏行为，这时就会出现查询请求等待刷脏结束，查询响应变慢。

那么，我们看看上面两个问题怎么解决呢？

针对第一个问题，由于表中的原有记录的数量是由业务发展决定的，业务发展快，记录数就会多，这点我们无法控制，所以，针对表数据量大导致扫描聚簇索引变慢，我们只能规避DDL带来的风险，规避方法如下：

评估表中的数据量
观察MySQL的CPU使用率

结合上面两个因素，如果数据量不大，那么，只要在非高峰期执行DDL（建议找到MySQL的CPU使用率比较低），对DML的影响是不大的。

目的	解决方法
减少业务影响	调大innodb_sort_buffer_size，降低磁盘IO
避免DDL过程中写Row Log溢出	调大innodb_online_alter_log_max_size
一定要在高峰期做DDL	建议使用第三方工具，比如，gh-ost，它是通过binlog完成DDL的，避免了扫描聚簇索引带来的CPU开销

如果一定要在高峰期做online DDL，怎么办？

或者一直都是高峰期，怎么办？

那么不做索引的添加了吗？

使用外部 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的使用，例如，合理调整分块大小和迁移速度等参数，以达到最佳的性能和资源利用效果。

gh-ost 和 pt-online-schema-change 的区别

gh-ost 和 pt-online-schema-change 是两个流行的在线schema变更工具，它们都用于在不锁定表的情况下进行DDL操作。以下是它们的主要区别：

一：触发器的使用：

gh-ost：不使用触发器，而是通过解析binary log来捕获对原表的更改，并将这些更改应用到ghost表中。这种方法使得gh-ost对迁移过程有更多的控制，可以真正暂停迁移过程，并且可以将迁移的写负载与主工作负载分离。
pt-online-schema-change：使用触发器来捕获对原表的更改，并将这些更改复制到新表中。这种方式在某些情况下可能会导致性能问题，尤其是在高并发的环境中。

二：同步性：

gh-ost：采用异步方式，通过解析binary log来异步地将变更应用到ghost表，这可能会增加网络流量，并且要求使用row-based replication以保证数据一致性。
pt-online-schema-change：采用同步方式，通过触发器同步地将变更应用到新表，这种方式可能会在高负载下影响性能。

三：对复制的影响：

gh-ost：由于不使用触发器，可以减少对复制的影响，并且可以更容易地暂停和恢复迁移过程。
pt-online-schema-change：使用触发器可能会增加主从延迟的风险，尤其是在高负载下。

四：对负载的控制：

gh-ost：提供了动态控制功能，允许用户在迁移过程中根据MySQL的指标动态调整迁移行为，例如设置线程运行的阈值。
pt-online-schema-change：也提供了负载控制功能，但可能需要在迁移过程中停止并重新配置以调整负载，这可能会增加操作的复杂性。

五：对特定场景的支持：

gh-ost：不支持外键和触发器，也不支持Galera Cluster，因为它使用LOCK TABLE进行表切换，这与Galera不兼容。
pt-online-schema-change：支持更多的场景，包括带有外键和触发器的表，以及Galera Cluster。

六：性能影响：

在某些测试中，gh-ost的性能开销可以忽略不计，而pt-online-schema-change可能会导致12%的性能下降。然而，在其他情况下，pt-online-schema-change可能提供比gh-ost更好的性能。

总的来说，gh-ost和pt-online-schema-change各有优势和局限性，选择哪个工具取决于具体的业务需求和环境。

尼恩架构团队的塔尖 sql 面试题

sql查询语句的执行流程：

网易面试：说说MySQL一条SQL语句的执行过程？

美团面试：Mysql 有几级缓存？每一级缓存，具体是什么？

索引

阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？

滴滴面试：单表可以存200亿数据吗？单表真的只能存2000W，为什么？

索引下推 ?

贝壳面试：什么是回表？什么是索引下推？

索引失效

美团面试：mysql 索引失效？怎么解决？（重点知识，建议收藏，读10遍+）

MVCC

MVCC学习圣经：一文穿透MySQL MVCC，吊打面试官

binlog、redolog、undo log

美团面试：binlog、redolog、undo log底层原理是啥？分别实现ACID哪个特性？（尼恩图解，史上最全）

mysql 事务

阿里面试：事务ACID，底层是如何实现的？

京东面试：RR隔离mysql如何实现？什么情况RR不能解决幻读？

分布式事务

分布式事务圣经：从入门到精通，架构师尼恩最新、最全详解 (50+图文4万字全面总结 )

阿里面试：秒杀的分布式事务，是如何设计的？

mysql 调优

如何做mysql调优？绝命7招，让慢SQL调优100倍

美团面试：Mysql如何选择最优执行计划，为什么？

说在最后：有问题找老架构取经‍

只要按照上面的尼恩团队梳理的方案去作答，你的答案不是 100分，而是 120分。面试官一定是心满意足，五体投地。

按照尼恩的梳理，进行深度回答，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

在面试之前，建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》，里边有大量的大厂真题、面试难题、架构难题。

很多小伙伴刷完后，吊打面试官，大厂横着走。

在刷题过程中，如果有啥问题，大家可以来找 40岁老架构师尼恩交流。

另外，如果没有面试机会，可以找尼恩来改简历、做帮扶。前段时间，刚指导一个小伙暴涨200%（涨2倍），29岁/7年/双非一本，从13K一次涨到 37K ，逆天改命。

狠狠卷，实现 “offer自由” 很容易的，前段时间一个武汉的跟着尼恩卷了2年的小伙伴，在极度严寒/痛苦被裁的环境下， offer拿到手软，实现真正的 “offer自由” 。

尼恩技术圣经系列PDF

……完整版尼恩技术圣经PDF集群，请找尼恩领取

《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》PDF，请到下面公号【技术自由圈】取↓↓↓

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

阿里面试：1000万级大表, 如何 加索引？