Symfoware

Symfowareについての考察blog

CodeIgniter 大量データの更新を高速に行う方法

CodeIgniterで大量データを一括登録する方法について調べました。
CodeIgniterで大量データを高速に登録する方法の検討

今回は一括更新の方法について調べてみます。


下準備



CodeIgniter 3.1.0、MariaDB 10.0.26でテストしてみます。

データは前回同様郵便番号の情報を使用します。
http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

更新するにあたり、郵便番号をプライマリキーとして使用したかったので、
こんなPythonスクリプトで重複のない住所データを作成しました。


  1. # -*- coding:utf-8 *-
  2. import codecs
  3. def sunit(s, zip_codes):
  4.     
  5.     s = s.strip()
  6.     data = [s.strip('"') for s in line.split(u',')]
  7.     zip_code = data[2]
  8.     if (zip_code in zip_codes):
  9.         return None
  10.     
  11.     zip_codes.add(zip_code)
  12.     address = data[6] + data[7] + data[8]
  13.     
  14.     return '%s,%s\n' % (zip_code, address)
  15. kenall = codecs.open('KEN_ALL.CSV', 'r', 'ms932')
  16. out = codecs.open('all.csv', 'w', 'utf-8')
  17. zip_codes = set()
  18. with kenall, out:
  19.     for line in kenall:
  20.         data = sunit(line, zip_codes)
  21.         if not data:
  22.             continue
  23.         out.write(data)




119,777件のテストデータとなりました。
以下のテーブルを作成し、事前にデータの登録を行っておきます。


  1. create table zip (
  2.     zip_code char(7) not null primary key,
  3.     address varchar(100) not null
  4. );







通常の更新



1件ずつ郵便番号をキーに住所を更新するプログラムはこうなりました。

・application/controllers/Test.php


  1. <?php
  2. class Test extends CI_Controller {
  3.     public function index() {
  4.         
  5.         $this->load->database();
  6.         
  7.         // csvファイル読み込み準備
  8.         $csv = new SplFileObject('/var/dev/php/all.csv', 'r');
  9.         $csv->setFlags(SplFileObject::READ_CSV);
  10.         
  11.         
  12.         // 時間計測開始
  13.         $start = microtime(true);
  14.         
  15.         $rows = [];
  16.         
  17.         foreach ($csv as $row) {
  18.             if ($row === [null]) {
  19.                 break;
  20.             }
  21.             
  22.             $this->db->where('zip_code', $row[0]);
  23.             $this->db->update(
  24.                 'zip',
  25.                 ['address' => '変更1'.$row[1]]
  26.             );
  27.         }
  28.         
  29.         $stop = microtime(true);
  30.         echo sprintf("処理時間:%.3f 秒\n", ($stop - $start));
  31.         
  32.     }
  33. }



実行してみると、3,947秒でした。


$ php index.php test
処理時間:3947.239 秒



テストは仮想環境で行っているため、ディスクIOが多く発生すると
性能が低下するようです。





一括コミット



コードはほぼそのままに、ループ全体にトランザクションをかけて
一括更新するようにしてみます。


  1. <?php
  2. class Test extends CI_Controller {
  3.     public function index() {
  4.         
  5.         $this->load->database();
  6.         
  7.         // csvファイル読み込み準備
  8.         $csv = new SplFileObject('/var/dev/php/all.csv', 'r');
  9.         $csv->setFlags(SplFileObject::READ_CSV);
  10.         
  11.         
  12.         // 時間計測開始
  13.         $start = microtime(true);
  14.         
  15.         // トランザクションをかけて一括更新
  16.         $this->db->trans_start();
  17.         foreach ($csv as $row) {
  18.             if ($row === [null]) {
  19.                 break;
  20.             }
  21.             
  22.             $this->db->where('zip_code', $row[0]);
  23.             $this->db->update(
  24.                 'zip',
  25.                 ['address' => '変更2'.$row[1]]
  26.             );
  27.         }
  28.         
  29.         $this->db->trans_complete();
  30.         
  31.         $stop = microtime(true);
  32.         echo sprintf("処理時間:%.3f 秒\n", ($stop - $start));
  33.         
  34.     }
  35. }



実行してみると20秒程度。
劇的に性能が向上しました。


$ php index.php test
処理時間:20.908 秒







update_batch



CodeIgniterのドキュメントを見てみると、update_batchという
メソッドが用意されていました。
Query Builder Class

どうやってupdate文をバッチ処理するんだろう?と思い、
実行するクエリを調べてみると、このようにcaseで設定する値を
変更していました。なるほど。


  1. UPDATE `zip` SET `address` = CASE
  2. WHEN `zip_code` = '0600000' THEN '変更3北海道札幌市中央区以下に掲載がない場合'
  3. WHEN `zip_code` = '0640941' THEN '変更3北海道札幌市中央区旭ケ丘'
  4. WHEN `zip_code` = '0600041' THEN '変更3北海道札幌市中央区大通東'
  5. ELSE `address` END
  6. WHERE `zip_code` IN('0600000','0640941','0600041')




update_batchを使用した処理に変更してみます。


  1. <?php
  2. class Test extends CI_Controller {
  3.     public function index() {
  4.         
  5.         $this->load->database();
  6.         
  7.         // csvファイル読み込み準備
  8.         $csv = new SplFileObject('/var/dev/php/all.csv', 'r');
  9.         $csv->setFlags(SplFileObject::READ_CSV);
  10.         
  11.         
  12.         // 時間計測開始
  13.         $start = microtime(true);
  14.         
  15.         $rows = [];
  16.         
  17.         foreach ($csv as $row) {
  18.             if ($row === [null]) {
  19.                 break;
  20.             }
  21.             
  22.             $rows[] = [
  23.                 'zip_code' => $row[0],
  24.                 'address' => '変更3'.$row[1],
  25.             ];
  26.             
  27.         }
  28.         
  29.         // update_batchで一括処理
  30.         // 第三引数に更新のキーとなる値を指定する
  31.         $this->db->update_batch('zip', $rows, 'zip_code');
  32.         
  33.         $stop = microtime(true);
  34.         echo sprintf("処理時間:%.3f 秒\n", ($stop - $start));
  35.         
  36.     }
  37. }




8秒で処理が終了しました。


$ php index.php test
処理時間:8.339 秒




update_batchは第四引数で一括処理するレコード数を指定できます。(デフォルト100)
1,000件、10,000件と一括処理する件数を変更して試してみます。


  1. <?php
  2. class Test extends CI_Controller {
  3.     public function index() {
  4.         
  5.         $this->load->database();
  6.         
  7.         // csvファイル読み込み準備
  8.         $csv = new SplFileObject('/var/dev/php/all.csv', 'r');
  9.         $csv->setFlags(SplFileObject::READ_CSV);
  10.         
  11.         
  12.         // 時間計測開始
  13.         $start = microtime(true);
  14.         
  15.         $rows = [];
  16.         
  17.         foreach ($csv as $row) {
  18.             if ($row === [null]) {
  19.                 break;
  20.             }
  21.             
  22.             $rows[] = [
  23.                 'zip_code' => $row[0],
  24.                 'address' => '変更3'.$row[1],
  25.             ];
  26.             
  27.         }
  28.         
  29.         // update_batchで一括処理
  30.         // 第三引数に更新のキーとなる値を指定する
  31.         // 第四引数で一括処理するレコード数を指定
  32.         $this->db->update_batch('zip', $rows, 'zip_code', 1000);
  33.         
  34.         $stop = microtime(true);
  35.         echo sprintf("処理時間:%.3f 秒\n", ($stop - $start));
  36.         
  37.     }
  38. }




1,000件一括更新

$ php index.php test
処理時間:9.253 秒



10,000件一括更新

$ php index.php test
処理時間:49.161 秒




クエリーの生成やデータベースサーバー側での解析に時間がかかるようで、
性能はデフォルトの100件の時と変わりませんでした。





update_batch + トランザクション



トランザクションをかけた状態で、update_batchを実行してみます。


  1. <?php
  2. class Test extends CI_Controller {
  3.     public function index() {
  4.         
  5.         $this->load->database();
  6.         
  7.         // csvファイル読み込み準備
  8.         $csv = new SplFileObject('/var/dev/php/all.csv', 'r');
  9.         $csv->setFlags(SplFileObject::READ_CSV);
  10.         
  11.         
  12.         // 時間計測開始
  13.         $start = microtime(true);
  14.         
  15.         $rows = [];
  16.         
  17.         foreach ($csv as $row) {
  18.             if ($row === [null]) {
  19.                 break;
  20.             }
  21.             
  22.             $rows[] = [
  23.                 'zip_code' => $row[0],
  24.                 'address' => '変更5'.$row[1],
  25.             ];
  26.             
  27.         }
  28.         
  29.         // トランザクションをかけて一括更新
  30.         $this->db->trans_start();
  31.         
  32.         // update_batchで一括処理
  33.         // 第三引数に更新のキーとなる値を指定する
  34.         // 第四引数で一括処理するレコード数を指定
  35.         $this->db->update_batch('zip', $rows, 'zip_code', 100);
  36.         
  37.         $this->db->trans_complete();
  38.         
  39.         $stop = microtime(true);
  40.         echo sprintf("処理時間:%.3f 秒\n", ($stop - $start));
  41.         
  42.     }
  43. }




100件ずつ一括更新

$ php index.php test
処理時間:9.680 秒



1,000件ずつ一括更新

$ php index.php test
処理時間:9.636 秒



10,000件ずつ一括更新

$ php index.php test
処理時間:51.278 秒




case文の解釈に時間がかかるのでしょうか、トランザクションの有無による
実行時間の差はほとんどありませんでした。




まとめ



各パターン5回実行した時の結果と平均をまとめています。数字はすべて秒です。
通常パターンはあまりに時間がかかるので1回しか測定していません。

内容1回目2回目3回目4回目5回目平均
単件update3947.239----3947.239
一括update20.90819.88719.65220.3619.31520.024
update_batch(100)8.3398.3688.2088.5998.8248.468
update_batch(1,000)9.2538.6118.5368.9688.4518.764
update_batch(10,00)49.16152.61449.04149.27548.85849.79
update_batch(100) + tran9.688.4468.2368.688.3978.688
update_batch(1,000) + tran9.6368.8099.6919.2498.4639.17
update_batch(10,00) + tran51.27850.61448.50550.18549.26449.969


update_batchデフォルトの100件ずつ、トランザクションなしで実行するのが一番速そうです。




関連記事

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2016/08/06(土) 17:07:19|
  2. PHP
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Debian 8(jessie)にOpenJDK 8をインストールする | ホーム | CodeIgniterで大量データを高速に登録する方法の検討>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1908-31e26dc9
この記事にトラックバックする(FC2ブログユーザー)