关于mysql:MySQL如何给大表加索引

最近通过慢查问日志发现了一条慢 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