?

innodb優(yōu)化后，29小時(shí)入庫(kù)1300萬(wàn)條數(shù)據(jù)

參考：http://blog.51yip.com/mysql/1369.html

?

對(duì)于Myisam類型的表，可以通過(guò)以下方式快速的導(dǎo)入大量的數(shù)據(jù)：

ALTER TABLE tblname DISABLE KEYS;
? ? loading the data
? ? ALTER TABLE tblname ENABLE KEYS;
這兩個(gè)命令用來(lái)打開(kāi)或者關(guān)閉Myisam表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個(gè)非空的Myisam表時(shí)，通過(guò)設(shè)置這兩個(gè)命令，可以提高導(dǎo)入的效率。對(duì)于導(dǎo)入大量數(shù)據(jù)到一個(gè)空的Myisam表，默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的，所以不用進(jìn)行設(shè)置。

?

?

DISABLE KEYS 和ENABLE KEYS 用來(lái)打開(kāi)或關(guān)閉MyISAM表非唯一索引的更新，可以提高速度，注意：對(duì)InnoDB表無(wú)效。

?

加載大量數(shù)據(jù)時(shí)，關(guān)閉非唯一索引，取消唯一性檢查，以及取消自動(dòng)提交以提高插入速度

set unique_checks=0 alter table stu disable keys set autocommit=0 load load infile........ alter table stu enable keys set unique_checks=1 set autocommit =1

?

?

沒(méi)有使用打開(kāi)或關(guān)閉MyISAM表非唯一索引：
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2 fields terminated by “,”;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0

使用打開(kāi)或關(guān)閉MyISAM表非唯一索引：
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected (0.0 sec)
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2;
Query OK,529056 rows affected (6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2 enable keys;
Query OK,0 rows affected (12.25 sec)
以上對(duì)MyISAM表的數(shù)據(jù)導(dǎo)入，但對(duì)于InnoDB表并不能提高導(dǎo)入數(shù)據(jù)的效率

?

?

對(duì)于Innodb類型的表，我們有以下幾種方式可以提高導(dǎo)入的效率：

?

• 因?yàn)镮nnodb類型的表是按照主鍵的順序保存的，所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排列，可以有效的提高導(dǎo)入數(shù)據(jù)的效率。如果Innodb表沒(méi)有主鍵，那么系統(tǒng)會(huì)默認(rèn)創(chuàng)建一個(gè)內(nèi)部列作為主鍵，所以如果可以給表創(chuàng)建一個(gè)主鍵，將可以利用這個(gè)優(yōu)勢(shì)提高導(dǎo)入數(shù)據(jù)的效率。
• 在導(dǎo)入數(shù)據(jù)前執(zhí)行SET UNIQUE_CHECKS=0，關(guān)閉唯一性校驗(yàn)，在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1，恢復(fù)唯一性校驗(yàn)，可以提高導(dǎo)入的效率。
• 如果應(yīng)用使用自動(dòng)提交的方式，建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0，關(guān)閉自動(dòng)提交，導(dǎo)入結(jié)束后再執(zhí)行SET AUTOCOMMIT=1，打開(kāi)自動(dòng)提交，也可以提高導(dǎo)入的效率。

因?yàn)镮nnoDB表的按照主鍵順序保存的，所以將導(dǎo)入的數(shù)據(jù)主鍵的順序排列，可以有效地提高導(dǎo)入數(shù)據(jù)的效率。

使用test3.txt文本是按表film_test4主鍵存儲(chǔ)順序保存的
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
使用test3.txt沒(méi)有任何順序的文本（效率慢了1.12倍）
mysql> load data infile ‘/home/mysql/film_test4.txt’into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

?

關(guān)閉唯一性效驗(yàn)可以提高導(dǎo)入效率

?

在導(dǎo)入數(shù)據(jù)前先執(zhí)行set unique_checks=0，關(guān)閉唯一性效驗(yàn)，在導(dǎo)入結(jié)束后執(zhí)行set unique_checks=1，恢復(fù)唯一性效驗(yàn)，可以提高導(dǎo)入效率。

?

當(dāng)unique_checks=1時(shí)
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
當(dāng)unique_checks=0時(shí)
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

?

關(guān)閉自動(dòng)提交可以提高導(dǎo)入效率

在導(dǎo)入數(shù)據(jù)前先執(zhí)行set autocommit=0，關(guān)閉自動(dòng)提交事務(wù)，在導(dǎo)入結(jié)束后執(zhí)行set autocommit=1，恢復(fù)自動(dòng)提交，可以提高導(dǎo)入效率。

當(dāng)autocommit=1時(shí)
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
當(dāng)autocommit=0時(shí)
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

?

知識(shí)點(diǎn)的補(bǔ)充：20160318

這里主要是針對(duì)innodb的優(yōu)化

?init_connect='SET?autocommit=0'????//關(guān)閉自動(dòng)提交，這個(gè)對(duì)于innodb來(lái)說(shuō)，很重要??

?innodb-file-per-table=1????????????//使用獨(dú)立表空間??

?innodb-open-file=500???????????????//打開(kāi)最大文件數(shù)據(jù)是500，默認(rèn)是300??

?innodb_log_file_size=512M??????????//log文件大小??

?innodb_log_buffer_size=8M??????????//緩沖日志數(shù)據(jù)的緩沖區(qū)的大小??

?innodb_flush_log_at_trx_commit=0???//提交數(shù)據(jù)等級(jí)0是最快，但是有可能會(huì)丟數(shù)據(jù)??

?innodb_buffer_pool_size=5G?????????//緩沖池大小，我把它設(shè)置內(nèi)在的65%??

?innodb_log_files_in_group=3????????//日志文件總數(shù)??

?innodb_file_io_threads=8???????????//根cpu核數(shù)是一樣，讀寫文件進(jìn)程數(shù)??

?event_scheduler=1??????????????????//開(kāi)啟動(dòng)mysql?event??

??

//添加一個(gè)mysql?event??

CREATE?EVENT?`commit_event`?ON?SCHEDULE?EVERY?5?MINUTE?STARTS?'2012-01-04?19:06:26'?ON?

COMPLETION?NOT?PRESERVE?ENABLE?DO?COMMit?

?

1,自動(dòng)提交對(duì)innodb的影響非常大的，這個(gè)我做過(guò)測(cè)試，請(qǐng)參考,mysql autocommit對(duì)myisam,innodb的性能影響.

2,innodb_flush_log_at_trx_commit我把它設(shè)置成0，我只要求速度最快，最是統(tǒng)計(jì)推廣的彈窗，

這些數(shù)據(jù)只是為了我們后期統(tǒng)計(jì)和分析用的，沒(méi)有太大的價(jià)值。如果數(shù)據(jù)很重要就不要設(shè)置成0了。

0代表日志只大約每秒寫入日志文件并且日志文件刷新到磁盤.
1InnoDB會(huì)在每次提交后刷新(fsync)事務(wù)日志到磁盤上
2代表日志寫入日志文件在每次提交后,但是日志文件只有大約每秒才會(huì)刷新到磁盤上.

3,還有一點(diǎn)就是我用了mysql event功能，根linux的crontab差不多。

這里對(duì)：mysql event的補(bǔ)充?? --- http://www.cnblogs.com/end/archive/2011/04/21/2023725.html

總結(jié)

以上是生活随笔為你收集整理的Mysql中Innodb大量插入数据时SQL语句的优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。