mysql千万级数据表结构修改

àì夳堔傛蜴生んèń 2022-07-18 04:17 301阅读 0赞

当需要对表进行ddl操作如加索引、增删列时，数据量小时直接在线修改表结构影响不大  
当表达到百万、千万数据就不能直接在线修改表结构  
  
下面是具体的过程：  
1、备份数据  
select \* from ih\_order into outfile '/bak/order.txt';  
mysql> select \* from ih\_order into outfile 'D:/bak/order.txt';  
Query OK, 10001000 rows affected (1 min 30.18 sec)  
  
2、复制原表物理结构  
create table ih\_order\_bak like ih\_order;  
  
3、执行表结构修改，这里是增加索引  
alter table  \`ih\_order\_bak\` add index (  \`consulate\` );  
  
4、把原表数据导入新表  
mysql> insert into ih\_order\_bak select \* from ih\_order;  
Query OK, 10001000 rows affected (6 min 10.30 sec)  
Records: 10001000  Duplicates: 0  Warnings: 0  
  
5、删除原表  
drop table ih\_order;  
  
6、新表重命名原表  
rename table ih\_order\_bak to ih\_order;  
  
  
注意：  
1、这种方法会影响业务运行，建议使用在线ddl工具：pt-online-schema-change，且在访问量少时进行  
2、大多数的alter table操作都会涉及lock-->copy to new table-->rename-->unlock的过程，锁表时间会很长,而且alter table 的process不可被kill,一旦执行就不可回退。  
在mysql5.5和之前版本，在运行的生产环境对大表（超过数百万纪录）执行alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。  
从mysql5.6开始，Online DDL特性被引进。增强了很多种类的Alter Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本，我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。  
但是即使在mysql5.6，仍然有一些alter操作（增加/删除列，增加/删除主键,改变数据类型等）需要重建