Mysql使用pt工具在大表添加索引
前言
在MySQL中,使用ALTER TABLE
语句对大表增加索引可能会耗费较长时间,并且可能影响数据库的正常使用。
ALTER TABLE your_table_name ADD INDEX index_name (column_name);
简介
MySQL工具pt-online-schema-change - 无锁表修改表结构,这里无锁表也不是绝对的,在交互原表与中间表 表名的时候也会有元数据锁,只不过时间很短。
语法
pt-online-schema-change [OPTIONS] DSN# 给表actor.sakila 添加一个列
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor# 修改表sakila.actor 的存储引擎为InnoDB。
# 如果该表原本就是innoDB的存储引擎,该语句还能用来非阻塞方式的 OPTIMIZE TABLE
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
pt-online-schema-change 可以在线修改表结构而不阻塞该表的读和写。
工作原理
pt-online-schema-change 使用了MySQL内部变更表的方式,但是 是对 需要变更表的中间表(很多人也叫幽灵表) 使用MySQL内部变更的方式。这意味着原表不会被锁表,客户端可以继续对该表进行读写。
1、检查更改表是否有主键或唯一索引,是否有触发器
2、检查修改表的表结构,创建一个中间表,在中间表上执行ALTER TABLE语句
3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
4、从源表拷贝数据到临时表,在拷贝过程中,对源表的更新操作会写入到新建表中
5、将临时表和源表rename(需要元数据修改锁,需要短时间锁表)
6、删除源表和触发器,完成表结构的修改。
限制(安全措施)
该工具不会修改表结构 ,除非执行了--execute选项:
- 如果表中不存在主键和唯一索引,该工具不会运行 。
- 如果从副本中设置了复制过滤规则 ,该工具不会运行 。与参数 --[no]check-replication-filters有关
- 如果外键约束引用该表,该工具将拒绝更改该表,除非指定 --alter-foreign-keys-method
- 如果发现从副本中的延迟超过了参数--max-lag 设置的值,该工具将暂停执行。
- 如果检测到负载过大,则该工具暂停或终止操作。与参数--max-load和--critical-load有关。
- 该工具会设置参数 innodb_lock_wait_timeout=1 和 (5.5版本或者更新版本) lock_wait_timeout=60 ,因此更有可能成为锁争用过程中的受害者,并且不太可能扰乱其他事务,这些参数可以通过--set-vars 来设置
- 该工具无法更改“Percona XtraDB Cluster”节点上的 MyISAM 表。
输出内容
该工具的日志输出到STDOUT ,可以方便查看具体操作。
在数据拷贝阶段,使用 --progress 会打印报告到 STDERR
可以通过使用 --print 来获取额外的日志信息。
通过执行 --statistics ,会将事件计数打印在最后 。
下载
下载最新版本
wget percona.com/get/percona-toolkit.tar.gzwget percona.com/get/percona-toolkit.rpmwget percona.com/get/percona-toolkit.deb
参考
官方文档
pt-online-schema-change — Percona Toolkit Documentation