MySQL如何给大表加索引

Author Avatar
Mr-houzi 7月 06, 2021

最近通过慢查询日志发现了一条慢SQL，相关业务表随着数据增加已达千万级，需要加索引进行优化查询。

给大表加索引、加字段属于DDL（数据定义语言）操作，任何对MySQL大表的DDL操作都值得警惕，不然很可能会引起锁表，报错Waiting for meta data lock，造成业务崩溃。那么如何对大表进行加索引操作？

早期DDL原理

再谈如何对加大表加索引前，先谈一下MySQL DDL操作为什么会锁表？对于这个问题，需要先了解一下MySQL5.6.7之前的早期DDL原理。

早期DDL操作分为copy table和inplace两种方式。

copy table 方式

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

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

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

早期copy VS inplace 方式？

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

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

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

知道了DDL的机制，下面就了解一下“如何对大表进行加索引操作”！

方案一：“影子策略”

此方法来自《高性能MySQL》一书中的方案。

方案思路

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

如何实践

SQL实现：

# 以下sql对应上面六步

create table tb_new like tb;

alter table tb_new add index idx_col_name (col_name);

rename table tb to tb_tmp, tb_new to tb;

alter table tb_tmp add index idx_col_name (col_name);

rename table tb to tb_new, tb_tmp => tb;

insert into tb (col_name1, col_name2) select col_name1, col_name2 from tb_new;

“影子策略”有哪些问题？

步骤3之后，新表改为原表名后（tb）开始承担业务，步骤3到结束之前这段时间的新产生的数据都是存在新表中的，但是如果有业务对老数据进行修改或删除操作，那将无法实现，所以步骤3到结束这段时间可能会产生数据（更新和删除）丢失。

方案二：pt-online-schema-change

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

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

工作原理

  1. 创建一张与原表结构相同的新表
  2. 对新表进行DDL操作（如加索引）
  3. 在原表上创建3个触发器（DELETE\UPDATE\INSERT），用来原表复制到新表时（步骤4）的数据改动时的同步
  4. 将原表数据以数据块（chunk）的形式复制到新表
  5. 表交换，原表重命名为old表，新表重命名原表名
  6. 删除旧表，删除触发器

如何使用

见使用 pt-online-schema-change 工具不锁表在线修改 MySQL 表结构一文

问题疑惑

见pt-online-schema-change的原理解析与应用说明-问题解答

方案三：ONLINE DDL

MySQL5.6.7 之前由于DDL实现机制的局限性，常用“影子策略”和 pt-online-schema-change 方案进行DDL操作，保证相对安全性。在 MySQL5.6.7 版本中新推出了 Online DDL 特性，支持“无锁”DDL。5.7版本已趋于成熟，所以在5.7之后可以直接利用 ONLINE DDL特性。

对于 ONLINE DDL 下的 inplace 方式，分为了 rebuild table 和 no-rebuild table。

Online DDL执行阶段

大致可分为三个阶段：初始化、执行和提交

Initialization阶段

此阶段会使用MDL读锁，禁止其他并发线程修改表结构
服务器将考虑存储引擎能力、语句中指定的操作以及用户指定的ALGORITHM 和 LOCK选项，确定操作期间允许的并发数

Execution阶段

此阶段分为两个步骤 Prepared and Executed
此阶段是否需要MDL写锁取决于Initialization阶段评估的因素，如果需要MDL写锁的话，仅在Prepared过程会短暂的使用MDL写锁
其中最耗时的是Excuted过程

Commit Table Definition阶段

此阶段会将MDL读锁升级到MDL写锁，此阶段一般较快，因此独占锁的时间也较短
用新的表定义替换旧的表定义(如果rebuild table)

ONLINE DDL 过程

  1. 获取对应要操作表的 MDL（metadata lock）写锁
  2. MDL写锁 降级成 MDL读锁
  3. 真正做DDL操作
  4. MDL读锁 升级成 MDL写锁
  5. 释放MDL锁

在第3步时，DDL操作时是不会进行锁表的，可以进行DML操作。但可能在拿DML写锁时锁住，见文章MySQL · 源码阅读 · 白话Online DDL

ONLINE DDL支持情况汇总

见文章MySQL · 源码阅读 · 白话Online DDL

[推荐阅读]

浅谈MySQL Online DDL
MySQL · 源码阅读 · 白话Online DDL

[参考]

大表加索引方案
使用 pt-online-schema-change 工具不锁表在线修改 MySQL 表结构
pt-online-schema-change的原理解析与应用说明-问题解答
MySQL Online DDL的改进与应用
浅谈MySQL Online DDL
MySQL · 源码阅读 · 白话Online DDL
MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations