MySQL修改表结构原理
MySQL修改表结构原理
1 原理分析
互联网需求,时刻都在变,需求变动的同时,也需要对数据库表结构进行修改,比如加个字段,新加个索引等等。
mysql在线ddl(加字段、加索引等修改表结构之类的操作)过程如下:
- 对表加锁(排他锁)(表此时只读)
- 在这个过程中会锁表。造成当前操作的表无法写入数据,影响用户使用。由于需要复制原表的数据到中间表,所以表的数据量越大,等待的时候越长,卡死在那里(用户被拒绝执行update和insert操作,表现就是延迟了一直在等待)。
- 复制原表物理结构
- 修改表的物理结构
- 把原表数据导入中间表中,数据同步完后,锁定中间表,并删除原表
- 平时进行修改表的结构,更改字段,新增字段,更改字段名称一般都是通过
ALTER TABLE TABLENAE
语法进行修改的。对于测试库,在线小表或者并发访问不是很大的情况是OK。但是如果是在线大表。那就很麻烦。由于表数据量大,复制表需要比较长的时间,在这个时间段里面,表是被加了锁的(写锁),加写锁时其他用户只能select表不能update、insert表。表数据量越大,耗时越长。
- 平时进行修改表的结构,更改字段,新增字段,更改字段名称一般都是通过
- rename中间表为原表
- 刷新数据字典,并释放锁、
注意事项:
- 如果表很大,该表的时间会很长
- 该表会将原表锁住(排他锁),也就是改表期间,该表只提供读的服务(select),不提供写的服务(insert、update、delete)
- 如果该表需要对外提供写的服务,那么需要考虑是否存在风险
- 如果是MySQL集群,那么改表一般在主库上修改,如果表很大,改表期间,主从复制可能会有较大的延迟
2 解决思路
(1)滚动更新
- 如果MySQL是集群部署,则先停掉一部分机器,让服务请求到能提供服务的机器上,MySQL停掉服务之后,再进行改表操作,这样风险最低,但是耗时较长。
- 改完表结构之后,最后在发代码到线上
(2)低峰期改表
让MySQL停服这样代价有点高,如果某张表对外只提供select
的操作,也就是没有写操作,那么可以在服务低峰期进行改表操作。
Reference
- mysql在线修改表结构大数据表的风险与解决办法归纳
- MySQL OSC(在线更改表结构)原理