MySQL-mysql中如何提高大表之间复制效率

Question

目前有个日志总表logs_a，一周大概产生500W左右日志，按天分区，现在想把这个表中数据复制到另一表logs_b还是按天分，两表的结构不同。
我们使用下面这种方式做的拷贝：

Insert into logs_b(…,…,…)
Select …,…,… from logs_a
Where log_date >= to_date(‘20120229’, ‘yyyymmdd’);

Answer 1

我觉得你可以把数据库文件整体拷贝过去，然后在数据库访问量不是很大的时候，分批把不要的数据删除。

Answer 2

看到你问题，感觉你的瓶颈应该是在执行时间太长，又不方便终止，建议你可以按照 MySQL-mysql中如何提高大表之间复制效率 的方法，这个可以解决你执行无法终止的问题，如果需求可以接受，这个到是一个很好的办法

下面我主要说下插入表可以增加速率的分享：

在myisam engine下

尽量使用 insert into table_name values (...), (.....),(.....) 这样形式插入数据，避免使用 inset into table_name values (); inset into table_name values (); inset into table_name values ();

2 增加bulk_insert_buffer_size(默认8M)

3 如果是非空表，使用 alter table table_name disable keys，然后 load data infile,导入完数据在执行：
alter table table_name enable keys. 如果是空表，就不需要这个操作，因为myisam表在空表中导入数据时，是先导入数据然后建立indexs。

4 在插入数据时考虑使用：insert delayed....这样操作实际mysql把insert操作放到队列里面，进行相对集中的插入，速度更快。

使用load data infile 比使用insert 操作快近20倍，尽量使用此操作。

在innodb engine下

1.导入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1.
2.导入数据之前执行set foreign_key_checks=0来禁止对外键的检查，数据导入完成之后再执行set foreign_key_checks=1.
3.导入数据之前执行set autocommit=0禁止自动事务的自动提交，数据导入完成之后，执行set autocommit=1 恢复自动提交操作。
使用innodb engine的表，物理存储都是按PK的顺序存的。不能使用类似于myisam一样disable keys.

硬件上提高磁盘的I/0对插入速度很有好处（所以如果进行大数据量的导入导出工作，尽量在比较NB的硬件上进行，能缩减完成的时间，已经防止出现问题）。

Answer 3

建议先select into outfile然后再load data infile

select a,b,c from oldtable into outfile 'a.txt';

load data INFILE 'a.txt' into table newtable (a,b,c ) ;

当从一个文本文件装载一个表时，使用load data infile。这通常比使用很多insert语句快几十倍。

load data infile 并不是先把数据 select 进来，load data infile 之前，可以先用 myisamchk --keys-used=0 -rq /path/to/db/tbl_name 从表中取消所有索引的使用。当导入完后 用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树，并且它更快，因为避免了大量磁盘搜索。结果索引树也被完美地平衡。

Answer 4

我觉得你可以分页导入啊，比如Insert into logs_b(…,…,…) Select …,…,… from logs_a Where log_date >= to_date(‘20120229’, ‘yyyymmdd’) limit 0,1000;