mysql加字段锁表

概述

在 mysql 5.5 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写入，对于大表这简直是灾难。从5.5特别是5.6里，情况有了好转，支持Online DDL，pt-online-schema-change是Percona-toolkit一员，通过改进原生ddl的方式，达到不锁表在线修改表结构。

1、pt-osc工作过程

1）创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)

2）在新表执行alter table 语句（速度应该很快）

3）在原表中创建触发器3个触发器分别对应ｉｎｓｅｒｔ,ｕｐｄａｔｅ,ｄｅｌｅｔｅ操作

4）以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表

5）ｒｅｎａｍｅ 原表到old表中，在把临时表ｒｅｎａｍｅ为原表

6）如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理

7）默认最后将旧原表删除

2、常用选项说明

这里只介绍部分常用的选项

--host=xxx --user=xxx --password=xxx连接实例信息，缩写-h xxx -u xxx -p xxx，密码可以使用参数--ask-pass 手动输入。--alter结构变更语句，不需要 ALTER TABLE关键字。与原始ddl一样可以指定多个更改，用逗号分隔。绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 ｄｅｌｅｔｅ和ｕｐｄａｔｅ 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，ｄｅｌｅｔｅ触发器将基于c1列。子句不支持 ｒｅｎａｍｅ 去给表重命名。ｒｅｎａｍｅ INDEX old_index_name TO new_index_namealter命令原表就不支持给索引重命名，需要先ｄｒｏｐ再add，在pt-osc也一样。但给字段重命名，千万不要ｄｒｏｐ-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "ｄｒｏｐ FOREIGN KEY _fk_foo"D=db_name,t=table_name指定要ddl的数据库名和表名--max-load默认为Threads_running=25。每个chunk拷贝完后，会检查 SHOW GLOBAL STATUS 的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。

因为拷贝行有可能会给部分行上锁，Threads_running 是判断当前数据库负载的绝佳指标。

--max-lag默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况（Seconds_Behind_Master）。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值。--check-interval配合使用，指定出现从库滞后超过 max-lag，则该工具将睡眠多长时间，默认1s，再检查。如--max-lag=5 --check-interval=2。

熟悉percona-toolkit的人都知道--recursion-method 可以用来指定从库dsn记录。另外，如果从库被停止，将会永远等待，直到从开始同步，并且延迟小于该值。

--chunk-time默认0.5s，即拷贝数据行的时候，为了尽量保证0.5s内拷完一个chunk，动态调整chunk-size的大小，以适应服务器性能的变化。

也可以通过另外一个选项--chunk-size禁止动态调整，即每次固定拷贝 1k 行，如果指定则默认1000行，且比 chunk-time 优先生效。

3、 使用限制

3.1 原表上不能有触发器存在

这个很容易理解，pt-osc会在原表上创建3个触发器，而一个表上不能同时有2个相同类型的触发器，为简单通用起见，只能一棍子打死。

所以如果要让它支持有触发器存在的表也是可以实现的，思路就是：先找到原表触发器定义；重写原表触发器；最后阶段将原表触发器定义应用到新表。

3.2 通过触发器写数据到临时新表，会不会出现数据不一致或异常

如果ｕｐｄａｔｅ t1，触发ｕｐｄａｔｅ t2，但这条数据还没copy到t2，不就有异常了吗？后台通过打开general_log，看到它创建的触发器：

6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER ｄｅｌｅｔｅ ON `confluence`.`sbtest3` FOR EACH ROW ｄｅｌｅｔｅ IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` OLD.`id` 6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER ｕｐｄａｔｅ ON `confluence`.`sbtest3` FOR EACH ROW ｒｅｐｌａｃｅ INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER ｉｎｓｅｒｔ ON `confluence`.`sbtest3` FOR EACH ROW ｒｅｐｌａｃｅ INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)并且copy操作是： 6165 Query ｉｎｓｅｒｔ LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) ｓｅｌｅｃｔ `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ‘4692805‘)) AND ((`id`