はじめに
こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅲ部のクエリのアンチパターンについて記載します。
この記事は「SQLアンチパターン」の気づき（クエリ2）の続きになります。
15章.ランダムセレクション
アンチパターン：データをランダムにソートする
SQLでランダムな行を取得するための最も一般的な方法は、ランダムにソートを行い、最初の行を取得するというものです。
SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;
デメリット
- ランダムにしたデータからデータを取得する方法はデータベースのパフォーマンスが落ちる
- インデックスからメリットを得られない(実行するたびに結果が変わるため)
- すべての結果を一時的なテーブルとして保存し、物理的に行を入れ替える処理を行う（テーブルスキャン）ため、データセットのサイズが大きくなるにつれてパフォーマンスも落ちる
解決策：特定の順番に依存しない
- 1と最大値の間のランダムなキー値を選択する
- テーブル全体のソートを回避するための方法は、1から主キーの最大値までの間の値をランダムに選択すること
- 主キーが1から始まり、連続している必要がある
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
- 欠番の穴の後にあるキー値を選択する
- １と最大キー値の間に欠番があり、かつ乱数によって欠番のキー値が算出される場合に、このクエリは欠番の穴の後にあるキー値を使う
SELECT b1.*
FROM Bugs AS b1
INNER JOIN
SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS bug_id
) AS b2 ON b1.bug_id >= b2.bug_id
ORDER BY b1.bug_id
LIMIT 1
- すべてのキー値のリストを受けとり、ランダムに１つを選択する
- 取得したリストのサイズが大きい場合、メモリのリソースを超えてしまいエラーになる
- オフセットを用いてランダムに行を選択する
- データセットの行数をカウントし、0と行数までの間の乱数を返す
- キー値が連続していることが前提とできず、各行が平等に選択される必要がある場合に使用できる
- べンダー依存の解決策
- ほとんどのデータベース製品には、この章の目的を実現するための独自の解決策が実装されている
16章.プアマンズ・サーチエンジン（貧者のサーチエンジン）
アンチパターン：パターンマッチ述語を使用する
SQLには文字列比較のためのパターンマッチ述語があり、キーワード検索をする際にLIKE述語がよく使われます。
LIKE述語は、０個以上の文字と一致するワイルドカード（％）を扱うことができキーワードの、キーワードの前後にこのワイルドカードを使用すると、その語が含まれるすべての文字列がマッチします。
SELECT * FROM Bugs WHERE description LIKE '%crash%';
デメリット
パターン述語を使うことによる問題点として以下が挙げられます。
- パフォーマンスの低下
- パターンマッチ述語は従来型のインデックスのメリットを得られないため、テーブル全ての行をスキャンしなければならない
- LIKEや正規表現を用いた単純なパターンマッチでは、意図しないマッチが生じてしまう
- 以下のクエリは、単語「one」を含むテキストとマッチしますが、「money」、「prone」、「lonely」などもマッチしてしまいます
SELECT * FROM Bugs WHERE description LIKE '%one%';
解決策：適切なツールを使用する
最善の方法は、SQLの代わりに全文検索エンジンを使うことです。次善の策は、繰り返しのコストを減らすために検索結果を保存することです。
まとめ
今回は第Ⅲ部のクエリのアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。
以上、小川でした。