修改主键也就意味着要重建所有的普通索引。删除二级索引更简单,修改InnoDB系统表信息和数据字典,标记该所以不存在,标记所占用的表空间可以被新索引或数据行重新利用。
1.3 在线DDL的限制在alter table时,如果涉及到table copy操作,要确保datadir目录有足够的磁盘空间,能够放的下整张表,因为拷贝表的的操作是直接在数据目录下进行的。
添加索引无需table copy,但要确保tmpdir目录足够存下索引一列的数据(如果是组合索引,当前临时排序文件一合并到原表上就会删除)
在主从环境下,主库执行alter命令在完成之前是不会进入binlog记录事件,如果允许dml操作则不影响记录时间,所以期间不会导致延迟。然而,由于从库是单个SQL Thread按顺序应用relay log,轮到ALTER语句时直到执行完才能下一条,所以从库会在master ddl完成后开始产生延迟。(pt-osc可以控制延迟时间,所以这种场景下它更合适)
During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT … FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.
在执行一个允许并发DML在线 ALTER TABLE时,结束之前这个线程会应用 online log 记录的增量修改,而这些修改是其它thread里产生的,所以有可能会遇到重复键值错误(ERROR 1062 (23000): Duplicate entry)。
涉及到table copy时,目前还没有机制限制暂停ddl,或者限制IO阀值
在MySQL 5.7.6开始能够通过 performance_schema 观察alter table的进度
一般来说,建议把多个alter语句合并在一起进行,避免多次table rebuild带来的消耗。但是也要注意分组,比如需要copy table和只需inplace就能完成的,应该分两个alter语句。
如果DDL执行时间很长,期间又产生了大量的dml操作,以至于超过了innodb_online_alter_log_max_size变量所指定的大小,会引起DB_ONLINE_LOG_TOO_BIG 错误。默认为 128M,特别对于需要拷贝大表的alter操作,考虑临时加大该值,以此获得更大的日志缓存空间
执行完 ALTER TABLE 之后,最好 ANALYZE TABLE tb1 去更新索引统计信息
2. 实现过程online ddl主要包括3个阶段,prepare阶段,ddl执行阶段,commit阶段,rebuild方式比no-rebuild方式实质多了一个ddl执行阶段,prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。
Prepare阶段:
创建新的临时frm文件(与InnoDB无关)
持有EXCLUSIVE-MDL锁,禁止读写
根据alter类型,确定执行方式(copy,online-rebuild,online-norebuild)
假如是Add Index,则选择online-norebuild即INPLACE方式
更新数据字典的内存对象
分配row_log对象记录增量(仅rebuild类型需要)
生成新的临时ibd文件(仅rebuild类型需要)
ddl执行阶段:
降级EXCLUSIVE-MDL锁,允许读写
扫描old_table的聚集索引每一条记录rec
遍历新表的聚集索引和二级索引,逐一处理
根据rec构造对应的索引项
将构造索引项插入sort_buffer块排序
将sort_buffer块更新到新的索引上
记录ddl执行过程中产生的增量(仅rebuild类型需要)
重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
重放row_log间产生dml操作append到row_log最后一个Block
commit阶段:
当前Block为row_log最后一个时,禁止读写,升级到EXCLUSIVE-MDL锁
重做row_log中最后一部分增量
更新innodb的数据字典表
提交事务(刷事务的redo日志)
修改统计信息
rename临时idb文件,frm文件
变更完成
这有一直导图挺直观的: 。
添加列 时由于需要copy table,row_log会重放到新表上(临时ibd文件),直到最后一个block,锁住原表禁止更新。
row_log记录了ddl变更过程中新产生的dml操作,并在ddl执行的最后将其应用到新的表中,保证数据完整性
3. 对比实验 3.1 添加二级索引