PHPEXCEL 20萬資料導入導出（一）

本片部落格記錄了一次實際開發中的需要使用PHPExcel導入導出大量資料（20萬）的解決過程。

複盤優化自己的項目，好處在于，一旦已找到好的方法，好的代碼，你就不可能再使用差的代碼

場景描述：開發環境：普通電腦+win+php+mysql+apache。開發一個人力資源管理系統，涉及機關，部門，人員和之間的對應關系。在本項目開發中，需要導入20萬條人員的資訊，導出100萬條使用者資訊。

Ques1:如何使用phpexcel導入20萬條使用者資料

1，優化邏輯代碼

對于20萬條資料顯然無法進行測試，我們先使用1萬條資料進行測試

先看一下縮略版的邏輯代碼

foreach(...){
    $arr = db('department')->where('departCode',$strs[1])->where('unitCode',$strs[2])->find();
    if(empty($arr)){      
        db('department')->insert([        
        'departCode' => $strs[0],       
        'name' => $strs[1],       
        'unitCode' => $strs[2],   
        ]);
    }else{    
        return $this->error("上傳失敗,已存在該部門",'department/lst');
    }
 }
           

2，減少sql的執行

第一次上傳了 7000 條資料，時間24秒左右。

分析在導入資料時，并不是直接全部導入，還需要進行一些必要的邏輯判斷，是以每一條資料都需要判斷和插入，插入肯定沒有辦法避免，可以看出判斷語句是在插入前判斷表内是否有重複的資料，避免插入重複的資料，那如果将判斷的sql去掉，能不能減少運作時間呢？

測試後結果在17秒左右。

[外鍊圖檔轉存失敗(img-1U76vGxi-1564537982061)(en-resource://database/488:1)]

證明省去了7000條sql查詢語句還是有用的，是以我們可以去掉這個查重的判斷，在最後資料出入完畢時，再對重複資料進行處理和删除。

按照這個思路，我們可以把 foreach中的插入的sql語句，放在外面，在循環中把資料拼接起來，在循環外去執行插入多條資料的一條sql。

代碼為

$data = array();
foreach(...){
    $temp = [       
        'departCode' => $strs[0],       
        'name' => $strs[1],        
        'unitCode' => $strs[2],    
    ];    
    array_push($data,$temp);
}
db('department')->insertAll($data);
           

來看一下運作時間竟然不到一秒。

[外鍊圖檔轉存失敗(img-DzUS3NnK-1564537982063)(en-resource://database/486:1)]

要執行的sql次數等于 = 循環内sql數 x 資料量，對于大量資料，mysql處理會很吃力。是以要避免過多sql，優化插入的sql。盡量把資料交給程式去處理，減少sql語句才是關鍵。

3，保護記憶體

那我們就用20萬條資料來測試一下結果。

（需要注意幾個點：以下幾個預設配置都會讓你在導入大量資料時出錯

• php預設上傳檔案大小限制2M，（php.ini： upload_max_filesize）
• 預設腳本使用最大記憶體限制128M，（php.ini： memory_limit）
• mysql資料庫支援最大位元組為4194304byte，（資料庫配置：max_allowed_packet）

• excel03版（字尾.xls）以前最大行數六萬五，之後版本(.xlsx)是104萬。

  是以20萬條資料，我的配置是，使用新版excel，php上傳檔案大小調高到20M，最大記憶體500M，mysql最大位元組100M）

[外鍊圖檔轉存失敗(img-t5huTAwG-1564537982064)(en-resource://database/492:1)]

雖然用了40s，但是至少程式可以運作了，如果資料量繼續增大，有可能進行運算的記憶體很有可能不夠用，是以，另一方面，應該從保護記憶體的方面思考。在程式中設定斷點檢視運作時間，phpexcel加載這20萬條資料花了15s，一行行的讀花了接近40s，等于說全部時間還是花費在phpexcel本身執行上面，不知道有什麼好的優化方法。

Ques2:如何使用phpexcel導出100萬條使用者資料

導出六萬條資料

[外鍊圖檔轉存失敗(img-Ano6yvjn-1564537982064)(en-resource://database/494:1)]

20萬條資料

發現時間超過100s無法運作

1，使用csv檔案格式導出

csv 與 excel相比，好處比較多，比如更安全，文本格式不會産生錯誤，程式設計語言處理速度更快等

[外鍊圖檔轉存失敗(img-l3rDZ3c3-1564537982066)(en-resource://database/496:1)]

20萬條資料17s完成了

2，分批生成檔案，最後下載下傳壓縮包

我們将100萬條資料分為五份，每次隻從資料庫中取出20萬條資料，然後寫入csv檔案中，最後将這五個檔案壓縮打包下載下傳

[外鍊圖檔轉存失敗(img-L5mmH0bR-1564537982066)(en-resource://database/498:1)]

100萬條資料能在15s内完成

生成單個檔案的話11s就可以完成