mysql中如何提高大表之間復制效率

mysql中如何提高大表之間復制效率   假如目前有個日志總表logs_a，一周大概產生500W左右日志，按天分區，現在想把這個表中數據復制到另一表logs_b還是按天分，兩表的結構不同。 我們使用下面這種方式做的拷貝：

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

 

現在問題是，這個sql要運行N久，也無法終止，問下有什麼好的方法能夠快速實現表數據復制沒？  

辦法一：

分頁導入，比如Insert into logs_b(…,…,…) Select …,…,… from logs_a Where log_date >= to_date(‘20120229’, ‘yyyymmdd’) limit 0,1000;

辦法二：

先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重新創建索引。這將在寫入磁盤前在內存中創建索引樹，並且它更快，因為避免了大量磁盤搜索。結果索引樹也被完美地平衡   辦法三：   在myisam engine下   1 盡量使用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的硬件上進行，能縮減完成的時間，已經防止出現問題）