はじめに
今年もアドベントカレンダーはじまりました！
この記事はNE Advent Calendar 2022のカレンダー2枚目1日目の記事です。
NE株式会社のエンジニアが自分の興味あるテーマについて自由に書いていきます。
毎年一緒に参加して盛り上げてくれる皆さんに感謝です 
初の2枚目突入にテンション上がっております。
概要
CSVで入力されたコードの配列があったとする。
| code | name |
|---|---|
| test1 | テスト1 |
| test2 | テスト2 |
| test3 | テスト3 |
| hoge | ほげ |
DBのテーブル（table_name: codes）
| code | name |
|---|---|
| test1 | テスト1 |
| test2 | テスト2 |
| test3 | テスト3 |
テーブルに存在しないものはバリデーションエラーで弾きたい。
この場合はhogeが未登録なのでエラーにしたい。
これを入力値が大量でテーブルレコードも大量にある場合に高速で処理したい。
案1: 愚直にループ回す
<?php
$codes = ['test1', 'test2', 'test3', 'hoge'];
foreach($codes as $code){
$sql = "select * from codes where code = {$code}";
$pdo = new PDO();
$result = $pdo->fetchAll($sql);
if(empty($result)){
var_dump("{$code} not exists");
}
}
↓
string(15) "hoge not exists"
説明
1番シンプル。
ただCSVの行数文クエリを発行するため重い。
大量データには向いてない。
※PDOクラス未定義だしprepareしてないしこのままでは動きません。あくまでサンプルです。
案2: テーブルに登録されているコード一覧を全て取得しておき配列同士で比較する
<?php
$codes = ['test1', 'test2', 'test3', 'hoge'];
$sql = "select code from codes";
$pdo = new PDO();
$result = $pdo->fetchColumn($sql);
$diff = array_diff($codes, $result);
var_dump($diff);
↓
array(1) { [3]=> string(4) "hoge" }
説明
速いが1度全レコードをメモリ上に展開する。
メモリ消費が厳しい処理では採用は難しい。
案3: CSVの情報をTEMPORARYテーブルに入れクエリで比較する
<?php
$pdo = new PDO();
$create_sql="
CREATE TEMPORARY TABLE IF NOT EXISTS `csv` (
`code` varchar(30),
`line` int(11),
KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
";
$pdo->query($create_sql);
$insert_sql="
insert into `csv`
(code, line)
values
('test1', 2),
('test2', 3),
('test3', 4),
('hoge', 5)
";
$pdo->query($insert_sql);
$sql="
select csv.code, csv.line from csv
left join codes on csv.code = codes.code
where codes.code is null;
";
$result = $pdo->fetchAll($sql);
var_dump($result);
↓
array(1) { [0]=> array(2) { ["code"]=> string(4) "hoge" ["line"]=> string(1) "5" } }
説明
TEMPORARYテーブルを作る必要があり若干手間。
テーブル作成→データインサート→クエリ比較
という3ステップが必要。
案4: unionで擬似的にテーブルを作成しクエリで比較する
<?php
$sql="
select csv.code, csv.line from(
select 'test1' as code, '2' as line
union
select 'test2' as code, '3' as line
union
select 'test3' as code, '4' as line
union
select 'hoge' as code, '5' as line
) as csv
left join codes on csv.code = codes.code
where codes.code is null;
";
$pdo = new PDO();
$result = $pdo->fetchAll($sql);
var_dump($result);
↓
array(1) { [0]=> array(2) { ["code"]=> string(4) "hoge" ["line"]=> string(1) "5" } }
説明
少々難解だがメモリ消費も文字列なのでそこまで重くない。
クエリも高速で結果を返す。
詳細説明
select csv.code, csv.line from(
select 'test1' as code, '2' as line
union
select 'test2' as code, '3' as line
union
select 'test3' as code, '4' as line
union
select 'hoge' as code, '5' as line
) as csv;
これを単体クエリで実行すると以下のようになる。
要するに擬似的にテーブル作ってるみたいな感じ。
この案の欠点はunionしまくるのでMySQLのパラメータ設定によってはいろんな部分に引っかかる可能性がある。（max_allowed_packetなど）
まとめ
何が最適かは正直悩む。
- 可読性
- 処理速度
- メモリ消費
- データ量
これらを鑑みて最適な処理を模索したい。
おわりに
アドカレ、今年は初めて2枚目に突入できて大変嬉しいです。
組織の成長、チームの成長、メンバーの成長を感じます。
ここがゴールではなく3枚、4枚とこなしていける組織になっていけるようにがんばります！