スポンサーリンク

SQLでマスタテーブルと結合したテーブルから最新1件ずつ表示する例と速度テスト

SQLiteでもMySQLでもPostgreSQLでも何でも良いのですが、顧客マスターのようなマスターテーブルと、注文情報のようなトランザクションテーブルを結合し、最新の1件だけ表示したいというケース、よくありますよね?
※業務システムでよく使う用語で、マスタ以外のデータテーブルの意味。SQLのトランザクションとは無関係。

例えば顧客一覧の画面で、その顧客ごとに最新の注文1件だけを表示したい場合など。

プログラムで実現するなら、顧客マスタを読み込みつつ、顧客Noを基に注文テーブルを読み、1件だけ表示、を繰り返しても構いませんが、SQL文1本で済ませたいこともある。

大して難しい話ではないのですが、ちょくちょく書き方を忘れるので備忘録ついでに処理方法別の速度も計測してみました。

スポンサーリンク

テスト用のテーブル情報

顧客マスタ

CREATE TABLE shop_customer (customer_seq INTEGER PRIMARY KEY, customer_name TEXT)
customer_seq顧客No
customer_name顧客名

サンプルデータなので、顧客Noと顧客名のみ。

テストはSQLiteで実行しているので、customer_seqは勝手に自動採番になります。SQLiteは数値属性の項目がPRIMARY KEYだったら勝手に自動採番してくれるんですよ。PostgreSQLの場合はserial型、MySQLの場合はAUTO_INCREMENTを付加すれば自動採番になります。

注文テーブル

CREATE TABLE shop_order (order_seq integer PRIMARY KEY, customer_seq INTEGER, product_name TEXT, price INTEGER)
order_seq注文No
customer_seq顧客No
product_name商品名
price価格

こちらもサンプルデータなのであくまで最低限の項目のみ。

顧客別に最新の注文1件を表示するSQL文

パフォーマンステストは長くなるので、先に結合用のSQL文を書いておきます。

SELECT * FROM shop_customer A LEFT JOIN shop_order B
 ON B.order_seq = (SELECT order_seq FROM shop_order WHERE customer_seq = A.customer_seq ORDER BY order_seq DESC LIMIT 1)

このSQL文で、顧客一覧を表示しつつ、最新の注文1件だけを表示することが可能です。

■表示例

顧客名最新の注文
顧客No.1商品名72542104,137円
顧客No.2商品名92819187,665円
顧客No.3商品名52703791,398円
以下略

最新の注文1件+注文数も表示したい場合

SELECT *,
(SELECT COUNT(*) FROM shop_order WHERE customer_seq=A.customer_seq) AS order_count
FROM shop_customer A LEFT JOIN shop_order B ON B.order_seq = (SELECT order_seq FROM shop_order WHERE customer_seq = A.customer_seq ORDER BY order_seq DESC LIMIT 1)

先ほどのSQL文にサブクエリー「(SELECT COUNT(*) FROM shop_order WHERE customer_seq=A.customer_seq) AS order_count」を追加しただけですが、これで顧客一覧を表示しつつ、最新の注文1件およびその顧客の全注文数が表示できます。

■表示例

顧客名最新の注文注文数
顧客No.1商品名72542104,137円6件
顧客No.2商品名92819187,665円5件
顧客No.3商品名52703791,398円9件
以下略

このような結合やサブクエリーを使うことで、その顧客が過去に何件注文を入れているのか、最新の注文はいつ頃でいくら使ったのか、1件あたりいくら使う客なのか等、経営者が必要とする情報を簡単にレポート化出来るようになるでしょう。

インデックスについて

余談な気もしますが、経験の長いフリーランスのエンジニアでもテーブルのインデックスがいいかげんというか、まったく張らないビックリさんもいるので、一応書いておきますが、先述のようなテーブルでも下記のインデックスを張らないとパフォーマンスが恐ろしく低下します。

CREATE INDEX idx_shop_order_customer ON shop_order (customer_seq);

PRIMARY KEYには自動でインデックスが張られるため、shop_customer.customer_seqとshop_order.order_seqにはインデックスが張られています。しかし、顧客情報と注文情報を紐づけるとき、shop_order.customer_seqをキーに結合または検索するはずなので、ここにインデックスを作っておかないと非効率極まりないのです。

注文情報が10万件ほどあった場合、本来0.2秒で終わる処理に240秒かかったりします。マジで。

疑問に感じたらEXPLAINを使おう

更に余談の余談ですが、インデックスが必要なのかな、どうなのかなと疑問に思った場合は EXPLAIN を使いましょう。SQL文の前に付けるだけです。SQLiteの場合は EXPLAIN QUERY PLAN [SQL文] とすることでインデックスが使われているかどうか確認できます。

