pHp+MySQL百萬級數(shù)據(jù)插入的優(yōu)化操作技巧

2023-11-28 分類：網(wǎng)站建設(shè)

插入分析

MySQL中插入一個記錄需要的時間由下列因素組成，其中的數(shù)字表示大約比例：

連接：（3）
發(fā)送查詢給服務(wù)器：（2）
分析查詢：（2）
插入記錄：（1x記錄大?。?/p>
插入索引：（1x索引）
關(guān)閉：（1）

如果我們每插入一條都執(zhí)行一個SQL語句，那么我們需要執(zhí)行除了連接和關(guān)閉之外的所有步驟N次，這樣是非常耗時的，優(yōu)化的方式有一下幾種：

在每個insert語句中寫入多行，批量插入
將所有查詢語句寫入事務(wù)中
利用Load Data導(dǎo)入數(shù)據(jù)

每種方式執(zhí)行的性能如下。

Innodb引擎

InnoDb 給 MySQL 提供了具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表。InnoDb 提供了行鎖(locking on row level)以及外鍵約束(FOREIGN KEY constraints)。

InnoDb 的設(shè)計目標是處理大容量數(shù)據(jù)庫系統(tǒng)，它的 CpU 利用率是其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能比的。在技術(shù)上，InnoDb 是一套放在 MySQL 后臺的完整數(shù)據(jù)庫系統(tǒng)，InnoDb 在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。

測試環(huán)境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

總數(shù)100W條數(shù)據(jù)

插入完后數(shù)據(jù)庫大小38.6Mb（無索引），46.8（有索引）

無索引單條插入總耗時：229s 峰值內(nèi)存：246Kb
有索引單條插入總耗時：242s 峰值內(nèi)存：246Kb
無索引批量插入總耗時：10s 峰值內(nèi)存：8643Kb
有索引批量插入總耗時：16s 峰值內(nèi)存：8643Kb
無索引事務(wù)插入總耗時：78s 峰值內(nèi)存：246Kb
有索引事務(wù)插入總耗時：82s 峰值內(nèi)存：246Kb
無索引Load Data插入總耗時：12s 峰值內(nèi)存：246Kb
有索引Load Data插入總耗時：11s 峰值內(nèi)存：246Kb

MyIASM引擎

MyISAM 是MySQL缺省存貯引擎。設(shè)計簡單，支持全文搜索。

測試環(huán)境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

總數(shù)100W條數(shù)據(jù)

插入完后數(shù)據(jù)庫大小19.1Mb（無索引），38.6（有索引）

無索引單條插入總耗時：82s 峰值內(nèi)存：246Kb
有索引單條插入總耗時：86s 峰值內(nèi)存：246Kb
無索引批量插入總耗時：3s 峰值內(nèi)存：8643Kb
有索引批量插入總耗時：7s 峰值內(nèi)存：8643Kb
無索引Load Data插入總耗時：6s 峰值內(nèi)存：246Kb
有索引Load Data插入總耗時：8s 峰值內(nèi)存：246Kb

總結(jié)

我測試的數(shù)據(jù)量不是很大，不過可以大概了解這幾種插入方式對于速度的影響，最快的必然是Load Data方式。這種方式相對比較麻煩，因為涉及到了寫文件，但是可以兼顧內(nèi)存和速度。

二、pHp+MySQL百萬數(shù)據(jù)插入

第一種方法：使用insert into 插入，代碼如下：

最后顯示為：23:25:05 01:32:05 也就是花了2個小時多! 這是時間擼幾把都行。。。

第二種方法：使用事務(wù)提交，批量插入數(shù)據(jù)庫(每隔10W條提交下)

最后顯示消耗的時間為：22:56:13 23:04:00 ，一共8分13秒，代碼如下：

時間是一下縮短不少但還是有點長。

第三種方法：使用優(yōu)化SQL語句：將SQL語句進行拼接，使用 insert into table () values (),(),(),()然后再一次性插入，如果字符串太長，

則需要配置下MYSQL，在mysql 命令行中運行：set global max_allowed_packet = 2*1024*1024*10;消耗時間為：11:24:06 11:24:11;

我擦插入200W條測試數(shù)據(jù)僅僅用了不到6秒鐘!代碼如下：

可能這里又會有人說數(shù)據(jù)不大 sql文件導(dǎo)出來也是小200M 5秒鐘對一表格進行200萬條數(shù)據(jù) 速度已經(jīng)不錯了。

這個再跟大家說一個 Tp框架了的 addall方法

速度也很快但是還不如原生。

著了說一下其中可能會遇到的問題比如試驗時可能會出現(xiàn)pHp內(nèi)存限制和mysql的SQL語句溢出甚至宕機

這里數(shù)幾個方案

1、可以進行相關(guān)的配置php.ini my.ini 等

2、大家可以用隊列進行分批處理

3、或者自己的一些方法

當前題目：pHp+MySQL百萬級數(shù)據(jù)插入的優(yōu)化操作技巧
文章轉(zhuǎn)載：http://www.muchs.cn/news40/297290.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、網(wǎng)頁設(shè)計公司、動態(tài)網(wǎng)站、全網(wǎng)營銷推廣、建站公司、微信公眾號

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容