大量のCSVのデータのDB存在チェックの効率化

概要

• 業務で数百万件のCSVファイルをインポートする処理を実装することがあった。
• 自分が従来書いていたDB存在チェックの処理だとCSV一行ずつDB存在チェックを行なっていたため、わかってはいたものの処理に時間がかかってしまっていた。
• まとめてDB存在チェックできるように実装したのでその仕組みを記載しておこうと思います。

前提

例として、以下のusersテーブルが存在するとします。
(例なので件数少ないですが…)

id	login_id	age
1	test_user_1	0
2	test_user_2	5
3	test_user_3	10
4	test_user_4	15
5	test_user_5	20

そのusersテーブルに対して、画面上からCSVをアップロードしてusersの登録が行えるインポート機能を実装します。
CSVは「loginid,age」を複数行で作成されるものとします。
login_idは重複を許していません。
なのでCSVに重複があった場合はエラーを返さなければいけません。
以下のようなCSVは「test_user_5」が登録済みなのでエラーにします。

test.csv

test_user_5,20
test_user_6,25
test_user_7,30
test_user_8,35
test_user_9,40

※本当は１項目ずつ半角アルファベット、アンダーバー、数字のみとか色々なバリデーションが必要かと思いますが今回は省略

従来の方法

例なので簡素なphpにしていますが言いたいことは伝わるかなと。
CSV一行ずつDB存在チェックしているのでCSVの行数分SQLが発行されてしまうのはわかってたのですが、こうしないと今調べてる行数とかがエラーメッセージで出せないと思ってたんですよね…

従来の方法サンプルコード

index.php

<?php

$errors = default_validation();

var_dump($errors);

function default_validation()
{
    $file_path = './test.csv';
    $errors = [];
    $fp = fopen($file_path, 'r');
    $line_num = 0;
    $clm = [
        'LOGIN_ID' => 0,
        'AGE' => 1,
    ];
    while (($line = fgetcsv($fp)) !== false) {
        $line_num++;

        // 既にDBに登録済みかチェックを行う。
        $users = get_users($line[$clm['LOGIN_ID']]);
        if (!empty($users)) {
            $errors[] = $line_num . '行目のデータは登録済みです。';
        }
    }
    fclose($fp);

    return $errors;
}

function get_users($login_id = '')
{
    // DBの準備が面倒だったので擬似的な関数
    // モデルクラスで単純な完全一致検索をする関数を想定
    // ここではこういうSQLを想定
    // SELECT login_id
    // FROM users
    // WHERE login_id = '引数のlogin_id'

    // usersテーブルにはlogin_id=test_user_5が登録されている想定
    if ($login_id === 'test_user_5') {
        return [
            0 => [
                'login_id' => 'test_user_5',
            ],
        ];
    }
    return [];
}

効率化した方法

• チェック用の配列にCSVの行数を記憶させる
• 自分が設定した件数ごとにまとめて存在チェックすることでSQLの発行回数を抑える
• DBで取得した値とチェック用の配列を照合してエラーメッセージ作成する

効率化した方法サンプルコード

index.php

<?php

$errors = validation();

var_dump($errors);

function validation()
{
    $file_path = './test.csv';
    $errors = [];
    $record_max = count(file($file_path));
    $fp = fopen($file_path, 'r');
    $line_num = 0;
    $clm = [
        'LOGIN_ID' => 0,
        'AGE' => 1,
    ];
    $exists_validation_limit = 10000;
    $exists_check = [];
    while (($line = fgetcsv($fp)) !== false) {
        $line_num++;

        // 既にDBに登録済みかチェックを行う。
        // 設定件数ごとにまとめてチェックすることで効率化。
        if (isset($line[$clm['LOGIN_ID']]) && $line[$clm['LOGIN_ID']] !== '') {
            // チェック用の配列にCSVの行数を記憶
            $exists_check[$line[$clm['LOGIN_ID']]] = $line_num;
        }
        if ($line_num % $exists_validation_limit === 0 || $line_num === $record_max) {
            $users = get_users(array_keys($exists_check));
            if (!empty($users)) {
                foreach ($users as $val) {
                    // DBで取得した値とチェック用の配列を照合してエラーメッセージ作成
                    if (isset($exists_check[$val['login_id']])) {
                        $errors[] = $exists_check[$val['login_id']] . '行目のデータは登録済みです。';
                    }
                }
            }
            $exists_check = [];
        }
    }
    fclose($fp);

    return $errors;
}

function get_users($login_ids = [])
{
    // DBの準備が面倒だったので擬似的な関数
    // モデルクラスで単純なIN句検索する関数を想定
    // ここではこういうSQLを想定
    // SELECT login_id
    // FROM users
    // WHERE login_id IN ('引数のlogin_id')

    // usersテーブルにはlogin_id=test_user_5が登録されている想定
    if (in_array('test_user_5', $login_ids, true)) {
        return [
            0 => [
                'login_id' => 'test_user_5',
            ],
        ];
    }
    return [];
}

今回は10000件毎、またはCSVの最大件数に達した時に存在チェックするようにしています。
この最大値はメモリなどによって適宜調整してあげると良いと思います。

あとがき

需要ありそうかな？と思って書き残したので、ぜひ誰かの参考になればと思います！