EXPLAIN QUERY PLAN
SELECT * FROM shop_customer A LEFT JOIN shop_order B
 ON B.order_seq = (SELECT order_seq FROM shop_order WHERE customer_seq = A.customer_seq ORDER BY order_seq DESC LIMIT 1)

■結果(インデックスなし)

SCAN TABLE shop_customer AS A
SEARCH TABLE shop_order AS B USING INTEGER PRIMARY KEY (rowid=?)
CORRELATED SCALAR SUBQUERY 1
SCAN TABLE shop_order

最初の行の SCAN TABLE shop_customer は良いです。なぜなら愚直に顧客一覧を表示するだけだから。

でも、最後の行の SCAN TABLE shop_order の部分はダメ。これはshop_customerとshop_orderの結合処理においてインデックスを使わず、全件読みつつ結合しているという意味で、凄まじく非効率。このせいで240秒もかかります。

ここに先ほどのインデックスを張ることで、結果はこうなります。

■結果(インデックスあり)

SCAN TABLE shop_customer AS A
SEARCH TABLE shop_order AS B USING INTEGER PRIMARY KEY (rowid=?)
CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE shop_order USING COVERING INDEX idx_shop_order_customer (customer_seq=?)

最後の行を見れば shop_order との結合にインデックス idx_shop_order_customer を使っていることがわかります。これで実行速度が0.2秒、実に1000倍の速さになるわけです。

パフォーマンステストの準備

SQL文についての解説は上の項で終わりなのですが、結合やサブクエリーを活用したパターンと、愚直にプログラムで1テーブルずつ見たパターンで、速度がどれほど違うものなのか確認したいと思います。

そのため、今回はPHPで顧客データを1万件、注文データを10万件ほど用意します。

PHPでテストデータを生成する

<?php
$db = new PDO('sqlite:test.db');

$cfg['max_customer'] = 10000; //顧客数
$cfg['max_order'] = 100000; //注文数(最大)
$cfg['max_price'] = 999999; //商品価格(最大)

$db->beginTransaction();

//テーブル作成
$db->query('DROP TABLE IF EXISTS shop_customer');
$db->query('DROP TABLE IF EXISTS shop_order');
$db->query('CREATE TABLE shop_customer (customer_seq INTEGER PRIMARY KEY, customer_name TEXT)');
$db->query('CREATE TABLE shop_order (order_seq integer PRIMARY KEY, customer_seq INTEGER, product_name TEXT, price INTEGER)');
$db->query('CREATE INDEX idx_shop_order_customer ON shop_order (customer_seq)');

//顧客情報生成
$st = $db->prepare('INSERT INTO shop_customer (customer_name) VALUES (?)');
for ($i = 1; $i <= $cfg['max_customer']; $i++) {
	$st->execute(array('顧客No.'.$i));
}
//注文情報生成
$st = $db->prepare('INSERT INTO shop_order (customer_seq, product_name, price) VALUES (?,?,?)');
for ($i = 1; $i <= $cfg['max_order']; $i++) {
	//ランダムに顧客番号を選択
	$customer_seq = mt_rand(1, $cfg['max_customer']);
	$product_name = '商品名'.mt_rand(1, $cfg['max_order']);
	$price = mt_rand(1, $cfg['max_price']);
	$st->execute(array($customer_seq, $product_name, $price));
}
$db->commit();
?>

手っ取り早くSQLiteでテストするため、データソースは sqlite:test.db となっていますが、PDOを使っているのでMySQLやPostgreSQL等に変えることも簡単なはず。

SQLiteはデータベース接続時に存在しなければ作る、というワイルドな仕様なのでこのPHP一本でデータベース作成、テーブル作成、インデックス作成、テストデータ生成が完結します。

テスト環境

一言でいうと非力なNUC上にnginxとPHP7.4がセットアップされている環境です。

ハードウェア
機種名ACEPC GK3
CPUIntel Celeron J4125
メモリ8GB
ストレージSSD 256GB

Amazonで購入したミニPCですが、今はもう売ってないっぽいですね。似たようなのだとこのへんかなぁ。

https://amzn.to/3oX8aM1

小さいクセに内蔵ストレージとは別に2.5インチのストレージを増設できるスペースが空いている点が気に入って買いました。J4125はPassmarkスコア3000点程度と非力ですが、この環境でストレスなく動けば格安レンタルサーバー上でも間違いなく快適に動作するのでワリと気に入っています。

ソフトウェア
OSWindows 10 Pro
HTTPサーバーnginx 1.18.0
PHP7.4.7
SQLite3.31.1

結合もサブクエリーも使わないパターンでテスト

PHPソース

<?php
$t1 = microtime(true);
$db = new PDO('sqlite:test.db');

echo '<table class="test">';
echo '<tr><th>顧客名</th><th colspan="2">最新の注文</th><th>注文数</th></tr>';

