はじめに
開発をしていく上で、SQLの非効率な書き方やDB設計のアンチパターンを知っておきたいと思いました。
著書「SQLアンチパターン」を読んで、各部から印象に残った章の内容を抜粋してまとめていきます。
読みづらいところなどあるかと思いますが、少しでも参考になれば幸いです。
SQLアンチパターンの要約一覧です。
他の章についても要約してます！
Ⅲ部 クエリのアンチパターン
16章 プアマンズ・サーチエンジン（貧者のサーチエンジン）
アンチパターン：パターンマッチ述語を使用する
キーワード検索を実装するときはLIKE述語がよく使われており、0個以上の文字と一致するワイルドカード（%）を扱うことができます。
キーワードの前後にこのワイルドカードを指定すると、その語が含まれるすべての文字列がマッチします。
SELECT * FROM Bugs WHERE description LIKE '%crash%';
多くのデータベース製品は、正規表現も独自の方法でサポートしています。正規表現を使うと、どのような部分文字列に対してもパターンマッチを行えるため、ワイルドカードは不要になります。
以下はMySQLの正規表現述語を使った例です。
SELECT * FROM Bugs WHERE description REGEXP 'crash';
パターンマッチ述語の問題点
-
パフォーマンスの低下
パターンマッチ述語は従来のインデックスのメリットを得られないため、テーブルの全ての行をスキャンしなければいけません。 -
LIKEや正規表現の単純なパターンマッチでは、意図しないマッチが生じてしまう
以下のクエリは、単語「one」を含むテキストとマッチしますが、「money」、「prone」、「lonely」などもマッチしてしまいます。
SELECT * FROM Bugs WHERE description LIKE '%one%';
アンチパターンを用いても良い場合
使用頻度が極めて低いクエリには、最適化に労力をかける意味がありません。
滅多に使用しないクエリのためにインデックスを更新するのは、その非効率なクエリをインデックス無しで実行するのと同じくらいコストがかかります。
単純なケースのためにパターンマッチを行う場合には、欠点を最小限に抑えつつ、十分な結果を得ることもできます。
解決策：適切なツールを使用する
最善の方法は、SQLの代わりに全文検索エンジンを使うことです。次善の策は、繰り返しのコストを減らすために検索結果を保存することです。
以下はデータベース製品における全文検索機能の概要です。
MySQLのフルテキストインデックス
MySQLのフルテキストインデックス（全文検索するためのインデックス）を定義できるのは、CHAR、VARCHAR、TEXT型の列です。
以下はBugsテーブルのsummary列とdescription列の内容を含むフルテキストインデックスの定義です。
ALTER TABLE Bugs ADD FULLTEXT INDEX bugfts(summary, description);
インデックスに格納されたテキストからキーワードを検索するには、MATCH関数を使います。
MATCH関数には、フルテキストインデックスに関連づけられた列名を指定する必要があります。列名は上記のsummary, descriptionのように複数指定することができます。
SELECT * FROM Bugs WHERE MATCH(summary, description) AGAINST('crash');
※AGAINSTには、検索する文字列と、実行する検索のタイプを示すオプションの修飾子を指定します。
PostgreSQLでのテキスト検索
PostgreSQLを使ってパフォーマンスを最適化するには、テキスト検索可能な特別なデータ型TSVECTORを用いてコンテンツを格納する必要があります。
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
summary VARCHAR(80),
description TEXT,
ts_bugtext TSVECTOR
-- 他の列...
);
TSVECTOR列は、検索対象のテキスト列の内容と常に同期する必要があります。
PostgreSQLでは同期作業を容易にするために、組み込みのトリガーが提供されています。
CREATE TRIGGER ts_bugtext BEFORE INSERT OR UPDATE ON Bugs
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(ts_bugtext, 'pg_catalog.english', summary, description);
さらに、TSVECTOR列に対してGIN(汎用転置インデックス)を作成しなければいけません。
CREATE INDEX bugs_ts ON Bugs USING GIN(ts_bugtext);
これで、PostgreSQLのテキスト検索演算子（@@）を用いて、フルテキストインデックスを活用した効果的な全文検索が行えるようになります。
SELECT * FROM Bugs WHERE ts_bugtext @@ to_tsquery('crash');
まとめ
LIKEのようなパターンマッチ述語は、意図しないマッチや、テーブルの全ての行をスキャンしなければいけない問題などが発生することがある。
データベース製品の全文検索エンジンなどの機能を使用することで、検索におけるパフォーマンスを向上させることができる。
所感
今回は時間の都合で抜粋してまとめました。ですが正直言って、他の章の内容も良かったので、テーマを一つに絞って書くのは難しかったです。
（個人的に、13章の「フィア・オブ・ジ・アンノウン（恐怖のunknown）」はもう一度読み返したい）
ただそれよりも重要なのは、「実務や実践の場で活かす」ことだと思っています。今後DB設計や実装をしていく中で、アンチパターンについて思い出しながら理解を深めていければと思います。
参考記事
世界一わかりやすい FULLTEXT INDEX の説明と気を付けるべきポイント
MySQL 8.0 リファレンスマニュアル / 12.10 全文検索関数
PostgreSQL 9.4.5文書 8.11.1. tsvector