怎麼提高Mysql執行sql匯入的速度

怎麼提高Mysql執行sql匯入的速度

 

 

1、如果mysql的data資料很少，記憶體足夠大，可以把data防止到記憶體盤中。

linux如下設定記憶體盤：

mount -t ramfs none /ram

預設使用記憶體一半

如果記憶體不夠大，系統有多個硬碟，則把mysql應用程式和data目錄分開到不同硬碟上。

2、mysql的表設定為myiasm，比同等條件下的innodb能快20倍以上

3、匯入完成以後才建立資料庫索引

4、匯入完成以後根據需要轉換為其他engine，比如innodb

5、多條資料插入一個表，可以使用多記錄方式：

insert into tablename values(’xxx’,'xxx’),(’yyy’,'yyy’)…;

6、如果多個mysql執行匯入，可以使用delayed

insert delayed into tablename values(’sss’,’ssss’);

7、大檔案sql檔案可以用split分成多份再導

8、同等條件下，redhat比ubuntu強很多（幾乎肯定）

9 效能調整技巧(Performance tuning tips)

1. 如果 Unix top 或 Windows 工作管理員(Task Manager) 顯示服務的 CPU 佔用率小於 70%，(shows that the CPU usage percentage with your workload is less than 70 %,）你的系統瓶頸可能在磁碟讀寫上。或許你提交了大量的事務，或者是緩衝池(buffer pool)太小了。將緩衝池設大點會有所協助，但一定要注意不能大於實體記憶體的 80%。

2. 在一個事務中包含幾個修改。如果事務對資料庫進行了修改，那麼在這個事務提交時 InnoDB 必須重新整理日誌到磁碟上。因為硬碟的旋轉速度通常至多為 167 轉/秒，那麼只要磁碟不欺騙作業系統，提交的事務數目限止也同樣為 167 次/秒·使用者。

3. 如果掉失最近的幾個事務無所謂的話，可以在 my.cnf 檔案中將參數 innodb_flush_log_at_trx_commit 設定為 0。InnoDB 無論如何總是嘗試一秒重新整理(flush)一次日誌，儘管重新整理並不能得到保證。

4. 將記錄檔(log files)設大一點，使記錄檔的總和正好與緩衝池(buffer pool)一樣大。當 InnoDB 用光記錄檔的空間時，它不得不在一個時間點上將緩衝池內修改過的內容寫到磁碟上。 小的記錄檔可能引起不必要的磁碟寫操作。但是大的記錄檔的缺點就是在資料恢複時將佔用較長的時間。

5. 同樣 log buffer 盡量設大點，比如說 8 MB。
6. 如果要儲存變長的字串或欄位可能會包含大量的 NULLs，請使用 VARCHAR 型欄位代替 CHAR 。一個 CHAR(n) 欄位總是使用 n bytes 來儲存資料，即使這個字串很短或是一個 NULL 值。較小的表更加適合緩衝池同時能夠減少磁碟 I/O 。

7. (適合從 3.23.41 以上版本) 在某些版本的 Linux 和 Unixes 中，使用 Unix fsync 或其它類似的方法將檔案重新整理到磁碟是異常地慢的。InnoDB 預設的方法就是 fsync 。如果你對資料庫系統的磁碟寫效能不能感到滿意，你可以嘗試在 my.cnf 中將 innodb_flush_method 設定為 O_DSYNC，儘管 O_DSYNC 選項在多數的系統上看起來比較慢。

8. 在向 InnoDB 匯入資料時，請確認 MySQL 沒有開啟 autocommit=1 。否則每個插入語句都要將 log 重新整理到磁碟。在你的 SQL 匯入檔案的第一行加入

set autocommit=0;

並在最後一行加入

commit;

如果使用 mysqldump 選項 –opt，你將會得到一個快速匯入 InnoDB 表的轉儲(dump)檔案，甚至可以不再使用上面所提的 set autocommit=0; … commit; 。

9. 小心 insert 集全的大復原(roolback)：在插入時 InnoDB 使用插入緩衝來減少磁碟 I/O，但在相應的復原中卻沒有使用這樣的機制。一個 disk-bound rollback 可能會花費相應插入時間的 30 倍。如果發生一個失控的復原，你可以查看第 6.1 章節的技巧來停止它。

10. 同樣也要小心一個大的 disk-bound 的操作。使用 Drop TABLE 或 TRUNCATE (從 MySQL-4.0 以上) 來清空一個表，而不要使用 Delete FROM yourtable。

11. 如果需要插入大量記錄行可以使用多行(multi-line)的 Insert 來減少用戶端與伺服器端的通訊開銷：

Insert INTO yourtable VALUES (1, 2), (5, 5);

這個技巧對插入任何錶均有效，而不僅僅是 InnoDB。

12. 如果在輔鍵上有 UNIQUE 約束，從 3.23.52 和 4.0.3 開始，可以通過在一個匯入會話中將唯一鍵檢查(uniqueness check)關閉來提高資料匯入速度：

SET UNIQUE_CHECKS=0;

一個大的表匯入這將減少大量的磁碟 I/O，因為這時 InnoDB 可能使用自身的插入緩衝來分批地記錄輔助索引。

13. 如果在表中有一個子 FOREIGN KEY 約束，從 3.23.52 和 4.0.3 開始，可以通過在一個匯入會話中將外鍵檢查(foreign key check)關閉來提高資料匯入速度：

SET FOREIGN_KEY_CHECKS=0;

對一個大的表匯入這將減少大量的磁碟 I/O。

轉載聲明：本文轉自http://hi.baidu.com/ssnh1379/blog/item/26733a1e12468103314e1579.html

====================================================================