//テストケース1
$st = $db->query('SELECT * FROM shop_customer');
$stOrder = $db->query('SELECT * FROM shop_order WHERE customer_seq=? ORDER BY order_seq DESC LIMIT 1');
$stOrderCount = $db->query('SELECT COUNT(*) AS order_count FROM shop_order WHERE customer_seq=?');
while ($row = $st->fetch()) {
	echo '<tr>';
	echo '<td>'.$row['customer_name'].'</td>';
	$stOrder->execute(array($row['customer_seq']));
	if ($rowOrder = $stOrder->fetch()) {
		echo '<td>'.$rowOrder['product_name'].'</td>';
		echo '<td>'.number_format($rowOrder['price']).'</td>';
		$stOrderCount->execute(array($row['customer_seq']));
		$rowOrder = $stOrderCount->fetch();
		echo '<td>'.number_format($rowOrder['order_count']).'</td>';
	} else {
		echo '<td colspan="3">なし</td>';
	}
	echo '</tr>';
}

echo '</table>';
echo '<style>.test { border-collapse:collapse; } .test th { background-color:lightgray; } .test td { border:1px solid lightgray; }</style>';
echo microtime(true) - $t1.' sec.';
?>

愚直にshop_customerテーブルを1件ずつ読み、そのたびにshop_orderから、その顧客の注文数および最新の注文情報を取得しています。

結果

約0.301秒

顧客情報1万件、注文情報10万件でもけっこう早いですね。何度かデータを作り直してテストしましたが、0.3秒前後なのは変わりませんでした。

結合とサブクエリーを使ったパターンでテスト

PHPソース

<?php
$t1 = microtime(true);
$db = new PDO('sqlite:test.db');

echo '<table class="test">';
echo '<tr><th>顧客名</th><th colspan="2">最新の注文</th><th>注文数</th></tr>';

//テストケース2
$st = $db->query('SELECT *,(SELECT COUNT(*) FROM shop_order WHERE customer_seq=A.customer_seq) AS order_count FROM shop_customer A LEFT JOIN shop_order B ON B.order_seq = (SELECT order_seq FROM shop_order WHERE customer_seq = A.customer_seq ORDER BY order_seq DESC LIMIT 1)');
while ($row = $st->fetch()) {
	echo '<tr>';
	echo '<td>'.$row['customer_name'].'</td>';
	if ($row['product_name'] != '') {
		echo '<td>'.$row['product_name'].'</td>';
		echo '<td>'.number_format($row['price']).'</td>';
		echo '<td>'.number_format($row['order_count']).'</td>';
	} else {
		echo '<td colspan="3">なし</td>';
	}
	echo '</tr>';
}

echo '</table>';
echo '<style>.test { border-collapse:collapse; } .test th { background-color:lightgray; } .test td { border:1px solid lightgray; }</style>';
echo microtime(true) - $t1.' sec.';
?>

結果

約0.196秒

なかなか大きな差がありました。約35%も速度上昇したとなると無視できる差異ではないかも知れません。

…10万件以上の注文データがあり、かつ1ページに顧客を1万人分も表示するなら…という条件付きなので、レアケースとも言えますが。

まとめ

加齢と共にSQLの結合処理がパッと頭に浮かばなくなってきたので備忘録代わりに投稿するつもりが、つい気になってパフォーマンステストまでしちゃった感じです。

顧客情報と注文情報に限らず、ユーザー情報とログイン情報だったり、商品別の最終注文者情報だったり、1対多の結合で、「多」の最新データ1件のみ欲しい、というケースはごろごろあります。

そんなときは

SELECT * FROM [マスタ] A LEFT JOIN [トランザクションテーブル] B
 ON B.seq = (SELECT seq FROM [トランザクションテーブル] WHERE [マスタSEQ] = A.seq ORDER BY seq DESC LIMIT 1)

みたいな感じでマスタ別の最新1件を取得しましょうね~という話でした。

また、こういった結合やサブクエリーの処理は携わる人間が多ければ多いほど、スキルレベルの違いにより間違いが発生しやすいし、複雑なSQL文を見ただけで拒否反応を起こし、修正したがらない人すらいます。

そういった過去の経験からも、結合やサブクエリーを使わず、わざわざアホっぽい愚直にプログラム側でマスタテーブルと、トランザクションテーブルを1件ずつ読む、というパターンも作ってみました。

今回の例では35%ほどの差がありましたし、ぼくの場合は数十万PVの公開サイトで使うこともあるので、できるだけ速度の速いほうを選択しますが、数百人程度しか使わない社内システムとかであればパフォーマンスを気にするより、コードの見やすさを重視して選択すれば良いのではないかな、と思いました。

コメント

タイトルとURLをコピーしました