mysql 插入資料加快

現在運作情況：10秒大概10000資料～～～～～～

1、INSERT裝載資料比LOAD DATA INFILE要慢得多，即使是使用上述的政策。

2、加鎖

　　如果不使用鎖定，2、3和4将在1和5前完成。如果使用鎖定，2、3和4将可能不在1或5前完成，但是整體時間應該快大約40%。

　　INSERT、UPDATE和DELETE操作在MySQL中是很快的，通過為在一行中多于大約5次連續不斷地插入或更新的操作加鎖，可以獲得更好的整體性能。如果在一行中進行多次插入，可以執行LOCK TABLES，随後立即執行UNLOCK TABLES(大約每1000行)以允許其它的線程通路表。這也會獲得好的性能。

• LOCK TABLES a WRITE;
• INSERT INTO a VALUES (1,23),(2,34),(4,33);
• INSERT INTO a VALUES (8,26),(6,29);
• UNLOCK TABLES;

3、通過以上對比發現對于插入大量資料時可以使用MyISAM存儲引擎，如果再需要修改MySQL存儲

　　引擎可以使用指令：

　　Sql代碼

　　ALTER TABLE t ENGINE = MYISAM;

4、  關閉MYSQL日志

執行：

/usr/local/mysql/bin/mysql -u root -p

輸入密碼，登陸到mysql，然後執行

reset master;

退出後執行：

vi /etc/my.cnf

查找

log-bin=mysql-bin binlog_format=mixed

再這兩行前面加上#，将其注釋掉，再執行重新開機mysql指令

這樣就關閉了mysql日志了

/etc/init.d/mysql restart

5、  max_allowed_packet 設定大一點｛windows--my.ini｝

6、幹掉索引再建

7、如果你正向一個非空表添加資料，可以調節bulk_insert_buffer_size變量，使資料插入更快

mysql資料庫插入速度和讀取速度的調整記錄

由于項目變态需求；需要在一個比較短時間段急劇增加資料庫記錄（兩三天内，由于0增加至4億）。在整個過程調優過程非常艱辛

（1）提高資料庫插入性能中心思想：盡量将資料一次性寫入到Data File和減少資料庫的checkpoint 操作。這次修改了下面四個配置項：

1）将 innodb_flush_log_at_trx_commit 配置設定為0；按過往經驗設定為0，插入速度會有很大提高。

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.

1：the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file

2：the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it

2）将 innodb_autoextend_increment 配置由于預設8M 調整到 128M

此配置項作用主要是當tablespace 空間已經滿了後，需要MySQL系統需要自動擴充多少空間，每次tablespace 擴充都會讓各個SQL 處于等待狀态。增加自動擴充Size可以減少tablespace自動擴充次數。

3）将 innodb_log_buffer_size 配置由于預設1M 調整到 16M

此配置項作用設定innodb 資料庫引擎寫日志緩存區；将此緩存段增大可以減少資料庫寫資料檔案次數。

4）将 innodb_log_file_size 配置由于預設 8M 調整到 128M

此配置項作用設定innodb 資料庫引擎UNDO日志的大小；進而減少資料庫checkpoint操作。

經過以上調整，系統插入速度由于原來10分鐘幾萬條提升至1秒1W左右；注：以上參數調整，需要根據不同機器來進行實際調整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要謹慎調整；因為涉及MySQL本身的容災處理。

（2）提升資料庫讀取速度，重資料庫層面上讀取速度提升主要由于幾點：簡化SQL、加索引和分區； 經過檢查程式SQL已經是最簡單，查詢條件上已經增加索引。我們隻能用武器：表分區。

資料庫 MySQL分區前準備：在MySQL中，表空間就是存儲資料和索引的資料檔案。

将S11資料庫由于同享tablespace 修改為支援多個tablespace;

将wb_user_info_sina 和 wb_user_info_tx 兩個表修改為各自獨立表空間；（Sina：1700W資料，2.6G 大資料檔案，Tencent 1400W，2.3G大資料檔案）；

分區操作：

将現有的主鍵和索引先删除

重制建立id,uid 的聯合主鍵

再以 uid 為鍵值進行分區。這時候到/var/data/mysql 檢視資料檔案，可以看到兩個大表各自獨立表空間已經分割成若幹個較少獨立分區空間。（這時候若以uid 為檢索條件進行查詢，并不提升速度；因為鍵值隻是安排資料存儲的分區并不會建立分區索引。我非常郁悶這點比Oracle 差得不是一點半點。）

再以 uid 字段上進行建立索引。再次到/var/data/mysql 檔案夾檢視資料檔案，非常郁悶地發現各個分區Size竟然大了。MySQL還是老樣子将索引與資料存儲在同一個tablespace裡面。若能index 與 資料分離能夠更加好管理。

經過以上調整，暫時沒能展現出系統讀取速度提升；基本都是在 2~3秒完成5K資料更新。

MySQL資料庫插入速度調整補充資料：

MySQL 從最開始的時候 1000條/分鐘的插入速度調高至 10000條/秒。 相信大家都已經等急了相關介紹，下面我做調優時候的整個過程。提高資料庫插入性能中心思想：

1、盡量使資料庫一次性寫入Data File

2、減少資料庫的checkpoint 操作

3、程式上盡量緩沖資料，進行批量式插入與送出

4、減少系統的IO沖突

根據以上四點内容，作為一個業餘DBA對MySQL服務進行了下面調整：

修改負責收錄記錄MySQL伺服器配置，提升MySQL整體寫速度；具體為下面三個資料庫變量值：innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size；此三個變量預設值分别為 5M、8M、8M，根據伺服器記憶體大小與具體使用情況，将此三隻分别修改為：128M、16M、128M。同時，也将原來2個 Log File 變更為 8 個Log File。此次修改主要滿足第一和第二點，如：增加innodb_autoextend_increment就是為了避免由于頻繁自動擴充Data File而導緻 MySQL 的checkpoint 操作；

将大表轉變為獨立表空并且進行分區，然後将不同分區下挂在多個不同硬碟陣列中。

完成了以上修改操作後；我看到下面幸福結果：

擷取測試結果：

Query OK, 2500000 rows affected (4 min 4.85 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

Query OK, 2500000 rows affected (4 min 58.89 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

Query OK, 2500000 rows affected (5 min 25.91 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

Query OK, 2500000 rows affected (5 min 22.32 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

最後表的資料量：

+------------+

| count(*) |

+------------+

| 10000000|

+------------+

從上面結果來看，資料量增加會對插入性能有一定影響。不過，整體速度還是非常面議。一天不到時間，就可以完成4億資料正常處理。預計資料庫瓶頸已經被巧妙解決，結果變成程式“猿”苦逼地向我埋怨，大哥不用這麼狠啊。

詳細出處參考：http://www.jb51.net/article/30920.htm

在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操作放到隊列裡面，進行相對集中的插入，速度更快。

5. 使用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的硬體上進行，能縮減完成的時間，已經防止出現問題）。