关于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