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

本文原文链接

尼恩说在前面

在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 方式

1. 创建与原表相同的临时表，并在临时表上执行DDL语句
2. 锁原表，不允许DML（数据操作语言），允许查询
3. 将原表中数据逐行拷贝至临时表（过程没有排序）
4. 原表升级锁，禁止读写，即原表暂停服务
5. 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，仅支持索引的创建跟删除）

1. 创建frm（表结构定义文件）临时文件
2. 锁原表，不允许DML（数据操作语言），允许查询
3. 根据聚集索引顺序构建新的索引项，按照顺序插入新的索引页
4. 原表升级锁，禁止读写，即原表暂停服务
5. 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）操作的在线进行。

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

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

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

“影子策略” 的优点

这种方法可以减少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 工作原理

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

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 的执行主要分为以下三个阶段：

1. Prepare 阶段：
   • 在这个阶段，MySQL 会创建新的临时 frm 文件（与 InnoDB 无关）。
   • 持有 MDL（metadata lock）写锁，禁止读写操作（禁止 DML 和 DDL）。
   • 根据 ALTER TABLE 类型，确定执行方式（copy, online-rebuild, online-no-rebuild）。对于 InnoDB 存储