Mysql 批量写入数据 性能优化
2019獨角獸企業(yè)重金招聘Python工程師標準>>>
測試環(huán)境
配置直接影響執(zhí)行速度，先上一下測試機配置：
- cpu i7 5500U（低電壓傷不起，以后再也不買低電壓的U了）
- 內(nèi)存 8G ddr3 1600
- php 7.1
- mysql 5.5.40
- 開發(fā)框架 CodeIgniter 3.1.2
?
影響寫入效率的因素都有什么？
-
數(shù)據(jù)庫引擎
?????????開發(fā)中常用的數(shù)據(jù)庫引擎 MyISAM，InnoDB 這兩種，其他的數(shù)據(jù)庫引擎我在開發(fā)中還沒用到，所以不在這里測試了。
? ? ? ? ?先看一下庫表結(jié)構(gòu) ：
?????????test庫下有兩張表：分別為上面提的兩種引擎：
?????
?????????每張表結(jié)構(gòu)如下（一個自增id，一個varchar類型待插入字段）：
????????? 缺省狀態(tài)下對兩表插入20w數(shù)據(jù)看一下效率：
????? ? ? PHP代碼如下：? ? ? ? ?
/*** * 測試插入效率* * @return void* */public function insertTest(){set_time_limit(0); //防止超300s 500錯誤$t1 = microtime(true);//隨機插入num條for ($i=1; $i<=200000; $i++){$result = $this->db->insert('myisam', ['value' => uniqid().$i]);}//程序運行時間$t2 = microtime(true);echo '耗時：'.round($t2-$t1,3).'秒<br>';echo '內(nèi)存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>";}????執(zhí)行結(jié)果：
????
????20w 數(shù)據(jù) Myisam要 接近3分鐘了。
?
? ? 看一下InnoDb缺省狀態(tài)下執(zhí)行時間：
? ? 插入1w條數(shù)據(jù) 用了 6分49秒，沒辦法等下去了， 按照這個數(shù)據(jù)量推測 6分49 * 20 = ？？？
? ? 后期由于數(shù)據(jù)量增多，也會影響插入性能，所以InnoDb默認狀態(tài)插入20w單字段數(shù)據(jù)要2小時以上，無法 ? ? 忍受。
-
? 業(yè)務(wù)邏輯
?? ? ?顯然上面的業(yè)務(wù)邏輯是有問題的，每條數(shù)據(jù)單次插入，增加了mysql的開銷，每次插入數(shù)據(jù)都要重新連 ? ? ? ? 接一下mysql，肯定是相當(dāng)浪費資源了。所以CI提供了 insert_batch()，批量寫入數(shù)據(jù)。Thinkphp3.2 ? ? ? ? 也有addAll() 這樣的方法來支持。其他框架應(yīng)該都有！
? ? ? 原理很簡單就是把二維數(shù)組，拼接為sql
? ? ? 將單條Sql如下：
????? $sql = "INSERT INTO TEST (value) VALUES ('helloworld1')";
? ? ? $sql = "INSERT INTO TEST (value) VALUES ('helloworld2')";
? ? ? 拼接為：
? ? ? $sql = "INSERT INTO TEST (value) VALUES ('helloworld1'), ('helloworld2')";
? ? ? 很明顯批量插入速度要快很多。
? ? ?
?????還是20w數(shù)據(jù)，MyISAM 下批量查詢速度多快？（已有數(shù)據(jù)會影響插入效率，已清空 myisam表）
? ? ?還是上代碼
/*** * 測試批量插入效率* * @return void* @author lidalin.se@gmail.com* */public function insertTest(){set_time_limit(0); //防止超300s 500錯誤$t1 = microtime(true);//隨機插入num條for ($i=1; $i<=200000; $i++){$data[$i] = ['value' => uniqid().$i];}//程序運行時間$t2 = microtime(true);echo '循環(huán)耗時：'.round($t2-$t1,3).'秒<br>';$this->db->insert_batch('myisam', $data); //批量插入$t3 = microtime(true);echo '插入耗時：'.round($t3-$t2,3).'秒<br>';echo '內(nèi)存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>";}????執(zhí)行結(jié)果：
????????
?????比起之前的167秒的單條插入速度快了 5 倍。內(nèi)存消耗增加1.5倍左右，內(nèi)存換時間，可取~~~
?
? ? InnoDB 會是什么速度呢？
? ? 執(zhí)行結(jié)果：
????????
? ? 這次終于執(zhí)行完了，而且速度很快。和之前的兩個小時比，效率也提升了N倍。
?
-
修改配置參數(shù)提升性能：
????????InnoDB 引擎 真的這么慢？這么low??????
????????答案顯然是：NO
? ? ? ? InnoDB寫入之所以這么慢的一個原因是：
innodb_flush_log_at_trx_commit?? ? ? ?參數(shù)配置的問題
????? ? 如下圖默認的值：
????? ? 關(guān)于值請查閱mysql文檔：
????????https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html?
????????
???????當(dāng)innodb_flush_log_at_trx_commit=0時, log buffer將每秒一次地寫入log file, 并且log file的flush(刷新 ? ? ? ? ?到disk)操作同時進行. 此時, 事務(wù)提交是不會主動觸發(fā)寫入磁盤的操作.
? ? ? ?當(dāng)innodb_flush_log_at_trx_commit=1時(默認), 每次事務(wù)提交時, MySQL會把log buffer的數(shù)據(jù)寫入log f ? ? ? ? ?ile, 并且將log file flush(刷新到disk)中去.
? ? ? ?當(dāng)innodb_flush_log_at_trx_commit=2時, 每次事務(wù)提交時, MySQL會把log buffer的數(shù)據(jù)寫入log file, 但 ? ? ? ? ?不會主動觸發(fā)flush(刷新到disk)操作同時進行. 然而, MySQL會每秒執(zhí)行一次flush(刷新到disk)操作.
????????
????????把值設(shè)置為2，再試一下：
????????
????? ? ?速度又提升了 3倍，和 MyISAM幾乎相同。
? ? ??? ?所以以后說InnoDB寫入速度慢，可能是配置問題
-
還可以優(yōu)化？
????? ? 還有什么可以優(yōu)化？
? ??? ? 由于我們使用了 框架的insert_batch，看一下CI源碼：
/*** The "set_insert_batch" function. Allows key/value pairs to be set for batch inserts** @param mixed* @param string* @param bool* @return CI_DB_query_builder*/public function set_insert_batch($key, $value = '', $escape = NULL){$key = $this->_object_to_array_batch($key);if ( ! is_array($key)){$key = array($key => $value);}is_bool($escape) OR $escape = $this->_protect_identifiers;$keys = array_keys($this->_object_to_array(current($key)));sort($keys);foreach ($key as $row){$row = $this->_object_to_array($row);if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0){// batch function above returns an error on an empty array$this->qb_set[] = array();return;}ksort($row); // puts $row in the same order as our keysif ($escape !== FALSE){$clean = array();foreach ($row as $value){$clean[] = $this->escape($value);}$row = $clean;}$this->qb_set[] = '('.implode(',', $row).')';}foreach ($keys as $k){$this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape);}return $this;}我們傳入的數(shù)據(jù)，方法會再循環(huán)，判斷。所以建議語句自己拼接
代碼修改如下：
/*** * 測試插入效率* * @return void* @author lidalin.se@gmail.com* */public function insertTest(){set_time_limit(0); //防止超300s 500錯誤$t1 = microtime(true);$sql = "insert into innodb (value) VALUES";//隨機插入num條for ($i=1; $i<=200000; $i++){$val = uniqid().$i;$sql .= "('{$val}'),";}$sql = substr($sql,0,-1);//程序運行時間$t2 = microtime(true);echo '循環(huán)耗時：'.round($t2-$t1,3).'秒<br>';$this->db->query($sql); //批量插入$t3 = microtime(true);echo '插入耗時：'.round($t3-$t2,3).'秒<br>';echo '內(nèi)存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>";}執(zhí)行結(jié)果：
20W條數(shù)據(jù) ?InnoDB ? 循環(huán)1.6秒，插入1.2秒。速度是不是很爽了。。
拼接語句可能會報錯
設(shè)置一下
max_allowed_packet = 500M
允許mysql 接受數(shù)據(jù)包大小。
?
?歡迎轉(zhuǎn)載：
轉(zhuǎn)載請注明：https://my.oschina.net/famoustone/blog/856736
轉(zhuǎn)載于:https://my.oschina.net/famoustone/blog/856736
總結(jié)
以上是生活随笔為你收集整理的Mysql 批量写入数据 性能优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： centos7 安装配置mesos+ma
- 下一篇： C++设计模式:Template Met