📕
SQLアンチパターン読書会のアウトプット
💭 記事の目的 💭
- 「発信」の練習
- 読んだ感想など、MEMO を残したいと思います
📕 書籍 📕
🐖 リポジトリ 🐖
サンプルデータベースと、PHP の実行環境を構築できるリポジトリ作成しました。
私自身が、手を動かしたくて自分用作ったので、簡単なものですが、だれかのお役にたてればうれしいです。
🌷 感想 🌷
15章 ランダムセレクション
序章
とあるテーブルから、ランダムで広告を取得する処理が、データ量が多くなるに連れて重くなっちゃったよー
みたいな話からスタート。
この章で達成したい目的
ランダムでデータを取得する効率のいいSQLクエリを書くぞ！
アンチパターン
データのランダムソート
orderby-rand.sql
SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;
＜だめなところ＞
- インデックスが効かない
- 上記に伴い、データ量が多くなるとパフォーマンスが低下する（序章の問題が発生する）
＜アンチパターン使っていい場面＞
- 取得対象のデータ量が少ない場合
解決策
その１：主キー（1 - 最大値）の間で、ランダム値とっちゃえ
rand-1-to-max.sql
SELECT b1.*
FROM Bugs AS b1
INNER JOIN(
SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id )AS b2 ON b1.bug_id = b2.rand_id;
＜あともう一歩なところ＞
- ランダムに選ばれた値が欠番の場合、結果が0行で返っちゃう
＜この解決策を使っていい場面＞
- 主キー（1 - 最大値）が全部使用されている場面ならOK
その２：欠番がある場合は、その後ろのキーを取得すればいいじゃない
next-higher.sql
SELECT b1.*
FROM Bugs AS b1
INNER JOIN(
SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS bug_id ORDER BY b1.bug_id
) AS b2 ON b1.bug_id >= b2.bug_id
LIMIT 1;
＜あともう一歩なところ＞
- 欠番の１つ上のキーが選択される頻度が高くなる可能性有
＜この解決策を使っていい場面＞
- 欠番があまりなくて、ランダム結果の均等性がさほど必要じゃない場合なら使っていいかも
その３：一旦、全キー取得して、そこからランダムに取ればいいじゃない
rand-key-from-list.php
<?php
$bug_id_list =
$pdo->query("SELECT bug_id FROM Bugs") -> fetchAll (PDO::FETCH_ASSOC);
$rand = rand(0, count($bug_id_list) - 1); $rand_bug_id = intval($bug_id_list[$rand]['bug_id']);
$stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id = ?"); $stmt->bindValue(1, $rand_bug_id, PDO::PARAM_INT); $stmt->execute();
$rand_bug = $stmt->fetch();
＜あともう一歩なところ＞
- 全キー取得でメモリ喰う
- クエリを２回実行する
＜この解決策を使っていい場面＞
- 全キー取得時のサイズがほどほどな時
その４：ランダムなオフセットを生成＆使えばいいじゃない
すべての弱点をおぎなったよバージョン（らしい）
limit-offset.php
<?php
$rand_sql = "SELECT FLOOR(
RAND()* (SELECT COUNT(*) FROM Bugs)
) AS id_offset";
$result = $pdo->query($rand_sql)->fetch(PDO:: FETCH_ASSOC);
$offset = intval($result['id_offset']);
$sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':offset', $offset, PDO:: PARAM_INT);
$stmt->execute();
$rand_bug = $stmt->fetch();
感想
オフセットランダムの発想、一休さん的（古い）で面白いと思った！
しかし、その４がについては、１～３の弱点を補ってます！との記載があったけども、、、
クエリの２回実行は仕方ないとして、データ量が多い場合、
SELECT COUNT(*) FROM Bugs
の部分で全件スキャン走るから、パフォーマンス落ちるのでは。。と思ったけど。。🤔（ちがうかな？
レコード数を定期的に計算して、その結果をキャッシュしておくような処理を別で作ってあげるといいのかな😯💭
：
そのうち別の章も追加予定。
：
Discussion